Как оценить потерянный доход в Google Ads с помощью языка R

Сколько прибыли вы не получили в Google Ads из-за нехватки бюджета или низкого рейтинга ключевых слов? Стандартные отчеты не позволяют это проанализировать, но я знаю способ, как подсчитать эти данные с помощью языка программирования R.

В октябре 2015-го года в блоге я описывал алгоритм для определения количества потерянных конверсий с помощью Supermetrics (специального дополнения к Google Таблицам). Тогда бесплатного базового функционала Supermetrics было достаточно, но со второй половины февраля 2016 года дополнение стало платным. Скорее всего, далеко не каждому захочется отдавать $99 в месяц за отчёт по потерянным конверсиям, поэтому я нашел другой способ реализации данного алгоритма.

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

Данное решение реализовано на языке R, он предназначен для статистической обработки данных и визуализации информации. Язык R разработан более 20 лет назад непосредственно для академических исследований. Как большинство бесплатных продуктов, R очень быстро стал популярным, в связи с чем к нему было написано множество пакетов, значительно расширяющих его базовый функционал. В этой статье мы рассмотрим ряд пакетов, включая пакеты предназначенные для работы с API Google Ads и Google Analytics.

Что необходимо для реализации алгоритма?

1. Установленный на сайте код отслеживания электронной коммерции, и соответственно в Google Analytics должны отслеживаться данные о совершенных транзакциях.
2. Аккаунт Google Analytics, связаный с Google Ads.
3. На ПК должен быть установлен язык R, для этого пройдите по ссылке, скачайте и установите последнюю версию (видео урок).
4. Для более удобной работы в R предлагаю также скачать и установить RStudio, именно в этой среде программирования мы запустим скрипт.
5. Developer Client в консоли разработчика Google, более подробно об этом я расскажу далее.
6. Токен разработчика Google Ads для доступа к API Google Ads, но это условие не является обязательным.

Если перечисленные пункты вас не смущают, можем двигаться дальше.

Как создать Developer Client в консоли разработчика Google?

В первую очередь нам необходимо зарегистрировать приложение в Google Cloud Console. Для этого выполните описанные ниже шаги:

1. Откройте Google Cloud Console.
2. Создайте новый проект, если у вас его ещё нет.

3. В основном меню перейдите в раздел APIs & Services — Oauth consent screen.

4. Заполните все необходимые поля, и перейдите в меню Credentials — Create credentials — OAuth client ID.

5. Из выпадающего меню выбираем Desktop app, вводим название приложение и жмём «Create».

6. На этом настройка приложения закончена жмём «Оk».

7. Для удобства созданное приложение можно сохранить на ПК, название файла при сохранении может быть произвольным, допустим что мы сохранили его с именем app.json по ардесу C:/auth.

8. Последнем шагом настройки проекта в Google Cloud необходимо включить Google Ads API, переходим в раздел library.

9. В поиске пишем Google Ads.

10. Включаем в проекте Google Ads API.

Далее активируйте все API связанные с Google Analytics.

Мы настроили проект в Google Cloud, теперь необходимо запросить токен разработчика Google Ads.

В учебных целях в пакет rgoogleads уже включены все необходимые компоненты: токен разработчика, client id, secret id. Тем не менее я настоятельно рекомендую вам получить собственные учётные данные. 

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

1. Создайте новый управляющий аккаунт, если у вас его нет. 
2. Перейдите в Инструменты и настройки — Настройки — Центр API.
3. Заполните сведения о разработчике.
4. Нажмите «Подать заявку на базовый доступ». 

5. Заполните и отправьте анкету.

Далее скорее всего вам потребуется в переписке объяснить поддержке Google Ads API для каких целей вы будете использовать API, после чего вы получите базовый доступ.

В статье мы будем работать с Google Ads API и Google Analytics API. Подробно рассматривать их мы не будем, но для более глубокого погружения в работу с Google Analytics API вы можете прочесть статью «Как загрузить данные из API Google Analytics в R: часть 2» или посмотреть видео доклада «Как работать с API Google Analytics на языке R с помощью пакета googleAnalyticsR».

Запуск скрипта в RStudio

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

Подключаем необходимые пакеты R

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

Ниже я приведу код установки и подключения нужных нам пакетов.

# установка пакетов
install.packages(c('rgoogleads', 'googleAnalyticsR', 'dplyr', 'tidyr', 'ggplot2'))

# подключение пакетов
library(rgoogleads)
library(googleAnalyticsR)
library(dplyr)
library(tidyr)
library(ggplot2)

Рассмотрим назначение установленных нами пакетов:

