`SQL`在`SEO`中的高级应用：如何通过`JOIN`和`WINDOW FUNCTION`分析`Google Search Console`数据。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

SQL在SEO中的高级应用：JOIN和WINDOW FUNCTION深度分析Google Search Console数据

各位SEO从业者，大家好！今天我们来深入探讨如何利用SQL，特别是JOIN和WINDOW FUNCTION，来更有效地分析Google Search Console (GSC) 数据，从而提升我们的SEO策略。

Google Search Console是SEO人员最重要的工具之一，它提供了关于网站在Google搜索结果中的表现的宝贵信息。然而，GSC的界面和内置报告功能在某些情况下可能显得不够灵活。通过将GSC数据导出到数据库，并利用SQL进行高级分析，我们可以发现隐藏的洞察，并做出更明智的决策。

1. 数据准备：将GSC数据导入数据库

首先，我们需要将GSC数据导出并导入到数据库中。GSC支持导出为CSV格式，你可以选择导出“搜索结果”和“网址检查”两个主要报告。

搜索结果报告： 包含查询词、页面、点击次数、展示次数、平均排名和点击率(CTR)等数据。
网址检查报告： 包含索引状态、移动设备易用性、增强结果等数据。

常见的数据库选择包括MySQL、PostgreSQL、BigQuery等。这里以PostgreSQL为例，假设我们创建了两个表：search_results和url_inspections。

1.1 创建表结构:

CREATE TABLE search_results (
    query VARCHAR(255),
    page VARCHAR(255),
    clicks INT,
    impressions INT,
    position DECIMAL(5,2),
    ctr DECIMAL(5,4),
    search_appearance VARCHAR(255), -- 新增字段
    device VARCHAR(50),         -- 新增字段
    country VARCHAR(50),        -- 新增字段
    date DATE
);

CREATE TABLE url_inspections (
    url VARCHAR(255) PRIMARY KEY,
    indexing_state VARCHAR(255),
    mobile_usability VARCHAR(255),
    enhanced_results VARCHAR(255),
    last_crawl DATE,
    crawl_allowed BOOLEAN,
    page_fetch BOOLEAN,
    robots_txt_state VARCHAR(255),
    canonical_url VARCHAR(255),
    user_declared_canonical VARCHAR(255)
);

1.2 导入数据:

你可以使用PostgreSQL的COPY命令或者图形化工具（如pgAdmin）导入CSV数据。

COPY search_results FROM '/path/to/search_results.csv' DELIMITER ',' CSV HEADER;
COPY url_inspections FROM '/path/to/url_inspections.csv' DELIMITER ',' CSV HEADER;

2. 利用JOIN进行关联分析

JOIN操作允许我们将来自不同表的数据关联起来，从而进行更深入的分析。以下是一些常见的JOIN应用场景：

2.1. 关联搜索结果和网址检查数据，分析未编入索引的页面表现

假设我们想分析那些未被Google索引的页面，它们在搜索结果中的表现如何？这可以帮助我们识别潜在的索引问题，并优化这些页面。

SELECT
    sr.page,
    sr.clicks,
    sr.impressions,
    sr.position,
    sr.ctr,
    ui.indexing_state
FROM
    search_results sr
JOIN
    url_inspections ui ON sr.page = ui.url
WHERE
    ui.indexing_state <> 'Page is indexed'
ORDER BY
    sr.impressions DESC;

解释:

JOIN ... ON sr.page = ui.url: 根据search_results表的page列和url_inspections表的url列进行关联。
WHERE ui.indexing_state <> 'Page is indexed': 筛选出未被索引的页面。
ORDER BY sr.impressions DESC: 按照展示次数降序排列，以便优先关注那些有一定曝光但未被索引的页面。

结果示例：

page	clicks	impressions	position	ctr	indexing_state
https://example.com/unindexed-page-1	5	100	15.2	0.0500	Page is not indexed
https://example.com/unindexed-page-2	2	50	20.5	0.0400	Page is not indexed
…	…	…	…	…	…

分析:

通过这个查询，我们可以快速识别出哪些未被索引的页面仍然能获得一定的展示和点击。这些页面可能存在索引问题，例如：

robots.txt 阻止抓取
noindex 元标记
页面质量低
网站结构问题

针对这些问题，我们可以采取相应的优化措施，例如：

检查robots.txt文件
移除noindex标记
提升页面内容质量
优化内部链接结构

2.2. 关联搜索结果和网址检查数据，分析移动设备易用性问题对搜索表现的影响

移动设备易用性是影响搜索排名的重要因素之一。我们可以通过关联搜索结果和网址检查数据，分析移动设备易用性问题对搜索表现的影响。

SELECT
    sr.page,
    sr.clicks,
    sr.impressions,
    sr.position,
    sr.ctr,
    ui.mobile_usability
FROM
    search_results sr
JOIN
    url_inspections ui ON sr.page = ui.url
WHERE
    ui.mobile_usability <> 'Mobile-friendly'
ORDER BY
    sr.impressions DESC;

解释:

WHERE ui.mobile_usability <> 'Mobile-friendly': 筛选出移动设备易用性存在问题的页面。

结果示例：

page	clicks	impressions	position	ctr	mobile_usability
https://example.com/mobile-issue-page-1	3	75	18.7	0.0400	Issues other than mobile-friendly
https://example.com/mobile-issue-page-2	1	30	22.1	0.0333	Issues other than mobile-friendly
…	…	…	…	…	…

分析:

通过这个查询，我们可以识别出哪些移动设备易用性存在问题的页面，并且了解这些页面在搜索结果中的表现。如果发现某些页面的移动设备易用性存在问题，且搜索表现不佳，那么就应该优先修复这些问题。

2.3 深入分析：结合查询词和页面，找出移动设备易用性问题影响特定关键词的页面

上述查询是针对所有页面的整体分析。为了更精准地定位问题，我们可以进一步结合查询词，找出移动设备易用性问题影响特定关键词的页面。

SELECT
    sr.query,
    sr.page,
    sr.clicks,
    sr.impressions,
    sr.position,
    sr.ctr,
    ui.mobile_usability
FROM
    search_results sr
JOIN
    url_inspections ui ON sr.page = ui.url
WHERE
    ui.mobile_usability <> 'Mobile-friendly'
    AND sr.query LIKE '%特定关键词%' -- 替换为你要分析的关键词
ORDER BY
    sr.impressions DESC;

解释:

AND sr.query LIKE '%特定关键词%': 增加一个条件，筛选出包含特定关键词的查询词。

结果示例：

query	page	clicks	impressions	position	ctr	mobile_usability
特定关键词相关文章	https://example.com/mobile-issue-page-1	1	25	19.2	0.0400	Issues other than mobile-friendly
特定关键词教程	https://example.com/mobile-issue-page-2	0	15	23.5	0.0000	Issues other than mobile-friendly
…	…	…	…	…	…	…

分析:

通过这个查询，我们可以看到哪些包含特定关键词的搜索查询，其对应 landing page 的移动设备易用性存在问题。这可以帮助我们更精准地定位需要优化的页面和关键词。

3. 利用WINDOW FUNCTION进行趋势分析和排名比较

WINDOW FUNCTION 允许我们在结果集的分区上执行计算，而无需对数据进行分组。这对于分析趋势、计算排名等非常有用。

3.1. 计算每个页面点击次数的变化趋势

假设我们想了解每个页面的点击次数在一段时间内的变化趋势。我们可以使用LAG()函数来计算每个页面与前一天相比的点击次数变化。

SELECT
    page,
    date,
    clicks,
    LAG(clicks, 1, 0) OVER (PARTITION BY page ORDER BY date) AS previous_day_clicks,
    clicks - LAG(clicks, 1, 0) OVER (PARTITION BY page ORDER BY date) AS click_difference
FROM
    search_results
ORDER BY
    page,
    date;

解释:

LAG(clicks, 1, 0) OVER (PARTITION BY page ORDER BY date): 计算每个页面前一天的点击次数。
- LAG(clicks, 1, 0): 获取前一行clicks的值，如果前一行不存在，则返回0。
- OVER (PARTITION BY page ORDER BY date): 指定窗口函数的分区和排序方式。按照page进行分区，按照date进行排序。
clicks - LAG(clicks, 1, 0) OVER (PARTITION BY page ORDER BY date): 计算当前点击次数与前一天点击次数的差值。

结果示例：

page	date	clicks	previous_day_clicks	click_difference
https://example.com/page-1	2023-01-01	10	0	10
https://example.com/page-1	2023-01-02	12	10	2
https://example.com/page-1	2023-01-03	8	12	-4
https://example.com/page-2	2023-01-01	5	0	5
https://example.com/page-2	2023-01-02	7	5	2
…	…	…	…	…

分析:

通过click_difference列，我们可以清楚地看到每个页面每天的点击次数变化情况。如果发现某个页面点击次数持续下降，那么就需要分析原因，并采取相应的优化措施。

3.2. 计算每个查询词的排名变化趋势

与分析点击次数类似，我们可以使用LAG()函数来计算每个查询词的排名变化。

SELECT
    query,
    date,
    position,
    LAG(position, 1, 0) OVER (PARTITION BY query ORDER BY date) AS previous_day_position,
    LAG(position,1) OVER (PARTITION BY query ORDER BY date) - position AS position_difference
FROM
    search_results
ORDER BY
    query,
    date;

解释:

