Алексей Филатов, 18 Февраля 2017

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

Продолжаем цикл статей на тему автоматизации разработки рекламных кампаний для интернет-магазина: прогнозируем конверсию!


После прочтения статей вы научитесь: оптимизировать кампании в 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

predictin.py, это кусок кода, который находится выше - скопируйте его и сохраните в соответствующий файл

training_set.xlsx, это файл с обучающей выборкой, который мы выгрузили и подготовили в данной главе

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

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

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


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


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


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


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


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


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

WA_keyword - прогнозное значение конверсии слова

Click - количество кликов, на основании которых был сделан прогноз

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

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



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

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


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


И


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


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

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


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


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


Все!

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

Хочешь уведомления
о новых постах?
Никакого спама — честно :)
Другие материалы по теме
Как построить dash-board в Google Data Studio с данными из Google Analytics? HOWTO
Как построить dash-board в Google Data Studio с данными из Google Analytics?
Что вы делаете, когда хотите посмотреть основные показатели по рекламным кампаниям? — Заходите в Google Analytics и хаотично просматриваете все стандартные отчеты или смотрите «кастомные отчеты» по ключевым словам, в уме сводя необходимые цифры. — Решение внутри!
Как управлять ставками в Яндекс Директ и Google Adwords? HOWTO
Как управлять ставками в Яндекс Директ и Google Adwords?
Как управлять ставками в Яндекс Директ и Google Adwords? Пример работы аукциона. Что делаеют оптимизаторы конверсий?
Рассчитываем ставки Часть 6
Рассчитываем ставки
Продолжаем цикл статей на тему автоматизации разработки рекламных кампаний для интернет-магазина: рассчитываем ставки!
Импорт рекламных кампаний в Директ Коммандер Часть 8
Импорт рекламных кампаний в Директ Коммандер
Продолжаем цикл статей на тему автоматизации разработки рекламных кампаний для интернет-магазина: импортируем кампанию!
Пишем заголовки в Яндекс Директ Часть 7
Пишем заголовки в Яндекс Директ
Продолжаем цикл статей на тему автоматизации разработки рекламных кампаний для интернет-магазина: составляем заголовки!
Прогнозирование конверсии для ключевых слов Часть 5
Прогнозирование конверсии для ключевых слов
Продолжаем цикл статей на тему автоматизации разработки рекламных кампаний для интернет-магазина: прогнозируем конверсию!
читать наш блог
Расскажите о своем проекте!
Мы все продумаем и сделаем
Отправить заявку!
Пишите на — info@1jam.ru , звоните в скайпе — jam.agency , или по телефону — 8 (499) 112-36-92