✔Строим сводный отчет по таргетированной рекламе за 15 минут с помощью Power Query - «Заработок»

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

Но что, если необходимо выгрузить и оценить данные по кампаниям с нескольких площадок и соотнести их с конверсиями на сайте? А если в каждом кабинете по 10 кампаний, в каждой из которых по пять групп объявлений и 10 креативов. Кажется, что определить наиболее эффективный креатив кажется невозможным. Сложность заключается в том, что данные находятся на разных площадках: их нужно найти, выгрузить и объединить.

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

1. Наименование рекламных кампаний в кабинетах.


2. Прометки рекламных объявлений.


3. Создания кастомных отчетов в Google Analytics.


4. Выгрузки отчетов из рекламных площадок.


5. Объединения данных в Power Query.


6. Создание сводной таблицы.

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

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

• размещает рекламу сразу в нескольких рекламных кабинетах: Facebook Ads, «ВКонтакте», myTarget;


• работает с бюджетами от 200 000 рублей в месяц;


• тестирует разные креативы, таргетинги единовременно, соответственно, в кабинетах много кампаний, групп объявлений и самих объявлений;


• хочет отслеживать эффективность рекламных кампаний как минимум раз в неделю;


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


• пользуется системой автоматизации Plarin или ее аналогами для «ВКонтакте» и myTarget (о том, почему это важно расскажем в шаге 6).

Шаг 1. Работа со структурой и неймингом рекламных кампаний в кабинете

Единые принципы нейминга и структуры кабинета удобны для:

• быстрой сортировки по платформам, типам креативов, геотаргетингу и другим параметрам.


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


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

В качестве примера приводим стандарт нейминга рекламных кампаний в Adventum.

Уровень

Что пишем

Пример

Кампания

Тип кампании

leads, traffic, conversion

Группа объявлений

Платформа_тип закупки_гео_возраст_пол_таргетинг

i_cpm_msk_25-44_m_int-cars

Объявления

Название тип баннера_номер креатива_номер текста

promo_1_1, carousel_1_1

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

Уровень

Что пишем

Пример

Кампания

Платформа_тип закупки_гео_возраст_пол_таргетинг

vk_cpm_msk_25-44_m_int-cars

Объявления

Название тип баннера-номер креатива-номер текста

promo_1_1, carousel_1_1

Важно: не у всех форматов во «ВКонтакте» в метку подставляется название кампании. Это доступно только у форматов «Рекламная запись с кнопкой» и «Кольцевая галерея».

Уровень

Что пишем

Пример

Кампания

Платформа_тип закупки_гео_возраст_пол_таргетинг

vk_cpm_msk_25-44_m_int-cars

Объявления

В название объявления автоматически подставляется заголовок объявления

Шаг 2. Прометка рекламных объявлений

Она необходима для того, чтобы отслеживать, какое именно рекламное объявление принесло конверсию на сайте. Существует два типа прометки: статическая и динамическая.

Статическая прометка

Метки ссылок в каждом объявлении прописываются вручную. Стандартно используются значения: utm_source, utm_medium, utm_campaign, utm_term. Например:

https://mysite/?utm_source=facebook&utm_medium=cpc&utm_campaign=event_02.04.2020

При большом количестве объявлений прометка может занимать часы, поэтому лучше использовать второй тип.

Динамическая прометка

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

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

Параметр

Значение

{{ad.id}}

идентификатор объявления

{{adset.id}}

идентификатор группы объявлений

{campaign.id}}

идентификатор рекламной кампании

{{ad.name}}

название объявления из рекламного кабинета

{{adset.name}}

название группы объявлений из рекламного кабинета

{{campaign.name}}

название кампании из рекламного кабинета

{{ad.name}}

название объявления из рекламного кабинета

{{adset.name}}

название группы объявлений из рекламного кабинета

{{campaign.name}}

название кампании из рекламного кабинета

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

Параметр

Значение

{ad_id}

идентификатор объявления

{campaign_id}

идентификатор рекламной кампании

