Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно?

Россия+7 (495) 960-65-87
Шрифт:
1 4236

Здравствуйте, коллеги! Сегодня хотелось бы рассказать и показать, как, используя 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, который имитирует нашу базу данных, где каждая вкладка – это отдельная таблица.

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

Яндекс.Директ

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 1.png

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

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

Google AdWords

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 2.png

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

Other_cpc

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 3.png

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

Other_Cost

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 4.png

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

- Комиссия за ведение рекламных систем.

- Данные по офлайн-расходам.

- Данные по расходам на SEO.

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

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

Google Analytics

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 5.png

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

Calltracking

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 6.png

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

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

CRM

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 7.png

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

В остальном же у нас есть следующие поля:

date – дата создания сделки в CRM

phone – номер телефона клиента

cid – Client ID

record – запись на нашу услугу

visit – отметка, что клиенту данную услугу оказали

price – выручка, полученная с клиента

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

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

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

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

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 8.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 9.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 10.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 11.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 12.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 13.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 14.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 15.png

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 16.png

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 17.png

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

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

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

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 18.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 19.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 20.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 21.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 22.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 23.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 24.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 25.png

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

Данные из CRM

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 26.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 27.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 28.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 29.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 30.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 31.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 32.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 33.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 34.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 35.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 36.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 37.png

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 38.png

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

- Первый источник получает 40% веса.

- Последний источник получает 40% веса.

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

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 39.png

Где у нас есть вся статистика по этому 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 визит)

)

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 40.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 41.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 42.png

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 43.png

Lastclick:

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 44.png

Custom:

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 45.png

Связь данных

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 46.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 47.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 48.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 49.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 50.png

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

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 51.png

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

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

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 52.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 53.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 54.png

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

Своя модель атрибуции в Power BI для сквозной аналитики – все ли так сложно 55.png

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

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

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

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

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

Еще раз ссылка на Google Spreadsheets: https://docs.google.com/spreadsheets/d/1OvesOZeJYhUurngYVlfx_KcYQO6oh8vgYJWs2Fn5hDY/edit?usp=sharing

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

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

0
0

Есть о чем рассказать? Тогда присылайте свои материалы в редакцию.


Новые 
Новые
Лучшие
Старые
Сообщество
Подписаться 
Подписаться на дискуссию:
E-mail:
ОК
Вы подписаны на комментарии
Ошибка. Пожалуйста, попробуйте ещё раз.
Отправить отзыв
ПОПУЛЯРНЫЕ ОБСУЖДЕНИЯ НА SEONEWS
Рейтинг Известности 2018: старт народного голосования
Михаил Р
1
комментарий
0
читателей
Полный профиль
Михаил Р - 1. Demis 2. кокс 3. Ашманов 4. Скобеев 5. Digital Strategy
Сколько ссылок помогут продвинуть молодой сайт
Павел Андрейчук
33
комментария
0
читателей
Полный профиль
Павел Андрейчук - Дело в том, что вряд ли в ваших платных "качественных" кейсах найдётся хоть пару % действительно новой и полезной информации которой бы не было на общедоступных источниках.
Рейтинг Известности 2018: второй этап народного голосования
Константин Сокол
3
комментария
0
читателей
Полный профиль
Константин Сокол - Кто был ответственный за дизайн таблицы голосования? Копирайтер?
Сайт на WordPress: за и против
Мира Смурков
1
комментарий
0
читателей
Полный профиль
Мира Смурков - Людмила, я согласен с большинством комментаторов. Вы хоть один полноценный магазин сделали на этих движках? Woocommerce это система с супер возможностями. И к ней есть дополнительные модули, с функционалом, который вряд ли появиться на Битрикс. А самому это программировать - сотни тысяч рублей на разработку. А приведя в пример сложности с robots.txt и Sitemap вы ставите под вопрос вашу компетенцию в понимании Интернет-бизнеса и веб-разработки в целом. Во-первых это такие мелочи, а во-вторых это все делается на вордпресса за 2 минуты, и опять же с возможностями многократно превышающими Битрикс.
Обзор популярных CMS: плюсы и минусы
Гость
1
комментарий
0
читателей
Полный профиль
Гость - как по мне joomla! самая удобная и гибкая система из всех современных (я про wp раньше так думал, пока меня в Extrit не убедили в обратном). Раньше всегда оставался на техподдержке, потому что сам заполнять не мог, а теперь делаю это сам, потому что админка простая как палка. + на других цмсках при просьбе что-то добавить мне либо цену загибали овер 100500 либо говорили - нереально, делай новый сайт. А на joomla норм
Google обошел Яндекс по популярности в России в 2018 году: исследование SEO Auditor
Рамблер
1
комментарий
0
читателей
Полный профиль
Рамблер - Вот вроде отечественный - это сказано верно.. «Я́ндекс» — российская транснациональная компания, зарегистрированная в Нидерландах. Так говорится в Википедии. И с хрена ли ОТЕЧЕСТВЕННЫЙ поисковик зарегистрирован в Европе? И где платится основная часть налогов? Ну-ууу, точно не в России. И если запахнет жаренным, то был Яндекс и нет Яндекса!
8 методик в SEO, от которых давно пора отказаться
Евгений Сметанин
11
комментариев
0
читателей
Полный профиль
Евгений Сметанин - Факторов вообще очень много, согласитесь, вы будете использовать максимальное их количество, особенно, если в ТОПе засели агрегаторы с сумасшедшими ПФ. В таких случаях, вхождение ключа в домен для маленького профильного сайта, сыграет свою положительную роль. Конечно же, если контент на страницах хорошего качества. У меня есть несколько успешных кейсов на эту тему. На сайте продают несколько видов товаров, а выстреливает в ТОП тот, название которого присутствует в доменном имени. Как корабль назовешь, так он и поплывет, верно?))
Инструкция: настраиваем цели Яндекс.Метрики через Google Tag Manager
Roman Gorkunenko
1
комментарий
0
читателей
Полный профиль
Roman Gorkunenko - Здравствуйте. Подскажите, пожалуйста, можно с айпи метрики вытащить среднюю стоимость клика по утм меткам? В метрике есть такой шаблон tags_u_t_m, но он не совместим с меткой директа, у них разные префиксы.
Аудит структуры интернет-магазина мебели от «Ашманов и партнеры»
Дмитрий
11
комментариев
0
читателей
Полный профиль
Дмитрий - Сергей, а вы допускаете, что вся ваша жизнь - seo-миф?
Идеальный каталог для продвижения сайта: структура и функционал
Татьяна Ягутьева
6
комментариев
0
читателей
Полный профиль
Татьяна Ягутьева - Уважаемый Денис, сделайте сами напишите свою статью. Посоветуйте другую универсальную CMS, желательно идеальную по всем параметрам. Битрикс очень сильно зависит от разработчика. Если у него прямые руки - бардака не будет. А вообще, конечно, холивар на тему CMS/конструкторов/фреймворков - наверное, вечный. Давайте не будем начинать :)
ТОП КОММЕНТАТОРОВ
Комментариев
910
Комментариев
834
Комментариев
554
Комментариев
540
Комментариев
483
Комментариев
373
Комментариев
345
Комментариев
262
Комментариев
246
Комментариев
171
Комментариев
156
Комментариев
137
Комментариев
121
Комментариев
100
Комментариев
97
Комментариев
97
Комментариев
96
Комментариев
80
Комментариев
74
Комментариев
67
Комментариев
60
Комментариев
59
Комментариев
57
Комментариев
56
Комментариев
55

Отправьте отзыв!
Отправьте отзыв!