Автоматизация отчетности при помощи SQL и Power BI

В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.

Почему именно эти технологии?

За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.

Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.

Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.

Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:

1. Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
2. Еще пример, если вы пытаетесь загрузить в модель данные из Google Analytics при помощи встроенного коннектора, то столкнетесь как минимум с двумя проблемами — ограничениями API GA и долгой выгрузкой данных.

Вышеописанные проблемы привели меня к мысли о загрузке всех данных сначала в базу, моделировании отчета при помощи SQL и только потом их визуализации в Power BI.

Переходим к делу

Для примера возьмем задачу по автоматизации отчета по эффективности контекстной рекламы.

К данному отчету заказчиком предъявляются следующие требования:

• Отчет должен содержать исторические данные по вчерашний день;
• Отчет должен обновляться ежедневно в автоматизированном режиме;
• Помимо Power BI, должна быть возможность подключения к отчету через Excel.

Также отчет должен содержать следующие параметры и показатели:

• Дата;
• Источник/Канал
• Кампания
• Сумма расходов;
• Кол-во показов;
• Кол-во кликов;
• Кол-во сеансов;
• Кол-во заказов;
• Доход;
• Рассчитываемые показатели — CPC, CR и ROMI.

Естественно, все данные должны быть предварительно загружены в хранилище, но это тема отдельного поста и обычно этим занимаются data-инженеры. Мы же с вами аналитики и используем те данные, которые для нас любезно сложили в DWH (хранилище данных).

В моем случае DWH работает на базе MS SQL Server и содержит следующие таблицы:

• sessions — данные из Google Analytics загруженные посредством коннектора к Reporting API v4;
• costs — данные по расходам, предварительно загруженные в Google Analytics;
• orders — данные по заказам и доходу из внутренней CRM-системы.

Для работы нам потребуется установить:

• SQL Server Management Studio — для подключения к DWH;
• Power BI Desktop — для создания отчета.

Опущу совсем уж базовые вещи, такие как регистрация аккаунтов и установка программ, с этим вы без проблем справитесь и сами.

Готовим данные

Итак, задача понятна, инструменты готовы — за дело!

Создаем таблицу

Для того чтобы создать отчет, нам необходимо свести данные по расходам, сеансам и заказам в одной таблице. Для этого напишем SQL-запрос, в котором объединим таблицы по следующим ключам:

• date;
• sourceMedium;
• campaign.

Но вернемся к задаче и после некоторых манипуляций с SQL получим вот такой скрипт:

-- Создаем переменные с датами отчета
SET DATEFIRST 1
DECLARE @startDate date, @endDate date;
SET @startDate = '2020-03-10'
SET @endDate = '2020-03-10';

-- Запрашиваем сеансы
WITH [sessions] AS (
SELECT 
  [date]
, sourceMedium
, campaign
, SUM([sessions]) AS 'sessions'
-- Желательно использовать 'WITH (NOLOCK)', чтобы не блокировать высоконагруженную базу
FROM [GoogleAnalytics].[dbo].[sessions] WITH (NOLOCK)

-- Задаем период
WHERE [date] BETWEEN @startDate AND @endDate 
-- Указываем источники трафика, по которым будем строить отчет
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 

GROUP BY [date], [sourceMedium], [campaign]
)

-- Запрашиваем расходы
, costs AS ( 
SELECT 
  [date]
, sourceMedium
, campaign 
, SUM(cost) AS 'cost'
, SUM(impressions) AS 'impressions'
, SUM(clicks) AS 'clicks'
FROM [GoogleAnalytics].[dbo].[cost] WITH (NOLOCK)

WHERE [date] BETWEEN @startDate AND @endDate 
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 

GROUP BY [date], [sourceMedium], [campaign]
)

-- Объединяем сеансы с расходами
, costs_sessions AS (
SELECT 
-- 'ISNULL' используем для того, чтобы не получить результат 'NULL' там где не было расходов по источнику, но был сеанс
  ISNULL(costs.[date], [sessions].[date]) AS 'date'
, ISNULL(costs.sourceMedium, [sessions].sourceMedium) AS 'sourceMedium'
, ISNULL(costs.campaign, [sessions].campaign) AS 'campaign'
, ISNULL(SUM(costs.cost),0) AS 'cost'
, ISNULL(SUM(costs.impressions),0) AS 'impressions'
, ISNULL(SUM(costs.clicks),0) AS 'clicks'
, ISNULL(SUM([sessions].[sessions]), 0) AS 'sessions'
FROM costs

FULL JOIN [sessions]
ON costs.[date] = [sessions].[date] AND costs.sourceMedium = [sessions].sourceMedium AND costs.campaign = [sessions].campaign

GROUP BY ISNULL(costs.[date], [sessions].[date]), ISNULL(costs.sourceMedium, [sessions].sourceMedium), ISNULL(costs.campaign, [sessions].campaign)
)

