Как записать сырые данные из Я.Метрики в ClickHouse и на основании них построить многоканальную последовательность по источнику и рекламным кампаниям
В этой статье мы разберем, как использовать Logs API Яндекс Метрики для построения многоканальной последовательности на основании источника и UTMCampaing, а так же как записывать и хранить данные в ClickHouse.
Привет!
Эту статью пишет Александр Завьялов, руководитель отдела аналитики в Медиа 108.
Сегодня мы научимся записывать сырые данные из Я.Метрики в ClickHouse и строить многоканальную последовательность по источнику и рекламным кампаниям.
После начала специальной военной операции многие компании столкнулись с ограничением стандартных ресурсов, таких как Google BigQuery и Universal Analytics и вынужденно перешли на другие системы аналитики и хранения данных. В качестве новой системы аналитики многие предпочитают Я.Метрику, но в ней нет того функционала, за который многие рекламщики любили Analytics, а это легко компенсируется с помощью Logs API Яндекс и хранение в своей Базе данных. В нашем примере это будет разработка от Яндекс — ClickHouse. На основании Logs API Яндекс можно строить разнообразные воронки, составление сложных целей и тп.
В этой статье мы разберем, как использовать Logs API Яндекс Метрики для построения многоканальной последовательности на основании источника и UTMCampaing, а так же как записывать и хранить данные в ClickHouse.
Кому подойдет данная статья? Тем, кто начинает агрегировать все возможные данные на своей стороне и хочет проанализировать не только первое и последнее касание в рекламе, но и проследить всю цепочку взаимодействия с рекламой.
Часть 1. Подготовка базы и запись данных из Я.Метрики в ClickHouse
Для записи и формирования базы мы используем Python. Все функции можно разделить на 3 этапа: работа с базой данных, подготовка данных для записи и вспомогательные.
Ниже мы пошагово покажем, как их использовать, и получим 2 таблицы.
Таблица Daily — данные обновляются каждый день за последние 32 дня (31 день записывается в таблицу, что позволяет всегда иметь актуальную информацию, 32 день записывается в таблицу History;
Таблица History — хранит в себе все предыдущие даты и постоянно дописывает 32 день из Daily.
Данная схема может показаться излишней, но лишь на первый взгляд. Мы взяли за основу уже созданный нами метод записи данных из директа, где постоянно происходят корректировки, и данные на начало и конец месяца всегда различаются, что очень ощутимо на больших бюджетах. Такую же механику мы используем в коллтрекингах и CRM, где постоянно ведётся работа с тэгами, различие лишь в том, что период Daily увеличен.
Работа с базой данных
Шаг 1. Подключение к базе данных происходит с помощью библиотеки «infi.clickhouse_orm».
Для подключение необходимо создать экземпляр класса Database, передаем в него имя таблицы, хост, имя пользователя и пароль:
Шаг 2. Вставка в базу данных.
— Происходит подключение к базе данных;
— Создается таблица на основании переданной модели;
— Выполняется миграция;
— Происходят попытки вставить данные в базу. Если попытка неудачная, программа засыпает на 10 секунд, после чего пытается записать по новой:
Шаг 3. Получение учетных данных (credentials) для API-коннекторов из модели api_connector_model.
Подготовка данных для записи
Шаг 1. Определяем основные параметры подключения и временной интервал для выгрузки данных.
Шаг 2. Подключаемся к базе данных и создаем таблицу с созданными моделями и выполняем миграцию и очищаем таблицу.
Шаг 3. Получаем токены с помощью кастомной модели ApiConnector и создаем списки для дальнейшего сохранения данных.
Шаг 4. Проходимся по первым токенам.
— Получаем метки levels и параметры для дальнейших запросов;
— Получаем список счетчиков для данного токена;
— Для каждого счетчика добавляется параметр токен;
Шаг 5. Формируем дата фрейм для дальнейшей записи.
— Создаем пул потоков;
— Объединяем данные переданные во вспомогательную функцию;
— Внутри вспомогательной функции формируем дата фрейм и сохраняем данные в базу данных.
Шаг 6. Сохраняем данные в json.
Вспомогательные функции
Функция для получении данных из Яндекс.Метрики
Функция для подготовки данных из дата фрейма и дальнейшей записи в ClickHouse
Функции для работы с яндекс метриками:
Получение данных из Яндекс.Метрики
Создание задачи, которая вернет данные из Яндекс.Метрики
Вы можете оценить возможность выгрузки данных с помощью запроса GET , если не уверены, что его удастся выгрузить.
2. После успешного выполнения запроса на создание логов, сохраните request_id и дождитесь подготовки лога. Узнать статус обработки лога можно с помощью запроса GET
3. Лог со статусом processed готов к выгрузке. Для выгрузки используйте запрос GET
4. После выгрузки очистите подготовленные для загрузки логи, чтобы освободить место для следующих запросов. Для этого выполните запрос DELETE
Нагрузка на сервис в разное время может различаться, если вам не удалось выгрузить данные, попробуйте позже или в другое время суток. Все запросы выполняются по очереди — каждый новый запрос попадает в конец очереди. Если ваш запрос находится в статусе created, дождитесь его выполнения.
Часть 2. Построение многоканальной последовательности
Для построения отчёта можно использовать различные программы визуализации.В нашем примере мы используем Power Query от Microsoft, которая доступна в программах Power Bi и Excel (данная настройка встроена с версии 2016 года).
Данные из ClickHouse можно забирать через ODBC или через WEB с помощью SQL запросов. В нашем примере мы будем использовать WEB метод.
Финальное количество таблиц/параметров для Power Query будет выглядеть так:
МгК последовательность – функция преобразующая данные в многоканальную последовательность;otkazi – параметр 0 или 1, где «0» учитывает все визиты, «1» учитывает только неотказные визиты;idSchetchik – указываем номер счётчика;DateTime_Start и DateTime_End – период анализа;количество каналов – вспомогательная таблица для удобства подсчета каналов, участвующих в последовательности;Метрика – финальная таблица с данными;Source, Campaing и GoalID – таблицы для фильтров в Power BI, для Excel они необязательные.
Шаг 1. Составляем SQL запрос для агрегирования информации из ClickHouse
Ниже пример кода. В параметр counterID вставляем номер счётчика метрики.
Закрепим: что нужно, чтобы записать сырые данные из Я.Метрики в ClickHouse и на основании них построить многоканальную последовательность по источнику и рекламным кампаниям.
Подготовить базу;
Забрать данные из Я.Метрики и записать в ClickHouse;
Получить 2 таблицы: Daily и History;
Преобразовать данные из таблиц в Power query;
Получить многоканальную последовательность;
Визуализировать результат в Excel или Power BI;
Надеемся, эта статья поможет вам оценить долю влияния каждого рекламного канала на достижение целевой конверсии, а также проанализировать рекламные компании, которые не приносят прямых конверсий, но являются вспомогательным элементом в достижении целевого действия.
How to
Просто о сложном
Новости 
Кейсы 
Интервью и дискуссии 
Маркетинговые исследования 
Проверено ADPASS 
Рекламные кампании