Как автоматизировать подготовку отчетов по рекламе с помощью Python

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

Проблема, которую нужно было решить

Когда я работал специалистом по трафику in-house, мне нужно было готовить отчеты по рекламным системам. При этом отчеты сегментировались двумя способами:

По регионам: компания присутствовала в шести регионах.
По рекламным системам: Яндекс.Директ, Google Ads, «ВКонтакте», myTarget и Facebook Ads.

Всего получалось пять рекламных систем на шесть регионов — 30 еженедельных отчетов. Если действовать традиционным способом — выгружать и сводить все данные в Excel через создание нескольких сводных таблиц и склейку с помощью ВПР в один итоговый файл, — на создание новых кампаний и оптимизацию уже работающих времени просто не останется. На помощь мне пришел Python и его самая популярная библиотека для работы с табличными данными Pandas.

Еще несколько нюансов:

Для построения одного отчета было необходимо делать две выгрузки: из рекламной системы и из базы данных, где хранились финансовые данные вроде дохода, прибыли и другие.
Legacy: utm_campaign не всегда дублировал название кампании. Терялся общий ключ, по которому можно было соединять две таблицы. Пришлось поколдовать с регулярными выражениями.
Legacy № 2: в контекстной рекламе были дубли ключевых слов. То есть в нескольких кампаниях могли встречаться одинаковые слова. Это, очевидно, проблема, которая решается парой кнопок в Коммандере или Редакторе Google Ads, но в нашем случае отключение дублей вызывало снижение дохода. Поэтому дубли оставили, а при подготовке отчета приходилось делать единый ключ из названия кампании и ключевого слова, чтобы данные о доходах подтягивались корректно.

Скрипт в Python будет приведен на примере Яндекс.Директа. Для других систем он не сильно отличался: нужно было менять названия столбцов — в разных системах они пишутся по-разному. Работать будем в среде Anaconda.

Построение отчета

Выгружаем данные в формате XLSX из рекламной системы и из базы данных. Не будем подробно останавливаться на этом этапе. Базы у всех разные, финансовые данные могут передаваться в Метрику. Как выгружать из Мастера отчета, все знают, — нужно просто выбрать необходимые нам кампании, выгрузить все столбцы, выбрав нужный период и в срезах кампанию и условие показа. На выходе получаем два файла Excel.

Загрузка данных в Python. Подгружаем нужные библиотеки и загружаем Excel-файлы в Anaconda.

import pandas as pd
import numpy as np
import warnings
warnings.filterwarnings('ignore')
# Открываем файл с помощью pd.read_excel
df = pd.read_excel(r'C:\Users\79299\Desktop\DS\Report\acc_yan_upd.xlsx')

df_cub = pd.read_excel(r'C:\Users\79299\Desktop\DS\Report\base_yan_upd.xlsx') # Открываем файл из базы

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

# Переименовываем столбцы для единообразия
df_cub = df_cub.rename(columns={'Utm_Campaign':'Campaign',
                   'Utm_Term':'Keyword',
                    'Сумма пополнений впервые пополненных ЛК (за месяц)':'НД',
                    'Впервые пополненные ЛК':'ПЛК',
                    'Пополнения':'Все деньги',
                    'Клиенты':'ЛК'}).drop(['Utm_Source'], axis=1)
# Отбираем только те столбцы которые нам понадобятся. Можно добавлять любые
columns_list = ['Кампания',
                   'Условие показа',
                   'Показы',
                   'Клики',
                   'CTR (%)',
                   'Расход (долл.)',
                   'Ср. цена клика (долл.)',
                   'Ср. позиция показов',
                   'Ср. объём трафика',
                   'Конверсия (%)',
                   'Цена цели (долл.)',
                   'Конверсии']
# При помощи loc оставляем [все строчки(:);наш список столбцов(columns_list)]
df = df.loc[:,columns_list]
# Переименовываем столбцы для единообразия
df.rename(columns={'Кампания':'Campaign',
                   'Условие показа':'Keyword',
                   'Показы':'Impressions',
                   'Клики':'Clicks',
                   'CTR (%)':'CTR',
                   'Расход (долл.)':'Cost',
                   'Ср. цена клика (долл.)':'CPC',
                   'Ср. позиция показов':'Avg_position',
                   'Ср. объём трафика':'Avg_traffic_vol',
                   'Конверсия (%)':'Convertion_rate',
                   'Цена цели (долл.)':'CPA',
                   'Конверсии':'Convertions'}, inplace=True)

Далее — небольшое техническое преобразование. Python определят тип некоторых столбцов как ‘object’, что означает — тип «строка». Проверить тип у всех столбцов можно, запустив в отдельной ячейке df.info(). Если есть столбцы, которые должны быть числом (‘int’) или числом с плавающей точкой (‘float’), а они ‘object’, это нужно исправить. Следующий код делает это.
```
#В квадратные скобки через запятую добавляем столбцы которые нужно заменить, 
# если их больше одного. #скобки должны быть двойными
df_obj = df[['CPC', 'Avg_position', 'Convertion_rate', 'CPA', 'Convertions', 'Avg_traffic_vol']]. \
    select_dtypes(include='object')

# Исправляем тип столбцов в цикле на тип 'float64'
for i in df_obj.columns:
    df[i] = df_obj[i].replace({'-':'0'}, regex=True).astype('float64')
```

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

