Аналитика

Как быстро построить кастомную отчетность онлайн-рекламы с помощью языка R

Всем привет! Меня зовут Константин Лолуа, я специалист по онлайн-рекламе. Если у вас много источников рекламы, то вы уже поняли, что для развернутой картины по аналитике одной Яндекс.Метрики или Google Analytics будет недостаточно. Ниже я рассмотрю с помощью языка R один из вариантов построения кастомной отчетности с большим количеством источников рекламы.

Рассматриваемый скрипт будет полезен всем, кто только начинает работать с готовыми пакетами на языке R и хочет бесплатно экспортировать данные из рекламных кабинетов в BI-системы.

Ко мне обратился предприниматель с просьбой составить удобную автоматизированную отчетность. Он хотели уйти от ручного сбора информации из рекламных кабинетов (и занесения их в Excel) и перейти к автоматическому импорту в Data Studio или Power BI.

Старый отчет выглядел так:

Реклама была запущена в Яндекс.Директ, MyTarget (ВКонтакте, Одноклассники), Facebook (Instagram), Яндекс.Маркет, Criteo. Нужно было собрать все расходы с кабинетов и посчитать стоимость лида в разрезе источников. Статистика нужна была в разрезе по дням.

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

В результате мы получали специфические данные: расход за сегодня 0 рублей, получили 5 лидов, цена лида 0 / 5 = 0 руб.

Заказчик сказал, что такое положение дел его устраивает, потому что отдельно будет смотреть сумму за месяц.

Я решил попробовать все с Data Studio и посмотреть готовые коннекторы. Главные проблемы — много источников рекламы и нехватка подходящих бесплатных коннекторов.

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

Раньше я сталкивался в работе с языком R. Я знал, что в R есть готовые пакеты (коннекторы) для выгрузки данных. Я уже пользовался пакетами Алексея Селезнева по Яндекс.Директу и Яндекс.Метрике, но еще были нужны пакеты для Criteo и Яндекс.Маркет. Я нашел их на Github у других разработчиков.

Что потребовалось для начала работы с отчетами:

Доступ на мой логин к Яндекс.Метрике заказчика.
Данные для доступа в Яндекс.Директ.
Запросили ключи доступа для API у Criteo — (Client ID и Client Secret). На это ушло 4 дня.
Данные для доступа в Яндекс.Маркет.
Данные для доступа в Mytarget (рекламу заказчик запускал с агентского аккаунта, при этом получать доступ к API необязательно).
Доступ администратора в рекламный кабинет Facebook для доступа к данным заказчика.

План был такой: собрать данные из рекламных кабинетов, выгрузить в Google BigQuery и оттуда потом уже на выбор DataStudio или PowerBI.

Отчетность нужна была в реальном времени, то есть каждый день видеть данные за предыдущий день и раньше. Поэтому при написании скрипта я учитывал, что данные нужно считывать раз в день за предыдущие сутки и отправлять информацию в BigQuery. Скрипт должен запускаться ежедневно. Пошаговая настройка описана в блоге Netpeak «Как выполнить запуск команды R-скрипта по расписанию?»

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

Поэтому в скрипте я делаю проверку на размер отдаваемых данных, и, если там пусто, сам заполняю строку нулями.

if (length(Facebook[[1]]) == 0) {

x <- data.frame(date = Sys.Date()-1, adsystem = "Facebook", goal_reach = 0)

Facebook <- rbind(Facebook,x)

}

Вторая сложность: при авторизации в MyTarget появлялась ошибка. После обращения за помощью к создателю пакета, он посоветовал получить права на учетную запись. Прав только на просмотр не хватало для выдачи токена. После получения полных прав все прошло успешно, и я получил токен авторизации.

При получении данных с Яндекс.Маркета необходимо условные единицы перевести в рубли.

Также в коде встречаются места, где идет приведение типов. Превращаем данные из текста в тип Date, чтобы таблицы с целями и расходами можно было объединить по дате.

FB_dates <- as.Date(fb_cost$date, "%Y-%m-%d")

FB_stats<-cbind(fb_cost,FB_dates)

Некоторые пакеты принимают даты на входе не YYYY-MM-DD, а к примеру DD-MM-YYYY. Замените передачу даты.

Вместо fromDate = Sys.Date()-1 нужно передавать

fromDate = format(Sys.Date()-1, "%d-%m-%Y")

Criteo отдает данные с датой в виде Wed 1/22/2020. Чтобы привести к единой дате, воспользуйтесь командой:

Sys.setlocale("LC_TIME","C")

for (y in 1:nrow(Criteo_cost)){

date_s <- strptime(Criteo_cost[y,2],format = "%a %m/%d/%Y")

Criteo_cost[y,2] <- toString(date_s)

}

