Как обработать данные по поисковым запросам из органики Google в Google BigQuery

Практически каждый сайт так или иначе оптимизируют для поисковых систем, а в рунете для двух — Яндекса и Google. Для оценки эффективности SEO-продвижения, необходимо отслеживать переходы на сайт из органики и поисковые запросы. С Яндексом все достаточно просто: в отчете «Поисковые системы» в Метрике доступны список поисковых фраз в Яндексе и динамика переходов по ним.

C Google все несколько сложней. В Google Analytics нет статистики по поисковым фразам. Можно только связать ресурс Google Analytics с Google Search Console, откуда и будут подгружаться данные в систему аналитики, но со следующими ограничениями:

1. Не будет привязки конкретной поисковой фразы к сеансу на сайте. Поисковые фразы доступны только в единственном отчете ‘Источники трафика’ -> ‘Search Console’ -> ‘Запросы’, и нет возможности использовать их в сегментах или любых других отчетах.
2. Отслеживаются не все поисковые фразы, многие помечаются как ‘(other)’. Например, на сайте eLama.ru таких поисковых фраз около 40%, а на ppc.world — до 80%.
3. В Google Analytics для импорта из Search Console можно настроить только один ресурс, а не набор ресурсов. Это имеет значение если у вас есть поддомены.
4. Сейчас нет возможности дать доступ другому пользователю к набору ресурсов в Search Console.
5. В нем хранятся данные за последние 90 дней:

Первые две проблемы невозможно решить без серьезных изменений на стороне Google. Но как решить последние три, мы рассмотрим в этой статье. Заодно построим два отчета. Первый - с динамикой переходов из поиска Google с разбивкой на брендированные и небрендированные запросы:

Показатели в таблице:

period — отчетный месяц;

b_clicks — количество кликов по брендированным запросам;

b_percent — доля кликов по брендированным запросам;

b_impressions — количество показов по брендированным запросам;

b_ctr — CTR брендированных запросов;

nb_clicks — количество кликов по небрендированным запросам;

nb_percent — доля кликов по небрендированным запросам;

nb_impressions — количество показов по небрендированным запросам;

nb_ctr — CTR небрендированных запросов;

o_clicks — количество кликов по неизвестным запросам (others);

o_part_off — какую долю от всех переходов из поиска Google составляют эти фразы (others);

o_impressions — количество показов по неизвестным запросам (others);

o_ctr — CTR неизвестных запросов (others).

Второй отчет будет содержать список небрендированных запросов и их статистику по месяцам:

Показатели:

search_term — поисковая фраза;

clicksAllTime — количество кликов за все время;

c_17_03 — количество кликов в марте 2017 г.

p_17_03 — позиция, которую занимала фраза в марте 2017 г.

Чтобы получить такие отчеты, мы будем каждый месяц скачивать из Google Search Console CSV-файл со статистикой за предыдущий месяц и загружать его в облачную базу данных Google BigQuery, где данные будут храниться и обрабатываться. Итак, подробнее по шагам:

1. Нужно зайти в Search Console и скачать статистику за определенный месяц. Ограничение — доступно максимум 999 поисковых запросов. Если вам нужно больше данных — они доступны в Google Search Console API.

2. В Google BigQuery создадим dataset, например ‘search_console_google’. О том, как начать работать с BigQuery можно прочитать в одной из моих предыдущих статей. Загрузим полученные CSV-файлы в созданный dataset:

3. У вас, скорее всего, сначала получится загрузить статистику только за 2-3 месяца. Для ppc.world выгружали статистику из Search Console раньше, поэтому у нас данных больше. Вот так выглядят загруженные таблицы в веб-интерфейсе BigQuery:

4. Теперь в нужно создать виртуальную таблицу (view) search_console_google.all, содержащую этот SQL-скрипт.