LAG(position, 1, 0) OVER (PARTITION BY query ORDER BY date): 计算每个查询词前一天的排名。
position_difference: 当前排名减去前一天排名。注意：排名越小越好，因此用前一天的排名减去当前排名，正值表示排名提升。

结果示例：

query	date	position	previous_day_position	position_difference
关键词1	2023-01-01	12.5	0	NULL
关键词1	2023-01-02	11.2	12.5	1.3
关键词1	2023-01-03	13.8	11.2	-2.6
关键词2	2023-01-01	8.7	0	NULL
关键词2	2023-01-02	9.1	8.7	-0.4
…	…	…	…	…

分析:

通过position_difference列，我们可以看到每个查询词每天的排名变化情况。如果发现某个查询词排名持续下降，那么就需要分析原因，并采取相应的优化措施。例如，检查页面内容是否相关，链接是否有效，以及竞争对手是否进行了优化。

3.3. 计算每个页面在所有页面中的点击次数排名

我们还可以使用RANK()函数来计算每个页面在所有页面中的点击次数排名。

SELECT
    page,
    clicks,
    RANK() OVER (ORDER BY clicks DESC) AS click_rank
FROM
    (SELECT page, SUM(clicks) AS clicks FROM search_results GROUP BY page) AS subquery
ORDER BY
    click_rank;

解释:

SELECT page, SUM(clicks) AS clicks FROM search_results GROUP BY page: 先对每个页面的点击次数进行汇总。
RANK() OVER (ORDER BY clicks DESC): 计算每个页面在所有页面中的点击次数排名，按照点击次数降序排列。

结果示例：

page	clicks	click_rank
https://example.com/popular-page-1	150	1
https://example.com/popular-page-2	120	2
https://example.com/less-popular-page-1	80	3
…	…	…

分析:

通过click_rank列，我们可以快速了解哪些页面最受欢迎，哪些页面需要改进。

4. 更复杂的应用场景

以上只是一些简单的示例。实际上，我们可以将JOIN和WINDOW FUNCTION结合起来，进行更复杂的分析。例如：

分析不同设备类型（移动设备、桌面设备）的点击次数和排名差异。 这可以帮助我们了解网站在不同设备上的表现，并针对性地进行优化。
分析不同国家/地区的点击次数和排名差异。 这可以帮助我们了解网站在不同地区的受欢迎程度，并针对性地进行本地化优化。
结合季节性因素，分析点击次数和排名的变化趋势。 这可以帮助我们了解季节性因素对网站流量的影响，并提前做好准备。

5. 注意事项

数据质量： 确保导入的数据是准确和完整的。GSC数据可能会存在延迟或抽样，需要注意。
性能优化： 对于大型数据集，SQL查询可能会比较慢。可以考虑使用索引、分区等技术来提高查询性能。
隐私保护： 在使用GSC数据时，需要遵守Google的隐私政策，并保护用户隐私。

6.代码优化建议

使用EXPLAIN ANALYZE: 在执行复杂的SQL查询之前，可以使用EXPLAIN ANALYZE命令来分析查询的执行计划，并找出性能瓶颈。
创建索引: 为经常用于JOIN和WHERE子句的列创建索引，可以显著提高查询性能。
**避免SELECT ***: 只选择需要的列，避免选择所有列，可以减少数据传输量，提高查询效率。
使用临时表: 对于复杂的查询，可以将中间结果存储到临时表中，然后对临时表进行查询，可以提高查询效率。但是要注意临时表的生命周期。
批量操作: 在导入数据时，尽量使用批量操作，而不是逐行插入，可以提高导入速度。

7.未来方向

自动化数据导入和分析： 可以编写脚本，自动从GSC API获取数据，并导入到数据库中，定期进行分析。
机器学习应用： 可以利用机器学习算法，预测关键词排名、点击次数等，并识别异常情况。
与其他数据源整合： 可以将GSC数据与其他数据源（例如，Google Analytics、广告数据）整合起来，进行更全面的分析。

SQL结合GSC数据，分析移动设备易用性问题对搜索表现的影响，分析页面点击趋势，分析关键词排名变化，这些都能够帮助我们更精细化的进行SEO优化。

快速回顾与总结

今天我们分享了如何利用SQL中的JOIN和WINDOW FUNCTION来深度分析Google Search Console数据。通过JOIN，我们可以关联不同表的数据，例如将搜索结果数据和网址检查数据关联起来，分析未索引页面的表现或移动设备易用性问题的影响。而WINDOW FUNCTION则可以帮助我们进行趋势分析，例如计算点击次数的变化趋势或关键词的排名变化。掌握这些技巧，能够帮助我们更深入地了解网站的SEO表现，并做出更明智的优化决策。

SQL在SEO中的高级应用：JOIN和WINDOW FUNCTION深度分析Google Search Console数据

发表回复 取消回复

发表回复取消回复