Автоматизация отчетности при помощи 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-запрос, в котором объединим таблицы по следующим ключам:

  • date;
  • sourceMedium;
  • campaign.

Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу utm-метками. О том как правильно ставить метки, читайте в одном из уроков бесплатного онлайн-курса «Digital-аналитика для новичков».

Но вернемся к задаче и после некоторых манипуляций с 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 Server

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

Подключение к базе данных

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

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

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

Публикация отчета в облаке

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

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

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

Настройка шлюза данных

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

Настраиваем расписание обновления отчета

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

Отчет в Power BI

А как же Excel?

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

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

Подключение данных к Excel

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

SQL-запрос в Excel

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

Итог

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

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

9 replies on “ Автоматизация отчетности при помощи SQL и Power BI ”
  1. Хорошо что мой муж когда-то администрировал сервера, он взялся объяснить как sql server настроить))

  2. классный пост, коротко и чётко! примерно также создаем дашборды и отчетимся здесь, в нефтянке, Казахстан! Спасибо!

  3. Такой вопрос возник. При подключение к mssql через powerbi он не запрашивает пароль к серверу, как быть вданном случае? Можно ли организовать доступ по паролю или надо отдельный инстантс поднимать для этих целей?

  4. Добрый день!
    Для получения данных из GA писали консоль или получилось как-то обратиться на прямую?

    1. Здравствуйте!
      Написали коннектор к API, который каждую ночь забирает данные и ретроспективно обновляет предыдущие несколько суток.

        1. Такой статьи нет, к сожалению.
          Но скоро напишу статью про Logs Api Яндекс Метрики — это сейчас более актуальный коннектор)

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *