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

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

Здравствуйте, коллеги! Сегодня хотелось бы рассказать и показать, как, используя 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
Тест: Кто как пробежал, или Итоги клиентского рейтинга SEOnews 2018
Анна Макарова
335
комментариев
0
читателей
Полный профиль
Анна Макарова - Друзья, спасибо всем за участие! Мы определили победителей. Кто ими стал - вы найдете по ссылке: www.seonews.ru/events/darim-knigi-ot-mif-pobediteli-opredeleny/ Если вы стали одним из победителей, обязательно свяжитесь с нами по указанной в новости (по ссылке выше) почте. Всем хороших выходных! =)
32 инструмента в помощь SEO-специалисту
luthorscorploanfirm
2
комментария
0
читателей
Полный профиль
luthorscorploanfirm - Я - г-н Марк Джо из корпорации corp, мы являемся законной, зарегистрированной и гарантированной кредитной компанией, мы предоставляем 3% -ые кредиты частным лицам, компаниям, государственным учреждениям и деловым организациям и людям всех родов. Свободные кредиты - это решение всех ваших финансовых проблем. свяжитесь с нами по электронной почте: mjoe0123@gmail.com
SEO глазами клиентов 2018
Гость
1
комментарий
0
читателей
Полный профиль
Гость - То есть *** пойми кто подался заявками и в итоге рейтинг должен называться seo глазами клиентов *** пойми каких компаний. Молодцы!
Комплексный аудит интернет-магазина от «Ашманов и партнеры». Часть 1
Александр Сова
1
комментарий
0
читателей
Полный профиль
Александр Сова - А вот и сеошники подъехали, покидать на вентилятор :D
Не очень удачный кейс продвижения сайта по услуге «Трезвый водитель» в Москве
Кирилл Щербаков
3
комментария
0
читателей
Полный профиль
Кирилл Щербаков - "даже пришлось подключить отслеживание звонков с сайта" "Даже" - как будто это что-то нереальное
Два идеальных способа разориться на старте интернет-магазина
Стас
4
комментария
0
читателей
Полный профиль
Стас - Seonews в последнее время такую чушь несет! Где вы таких афторов находите? Статья ничего не стоит и несет чушь! Кто хоть немного понимает в этом так вам и скажет, и такие де комментаторы горе-сеошники, просто смешно читать, вы хоть модерируете ваши статьи или просто для воды на сайте?! Бред сивой кобылы эта статья до самой последней точки!!!
Эксперимент: как уникальность контента влияет на продвижение сайта
Ilia Nazmutdinov
2
комментария
0
читателей
Полный профиль
Ilia Nazmutdinov - Кстати, ПФ не работают на нулевом трафике. Пока на сайт не льются тысячи показов по одним и тем же запросам влияние оказывает ток ссылочное\внешнее и внутреннее\ и внутренняя оптимизация.
Яндекс перестал индексировать сайты, созданные на Wix
Константин Даткунас
3
комментария
0
читателей
Полный профиль
Константин Даткунас - Было бы интересно посмотреть саму выборку из 10 000 и методику анализа.
Прощай, тИЦ! Яндекс переходит на новый показатель качества
Дмитрий Кондратенко
3
комментария
0
читателей
Полный профиль
Дмитрий Кондратенко - PR уже год как отменили, ТИЦ пол года не обновляется... Вы или "не те книги читали", или Вас кинули ушлые СЕОшники... www.internet-reklama.pp.ua
Расчет трафика из поисковых систем по брендовым и небрендовым запросам
Дарья Калинская
239
комментариев
0
читателей
Полный профиль
Дарья Калинская - А вот и статья :) www.seonews.ru/analytics/raschet-trafika-po-nebrendovym-zaprosam-pri-pomoshchi-r/
ТОП КОММЕНТАТОРОВ
Комментариев
910
Комментариев
834
Комментариев
554
Комментариев
540
Комментариев
483
Комментариев
373
Комментариев
335
Комментариев
262
Комментариев
239
Комментариев
171
Комментариев
156
Комментариев
137
Комментариев
121
Комментариев
97
Комментариев
97
Комментариев
95
Комментариев
89
Комментариев
80
Комментариев
67
Комментариев
60
Комментариев
60
Комментариев
59
Комментариев
57
Комментариев
55
Комментариев
54

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