Рассчитываем ставки

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

Темы статей:

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

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

В прошлых статьях мы собирали, выгружали, парсили и агрегировали данные, а в это статье поговорим о том, как управлять ставками в Директе используя все эти данные.

В каждом кейсе для назначения ставок в директе используются разные данные, но принципы одни и те же. Как мы говорили ранее, в основе лежит простая формула:

	Ставка = Средний чек * Доля маржи в чеке * Конверсия сайта

То есть, для того чтобы рассчитать оптимальную ставку в директе по ключевому слову, нам нужно взять его конверсию и средний чек, но что если у нас по слову всего 10 кликов и ни одной продажи? Тогда берем показатели среднего чека и конверсии от категории.

Например, возьмем сегмент слов игрушек Лего, мы знаем, что в среднем по категории конверсия составляет 2% и средний чек 3600 рублей, однако, если взять конкретные ключевые слова, например «купить лего дупло», то средний чек будет 9000 рублей, а конверсия 4%. Давайте попробуем рассчитать эти два случая по нашей формуле при условии, что наша маржа в среднем чеке составляет 30%:

	0,02 * 3600 * 0,3 = 21,5 руб

	0,04 * 9000 * 0,3 = 108 руб

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

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

Разница в 5 раз! Так вот вся эта погрешность будет съедать вашу прибыль. Укажите ставку больше - уйдете в минус, меньше - недополучите прибыль. Как быть если по категории у нас 1000 кликов, а по ключевому слову всего 10 кликов. Нам нужен какой-то механизм, который будет уравновешивать значение данных. Этот механизм называется пуллинг и его формула:

	Количество конверсий у слова + 1 / Количество кликов у слова + (1/ Прогнозное значение конверсии) = Конверсия слова

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

Продолжим пример выше, в качестве прогнозного значения категории мы ставим конверсию категории Лего

	(10 * 0,04) + 1 / 10 + (1 / 0,02) = 1,4 / 60 = 0,023 

Так уже лучше! А что, если бы у ключевого слова было 80 кликов?

	(80 * 0,04) + 1 / 80 + (1 / 0,02) = 4,2 / 130 = 0,032
	

То есть чем больше кликов получает ключевое слово, тем оно менее зависимо от конверсии категории.

Окей, с конверсией разобрались, а что делать со средним чеком? Используем аналогичную формулу пуллинга.

	(Средний чек слова * Количество транзакций слова + 0,5 * Средний чек категории) / Количество транзакций слова + 0,5

Пример с Лего при 80-ти кликах.

	(9000 * (80 * 0,02) + 0,5 * 3600) / (80 * 0,02) + 0,5 = 16 200 / 2,1 = 7 700 руб

Как вы видите средний чек тоже начал сглаживаться с ростом кликов. Теперь нам нужно как-то все наши новые знания применить на деле с помощью формул Excel. Каждый проект требует разных расчетов, так как данные могут отличаться, рассмотрим кейс с нашими данными.

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

Рассчитаем оценочную конверсию

Начинаем конструкцию с того, что известно всегда - средняя конверсия сайта и средняя конверсия по категории/URL

	=(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта))

	=IFNA(

		(Количество конверсий по URL + 1 ) / (Количество кликов по URL + (1/ Средняя конверсия сайта));

		Средняя конверсия сайта

	)
	

Добавляем сценарий наличия данных прогнозной конверсии

	=IFNA(

		(Прогнозная конверсия + 1 ) / (Прогнозное количество кликов + (1/ 

			IFNA(

				(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

				Средняя конверсия сайта

			)
		));

		IFNA(

			(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

			Средняя конверсия сайта

		)

	)
	

Страшно? Да, ну а что поделать, зато все это работает и работает в простом Excel! Добавляем еще 2 этажа, когда имеются данные из Яндекс Директа и органического поиска.

	=IFNA(
		
		(Количество конверсий с Директа + 1 ) / (Количество кликов с Директа + (1/ 

			IFNA(

				(Количество конверсий с поиска + 1 ) / (Количество кликов с поиска + (1/ 

					IFNA(

						(Прогнозная конверсия + 1 ) / (Прогнозное количество кликов + (1/ 

							IFNA(

								(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

								Средняя конверсия сайта

							)
						));

						IFNA(

							(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

							Средняя конверсия сайта

						)

					)
				));

				IFNA(

					(Прогнозная конверсия + 1 ) / (Прогнозное количество кликов + (1/ 

						IFNA(

							(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

							Средняя конверсия сайта

						)
					));

					IFNA(

						(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

						Средняя конверсия сайта

					)

				)

			)

		));

		IFNA(

			(Количество конверсий с поиска + 1 ) / (Количество кликов с поиска + (1/ 

				IFNA(

					(Прогнозная конверсия + 1 ) / (Прогнозное количество кликов + (1/ 

						IFNA(

							(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

							Средняя конверсия сайта

						)
					));

					IFNA(

						(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

						Средняя конверсия сайта

					)

				)

			));

			IFNA(

				(Прогнозная конверсия + 1 ) / (Прогнозное количество кликов + (1/ 

					IFNA(

						(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

						Средняя конверсия сайта

					)
				));

				IFNA(

					(Количество конверсий URL + 1 ) / (Количество кликов URL + (1/ Средняя конверсия сайта));

					Средняя конверсия сайта

				)

			)

		)

	)
	

Все! Данная формула рассматривает все сценарии с данными из 5-ти источников и высчитывает оценочную конверсию для слова.

Чтобы использовать формулу в нашем кейсе, копируем ее в текстовый редактор и подставляем индексы ячеек через поиск/замену.

Вставляем в Excel

Теперь посчитаем средний чек, здесь по-проще, всего 2 вариации

	=IFNA(

		(Средний чек URL * Количество транзакций URL + 0,5 * Средний чек сайта) / (Количество транзакций URL + 0,5);

		Средний чек сайта

	)
	

И в конце умножим все производные и рассчитаем ставку

			=Прогнозная конверсия * Прогнозный средний чек * Доля маржи в среднем чеке
			

Добавим условие, что, если сайт входит в поиске ТОП3, то показы отключаем из за каннибализации трафика. И условие максимальной ставки.

	=IF(

		IF(

			Позиция > 3;

			Прогнозная конверсия * Прогнозный средний чек * Доля маржи в среднем чеке;

			1
		) > Максимальная ставка;

		Максимальная ставка;

		IF(

			Позиция > 3;

			Прогнозная конверсия * Прогнозный средний чек * Доля маржи в среднем чеке;

			1
		)

	)
	

Все, ставки рассчитаны! О том, как настроить ставки в самом Яндекс Директе поговорим, когда будем загружать кампании.

Стоит заметить, что мы везде использовали один и тот же уровень пуллинга - 1, что математически является неправильно. Уровень пуллинга вычисляется для каждого источника данных путем перебора до минимизации погрешности на 2-х периодах, но это более сложный подход, который в рамках статьи рассматриваться не будет.

Второй момент, это погрешность, у нас есть строки, где данных либо совсем нет или есть , но очень мало. Давайте создадим столбик Error и сложим в нем все клики из всех источников данных.

Чтобы наглядно представить картину, давайте отсортируем столбик по убыванию и построим график.

Как мы видим, у трети слов прогноз конверсии составлен из менее, чем 100 кликов Хотя мы брали только запросы с конверсиями, на первый запуск имеет смысла такие запросы отключить.

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