数据模型描述草案

以下为“线索到成交流程”（Lead-to-Close）的标准分析数据模型设计，面向销售与市场一体化漏斗分析、转化与速度（velocity）分析、归因与预测。

一、建模目标与关键问题

• 漏斗：各阶段进出量、转化率、阶段停留时长、漏损点。
• 归因：市场活动/渠道对线索、商机与成交的贡献。
• 速度：从线索创建到MQL/SAL/SQL/商机/成交的周期。
• 管理：管道健康（覆盖率、老化、再打开）、赢单率、客单价、ARR/ACV。
• 预测：按时点快照进行趋势与预测准确性跟踪。

二、总体架构（推荐三层）

• 采集层（ODS）：CRM、营销自动化、网站行为、广告平台、客服系统。
• 统一实体层（CDM）：统一身份与主数据（Lead/Contact/Account/Opportunity/Product/Campaign）。
• 分析层（星型模型）：事件事实、快照事实与维度表，支持核算口径一致。

三、核心星型模型 A. 事实表（Facts）

1. f_lead_stage_history（线索阶段变更事实）

• 粒度：每条线索每一次阶段/状态变更一行（含创建、资格、并回退/无效）。
• 主键与外键：lead_sk, stage_sk, date_sk, owner_sk, account_sk(若已匹配), campaign_sk_first_touch, campaign_sk_last_touch, channel_sk。
• 度量：enter_flag, exit_flag, stage_duration_days, response_time_hours(首次响应), touch_count_to_stage, is_mql/is_sal。
• 用途：计算阶段进入/退出、转化率、阶段停留、响应SLA。

2. f_touchpoint（触点/活动事实）

• 粒度：每一次触点（邮件、电话、会议、表单、广告点击、网站关键事件）一行。
• 外键：contact_sk或lead_sk或anonymous_identity_sk, campaign_sk, channel_sk, activity_type_sk, date_sk, owner_sk。
• 度量：touch_cost, engagement_score, reply_flag, meeting_minutes。
• 用途：构建多触点路径、归因、触达效率、销售活动产出比。

3. f_opportunity_stage_history（商机阶段变更事实）

• 粒度：每个商机每一次阶段/概率变更一行。
• 外键：opportunity_sk, stage_sk, date_sk, owner_sk, account_sk, segment_sk。
• 度量：pipeline_amount, probability, stage_duration_days, re_open_flag。
• 用途：管道流动、阶段老化、再打开、预测准确性。

4. f_pipeline_snapshot（管道日/周快照事实）

• 粒度：每个“快照日×商机”一行（或按周/月快照）。
• 外键：opportunity_sk, date_sk, owner_sk, stage_sk, account_sk。
• 度量：open_pipeline_amount, weighted_pipeline_amount(probability加权), next_step_due_days。
• 用途：时点口径的覆盖率、趋势、预测对比（快照能避免仅凭历史事件回放的偏差）。

5. f_revenue_bookings（成交/签约事实）

• 粒度：每笔成交（Closed-Won）或每行项目（如产品明细）。
• 外键：opportunity_sk, product_sk, date_sk, account_sk, owner_sk。
• 度量：booking_amount, arr/acv, quantity, discount, term_months。
• 用途：赢单率、ASP/ACV、产品结构、区域/团队产出。若涉及开票与回款，再增f_invoice与f_cash_collection。

6. f_attribution_contribution（归因分摊事实，可选）

• 粒度：每个“目标事件（如MQL、商机创建、成交）×触点路径节点”一行。
• 外键：goal_event_sk, campaign_sk, channel_sk, date_sk, attribution_model_sk。
• 度量：credit_share（分摊权重）, contributed_amount。
• 用途：多触点归因（首次触点、末次触点、线性、时间衰减、数据驱动）。

B. 维度表（Dimensions）

