12 SQL заявки в BigQuery за SEO анализ

Анализът на SEO данни от Google Search Console чрез BigQuery отваря врата към неограничени възможности. Вместо да разчитате на ограничения интерфейс на GSC, с SQL заявки можете да задавате всякакви въпроси към данните си – от прости агрегации до сложни прогнози и корелации. Тази статия представя 12 практически SQL заявки, които всеки SEO специалист трябва да знае. Всяка заявка включва обяснение, SQL код и примерни резултати в табличен вид.

🎯 Как да използвате тези заявки: Приемаме, че имате таблица your_project.searchconsole.gsc_data с колони: date, query, page, clicks, impressions, position, device, country. Заменете `your_project` с вашия GCP проект.

Част 1: Базови агрегации и KPI анализи

1. Общи KPI за период – кликове, импресии, CTR, позиция

Полза: Бърз поглед върху здравето на SEO кампанията за даден период. Сравнявайте месеци или години.

-- Заявка 1: Основни KPI за последните 30 дни
SELECT 
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS avg_ctr_percent,
    ROUND(AVG(position), 1) AS avg_position
FROM `your_project.searchconsole.gsc_data`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

total_clicks	total_impressions	avg_ctr_percent	avg_position
124,560	2,450,300	5.08%	12.3

2. Топ 10 страници по кликове

Полза: Идентифициране на най-ценните URL адреси. След това оптимизирайте още повече тези страници.

-- Заявка 2: Най-ефективни страници
SELECT 
    page,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
    ROUND(AVG(position), 1) AS avg_pos
FROM `your_project.searchconsole.gsc_data`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY page
ORDER BY clicks DESC
LIMIT 10;

page	clicks	impressions	ctr	avg_pos
/blog/sql-seo-guide	12,450	78,200	15.9%	4.2
/tools/bigquery-analyzer	8,320	45,100	18.4%	2.8

3. Топ 10 заявки с нисък CTR (инвестиционен потенциал)

Полза: Откриване на "ниско висящи плодове" – фрази, които се показват често, но не се кликват. Проблемът е в заглавието или описанието.

-- Заявка 3: Високи импресии, слаб CTR
SELECT 
    query,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
    ROUND(AVG(position), 1) AS avg_pos
FROM `your_project.searchconsole.gsc_data`
WHERE date >= CURRENT_DATE() - 30
GROUP BY query
HAVING impressions > 5000 AND ctr < 2
ORDER BY impressions DESC
LIMIT 15;

query	clicks	impressions	ctr	avg_pos
bigquery sql примери	87	12,400	0.70%	8.3
google search console api	142	9,800	1.45%	6.1

Част 2: Дълбоки сегменти и сравнения

4. Сравнение на устройства (Desktop vs Mobile)

Полза: Ако мобилният трафик има по-висок CTR или по-добра позиция, може би сайтът ви е по-оптимизиран за мобилни устройства (или обратното).

-- Заявка 4: Ефективност по устройство
SELECT 
    device,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
    ROUND(AVG(position), 1) AS avg_position
FROM `your_project.searchconsole.gsc_data`
WHERE date >= '2024-01-01'
GROUP BY device
ORDER BY clicks DESC;

device	clicks	impressions	ctr	avg_position
MOBILE	89,450	1,250,000	7.16%	14.2
DESKTOP	44,200	890,000	4.97%	11.5

5. Анализ по държави – къде имате най-голям потенциал?

Полза: Разширяване на SEO стратегията в географски региони с високи импресии, но нисък CTR.

-- Заявка 5: Ефективност по държави
SELECT 
    country,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
    ROUND(AVG(position), 1) AS avg_pos
FROM `your_project.searchconsole.gsc_data`
WHERE date >= '2024-01-01'
GROUP BY country
HAVING impressions > 10000
ORDER BY ctr ASC
LIMIT 20;

6. Сравнение на два времеви периода (WoW или MoM)

Полза: Измерване на ефекта от SEO промени – например след ъпдейт на съдържание.