• rgoogleads — пакет для работы с Google Ads API;
• googleAnalyticsR — пакет для работы с Google Analytics API;
• dplyr — пакет для манипуляции табличными данными в R;
• tidyr — пакет для очистки и преобразования данных;
• ggplot2 — пакет для визуализации данных.

Авторизация в сервисах

Работа практически с любым API начинается с авторизации. Нам для работы изначально надо авторизоваться в Google Ads и Google Analytics. Используемые нами пакеты включают гибкий и удобный функционал для авторизации.

В этой статье мы рассмотрим самый простой способ авторизации — авторизацию с дефолтными параметрами через вшитые в пакеты учётные данные.

# авторизация
## google ads
gads_auth(email = 'me@gmail.com')
## google analytics
ga_auth(email = 'me@gmail.com')

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

При успешной авторизации вы увидите в браузере сообщение «Authentication complete. Please close this page and return to R.». Увидев это сообщение возвращайтесь в RStudio и продолжайте работу.

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

Если вы хотите пройти авторизацию в Google Ads API используя собственные учётные данные: OAuth клиент и токен разработчика почитайте документацию к функции ?gads_auth_configure().

Для использования продвинутой настройки авторизации в Google Analytics API почитайте документацию.

Загрузка данных из Google Ads и Google Analytics

Пройдя авторизацию мы можем приступить к основной части работы — загрузке и постобработке данных из API.

# аккаунты
## управляющий аккаунт
gads_set_login_customer_id('xxx-xxx-xxxx')
## рекламный аккаунт
gads_set_customer_id('xxx-xxx-xxxx')

# запрос данных из Google Ads
ads_data <- gads_get_report(
  resource = 'campaign',
  fields = c('campaign.id',
            'campaign.name',
            'metrics.impressions',
            'metrics.clicks',
            'metrics.cost_micros',
            'metrics.ctr',
            'metrics.search_budget_lost_impression_share',
            'metrics.search_rank_lost_impression_share',
            'metrics.content_budget_lost_impression_share',
            'metrics.content_rank_lost_impression_share'),
  where     = 'metrics.impressions > 0',
  during    = 'LAST_30_DAYS'
)

# запрос данных из Google Analytics
ga_data <- google_analytics(
  viewId = 247168223,
  date_range = c('31daysAgo', 'yesterday'),
  dimensions = c("adwordsCampaignID"),
  metrics = c("transactions", "transactionRevenue"),
  filtersExpression = "ga:medium==cpc,ga:source==google"
)

В примере кода замените xxx-xxx-xxxx на идентификаторы вашего управляеющего и рекламного аккаунта.

Представленный пример кода загрузит нужные нам для расчётов данные из Google Ads API и Google Analytics API за последние 30 дней.

Если вы хотите получить данные за какой-то конкретный статичный период, то в функции gads_get_report() используйте аргументы date_from и date_to, передав начальную и конечную дату в формате ГГГГ-ММ-ДД, а в функции google_analytics() передайте даты начала и завершения в том же формате, вместо пресетов '31daysAgo' и 'yesterday'.

Все необходимые данные для расчёта потерянного дохода у нас уже есть, осталось реализовать сами расчёты:

# объединяем данные из Google Ads и Google Analytics
data <- left_join(ads_data, ga_data, by = c("campaign_id" = "adwordsCampaignID"))

# заполняем пропущенные значения нулями
# добавляем все необходимые вычисления
data <- mutate(data,
    across(where(is.numeric), replace_na, 0),

    lost_impression_by_budget_search = round(impressions / (1 - search_budget_lost_impression_share) - impressions, 0),
    lost_impression_by_rank_search = round(impressions / (1 - search_rank_lost_impression_share) - impressions, 0),
    lost_impression_by_budget_display = round(impressions / (1 - content_budget_lost_impression_share) - impressions, 0),
    lost_impression_by_rank_display = round(impressions / (1 - content_rank_lost_impression_share) - impressions,0),
    lost_impression_by_budget = lost_impression_by_budget_search + lost_impression_by_budget_display,
    lost_impression_by_rank = lost_impression_by_rank_search + lost_impression_by_rank_display,
    lost_clicks_by_budget = round(lost_impression_by_budget * ctr, 0),
    lost_clicks_by_rank = round(lost_impression_by_rank * ctr, 0),
    lost_transactions_by_budget = ifelse(is.nan(round(lost_clicks_by_budget * (transactions / clicks), 0)), 0, round(lost_clicks_by_budget * (transactions / clicks), 0)),
    lost_transactions_by_rank = ifelse(is.nan(round(lost_clicks_by_rank * (transactions / clicks), 0)), 0, round(lost_clicks_by_rank * (transactions / clicks), 0)),
    lost_transactions = lost_transactions_by_budget + lost_transactions_by_rank,
    lost_revenue_by_budget = ifelse(is.nan(round(lost_transactions_by_budget * (transactionRevenue / transactions), 0)), 0,round(lost_transactions_by_budget * (transactionRevenue / transactions), 0)),
    lost_revenue_by_rank = ifelse(is.nan(round(lost_transactions_by_rank * (transactionRevenue / transactions), 0)), 0, round(lost_transactions_by_rank * (transactionRevenue / transactions), 0)),
    lost_revenue = lost_revenue_by_budget + lost_revenue_by_rank
)

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