• d_lead（线索维，SCD2）：来源、行业猜测、公司域名、线索状态、所有者；记录有效期与current_flag。
• d_contact（联系人维，SCD2）：职务、部门、资历、邮箱域名、是否关键人。
• d_account（账户维，SCD2）：行业、规模、地域、分层（Enterprise/Commercial/SMB）、客户阶段（Prospect/Customer）、父子层级。
• d_opportunity（商机维，SCD2）：商机类型（新客/续约/扩展）、货币、预测类别、是否伙伴渠道、创建来源。
• d_stage（阶段维）：线索阶段（新建/已资格/MQL/SAL/SQL/无效等）、商机阶段（Qualification/Proposal/Negotiation/Closed Won/Lost等），统一映射表支持跨系统口径一致。
• d_campaign（活动维）：活动类型（广告/网络研讨会/展会/内容下载）、预算、开始/结束日期。
• d_channel（渠道维）：付费搜索、自然搜索、直访、社媒、邮件、活动等。
• d_activity_type（活动类型维）：电话、邮件、会议、表单提交、网站关键事件。
• d_sales_user（销售人员维）：角色、团队、区域，配合d_team/d_territory形成组织层级。
• d_product（产品维）：产品线、SKU、计费度量、包装/版本。
• d_geo（地域维）：国家/区域/时区。
• d_reason_code（原因维）：无效线索原因、丢单原因（价格、功能、时机、竞争对手）。
• d_date, d_time, d_fiscal（日期/时间/财年维）。

C. 桥接与映射表（Bridges）

• b_lead_to_opportunity（线索-商机映射）：支持线索转化为联系人/账户/商机，并保留转化时间与规则来源；解决一线索对多商机或再资格化。
• b_opportunity_contact_role（商机-联系人角色）：多对多及角色（决策者/影响者/使用者）。
• b_opportunity_product（商机-产品行）：商机与产品/套餐的多对多。
• b_lead_campaign_touch（线索/联系人-活动触点）：支持多触点归因与触点回溯。
• b_account_hierarchy（账户层级）：母子公司、全球-区域-本地。
• b_identity_map（身份映射）：邮箱、域名、设备ID、Cookie与Lead/Contact统一身份（解决匿名到实名衔接）。

四、建模关键点与口径控制

• 粒度清晰：事件型（stage_history、touchpoint）与时点型（pipeline_snapshot）并存；避免用事件回放替代时点库存。
• 主键策略：维度采用代理键（surrogate key），保留来源系统自然键与来源标识供血缘追溯。
• SCD管理：账户、联系人、商机等业务属性采用SCD2（有效起止日期与current_flag）；显示名等低风险属性可SCD1。
• 统一阶段映射：建立配置表将不同系统及团队命名的阶段映射为标准阶段，驱动转化口径一致。
• 身份解析与去重：基于邮箱域名、公司名标准化、网站域名、第三方清洗进行Lead-to-Account匹配；记录合并与拆分历史。
• 货币与汇率：在事实表中保留本币与报告币金额；d_date绑定当期汇率快照。
• 时区与财年：日期维同时支持自然日与财务日历；管道快照建议以“业务日终”口径。
• 合规与安全：触点层屏蔽/哈希PII；行级权限按区域/团队/数据域控制。

五、核心指标与计算示例（由模型直接支撑）

• 转化率：Lead->MQL、MQL->SAL、SAL->SQL、SQL->Won（分活动/渠道/地区/细分）。
• 速度：创建->MQL、MQL->商机、商机->成交的中位数与P90；各阶段停留时长。
• 赢单与产出：Win Rate、ASP/ACV、ARR、产品结构占比、漏损原因分布。
• 触点与归因：每单触点数、首末次触点贡献、线性/时间衰减分摊的Pipeline与Revenue。
• 管道健康：覆盖率（Pipeline/Quota）、老化分布、预测偏差（快照对比实际）。
• 漏斗质量：MQL有效率、销售首次响应时间、再打开率、重复线索率。

六、实施建议

• 抽取与变更捕获：对CRM与营销平台采用CDC增量；事件表Append-only，维度SCD2。
• 数据质量规则：空域名补全、邮箱校验、行业/地域标准化，线索去重、账户合并日志处理。
• 性能与成本：事实表按date_sk分区；大表列式存储；快照保留策略按业务需要分层归档。
• 可扩展性：新增阶段或渠道仅需更新维表与映射，不影响事实历史口径。

该数据模型能稳定支持从“线索-资格-商机-成交”的全链路分析，既覆盖事件明细，又具备时点快照，确保转化、速度、归因与预测等关键管理问题可被准确回答并可追溯。

以下为“指标口径”和“术语目录”的数据模型设计，面向企业级元数据管理与指标治理，重点覆盖统一口径、可追溯、可版本化、可审核与跨系统对齐。

一、建模目标与原则