-- Заявка 6: Сравнение на текущия месец с предходния
WITH current_period AS (
    SELECT SUM(clicks) AS clicks, SUM(impressions) AS impressions
    FROM `your_project.searchconsole.gsc_data`
    WHERE date BETWEEN '2024-04-01' AND '2024-04-30'
),
previous_period AS (
    SELECT SUM(clicks) AS clicks, SUM(impressions) AS impressions
    FROM `your_project.searchconsole.gsc_data`
    WHERE date BETWEEN '2024-03-01' AND '2024-03-31'
)
SELECT 
    c.clicks AS current_clicks,
    p.clicks AS previous_clicks,
    ROUND((c.clicks - p.clicks) / p.clicks * 100, 1) AS clicks_change_percent,
    ROUND(SAFE_DIVIDE(c.clicks, c.impressions) * 100, 2) AS current_ctr,
    ROUND(SAFE_DIVIDE(p.clicks, p.impressions) * 100, 2) AS previous_ctr
FROM current_period c, previous_period p;

Част 3: Откриване на аномалии и технически проблеми

7. Канибализация на ключови думи – една заявка, много URL

Полза: Идентифициране на дублиране на съдържание от гледна точка на Google – няколко страници се борят за една и съща заявка.

-- Заявка 7: Канибализация (>= 3 URL-а с кликове за една заявка)
SELECT 
    query,
    COUNT(DISTINCT page) AS urls_count,
    SUM(clicks) AS total_clicks,
    STRING_AGG(DISTINCT page ORDER BY page LIMIT 5) AS sample_urls
FROM `your_project.searchconsole.gsc_data`
WHERE clicks > 0 AND date >= CURRENT_DATE() - 60
GROUP BY query
HAVING urls_count >= 3
ORDER BY total_clicks DESC
LIMIT 30;

query	urls_count	total_clicks	sample_urls
sql заявки bigquery	5	1,230	/blog/sql, /bigquery/guide, /sql-tips

8. Страници с внезапен спад на кликовете

Полза: Бързо откриване на технически проблеми или загуба на backlinks.

-- Заявка 8: Топ 20 страници с най-голям спад (последните 28 срещу предходните 28 дни)
WITH last_28 AS (
    SELECT page, SUM(clicks) AS clicks_recent
    FROM `your_project.searchconsole.gsc_data`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
    GROUP BY page
),
prev_28 AS (
    SELECT page, SUM(clicks) AS clicks_previous
    FROM `your_project.searchconsole.gsc_data`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 29 DAY)
    GROUP BY page
)
SELECT 
    l.page,
    l.clicks_recent,
    p.clicks_previous,
    (l.clicks_recent - p.clicks_previous) AS change,
    ROUND((l.clicks_recent - p.clicks_previous) / p.clicks_previous * 100, 1) AS percent_change
FROM last_28 l
JOIN prev_28 p ON l.page = p.page
WHERE p.clicks_previous > 100
ORDER BY change ASC
LIMIT 20;

Част 4: Напреднали статистики и прогнози

9. Разпределение на позициите (позиция 1-3, 4-10, 11-20, 20+)

Полза: Разбиране къде се намират повечето ви импресии – дали сте в топ 3 или извън първите 20 резултата.

-- Заявка 9: Класове на позициите
SELECT 
    CASE 
        WHEN position <= 3 THEN 'Топ 3'
        WHEN position <= 10 THEN '4-10'
        WHEN position <= 20 THEN '11-20'
        ELSE '20+'
    END AS position_bucket,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    ROUND(SAFE_DIVIDE(SUM(clicks), SUM(impressions)) * 100, 2) AS ctr,
    COUNT(DISTINCT query) AS unique_queries
FROM `your_project.searchconsole.gsc_data`
WHERE date >= '2024-01-01'
GROUP BY position_bucket
ORDER BY MIN(position);

position_bucket	clicks	impressions	ctr	unique_queries
Топ 3	78,200	420,000	18.6%	1,240
4-10	34,100	890,000	3.83%	3,890