Напомню: изначально я начал писать код, который получал данные за месяц. Поэтому для корректного соотношения данных мне нужны были смены типов для дат, чтобы объединить аналогом JOIN SQL две таблицы (расходы за месяц и цели за месяц). Со временем я переписал код, который получает данные за день. Значит, неактуальные фрагменты кода нужно убрать.

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

Так делаем по всем источникам.

FB_CPA <- FB_all_data[,4]/FB_all_data[,2]

FB_all_data <- cbind(FB_all_data,FB_CPA)

Потом нужно собрать данные в одной строке.

ALL_DATA <- cbind.data.frame (yandex_market_all_data, Criteo_all_data, direct_all_data, myTG_all_data, FB_all_data)

Затем нужно отправить данные в BigQuery. Подробная инструкция описана в статье «Хранилище Google BigQuery — зачем нужна облачная база данных».

insert_upload_job("mytest-****",

"my_test",

"Statistic",

ALL_DATA,

"mytest-*****",

create_disposition = "CREATE_IF_NEEDED",

write_disposition = "WRITE_APPEND")

Далее в Data Studio добавьте источник данных BigQuery. Затем выберите проект для экспорта и нужную таблицу. Переносите нужные столбцы на лист и получите данные. Отчет обновляется кнопкой «Обновить в DataStudio» — подтягиваются последние данные из BigQuery.

Вы также можете использовать стандартные диаграммы DataStudio для визуализаций имеющихся данных.

Это лишь начало. Чуть позже (примерно через неделю-две) отдельно добавятся данные по сумме за период: плановые/фактические данные и таблицы с процентами выполнения KPI.

Готовый скрипт:

library(devtools)
library(timeperiodsR)
library(readr)    
library(data.table)   
library(stringr)
library(bigrquery)
library(ryandexdirect)
library(rym)
library(yamarketr)
library(CriteoR)
library(rfacebookstat)
library(rmytarget)
############################
# получаем статистику из метрики по целям за вчера										  
stat_metrika <- rym_get_data(counters   = "*****", #номер счетчика
                                               date.from  = Sys.Date()-1,
                                               date.to    = Sys.Date()-1,
                                               dimensions = "ym:s:date,ym:s:lastsignAdvEngine",
                                               metrics    = "ym:s:goal*****reaches",  #номер цели
											                         sort       = "-ym:s:date",
                                               login      = "****", #логин метрики
                                               token.path = "metrica_token",
                                               lang = "en")
setnames(stat_metrika, 1,"date")
setnames(stat_metrika, 2,"adsystem")   
setnames(stat_metrika, 3,"goal_reach")
############################
# делим на фреймы по каналам, и заполняем пустоты в случае необходимости
Criteo <- subset(stat_metrika, adsystem == "Criteo")
if (length(Criteo[[1]]) == 0) {
   x <- data.frame(date = Sys.Date()-1, adsystem = "Criteo", goal_reach = 0)
   Criteo <- rbind(Criteo,x)
}

Facebook <- subset(stat_metrika, adsystem == "Facebook")
if (length(Facebook[[1]]) == 0) {
    x <- data.frame(date = Sys.Date()-1, adsystem = "Facebook", goal_reach = 0)
  Facebook <- rbind(Facebook,x)
}

Market <- subset(stat_metrika, adsystem == "Market")
if (length(Market[[1]]) == 0) {
  x <- data.frame(date = Sys.Date()-1, adsystem = "Market", goal_reach = 0)
  Market <- rbind(Market,x)
}

Yandexdirect <- subset(stat_metrika, adsystem == "Yandex: Direct")
if (length(Yandexdirect[[1]]) == 0) {
  x <- data.frame(date = Sys.Date()-1, adsystem = "Yandex: Direct", goal_reach = 0)
  Yandexdirect <- rbind(Yandexdirect,x)
}

Mailru <- subset(stat_metrika, adsystem == "Target@Mail.ru")
if (length(Mailru[[1]]) == 0) {
  x <- data.frame(date = Sys.Date()-1, adsystem = "Target@Mail.ru", goal_reach = 0)
  Mailru <- rbind(Mailru,x)
}
############################
#получаем стату директа
direct_cost <- yadirGetReport(ReportType = "CUSTOM_REPORT",
                              DateRangeType = "CUSTOM_DATE",
                              DateFrom = Sys.Date()-1,
                              DateTo = Sys.Date()-1,
                              FieldNames = c( "Date",
                                              "Cost"),
                              Login = "****")#логин директа

if (length(direct_cost) == 0) {
  direct_cost <- data.frame(Date = Sys.Date()-1, Cost = 0)
  print("Пустоты заполнены")
}

