Прогнозирование конверсии для ключевых слов

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

Темы статей:

1. Выгрузка данных из Google Analytics и Яндекс Метрики по API
2. Парсинг ключевых слов в Key Collector
3. Кластеризация запросов
4. Агрегирование данных
5. Прогнозирование конверсии для ключевых слов
6. Расчет ставок
7. Разработка заголовков
8. Разработка рекламных кампаний
9. Анализ эффективности

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

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

Выгрузка обучающей выборки

Нам нужно сделать обучающую выборку. Ранее мы делали выгрузку из API Метрики через Excel, сейчас нам нужно сделать тоже самое, но немного изменить запрос, поэтому просто копируем старый файл и в нем же редактируем запрос.

Открываем вкладку Data и Show Queries, и Edit

Нажимаем на шестеренку и меняем поля demensions и filters

	demensions = ym:s:lastSourceEngine,ym:s:startURLPathFull,ym:s:lastSearchPhrase

	filters = ym:s:lastSourceEngine=='organic.yandex' AND ym:s:lastSearchPhrase!~',брендовыйЗапрос1|брендовыйЗапрос2' AND ym:s:lastSearchPhrase!=null

Нажимаем Load и выгружаем запросы, у нас получилось 110 000 строк

Лемматизируем слова по старой схеме, с помощью функции VLOOKUP подставляем леммы к словам. Заметьте, что на 100 000 строк простая подстановка может занимать до 30 минут! Это первый аргумент в сторону более продвинутых инструментов для работы с данными, например, библитека Pandas на Python.

Возникли трудности?

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

В итоге мы получаем документ со следующими столбиками.

Теперь нам нужно задать столбикам определенный порядок и поменять названия в верхней строке. Получившийся лист сохраняем в отдельный файл с названием training_set.xlsx

Теперь рассмотрим сам процесс обучения модели. Под моделью мы подразумеваем словарь, где для каждого слова (именно слова, а не целой фразы) из выборки будет посчитан средневзвешенный показатель конверсии на URL. Объяснять расчеты было бы достаточно объемно в рамках данной статьи, поэтому выкладываем код на python и говорим, что нужно на входе и что на выходе.

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

# импортируем библиотеки
import pandas as pd
import numpy as np
from math import sqrt
import re

#  функция для обучения модели
def weighted_average_conversion_words(report):
    report = report.dropna().reset_index(drop=True)
    temp =[]

    for i in range(len(report)):
        print 'generating temp for abc ' + str( (float(i) / len(report)) * 100) + ' %'
        if report.loc[i, 'Keyword'] != None and type(report.loc[i, 'Keyword']) == unicode:
            for j in report.loc[i, 'Keyword'].split(' '):
                temp.append([j, report.loc[i, 'URL'], report.loc[i, 'Click'], report.loc[i, 'KPI']])

 
    words = pd.DataFrame(temp, columns=['Word', 'URL', 'Click', 'KPI'])
    words.loc[words.Click > 100, 'Click'] = 100
            
    words['WA_Word'] = words['KPI'] * ( words['Click']\
    / words.groupby(['Word', 'URL'])['Click'].transform(sum) )
        
    words['WA_Word'] = words.groupby(['Word', 'URL'])['WA_Word'].transform(sum)
    words['WA_Click'] = words.groupby(['Word', 'URL'])['Click'].transform(sum)
    words.loc[words.WA_Click > 100, 'WA_Click'] = 100
        
    words = words[['Word', 'URL', 'WA_Word', 'WA_Click']].drop_duplicates().reset_index(drop=True)
                
    return words
    
# функция для прогнозирования конверсии на основании модели
def predict_conversion_words(words, report):
    
    temp =[]
    for i in range(len(report)):
        print 'generating temp for prediction ' + str( (float(i) / len(report)) * 100) + ' %'
        if report.loc[i, 'Keyword'] != None and type(report.loc[i, 'Keyword']) == unicode:
            for j in report.loc[i, 'Keyword'].split(' '):
                temp.append([report.loc[i, 'Keyword'], report.loc[i, 'URL'], j])
    
    keywords = pd.DataFrame(temp, columns=['Keyword', 'URL', 'Word'])
    
    keywords.loc[:, 'KPI'] = 0
    keywords.loc[:, 'Click'] = 0
    keywords.loc[:, 'n_words'] = 0
    
    for i in range(len(keywords)):
        print 'prediction ' + str( (float(i) / len(keywords)) * 100) + ' %'
        if ((words['Word'] == keywords.loc[i, 'Word']) & (words['URL'] == keywords.loc[i, 'URL'])).any() == True:
            
