Академия интернет-маркетинга
№1
 
Модальное окно

Как контролировать весь отдел PPC и видеть полную картину в одном отчёте Power BI

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

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(1 голос, в среднем: 5 из 5)

От редакции: Статья просто отличная. Поэтому не удержались и сделали репост. Пусть все хорошее и полезное будет в нашем блоге. Тема контроля и отчетности актуальна всегда.
how-to-monitor-the-entire-ppc-department
 

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

 

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

 
meat
 

Какие задачи стояли перед нами:

 

• реализовать просмотр динамики по основным показателям с различных аккаунтов Google AdWords в одном месте для более удобного контроля руководителем отдела;

• реализовать возможность быстро отвечать на вопросы и генерировать отчеты для клиента по основным показателям;

• реализовать поиск слабых мест в работе аккаунтов в графическом виде.

 

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

 

Какие инструменты мы использовали:

 

• Google Spreadsheets — для хранения части данных для отчета с возможностю корректировки (информация о PPC-специалисте и аккаунт-менеджере);

• пакет R RAdWords — для загрузки данных с Google AdWords;

• пакет R googlesheets — для загрузки данных с Google Spreadsheets;

• пакет R RMySQL — для работы с базами данных MySQL;

• база данных MySQL — для хранения данных с AdWords;

Power BI — для визуализации данных и формирования удобных отчетов.

 

Все работы мы разделили на три логических этапа:

 

1. Сбор необходимых данных

2. Визуализация полученных данных

3. Настройка автообновления отчета

 

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

 

1. Общий отчет по всем рекламным кампаниям по следующим показателям:

 

• Показы

• Клики

• CTR

• CPC

• Затраты

• Валюта аккаунта

• Количество конверсий

• Коэффициент конверсии

• Стоимость конверсии

 

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

 

• PPC-специалистам;

• аккаунт-менеджерам;

• рекламным аккаунтам (отдельно стоит упомянуть о детализации вглубь аккаунта Кампания > Группа объявлений > Ключевое слово);

• статусам кампаний;

• типам устройств;

• типам сетей;

• и ярлыкам.

 
report-by-accounts-adwords-в-Power-BI
 

2. Сравнительный отчет по рекламным кампаниям

 

Отчет, аналогичный предыдущему, за исключением одного важного момента — в нем можно сравнивать два любых периода (подробнее о том, как это реализовать читайте ниже). Показатели и фильтры такие же как и в первом отчете.

 
Example-report-with-comparison-in-Power-BI
 

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

 
Table-by-comparison-data-over-different-periods
 

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

 
Table-by-comparison-data-by-keyword-for-different periods
 

А теперь поговорим о том, как все это было реализовано.

 

Техническая реализация проекта

 

Этап 1. Сбор необходимых данных

 

Для построения отчетов нам необходимы были следующие данные с AdWords:

 

• Название аккаунта

• Название кампании

• Название группы объявлений

• Статус кампании

• Ключевое слово

• Тип устройства

• Тип сети

• Показы

• Клики

• Затраты

• Валюта аккаунта

• Конверсии

• Ярлыки

 

Для получения этих данных мы воспользовались R пакетом RAdwords. Написали небольшую функцию, которая на входе принимает ID аккаунта Google AdWords, а на выходе отдает данные по этому аккаунту. Пример кода ниже:

 

get_data <- function(AdWordsID) {

##Получаем данные из Google AdWords

body <- statement(select=c('Date',

'AccountDescriptiveName',

'CampaignName',

'CampaignStatus',

'AdGroupName',

'Criteria',

'Device',

'AdNetworkType2',

'Impressions',

'Clicks',

'Cost',

'Conversions',

'AccountCurrencyCode',

'Labels'),

report="CRITERIA_PERFORMANCE_REPORT",

start=start_date,

end=end_date)

rawDataAdWords <- getData(clientCustomerId=AdWordsID,

google_auth=google_auth,

statement=body)

return(rawDataAdWords)
}

 

Более подробно о работе с пакетом можно почитать в официальной документации.

 

Кроме этих данных нужно было также получить данные о PPC-специалисте и аккаунт-менеджере, которые ведут аккаунт. Для этого мы создали в Google Spreadsheets таблицу со следующим шаблоном:

 
example-report-in-google-spreadsheets
 

Для загрузки данных с таблицы в R был написан небольшой скрипт с которым можете ознакомится ниже:

 

 library(googlesheets)

   ##Проходим авторизацию

 gs_auth()

   ##Записываем ключ

 key <- gs_key("your_key")

  ##Загружаем необходимые данные

 AdWords_ID_list <- as.data.frame(gs_read(key, col_names = TRUE))

 

где вместо your_key нужно ввести ваш ключ. Где его взять показано на скриншоте.

 
key-Google-Spreadsheets
 

Более подробно об использовании пакета googlesheets можно почитать в официальной документации.

 

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

 

Для загрузки данных в БД использовали пакет R RMySQL. Пример кода ниже.

 

if(!"RMySQL" %in% installed.packages()[,1]){install.packages("RMySQL")}

library(RMySQL)

 #Подключаемся к базе данных

 conMySQL <- dbConnect(MySQL(), dbname = "yourdbname", user = "youruser", password = "yourpassword", host = "yourhost")

#Загружаем данные в базу данных

 dbWriteTable (conMySQL, "yourtable", data_name, row.names = FALSE, append = TRUE)

 #Закрываем соединение

 dbDisconnect(conMySQL)


 

где вместо значений «yourdbname», «youruser», «yourpassword», «yourhost” и «yourtable» нужно ввести свои значения.

 

Вроде как все хорошо, и все нужные данные уже есть в нашей базе данных, но нам нужно было реализовать возможность загрузки новых данных каждый день. Чтобы не подгружать каждый день данные за весь период мы написали небольшой код, который определяет последнюю дату с данными в БД и подгружает только недостающие данные. Если по определенному ID данные и вовсе отсутствуют, то данные загружаются с определенной даты указанной в скрипте.

 

##Получаем данные о последней дате с данными и определяем дату начала подгрузки данных

 

get_start_date <- function(ID) {

   name_db <- "yourtable"

   ## Получаем список существующих таблиц

   list_tables <- dbListTables(conMySQL)

   ## Проверяем, существует ли наша таблица

  ifelse (name_db %in% list_tables, {

       ## Загружаем данные если таблица есть

   old_data <- dbReadTable(conMySQL, name_db)

   id_adwords <- unique(old_data$ids)

       ifelse (ID %in% id_adwords, {

         filter_data <- filter(old_data, ids == ID)

       last_date <- max(filter_data$Day)

       start_date <- as.character.Date(as.Date(last_date)+1)

         }, {

             start_date <- "2017-06-01"

             })

   },

   {

   start_date <- "2017-06-01"

 })

   return(start_date)

}


 

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

 

Этап 2. Визуализация полученных данных

 

Для того, чтобы визуализировать данные в Power BI их нужно туда сначала загрузить. Особо останавливаться на этом мы не будем, так как для MySQL все достаточно просто. Если у вас возникнут с этим сложности, пишите вопросы в комментариях.

 

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

 

Для визуализации данных мы использовали как стандартные, так и пользовательские визуализации:

 

Как использовать пользовательские визуализации в Power BI

 

В Power BI множество стандартных визуализаций, но иногда их недостаточно для реализации ваших целей. В таких случаях вам помогут пользовательские, которые можно скачать по ссылке в Office Store. Для нашего отчета мы использовали визуализацию Smart Filter by OKViz. Для использования в Power BI её нужно сначала добавить, для этого нужно нажать на троеточие в панели визуализаций и выбрать пункт “Импортировать из файла”.

 
Download-User-Visualization-in-Power-BI
 

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

 

Как реализовать сравнение произвольных периодов в Power BI

 

В нашем отчете есть вот таких два ползунка:

 
comparison-periods-in-power-bI
 

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

 

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

 

Для создания этих таблиц можно воспользоваться функцией List.Dates.

 

Пример кода для создания такой таблицы ниже:

 

