новичокCreated using Figmaновичок, спецCreated using Figmaновичок, спецCreated using FigmaэкспертCreated using FigmaспециалистCreated using Figmaспец, экспертCreated using Figma

Как загрузить статистику из рекламных систем в Google BigQuery

В еЛаме мы используем большое количество платных рекламных каналов. Кроме традиционных Яндекс.Директа, Google AdWords, Facebook, «ВКонтакте», MyTarget, мы можем покупать платные посты в партнерских группах во «ВКонтакте» или делать другие платные размещения.

Чтобы корректно анализировать эффективность рекламы, нам необходимо все расходы агрегировать в одной системе и строить отчеты в едином стиле. Казалось бы, можно просто настроить импорт расходов в Google Analytics, используя OWOX BI Pipeline. Но нам этот вариант не подходит по нескольким причинам:

  • в настройке отчетов мы будем ограничены стандартным функционалом Google Analytics;
  • из-за сэмплирования данных в Google Analytics в наших отчетах за период больше 10-14 дней будут показываться неточные значения;
  • для единичных размещений нет возможности указать расход в рамках utm_campaign;

Для сбора несэмплированных данных Google Analytics у нас настроен OWOX BI Streaming в Google BigQuery. Для его настройки нужно установить на сайте дополнительные теги, и данные будут автоматически отправляться с фронтенда сайта на сервера OWOX и BigQuery параллельно с отправкой данных на сервера Google Analytics.

Таким образом, в нашем проекте BigQuery мы получаем данные обо всех посещениях, событиях и конверсиях на сайте. Если мы загрузим туда также расходы на платную рекламу, то сможем анализировать всё в одном месте. О его возможностях и старте работы с ним я рассказывал раньше.

1. Загрузка данных из Google AdWords

В документации Google есть стандартный скрипт для экспорта данных в BigQuery, который нужно загрузить в ваш аккаунт AdWords. Скопируйте его себе в текстовый редактор и измените ряд полей:

  • BIGQUERY_PROJECT_ID — идентификатор проекта в BigQuery, который можно узнать в консоли Google Cloud Platform, например, ‘linear-theater-******’
  • BIGQUERY_DATASET_ID — набор данных, например ‘advertising_systems’;
  • TRUNCATE_EXISTING_TABLES — укажите ‘true’, чтобы перезаписывать данные по расходам;
  • DEFAULT_DATE_RANGE — период, за который будут экспортироваться данные, например ‘20170401,20171231’ (можно указать дату из будущего). Другие допустимые варианты DateRange можно посмотреть в справке. Началом периода я рекомендую указать дату 1-2 месяца назад от текущего дня — в этот период на стороне AdWords еще могут происходить корректировки. А скрипт для более ранних периодов лучше выполнить один раз, а результаты скопировать в отдельную таблицу BigQuery, например, ‘adwords_cache’.
  • REPORTS — содержит список отчетов, которые будут выгружаться. Примеры отчетов, которые используем мы:

//статистика с разбивкой по кампаниям
{NAME: 'CAMPAIGN_PERFORMANCE_REPORT',  
     CONDITIONS: 'WHERE Impressions > 0',
     FIELDS: {'CampaignId' : 'STRING',
              'CampaignName' : 'STRING',
              'Date' : 'STRING',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Cost' : 'FLOAT'
             }
    }

//статистика с разбивкой по поисковым фразам
{NAME: 'KEYWORDS_PERFORMANCE_REPORT',
     CONDITIONS: 'WHERE Impressions > 0 AND AdNetworkType1=SEARCH',
     FIELDS: {'CampaignId' : 'STRING',
              'CampaignName' : 'STRING',
              'Criteria' : 'STRING',
              'Cost' : 'FLOAT',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Date' : 'STRING'
             }
    }

//статистика с разбивкой по объявлениям в КМС (удаленные объявления не попадают в отчет)
{NAME: 'AD_PERFORMANCE_REPORT',
     CONDITIONS: 'WHERE Impressions > 0 AND AdNetworkType1=CONTENT',
     FIELDS: {'CampaignId' : 'STRING',
              'CampaignName' : 'STRING',
              'AdType' : 'STRING',
              'Headline' : 'STRING',
              'HeadlinePart1' : 'STRING',
              'HeadlinePart2' : 'STRING',
              'ImageCreativeName' : 'STRING',
              'Cost' : 'FLOAT',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Date' : 'STRING'
             }
    }