10.Корелация между позиция и CTR

Полза: Колко силно позицията влияе върху кликванията за вашия сайт. Показва дали снипетите ви са привлекателни.

-- Заявка 10: Коефициент на корелация на Пиърсън
SELECT 
    CORR(position, SAFE_DIVIDE(clicks, impressions)) AS position_ctr_correlation,
    COUNT(*) AS sample_size
FROM `your_project.searchconsole.gsc_data`
WHERE impressions > 50 AND clicks >= 0 AND date >= CURRENT_DATE() - 90;
-- Резултат близък до -0.8 = силна негативна зависимост (по-ниска позиция = по-висок CTR)

11. Rolling average – 7-дневна пълзяща средна за кликове

Полза: Изглаждане на дневните флуктуации, за да видите реалния тренд.

-- Заявка 11: Пълзяща средна на кликовете за последните 90 дни
WITH daily AS (
    SELECT 
        date,
        SUM(clicks) AS daily_clicks
    FROM `your_project.searchconsole.gsc_data`
    WHERE date >= CURRENT_DATE() - 90
    GROUP BY date
)
SELECT 
    date,
    daily_clicks,
    AVG(daily_clicks) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM daily
ORDER BY date DESC;

12. Топ растящи заявки – положителна промяна на позицията

Полза: Откриване на възходящи SEO трендове. Инвестирайте в съдържание, което вече печели позиции.

-- Заявка 12: Заявки с най-голямо подобрение на позицията
WITH query_last_month AS (
    SELECT query, AVG(position) AS avg_pos_old
    FROM `your_project.searchconsole.gsc_data`
    WHERE date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)
    GROUP BY query
),
query_current_month AS (
    SELECT query, AVG(position) AS avg_pos_new
    FROM `your_project.searchconsole.gsc_data`
    WHERE date >= CURRENT_DATE() - 30
    GROUP BY query
)
SELECT 
    qc.query,
    qc.avg_pos_new AS current_position,
    qo.avg_pos_old AS previous_position,
    (qo.avg_pos_old - qc.avg_pos_new) AS position_improvement
FROM query_current_month qc
JOIN query_last_month qo ON qc.query = qo.query
WHERE qo.avg_pos_old > qc.avg_pos_new AND qo.avg_pos_old - qc.avg_pos_new > 2
ORDER BY position_improvement DESC
LIMIT 25;

Ползи от използването на SQL за SEO анализ

Полза	Обяснение
⚡ Скорост и мащаб	Анализирайте милиони редове за секунди. GSC интерфейсът дава само 1000 реда.
🔗 Интеграция на данни	JOIN-вайте GSC с GA4, backlinks, CRM данни за пълна картина.
📐 Сложни изчисления	Медиани, процентили, корелации, rolling averages – невъзможни в стандартния GSC.
🤖 Автоматизация	Свържете с Looker Studio (бивш Data Studio) или автоматизирайте Slack/Email доклади.
🎯 Прецизно сегментиране	Редовни изрази, позиционни кошове, групиране по URL патерни.
🧠 Откриване на аномалии	Намерете спадове и пикове преди да са навредили на KPI-тата.

Важно: Всички горепосочени заявки са съвместими със стандартния SQL диалект на BigQuery. Тествани са в реална среда. Заменете `your_project.searchconsole.gsc_data` с реалното име на вашата таблица.

Как да започнете още днес?

Създайте GCP проект и активирайте BigQuery (безплатен tier – 1TB обработка на месец).
Експортирайте Google Search Console към BigQuery от настройките на GSC.
Отворете BigQuery редактора и изпълнете първата заявка от списъка.
Запазете често използваните заявки като Saved Queries или създайте View.
Свържете Looker Studio за визуализация в реално време.

Професионален съвет: Комбинирайте Заявка 7 (канибализация) със Заявка 8 (спад в кликовете) – често канибализацията води до спад след алгоритмична актуализация на Google.

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

12 SQL заявки в BigQuery за SEO анализ

12 SQL заявки в BigQuery за SEO анализ