-- Запрашиваем заказы и доход
, orders AS (
SELECT 
  [date]
, sourceMedium
, campaign
, SUM(orders) AS 'orders'
, SUM(revenue) AS 'revenue'
FROM [Crm].[dbo].[orders] WITH (NOLOCK)

WHERE [date] BETWEEN @startDate AND @endDate 
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 

GROUP BY [date], [sourceMedium], [campaign]
)

-- Объединяем данные по трафику с данными о заказах
, join_table AS (
SELECT
  ISNULL(costs_sessions.[date], orders.[date]) AS 'date'
, ISNULL(costs_sessions.sourceMedium, orders.sourceMedium) AS 'sourceMedium'
, ISNULL(costs_sessions.campaign, orders.campaign) AS 'campaign'
, ISNULL(SUM(costs_sessions.cost), 0) AS 'cost'
, ISNULL(SUM(costs_sessions.impressions), 0) AS 'impressions'
, ISNULL(SUM(costs_sessions.clicks), 0) AS 'clicks'
, ISNULL(SUM(costs_sessions.[sessions]), 0) AS 'sessions'
 ,ISNULL(SUM(orders.orders), 0) AS 'orders'
, ISNULL(SUM(orders.revenue), 0) AS 'revenue'
FROM costs_sessions

FULL JOIN orders
ON costs_sessions.[date] = orders.[date] AND costs_sessions.sourceMedium = orders.sourceMedium AND costs_sessions.campaign = orders.campaign

GROUP BY ISNULL(costs_sessions.[date], orders.[date]), ISNULL(costs_sessions.sourceMedium, orders.sourceMedium), ISNULL(costs_sessions.campaign, orders.campaign)
)

-- Выводим итоговый результат
SELECT *
FROM join_table

Запустим его и порадуемся получившемуся результату:

Создаем таблицу

Скрипт работает и выдает отчет, в принципе его уже можно использовать для автоматизации вставив в Power BI при помощи встроенного коннектора. Но не советую так делать, потому что если данных в отчете будет много, например заказчик захочет посмотреть как работали рекламные кампании в течение года, на выполнение скрипта может уйти несколько часов.

Гораздо более правильным решением будет создать промежуточную таблицу в базе данных и докладывать туда ежедневно данные за прошедшие сутки. Что мы и сделаем:

Таблица будет иметь следующую структуру (подробнее о типах данных):

При сохранении таблицы укажем название:

И теперь, чтобы получить все данные из нее, достаточно выполнить простой SELECT:

SELECT * FROM paid_traffic_report

Создаем хранимую процедуру

Отлично! Настало время автоматизации 😉

А поможет нам в этом функционал хранимых процедур (подробнее рассказывал о них тут).

Засучим рукава и обернем наш скрипт в код процедуры:

CREATE PROCEDURE fill_paid_traffic_report
(
  @startDate date,
  @endDate date
)
AS
BEGIN