SELECT
   period,
   domain,
   search_term,
   CASE
      WHEN search_term='(other)' THEN '(other)'
      WHEN search_term CONTAINS 'ppc world' OR
           search_term CONTAINS 'ppcworld' OR
           search_term CONTAINS 'ppc.world' OR
           search_term CONTAINS 'ppc новости' OR
           search_term CONTAINS 'ppc-world' OR
           search_term CONTAINS 'world ppc' OR
           search_term = 'ppc' THEN 'branded'
      ELSE 'not branded'
   END as type,
   clicks,
   impressions,
   ctr,
   FLOAT (REPLACE(position,',','.')) as position,
FROM
(
   SELECT
      period, domain, search_term, clicks, impressions, ctr, position,
   FROM
   (
      SELECT '2017-02' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_02
   ),
   (
      SELECT '2017-03' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_03
   ),
   (
      SELECT '2017-04' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_04
   ),
   (
      SELECT '2017-05' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_05
   ),
   (
      SELECT '2017-06' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_06
   ),
   (
      SELECT '2017-07' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_07
   ),
   (
      SELECT '2017-08' as period, 'ppc.world' as domain, search_term, clicks, impressions, ctr, position
      FROM search_console_google.ppcworld_2017_08
   ),
   (
      SELECT '2017-06' as period, 'ppc.world' as domain, '(other)' as search_term, 3039 as clicks, 65804 as impressions,
   ),
   (
      SELECT '2017-07' as period, 'ppc.world' as domain, '(other)' as search_term, 3076 as clicks, 68038 as impressions,
   ),
   (
      SELECT '2017-08' as period, 'ppc.world' as domain, '(other)' as search_term, 3771 as clicks, 76011 as impressions,
   ),
)

Отредактируйте его под свой проект, запустите в BigQuery и сохраните view. Эта таблица будет содержать подробную информацию по каждому поисковому запросу за каждый отчетный месяц по вашему сайту или нескольким поддоменам. Ее нужно будет дополнять каждый месяц.

Посмотрим на результат выполнения этого скрипта (нужно нажать Edit Query, Run Query):

Показатели отчета:

period — отчетный период, соотносится с каждой таблицей из Search Console, прописывается в SQL-запросе;

domain — домен, соотносится с каждой таблицей из Search Console, прописывается в SQL-запросе;

search_term — поисковая фраза;

type — тип ключевой фразы, определяется в SQL-запросе, принимает три значения: ‘(other)’, ‘branded’, ‘not branded’;

clicks — количество кликов; для фраз ‘(other)’ значение нужно взять из Google Analytics, потому что в Search Console эти значения не отображаются, и прописать в SQL-запросе (соответственно если у вас несколько поддоменов, то получиться указать значения только для одного из них) ;

impressions — количество показов, аналогично как с кликами;

ctr — CTR поисковой фразы, берется из загруженного CSV-файла без изменений; для фраз ‘(other)’ не указывается (NULL);

position — позиция поисковой фразы, берется из загруженного CSV-файла и преобразуется в тип FLOAT; для фраз ‘(other)’ не указывается (NULL).

5. Чтобы получить первый отчет о динамике переходов из поиска Google с разбивкой на брендированные и небрендированные запросы, создайте view search_console_google.months, содержащий следующий SQL-скрипт.

SELECT
   period,
   SUM (branded_clicks) as b_clicks,
   ROUND(100 * SUM (branded_clicks) / (SUM (branded_clicks) + SUM (not_branded_clicks))) as b_percent,
   SUM (branded_impressions) as b_impressions,
   ROUND (100 * SUM (branded_clicks) / SUM (branded_impressions), 1) as b_ctr,
   SUM (not_branded_clicks) as nb_clicks,
   ROUND(100 * SUM (not_branded_clicks)/ (SUM (branded_clicks) + SUM (not_branded_clicks))) as nb_percent,
   SUM (not_branded_impressions) as nb_impressions,
   ROUND (100 * SUM (not_branded_clicks) / SUM (not_branded_impressions), 1) as nb_ctr,
   SUM (others_clicks) as o_clicks,
   ROUND(100 * SUM (others_clicks)/ (SUM (branded_clicks) + SUM (not_branded_clicks) + SUM (others_clicks))) as o_part_off,
   SUM (others_impressions) as o_impressions,
   IFNULL(ROUND (100 * SUM (others_clicks) / SUM (others_impressions), 1),0) as o_ctr,
