Введение
Модель, которую я буду описывать ниже, показывает, как SEO влияет на небольшой объем данных.
Представим такую ситуацию. Вы сидите на встрече с клиентом, презентуете ему стратегию, план продвижения, но в итоге слышите вопрос: “А какой рост вы ожидаете?”.
И конечно же ответ сводится к тому, что предсказать точный результат от seo нельзя, так как есть много косвенных факторов, на которые невозможно повлиять:
- алгоритм ПС,
- сезонность,
- изменения на сайте у конкурентов и т.д.
Seo специалист может проделать большой стек работы, но при этом не исключено, что при очередном обновлении алгоритма, сайт может, как взлететь до небес, так и провалиться. Следующую модель рекомендую позиционировать, как способ, который помогает понять, насколько работа над целевым набором ключевых слов, способна увеличить показатели при правильной оптимизации.
Что необходимо для создания модели прогнозирования?
В качестве входных данных и инструментов, которые будут их обрабатывать:
- Аккаунт Google и доступы к аналитике продвигаемого сайта.
- Домен, который вы планируете продвигать и анализировать.
- ТОП-150 целевых запросов, на которые вы хотите ориентироваться.
- Частота запросов.
- Среднемесячный объем органического трафика с десктопных и мобильных устройств.
- Среднемесячная конверсия с десктопных и мобильных устройств.
- Текущая позиция домена по запросам.
- Google Таблицы и Data Studio.
Наверно, самое интересное в этой модели то, что она показывает, какой прирост можно ожидать, если делать усилия на конкретные запросы. Это значит, что она не учитывает остальные запросы, которые также приносят трафик на сайт и конверсии. Скачать пример файла можно тут.
На первом листе “Запросы” вам нужно заполнить столбцы А, В, С и строки — 2-151 своими запросами с позициями на текущий момент и частотами, отсортированными по их убыванию (рис. 1).
Рисунок №1 — Заполнение таблицы в шаблоне
Важно!
Строки 152 и далее будут заполняться автоматически. Если у вас запросов меньше, то шаблон потребует корректировок.
На втором листе “Средний CTR и входные по трафику”, нужно заполнить данные по среднемесячному органическому трафику с мобильных и десктопных устройств, а также конверсию (рис 2).
Рисунок №2 — Данные о трафике и конверсиях
На этом же листе в ячейках H1:J101 данные, которые менять не стоит. Это данные по среднему показателю CTR в зависимости от позиции запроса в выдаче. Данные взяты отсюда.
Все формулы уже присутствуют, вам не нужно писать их вручную, далее немного поясню некоторые из них.
Формулы расчета прогноза трафика и конверсий
Для расчета прироста мобильного и десктопного трафика используются формулы ВПР Excel.
Формула 1:
=Значение CTR для запроса*Частота запроса*Среднемесячный мобильный(десктопный) трафик
=ВПР(E2;'Средний CTR и входные по трафику'!H:J;3;0)*C2*'Средний CTR и входные по трафику'!$C$4
Данная формула ищет позицию запроса из ячейки “Е2” в диапазоне позиций “H:J” на втором листе с показателями CTR. Соответствующее значение умножается на частоту запроса и на среднемесячный показатель мобильного трафика.
- E2 — искомая позиция;
- ‘Средний CTR и входные по трафику’!H:J;3;0 — диапазон, в котором ищется совпадение с искомой позицией, и возвращается результат(соседняя ячейка);
- С2 — частота запроса;
- ‘Средний CTR и входные по трафику’!$C$4 — значение мобильного трафика.(Для десктопного трафика, данные получаем из ячейки $В$4)
Для расчета конверсий используется такая формула:
Формула 2:
=Прогнозируемый мобильный(десктопный) трафик*Среднемесячная мобильная(десктопная) конверсия
=(F2*'Средний CTR и входные по трафику'!$E$4)
Полученное значения мобильного и десктопного трафика умножаются на соответствующие показатели среднемесячной конверсии с устройств.
- F2 — прирост мобильного трафика (G2 — десктопного трафика);
- ‘Средний CTR и входные по трафику’!$E$4 — среднемесячная мобильная конверсия($D$4 — десктопная).
Формулы генерации ситуаций роста позиций
В столбце “Динамика”, на первом листе, указаны 7 ситуаций — рост позиций:
- Не изменилась.
- На 1 выросла.
- На 2 выросла.
- На 3 выросла.
- На 4 выросла.
- На 5 выросла.
- На 10 выросла.
Для этих ситуаций прописано 7 формул Excel, которые заполняют столбец “Новая позиция” автоматически. Как только вы заполните данные на первом листе и на втором, можно сказать, что больше половины работы выполнено.
Подключение Google Таблицы к Data Studio
Для того, чтобы связать Google Таблицу с данными и Data Studio, перейдите по ссылке и скопируйте шаблон, в верхней части(рис. 3).
Рисунок №3 — Копия шаблона
Вам откроется диалоговое окно, в котором нужно создать новый источник данных. Открываем выпадающее окно и выбираем Создать источник данных (рис.4, 5).
Рисунок №4 — Копирование отчета
Рисунок №5 — Создать новый источник данных
После того, как вы выберете Создать источник данных, откроется список с источниками, которые можно связать с Data Studio, среди них необходимо найти Google Таблицы и нажать Выбрать(рис. 6).
Рисунок №6 — Источник данных Google Таблицы
Вам откроется окно, где нужно найти ранее скопированный шаблон таблицы.
- Выбираем шаблон (Шаблон ТОП150 для прогнозирования).
- Параметры оставляем без изменений, если галочек нет, то добавляем.
- Нажимаем связать(рис. 7).
Рисунок №7 — Настройка источника
Следующую настройку также оставляем без изменений и нажимаем Добавить (рис. 8).
Рисунок №8 — Подключение источника
После создания подключения, ваша диаграмма с данными будет выглядеть так(рис. 9).
Рисунок №9 — Диаграмма с данными
Данные в этом прогнозе получены из расчета среднемесячного объема трафика, среднего CTR* и текущей позиции домена по запросу.
*среднемесячные данные по CTR получены с источника https://www.advancedwebranking.com/ctrstudy/
При наведении курсором на диаграмму, можно увидеть данные полученные в ходе расчета(рис. 10).
Рисунок №10 — Динамика данных
Панель этой модели полностью интерактивна. В верхнем правом углу можно фильтровать и смотреть данные по конкретным запросам(рис. 11) или по динамике(рис. 12).
Рисунок №11 — Данные по запрос150
Рисунок №12 — Данные по запросам, выросшим на 4 позиции
Внизу отчета исходные данные из нашей таблицы. Их также можно сортировать по динамике и частоте(рис. 13).
Рисунок №13 — Данные из Google Таблиц
Такую диаграмму данных удобно презентовать руководителю или потенциальному клиенту вместе со стратегией или планом работ. Так она представляет быструю визуализацию данных, которые будут оптимизированы сео-специалистом.
Автор Ирина Старченко
практикующий веб разработчик
и seo-специалист