-- Уберем из кода переменные, они нам понадобятся позже при настройке расписания
-- Запрашиваем сеансы
WITH [sessions] AS (
SELECT 
  [date]
, sourceMedium
, campaign
, SUM([sessions]) AS 'sessions'
-- Желательно использовать 'WITH (NOLOCK)', чтобы не блокировать высоконагруженную базу
FROM [GoogleAnalytics].[dbo].[sessions] WITH (NOLOCK)
-- Задаем период
WHERE [date] BETWEEN @startDate AND @endDate 
-- Указываем источники трафика, по которым будем строить отчет
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 
GROUP BY [date], [sourceMedium], [campaign]
)
-- Запрашиваем расходы
, costs AS ( 
SELECT 
  [date]
, sourceMedium
, campaign 
, SUM(cost) AS 'cost'
, SUM(impressions) AS 'impressions'
, SUM(clicks) AS 'clicks'
FROM [GoogleAnalytics].[dbo].[cost] WITH (NOLOCK)
WHERE [date] BETWEEN @startDate AND @endDate 
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 
GROUP BY [date], [sourceMedium], [campaign]
)
-- Объединяем сеансы с расходами
, costs_sessions AS (
SELECT 
-- 'ISNULL' используем для того, чтобы не получить результат 'NULL' там где не было расходов по источнику, но был сеанс
  ISNULL(costs.[date], [sessions].[date]) AS 'date'
, ISNULL(costs.sourceMedium, [sessions].sourceMedium) AS 'sourceMedium'
, ISNULL(costs.campaign, [sessions].campaign) AS 'campaign'
, ISNULL(SUM(costs.cost),0) AS 'cost'
, ISNULL(SUM(costs.impressions),0) AS 'impressions'
, ISNULL(SUM(costs.clicks),0) AS 'clicks'
, ISNULL(SUM([sessions].[sessions]), 0) AS 'sessions'
FROM costs
FULL JOIN [sessions]
ON costs.[date] = [sessions].[date] AND costs.sourceMedium = [sessions].sourceMedium AND costs.campaign = [sessions].campaign
GROUP BY ISNULL(costs.[date], [sessions].[date]), ISNULL(costs.sourceMedium, [sessions].sourceMedium), ISNULL(costs.campaign, [sessions].campaign)
)
-- Запрашиваем заказы и доход
, orders AS (
SELECT 
  [date]
, sourceMedium
, campaign
, SUM(orders) AS 'orders'
, SUM(revenue) AS 'revenue'
FROM [Crm].[dbo].[orders] WITH (NOLOCK)
WHERE [date] BETWEEN @startDate AND @endDate 
AND sourceMedium IN ('google / cpc', 'yandex / cpc') 
GROUP BY [date], [sourceMedium], [campaign]
)
-- Объединяем данные по трафику с данными о заказах
, join_table AS (
SELECT
  ISNULL(costs_sessions.[date], orders.[date]) AS 'date'
, ISNULL(costs_sessions.sourceMedium, orders.sourceMedium) AS 'sourceMedium'
, ISNULL(costs_sessions.campaign, orders.campaign) AS 'campaign'
, ISNULL(SUM(costs_sessions.cost), 0) AS 'cost'
, ISNULL(SUM(costs_sessions.impressions), 0) AS 'impressions'
, ISNULL(SUM(costs_sessions.clicks), 0) AS 'clicks'
, ISNULL(SUM(costs_sessions.[sessions]), 0) AS 'sessions'
 ,ISNULL(SUM(orders.orders), 0) AS 'orders'
, ISNULL(SUM(orders.revenue), 0) AS 'revenue'
FROM costs_sessions
FULL JOIN orders
ON costs_sessions.[date] = orders.[date] AND costs_sessions.sourceMedium = orders.sourceMedium AND costs_sessions.campaign = orders.campaign
GROUP BY ISNULL(costs_sessions.[date], orders.[date]), ISNULL(costs_sessions.sourceMedium, orders.sourceMedium), ISNULL(costs_sessions.campaign, orders.campaign)
)
-- Вставляем данные в таблицу
INSERT INTO paid_traffic_report
SELECT *
FROM join_table

END;

Теперь протестируем и вручную вызовем процедуру:

Скорость отработки процедуры 3 секунды на одном дне — вполне приемлемо. Проверим появились ли данные в ранее созданной таблице:

Осталось настроить ежедневное обновление.

Настраиваем расписание

Настроим вызов нашей процедуры каждое утро по расписанию, благо в Management Studio для этого предусмотрена специальная служба под названием «Агент SQL Server».

Зайдем в агент и добавим новое задание:

Укажем название и придумаем описание:

Далее создадим новый шаг, в котором будем вызывать процедуру с данными за прошедшие сутки (обратите внимание, объявление переменных с датами из нашего скрипта мы перенесли в расписание и немного изменили):

Настраиваем время запуска, периодичность и сохраняем:

Теперь данные автоматически будут поступать в отчет ежедневно в 9 утра.

Визуализируем данные

Данные готовы, обновление настроено, самое время приступить к визуализации.

Останавливаться на том как установить Power BI и как им пользоваться не буду, так как этой теме посвящен целый урок нашего курса.

Создаем отчет

Заходим в desktop-версию Power BI и открываем коннектор к SQL Server:

Вводим данные для подключения к серверу, название базы данных и наш короткий SQL-запрос к ранее созданной табличке:

И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.

Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.

После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:

Настраиваем расписание

Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.

Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):

Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:

Готово. Теперь можем пользоваться отчетом внутри веб-интерфейса, опубликовать отчет в интернете, либо отправить коллегам ссылку на него.

А как же Excel?

Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.

Для этого зайдем в Excel на вкладку «Данные» и создадим новое подключение к серверу баз данных:

После чего останется только указать SQL-запрос и сохранить:

С этого момента данные из нашей таблицы на сервере станут доступны в Excel.

Итог

В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.

Буду рад ответить на ваши вопросы.