Содержание
- Создание архитектуры
- Подготовка данных
- Создание отчетности
Создание архитектуры
Перед тем как приступать к созданию любой системы автоматизации отчетности, очень важно продумать архитектуру, на базе которой все это будет работать.Определение источников данных
Первое и самое важное что необходимо сделать, это определить, какие источники данных нам необходимо использовать. В нашем тестовом примере мы взяли следующую ситуацию. Используются такие рекламные каналы:- Яндекс.Директ
- Google Adwords
- Таргетированная реклама в соц. сетях
- Наружная реклама на билборде
- Коллтрекинг
- Google Analytics
- CRM c данными по заказам
Сбор данных и их хранение
Для хранения данных мы используем Google BigQuery. О плюсах и минусах данного решения в рамках нашей статьи мы рассуждать не будем, тут выбор не так принципиален. Для выгрузки данных из Яндекс.Директ, Google Adwords и Google Analytics мы используем сервис наших партнеров Renta.im. Настройка его достаточно простая, останавливаться на этом более подробно нет смысла. Для сбора данных из коллтрекинга мы обычно используем самописный скрипт, который принимает вебхуки их системы. То есть схема выглядит следующим образом:- Приходит входящий звонок, по нему определяется источник и остальные данные
- Оператор проводит звонок
- Система коллтрекинга отправляет данные на наш скрипт
- Наш скрипт записывает данные о звонке в BigQuery.
Тестовые данные
Как уже упоминалось выше, для удобства работы с данными был создан документ в Google SpreadSheets, который имитирует нашу базу данных, где каждая вкладка - это отдельная таблица. Давайте более подробно рассмотрим его структуру.Yandex.Direct
На данной вкладке хранится образец данных, которые мы обычно получаем из Яндекс.Директа. Информация стандартная: Дата - Источник - Канал - Кампания - Ключевое слово - Число кликов - Стоимость. Для того чтобы не усложнять нашу тестовую модель, мы убрали отсюда данные по показам и площадкам, но использовать их для анализа в реальной жизни можно и нужно.Google.Adwords
На данной вкладке все аналогично Яндекс.Директу, данные храним те же самые.Other_cpc
Данная вкладка у нас отвечает за хранение расходов по другим платным источникам, расход из которых мы можем получить автоматически через API, или, например, настроить экспорт в Google Analytics с помощью некоторых бесплатных сервисов и получать данные уже из этой системы.Other_Cost
В этой таблице мы храним данные по источникам, расход по которым мы можем добавить вручную, например, такие:- Комиссия за ведение рекламных систем
- Данные по оффлайн расходам
- Данные по расходам на SEO
- И любые другие данные, которые мы хотели бы автоматически добавить в отчет
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:- Выбираем нужны источник:
Обработка и сведение данных
Для того чтобы нам можно было визуализировать данные, их надо предварительно обработать. Для удобства давайте разобьем данную часть статьи на несколько подчастей.Данные по расходу и трафику
Для начала соберем все данные по расходу в одну таблицу: Обратите внимание, что у нас в таблице Other_cost нет данных по кликам, так что у нас там автоматически проставился null: Здесь и далее null всегда будет заменяться заменой на “0” для последующей корректности обсчета данных. Следующим шагом сделаем копию наших данных из Google Analytics и немного преобразуем их: Сгруппируем по дате и источнику и посчитаем сумму сессий: И добавим данные по расходам: Добавим пользовательский столбец, который определяет, что приоритет в статистике мы отдаем кликам из рекламных систем: В конечном итоге мы получаем таблицу с данными о трафике и расходах по всем источникам:Данные из коллтрекинга
Преобразуем исходную таблицу с данными по звонкам, чтобы у нас считалось сколько у нас всего звонков поступило и сколько из них первичных. При необходимости можно еще считать уникальные или уникально-целевые. Как преобразовывать данные - тоже можно посмотреть в тестовом файле в конце статьи.Данные из CRM
Для удобства следующим шагом сводим данные из CRM системы и системы коллтрекинга в одну таблицу. Отдельно делаем для записей, где у нас есть cid, по ним мы можем отследить цепочки транзакций: То есть в данной таблице у нас есть все данные по звонкам и заказам, включая сумму заказа. А также заказы без cid: Тут отдельно хотелось бы обратить внимание, что по звонкам, которые поступили на статический номер, который у нас закреплен за билбордом у нас также свелись заказы по номеру звонившего: А вот данные из CRM: Соответственно, если забежать немного вперед, они у нас будут также фигурировать в отчетах, включая расходы на них, переходы на сайт естественно нулевые:Данные для модели атрибуции Last Click
Предварительная подготовка данных на прошлых шагах завершена, теперь можно считать данные для наших моделей атрибуции. С LastClick все просто, соединяем данные по трафику с данными по заявкам: После преобразований, получаем данные по обращениям и заказам в разрезе источника трафика:Данные для кастомной модели атрибуции
С кастомной моделью атрибуции все немного сложнее. За основу берутся все те же данные что и для модели атрибуции LastClick: Сортируем наши записи по дате по возрастанию: Затем добавляем индексный столбец (это нужно для определения первого и последнего источника): и группируем все строки по cid: Затем делаем дубль данного столбца и раскрываем его обратно: На выходе мы получаем заготовку, по которой уже можем считать нашу модель атрибуции. Для примера мы взяли следующую модель:- Первый источник получает 40% веса
- Последний источник получает 40% веса
- Между источниками в середине остальные 20% разделяются поровну
Спасибо за материал, но ссылка на файл в Яндексе пустая.