В своей прошлой статье я рассказывал про программные возможности языка SQL и обещал поделиться кейсом по созданию автоматизированного отчета на основе стека технологий MS SQL Server и Power BI.
Почему именно эти технологии?
За время работы аналитиком, я перепробовал различные варианты сбора отчетности. Начиная с ручной выгрузки данных из кабинетов рекламных систем, с последующим сведением в Excel, и заканчивая созданием специальных отчетов в Google Analytics или дашбордов в Data Studio.
Но ни один из вариантов не был идеальным и каждый имел свои недостатки. Все изменилось, когда я открыл для себя Power BI.
Microsoft Power BI — это один из самых технологичных на данный момент инструментов по визуализации данных, обладающий большим набором коннекторов к различным системам.
Но и Power BI сам по себе не идеален и без грамотного использования будет работать медленно и неэффективно. Приведу два примера:
- Если вы попытаетесь собрать модель данных из различных источников, с большим количеством связей и рассчитываемых показателей на стороне Power BI, то отчет будет жутко тормозить, а ведь именно таким принципам работы учит большое количество курсов по данному инструменту.
- Еще пример, если вы пытаетесь загрузить в модель данные из 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
.
Кстати, никакой сквозной аналитики у вас никогда не получится, если вы не умеете грамотно размечать рекламу 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-запрос к ранее созданной табличке:
И это все! Никаких сложных моделей в Power BI строить не нужно, так как мы уже это сделали на стороне SQL-запроса.
Наиболее правильным считаю подход, когда инструмент визуализации используется именно для этой самой визуализации и еще для создания рассчитываемых показателей (например, CPC, CPO, ROMI). Используйте эти рекомендации и ваши отчеты будут летать.
После того как будет готов дизайн отчета, его нужно загрузить в облако Microsoft:
Настраиваем расписание
Отчет опубликован! Остался финальный шаг, для этого переходим в веб-версию Power BI и настраиваем расписание обновления.
Но перед этим не забываем поставить на компьютер, с которого будет происходить обновление, локальный шлюз Power BI (а лучше всего завести под это дело отдельную виртуальную машину):
Важно так подгадать расписание, чтобы оно запускалось в тот момент, когда на стороне SQL Server уже отработает наша процедура и положит в табличку свежие данные. Плюс нужно заложить небольшой запас времени, на возможные проблемы с сервером при его перегрузке:
Готово. Теперь можем пользоваться отчетом внутри веб-интерфейса, опубликовать отчет в интернете, либо отправить коллегам ссылку на него.
А как же Excel?
Иногда заказчики могут попросить загрузить данные в Excel для более детального анализа.
Для этого зайдем в Excel на вкладку «Данные» и создадим новое подключение к серверу баз данных:
После чего останется только указать SQL-запрос и сохранить:
С этого момента данные из нашей таблицы на сервере станут доступны в Excel.
Итог
В итоге мы получили автообновляемую отчетность, без привлечения каких-то гигантских ресурсов разработки и без особых денежных затрат.
Буду рад ответить на ваши вопросы.