• 统一语义：术语与指标一一清晰对应，避免歧义。
• 可版本化：定义变更可回溯（生效/失效时间），支持并行管理历史版本。
• 可追溯：从业务定义到物理字段与加工逻辑的全链路血缘。
• 可治理：角色、审批、状态、影响分析闭环。
• 可落地：字段级可实现，适配SQL/引擎的表达式与参数化。

二、总体结构（两个域，若干共享维度）

• 术语目录（Glossary）：业务语义的权威源。
• 指标口径（Metric）：可计算、可落地的度量定义。
• 共享维度：域/主题域、组织与人员、系统/数据源、单位/度量单位、标签、策略与权限等。

三、术语目录数据模型（核心实体）

1. Term（术语）

• 关键字段：term_id（主键）、term_code（业务编码）、term_name、definition（权威定义）、scope说明、examples、status（draft/under_review/approved/deprecated）、domain_id、subject_area_id、steward_user_id、owner_org_id、tags、created_at/updated_at
• 说明：term_code作为跨版本稳定标识；status用于治理流程。

2. Term_Version（术语版本）

• term_version_id、term_id、version_no、valid_from、valid_to、change_note、breaking_change_flag
• 说明：SCD2/双时间线均可；建议生效时间有效管理下游影响。

3. Term_Alias（别名/缩写/同义）

• alias_id、term_id、alias、alias_type（synonym/abbreviation/translation）、language

4. Term_Relation（术语关系）

• relation_id、from_term_id、to_term_id、relation_type（broader/narrower/related/derived_from）
• 说明：支持层级与关联检索。

5. Term_Physical_Mapping（术语到物理字段映射）

• map_id、term_id、system_id、catalog/schema/table/column、transform_note、coverage（百分比/适用范围）、data_owner
• 说明：实现术语到数据资产的落地映射与覆盖率说明。

6. Term_Policy_Link / Term_Reference / Term_Translation（可选）

• 关联合规策略、参考来源与多语言。

四、指标口径数据模型（核心实体）

1. Metric（指标）

• metric_id（主键）、metric_code（业务编码）、metric_name、metric_type（atomic/derived/ratio/kpi）、business_purpose、grain（度量粒度说明）、unit_id、aggregation（sum/avg/min/max/distinct_count/last_value等）、periodicity（day/week/month/rolling）、missing_value_rule、dedup_rule、status、domain_id、subject_area_id、steward_user_id、owner_org_id、tags、created_at/updated_at
• 说明：粒度、聚合、周期与缺失/去重规则明确写入，有利一致性与可计算性。

2. Metric_Version（指标版本）

• metric_version_id、metric_id、version_no、valid_from、valid_to、change_note、compatibility（backward_compatible/ incompatible）、kpi_flag
• 说明：变更口径管理的基座；与生产调度绑定生效时间。

3. Metric_Definition（口径要素拆解）

• def_id、metric_version_id
• fields:
  • inclusion_criteria / exclusion_criteria（包含/排除规则）
  • time_window（如T-7/T-30/自然周月）
  • snapshot_vs_flow（快照/流水）
  • reference_timepoint（统计口径的时间戳定义如下单时间/支付时间）
  • outlier_rule / null_handling / rounding_rule
  • compliance_note（合规与隐私处理）
• 说明：将“口径文字”结构化，避免歧义。

4. Metric_Expression（计算表达式）

• expr_id、metric_version_id、expr_text（DSL/SQL/公式）、expr_lang（sql/dsl/json-ast）、engine_hint（spark/federated/sqlserver等）、test_case_ref
• 说明：表达式可存AST或DSL，便于解析与跨引擎生成；同一语义多实现版本可分多条记录（engine_hint区分）。

5. Metric_Component（组成明细）

• component_id、metric_version_id、component_type（source_metric/base_column/constant/parameter）、ref_id（引用的metric_version_id或column_id）、operator（+ - * / window等）、sequence
• 说明：支持指标分解与重用，利于血缘与影响分析。

6. Metric_Dimension_Assoc（适配维度）

• id、metric_version_id、dimension_term_id（引用Term）、is_mandatory、is_time_dimension、allowed_values_ref
• 说明：定义“该指标允许/必须按哪些维度切分”，解决跨报表维度可比性。

7. Metric_Filter_Parameter（参数化过滤）

