Прогнозируем поисковую активность в Яндексе с помощью Wordstat и Google Таблиц > NetPeak - Независимость и осознанность
NetPeak Biz Tech    


Прогнозируем поисковую активность в Яндексе с помощью Wordstat и Google Таблиц

Прогнозируем поисковую активность в Яндексе с помощью Wordstat и Google Таблиц

< >

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

Алгоритм прогнозирования поисковой активности

  1. Вычисляем линейный тренд на основе данных за последние два года.
  2. Вычисляем коэффициент сезонности для каждого месяца на основе данных за последние 2 года.
  3. Усредняем коэффициент сезонности за последние два года.
  4. Корректируем линейный тренд на усредненный коэффициент сезонности.

Для наглядного примера построим прогноз поисковой активности на ближайшие три месяца по запросу «купить обогреватель». Для того, чтобы получить исходные данные, необходимые для построения прогноза, воспользуемся бесплатным сервисом от Яндекса Wordstat. В данном случае нас интересует раздел «История запросов». Далее в текстовом поле вводим запрос «купить обогреватель» и жмем «Подобрать». После этого будет сформирована таблица количества поисковых запросов «купить обогреватель» за последние два года. Данные из этой таблицы мы и будем использовать для построения прогноза. На самом деле из всей таблицы нас интересует поле «Абсолютное». Для того, чтобы очистить данные и оставить только нужное поле, необходимо перенести всю таблицу в любой процессор электронных таблиц. Для примера используем Google Таблицы, бесплатный и самый доступный инструмент. Создаем новую таблицу, после чего вставляем в нее скопированные из интерфейса Wordstat данные. Для этого достаточно просто выделить с помощью мыши таблицу в Wordstat, скопировать ее в буфер обмена с помощью контекстного меню и вставить в Google Таблицу. В результате в Google Таблице у нас будет вся необходимая для построения прогноза информация, осталось только очистить ее от мусора. Для этого удалим столбец C и строку 14, так как для расчетов они нам не понадобятся. Теперь у нас есть данные, необходимые для дальнейшего расчета прогноза. Для последующих вычислений давайте добавим в нашу таблицу колонки:

  • № наблюдения;
  • № наблюдения в году;
  • среднемecячное значение;
  • коэффициент сезонности;
  • усредненный коэффициент сезонности;
  • линейный тренд;
  • прогноз.

Таблица примет следующий вид: Теперь поочередно рассчитаем и заполним все колонки нашей таблицы, начиная с «№ Наблюдения». В данном случае каждый месяц для нас — отдельное наблюдение, в связи с чем достаточно просто пронумеровать строки от 1 до 27. До 27, потому что у нас есть данные за прошедшие 24 месяца, и на 3 следующих мы будем строить прогноз. Столбец «№ наблюдения» содержит скользящую нумерацию. Поле «Номер наблюдения в году» содержит информацию по годам (в данном случае мы используем не календарный год с января по декабрь, а год как отрезок времени в 12 месяцев), в связи с чем эта колонка будет заполнена числами от 1 до 12, повторяясь до конца таблицы. В результате эти поля будут выглядеть так: Для того, чтобы заполнить столбец «Среднемecячное значение», воспользуйтесь формулой: “=if(C2<13;average($2:$13) ;average($14:$25))”. Дальше необходимо рассчитать коэффициент сезонности. Для этого необходимо разделить среднемecячный показатель на абсолютное значение. В нашем случае это делается с помощью формулы “=B2/E2”. Для расчета среднего коэффициента сезонности воспользуемся функцией AVERAGEIF:“=averageif($2:$25;D2;$2:$25)”. Для заполнения поля «Линейный тренд» в Google Таблицах существует функция FORECAST, которая, используя линейную регрессию, определяет предполагаемое значение Y, исходя из существующего значения X. В нашем случае X — это номер наблюдения, а Y — значение поля «Абсолютное». Так как столбец «Линейный тренд» необходим только для расчета прогноза, его стоит заполнять, начиная с 26 строки. Данные до 26 строки — не прогнозные, а фактические. Формула для расчета линейного тренда выглядит так: “=forecast(C26;$2:$25;$2:$25)”. Для построения прогноза нам остается скорректировать линейный тренд на средний коэффициент сезонности. Каждый месяц имеет свой коэффициент сезонности, в связи с чем необходимо воспользоваться функцией VLOOKUP, чтобы подтянуть в расчет коэффициент сезонности нужного месяца. Формула для вычисления прогноза “=H26*vlookup(D26;$2:$25;4;0)”. Заполнять столбец «Прогноз» надо также с 26 строки. В результате всех проделанных вычислений должна получиться такая таблица: В столбце «Прогноз» мы рассчитали прогнозируемый объем поисковых запросов по фразе «купить обогреватель» на ближайшие три месяца. Формулы для расчета столбцов:

  1. Среднемecячное значение “=if(C2<13;average($2:$13) ;average($14:$25))”, заполняется со 2 по 25 строку, в столбце E.
  2. Коэффициент сезонности “=B2/E2”, заполняем со 2 по 25 строку в столбце F.
  3. Средний коэффициент сезонности “=averageif($2:$25;D2;$2:$25)”, заполняем со 2 по 25 строку в столбце G.
  4. Линейный тренд “=forecast(C26;$2:$25;$2:$25)”, заполняем с 26 по 28 строку в столбце H.
  5. Прогноз = “H26*vlookup(D26;$2:$25;4;0)”, заполняем с 26 по 28 строку в столбце I.

Формулы для тех, кто привык работать в Excel:

  1. Среднемecячное значение “=ЕСЛИ(C2<13;СРЗНАЧ($2:$13) ;СРЗНАЧ($14:$25))”, заполняется с 2 по 25 строку, в столбце E.
  2. Коэффициент сезонности “=B2/E2”, заполняем со 2 по 25 строку в столбце F.
  3. Средний коэффициент сезонности “=СРЗНАЧЕСЛИ($2:$25;D2;$2:$25)”, заполняем со 2 по 25 строку в столбце G.
  4. Линейный тренд “=ПРЕДСКАЗ(C26;$2:$25;$2:$25)”, заполняем с 26 по 28 строку в столбце H.
  5. Прогноз = “=H26*ВПР(D26;$2:$25;4;0)”, заполняем с 26 по 28 строку в столбце I.

Также мы можем графически показать этот расчет на гистограмме или графике. Для этого выделите паралельно два диапазона \\\'Лист1\\\'!B1:B28, \\\'Лист1\\\'!I1:I28 и, используя меню «Вставка» => «Диаграмма», постройте нужный вам тип диаграммы, например, гистограмму. Если у вас не получилось построить прогноз по написанной инструкции, вы можете открыть Google Docs, который приведен в качестве примера в этой статье, и скопировать его на свой Google Диск («Файл» => «Создать копию»). В принципе, в столбец «Абсолютное» можно внести данные не только о поисковой активности, но также и о продажах или о количестве кликов, если у вас есть эти данные за последние 24 месяца.

Проверяем алгоритм прогноза

Описанный алгоритм я протестировал на пяти поисковых запросах в поисковых системах Google и Яндекс. Список запросов для теста:

  • купить кондиционер;
  • поступление в институт;
  • тур в Египет;
  • купить обогреватель;
  • купить учебники.

У всех выбранных для теста запросов — ярко выраженная сезонность с пиком как в летний, так и в зимний период. Тест проходил в течении трех месяцев. Отклонения прогноза от фактических данных по перечисленным выше поисковым запросам составили не более 37%. Графики факт/прогноз для Яндекса: Как видно из представленных графиков, такой подход к построению прогноза имеет право на жизнь. Он достаточно прост и результат его работы находится в пределах допустимой погрешности.



Комментарии:

Как нанять маркетолога и не уволить через месяц

Как нанять маркетолога и не уволить через месяц Краткая инструкция по поиску идеального кандидата, потому что (как видим) многим на рынке все еще сложно найти хорошего маркетолога. Да и понять, стоит ли вкладывать в сотрудника силы, время, средства с первой встречи достаточно сложно....

17 04 2024 2:46:37

Семь улучшений Netpeak: новые услуги и веб-аналитика для крупного бизнеса. Выпуск шестой

Семь улучшений Netpeak: новые услуги и веб-аналитика для крупного бизнеса. Выпуск шестой Рассказываем в подробностях, что полезного сделал Netpeak для своих клиентов с января по апрель 2017 года...

16 04 2024 12:40:13

Как выбрать шрифт — инструкция для маркетологов

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

15 04 2024 16:59:50

Песочница Netpeak: бесплатная площадка для ваших статей

Рассказываем о том, что такое Песочница, как сюда писать и получать больше аудитории для своего бизнеса...

14 04 2024 17:37:32

Что такое логическая структура сайта и зачем она нужна

