UnionCreated using FigmalectureCreated using Figma
new

PPC CONF: 30 докладов об успехах и провалах в интернет-маркетинге Подробнее

7924 https://ppc.world/uploads/images/f9/7b/60c9c1b980451-fidias-cervantes-oHW-2VFNg-Q-unsplash.jpg 2021-06-17 Другое ppc.world 160 31

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

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

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

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

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

  1. По регионам: компания присутствовала в шести регионах.

  2. По рекламным системам: Яндекс.Директ, Google Ads, «ВКонтакте», myTarget и Facebook Ads.

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

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

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

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

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

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

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

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

  2. Загрузка данных в 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') # Открываем файл из базы
    
  3. Теперь отбираем и переименовываем нужные столбцы в обеих выгрузках.

    Отбор столбцов в выгрузках

    # Переименовываем столбцы для единообразия
    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)
    
  4. Далее — небольшое техническое преобразование. 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')
    
  5. Начнем приводить названия кампаний к одному виду, попутно удаляя лишнее из ключей. Это пункт необязателен, его применение зависит от того, есть ли порядок в 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)
    
  6. Попутно можно создавать новые столбцы, чтобы считать в них другие метрики, например: CPA, ROI, ДРР. Тут я считаю взвешенный CTR.

    Создание столбца для взвешенного CTR

    # Создание нового столбца для взвешенного CTR
    df['wCTR'] = np.round((df['CTR']*df['Avg_traffic_vol'])/100, 2)
    
  7. Создаем уникальный ключ «Кампания+ключевое слово». С его помощью мы будем группировать данные и объединять таблицы.

    Ключ + кампания

    # В обоих файлах создаем столбцы с уникальным ключем Кампания+ключ
    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(' ')
    
  8. Группировка и подсчет метрик в обоих файлах.

    Подсчет метрик в файлах

    # Группируем по ключу файл из Яндекс Директа
    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})
    
  9. Устанавливаем наш кастомный столбец как индекс в обоих файлах, объединяем и заполняем пропуски нулями.

    Кастомный столбец как индекс

    #В обоих файлах устанавливаем наш кастомный ключ как индекс
    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) # Заполняем пропуски нулями
    
  10. Для удобства чтения отчета разбиваем кастомный ключ обратно на кампанию и ключевое слово, больше он нам не потребуется. Удаляем ненужное.

    разъединение ключа и кампании

    # Разбиваем кастомный ключ обратно, на Кампанию и Ключ
    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)
    
  11. Последний шаг — записать результат в новый файл для отправки коллегам.

    Запись отчета в файл

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

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

Итого

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

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

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

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

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

Сообщить об опечатке

Текст, который будет отправлен нашим редакторам: