Как отследить эффективность рекламных кампаний в привязке к продажам конкретного товара и производителя?

Если нет времени читать статью, ниже краткая версия кейса.
Далее по тексту — расширенная.

Как известно в большинстве случаев, до совершения покупки клиент совершает более одного касания с вашим сайтом.

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

Однако есть ситуации, когда бизнесу интересно какие каналы эффективны при продаже определенных брендов и товаров.

Настроив расширенную электронную торговлю можно увидеть какой бренд или товар покупают, и какой источник трафика является последним на пути к покупке некоторого количества этого товара.

План решения задачи был следующий

ШАГ 1. Мы определили какие метрики и показатели нам необходимы для решения бизнес задач клиента.

ШАГ 2. Определили какие необходимые показатели и метрики связаны между собой.

ШАГ 3. Стали искать пути получения и объединения необходимых нам данных.

ШАГ4. Визуализировали в удобный дашборд полученную информацию.

Теперь остановимся на каждом шаге отдельно и подробно расскажем что сделали.

Определяем какие метрики и показатели нам необходимы для решения бизнес задач клиента. И как они связаны между собой.

Для реализации первого и второго шага мы:

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

Поиск путей получения и объединения необходимых нам данных.

Как известно, Google Analytics имеет API для работы с вашими данными без использования веб-интерфейса сервиса.

Google предоставляет 6 разновидностей API для работы с различными типами данных

Для решения нашей задачи нас интересует 2 вида API:

1. Core Reporting API — позволяет получить параметры и показатели для создания отчетов. С помощью него можно получить данные об Электронной торговле, поведении пользователе, трафике и еще много другой информации. НО нельзя получить информацию о многоканальных последовательностях.
2. Multi-Channel Funnels Reporting API — как раз позволяет получить данные для построения отчетов по многоканальным последовательностям для анализа сложных путей к транзакциям и конверсиям.

Один из простых способов для НЕ программистов — получать данные при помощи API Google Analytics, использовать дополнение к Google таблицам Google Analytics Spreadsheet Add-on. Дополнение как раз дает возможность получать данные от интересующих нас API

О том как использовать Google Analytics Spreadsheet Add-on в разрезе Core Reporting API написано много хороших статей, а о использовании Multi-Channel Funnels Reporting API вскоре появится наша статья. Потому в этой статье мы не заостряем внимание на том, как работать с дополнением.

Вернемся к нашей задаче.

При помощи трех запросов получили необходимые нам данные. Затем, используя функции Google Таблиц соединяем данные воедино.

Ниже приведено краткое описание параметров конфигурации, которые мы использовали для решения нашей задачи:

Report Name - название отчет и листа, куда будет сохранена информация.
View ID - идентификатор представления из которого будет браться информация.
Start Date и End Date - дата начала и конца периода за который необходимо получить данные.
Metrics - показатели, которые необходимо выгрузить.
Dimensions - параметры, которые необходимо выгрузить
Filters - фильтр данных.
Limit - максимальное количество строк в выгрузке (есть ограничения).
Report Type - тип API, которое используется в запросе. Может иметь два типа core и mcf. Если поле пустое, то по умолчанию используется core.

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

При этом выгружая данные о последнем непрямом клике при помощи Core Reporting API последнее непрямое касание отобразится верно, даже если оно было совершено более чем 30 дней до конверсии.

Потому в некоторых транзакциях в выгрузке 'last non-direct con’ есть источники, отличные от прямых, а в выгрузке ‘MFC’ эта же конверсия будет иметь только прямые переходы. Но более частая ситуация, когда в выгрузке 'last non-direct con’ есть источник/канал как последний непрямой, а в выгрузке ‘MFC’ он либо ассоциированный, либо первый либо последний. Чтобы не потерять источники нам необходимо выбрать все уникальные пары Идентификатор транзакции — источник или канал из двух таблиц ‘MFC’ и 'last non-direct con’

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

=UNIQUE(QUERY({'last non-direct con'!B16:C;MFC!B16:C}; "Select * where Col2!=''"))

• UNIQUE() — функция, которая возвращает уникальные строки в указанном диапазоне, убирая дубликаты.
• QUERY(данные; запрос) — функция Выполняет запросы на базе языка запросов API визуализации Google. Это SQL подобный язык.
• {Источник 1;Источник 2} — данные для запроса из нескольких источников.
• "Select * where Col2!=''" — запрос, который выбирает все столбцы данных при условии, что стрики.

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

Рассмотрим как пример полученные значения для последнего непрямого клика. Для этого используем формулы которая делает следующее:

Для ячейки С2: Данная формула для всех непустых строк проверяет есть ли совпадение пары параметров в выгрузке 'last non-direct con’. Если совпадение есть, то нужно добавить число транзакций по последнем непрямому клику для данной пары параметров, если нет — записать ноль.

Вид формулы

=ARRAYFORMULA(ЕСЛИ(A2:A<>"";ЕСЛИОШИБКА(ARRAYFORMULA(ВПР(A2:A&B2:B;{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D};2;0));0);""))

• ARRAYFORMULA() — Благодаря ей можно использовать использовать массивы в функциях, которые не предназначенных для этого.
• ЕСЛИ(источник; значение_при_соблюдении_условия; значение_при_несоблюдении_условия) — позволяет задать условие, когда использовать формулу
• ЕСЛИОШИБКА(значение; [значение_при_ошибке]) — формула для обработки ошибок
• ВПР(запрос; диапазон; индекс; [отсортировано]) — производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.

Рассмотрим детальнее часть формулы

ARRAYFORMULA(ВПР(A2:A&B2:B;{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D};2;0))

• Так как функиця ВПР может искать только по одному значению, то объединяем в формуле параметры в финальной и исходной таблице в один:

A2:A&B2:B
'last non-direct con'!B:B&'last non-direct con'!C:C

• Из таблицы нам нужно вернуть значения столбца D при выполнении заданных условий. Для этого создаем массив данных, сгруппировав в нем получившейся объединенный параметр со столбцом из таблицы:

{'last non-direct con'!B:B&'last non-direct con'!C:C\'last non-direct con'!D:D}

• 2 - указывает какой столбец из группы возвращать при совпадении.
• 0 - указывает на то, что совпадение параметров должно быть точным

Аналогично для других значений.

В результате получаем следующую таблицу:

Далее при помощи скрипта объединяем транзакцию с ее товарами и их параметрами и сохраняем в архив. На примере транзакции 100602 в результате работы скрипта получим следующее:

Финальная таблица имеет довольно странный вид, так как предназначена для визуализации в Google Data Studio.

Последним шагом при настройке сбора данных для отчета является установка таймеров на выгрузку данных из Google Analytics и скрипт.

Финальный шаг — визуализация данных в Google Data Studio.

При визуализации необходимо быть внимательным при выборе типа агрегации.

Так, например, для графика “число транзакций за выбранный период”. Идентификатор транзакции берется как показатель и считается его количество без учета повторов, так как идентификатор транзакции является уникальным.

Кто-то может спросить, почему нельзя использовать совмещенные данные в Data Studio?

Совмещение данных в Google Data Studio появилось относительно недавно и все еще имеет ряд ограничений в работе сервиса. Например, не всегда удобно использовать совмещенные данные, по той причине, что технически, совмещенный источник данных является продуктом операции left outer join (Она знакома тем, кто работает с SQL). К тому же нашей команде было быстрее и проще написать скрипт.

Но, если у вас нет опыта написания скриптов, то вы можете создать совмещенные данные в Data Studio. Он позволит устранить необходимость сначала управлять вашими данными, экономя ваше время и усилия.

Ну и в завершение, расскажем о плюсах и минусах нашей настройки.

Плюсы

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

Минусы

• Такая реализация подходит для бизнеса у которого период принятия решения менее 30 дней или они готовы пожертвовать данными и статистикой за более длительный период.
• В Google Таблицах можно хранить до 5 миллионов ячеек в одном документе, потому вам необходимо рассчитать как надолго вам хватит таблицы для хранения данных. Возможно на года, а возможно и на месяца.

Как видим, при помощи Google Sheets, Google App Script, Google Analytics Spreadsheet Add-on, Google Data Studio можно создавать базу для решения нетривиальных задач.