SEO专家如何利用ChatGPT结合示例进行BigQuery

2024 年 6 月 3 日

AI正在通过使以前无法获得的技能（如编码或数据可视化）对每个领域进行塑造，并且可以让每个人轻松访问这些技能。

能够运行正确提示的AI操作员可以执行低难度和中等难度的任务，从而可以更多关注战略决策。

在本指南中，我们将逐步介绍如何使用AI聊天机器人与ChatGPT作为示例来运行复杂的BigQuery查询，以满足您的SEO报告需求。

我们将回顾两个示例：

如何分析由于Google算法影响而导致的流量下降
如何将搜索流量数据与GA4中的参与指标结合

它还将为您提供如何使用聊天机器人来减轻运行SEO报告时的负担的整体想法。

为什么需要学习BigQuery？ #

像Google Search Console或Google Analytics 4这样的SEO工具具有易于访问的用户界面，您可以使用它们访问数据。但通常情况下，它们会限制您可以执行的操作并显示不完整的数据，通常称为数据抽样。

在GSC中，这是因为该工具省略了匿名查询并将表行限制为最多1,000行。

通过使用BigQuery，您可以解决这个问题并运行任何复杂的报告，消除与大型网站合作时经常发生的数据抽样问题。

（或者，您可以尝试使用Looker Studio，但本文的目的是说明如何使用ChatGPT操作BigQuery。）

对于本文，我们假定您已经将您的GSC和GA4帐户连接到了BigQuery。如果您还没有这样做，您可能需要查看我们如何操作的指南：

SQL基础知识 #

如果您了解结构化查询语言（SQL），则可以跳过此部分。但对于那些不了解的人，这里是SQL语句的快速参考：

语句

描述

SELECT

从表中检索数据

INSERT

将新数据插入表中

UNNEST

将数组展平为一组行

UPDATE

更新表中的现有数据

DELETE

从表中删除数据

CREATE

创建新表或数据库

ALTER

修改现有表

DROP

删除表或数据库。

我们将使用的条件，以便您熟悉：

条件

描述

WHERE

根据特定条件筛选记录

AND

将两个或更多条件组合在一起，所有条件必须为真

将两个或更多条件组合在一起，至少一个条件必须为真

NOT

否定一个条件

在列中搜索指定模式。

检查值是否在一组值内

BETWEEN

在给定范围内选择值

IS NULL

检查空值

IS NOT NULL

检查非空值

存在

检查子查询是否返回任何记录

现在，让我们深入了解如何通过ChatGPT使用BigQuery的示例。

1. 如何分析由于Google算法影响而导致的流量下降 #

如果您受到Google算法更新的影响，您应该做的第一件事是对受影响的页面运行报告，并分析为什么会受到影响。

请记住，您可以做的最糟糕的事情是立即在网站上进行更改，进入恐慌模式。这可能会导致搜索流量波动，并使分析影响变得更加困难。

如果您的索引中的页面较少，您可能会发现使用GSC UI数据对数据进行分析是令人满意的，但如果您有成千上万的页面，它不会让您导出超过1,000行（页面或查询）的数据。

假设您有一个自算法更新完成后的一周数据，并且想要将其与上一周的数据进行比较。要在BigQuery中运行该报告，您可以从这个简单的提示开始： -04-18' AND '2024-04-30' AND search_type = 'WEB' GROUP BY url ), comparison AS ( SELECT COALESCE(p1.url, p2.url) AS url, IFNULL(p1.clicks, 0) AS clicks_period1, IFNULL(p2.clicks, 0) AS clicks_period2, IFNULL(p1.impressions, 0) AS impressions_period1, IFNULL(p2.impressions, 0) AS impressions_period2, IFNULL(p1.avg_position, 0) AS avg_position_period1, IFNULL(p2.avg_position, 0) AS avg_position_period2, IFNULL(p1.clicks, 0) - IFNULL(p2.clicks, 0) AS clicks_diff, IFNULL(p1.impressions, 0) - IFNULL(p2.impressions, 0) AS impressions_diff, IFNULL(p1.avg_position, 0) - IFNULL(p2.avg_position, 0) AS avg_position_diff FROM period1 p1 FULL OUTER JOIN period2 p2 ON p1.url = p2.url ) SELECT url, clicks_period1, clicks_period2, clicks_diff, impressions_period1, impressions_period2, impressions_diff, avg_position_period1, avg_position_period2, avg_position_diff FROM comparison ORDER BY url;


现在运行它，享受数据，可以将数据导出为CSV或Google表格。

在处理数百万个URL时，您可能无法在Google表格或CSV导出中工作，因为数据量太大。另外，这些应用程序在单个文档中可以拥有多少行有限制。在这种情况下，您可以将结果保存为BigQuery表，并连接到Looker Studio以查看数据。

但请记住，BigQuery是一项免费服务。每月免费处理1 TB的查询数据。一旦超出限制，您的信用卡将根据您的使用情况自动收费。

这意味着，如果您将您的BigQuery连接到Looker Studio并在那里浏览数据，每次打开Looker仪表板时都会计入您的账单。

这就是为什么当导出有数万或数十万行时，我喜欢使用Google表格。我可以轻松将它连接到Looker Studio进行数据可视化和混合，这不会计入我的账单。

如果您有ChatGPT Plus，您可以简单地使用我制作的这个自定义GPT，它考虑了GA4和Search Console的表模式。在上述指南中，我假设您正在使用免费版，并演示了如何使用ChatGPT整体运行BigQuery。

如果您想了解该自定义GPT中有什么内容，请查看后端的截图。

没有复杂的内容 - 您只需要从BigQuery中以JSON的形式复制表格，并将其上传到自定义GPT中，以便它可以参考表结构。此外，还有一个提示，要求GPT在撰写查询时参考附加的JSON文件。

这是另一个说明，说明了如何使用ChatGPT更有效地执行任务，消除重复的任务。

如果您需要处理另一个数据集（不同于GA4或GSC）且不了解SQL，您可以将BigQuery中的表模式上传到ChatGPT，并撰写适用于该表结构的SQL。很简单，对吧？

作业建议您分析哪些查询受到AI概述的影响。

Google Search Console表中没有区分符，但您可以运行一个查询，看看哪些页面在2024年5月14日之后出现了CTR大幅下降，当时Google推出了AI概述。

您可以将2024年5月14日之后的两周与之前的两周进行比较。仍然有可能是由于其他搜索功能，比如竞争对手获得了精选摘要，导致了CTR下降，但您应该能找到足够的有效案例，显示您的点击率受到了AI概述（以前是Search Generative Experience或“SGE”）的影响。

## 如何将搜索流量数据与GA4的参与指标结合起来

在分析搜索流量时，理解用户与内容的互动有多重要，因为用户参与信号是排名因素。请注意，我指的不是GA4中定义的确切指标。

但是，GA4的参与指标（例如“每次会话的平均参与时间”，即用户浏览器中您的网站处于焦点的平均时间）可能暗示您的文章是否足够吸引用户阅读。

如果该指标太低，这意味着您的博客页面可能存在问题，并且用户不会阅读它们。

如果您将该指标与搜索控制台数据结合起来，您可能会发现排名较低的页面也具有较低的平均每次会话的参与时间。

请注意，GA4和GSC具有不同的来源归因模型。GA4使用最后点击归因模型，这意味着如果有人从Google首次访问文章页面，然后直接再次访问两次，GA4可能会将这三次访问归因给Google，而GSC只会报告一次。

因此，这不是100%准确且可能不适合企业报告，但从GA4获取参与指标并与GSC数据结合提供了有价值的信息，可以分析排名与参与之间的相关性。

要使用ChatGPT处理BigQuery需要一些准备工作。在我们进行提示之前，建议您阅读GA4表的结构，因为它不像GSC的表那样简单。

它有一个event_params列，其中包含记录类型，并包含诸如`page_location`，`ga_session_id`和`engagement_time_msec`之类的维度。它跟踪用户在网站上积极参与的时间。

event_params关键字`engagement_time_msec`不是站点上的总时间，而是在特定互动上花费的时间（如单击或滚动），每次互动都会增加新的参与时间。这就像将用户积极使用您的网站或应用的所有小时刻相加。

因此，如果我们将该指标求和，并将其对会话平均，则我们可以获得每次会话的平均参与时间。

现在，一旦您了解了`engagement_time_msec`，让我们让ChatGPT帮助我们构建一个查询，该查询会为每个URL提取GA4的“每次会话的平均参与时间”，并将其与GSC文章的搜索性能数据结合起来。

我将使用的提示是：

想象一下，您是一位在Google Analytics 4（GA4），Google Search Console，SQL和BigQuery方面经验丰富的数据分析师。撰写一条SQL查询，从Google Search Console中提取以下数据，对于先前的7天，不包括当天：

点击次数，
曝光次数，
平均位置（计算为位置之和除以曝光之和）。

从GA4 BigQuery表中展开event_params ga_session_id，engagement_time_msec和page_location。仅选择具有非null值的engagement_time_msec的行，将所有具有相同ID和page_location的会话汇总并将engagement_time_msec求和，然后除以会话数求和。

通过URL在相同时期结合GA4和Google Search Console数据。另外，优化查询，从GA4的表分区中提取，而不是查询整个表。

详细信息：

BigQuery项目名称：use_your_bigquery_projectname
GA4数据集名称：use_your_ga4_bigquery_dataset_name
GA4表名称：use_your_ga4_bigquery_table_name_under_dataset
Google Search Console数据集名称：use_your_gsc_bigquery_table_name_under_dataset
Google Search Console表名称：use_your_gsc_bigquery_table_name_under_dataset
在这里这里是用于GA4的BigQuery表架构: [粘贴表架构在这里] 这里是用于Google Search Console的BigQuery表架构: [粘贴表架构在这里]

一旦我将其复制并粘贴到BigQuery中，结果显示“每次会话的平均参与时间”全部为空。因此，显然，ChatGPT需要更多关于GA4工作原理的上下文和指导。

我通过提供额外的知识 (opens new window)作为GA4官方文档的后续问题，解释了它如何计算engagement_time_msec。我将文档复制并粘贴到后续提示中，并要求在撰写查询时参考该知识，这有所帮助。（如果出现任何语法错误，只需将其复制/粘贴为后续问题，并要求修复。）

在15分钟的努力后，我得到了正确的SQL：


WITH
-- 步骤1：过滤GA4数据至过去7天并展开event_params
ga4_data AS (
  SELECT
    event_date,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
  FROM
    `your_bq_project_name.your_bq_ga4_dataset.events_*`
  WHERE
     _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY))
    AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') IS NOT NULL
),

-- 步骤2：按会话ID和页面位置对GA4数据进行聚合
aggregated_ga4_data AS (
  SELECT
    page_location,
    SUM(engagement_time_msec/1000) / NULLIF(COUNT(DISTINCT ga_session_id), 0) AS avg_engagement_time_msec
  FROM
    ga4_data
  GROUP BY
    page_location
),
-- 步骤3：过滤过去7天的GSC数据并选择有点击的URL
gsc_data AS (
  SELECT
    url,
    SUM(clicks) AS clicks,
    SUM(impressions) AS impressions,
    SUM(sum_position) / SUM(impressions) AS avg_position
  FROM
    `your_bq_project_name.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    and 
    clicks > 0
  GROUP BY
    url
)

-- 通过page_location和url将Google Search Console数据与GA4数据进行连接
SELECT
  gsc.url,
  gsc.clicks,
  gsc.impressions,
  gsc.avg_position,
  ga4.avg_engagement_time_msec
FROM
  gsc_data AS gsc
LEFT JOIN
  aggregated_ga4_data AS ga4
ON
  gsc.url = ga4.page_location
ORDER BY
  gsc.clicks DESC;

这从GA4提取了具有来自GA4的参与指标的GSC数据。

请注意，您可能会注意到GA4 UI中的数字与从BigQuery表中查询的数据存在差异。

这是因为GA4专注于“活跃用户”，并将稀有数据点分组到“(其他)”类别中，而BigQuery显示所有原始数据。当未给出同意时，GA4还使用建模数据填充间隙，而BigQuery不包括这些数据。

此外，GA4可能对数据进行抽样以快速生成报告，而BigQuery包含所有数据。这些变化意味着GA4提供了快速概览，而BigQuery提供了详细分析。在这篇文章 (opens new window)中详细了解为什么会发生这种情况。

也许您可以尝试修改查询以仅包括活跃用户，从而使结果更接近GA4 UI。

或者，您可以使用Looker Studio来混合数据，但是它存在限制 (opens new window)，在处理非常大的数据集时有限制。BigQuery通过高效处理几TB的数据提供了可扩展性，使其成为大规模SEO报告和详细分析的理想选择。

其高级SQL功能允许进行复杂查询，以获得比Looker Studio或其他仪表板工具更深入的洞察。

结论 #

利用ChatGPT的编码能力为您的报告需求撰写BigQuery查询将提升您的水平，并开辟新的领域，使您可以结合多个数据源。

这展示了ChatGPT如何简化复杂的数据分析任务，使您能够专注于战略决策。

与此同时，这些例子向我们表明，人类绝对需要操作AI聊天机器人，因为它们可能产生幻觉或给出错误答案。

更多资源：

特色图片：NicoElNino/Shutterstock