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

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

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

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

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

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

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

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

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

1. Доступ на мой логин к Яндекс.Метрике заказчика.
2. Данные для доступа в Яндекс.Директ.
3. Запросили ключи доступа для API у Criteo — (Client ID и Client Secret). На это ушло 4 дня.
4. Данные для доступа в Яндекс.Маркет.
5. Данные для доступа в Mytarget (рекламу заказчик запускал с агентского аккаунта, при этом получать доступ к API необязательно).
6. Доступ администратора в рекламный кабинет 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)}

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

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

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*30cost_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.spentspent_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.spendspend_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. Вы получаете готовую базу данных по нужным источникам рекламы для последующего использования в анализе или визуализации этих данных.

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