let

   x = Number.From(DateTime.LocalNow())-Number.From(#date(2017, 6, 1)),

   Source = List.Dates(#date(2017, 6, 1), x, #duration(1, 0, 0, 0)),

   #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

   #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),

   #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DimDate"}})

in

   #"Renamed Columns"


 

Такой код создает таблицу с датами с 01-06-2017 по сегодняшний день. После этого нужно соединить таблицы с периодами, назовем их условно DimDate и DimDate Compare, с нашими основными данными.

 
building-links-between-tables-in-power-BI
 

При этом связь с таблицей DimDate делаем активной, а связь с таблицей DimDate Compare нет.

 

После этого остается только создать соответствующие меры (рассмотрим на примере данных по кликам):

 

• для расчета кликов за текущий период:

 

Clicks. = CALCULATE(SUM('data'[Clicks]))

 

• для расчета кликов за период для сравнения:

 

Clicks (prev) = CALCULATE( SUM('data'[Clicks]);ALL('DimDate');USERELATIONSHIP('data'[Date];'DimDate Compare'[DimDate Compare]))

 

• для сравнения данных за периоды:

 

Clicks (diff) = 'data'[Clicks.] - 'Compare Measures'[Clicks (prev)]

 

Осталось закинуть данные например в матрицу и искать новые инсайты :)

 

Этап 3. Настройка автообновления отчета

 

• Автообновление данных в БД

 

Так как данные в БД мы передаем с помощью R-скрипта, то все, что нужно сделать это настроить запуск скрипта по расписанию. Здесь особо останавливаться не будем, так как у Анастасии Каминской есть отличная статья на эту тему.

 

• Автообновление данных в Power BI

 

Для реализации автообновления данных в Power BI нужно установить Локальный шлюз данных. Подробнее о его установке написано здесь.

 

После этого переходим в онлайн версию Power BI в раздел Настройки > Управление шлюзами:

 
configure-auto-update-in-Power-BI-step-1
 

Заполняем необходимые данные и нажимаем “Добавить источник данных” как показано на скриншоте:

 
Configure-auto-update-in-Power-BI-step-2
 

В появившимся окне заполняем все необходимые даные и нажимаем кнопку “Добавить”.

 
Configure-auto-update-in-Power-BI-step-3
 

После того как источник данных создан, нужно:

 

• перейти в настройки наборов данных (1),

• выбрать нужный набор данных (2),

• в пункте “Подключение шлюза” указать ваш шлюз (3),

• в пункте “Запланированное обновление” указать дни и время для обновления данных (4)

 
configure-auto-update-in-Power-BI Step-4
 

ВАЖНО! При настройке автообновления делайте так, чтобы сначала данные обновлялись в БД MySQL, а потом уже обновленные данные подтягивались в Power BI.

 

Что мы получили в результате всей проделанной работы?

 

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

 

• мы обеспечили быстрый доступ к статистике AdWords по всем аккаунтам без предоставления доступов в AdWords для аккаунт-менеджеров;

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

 

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

 
От редакции: главное после прочтения статьи что? Правильно! Внедрять. А если у вас есть хорошие идеи, кейсы, гипотезы — смело пишите главреду по адресу zaiats@web-promo.ua. Будем публиковать.

Макс Гапчук

Интернет-маркетолог в SOTNIK

Если Вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Поделитесь этим материалом с вашими друзьями в социальных сетях:

 

Подпишись на обновления блога

Поделитесь вашим мнением. Оставьте комментарий!

Подпишись на обновления блога

  • Новое видео

  • Популярное

  • Комментарии

  • 301 редирект Adsense alt API B2B B2C Call tracking Click CMS CPA CPC CPI CPO CRM CSS CTR DMOZ E-commerce email-маркетинг Facebook Google Google Adsense Google Adwords Google Analytics Google Panda Google Tag Manager Google Trends Instagram KeyCollector KPI Landing page PageRank PPC Rambler Robots.txt ROI Rookie RTB SaaS Sape SEM Seopult serm SERP Sitemap SMO Title Twitter URL Usability UTM-метка Webmasters Whois XML Xrumer Yahoo YML YouTube АГС Алгоритм Анкоры Аудит сайта Ключевые слова Конверсия Контент Микроразметка Медийная реклама Метатеги Оптимизация сайта Оффер Парсинг Партнерская программа Перелинковка Поддомен Поисковая система Баннерная реклама ВКонтакте Видео-реклама Виральность Вирусный маркетинг Воронка продаж Геотаргетинг Донор ссылок Емейл Индекс Индексация Интернет-магазин Интернет-маркетинг Лидогенерация Линкбилдинг Лендинг РСЯ Ремаркетинг Редирект Рекламная кампания Релевантность Ретаргетинг Рунет ТИЦ Тизерная реклама Спам Таргетинг Таргетированная реклама Сниппет Социальная сеть Трафик Ссылки маркетинговый анализ Фильтр Целевая аудитория ЧПУ инструменты для маркетолога клиенты контекстная реклама контент-маркетинг креатив планирование подборки Яндекс Яндекс.Каталог Яндекс.Метрика Яндекс.Вебмастер Яндекс.Вордстат Яндекс.Деньги Яндекс Директ продвижение на Запад автоматизация контекстной рекламы аудит контекстной рекламы аутрич бизнес веб-дизайн веб-аналитика отзывы покупателей рассылка сервисы маркетолога стратегия контент-маркетинга телеграм-каналы фейсбук юзабилити