Что такое логическая структура сайта и зачем она нужна Базовые знания, которые нужны, чтобы ресурс понравился поисковикам и пользователям....

13 04 2024 2:38:16

Семь улучшений Netpeak: создаем бизнес-страницу в Instagram, на Facebook, делаем email-маркетинг и публикуем истории бизнеса

Семь улучшений Netpeak: создаем бизнес-страницу в Instagram, на Facebook, делаем email-маркетинг и публикуем истории бизнеса Настроенная бизнес-страница компании в Instagram и на Facebook помогает в продвижении товаров и услуг в интернете. Поэтому так важно сделать их seo-friendly. Читать!...

12 04 2024 10:58:56

45 советов по мобильному маркетингу от Mobile Growth Experts

45 советов по мобильному маркетингу от Mobile Growth Experts Лучшие фишки продвижения мобильных приложений в адаптированном переводе отчета Branch.io...

11 04 2024 11:51:46

Как я работаю дома с двумя детьми — хроники безумного папы

Как я работаю дома с двумя детьми — хроники безумного папы Опыт сотрудника отдела маркетинга Netpeak. Дочке Маше — 10 лет, сыну Егору — почти 4 года. Мы по-прежнему не очень продуктивны в таких условиях, но все же накопили больше опыта, чем те люди, которые сейчас внезапно столкнулись с удаленной работой...

10 04 2024 9:50:31

20 инструментов SEO-копирайтинга — полезные программы и сервисы

20 инструментов SEO-копирайтинга — полезные программы и сервисы 20 сервисов для прокачки контента под SEO. Штуки, которые будут полезны новичкам и экспертам....

09 04 2024 18:29:43

Как реклама в Facebook и Google Ads принесла образовательному центру на 98% больше регистраций

Как реклама в Facebook и Google Ads принесла образовательному центру на 98% больше регистраций Рекламируем онлайн-уроки к 1 сентября. Именно сочетание Facebook и Google Ads помогло нам сначала обратиться к родителям (которые, например, листают соцсеть по пути домой) и потом уже напомнить о себе с помощью Google Рекламы...

08 04 2024 2:46:49

ТОП-11 бесплатных курсов от Netpeak

ТОП-11 бесплатных курсов от Netpeak Уроки, написанные понятным и доступным языком экспертами по контекстной рекламе, поисковому продвижению, SMM....

07 04 2024 21:36:37

Как добавить компанию на карты Google и Яндекс

Как добавить компанию на карты Google и Яндекс Как добавить данные о компании на карты Google и Яндекс....

06 04 2024 16:53:13

Engagement Rate в email-маркетинге: что это, как посчитать и повысить

Engagement Rate в email-маркетинге: что это, как посчитать и повысить Определяем, кто из mail подписчиков предан вашему бренду по-настоящему....

05 04 2024 3:30:19

Почему Google Merchant Center может заблокировать аккаунт

Почему Google Merchant Center может заблокировать аккаунт На что обращает внимание система и менеджеры Google при проверке сайта и аккаунта....

04 04 2024 4:33:12

Оптимизация скорости сайта: как уменьшить время ответа сервера

Оптимизация скорости сайта: как уменьшить время ответа сервера Как уменьшить время ответа сервера и повысить общий показатель скорости загрузки сайта...

03 04 2024 9:44:49

Правила группировки фраз для семантического ядра сайта

Правила группировки фраз для семантического ядра сайта Грамотная группировка поисковых запросов позволяет максимально увеличить поисковой трафик....

02 04 2024 9:49:40

«Демография» и «Технология». Сравнение отчетов в GA4 и Google Universal ***ytics

Решили перейти на Google ***ytics 4? Предлагаем разобраться, чем отличается инструмент от предыдущей версии и изучить новинки в отчетах «Демография» и «Технология»....

01 04 2024 8:22:56

Анализ ссылочной массы с помощью Ahrefs и Serpstat

Краткое сравнение двух инструментов и третьего на закуску....

31 03 2024 21:33:49

Как настроить божественные списки ремаркетинга с помощью GTM

Как настроить божественные списки ремаркетинга с помощью GTM Несколько фишек Google Tag Manager, чтобы сделать продвинутые списки ремаркетинга....

30 03 2024 4:21:20

Pitch perfect: как получить публикации в топовых медиа

Pitch perfect: как получить публикации в топовых медиа Если у вас есть продукт, о котором миру стоит узнать, книга Pitch perfect придется вам очень кстати....

29 03 2024 22:32:20

«Волшебный пинок»: вдохновение от знаменитостей. 11 мотивирующих речей

«Волшебный пинок»: вдохновение от знаменитостей. 11 мотивирующих речей Джефф Безос, Стив Джобс, Билл Гeйтс, Джимм Керри и другие. Собрали для вас подборку крутых выступлений людей, которые знают, как добиться ... в общем-то, всего. Читайте их основные тезисы, а главное — послушайте....

28 03 2024 18:49:42

Как работать с приложениями в Google Play Developers Console

Как работать с приложениями в Google Play Developers Console Как узнавать эффективность страницы мобильного приложения в Play Маркете? Учимся работать в Google Play Developers Console....

27 03 2024 15:44:41

Продвижение Telegram-канала с помощью агентства Netpeak: чтоооооо???

Продвижение Telegram-канала с помощью агентства Netpeak: чтоооооо??? Новая услуга для бизнесов, которым большое количество подписчиков не приносит продаж...

26 03 2024 2:34:59

5 железных правил IT-бухгалтеров

5 железных правил IT-бухгалтеров Бухгалтерский мир Netpeak: цель, задачи, мотивация....

25 03 2024 1:31:58

Как мотивировать клиентов оставить отзыв

Как мотивировать клиентов оставить отзыв Лестные отзывы от клинта в интернете невозможно переоценить. Если вы владелец малого бизнеса и только разpaбатываете и внедряете свою маркетинговую стратегию, книга «По рекомендации» будет вам полезной...

24 03 2024 23:55:58

Продвижение мобильного приложения в Яндекс.Директ — руководство для новичков

Продвижение мобильного приложения в Яндекс.Директ — руководство для новичков Детальная инструкция по настройке рекламы мобильного приложения в Яндекс.Директ....

23 03 2024 23:37:39

Big Money: как Netpeak Group связана с Сингапуром

Big Money: как Netpeak Group связана с Сингапуром Всё о крупной рыбе украинского интернет-маркетинга: компании, люди, кейсы в интервью с основателем Артёмом Бородатюком....

22 03 2024 6:31:31

Как мы нашли модель аналитики для SaaS-проекта UniSender.com

Как мы нашли модель аналитики для SaaS-проекта UniSender.com Что делать, если нет четкой модели оценки эффективности контекстной рекламы?...

21 03 2024 23:30:22

Продвижение интернет-магазина с помощью Google Рекламы: первые шаги

Продвижение интернет-магазина с помощью Google Рекламы: первые шаги Какие типы кампаний и ключевых слов стоит первыми запускать в Google Рекламе? Советы новичкам...

20 03 2024 2:11:57

Импорт данных из Google ***ytics в Excel с помощью ***ytics Edge — подробное руководство

Импорт из Google ***ytics в Excel — manual по ***ytics Edge....

19 03 2024 15:38:42

Как быстро и бесплатно импортировать в Google Таблицы статистику из Facebook

Понадобится всего пару минут, чтобы создать отчет о тратах, количестве конверсий или любых других важных показателях. Понятная инструкция для PPC-специалистов и про возможности и ограничения бесплатной версии расширения. Читайте дальше!...

18 03 2024 22:34:43

Как приводить трафик на молодой сайт: результаты обсуждения темы на Quora

Как приводить трафик на молодой сайт: результаты обсуждения темы на Quora Повышение посещаемости на сайте: контент, seo, ppc и многое другое...

17 03 2024 5:51:32

Пять вопросов для проверки софт-скиллз в IT

Пять вопросов для проверки софт-скиллз в IT Как узнать, что потенциальный сотрудник креативный, умеет убеждать, работать в комaнде, адаптироваться к быстрой смене условий, владеет эмоциональным интеллектом? Вопросы для проверки этих софт-скиллов здесь....

16 03 2024 12:25:55

Ежемecячная подписка в Telegram — что это значит и кому может быть полезна

Ежемecячная подписка в Telegram — что это значит и кому может быть полезна Субъективный рейтинг новых функций мессенджера от Head of Telegram Network в Netpeak....

15 03 2024 4:44:11

Как создать чат-бота для сайта

Чат-боты упрощают процесс покупки продуктов онлайн. Netflix, Adidas, British Airways и другие крупные компании уже внедрили ботов в свои маркетинговые системы. Так ли это эффективно, если компании начинают отказываться от операторов в пользу ботов?...

14 03 2024 22:25:46

Как настроить прослушивание кликов в Google Tag Manager 2.0

