Создание отчетов — неизбежная часть работы специалиста по рекламе. Отчеты должны быть регулярными, корректными и в идеале содержать только ту информацию, которую должен видеть получатель. А ведь бывает так, что в одной компании много отделов или региональных подразделений, и каждый менеджер хочет видеть только статистику, например, своего региона.
Проблема, которую нужно было решить
Когда я работал специалистом по трафику 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:Users79299DesktopDSReportacc_yan_upd.xlsx') df_cub = pd.read_excel(r'C:Users79299DesktopDSReportbase_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, которые я использовал, можно найти в справке.