FROM
(
   SELECT
      period,
      IF (type='branded' , clicks, 0) as branded_clicks,
      IF (type='not branded' , clicks, 0) as not_branded_clicks,
      IF (type='(other)' , clicks, 0) as others_clicks,
      IF (type='branded' , impressions, 0) as branded_impressions,
      IF (type='not branded' , impressions, 0) as not_branded_impressions,
      IF (type='(other)' , impressions, 0) as others_impressions,
   FROM [your-project-id:search_console_google.all]
)  
GROUP BY period,
ORDER BY period DESC

В нем ничего менять не нужно кроме project-id из BigQuery, он сразу должен заработать. В скрипте рассчитываются параметры на основе значений из search_console_google.all, который мы настроили в предыдущем пункте.

6. Чтобы получить второй отчет со статистикой по небрендированным запросам, создайте view search_console_google.kewords на основе следующего SQL-скрипта.

SELECT
   search_term,
   SUM (clicks_2017_02) + SUM (clicks_2017_03) + SUM (clicks_2017_04) +
   SUM (clicks_2017_05) + SUM (clicks_2017_06) + SUM (clicks_2017_07) +
   SUM (clicks_2017_08)     as clicksAllTime,
   SUM (clicks_2017_02) as clicks_17_02,   MAX (position_2017_02) as pos_2017_02,
   SUM (clicks_2017_03) as c_17_03,        MAX (position_2017_03) as p_17_03,
   SUM (clicks_2017_04) as c_17_04,        MAX (position_2017_04) as p_17_04,
   SUM (clicks_2017_05) as c_17_05,        MAX (position_2017_05) as p_17_05,
   SUM (clicks_2017_06) as c_17_06,        MAX (position_2017_06) as p_17_06,
   SUM (clicks_2017_07) as c_17_07,        MAX (position_2017_07) as p_17_07,
   SUM (clicks_2017_08) as c_17_08,        MAX (position_2017_08) as p_17_08,
FROM
(  //установим кол-во кликов и показов в соответствующие колонки по периодам,
   //чтобы потом считать клики и позицию по каждому периоду в отдельности
   SELECT
      search_term,
      IF (period='2017-02' , clicks, 0) as clicks_2017_02,      IF (period='2017-02' , position, 0) as position_2017_02,
      IF (period='2017-03' , clicks, 0) as clicks_2017_03,      IF (period='2017-03' , position, 0) as position_2017_03,
      IF (period='2017-04' , clicks, 0) as clicks_2017_04,      IF (period='2017-04' , position, 0) as position_2017_04,
      IF (period='2017-05' , clicks, 0) as clicks_2017_05,      IF (period='2017-05' , position, 0) as position_2017_05,
      IF (period='2017-06' , clicks, 0) as clicks_2017_06,      IF (period='2017-06' , position, 0) as position_2017_06,
      IF (period='2017-07' , clicks, 0) as clicks_2017_07,      IF (period='2017-07' , position, 0) as position_2017_07,
      IF (period='2017-08' , clicks, 0) as clicks_2017_08,      IF (period='2017-08' , position, 0) as position_2017_08,
   FROM
      [your-project-id:search_console_google.all]
   WHERE
      type='not branded'
)     
GROUP BY search_term
ORDER BY clicksAllTime DESC

В этом скрипте необходимо заменить project-id из BigQuery, и потом нужно будет ежемесячно добавлять новые строки для обработки статистики по новому месяцу.

7. Отчеты созданы. Теперь можно расшарить своим коллегам dataset и входящие в него отчеты.

8. С такими отчетами можно работать не только в веб-интерфейсе Google BigQuery, но и просто сохранить в Google Sheets или скачать CSV. Можно поступить другим образом — создать Google Sheets с доступом коллегам, а в него данные подтягивать через OWOX BI BigQuery Reports. Или же подключить инструменты визуализации: Google Data Studio, Redash, Tableau и другие.

Таким образом мы разобрались, как можно сохранить статистику переходов из органики Google, а также автоматизировать отчетность. Если у вас есть вопросы или дополнения — пишите в комментариях.