{random}

случайное число

{campaign_name}

название кампании из рекламного кабинета
(только для форматов рекламная запись с кнопкой и карусель)

{platform}

платформы, с которых совершались переходы по рекламе (только для форматов рекламная запись с кнопкой и карусель)

{price_list_id}

цена (для динамического ремаркетинга)

{product_id}

продукт (для динамического ремаркетинга)

Пример размеченной ссылки с динамическими параметрами для «ВКонтакте»: https://vk. com/ads?utm_source=vk&utm_medium=cpc&utm_campaign={campaign_id}&utm_term={ad_id}&utm_content={random}

Параметр

Значение

{{advertiser_id}}

идентификатор рекламодателя

{{campaign_id}}

идентификатор рекламной кампании

{{campaign_name}}

название рекламной кампании

{{banner_id}}

идентификатор баннера

{{geo}}

id региона по геодереву myTarget, из которого был сделан переход

{{gender}}

пол пользователя, который сделал переход

{{age}}

возраст пользователя, который сделал переход

{{random}}

случайное число

{{impression_weekday}}

передает день недели, в который произошел показ баннера

{{impression_hour}}

передает час, в который произошел показ по московскому времени в 24-часовом формате

{{user_timezone}}

передает временную зону пользователя, которому был сделан показ

Чтобы построить отчет, достаточно только параметра ad id — идентификатора объявления (для myTarget — баннера). Но для более детального отслеживания эффективности рекламных кампаний используйте и другие параметры.

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

Шаг 3. Создание кастомных отчетов в Google Analytics

1. 
   

   Заходим в Google Analytics в раздел «Мои отчеты».

   
   

   
   

   
   


2. 
   

   Нажимаем «Добавить отчет».

   
   

   
   

   
   


3. 
   

   Собираем отчет:

   
   
   • 
     

     в показателях выбираем сеансы и цели. Если целей несколько — выбираем все;

     
     
   
   
   • 
     

     в параметрах выбираем «Содержание объявления» — это как раз параметр ad id, который мы ранее указывали в динамической прометке;

     
     
   
   
   • 
     

     в фильтрах указываем источники, по которым будем строить отчет. В данном случае нас интересуют источники таргетированной рекламы — «ВКонтакте», myTarget, Facebook.

     
     
   
   

   
   

   
   


4. 
   

   Получаем отчет. Выбираем период и выгружаем в Excel.

   
   

   
   

   
   

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

Шаг 4. Выгрузка отчетов из площадок

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

1. Данные по Facebook мы выгружаем из самого кабинета Facebook Ads.


2. Данные по «ВКонтакте» и myTarget выгружаем через Plarin или его аналоги: Iskander и HiConversion.

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

Если вы не используете их и у вас большое количество кампаний, то, к сожалению, отчет в разбивке по группам объявлений и объявлениям построить будет очень сложно. «ВКонтакте» и myTarget не позволяют делать выгрузки одновременно в разбивке по кампаниям, группам объявлений и объявлениям. Понадобится выгружать статистику по объявлениям из каждой группы вручную.

Выгрузка данных по рекламе из Facebook Ads

Для выгрузки данных переходим в отчеты, «Создать свой отчет».

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

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

После того как построили отчет, нажимаем Export.

Выгрузка данных по рекламе «ВКонтакте» и myTarget из Plarin

Выгрузить данные по «ВКонтакте» и myTarget нужно будет отдельно, но по одному алгоритму.

Заходим в кабинет, выбираем нужный период, рекламные аккаунты и кампании и нажимаем «Экспорт в Excel».

Для экспорта выбираем «Основные показатели и статистика».

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

Шаг 5. Объединение данных в Power Query

Теперь у нас есть данные по кампаниям, таргетингам и объявлениям из рекламных кабинетов и данные по конверсиям из Google Analytics. Все они хранятся в четырех файлах:

• Выгрузка из Google Analytics


• Выгрузка из Facebook


• Выгрузка из Plarin по «ВКонтакте»


