生产级谷歌广告分析系统架构设计方案
下面给你一套 生产级（企业用） 的数据表设计方案，用于整合：
- Google Ads API
- gclid
- 搜索字词 (search term)
- 匹配关键词 (matched keyword)
- campaign/ad group/ad
- impressions/clicks/ctr/cpc/cost
- landing page
- conversion / conversion value
- GA4 API / BigQuery Export
- session_id
- user_pseudo_id
- 页面浏览历史 (pageview list)
- 停留时长
- 用户访问次数 (session_count)
- 来源/媒介/source_medium/utm
- 转化事件
- 询盘 (Lead)
- 表单提交数据
- 来源归因（Ads or SEO）
并最终实现 广告&SEO全链路分析系统：
从广告关键词 → 点击 → 会话 → 浏览行为 → 表单 → 询盘 → 转化价值
形成完整客户路径（Click → Session → Lead → Conversion）。
🚀 一、总体数据库结构（核心理念）
数据库由 6 张核心表 + 2 张辅助维表组成：
✔️ 核心 6 张事实表（Fact Tables）
fact_ads_clicks（广告点击级别）fact_ga4_sessions（GA4 会话级别）fact_ga4_events（GA4 事件级别）fact_leads（询盘级别）fact_ads_conversions（Ads 转化级别）fact_lead_attribution（最终归因表，最关键）
✔️ 维表（Dimension Tables）
dim_keywordsdim_campaigns
接下来给你完整字段设计，每张表都可直接用于 MySQL / PostgreSQL / BigQuery。
🧱 二、数据表详细设计（完全可落地）
① fact_ads_clicks（广告点击级别表）
来源：Google Ads API → GAQL（search_term_view / click_view）
CREATE TABLE fact_ads_clicks ( gclid STRING, click_time DATETIME, campaign_id STRING, campaign_name STRING, ad_group_id STRING, ad_group_name STRING, ad_id STRING, keyword_id STRING, keyword_text STRING, match_type STRING, -- exact/broad/phrase search_term STRING, final_url STRING, device STRING, impressions INT, clicks INT, cost_micros BIGINT, currency_code STRING, created_at DATETIME );
用途：
- 按 gclid → lead → conversion 完整追踪
- 分析搜索字词 / 关键词 / 平台 / 设备
- 计算广告 ROI / CPA / ROAS
② fact_ga4_sessions（会话级别表）
来源：GA4 BigQuery Export 或 GA4 API → runReport
CREATE TABLE fact_ga4_sessions ( session_id STRING, -- ga_session_id user_pseudo_id STRING, -- 匿名用户ID gclid STRING, -- GA4 自动提取 source STRING, medium STRING, campaign STRING, utm_term STRING, landing_page STRING, device_category STRING, session_start DATETIME, session_end DATETIME, engagement_time_ms INT, page_view_count INT, session_number INT, -- 访问次数（GA4: ga_session_number） created_at DATETIME );
用途：
- 分析 SEO vs 广告流量
- 落地页效果
- 用户停留时间、互动度
③ fact_ga4_events（用户行为事件级表）
来自 GA4 BigQuery Export（event table）
CREATE TABLE fact_ga4_events ( event_id STRING, session_id STRING, user_pseudo_id STRING, event_name STRING, event_time DATETIME, page_location STRING, page_referrer STRING, engagement_time INT, gclid STRING, event_params JSON );
用途：
- 构建用户浏览路径（page flow）
- 分析用户行为：滚动、输出、按钮点击
- 观察 SEO/Ads 用户质量（互动度）
④ fact_leads（询盘级别表）
来自：WordPress 表单 / CRM
CREATE TABLE fact_leads ( lead_id STRING, name STRING, email STRING, phone STRING, message STRING, gclid STRING, ga_session_id STRING, user_pseudo_id STRING, landing_page STRING, utm_source STRING, utm_medium STRING, utm_campaign STRING, utm_term STRING, ip_address STRING, user_agent STRING, created_at DATETIME );
用途：
- 询盘来源识别（广告 or SEO）
- 按 gclid 绑定 Ads
- 按 session_id 绑定 GA4 会话
- 全链路漏斗分析
⑤ fact_ads_conversions（广告转化表）
来自：Google Ads API 或 UploadConversion API
CREATE TABLE fact_ads_conversions ( conversion_id STRING, gclid STRING, conversion_action STRING, conversion_time DATETIME, conversion_value DECIMAL(10,2), conversion_currency STRING, created_at DATETIME );
用途：
- 广告真实转化
- 用于与 leads 关联后分析 ROI/ROAS
⑥ fact_lead_attribution（最终归因表）
这个表是整个系统最终使用的 分析核心表。
CREATE TABLE fact_lead_attribution ( lead_id STRING, gclid STRING, session_id STRING, user_pseudo_id STRING, -- Ads 信息 campaign_id STRING, campaign_name STRING, ad_group_id STRING, ad_group_name STRING, keyword_id STRING, keyword_text STRING, match_type STRING, search_term STRING, device STRING, final_url STRING, click_time DATETIME, cost_micros BIGINT, -- GA4 会话行为 landing_page STRING, pageview_count INT, engagement_time_ms INT, session_number INT, -- 转化 conversion_value DECIMAL(10,2), conversion_time DATETIME, conversion_action STRING, attribution_model STRING, -- last_click / data_driven / position_based attribution_weight DECIMAL(5,4), created_at DATETIME );
这是投手、老板最需要的表。
每一行 = 一个 Lead 及其完整来源信息。
🧱 三、维表（供 JOIN 和报表使用）
⑦ dim_keywords（关键词维表）
CREATE TABLE dim_keywords ( keyword_id STRING, keyword_text STRING, match_type STRING, status STRING, created_at DATETIME );
⑧ dim_campaigns
CREATE TABLE dim_campaigns ( campaign_id STRING, campaign_name STRING, budget DECIMAL(10,2), status STRING, created_at DATETIME );
🎯 四、关键 Join 逻辑（全链路贯通）
1. Ads → Lead（通过 gclid）
SELECT * FROM fact_ads_clicks a JOIN fact_leads l ON a.gclid = l.gclid
2. GA4 Session → Lead（通过 session_id）
SELECT * FROM fact_ga4_sessions s JOIN fact_leads l ON s.session_id = l.ga_session_id
3. GA4 Events → Session（session_id）
SELECT * FROM fact_ga4_events e JOIN fact_ga4_sessions s ON e.session_id = s.session_id
4. Ads Conversion → Lead
SELECT * FROM fact_ads_conversions c JOIN fact_leads l USING (gclid)
5. 最终归因写入 fact_lead_attribution（ETL 逻辑）
- 使用 Last Click
- 或使用 GA4 data-driven 模型
- 或自定义模型
🧠 五、这套表能分析什么？（专业投手级）
你的分析能力将提升到：
✔ 广告效果（投手 KPI）
- search term 的 真实询盘产生率
- 每个关键词的 CPA / ROAS / 转化率
- 每个落地页的 询盘产出能力
- 设备性能（mobile/desktop）
- 广告花费 vs Lead 价值曲线
✔ SEO 效果
- 拿到会话 → 页面浏览 → 表单提交 → lead
- 每个自然关键词 / 页面转化率
- 用户停留时长、跳出率、流量质量
✔ 用户行为路径（GA4）
- 点击广告后访问了哪些页面？
- 停留多久？
- 看了几页？
- 最后在哪个页面提交表单？
✔ 询盘全链路数据（老板级）
“哪个关键词 → 产生了哪些询盘 → 哪些询盘成单 → 赚了多少钱”
=====================最终优化方案如下===============================
生产级广告&SEO全链路分析系统 – 数据库设计与实现方案
📋 目录
一、方案概述
1.1 业务目标
构建一个生产级的广告&SEO全链路分析系统，实现从广告关键词 → 点击 → 会话 → 浏览行为 → 表单 → 询盘 → 转化价值的完整客户路径追踪。
1.2 核心价值
- 广告效果精准分析：每个关键词的ROI、CPA、ROAS
- SEO效果评估：自然流量的转化路径和质量
- 用户行为洞察：完整的用户浏览路径和交互行为
- 数据驱动决策：支持多维度的数据分析和报表
1.3 技术架构
数据源层
├── Google Ads API (GAQL查询)
├── GA4 BigQuery Export / GA4 API
└── WordPress 表单提交
数据采集层
├── 实时采集（询盘提交时）
├── 异步同步（后台任务）
└── 批量导入（历史数据）
数据存储层
├── 6张事实表（Fact Tables）
├── 2张维表（Dimension Tables）
└── 1张归因表（Attribution Table）
分析应用层
├── ROI/ROAS计算
├── 转化路径分析
├── 用户行为分析
└── 报表可视化
二、数据库架构设计
2.1 表结构总览
核心事实表（6张）
├── fact_ads_clicks (广告点击级别)
├── fact_ga4_sessions (GA4会话级别)
├── fact_ga4_events (GA4事件级别)
├── fact_leads (询盘级别)
├── fact_ads_conversions (Ads转化级别)
└── fact_lead_attribution (最终归因表)
维表（2张）
├── dim_keywords (关键词维表)
└── dim_campaigns (广告系列维表)
辅助表（可选）
├── dim_devices (设备维表)
├── dim_landing_pages (着陆页维表)
└── dim_geo_locations (地理位置维表)
2.2 数据流向图
Google Ads API
↓
fact_ads_clicks (gclid)
↓
fact_ads_conversions
↓
┌──────────────┐
│ │
GA4 API/BigQuery │
↓ │
fact_ga4_sessions │
↓ │
fact_ga4_events │
↓ │
└──────────────┘
↓
fact_leads (关联gclid/session_id)
↓
fact_lead_attribution (最终归因)
三、表结构详细设计（优化版）
3.1 fact_ads_clicks（广告点击级别表）
优化点：
- 添加分区字段（按日期分区）
- 添加时区字段
- 优化索引策略
- 添加数据质量字段
CREATE TABLE fact_ads_clicks (
-- 主键和关联
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
gclid VARCHAR(255) NOT NULL COMMENT 'Google Click ID',
-- 时间字段
click_date DATE NOT NULL COMMENT '点击日期（用于分区）',
click_time DATETIME NOT NULL COMMENT '点击时间',
click_timezone VARCHAR(50) DEFAULT 'UTC' COMMENT '时区',
-- 广告层级信息
account_id VARCHAR(50) COMMENT '广告账户ID',
account_name VARCHAR(255) COMMENT '广告账户名称',
campaign_id VARCHAR(50) NOT NULL COMMENT '广告系列ID',
campaign_name VARCHAR(255) COMMENT '广告系列名称',
ad_group_id VARCHAR(50) COMMENT '广告组ID',
ad_group_name VARCHAR(255) COMMENT '广告组名称',
ad_id VARCHAR(50) COMMENT '广告ID',
ad_type VARCHAR(50) COMMENT '广告类型：search, display, video等',
-- 关键词信息
keyword_id VARCHAR(50) COMMENT '关键词ID',
keyword_text VARCHAR(500) COMMENT '关键词文本',
match_type VARCHAR(20) COMMENT '匹配类型：exact, phrase, broad, bmm',
search_term VARCHAR(500) COMMENT '搜索字词（用户实际搜索的词）',
-- 网址和位置
final_url TEXT COMMENT '最终URL',
display_url VARCHAR(500) COMMENT '显示URL',
landing_page TEXT COMMENT '着陆页URL',
-- 设备和位置
device VARCHAR(50) COMMENT '设备类型：mobile, desktop, tablet',
network_type VARCHAR(50) COMMENT '网络类型：search, display, video, shopping',
country_code VARCHAR(10) COMMENT '国家代码',
location VARCHAR(255) COMMENT '地理位置',
-- 广告指标
impressions INT UNSIGNED DEFAULT 0 COMMENT '展示次数',
clicks INT UNSIGNED DEFAULT 0 COMMENT '点击次数',
ctr DECIMAL(10,4) COMMENT '点击率',
cost_micros BIGINT UNSIGNED DEFAULT 0 COMMENT '花费（微单位）',
cost DECIMAL(15,2) COMMENT '花费（实际金额）',
currency_code VARCHAR(10) DEFAULT 'USD' COMMENT '货币代码',
avg_cpc DECIMAL(15,2) COMMENT '平均每次点击费用',
-- 质量指标
quality_score INT COMMENT '质量得分（1-10）',
expected_ctr DECIMAL(10,4) COMMENT '预期点击率',
ad_relevance INT COMMENT '广告相关性',
landing_page_exp INT COMMENT '着陆页体验',
-- 数据质量字段
data_source VARCHAR(50) DEFAULT 'google_ads_api' COMMENT '数据来源',
sync_status VARCHAR(20) DEFAULT 'synced' COMMENT '同步状态：pending, synced, failed',
sync_time DATETIME COMMENT '同步时间',
raw_data JSON COMMENT '原始API响应数据（JSON格式）',
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 索引
INDEX idx_gclid (gclid),
INDEX idx_click_date (click_date),
INDEX idx_campaign_id (campaign_id),
INDEX idx_keyword_id (keyword_id),
INDEX idx_search_term (search_term(100)),
INDEX idx_device (device),
INDEX idx_click_time (click_time),
INDEX idx_sync_status (sync_status),
-- 唯一索引（防止重复）
UNIQUE KEY uk_gclid_click_time (gclid, click_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(click_date)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
-- 可以继续添加更多分区
);
关键优化：
- ✅ 按日期分区，提高查询性能
- ✅ 添加质量得分等字段
- ✅ 添加同步状态，支持增量同步
- ✅ 保存原始数据，便于问题排查
- ✅ 唯一索引防止重复数据
3.2 fact_ga4_sessions（GA4会话级别表）
优化点：
- 添加会话时长计算字段
- 添加参与度指标
- 优化索引策略
CREATE TABLE fact_ga4_sessions (
-- 主键和关联
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
session_id VARCHAR(255) NOT NULL COMMENT 'GA4会话ID',
user_pseudo_id VARCHAR(255) NOT NULL COMMENT 'GA4用户伪ID',
user_id VARCHAR(255) COMMENT '用户ID（如果已登录）',
-- GCLID关联
gclid VARCHAR(255) COMMENT 'Google Click ID（如果有）',
-- 会话时间
session_date DATE NOT NULL COMMENT '会话日期（用于分区）',
session_start DATETIME NOT NULL COMMENT '会话开始时间',
session_end DATETIME COMMENT '会话结束时间',
session_duration INT UNSIGNED COMMENT '会话时长（秒）',
-- 来源信息
source VARCHAR(255) COMMENT '来源',
medium VARCHAR(255) COMMENT '媒介',
campaign VARCHAR(255) COMMENT '广告系列',
term VARCHAR(500) COMMENT '关键词',
content VARCHAR(500) COMMENT '内容',
-- UTM参数
utm_source VARCHAR(255) COMMENT 'UTM来源',
utm_medium VARCHAR(255) COMMENT 'UTM媒介',
utm_campaign VARCHAR(255) COMMENT 'UTM广告系列',
utm_term VARCHAR(500) COMMENT 'UTM关键词',
utm_content VARCHAR(500) COMMENT 'UTM内容',
-- 页面信息
landing_page TEXT COMMENT '着陆页URL',
landing_page_path VARCHAR(500) COMMENT '着陆页路径',
exit_page TEXT COMMENT '退出页URL',
-- 设备和位置
device_category VARCHAR(50) COMMENT '设备类别',
device_brand VARCHAR(100) COMMENT '设备品牌',
device_model VARCHAR(100) COMMENT '设备型号',
operating_system VARCHAR(100) COMMENT '操作系统',
browser VARCHAR(100) COMMENT '浏览器',
country VARCHAR(100) COMMENT '国家',
city VARCHAR(100) COMMENT '城市',
region VARCHAR(100) COMMENT '地区',
-- 会话指标
page_view_count INT UNSIGNED DEFAULT 0 COMMENT '页面浏览量',
session_number INT UNSIGNED DEFAULT 1 COMMENT '会话序号（第几次访问）',
engagement_time_ms INT UNSIGNED DEFAULT 0 COMMENT '参与时间（毫秒）',
engagement_rate DECIMAL(10,4) COMMENT '参与率',
bounce_rate DECIMAL(10,4) COMMENT '跳出率',
is_bounce TINYINT(1) DEFAULT 0 COMMENT '是否跳出',
-- 转化指标
conversion_count INT UNSIGNED DEFAULT 0 COMMENT '转化次数',
conversion_value DECIMAL(15,2) DEFAULT 0 COMMENT '转化价值',
-- 数据质量字段
data_source VARCHAR(50) DEFAULT 'ga4_api' COMMENT '数据来源',
property_id VARCHAR(50) COMMENT 'GA4属性ID',
sync_status VARCHAR(20) DEFAULT 'synced',
sync_time DATETIME,
raw_data JSON COMMENT '原始数据',
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 索引
INDEX idx_session_id (session_id),
INDEX idx_user_pseudo_id (user_pseudo_id),
INDEX idx_gclid (gclid),
INDEX idx_session_date (session_date),
INDEX idx_source_medium (source, medium),
INDEX idx_utm_campaign (utm_campaign),
INDEX idx_session_start (session_start),
INDEX idx_sync_status (sync_status),
-- 唯一索引
UNIQUE KEY uk_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(session_date)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
3.3 fact_ga4_events（GA4事件级别表）
优化点：
- 使用JSON字段存储事件参数
- 优化查询性能
- 支持事件序列分析
CREATE TABLE fact_ga4_events (
-- 主键
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-- 关联字段
event_id VARCHAR(255) NOT NULL COMMENT '事件ID（唯一）',
session_id VARCHAR(255) NOT NULL COMMENT '会话ID',
user_pseudo_id VARCHAR(255) NOT NULL COMMENT '用户伪ID',
-- 事件信息
event_date DATE NOT NULL COMMENT '事件日期（用于分区）',
event_time DATETIME NOT NULL COMMENT '事件时间',
event_name VARCHAR(255) NOT NULL COMMENT '事件名称',
-- 页面信息
page_location TEXT COMMENT '页面URL',
page_path VARCHAR(500) COMMENT '页面路径',
page_title VARCHAR(500) COMMENT '页面标题',
page_referrer TEXT COMMENT '来源页面',
-- 事件参数（JSON格式存储所有参数）
event_params JSON COMMENT '事件参数（JSON格式）',
-- 常用参数（提取到独立字段便于查询）
engagement_time INT UNSIGNED COMMENT '参与时间（秒）',
value DECIMAL(15,2) COMMENT '事件价值',
currency VARCHAR(10) COMMENT '货币',
-- 设备和位置
device_category VARCHAR(50),
country VARCHAR(100),
city VARCHAR(100),
-- 数据质量
data_source VARCHAR(50) DEFAULT 'ga4_bigquery',
property_id VARCHAR(50),
sync_status VARCHAR(20) DEFAULT 'synced',
sync_time DATETIME,
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- 索引
INDEX idx_event_id (event_id),
INDEX idx_session_id (session_id),
INDEX idx_user_pseudo_id (user_pseudo_id),
INDEX idx_event_date (event_date),
INDEX idx_event_name (event_name),
INDEX idx_event_time (event_time),
INDEX idx_session_event (session_id, event_time),
-- 唯一索引
UNIQUE KEY uk_event_id (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(event_date)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
事件参数JSON结构示例：
{
"page_title": "产品详情页",
"page_location": "https://example.com/products/123",
"engagement_time_msec": 5000,
"link_url": "https://example.com/contact",
"link_text": "联系我们",
"method": "click"
}
3.4 fact_leads（询盘级别表）
优化点：
- 关联所有来源标识符
- 添加质量评分字段
- 支持多表单类型
CREATE TABLE fact_leads (
-- 主键
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
lead_id VARCHAR(100) NOT NULL COMMENT '询盘ID（业务唯一标识）',
-- 关联字段（用于关联Ads和GA4数据）
gclid VARCHAR(255) COMMENT 'Google Click ID',
session_id VARCHAR(255) COMMENT 'GA4会话ID',
user_pseudo_id VARCHAR(255) COMMENT 'GA4用户伪ID',
-- 询盘基本信息
name VARCHAR(255) COMMENT '姓名',
email VARCHAR(255) NOT NULL COMMENT '邮箱',
phone VARCHAR(50) COMMENT '电话',
company VARCHAR(255) COMMENT '公司',
message TEXT COMMENT '留言内容',
subject VARCHAR(500) COMMENT '主题',
-- 表单信息
form_id VARCHAR(100) COMMENT '表单ID',
form_name VARCHAR(255) COMMENT '表单名称',
form_type VARCHAR(50) COMMENT '表单类型：contact, quote, demo等',
-- 来源信息
landing_page TEXT COMMENT '着陆页URL',
referrer_url TEXT COMMENT '来源页面URL',
utm_source VARCHAR(255) COMMENT 'UTM来源',
utm_medium VARCHAR(255) COMMENT 'UTM媒介',
utm_campaign VARCHAR(255) COMMENT 'UTM广告系列',
utm_term VARCHAR(500) COMMENT 'UTM关键词',
utm_content VARCHAR(500) COMMENT 'UTM内容',
-- 位置信息
ip_address VARCHAR(45) COMMENT 'IP地址',
country VARCHAR(100) COMMENT '国家',
region VARCHAR(100) COMMENT '省份/地区',
city VARCHAR(100) COMMENT '城市',
-- 设备信息
user_agent TEXT COMMENT '用户代理',
device_type VARCHAR(50) COMMENT '设备类型',
browser VARCHAR(100) COMMENT '浏览器',
os VARCHAR(100) COMMENT '操作系统',
-- 时间信息
lead_date DATE NOT NULL COMMENT '询盘日期（用于分区）',
lead_time DATETIME NOT NULL COMMENT '询盘提交时间',
first_visit_time DATETIME COMMENT '首次访问时间',
time_to_lead INT COMMENT '从首次访问到询盘的时间（秒）',
-- 质量评分
lead_score INT COMMENT '询盘质量评分（1-100）',
lead_status VARCHAR(50) DEFAULT 'new' COMMENT '状态：new, contacted, qualified, converted, lost',
is_qualified TINYINT(1) DEFAULT 0 COMMENT '是否合格',
-- 转化价值
estimated_value DECIMAL(15,2) COMMENT '预估价值',
actual_value DECIMAL(15,2) COMMENT '实际价值（成交后更新）',
-- 数据质量
data_source VARCHAR(50) DEFAULT 'wordpress_form',
sync_status VARCHAR(20) DEFAULT 'synced',
sync_time DATETIME,
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- 索引
INDEX idx_lead_id (lead_id),
INDEX idx_gclid (gclid),
INDEX idx_session_id (session_id),
INDEX idx_user_pseudo_id (user_pseudo_id),
INDEX idx_lead_date (lead_date),
INDEX idx_email (email),
INDEX idx_lead_status (lead_status),
INDEX idx_utm_campaign (utm_campaign),
INDEX idx_sync_status (sync_status),
-- 唯一索引
UNIQUE KEY uk_lead_id (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(lead_date)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
3.5 fact_ads_conversions（广告转化表）
优化点：
- 支持多个转化动作
- 关联原始点击数据
CREATE TABLE fact_ads_conversions (
-- 主键
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
conversion_id VARCHAR(255) NOT NULL COMMENT '转化ID（唯一）',
-- 关联字段
gclid VARCHAR(255) NOT NULL COMMENT 'GCLID（关联点击）',
-- 转化信息
conversion_date DATE NOT NULL COMMENT '转化日期（用于分区）',
conversion_time DATETIME NOT NULL COMMENT '转化时间',
conversion_action VARCHAR(255) NOT NULL COMMENT '转化动作名称',
conversion_action_id VARCHAR(100) COMMENT '转化动作ID',
-- 转化价值
conversion_value DECIMAL(15,2) DEFAULT 0 COMMENT '转化价值',
conversion_currency VARCHAR(10) DEFAULT 'USD' COMMENT '货币',
-- 转化类型
conversion_type VARCHAR(50) COMMENT '转化类型：click, view, call等',
conversion_category VARCHAR(50) COMMENT '转化类别：purchase, lead, signup等',
-- 关联询盘
lead_id VARCHAR(100) COMMENT '关联的询盘ID',
-- 数据质量
data_source VARCHAR(50) DEFAULT 'google_ads_api',
sync_status VARCHAR(20) DEFAULT 'synced',
sync_time DATETIME,
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-- 索引
INDEX idx_conversion_id (conversion_id),
INDEX idx_gclid (gclid),
INDEX idx_conversion_date (conversion_date),
INDEX idx_conversion_action (conversion_action),
INDEX idx_lead_id (lead_id),
-- 唯一索引
UNIQUE KEY uk_conversion_id (conversion_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(conversion_date)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
3.6 fact_lead_attribution（最终归因表）⭐核心表
优化点：
- 支持多种归因模型
- 完整保存归因逻辑
- 便于直接查询分析
CREATE TABLE fact_lead_attribution (
-- 主键
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-- 关联字段
lead_id VARCHAR(100) NOT NULL COMMENT '询盘ID',
gclid VARCHAR(255) COMMENT 'GCLID',
session_id VARCHAR(255) COMMENT '会话ID',
user_pseudo_id VARCHAR(255) COMMENT '用户伪ID',
-- ========== Ads 信息 ==========
account_id VARCHAR(50) COMMENT '广告账户ID',
account_name VARCHAR(255) COMMENT '广告账户名称',
campaign_id VARCHAR(50) COMMENT '广告系列ID',
campaign_name VARCHAR(255) COMMENT '广告系列名称',
ad_group_id VARCHAR(50) COMMENT '广告组ID',
ad_group_name VARCHAR(255) COMMENT '广告组名称',
ad_id VARCHAR(50) COMMENT '广告ID',
-- 关键词信息
keyword_id VARCHAR(50) COMMENT '关键词ID',
keyword_text VARCHAR(500) COMMENT '关键词文本',
match_type VARCHAR(20) COMMENT '匹配类型',
search_term VARCHAR(500) COMMENT '搜索字词',
-- Ads时间和成本
click_time DATETIME COMMENT '点击时间',
click_date DATE COMMENT '点击日期',
cost_micros BIGINT UNSIGNED DEFAULT 0 COMMENT '花费（微单位）',
cost DECIMAL(15,2) COMMENT '花费（实际金额）',
currency_code VARCHAR(10) DEFAULT 'USD' COMMENT '货币',
-- Ads指标
impressions INT UNSIGNED DEFAULT 0 COMMENT '展示次数',
clicks INT UNSIGNED DEFAULT 0 COMMENT '点击次数',
ctr DECIMAL(10,4) COMMENT '点击率',
quality_score INT COMMENT '质量得分',
-- 网址和位置
landing_page TEXT COMMENT '着陆页URL',
final_url TEXT COMMENT '最终URL',
device VARCHAR(50) COMMENT '设备类型',
country_code VARCHAR(10) COMMENT '国家代码',
-- ========== GA4 会话信息 ==========
source VARCHAR(255) COMMENT '来源',
medium VARCHAR(255) COMMENT '媒介',
utm_campaign VARCHAR(255) COMMENT 'UTM广告系列',
utm_term VARCHAR(500) COMMENT 'UTM关键词',
-- GA4行为指标
pageview_count INT UNSIGNED DEFAULT 0 COMMENT '页面浏览量',
session_duration INT UNSIGNED COMMENT '会话时长（秒）',
engagement_time_ms INT UNSIGNED COMMENT '参与时间（毫秒）',
session_number INT UNSIGNED COMMENT '会话序号',
bounce_rate DECIMAL(10,4) COMMENT '跳出率',
-- ========== 时间信息 ==========
first_visit_time DATETIME COMMENT '首次访问时间',
last_visit_time DATETIME COMMENT '最后访问时间',
conversion_time DATETIME NOT NULL COMMENT '转化时间（询盘提交时间）',
days_to_conversion INT COMMENT '从首次访问到转化的天数',
-- ========== 转化信息 ==========
conversion_action VARCHAR(255) COMMENT '转化动作',
conversion_value DECIMAL(15,2) DEFAULT 0 COMMENT '转化价值',
conversion_currency VARCHAR(10) DEFAULT 'USD' COMMENT '转化货币',
-- ========== 归因信息 ==========
attribution_model VARCHAR(50) DEFAULT 'last_click' COMMENT '归因模型：last_click, first_click, linear, time_decay, position_based, data_driven',
attribution_weight DECIMAL(5,4) DEFAULT 1.0000 COMMENT '归因权重（0-1）',
is_primary_source TINYINT(1) DEFAULT 1 COMMENT '是否为主要来源',
source_type VARCHAR(50) COMMENT '来源类型：ads, seo, direct, referral, email, social',
-- ========== 询盘质量 ==========
lead_score INT COMMENT '询盘质量评分',
lead_status VARCHAR(50) COMMENT '询盘状态',
estimated_value DECIMAL(15,2) COMMENT '预估价值',
actual_value DECIMAL(15,2) COMMENT '实际价值',
-- ========== 计算指标 ==========
-- ROI相关
roas DECIMAL(10,4) COMMENT 'ROAS = conversion_value / cost',
roi_percentage DECIMAL(10,2) COMMENT 'ROI百分比 = (conversion_value - cost) / cost * 100',
cpa DECIMAL(15,2) COMMENT 'CPA = cost / conversions',
-- 效率指标
cost_per_lead DECIMAL(15,2) COMMENT '每个询盘成本',
value_per_lead DECIMAL(15,2) COMMENT '每个询盘价值',
lead_to_value_ratio DECIMAL(10,4) COMMENT '询盘价值比',
-- ========== 数据质量 ==========
attribution_method VARCHAR(50) COMMENT '归因方法：automatic, manual, rule_based',
confidence_score DECIMAL(5,4) COMMENT '归因置信度（0-1）',
data_completeness DECIMAL(5,4) COMMENT '数据完整性（0-1）',
-- 时间戳
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- ========== 索引 ==========
INDEX idx_lead_id (lead_id),
INDEX idx_gclid (gclid),
INDEX idx_session_id (session_id),
INDEX idx_campaign_id (campaign_id),
INDEX idx_keyword_id (keyword_id),
INDEX idx_search_term (search_term(100)),
INDEX idx_conversion_time (conversion_time),
INDEX idx_conversion_date (DATE(conversion_time)),
INDEX idx_source_type (source_type),
INDEX idx_attribution_model (attribution_model),
INDEX idx_lead_status (lead_status),
-- 复合索引
INDEX idx_campaign_conversion (campaign_id, conversion_time),
INDEX idx_keyword_conversion (keyword_id, conversion_time),
-- 唯一索引（一个询盘一条归因记录）
UNIQUE KEY uk_lead_id (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
PARTITION BY RANGE (TO_DAYS(conversion_time)) (
PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01'))
);
这是整个系统的核心表，每一行代表一个询盘的完整归因信息。
3.7 维表设计
dim_keywords（关键词维表）
CREATE TABLE dim_keywords (
keyword_id VARCHAR(50) PRIMARY KEY,
keyword_text VARCHAR(500) NOT NULL,
match_type VARCHAR(20) COMMENT 'exact, phrase, broad, bmm',
-- 关键词分类
keyword_category VARCHAR(100) COMMENT '关键词类别',
keyword_group VARCHAR(100) COMMENT '关键词分组',
-- 状态
status VARCHAR(50) DEFAULT 'active' COMMENT 'active, paused, deleted',
bid_strategy VARCHAR(50) COMMENT '出价策略',
max_cpc DECIMAL(15,2) COMMENT '最高CPC',
-- 统计信息（定期更新）
total_clicks INT UNSIGNED DEFAULT 0,
total_cost DECIMAL(15,2) DEFAULT 0,
total_conversions INT UNSIGNED DEFAULT 0,
avg_quality_score DECIMAL(5,2) COMMENT '平均质量得分',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_status (status),
INDEX idx_keyword_text (keyword_text(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
dim_campaigns（广告系列维表）
CREATE TABLE dim_campaigns (
campaign_id VARCHAR(50) PRIMARY KEY,
account_id VARCHAR(50) NOT NULL,
account_name VARCHAR(255),
campaign_name VARCHAR(255) NOT NULL,
-- 系列类型
campaign_type VARCHAR(50) COMMENT 'search, display, video, shopping',
campaign_status VARCHAR(50) DEFAULT 'active' COMMENT 'active, paused, removed',
-- 预算信息
budget DECIMAL(15,2) COMMENT '预算',
budget_type VARCHAR(50) COMMENT 'daily, total',
budget_currency VARCHAR(10) DEFAULT 'USD',
-- 目标
campaign_goal VARCHAR(100) COMMENT 'campaign目标',
target_roas DECIMAL(10,4) COMMENT '目标ROAS',
target_cpa DECIMAL(15,2) COMMENT '目标CPA',
-- 统计信息（定期更新）
total_clicks INT UNSIGNED DEFAULT 0,
total_cost DECIMAL(15,2) DEFAULT 0,
total_conversions INT UNSIGNED DEFAULT 0,
avg_roas DECIMAL(10,4) COMMENT '平均ROAS',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_account_id (account_id),
INDEX idx_status (campaign_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
四、数据同步与ETL流程
4.1 数据采集流程
┌─────────────────────────────────────────────────────────────┐
│ 数据采集流程 │
└─────────────────────────────────────────────────────────────┘
1. 实时采集（询盘提交时）
├── 从URL参数获取：gclid, utm参数
├── 从Cookie获取：_ga, _ga_session_id
├── 从Session获取：用户行为数据
└── 创建 fact_leads 记录
2. 异步同步（后台任务）
├── 通过GCLID拉取Google Ads数据 → fact_ads_clicks
├── 通过Session ID拉取GA4数据 → fact_ga4_sessions
├── 拉取GA4事件数据 → fact_ga4_events
└── 拉取Ads转化数据 → fact_ads_conversions
3. 定期批量同步
├── 每日同步前一天的完整数据
├── 每周同步历史数据
└── 每月数据归档和清理
4.2 ETL归因流程
-- 归因ETL逻辑（伪代码）
-- 步骤1：从fact_leads获取所有询盘
-- 步骤2：通过gclid关联fact_ads_clicks
-- 步骤3：通过session_id关联fact_ga4_sessions和fact_ga4_events
-- 步骤4：计算归因权重（根据归因模型）
-- 步骤5：写入fact_lead_attribution
INSERT INTO fact_lead_attribution (
lead_id, gclid, session_id,
campaign_id, campaign_name,
keyword_id, keyword_text, search_term,
click_time, cost, conversion_time, conversion_value,
source_type, attribution_model, attribution_weight,
roas, roi_percentage, cpa
)
SELECT
l.lead_id,
l.gclid,
l.session_id,
a.campaign_id,
a.campaign_name,
a.keyword_id,
a.keyword_text,
a.search_term,
a.click_time,
a.cost,
l.lead_time as conversion_time,
COALESCE(c.conversion_value, l.estimated_value, 0) as conversion_value,
CASE
WHEN l.gclid IS NOT NULL THEN 'ads'
WHEN s.source = 'google' AND s.medium = 'organic' THEN 'seo'
WHEN s.source IS NULL THEN 'direct'
ELSE 'referral'
END as source_type,
'last_click' as attribution_model,
1.0 as attribution_weight,
CASE
WHEN a.cost > 0 THEN COALESCE(c.conversion_value, l.estimated_value, 0) / a.cost
ELSE NULL
END as roas,
CASE
WHEN a.cost > 0 THEN (COALESCE(c.conversion_value, l.estimated_value, 0) - a.cost) / a.cost * 100
ELSE NULL
END as roi_percentage,
a.cost as cpa
FROM fact_leads l
LEFT JOIN fact_ads_clicks a ON l.gclid = a.gclid
LEFT JOIN fact_ga4_sessions s ON l.session_id = s.session_id
LEFT JOIN fact_ads_conversions c ON l.gclid = c.gclid AND c.conversion_action = 'lead'
WHERE l.sync_status = 'synced'
AND NOT EXISTS (
SELECT 1 FROM fact_lead_attribution attr
WHERE attr.lead_id = l.lead_id
);
五、关键优化点
5.1 性能优化
- 分区表设计
- 所有事实表按日期分区
- 提高查询性能，便于数据管理
- 索引策略
- 为常用查询字段创建索引
- 复合索引支持多维度查询
- 前缀索引用于长文本字段
- 数据归档
- 定期归档历史数据
- 保留热数据在线上，冷数据归档
5.2 数据质量
- 唯一性约束
- 防止重复数据
- 保证数据完整性
- 数据验证
- 同步状态字段
- 数据完整性评分
- 错误处理
- 保存原始数据
- 支持数据重试和修复
5.3 扩展性
- JSON字段
- 存储灵活的结构化数据
- 便于扩展新字段
- 预留字段
- 支持未来业务需求
- 便于功能扩展
六、实现方案
6.1 技术栈
- 数据库：MySQL 8.0+ / PostgreSQL 13+ / BigQuery
- 后端：PHP 7.4+ (WordPress插件)
- API集成：
- Google Ads API (v22)
- GA4 Data API / BigQuery Export
- 任务调度：WordPress Cron / 外部调度系统
6.2 实现步骤
阶段1：数据库表创建
- 创建所有事实表和维表
- 创建索引和分区
- 验证表结构
阶段2：数据采集
- 实现实时数据采集（询盘提交时）
- 实现Google Ads API数据同步
- 实现GA4 API数据同步
阶段3：ETL归因
- 实现归因逻辑
- 实现多种归因模型
- 实现数据计算和聚合
阶段4：查询和分析
- 实现常用查询接口
- 实现报表生成
- 实现数据可视化
七、查询与分析场景
7.1 广告效果分析
-- 查询每个广告系列的ROI
SELECT
campaign_name,
SUM(cost) as total_cost,
SUM(conversion_value) as total_value,
COUNT(DISTINCT lead_id) as lead_count,
SUM(conversion_value) / SUM(cost) as roas,
(SUM(conversion_value) - SUM(cost)) / SUM(cost) * 100 as roi_percentage,
SUM(cost) / COUNT(DISTINCT lead_id) as cpa
FROM fact_lead_attribution
WHERE source_type = 'ads'
AND conversion_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY campaign_id, campaign_name
ORDER BY roas DESC;
7.2 搜索词效果分析
-- 查询搜索词的询盘效果
SELECT
search_term,
matched_keyword,
COUNT(DISTINCT lead_id) as lead_count,
SUM(cost) as total_cost,
SUM(conversion_value) as total_value,
AVG(conversion_value) as avg_value,
SUM(cost) / COUNT(DISTINCT lead_id) as cpa
FROM fact_lead_attribution
WHERE source_type = 'ads'
AND search_term IS NOT NULL
AND conversion_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY search_term, matched_keyword
HAVING lead_count >= 1
ORDER BY lead_count DESC, total_value DESC;
7.3 用户转化路径分析
-- 查询用户的完整浏览路径
SELECT
attr.lead_id,
attr.session_id,
attr.first_visit_time,
attr.conversion_time,
DATEDIFF(attr.conversion_time, attr.first_visit_time) as days_to_conversion,
attr.pageview_count,
attr.session_duration,
GROUP_CONCAT(e.page_path ORDER BY e.event_time SEPARATOR ' → ') as page_path
FROM fact_lead_attribution attr
LEFT JOIN fact_ga4_events e ON attr.session_id = e.session_id
WHERE attr.lead_id = 'LEAD_12345'
GROUP BY attr.lead_id, attr.session_id;
7.4 SEO vs 广告对比
-- 对比SEO和广告来源的效果
SELECT
source_type,
COUNT(DISTINCT lead_id) as lead_count,
SUM(conversion_value) as total_value,
AVG(conversion_value) as avg_value,
AVG(pageview_count) as avg_pageviews,
AVG(session_duration) as avg_duration,
AVG(days_to_conversion) as avg_days_to_conversion
FROM fact_lead_attribution
WHERE conversion_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY source_type
ORDER BY lead_count DESC;
八、性能优化策略
8.1 查询优化
- 使用分区剪枝
- 查询时指定日期范围
- 只扫描相关分区
- 索引优化
- 复合索引支持多字段查询
- 覆盖索引减少回表
- 物化视图
- 预计算常用统计结果
- 定期刷新
###