Автор: Антон Леонтьєв, керівник відділу веб-аналітики eLama

Практично кожен сайт так чи інакше оптимізують для пошукових систем, а в рунеті для двох - Яндекса і 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 .

1. В Google BigQuery створимо dataset, наприклад 'search_console_google'. Про те, як почати працювати з BigQuery можна прочитати в одній з моїх попередніх статей . Завантажимо отримані CSV-файли в створений dataset:

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

1. Тепер в потрібно створити віртуальну таблицю (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' a s 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).

1. Щоб отримати перший звіт про динаміку переходів з пошуку 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, який ми налаштували в попередньому пункті.

1. Щоб отримати другий звіт зі статистикою по небрендовану запитам, створіть 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', cli cks, 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, і потім потрібно буде щомісяця додавати нові рядки для обробки статистики по новому місяця.

1. Звіти створені. Тепер можна розшарити своїм колегам dataset і входять до нього звіти.

1. З такими звітами можна працювати не тільки в веб-інтерфейсі Google BigQuery, але і просто зберегти в Google Sheets або завантажити CSV. Можна вчинити іншим чином - створити Google Sheets з доступом колегам, а в нього дані підтягувати через OWOX BI BigQuery Reports . Або ж підключити інструменти візуалізації: Google Data Studio, Redash, Tableau і інші.

Таким чином ми розібралися, як можна зберегти статистику переходів з органіки Google, а також автоматизувати звітність. Якщо у вас є питання або доповнення - пишіть в коментарях.