Как настроить прослушивание кликов в Google Tag Manager 2.0 Бонус в статье: как собрать аудитории для ремаркетинга на брошенную корзину...

13 03 2024 16:30:24

Востребованные IT-профессии в Украине — все кроме программирования. Какие курсы выбрать в 2022 году

Востребованные IT-профессии в Украине — все кроме программирования. Какие курсы выбрать в 2022 году Рассказываем об интересных диджитал-специальностях и зарплатах, как обучиться этим профессиям с нуля (в том числе в зрелом возрасте) и можно ли работать удалённо....

12 03 2024 18:59:35

14 неочевидных и пpaктически применимых фишек — круглый стол 8P 2020

Лучшие фишки от спикеров конференции 8P 2020. В конце каждой конференции спикеры 8P собираются за круглым столом, чтобы поделиться пpaктически применимыми советами, которые можно внедрить в свой проект и сразу почувствовать отдачу...

11 03 2024 3:24:48

Как написать follow up, чтобы от него был результат?

Памятка для всех, кому важно сохранить детали переговоров....

10 03 2024 7:10:40

Кейс Office-Expert.kz. Как дополнительно стимулировать покупки на 50 млн тенге в месяц

Кейс Office-Expert.kz. Как дополнительно стимулировать покупки на 50 млн тенге в месяц Мы решили внедрить товарные рекомендации на сайте. Они приносят дополнительно до 10% дохода....

09 03 2024 4:25:56

Каким должен быть дизайн продающей страницы

Каким должен быть дизайн продающей страницы Обязательные элементы продающей страницы: удобный и красивый дизайн, легко читаемые форматы и стили шрифтов, текстовые блоки, кнопки призыва к действию. Узнать больше!...

08 03 2024 8:58:10

8 секретов крутых заголовков для 1000 репостов в соцсетях

8 секретов крутых заголовков для 1000 репостов в соцсетях Придумать цепляющие заголовки для 1000 репостов в соцсетях — что можно узнать в результате анализа миллиона заголовков в блогах....

07 03 2024 17:34:53

Анализ пути в Google ***ytics 4: как ускорить процесс покупки на сайте

Анализ пути в Google ***ytics 4: как ускорить процесс покупки на сайте Анализ пути — это как многокaнaльные последовательности в Universal ***ytics, только лучше....

06 03 2024 20:45:23

Как оптимизировать YouTube-канал

Как оптимизировать YouTube-канал Инструкция по технической подготовке к продвижению...

05 03 2024 18:43:14

Как заказать эффективную рекламу у Instagram-блогеров за $15-30

Как заказать эффективную рекламу у Instagram-блогеров за $15-30 Обсуждаем, как попробовать инфлюенсер-маркетинг без особого бюджета...

04 03 2024 7:57:46

Как я работаю: рассказ тимлида комaнды управления онлайн-репутацией

Как я работаю: рассказ тимлида комaнды управления онлайн-репутацией Как настроить работу удаленной комaнды сотрудников и успевать выполнить все задачи...

03 03 2024 1:33:45

Как редактировать скрипты Google Ads

Как редактировать скрипты Google Ads Пpaктика: где искать шаблоны скриптов, как их редактировать и какие есть меры предосторожности при работе со скриптами....

02 03 2024 16:34:45

Как создать портрет покупателя — руководство для бизнеса от SEMrush

Как создать портрет покупателя — руководство для бизнеса от SEMrush Как создать портрет покупателя и зачем он нужен бизнесу....

01 03 2024 23:59:11

Владимир Усов: о цифровом сторителлинге XXI века и конструкторском бюро идей Gutenbergz

Владимир Усов: о цифровом сторителлинге XXI века и конструкторском бюро идей Gutenbergz Красивый слог и сюжет должны быть в интеpaктивной оболочке, соответствующей времени. Проект Gutenbergz идет к созданию именно такой — идеальной истории для людей XXI века. О пути к этой цели, топе App Store и успехе на Kickstarter мы поговорили с руководи...

29 02 2024 5:14:34

Как настроить запуск R-скрипта по расписанию?

Как настроить запуск R-скрипта по расписанию? Работаете со скриптами R регулярно? Потратьте десять минут на настройку автозапуска и избавьтесь от необходимости активировать R-скрипты вручную...

28 02 2024 7:12:53

Еще:
понять и запомнить -1 :: понять и запомнить -2 :: понять и запомнить -3 :: понять и запомнить -4 :: понять и запомнить -5 :: понять и запомнить -6 :: понять и запомнить -7 ::