Кухня IT-Agency. Как устроена отчётность изнутри

Вторая часть рассказа про отчётность в агентстве — о её внутреннем устройстве. Первая часть была про принципы отчётности.

Каркас отчётности

Вся статистика проектов в агентстве хранится в базах данных (БД). Базы хранятся в облаке Microsoft Azure. В БД данные попадают из рекламных систем, Google Analytics и CRM клиента. Из базы они загружается в сводные таблицы в Эксель или на отдельный сервер онлайн-отчётов для быстрого доступа клиентов к данным.

Сводные таблицы в Эксель — основной рабочий инструмент команды проекта (отчёт по эффективности), который создаёт джедай и по которому клиент оценивает нашу работу. Хранится в Dropbox.

Источники данных и способы импорта

Мы загружаем данные в БД двумя способами: автоматически через API или вручную эксельками. Данные грузим разные:

  • Бюджеты, показы и клики забираем из рекламных площадок через API, Google Analytics или эксельки;
  • Данные по остальным интернет-каналам (SEO, type-in, рефералы) и конверсии — из Google.Analytics;
  • Бюджеты по оффлайн-источникам (реклама в метро, на радио и т.п.) — из выгрузок от клиента;
  • Звонки — через выгрузки клиента, системы колл-трекинга или автоматически из CRM;
  • Лиды, счета, оплаты и другие этапы воронки — из выгрузок клиента или автоматически из CRM.

Данные загружаем в базу с помощью самописной программы Элеонора. Она работает в двух вариантах:

  • серверная часть забирает данные с площадок автоматически раз в день: статистика Яндекс.Директ, Google AdWords, не-рекламный интернет-трафик, цели из Google Analytics;
  • клиентская часть устанавливается джедаем на компьютер и позволяет импортировать выгрузки в xml- или xls- форматах от клиента и с площадок, у которых нет API.

Одно из окон клиентской части Элеоноры

Ежедневно после автоматического импорта команде проекта приходит письмо: все ли данные загрузила Элеонора, сколько осталось денег на рекламных аккаунтах, все ли метки в объявлениях верные.

Из чего состоит БД

Начальная сущность БД — атом, к нему привязываются все данные. Атом — мельчайшая единица, статистику по которой мы видим в отчётности. Как правило, это кампания.

Атом состоит из комбинации Город + Источник + Рекламная кампания + Сегмент. На практике это выглядит так:

рекламная кампания = Москва + Яндекс Поиск + Целевые + utm_campaign=msk_y_celevie_poisk

Сама база данных — это система таблиц. В наших БД есть таблицы с данными, таблицы-справочники и таблицы с правилами.

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

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

В таблицах с правилами записаны правила, которые объясняют Элеоноре, какую статистику брать и к чему её привязывать. Правила формируются с помощью динамических сегментов, которые Элеонора накидывает на общий массив данных в Google Analytics, а потом привязывает данные к нужному атому.

Пример связей между таблицами в базе (кликабельно)

Устройство БД на примере статистики поисковой кампании в AdWords по брендам конкурентов в Екатеринбурге

Основная таблица — core.Atoms. В ней записаны все свойства атома — рекламной кампании.

Таблица core.Segments с правилами на основании динамических сегментов объясняет, какую статистику брать и куда её привязывать.

Таблица core.AdEngineCampaigns указывает Элеаноре из какой кампании в интерфейсе AdWords брать данные по расходам:

При помощи таблиц-справочников мы понимаем к какому городу, источнику и кампании относится эта статистика. Вот пример для города — таблица core.Cities:

В итоге статистика по кампании хранится в двух таблицах с данными: из рекламных площадок в таблице core.SourceData и из Google Analytics в таблице core.GaData.

Как данные из БД попадают в отчёт

В БД данные хранятся в виде множества разных таблиц, при этом текстовые данные зашифрованы. Для удобной работы нужно всё расшифровать, соединить в одну таблицу и выгрузить её из базы в Эксель. Это возможно с помощью Views.

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

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

Например, чтобы вывести в отчёт по эффективности данные по количеству целей за определённую дату по конкретной кампании, надо:

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

То есть нужно написать минимум 4 вьюхи, чтобы получить сводную таблицу, с которой можно будет работать в Эксель. Звучит страшно, зато позволяет создавать сквозную отчётность до продаж.

БД и джедаи

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

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

В изучении технической стороны работы баз данных помогает Стэнфордский курс по БД (лекции 1-7, 12), книга Мартина Граббера «Введение в SQL», электронный учебник sql-tutorial.ru и практика в sql-ex.ru.

Ваш комментарий
адрес не будет опубликован

ХТМЛ не работает

Ctrl + Enter
Популярное