10. 数据测量、GSC、诊断与实验 · 资料陈述 · 教学步骤 7

用真实行为校准关键词

有访问后，关键词价值应加入任务完成和转化。

原知识点：有真实访问后，再加入着陆后的停留/参与、跳出和转化信息校准关键词。

执行步骤

1. 把GSC查询导出到规范化URL与日期粒度，并记录国家、设备和搜索类型。
2. 把站内事件按同一canonical URL、日期和匿名cohort聚合，先去除机器人、员工和测试。
3. 建立URL重定向/canonical映射与时区转换表，再做左连接，保留无行为数据的搜索行。
4. 计算点击到任务开始、完成、支付和贡献毛利的区间，标出小样本。
5. 按需求主题而非单个匿名查询汇总，给出继续、调整或降级及复查日。

可复制工作表

# 数据口径
GSC完整日期/时区：__
GSC字段：date, query, page, country, device, impressions, clicks
站内事件时间字段/时区：event_time_utc / UTC
站内字段：event_time_utc, canonical_url, starts, completes, payments, contribution_margin
过滤：机器人__/员工__/测试__/重复request_id__
URL映射表：raw_url, canonical_url, valid_from, valid_to（valid_to为不含当日的上界）
页面主题表：canonical_url, intent_theme, valid_from, valid_to；同一页面日在发布前只能匹配一个主题

# 查询诊断SQL：只连接URL，不下推站内结果
```sql
WITH gsc_query AS (
  SELECT g.date, g.query,
         COALESCE(m.canonical_url, g.page) AS canonical_url,
         CASE WHEN m.canonical_url IS NULL THEN 0 ELSE 1 END AS url_mapped,
         SUM(g.impressions) AS impressions, SUM(g.clicks) AS clicks
  FROM gsc_export g
  LEFT JOIN url_map m
    ON g.page=m.raw_url
   AND g.date>=m.valid_from
   AND (m.valid_to IS NULL OR g.date<m.valid_to)
  WHERE g.date BETWEEN '__' AND '__'
    AND g.country='__' AND g.device='__'
  GROUP BY 1,2,3,4
)
SELECT date, query, canonical_url, url_mapped, impressions, clicks
FROM gsc_query;
```

# 页面业务漏斗SQL：先聚合页面日，再一对一连接站内事件
```sql
WITH gsc_mapped AS (
  SELECT g.date,
         COALESCE(m.canonical_url, g.page) AS canonical_url,
         CASE WHEN m.canonical_url IS NULL THEN 0 ELSE 1 END AS url_mapped,
         SUM(g.impressions) AS impressions, SUM(g.clicks) AS clicks
  FROM gsc_export g
  LEFT JOIN url_map m
    ON g.page=m.raw_url
   AND g.date>=m.valid_from
   AND (m.valid_to IS NULL OR g.date<m.valid_to)
  WHERE g.date BETWEEN '__' AND '__'
    AND g.country='__' AND g.device='__'
  GROUP BY 1,2,3
), gsc_page AS (
  SELECT date, canonical_url, MIN(url_mapped) AS url_mapped,
         SUM(impressions) AS impressions, SUM(clicks) AS clicks
  FROM gsc_mapped
  GROUP BY 1,2
), events AS (
  SELECT CAST(event_time_utc AT TIME ZONE '__GSC时区__' AS DATE) AS date,
         canonical_url,
         SUM(starts) AS starts, SUM(completes) AS completes,
         SUM(payments) AS payments,
         SUM(contribution_margin) AS contribution_margin
  FROM onsite_events
  WHERE event_time_utc>=__UTC开始__ AND event_time_utc<__UTC结束__
    AND is_test=false AND is_bot=false AND is_employee=false
  GROUP BY 1,2
)
SELECT g.date, g.canonical_url, t.intent_theme,
       g.url_mapped, g.impressions, g.clicks,
       e.starts, e.completes, e.payments, e.contribution_margin,
       CASE WHEN e.canonical_url IS NULL THEN 0 ELSE 1 END AS behavior_matched
FROM gsc_page g
LEFT JOIN page_theme_map t
  ON g.canonical_url=t.canonical_url
 AND g.date>=t.valid_from
 AND (t.valid_to IS NULL OR g.date<t.valid_to)
LEFT JOIN events e
  ON g.date=e.date AND g.canonical_url=e.canonical_url;
```

# 质量与公式
GSC查询行数：__；URL映射行：__；URL映射率=__/__=__
聚合后页面日：__；站内匹配页面日：__；行为匹配率=__/__=__
主题唯一映射率：__；未映射URL/主题进入队列：__
缺失不填0的字段：starts, completes, payments, contribution_margin
完成率=completes/starts：__
支付率=payments/completes：__
每点击贡献毛利=contribution_margin/clicks：__
禁止：把页面日站内结果复制到query行并声称关键词级转化

# 主题决策（每个页面只能归一个预锁定需求主题）
|主题|曝光|点击|开始|完成|支付|贡献毛利|样本/区间|最早断点|决定|动作/复查日|
|---|---:|---:|---:|---:|---:|---:|---|---|---|---|
|__|__|__|__|__|__|__|__|__|继续/调整/降级|__|

完整示例

完成清单

• 查询诊断与页面业务漏斗分别提供可直接改写的SQL，URL有效期、日期时区与过滤口径明确。
• 查询行只承载搜索曝光和点击；站内事件只在页面日汇总一次，没有复制成关键词级转化。
• 连接保留未匹配行，分别报告URL映射率、行为匹配率和主题唯一映射率，缺失值未静默变成业务0。
• 每个页面主题计算从点击到任务、支付和贡献毛利的完整链，并输出最早断点、动作与复查日。

适用条件

埋点、样本量、页面类型和归因窗口必须可靠。

来源：Iris：一个搜索引擎出海人的观察和思考