Визуализация данных

Для начала можем оценить общие потери дохода по аккаунту, построив круговую диаграмму:

# круговая диаграмма
res <- tibble(
  type  = c("полученный доход", "потерянный по бюджету", "потерянный по рейтингу"),
  value = c(sum(data$transactionRevenue), sum(data$lost_revenue_by_budget), sum(data$lost_revenue_by_rank))
)

res %>%
  ggplot( aes(x = 1, y = value, fill = type) ) +
  geom_col(position = position_stack(reverse = T)) +
  scale_fill_manual(values = c("green", "red", "firebrick")) +
  coord_polar(theta = "y")

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

Посмотрим данные в более глубокой детализации, разбив их по кампаниям:

# Потерянный доход  в разрезе кампаний
data %>%
  filter(transactionRevenue > 0) %>%
  select(campaign_name, transactionRevenue, lost_revenue_by_budget, lost_revenue_by_rank) %>%
  pivot_longer(
    c('transactionRevenue', 'lost_revenue_by_budget', 'lost_revenue_by_rank'),
    names_to = 'type',
    values_to = 'value'
  ) %>%
  ggplot( aes(x = campaign_name, y = value, fill = type) ) +
  geom_col(position = 'fill')+
  theme(axis.text.x = element_text(angle = 90, hjust = 1, vjust = 0.5, size = 9)) +
  scale_fill_manual(values = c(transactionRevenue = "forestgreen", lost_revenue_by_budget = "firebrick1", lost_revenue_by_rank = "tan1")) +
  ggtitle("Lost Revenue") +
  xlab('Campaign') +
  ylab('Lost Revenue, %')

Какие действия можно предпринять по данным диаграммам

На данном этапе мы визуализировали данные о потерянном доходе, как глядя на диаграммы минимизировать эти потери?

Потерянный доход у нас разбит на 2 части:

• Доход потерянный в связи с низким рейтингом объявлений и проигранными аукционами.
• Доход потерянный в связи дефицитом бюджета.

В первом случае можно поднять ставки, если вам позволяет CPO, или проработать над показателем качества. О том как анализировать, и поднимать показатель качества я рассказывал в статье «Как визуализировать показатель качества ключевых слов — рецепт скрипта на языке R».

Во втором случае, потери можно минимизировать если вовремя и в полном объёме пополнять баланс вашего рекламного аккаунта.

Работаем с данными в CSV-файле

С языком R знакомы не все, возможно вы захотите построить на основе полученных данных какие-то другие диаграммы, или выполнить дополнительные расчёты. В таком случае возможно вам захочется прибегнуть к более привычным инструментам, например процессорам электронных таблиц.

Одной строкой кода вы можете сохранить полученные данные в csv файл:

# выгрузка в csv
write.table(
  data,
  file = 'lostRevenue.csv',
  sep = ";",
  dec = ",",
  row.names = FALSE,
  fileEncoding = '1251'
  )

Полученный csv файл можно открыть в Excel, или загрузить в Google Spreadsheets и, уже используя их функционал, строить визуализации:

Ниже приведу описание полей csv файла:

Выводы

1. Для реализации алгоритма необходимо:

• настроенное отслеживание транзакций в Google Analytics;
• установленная связь между Google Analytics и Google Ads;
• установленный язык R на ПК и инструмент для работы с ним — RStudio;
• Developer Client в консоли разработчика Google;
• токен разработчика Google Ads для доступа к API Google Ads.

2. Чтобы запустить скрипт в RStudio, нужно:

• подключить пакеты «rgoogleads», «googleAnalyticsR», «dplyr», «tidyr»  и «ggplot2»;
• пройти аутентификацию в Google Analytics и Google Ads;
• запустить скрипт.

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

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

Если вы делаете первые шаги в изучении языка R, то рекомендую начать его изучение с моего бесплатного курса “Язык R для пользователей Excel”. Также наверняка вам будет полезен мой telegram и youtube канал R4marketing, большая часть контента которого состоит из обучающих материалов по языку R.

Буду рад ответить на ваши вопросы в комментариях!