Как быстро построить кастомную отчетность онлайн-рекламы с помощью языка R
Всем привет! Меня зовут Константин Лолуа, я специалист по онлайн-рекламе. Если у вас много источников рекламы, то вы уже поняли, что для развернутой картины по аналитике одной Яндекс.Метрики или Google Analytics будет недостаточно. Ниже я рассмотрю с помощью языка R один из вариантов построения кастомной отчетности с большим количеством источников рекламы.
Рассматриваемый скрипт будет полезен всем, кто только начинает работать с готовыми пакетами на языке R и хочет бесплатно экспортировать данные из рекламных кабинетов в BI-системы.
Старый отчет выглядел так:
Реклама была запущена в Яндекс.Директ, MyTarget (ВКонтакте, Одноклассники), Facebook (Instagram), Яндекс.Маркет, Criteo. Нужно было собрать все расходы с кабинетов и посчитать стоимость лида в разрезе источников. Статистика нужна была в разрезе по дням.
В Яндекс.Метрике были данные по целям и источнику, а с кабинетов, соответственно, я брал расходы и объединял информацию. Конечно, возникает нюанс с отложенными конверсиями. К примеру, сегодня нет расходов по Яндекс.Директ, а конверсии есть, потому что источник конверсии привязан к первому посещению клиента, который, возможно, уже посещал сайт.
В результате мы получали специфические данные: расход за сегодня 0 рублей, получили 5 лидов, цена лида 0 / 5 = 0 руб.
Заказчик сказал, что такое положение дел его устраивает, потому что отдельно будет смотреть сумму за месяц.
Я решил попробовать все с Data Studio и посмотреть готовые коннекторы. Главные проблемы — много источников рекламы и нехватка подходящих бесплатных коннекторов.
Плюс еще нужно было соединить полученные данные с Яндекс.Метрикой. А для этого, скорее всего, понадобится написать вручную команду для выгрузки данных из кабинетов.
Что потребовалось для начала работы с отчетами:
- Доступ на мой логин к Яндекс.Метрике заказчика.
- Данные для доступа в Яндекс.Директ.
- Запросили ключи доступа для API у Criteo — (Client ID и Client Secret). На это ушло 4 дня.
- Данные для доступа в Яндекс.Маркет.
- Данные для доступа в Mytarget (рекламу заказчик запускал с агентского аккаунта, при этом получать доступ к API необязательно).
- Доступ администратора в рекламный кабинет Facebook для доступа к данным заказчика.
План был такой: собрать данные из рекламных кабинетов, выгрузить в Google BigQuery и оттуда потом уже на выбор DataStudio или PowerBI.
Отчетность нужна была в реальном времени, то есть каждый день видеть данные за предыдущий день и раньше. Поэтому при написании скрипта я учитывал, что данные нужно считывать раз в день за предыдущие сутки и отправлять информацию в BigQuery. Скрипт должен запускаться ежедневно. Пошаговая настройка описана в блоге Netpeak «
Основная проблема, с которой я столкнулся: некоторые кабинеты отдают пустоты, если не было расходов. К примеру, я выгрузил данные за месяц и увидел, что некоторых дней нет. В таблице отсутствовали строки с датами. В некоторых же все было хорошо и там отдавались нули в строках.
Поэтому в скрипте я делаю проверку на размер отдаваемых данных, и, если там пусто, сам заполняю строку нулями.
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. Подробная инструкция описана в статье «
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. Некоторые моменты конечно можно еще упростить. Пишите в комментариях, если вы сталкивались с подобной задачей и нашли решение лучше.
Свежее
История успеха vchasno.ua: разработали карты коммуникаций и триггерные цепочки писем
Готовые триггерные письма и сценарии позволяют быстро запустить триггеры после реализации технических заданий разработчиком.
Управление репутацией в сети — особенности работы с отзовиками
Информация о любой организации распространяется в интернете очень быстро, особенно негативная. Поэтому необходимо учиться управлять своей репутацией.
Кейс avtokrisla.com: триггерное письмо «Заказ оформлен» как дополнительный источник прибыли
Многие недооценивают триггерное письмо после оформления покупки, а зря. И вот почему.