Блог про интернет-маркетинг для бизнеса

Аналитика

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

85
4
11
46

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комментарии (14)

  1. 0
    2 месяца назад
    Добрый день. Корректно ли прогнозировать таким образом базовый трафик на сайт, если клиент хочет прогноз трафика с продвижение и без? или может подскажите как вы это делаете?
  2. 1
    3 месяца назад

    Интересный вариант расчета при помощи таблиц. Однако есть более легкий способ максимально быстро и эффективно построить диаграмму - это Key Collector.


    Нажатием одной кнопки - "Сбор данных сезонности из сервиса Yandex/Wordstat".  

    Там можно выводить не только по месяцам но и по неделям данные. 

    График сезонности в Key Collector

  3. 0
    4 месяца назад
    А потом проверяли данные исходя из фактических показателей вордстата по значению абсолютное?
  4. 0
    год назад

    Попробуйте в помощью вашей модели получить прогноз по запросу "подарок на 8 марта". У меня получились отрицательные значения. В чём причина?

    • 0
      Николай Красавцев
      год назад

      В данном случае прогноз строится некорректно в связи с тем, что линейный тренд уходит в отрицательные значения:
      http://img.netpeak.ua/alsey/145388365251_kiss_28kb.jpg

      В связи с чем вам данная модель прогноза не подходит, я советую использовать в данном случае Microsoft Excel 2016 и функцию "Лист прогноза", все ссылки на использования данного функционала я указал в ответе предыдущему комментатору.

      Microsoft Excel 2016 строит следующий прогноз по запросу "подарок на 8 марта":
      http://img.netpeak.ua/alsey/145388388200_kiss_46kb.jpg

  5. 0
    год назад

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

    • 0
      Александр
      год назад

      Этого нельзя сделать простыми инструментами, о которых говорится в статье, то, о чём вы говорите, является достаточно дорогостоящим исследованием, на данный момент есть компании, которые могли бы решить подобную задачу, но используя свои разработки, а не бесплатные инструменты, вроде WordStat и Google SpreadSheets.

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

  6. 0
    год назад

    "Отклонения прогноза от фактических данных по перечисленным выше поисковым запросам составили не более 37%", - вы это имеете в виду, когда говорите "результат его работы находится в пределах допустимой погрешности"?

    • 0
      sstass
      год назад

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

      • 1
        Александр
        год назад

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

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

    • 0
      sstass
      год назад

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

Чтобы оставить комментарий, необходимо авторизироваться

Подписаться

на самую полезную рассылку по интернет-маркетингу

Самое

обсуждаемое популярное читаемое