Автоматизировали сбор данных с помощью сквозной аналитики

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

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

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

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



Для этого нам требовалось объединить данные, получаемые из рекламных, аналитических и внутренних систем (CRM) клиента. На первый взгляд это кажется легкой задачей, ведь все они предоставляют полную выгрузку данных. Но все не так просто. Данные имели разный формат, некоторые системы не подразумевали интеграции между собой, поэтому работа над созданием сквозной аналитики превратилась в разработку инструментов, которые соединили бы все то, что изначально не подразумевало этого.

То, что требовалось объединить:

• RetailCRM (транзакции реальные, оборот реальный, маржа)
• Google Analytics (транзакции входящие, оборот входящий)
• Рекламные каналы (Яндекс.Директ, myTarget, VK, Яндекс.Маркет, Google Ads)

В качестве системы, которая будет агрегировать данные из разных источников и на которой будут создаваться дашборды (визуальное оформление данных), мы выбрали Microsoft Power BI. Она недорогая, имеет множество функций для обработки данных и их нормализации (приведения к общему виду), позволяет создавать визуальные связи между таблицами, то есть логику того, как данные будут между собой взаимодействовать.





Мы начали процесс реализации задачи с получения данных из CRM клиента. Был написан скрипт, который в два клика позволял выкачивать по API следующую информацию:

• дата транзакции;
• id транзакции;
• наименование товара;
• доход с транзакции;
• данные о клиенте (телефон, email, предыдущие заказы).

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

Однако тут встал вопрос безопасности данных покупателей (телефоны, email) – Адамас был против загрузки в рекламные сервисы незашифрованных данных. И поэтому сделали еще один сервис, который пополнил копилку собственных разработок агентства, – инструмент хеширования данных. Он позволяет шифровать данные различными методами для их безопасной передачи в рекламные системы и не требует при использовании навыков программирования. Теперь любой аккаунт-менеджер или клиент нашего агентства самостоятельно может выполнить эту операцию.


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



Итого: мы получали данные из CRM обо всех заказах и их маржинальности, данные о клиентах для RFM-анализа, а также данные из Google Analytics. Их можно было сопоставить, понять, состоялась ли сделка, и если да, то сколько денег было заработано на ней.

Однако нам не хватало данных о расходах и транзакциях тех рекламных каналов, которые не были связаны с Google Analytics – Яндекс.Директ, Яндекс.Маркет, myTarget и VK.


Изначально мы хотели использовать для этой задачи функционал внешнего оптимизатора контекстной рекламы К50. Но поскольку К50 работает не со всеми рекламными системами, мы разработали собственные коннекторы для Яндекс.Маркета, myTarget, VK, которые работали аналогично коннектору к Google Analytics (Шаг 3): клиент предоставлял доступ к аккаунтам, и мы по API выгружали все данные из рекламной системы и складывали в наши базы данных.

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


Использование определенной структуры utm-меток для каждого товара, размещенного в Яндекс.Маркете, является важным условием сбора качественной аналитики. Благодаря меткам мы понимаем, какой товар привел клиента на сайт, и что потом произошло. Нередко компании используют фид Яндекс.Маркета при запуске PLA кампаний в Google Ads, и тогда трафик, идущий на сайт, распознается как трафик с Я.Маркета. А это неверно. Поэтому мы создали UTM replace, который позволяет использовать базовый фид и в режиме реального времени добавлять в него необходимые метки.



Так мы начали получать детально размеченный трафик из Яндекс.Маркета. А после того, как Яндекс.Маркет отменил CPA, то весь канал стал 100 % отслеживаемым.

Здесь нас ждал подвох. В статистике, идущей от Яндекс.Маркета, мы обнаружили волнообразные изменения трафика и конверсии на сайте: в какие-то дни трафик резко проседал и тащил за собой коэффициент конверсии, а в другие резко рос. Все это не соответствовало нашим прогнозам, и мы начали искать причину. Их могло быть множество: от неверно переданной цены в фид до демпинга цен со стороны конкурента. Но одна из причин нам казалось наиболее вероятной: трафик реагировал на ежедневное колебания ассортимента в фиде – товары раскупали, их становилось меньше в фиде, и соответственно меньше предложений, которые уводили бы с Яндекс.Маркета на сайт. И наоборот.

Чтобы проверять эту гипотезу всякий раз, нам надо было бы заходить в аккаунт Яндекс.Маркета каждый день. И мы решили оптимизировать этот процесс, создав еще один дополнительный сервис, который проверял фид клиента и информировал нас о том, что же случилось с товарами в нем: какие новые товары в него пришли, а какие ушли. Информация сразу поступала в Power BI, визуализировалась с остальными данными, мы уже точно знали, с чем связаны всплески и падения трафика.


Итак, все данные начали поступать в Microsoft Power BI. Поскольку обработка данных происходит непосредственно в PowerBI, было необходимо, чтобы они поступали в едином формате. Например, одна система фиксировала дату через точку: 01.06.2019, другая через слэш: 01/06/2019, третья через дефис: 01-06-2019. Это человеку понятно, что речь идет об одной и той же дате – 1 июня 2019 года – а для машины это три разных значения. Поэтому было необходимо применить переименование, нормализацию, фильтрацию и т.п., что мы и сделали непосредственно в интерфейсе Power BI.



После того, как мы нормализовали все данные, настал черед объединять таблицы из разных источников между собой. Здесь помогла опция PowerBI - визуализация связей таблиц:



Одна и та же кампания в разных рекламных системах имеет разные названия. Чтобы связать эти кампании и их результаты, мы создали промежуточные таблицы-словари, которые производили необходимый поиск во всех таблицах с данными и стандартизировали названия.

В дальнейшем данные из словаря были импортированы в таблицу с данными по Google Analytics с помощью нескольких запросов:

= Table.NestedJoin(#"Replaced Value",{"ID Кампании"},#"id for analytics",{"ID Кампании"},"NewColumn",JoinKind.LeftOuter)

= Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Кампания"}, {"Кампания"})

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

• эффективность рекламных каналов;
• сравнение оформленных и выкупленных заказов;
• отчеты с детальной сегментацией на внутренние категории (собственные списки площадок, география и типы кампаний);
• отчеты с сравнением большого количества параметров, а также вычисление собственных уникальных параметров.

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

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