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

Итак, давайте разберемся, что такое сквозная аналитика и что нам необходимо для того, чтобы ее внедрить. Для удобства мы разбили нашу статью на несколько шагов:

1. Создание архитектуры
2. Подготовка данных
3. Создание отчетности

Давайте по очереди разберем каждый из них.

Для пример будут использоваться тестовые данные, которые вы сможете найти по ссылке и повторить самостоятельно все описанные действия с ними:

Ссылка на Google Spreadsheets

Создание архитектуры

Перед тем как приступать к созданию любой системы автоматизации отчетности, очень важно продумать архитектуру, на базе которой все это будет работать.

Определение источников данных

Первое и самое важное что необходимо сделать, это определить, какие источники данных нам необходимо использовать. В нашем тестовом примере мы взяли следующую ситуацию.

Используются такие рекламные каналы:

1. Яндекс.Директ
2. Google Adwords
3. Таргетированная реклама в соц. сетях
4. Наружная реклама на билборде

Для сбора статистики используются следующие инструменты:

1. Коллтрекинг
2. Google Analytics
3. CRM c данными по заказам

Данный список источников является вполне типичным для нашего рынка, так что на нем в этой статье мы решили и остановиться.

Сбор данных и их хранение

Для хранения данных мы используем Google BigQuery. О плюсах и минусах данного решения в рамках нашей статьи мы рассуждать не будем, тут выбор не так принципиален.

Для выгрузки данных из Яндекс.Директ, Google Adwords и Google Analytics мы используем сервис наших партнеров Renta.im. Настройка его достаточно простая, останавливаться на этом более подробно нет смысла.

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

1. Приходит входящий звонок, по нему определяется источник и остальные данные
2. Оператор проводит звонок
3. Система коллтрекинга отправляет данные на наш скрипт
4. Наш скрипт записывает данные о звонке в BigQuery.

Данные из CRM мы импортируем также отдельным скриптом или напрямую из БД CRM системы, или используя их API. Делается это раз в сутки — ночью, за прошедший день.

Тестовые данные

Как уже упоминалось выше, для удобства работы с данными был создан документ в Google SpreadSheets, который имитирует нашу базу данных, где каждая вкладка — это отдельная таблица.

Давайте более подробно рассмотрим его структуру.

Yandex.Direct

На данной вкладке хранится образец данных, которые мы обычно получаем из Яндекс.Директа. Информация стандартная:

Дата — Источник — Канал — Кампания — Ключевое слово — Число кликов — Стоимость. Для того чтобы не усложнять нашу тестовую модель, мы убрали отсюда данные по показам и площадкам, но использовать их для анализа в реальной жизни можно и нужно.

Google.Adwords

На данной вкладке все аналогично Яндекс.Директу, данные храним те же самые.

Other_cpc

Данная вкладка у нас отвечает за хранение расходов по другим платным источникам, расход из которых мы можем получить автоматически через API, или, например, настроить экспорт в Google Analytics с помощью некоторых бесплатных сервисов и получать данные уже из этой системы.

Other_Cost

В этой таблице мы храним данные по источникам, расход по которым мы можем добавить вручную, например, такие:

• Комиссия за ведение рекламных систем
• Данные по оффлайн расходам
• Данные по расходам на SEO
• И любые другие данные, которые мы хотели бы автоматически добавить в отчет

У нас присутствуют два поля “start” и “end”, которые обозначают дату начала и конца периода расхода.

Google Analytics

Здесь мы храним данные непосредственно по всему трафику с сайта. Очень важный момент: чтобы предварительно у нас была настроена передача Client ID в Google Analytics. Есть несколько вариантов настройки, информацию можно без проблем найти в интернете. Без этого построить отчеты по мультиканальности возможности не будет.

Calltracking

Здесь мы храним данные по звонкам. Так как коллтрекинг у нас бывает двух видов — динамический и статический, то соответственно для динамического мы получаем Client ID посетителя и по нему уже определяем источник перехода. В случае со статическим коллтрекингом мы сразу забираем данные по источнику из системы коллтрекинга (обычно они настраиваются вручную и прикрепляются к источнику).