#             print keywords.loc[i, 'Word'] + ' == ' \
#                 + words.loc[(words.Word == keywords.loc[i, 'Word']) & (words.URL == keywords.loc[i, 'URL']), 'Word']
#             print words.loc[(words.Word == keywords.loc[i, 'Word']) & (words.URL == keywords.loc[i, 'URL']), 'URL'] \
#                 + ' == '
#             print keywords.loc[i, 'URL']

            keywords.loc[i, 'KPI'] = float(words.loc[(words.Word == keywords.loc[i, 'Word']) & (words.URL == keywords.loc[i, 'URL']), 'WA_Word'])
            keywords.loc[i, 'Click'] = float(words.loc[(words.Word == keywords.loc[i, 'Word']) & (words.URL == keywords.loc[i, 'URL']), 'WA_Click'])
        else:
            keywords.loc[i, 'KPI'] = 0
            keywords.loc[i, 'Click'] = 0
            keywords.loc[i, 'n_words'] += 1
    
    
    keywords['WA_Keyword'] = keywords['KPI'] * ( keywords['Click']\
    / keywords.groupby(['Keyword', 'URL'])['Click'].transform(sum) )
    
    keywords['WA_Keyword'] = keywords.groupby(['Keyword', 'URL'])['WA_Keyword'].transform(sum)
    keywords['Click'] = keywords.groupby(['Keyword', 'URL'])['Click'].transform(sum)
    keywords['n_words'] = keywords.groupby(['Keyword', 'URL'])['n_words'].transform(sum)
    
    
    result = keywords[['Keyword', 'URL', 'WA_Keyword', 'Click', 'n_words']].drop_duplicates().reset_index(drop=True)
    
    return pd.merge(report, result, how='left', on=['Keyword', 'URL'])
    

# training_set - Excel файл для обучения модели с колонками Keyword_raw, Keyword, URL, Click, KPI
# data - Excel файл для прогнозирования с колонками ID, Keyword, URL

# исполняющая функция
def predict_conversion(training_set, data):
    
    # открываем обучающую выборку
    training_set = pd.read_excel(training_set)

    # расчитываем конверсию    
    training_set.KPI = training_set.KPI / training_set.Click
    
    # выодим на экран таблицу обучающей выборки
    print training_set
 
    # вызываем функцию для обучения модели
    words = weighted_average_conversion_words(training_set)

    # выодим на экран модель
    print words
    
    # сохраняем словарь в отдельный файл на будующее
    words.to_excel('words_wa.xlsx')

    # открываем словарь    
    words = pd.read_excel('words_wa.xlsx')

    # открываем данные для прогноза
    data = pd.read_excel(data)
    
    # выводим на экран таблицу с данными для прогноза
    print data

    # выполняем функцию прогнозирования на основании модели
    report = predict_conversion_words(words, data)

    # выводим рузультаты прогноза
    print report

    # сохраняем таблицу с прогнозами в отдельном файле
    report.to_excel('result.xlsx')    
    
# вызываем функцию и выполняем весь код
predict_conversion('training_set — копия.xlsx', 'data — копия.xlsx')

Помещаем 3 файла в одну директорию: predictin.py , training_set.xlsx и data.xlsx

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

Копируем фразы и ссылки из общего файла в отдельный и называем его data.xlsx

Но нам нужно создать уникальный ID, что бы потом подтянуть данные в общую таблицу. Соединяем фразу и URL.

Меняем заголовки и того мы имеем.

Теперь проверяем, чтобы все три файла были в одной директории.

И запускаем скрипт. У меня Mac OS и я использую приложение Terminal, у вас должны быть установлены библиотеки Python и Pandas.

После того, как скрипт отработает, а в моем случае это заняло 3 часа, в директории появится новый файл result.xlsx

В нем появятся 3 новых столбика:

Добавим новый столбик с формулой для подсчета количества слов в ячейке

	=IF(ISBLANK(A1);0;LEN(TRIM(A1))-LEN(SUBSTITUTE(A1;" ";""))+1)
	

И столбик отношения отсутствующих слов к общему количеству.

Теперь фильтрами исключим строки, где не хватает данных. Например, до 40 кликов по слову и от 60% отсутствующих слов. Это экспериментальные условия, для каждого кейса используются свои значения. Чтобы найти их, нужно экспериментировать.

И

Копируем слова в общую таблицу и подтягиваем данные в общую вкладку по ID, которую мы сделали ранее.

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

Берем данные из выборки, которую мы составили для обучения модели, и копируем во вкладку Organic.

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

Функцией VLOOKUP подтягиваем клики и конверсии для фраз в новые столбики. Как можно заметить - данных нам не хватает, так как мы выгружали выборку только за несколько месяцев, чтобы снизить влияние сезонности.

Все!

Пишите вопросы в комментариях, какие темы было бы интересно раскрыть подробнее? Если у вас есть идеи или советы, то делитесь!