//статистика с разбивкой по удаленным объявлениям в КМС (при внесении изменений в объявление в AdWords система  его удаляет и создает новое)
{NAME: 'AD_PERFORMANCE_REPORT',
     CONDITIONS: 'WHERE Impressions > 0 AND AdNetworkType1=CONTENT AND Status=DISABLED',
     FIELDS: {'CampaignId' : 'STRING',
              'CampaignName' : 'STRING',
              'AdType' : 'STRING',
              'Headline' : 'STRING',
              'HeadlinePart1' : 'STRING',
              'HeadlinePart2' : 'STRING',
              'ImageCreativeName' : 'STRING',
              'Cost' : 'FLOAT',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Date' : 'STRING'
             }
    }

Все доступные отчеты можно посмотреть по этой ссылке. Также есть скрипт для MCC аккаунта.

Если нам нужен только один отчет по кампаниям, то REPORTS будет выглядеть так:

  REPORTS: [{NAME: 'CAMPAIGN_PERFORMANCE_REPORT',
     CONDITIONS: 'WHERE Impressions > 0',
     FIELDS: {'CampaignId' : 'STRING',
              'CampaignName' : 'STRING',
              'Date' : 'STRING',
              'Impressions' : 'INTEGER',
              'Clicks' : 'INTEGER',
              'Cost' : 'FLOAT',
             }
    }],

  • RECIPIENT_EMAILS — email, на который будут приходить уведомления.

Полученный скрипт нужно добавить в аккаунт AdWords -> Массовые операции -> Скрипты.

Задержка обновления статистики в AdWords может составлять до трех часов, поэтому ежедневное выполнение скрипта рекомендуется запланировать на 3 часа утра или позже. Будут доступны данные до вчерашнего дня включительно.

Если все прошло успешно, то после первого выполнения скрипта в BigQuery должна появиться таблица advertising_systems.CAMPAIGN_PERFORMANCE_REPORT, как на скриншоте ниже. Она будет содержать статистику по кампаниям по дням. Следует помнить, что расход в AdWords не включает НДС.

2. Загрузка данных из Яндекс.Директа

Для загрузки данных из Яндекс.Директа мы используем библиотеку ryandexdirect на языке R, разработанную Алексеем Селезневым, Head of Analytics Department at Netpeak. Скрипт выполняется ежедневно на локальном компьютере по расписанию и загружает данные до вчерашнего дня включительно. Итак, порядок действий:

2.1) Установите актуальную версию языка R и R Studio. Инсталлируйте пакеты ’devtools’, ’bitops’, ’ryandexdirect,’bigrquery’’. Подробности в статье Алексея.

2.2) Получите токен для доступа к Директу через R, используя команду yadirGetToken().

2.3) Скрипт на языке R для получения данных из Директа и их записи в BigQuery будет иметь вид:

print(Sys.time())
library(ryandexdirect)
library(bigrquery)
setwd("C:/work/Dropbox/R")

ya_token <- "AQGP_QXXXXXX"
campList <- yadirGetCampaignList(logins = NULL, token = ya_token)
statYa <- yadirGetSummaryStat(campaignIDS = campList$Id, dateStart = "2017-05-01", dateEnd = Sys.Date()-1, currency = "USD", token = ya_token)
insert_upload_job("bigquery_project_id", "advertising_systems", "direct", statYa, "bigquery_project_id", create_disposition = "CREATE_IF_NEEDED", write_disposition = "WRITE_TRUNCATE")

Здесь нужно заменить:

  • ‘C:/work/Dropbox/R’ — рабочая папка R, где будет лежать сам скрипт и служебные файлы R;
  • ‘AQGP_QXXXXXX’ — измените на ваш токен;
  • ‘dateStart’ — дата начала отчетного периода;
  • ‘Sys.Date()-1’ — дата окончания (вчерашний день);
  • ‘bigquery_project_id’ — идентификатор проекта в BigQuery;
  • ‘advertising_systems’ — dataset в BigQuery;
  • ‘direct’ — название таблицы для записи данных в BigQuery.

Примечание: в документации к библиотеке ’ryandexdirect’ указано возможное значение currency = «RUB», но у меня оно не заработало, поэтому мы выгружаем данные в у.е. (1 у.е. = 30 руб).