На данном этапе хотелось бы обратить внимание, что для примера мы добавили информацию о том, что звонки за одним из номеров у нас закреплены за источником billboard \ static, что по сути и представляет из себя оффлайн рекламу.

CRM

Для данного примера была взята ситуация, когда передача Client ID в CRM систему настроена. В отличие от ситуации с Google Analytics, это достаточно сложная процедура, и она обычно индивидуальна для каждой CRM системы, так что данную тему имеет смысл раскрывать в рамках отдельной статьи.

В остальном же у нас есть следующие поля:
date — дата создания сделки в CRM
phone — номер телефона клиента
cid — Client ID
record — запись на нашу услугу
visit — отметка, что клиенту данную услугу оказали
price — выручка, полученная с клиента

На этом обзор архитектуры наших данных завершен, можно переходить к следующему шагу.

Подготовка данных

Для подготовки данных мы используем Power Query — язык, который нам по умолчанию доступен в Power BI.

Загрузка данных в систему

Так как Power Query умеет получать данные непосредственно из интернета, то мы можем без проблем загрузить нашу информацию из Google SpreadSheets:

1. Выбираем нужны источник:

2) Нажимаем Файл — Опубликовать в Интернете…

Затем выбираем нужную вкладку, формат CSV и публикуем данные. В итоге мы получаем URL.

3) Загружаем данную ссылку в диалоговое окно Power BI:

Не забываем поменять кодировку, чтобы у нас правильно отображалась кириллица, и нажимаем кнопку Edit.

4) Переименовываем данную таблицу и преобразовываем в нужный нам формат:

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

5) Добавляем аналогично Google Adwords и Other_cpc:

6) Добавляем таблицу Other_Cost:

И разбиваем расход по дням равными долями, преобразовывая данную таблицу:

Если вам интересно более подробно узнать, как это сделать, то можете загрузить тестовый BI файл в конце статьи.

7) Добавляем также данные из Google Analytics, Calltracking и CRM:

Обратите внимание, что корректнее хранить телефоны в формате “Текст”, а не “Целое число”, как предлагает по умолчанию Power BI.

Обработка и сведение данных

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

Данные по расходу и трафику

Для начала соберем все данные по расходу в одну таблицу:

Обратите внимание, что у нас в таблице Other_cost нет данных по кликам, так что у нас там автоматически проставился null:

Здесь и далее null всегда будет заменяться заменой на “0” для последующей корректности обсчета данных.

Следующим шагом сделаем копию наших данных из Google Analytics и немного преобразуем их:

Сгруппируем по дате и источнику и посчитаем сумму сессий:

И добавим данные по расходам:

Добавим пользовательский столбец, который определяет, что приоритет в статистике мы отдаем кликам из рекламных систем:

В конечном итоге мы получаем таблицу с данными о трафике и расходах по всем источникам:

Данные из коллтрекинга

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

Как преобразовывать данные — тоже можно посмотреть в тестовом файле в конце статьи.

Данные из CRM

Для удобства следующим шагом сводим данные из CRM системы и системы коллтрекинга в одну таблицу.

Отдельно делаем для записей, где у нас есть cid, по ним мы можем отследить цепочки транзакций:

То есть в данной таблице у нас есть все данные по звонкам и заказам, включая сумму заказа.

А также заказы без cid:

Тут отдельно хотелось бы обратить внимание, что по звонкам, которые поступили на статический номер, который у нас закреплен за билбордом у нас также свелись заказы по номеру звонившего:

А вот данные из CRM:

Соответственно, если забежать немного вперед, они у нас будут также фигурировать в отчетах, включая расходы на них, переходы на сайт естественно нулевые:

Данные для модели атрибуции Last Click

Предварительная подготовка данных на прошлых шагах завершена, теперь можно считать данные для наших моделей атрибуции. С LastClick все просто, соединяем данные по трафику с данными по заявкам:

После преобразований, получаем данные по обращениям и заказам в разрезе источника трафика:

Данные для кастомной модели атрибуции

С кастомной моделью атрибуции все немного сложнее. За основу берутся все те же данные что и для модели атрибуции LastClick:

Сортируем наши записи по дате по возрастанию:

Затем добавляем индексный столбец (это нужно для определения первого и последнего источника):

и группируем все строки по cid:

Затем делаем дубль данного столбца и раскрываем его обратно:

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

• Первый источник получает 40% веса
• Последний источник получает 40% веса
• Между источниками в середине остальные 20% разделяются поровну

Теперь давайте разберемся, как же это посчитать в Power BI.

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

Где у нас есть вся статистика по этому Client ID. Для того чтобы посчитать вес, нам необходимо использовать следующую формулу:

if List.Sum([group][call]) = 0 then 0 //если сумма столбца call во вложенной таблице group = 0, то звонков было 0

else (if List.Count([group][Index])>2 then //если строк в таблице больше двух

(

if [Index]=List.First([group][Index]) then List.Sum([group][call]) * 0.4 //Для первого визита сумму звонков умножаем на 0,4

else if [Index]=List.Last([group][Index]) then List.Sum([group][call]) * 0.4 //Для последнего визита сумму звонков умножаем на 0,4

else List.Sum([group][call]) * 0.2/(List.Count([group][Index])-2) //Для остальных визитов сумму звонков умножаем на 0,2 и делим на количество строк минус два (первый и последний визит исключаем)

)

else if List.Count([group][Index])=2 then List.Sum([group][call]) * 0.5 //Если у нас число строк в таблице 2, то разделяем вес по 0,5

else List.Sum([group][call]) * 1 //В противном случае просто считаем количество звонков (это только один вариант, когда у нас был всего 1 визит)

)

Таким образом, мы получаем дробное количество звонков:

По аналогии считаем остальные столбцы:

Для сравнения таблица Lastclick:

Как мы видим — доля звонков у нас равномерно распределилась по источникам исходя из нашего кастомного правила. Коэффициенты можно задавать любые, на свое усмотрение.

Остается только одно — добавить в обе наши атрибуции данные, у которых нет Client ID, их мы по умолчанию считаем в разрезе LastClick и добавляем как есть:

Lastclick:

Custom:

Связь данных

Все готово, осталось только эти данные связать. Для этого мы прибегнем к небольшой хитрости. Так как для установления связи необходимы уникальные значения в таблице, то мы создаем, для начала, столбец с уникальным значением, который назовем Key по формуле ниже, а затем по нему и свяжем таблицы:

В итоге получаем такой столбец в каждой таблице:

Затем создаем новую таблицу, в которую собираем все данные по трафику и конверсиям:

Получаем на выходе таблицу, затем удаляем лишние столбцы и дубли значений по столбцу Key:

Теперь осталось только правильно сделать связи для того, чтобы можно было использовать все данные в одном отчете:

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

Визуализация данных

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

Как видно из скриншота — дату и источник (Source — Medium — Campaign — Term) мы берем из таблицы Bridge (синий цвет), данные по трафику и стоимости мы берем из таблицы Traffic (красный цвет), и данные по звонкам и заказам в двух моделях атрибуции (желтый и зеленый цвета), где (L) это LastClick, а (С) это Custom.

Следующим этапом добавим окно ретроспективного обзора, полезная штука для мультиканальной атрибуции. И в нашей модели оно может быть любым, в отличие от Google Analytics, где мы ограничены 90 днями:

Выведем дату первого визита в отдельный столбец:

И посчитаем разницу в днях между последней датой и первой:

Теперь можем использовать данную цифру как фильтр:

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

Где (L) это LastClick, а (С) — это Custom.

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

Вместо заключения

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

Пример BI файла, который получился в ходе написания статьи: https://yadi.sk/d/EV9L68pm3RUbRm

Еще раз ссылка на Google Spreadsheets.

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

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