setnames(direct_cost, 1,"date")
Yandexdirect_date <- as.Date(Yandexdirect$date, "%Y-%m-%d")
direct_all<-cbind(Yandexdirect_date,Yandexdirect)
setnames(direct_all, 2,"direct_old_date")
setnames(direct_all, 1,"date")
direct_last<- dplyr::left_join(direct_cost,direct_all,by="date")
result_direct <- data.frame(direct_last$date,direct_last$Cost,direct_last$goal_reach)
result_f_direct <- result_direct[,2]/result_direct[,3]
data <- data.frame(direct_CPA = result_f_direct)
direct_all_data <-cbind(result_direct,data)
setnames(direct_all_data, 1,"Date")
setnames(direct_all_data, 2,"Direct_cost")
setnames(direct_all_data, 3,"Direct_goal")
setnames(direct_all_data, 4,"Direct_CPA")



############################
#получаем стату Критео по расходам

token_criteo <- criteo_auth("**********", "*******") #данные токена, получить в поддержке критео
Criteo_cost <- criteo_stats_report(start_date = Sys.Date()-1, 
                    end_date = Sys.Date()-1,
                    dimensions = "Day",
                    metrics = list("AdvertiserCost", 
                                   "SalesPc"),
                    prettify = F)

Sys.setlocale("LC_TIME","C")

for (y in 1:nrow(Criteo_cost)){
  date_s<- strptime(Criteo_cost[y,2],format = "%a %m/%d/%Y")
  Criteo_cost[y,2] <- toString(date_s)
  }

setnames(Criteo_cost, 2,"date")
Criteo_dates <- as.Date(Criteo_cost$date, "%Y-%m-%d")
Criteo_all<-cbind(Criteo_dates,Criteo_cost)
setnames(Criteo_all, 3,"criteo_old_date")
setnames(Criteo_all, 1,"date")

Criteo$date<-as.Date(Criteo$date)
Crit<- dplyr::left_join(Criteo_all,Criteo, by = "date")
result <- data.frame(Crit$date,Crit$Cost,Crit$goal_reach)
result_f <- result[,2]/result[,3]
data <- data.frame(criteo_CPA = result_f)
Criteo_all_data <-cbind(result,data)

setnames(Criteo_all_data, 1,"Criteo_date")
setnames(Criteo_all_data, 2,"Criteo_cost")
setnames(Criteo_all_data, 3,"Criteo_goal")
setnames(Criteo_all_data, 4,"Criteo_CPA")


############################
# получаем статистику из яндекс.маркета

yamarketrAuth(Login = "*******", NewUser = FALSE) #логин маркета
Market_cost<- yamarketrGetCosts(****, fromDate = format(Sys.Date()-1, "%d-%m-%Y"), toDate = format(Sys.Date()-1, "%d-%m-%Y"), Login = NULL, TokenPath = getwd(), places = 0, model = 0, fetchBy = "daily") # № Campaign

#заполнение пустот
if (length(Market_cost[[1]]) == 0) {
  Market_cost <- data.frame(date = Sys.Date()-1, a = 0, b = 0, c = 0, d = 0, Cost = 0)
}

Market_dates <- as.Date(Market$date, "%Y-%m-%d")
Market_all<-cbind(Market_dates,Market)
setnames(Market_all, 2,"market_old_date")
setnames(Market_all, 1,"date")

Market_all_data<- dplyr::left_join(Market_all,Market_cost, by = "date")
setnames(Market_all_data, 9,"Cost")
result_market <- data.frame(Market_all_data$date,Market_all_data$Cost,Market_all_data$goal_reach)

cost_rubles <- result_market$Market_all_data.Cost*30
cost_rubles <- data.frame(cost_rubles = cost_rubles)
result_market_rubl <- cbind(result_market, cost_rubles)
result_f_market <- result_market_rubl[,4]/result_market_rubl[,3]
market_CPA <- data.frame(market_CPA = result_f_market)
yandex_market_all_data <-cbind(result_market_rubl,market_CPA)

setnames(yandex_market_all_data, 1,"Market_date")
setnames(yandex_market_all_data, 2,"Market_cost_ye")
setnames(yandex_market_all_data, 3,"Market_goal")
setnames(yandex_market_all_data, 4,"Market_cost_rubles")
setnames(yandex_market_all_data, 5,"Market_CPA")


############### яндекс маркет обработка завершена

############################
# получаем статистику из MyTarget по целям за вчера		

myTG_cost <-  myTarGetStats(date_from   =  Sys.Date() - 1,
                            date_to     = Sys.Date() - 1,
                            object_id   = ******, 
                            object_type = "users",
                            metrics     = "all",
                            login       = "*****@agency_client", #агентский логин
                            token_path = "myTargetAuth")

