营销数据建模
经过清理并连接的数据表
营销数据建模 是 CoddyKit 上的免费 Digital Marketing Academy 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Digital Marketing Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Digital Marketing Academy 课程共包含 4 节课。
为什么要建模
原始连接器表通常很混乱：列名不一致、货币混用、粒度不同，还有各个平台特有的差异。直接查询这些表会产生错误且无法复现的数字。
数据建模是一套将原始行转换为整洁、一致、可用于业务的表的规范。ROAS、转化和收入都应当在这里一次性、正确地定义，从而让每份报表保持一致。
星型模式
占主导地位的分析模型是星型模式：中心是一张记录可度量事件的事实表，周围则是描述这些事件的维度表。事实表保存数字（支出、点击、收入）；维度表保存上下文（广告活动、日期、渠道、客户）。
这种结构对营销人员来说直观，对 BI 工具来说也高效，因为 BI 工具可以将一张事实表与多个维度表连接起来，按任意属性拆分指标。
dim_date
|
dim_channel -- fct_ad_spend -- dim_campaign
|
dim_account
fct_ad_spend (facts): impressions, clicks, cost, conversions
dims: who / what / when context事实表与维度表
事实表通常较长且可加总：每个事件或每个日期与广告活动组合占一行，其中包含可以求和的数值度量。维度表通常较宽且用于描述：每个广告活动或客户占一行，其中包含用于筛选和分组的属性。
判断标准是：如果您会对它执行 SUM，它就是事实；如果您会按它执行 GROUP BY，它就是维度。支出是事实；广告活动名称是维度。
fct_ad_spend dim_campaign
---------------- ----------------
date campaign_id (PK)
campaign_id (FK) campaign_name
cost <-SUM-> channel
clicks <-SUM-> objective
conversions start_date粒度：首要决策
粒度指事实表中的一行所代表的内容。首先声明粒度，是最重要的建模决策。将不同粒度混在一起，例如把每日行与生命周期总计混合，会造成重复计数，并破坏下游的每个指标。
请用易懂的语言说明粒度：每行对应每天的一个广告活动。这样一来，每一列都必须在该粒度下成立，每次加载也都必须遵守这一粒度。
Declared grain: one row per campaign per day
-- enforce uniqueness on the grain
SELECT date, campaign_id, COUNT(*)
FROM fct_ad_spend
GROUP BY 1,2
HAVING COUNT(*) > 1; -- must return 0 rows暂存模型
在构建事实表和维度表之前，请先构建暂存模型：每张源表对应一个暂存模型，将列名重命名为统一标准、转换数据类型，并统一单位（例如将美分转换为美元、将日期统一为 UTC）。一个暂存模型只对应一张原始表，不多不少。
暂存层负责清理数据，并隔离源系统的差异，因此下游数据集市不必知道 Meta 将其称为支出，而 Google 将其称为成本。
-- stg_google_ads__spend
SELECT
date AS spend_date,
campaign_id,
'google' AS channel,
cost_micros / 1000000 AS cost, -- micros -> dollars
clicks,
conversions
FROM raw.google_ads__campaign_stats;合并渠道数据
每个平台的广告数据报表格式各不相同，但经过暂存后，它们会具有统一结构。下一步模型会将它们合并为一张跨渠道支出事实表，作为混合报表的基础。
正是这张统一的表让总 ROAS 成为可能。当每个渠道都统一为相同的列时，一条查询就能同时汇总 Google、Meta 和 TikTok 的支出。
-- fct_ad_spend: union all channels
SELECT * FROM stg_google_ads__spend
UNION ALL
SELECT * FROM stg_meta_ads__spend
UNION ALL
SELECT * FROM stg_tiktok_ads__spend;
-- now: SUM(cost) GROUP BY channel works一致维度
进行跨渠道分析时，维度必须保持一致：所有事实表都以完全相同的方式连接到共享的日期维度和渠道维度。这样，无论数据来源是广告、电子邮件还是网站，按月按渠道统计收入都具有相同含义。
一致维度让您可以在同一张图表中并列展示支出和收入。没有一致维度，连接就会错位，总计也会悄悄出现差异。
Conformed dims shared across facts:
dim_date -> joined by every fact on date
dim_channel -> 'google','meta','email','organic'
dim_campaign -> unified campaign keys
-> spend and revenue line up on the same axesSQL 中的归因
归因是将转化功劳分配给各个接触点。末次点击归因最简单：转化前最后一个营销来源获得全部功劳。首次点击、线性归因和基于位置的归因则会以不同方式分配功劳。
在数据仓库中，您应将归因实现为模型，而不是依赖平台的黑盒。借助 GA4 的事件级数据，您可以按用户使用窗口函数处理接触点，应用任意规则，然后诚实地比较不同模型的结果。
-- last non-direct click per conversion
WITH touches AS (
SELECT user_id, channel, event_time,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY event_time DESC) AS rn
FROM web_touchpoints
WHERE channel <> 'direct'
)
SELECT channel, COUNT(*) FROM touches WHERE rn=1
GROUP BY 1;缓慢变化维度
维度属性会随时间变化：广告活动的预算负责人可能更换，客户的等级可能提升。类型 2 缓慢变化维度不会覆盖旧记录，而是通过添加带有效日期的新行来保留历史。
这对于准确的时间点报表非常重要。要知道客户转化时属于哪个细分群体，您需要使用当时有效的维度版本，而不是今天的版本。
dim_customer (SCD Type 2)
cust_id tier valid_from valid_to is_current
101 free 2026-01-01 2026-04-01 false
101 pro 2026-04-01 9999-12-31 true
-- join on event_date BETWEEN valid_from AND valid_to测试与文档
模型也是代码，因此要对其进行测试。dbt 等工具可以让您断言键是唯一且不为空、渠道值属于可接受的集合，并且表之间的关系成立。
测试会在问题到达仪表板之前发现模式漂移和错误连接。配合自动生成的文档和数据血缘，它们可以让模型值得信任、易于交接，而不再是脆弱的黑盒。
# dbt schema test
models:
- name: fct_ad_spend
columns:
- name: campaign_id
tests: [not_null]
- name: channel
tests:
- accepted_values:
values: ['google','meta','tiktok']数据集市：最终层
最上层是数据集市：面向特定受众构建的、可直接用于业务的表，例如 marketing_performance 数据集市已经将支出与收入连接起来，并计算出每个渠道每天的 ROAS。
BI 工具只从数据集市读取数据。在这里预先连接并预先汇总后，仪表板会保持快速且低成本，每位分析师也都会沿用同一套正确的定义。
-- marts.marketing_performance (1 row / day / channel)
SELECT s.spend_date, s.channel,
SUM(s.cost) AS spend,
SUM(r.revenue) AS revenue,
SAFE_DIVIDE(SUM(r.revenue), SUM(s.cost)) AS roas
FROM fct_ad_spend s
LEFT JOIN fct_revenue r USING (spend_date, channel)
GROUP BY 1,2;快速检查
您正在构建一张广告效果事实表，并且必须避免重复计数。在编写任何列之前，最重要的事情是什么？
回顾
建模通过分层将杂乱的原始表转换为可信、可直接用于业务的数据：暂存层清洗并统一各个来源，事实表和一致性维度构成星型架构，数据集市则预先连接所有内容，供 BI 使用。
请先声明粒度，为混合指标合并各个渠道，在结构化查询语言中实现归因和 SCD 类型 2 历史记录，并测试每个模型，让错误数字立即暴露，而不是一路进入仪表板。
常见问题解答
「营销数据建模」课时是免费的吗？
是的 — 「营销数据建模」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Digital Marketing Academy 课程的其余内容，请升级到 CoddyKit PRO。 Digital Marketing Academy 课程共包含 4 节课。
「营销数据建模」这节课中我会学到什么？
经过清理并连接的数据表 你通过在浏览器中直接运行的动手代码来练习 Digital Marketing Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Digital Marketing Academy 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Digital Marketing Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。
「营销数据建模」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Digital Marketing Academy 课中编写并运行代码吗？
能。每节 Digital Marketing Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。