• Выгрузка из Plarin по myTarget

Их нужно объединить по общему параметру — идентификатор объявления.

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

Объединяем в Power Query данные из трех рекламных кабинетов

1. 
   

   Открываем Excel — «Данные» — «Создать запрос из файла». Выбираем сначала файл с выгрузкой из Facebook.

   
   

   
   

   
   


2. 
   

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

   
   

   
   

   
   


3. 
   

   ID рекламы по умолчанию загружается в числовом формате и поэтому отображаются не все числа. Чтобы такого не было, переводим его в текстовый формат.

   
   

   
   

   
   


4. 
   

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

   
   

   
   

   
   


5. 
   

   Удаляем столбцы, которые нам не пригодятся: тип закупки и даты. Чтобы мы не потерялись, все наши шаги записываются в окне справа.

   
   

   
   

   
   


6. 
   

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

   
   

   
   

   
   


7. 
   

   Далее переходим к столбцу «Название объявления»: разделяем на столбцы и переименовываем их. В названии объявления мы записывали тип креатива, номер креатива и номер уникального текста для креатива.

   
   

   
   

   
   


8. 
   

   Используем «Замену значений», чтобы перевести метки на русский язык. Это не обязательно, но если вы хотите показать этот отчет клиенту или другому коллеге и чтобы всем были понятны данные из отчета, лучше все значения перевести. Например, int-cars — это «Интересы: авто», а promo — это «Промопост».

   
   

   
   

   
   


9. 
   

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

   
   

   Для подсчета расхода добавляем «Пользовательский столбец» и считаем по формуле:

   
   

   
   

   
   


10. 
    

    Загружаем и сохраняем.

    
    

    
    

    
    

    Первая таблица с данными готова. Далее по аналогии нужно загрузить на соседних листах данные с «ВКонтакте» и myTarget. В итоге получим три листа с данными по разным каналам. Важно, чтобы во всех листах названия столбцов были одинаковыми.

    
    

    
    

    
    


11. 
    

    Объединяем три листа с данными. Выбираем «Данные» — «Из таблицы».

    
    

    
    

    
    


12. 
    

    Выбираем «Комбинировать» — «Добавить запросы».

    
    

    
    

    
    

Теперь у нас объединены данные из трех рекламных кабинетов в одном листе «Data общая». В ней есть данные по кампаниям, таргетингам, показам, кликам, но нет данных по конверсиям. Для этого данные по рекламным кабинетам нужно свести с конверсиями из Google Analytics.

Сводим данные по рекламным кабинетам с Google Analytics

1. 
   

   Загружаем в Excel данные из Google Analytics на отдельном листе.

   
   

   
   

   
   


2. 
   

   Делаем слияние данных с листа «Data общая» с данными из Google Analytics: «Данные» — «Создать запрос» — «Объединить запросы» — «Объединить».

   
   

   
   

   
   


3. 
   

   Выбираем параметр для слияния — «ID рекламы».

   
   

   
   

   
   


4. 
   

   Появляется новый запрос с колонкой New column. Нажимая на него, мы можем выбрать колонки для добавления. Нужно выбрать все, кроме параметра, по которому мы делали слияние — «ID рекламы».

   
   

   
   

   
   

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

Шаг 6. Создание сводной таблицы

Теперь, когда у нас есть все данные, можно построить сводную таблицу и проанализировать эффективность рекламных кампаний. Выбираем «Вставка» — «Сводная таблица».

На основе данных можно построить два отчета в разрезе по креативам и по таргетингам:

Результат

Раньше все данные сводились вручную, и это отнимало до шести часов рабочего времени. На сбор отчета по этому алгоритму уходит всего около 15 минут.

Всё, что требуется на старте — это:

• разработать единый нейминг и структуру рекламных кабинетов;


• правильно разметить рекламные кампании;


• выгрузить отчеты со всех площадок;


• объединить данные в Power Query;


• объединить запросы.

После прохождения всех шагов у вас будет готовая программа. Создание сводных становится быстрее и проще.

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