myTG_stat_data<- dplyr::left_join(Mailru,myTG_cost,by = "date")
myTG_stat_data <- data.frame(myTG_stat_data$date,myTG_stat_data$goal_reach,myTG_stat_data$spent)
setnames(myTG_stat_data, 1,"date")
myTG_dates <- as.Date(myTG_stat_data$date, "%Y-%m-%d")
myTG_stat_data_full<-cbind(myTG_stat_data,myTG_dates)
setnames(myTG_stat_data_full, 1,"mytg_old_date")
setnames(myTG_stat_data_full, 4,"date")


spent_mytarget <- myTG_stat_data_full$myTG_stat_data.spent
spent_mytarget = as.numeric(as.character(spent_mytarget))

myTG_stat_data_full<-cbind(myTG_stat_data_full,spent_mytarget)
setnames(myTG_stat_data_full, 5,"Mytarget_spend")

mytarget_CPA <- myTG_stat_data_full[,5]/myTG_stat_data_full[,2]
myTG_all_data<-cbind(myTG_stat_data_full,mytarget_CPA)

setnames(myTG_all_data, 1,"MYtarget_date_old")
setnames(myTG_all_data, 2,"MYtarget_goal")
setnames(myTG_all_data, 3,"MYtarget_cost_old")
setnames(myTG_all_data, 4,"MYtarget_date")
setnames(myTG_all_data, 5,"MYtarget_cost")
setnames(myTG_all_data, 6,"MYtarget_CPA")

##############################
### Получение статы Facebook
###

fbAuth(app_id = ***********, app_secret = "**********") #данные приложения Фейсбука
fb_cost <- fbGetMarketingStat(level = "account",
                                  fields = "account_name,campaign_name,impressions,clicks,spend",
                                  date_start = Sys.Date()-1,
                                  date_stop = Sys.Date()-1,
                                  interval = "day",accounts_id = "act_*******")

if (length(fb_cost) == 0) {
  FB_stats <- data.frame( a = 0, b = 0, c = 0, spend = 0, date = Sys.Date()-1)
} else { 
  setnames(fb_cost, 5,"date")
  FB_dates <- as.Date(fb_cost$date, "%Y-%m-%d")
  FB_stats<-cbind(fb_cost,FB_dates)
  setnames(FB_stats, 5,"fb_old_date")
  setnames(FB_stats, 7,"date")
}

FB_stats_full<- dplyr::left_join(Facebook,FB_stats,by = "date")
FB_stats_full <- data.frame(FB_stats_full$date,FB_stats_full$goal_reach,FB_stats_full$spend)
spend_fb <- FB_stats_full$FB_stats_full.spend
spend_fb = as.numeric(as.character(spend_fb))
FB_all_data<-cbind(FB_stats_full,spend_fb)
setnames(FB_all_data, 4,"spend")

FB_CPA <- FB_all_data[,4]/FB_all_data[,2]
FB_all_data<-cbind(FB_all_data,FB_CPA)
setnames(FB_all_data, 1,"Facebook_date")
setnames(FB_all_data, 2,"Facebook_goal")
setnames(FB_all_data, 3,"Facebook_cost_old")
setnames(FB_all_data, 4,"Facebook_cost")
setnames(FB_all_data, 5,"Facebook_CPA")

##Объединение данных
ALL_DATA <- cbind.data.frame(yandex_market_all_data,Criteo_all_data,direct_all_data,myTG_all_data,FB_all_data)


##### отправка в биг квери
insert_upload_job("*****",  #номер проекта
            "my_test", 
            "Statistic",  
           ALL_DATA, 
           "mytest", 
           create_disposition = "CREATE_IF_NEEDED",     
           write_disposition = "WRITE_APPEND")

Вывод

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

Данные собираются в единое хранилище Google BigQuery. Вы получаете готовую базу данных по нужным источникам рекламы для последующего использования в анализе или визуализации этих данных.

Первые 10 ГБ в BigQuery бесплатны, затем $0,02 за ГБ. Уверен, 99% предпринимателям на первое время хватит бесплатной версии. Если вас не устраивает внешнее хранилище, то с помощью соответствующих пакетов R вы сможете выгрузить данные в любую SQL-базу.

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

Обнаружили ошибку? Выделите ее и нажмите Ctrl + Enter.

Константин Лолуа

Интернет-маркетолог. Специализируюсь на контекстной рекламе в Яндекс.Директ и Google.Ads. Внедряю сквозную аналитику. Осваиваю работу с API в R. Сертифицированный специалист Google Ads и Яндекс.Директ.

Другие статьи автора