• id、metric_version_id、param_name、datatype、allowed_values_ref、default_value、is_required
• 说明：实现同一指标在不同场景下的复用（如地区=全国/华东）。

8. Metric_Threshold_Target（阈值与目标）

• id、metric_version_id、target_type（target/warn/critical）、value/expr_ref、comparison（>=/<=/between）、direction（higher_is_better/lower_is_better）、period_scope（月目标/季目标）、owner_org_id

9. Metric_Lineage（血缘）

• lineage_id、metric_version_id、source_type（metric/column/table/job）、source_id、transform_step、implementation_ref（sql_id/notebook_id/pipeline_id）、confidence_level
• 说明：清楚表述来源与实现，支持端到端追溯。

10. Metric_Term_Link（指标与术语的语义绑定）

• id、metric_version_id、term_id、role（subject/concept/definition_reference）
• 说明：明确引用的业务术语，确保语义一致。

11. Metric_Governance（治理信息）

• id、metric_version_id、approval_state（draft/under_review/approved/retired）、approver_user_id、approval_at、risk_level、security_classification
• 说明：审批闭环与分级分类。

五、共享维度与支撑实体

• Domain（业务域）、Subject_Area（主题域）
• Unit_Of_Measure（单位：id、code、name、conversion_rule）
• System / Data_Source（系统信息）
• User / Org（人员与组织）
• Policy / Tag（策略与标签）
• Data_Quality_Rule（质量规则：适配术语与指标版本，含检查类型、阈值、采样、结果记录）

六、关键关系与基数

• 一个Domain/Subject_Area下有多Term与多Metric（一对多）。
• 一个Term可有多个版本Term_Version（一对多）；Term间多对多关系通过Term_Relation。
• 一个Metric有多个版本Metric_Version（一对多）。
• Metric_Version与Term多对多（Metric_Term_Link），绑定语义。
• Metric_Version与维度术语多对多（Metric_Dimension_Assoc），控制可切分性。
• Metric_Version与物理字段/上游指标多对多（Metric_Lineage、Metric_Component），实现血缘与复用。
• 术语与物理字段多对多（Term_Physical_Mapping），实现语义到数据资产的落地。

七、版本与时间管理建议

• 基本做法：主实体稳定ID + 版本表（推荐）。在版本表使用valid_from/valid_to管理生效区间；生产系统按“查询时间点”选择有效版本。
• 可选：双时间线（业务生效时间valid_from/to + 记录写入时间recorded_from/to）支持回溯与审计。
• 变更类型标注：breaking_change_flag/compatibility，指导回填与报表切换策略。

八、治理与流程

• 状态流转：draft → under_review → approved → deprecated/retired；所有变更保留change_note与审批记录。
• 影响分析：通过Metric_Component与Metric_Lineage自动计算影响范围（指标、报表、任务、字段）。
• 标准化：强制填写steward与owner；缺失/去重/时间口径为必填；聚合规则、粒度、单位必填。
• 一致性校验：将Metric_Dimension_Assoc与Term_Physical_Mapping结合，验证维度与数据源可用性。

九、安全与权限

• 行级：按域/主题域/组织授予查看及变更权限。
• 列级：敏感字段脱敏，术语/指标可设置security_classification。
• 审计：记录查看、修改、审批、发布操作日志。

十、实现与落地要点

• 表达式引擎：统一DSL与AST存储，提供到SQL/引擎的代码生成器；配测试用例与样本数据校验。
• API与服务：元数据服务暴露查询接口（按域/主题/状态/时间点）；血缘、影响分析可视化。
• 数据质量：对关键指标建立DQ规则与监控结果表；阈值偏离触发告警与治理工单。
• 多语言与多地区：Term_Translation、单位换算、时区敏感的时间口径字段。

最小必备清单（可先行落地）

• 术语域：Term、Term_Version、Term_Physical_Mapping、Term_Relation
• 指标域：Metric、Metric_Version、Metric_Definition、Metric_Expression、Metric_Dimension_Assoc、Metric_Lineage、Metric_Term_Link
• 治理域：Domain、Subject_Area、User/Org、Unit_Of_Measure、Metric_Governance

通过以上模型，企业可实现指标口径与术语的统一管理、跨系统一致的语义与计算、变更的可追溯与审批落地，并为报表与自助分析提供稳定、可控的元数据基础。