# Блок кода для приведения к единообразию написания названий кампаний в df (Выгрузка Яндекса),
# и значений utm_Campaign в df_cub

df['Campaign'] = df['Campaign'].replace({'\|\|Dec2016':''}, regex=True) #удаляем ненужные хвосты в названиях
df['Campaign'] = df['Campaign'].str.replace('(([а-яА-Я_\|0123456789]+_?)+)\|?\|', '') #оставляем правую часть названий
df['Campaign'] = df['Campaign'].str.replace('(_new)$', '') #еще раз убираем ненужный мусор

# Заполнение пропусков в столбце Keyword для кампаний ремаркетинга
a = df[df['Campaign'].str.startswith('rmkt')]['Keyword']
df.loc[a.index,['Keyword']] = '--'

# Удаление лишнего в столбцах Keyword для остальных кампаний
df_cub['Keyword'] = df_cub['Keyword'].replace({"---":''}, regex=True). \
    replace({'(%.+)':''}, regex=True)
df['Keyword'] = df['Keyword'].replace({"'---":''}, regex=True). \
    replace({'(-[a-zA-Zа-яА-Я!\+"\.0123456789]+\s?)':''}, regex=True). \
    replace({'!|\+|\[|\]|"':''}, regex=True)

Попутно можно создавать новые столбцы, чтобы считать в них другие метрики, например: CPA, ROI, ДРР. Тут я считаю взвешенный CTR.
```
# Создание нового столбца для взвешенного CTR
df['wCTR'] = np.round((df['CTR']*df['Avg_traffic_vol'])/100, 2)
```

Создаем уникальный ключ «Кампания+ключевое слово». С его помощью мы будем группировать данные и объединять таблицы.

# В обоих файлах создаем столбцы с уникальным ключем Кампания+ключ
df['Campaign+Key'] = pd.Series(df['Campaign'] + '+++' + df['Keyword']).str.lower().str.strip(' ')
df_cub['Campaign+Key'] = pd.Series(df_cub['Campaign'] + '+++' + df_cub['Keyword']).str.lower().str.strip(' ')

Группировка и подсчет метрик в обоих файлах.

# Группируем по ключу файл из Яндекс Директа
df = df.groupby('Campaign+Key', as_index=False). \
    aggregate({'Impressions':np.sum, 
               'Clicks': np.sum,
               'wCTR': np.mean,
               'Cost': np.sum})

# Группируем по ключу файл из базы данных
df_cub = df_cub.groupby('Campaign+Key', as_index=False).aggregate({'ЛК':np.sum \
                                                                   , 'ПЛК':np.sum \
                                                                   , 'НД': np.sum \
                                                                   , 'Все деньги':np.sum \
                                                                   , 'ПЛК':np.sum})

Устанавливаем наш кастомный столбец как индекс в обоих файлах, объединяем и заполняем пропуски нулями.

#В обоих файлах устанавливаем наш кастомный ключ как индекс
df = df.set_index('Campaign+Key')
df_cub = df_cub.set_index('Campaign+Key')

df_f = df.join(df_cub, how='outer', on=df.index) # Объединяем таблицы
df_f = df_f.reset_index().drop('index', axis=1).rename(columns={'key_0':'Campaign_Key'}) # Удаляем индекс
df_f = df_f.fillna(0) # Заполняем пропуски нулями

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

# Разбиваем кастомный ключ обратно, на Кампанию и Ключ
df_f['Campaign'] = df_f['Campaign_Key'].apply(lambda x: x[0:x.find('+')])
df_f['Key'] = df_f['Campaign_Key'].apply(lambda x: x[x.find('+')+3:])

# Удаляем не нужное
df_f = df_f.reset_index().drop('index', axis=1)
df_f = df_f.drop('Campaign_Key', axis=1)

Последний шаг — записать результат в новый файл для отправки коллегам.

Скачать код скрипта можно на GitHub. P.S.: финансовые данные в исходных файлах изменены.

Загрузить код

Итого

В Excel сводить один отчет нужно было около 25 минут. Умножив это время на 30 отчетов, получим примерно 13 часов и добавим еще один час на выгрузку исходных Excel-файлов из рекламных систем и базы данных — всего около 14 часов.

После использования скрипта я по-прежнему трачу час на выгрузку, затем только меняю названия файлов в строке пути (второй пункт) и нажимаю на кнопку запуска. Уходит на это около часа. Экономия времени — 12–13 часов.

Конечно, нельзя взять скрипт и просто применить его к своему проекту. А вот взять идею и части кода не только можно, но и нужно. Если вы знакомы с базовым синтаксисом Python, освоить библиотеку Pandas для построения подобного скрипта не составит проблемы.

Если вы не знакомы с программированием, но много работаете с отчетами, возможно, стоит задуматься об автоматизации. Порой с ее помощью можно сэкономить даже больше 12 часов. Например, если выгружать Excel-файлы из рекламных систем с помощью API. Об этом уже рассказывали на ppc.world в этой статье.

Описание методов из библиотеки Pandas, которые я использовал, можно найти в справке.