2.4) Попробуйте выполнить скрипт. Если все прошло успешно, то в BigQuery появится таблица advertising_systems.direct, содержащая следующие поля:

 

Нюанс № 1. Как видите, в передаваемых данных нет названия кампаний. Поэтому в Google Spreadsheets необходимо вести табличку, которая будет содержать два столбца: id кампании в Яндексе и ее название. Эту таблицу нужно держать актуальной в BigQuery (advertising_systems.campaigns_list). Для загрузки документа Google Spreadsheets подойдет OWOX BI BigQuery Reports. Как по id кампании найти ее название в другой таблице можно посмотреть в SQL-скрипте для отчетности из пункта 7.

Нюанс № 2. Поле Date пишется в формате TIMESTAMP в UTC времени, то есть, чтобы получить верную дату по московскому времени, нужно прибавить три часа. В BigQuery для этого можно использовать функцию DATE_ADD (пример использования — в SQL-скрипте для отчетности из пункта 7).

2.5) Теперь нужно настроить запуск R-скрипта по расписанию. У нас он настроен на 4 часа утра и срабатывает при включении компьютера. Через несколько минут в рабочей папке R появляется лог-файл ‘script.r.Rout’, в котором можно проверить, были ли ошибки при выполнении скрипта.

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

3. Загрузка данных из Facebook

С рекламной платформой Facebook будем работать также используя R библиотеку rfacebookstat Алексея Селезнева.

3.1) Будем считать, что у вас установлен R и библиотеки из пункта про Яндекс.Директ. Установите дополнительно пакеты ‘rfacebookstat’ и ‘zoo’.

3.2) Создайте приложение в Facebook, как написано в статье Алексея. Получите краткосрочный токен для работы, затем измените его на долгосрочный.

3.3) Скрипт для выгрузки расходов по дням будет иметь вид:

print(Sys.time())
library(rfacebookstat)
library(bigrquery)
library(zoo)
setwd("C:/work/Dropbox/R")

fb_token <- "EAAYXXXXXX"
statFb <- data.frame()
for (i in as.Date("2017-05-01"):(Sys.Date()-1))
{
   dt <- format(as.Date(i), "%Y-%m-%d")
   temp <- fbGetMarketingStat(accounts_id = "act_103503XXXXXXX",
      sorting = NULL, level = "campaign", breakdowns = NULL,
      fields = "campaign_id,campaign_name,impressions,inline_link_clicks,spend",
      filtering = NULL, date_start = dt, dt, api_version = "v2.8", fb_token)
   statFb <- rbind(statFb, temp)
}
statFb$campaign_name = iconv(statFb$campaign_name, "", "UTF-8") 
insert_upload_job("bigquery_project_id", "advertising_systems", "facebook", statFb, "bigquery_project_id", create_disposition = "CREATE_IF_NEEDED", write_disposition = "WRITE_TRUNCATE")

Здесь нужно заменить:

  • ‘C:/work/Dropbox/R’ — рабочая папка R, где будет лежать сам скрипт и служебные файлы R;
  • ‘EAAYXXXXXX’ — ваш долгосрочный токен;
  • ‘2017-05-01’ — дата начала;
  • ‘Sys.Date()-1’ — дата окончания (вчерашний день);
  • ‘act_103503XXXXXXX’ — ID рекламного аккаунта в Facebook;
  • ‘bigquery_project_id’ — идентификатор проекта в BigQuery;
  • ‘advertising_systems’ — dataset в BigQuery;
  • ‘facebook’ — название таблицы для записи данных в BigQuery;
  • ‘fields’ — список передаваемых полей. Актуальный список всех доступных полей можно посмотреть в официальной документации к API.

3.4) После выполнения скрипта в BigQuery должна появиться таблица advertising_systems.facebook, содержащая статистику до вчерашнего дня включительно. Следует помнить, что за большие периоды (например, полгода) Facebook не даст выгрузить информацию из-за превышения допустимого числа обращений к API. В таких случаях статистику следует хранить в нескольких таблицах BigQuery.

3.5) Теперь нужно настроить запуск R скрипта по расписанию, как описано в пункте 2.5. Скрипты по выгрузке из Яндекс.Директа и Facebook можно объединить в один.

4. Загрузка данных из ВКонтакте

У Алексея Селезнева есть R-библиотека rvkstat, но она выгружает только данные о посетителях сообществ «ВКонтакте» по дням, в разрезе возрастных групп, пола и геолокации посетителей. Нам же нужна статистика из рекламного кабинета. Мы будем выгружать ее вручную через экспорт статистики в CSV-файл. «ВКонтакте» отдает информацию в кодировке ‘cp-1251’. Если в названиях ваших кампаний используется кириллица, не забудьте перекодировать файл в текстовом редакторе в UTF-8.

 

Выгрузку мы делаем каждый понедельник за прошлую неделю и дописываем новые данные вручную в общий файл vk.csv, содержащий статистику за все периоды в следующем виде:

Затем вручную загружаем файл vk.csv в BigQuery в таблицу advertising_systems.vk. Способы загрузки данных описаны в вводной статье про BigQuery.

5. Загрузка данных из MyTarget

Алексей Селезнев подготовил R-библиотеку rmytarget, аналогичную ryandexdirect и rfacebookstat, которая помогает получить список клиентов агентств из аккаунта MyTarget, список и общие параметры рекламных кампаний по каждому из проектов, а также детальную статистику по кампаниям за каждый день. Не будем занимать место в статье переписыванием справки, в ней всё описано достаточно понятно.

Стоит добавить, мы не используем автоматический экспорт из MyTarget по двум причинам:

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

Кроме того, чем сложней система, тем больше ошибок, поэтому расходы из MyTarget мы указываем вручную — об этом ниже.

6. Единичные размещения

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

  • utm_channel — рекламный канал;
  • name — название кампании;
  • month — месяц;
  • impressions — количество показов в этой кампании за отчетный период (если неизвестно — 0);
  • clicks — количество кликов аналогично показам;
  • cost — стоимость размещения.

Для загрузки данных в BigQuery будем использовать бесплатный плагин для браузера OWOX BI BigQuery Reports. Название таблицы — advertising_systems.manually.

7. Построение отчетов

Итак, в BigQuery есть пять таблиц, содержащих расходы на платные каналы трафика (для простоты не будем учитывать возможные таблицы за прошлые периоды):

  • advertising_systems.CAMPAIGN_PERFORMANCE_REPORT (AdWords),
  • advertising_systems.direct,
  • advertising_systems.facebook,
  • advertising_systems.vk,
  • advertising_systems.manually.

Для анализа, у вас в BigQuery должны быть загружены данные о конверсиях пользователей, возможно их платежи, звонки и любая другая необходимая информация. Мы возьмем простейший случай, и посчитаем регистрации на сайте (событие Google Analytics), которые передаются с помощью OWOX BI Streaming в Google BigQuery.

Чтобы сеансы и конверсии в Google Analytics соотнеслись с кампаниями в статистике расходов, рекламные ссылки должны быть размечены UTM-метками (кроме объявлений в AdWords, где используется автопометка). Название кампании в рекламной системе должно совпадать с utm_campaign в ссылке. Для Яндекс.Директа можно использовать автоматическую UTM-разметку в еЛаме. В Facebook, «ВКонтакте», MyTarget и любых нерегулярных размещениях utm-метки прописываем вручную в рекламных системах.

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

Фрагмент SQL-запроса для формирования отчета по платным каналам:

 

SQL-запрос можно выполнять прямо в BigQuery — отчет будет в виде таблицы. Или же строить отчет в Google Spreadsheets с помощью OWOX BI BigQuery Reports (он будет выполнятся по запросу с сервера OWOX), причем отчет можно поставить на ежедневное обновление по расписанию.

Фрагмент полученного из BigQuery отчета по анализу платных каналов:

Обычно мы создаем дополнительный лист-отчет в Google Spreadsheets, на котором располагаем элементы в необходимой последовательности, а данные являются ссылками на лист-таблицу с результатом выполнения SQL-запроса из BigQuery. Для визуализации можно воспользоваться стандартными диаграммами в Google Spreadsheets, Google DataStudio, Redash или другими инструментами.

Заключение

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

Недостаток описанных методов заключается в том, что статистика собирается на уровне кампании. Поэтому для детального анализа групп объявлений за небольшие промежутки времени мы используем импорт расходов в Google Analytics OWOX BI Pipeline и стандартные отчеты аналитической системы.