53 KPIs defined.
14 blocked.
3 rules to write.

-- ── Attribution allowlist — agreed Oct 2026, CMO sign-off ──────────
ALTER TABLE aon_mdl_test.marketing.lead_attributes_it
  ALTER COLUMN lead_source
  COMMENT 'Agreed allowlist for marketing-sourced attribution (Oct 2026):
Event, Content Syndication, Paid Social, Webinar, Email Campaign.
Use Eloqua C_Lead_Source___Original1 (first-touch), NOT this field,
when computing marketing_sourced_pipeline. Any value outside the
allowlist is attributed to Sales / Other.';

-- ── MQL derivation rule — no is_mql flag exists in MDL ─────────────
ALTER TABLE aon_mdl_test.marketing.lead_attributes_it
  ALTER COLUMN lead_status_code
  COMMENT 'MQL derivation rule (agreed Oct 2026):
A lead is MQL when lead_status_code = QUALIFIED AND lead_stage = MQL.
No pre-built is_mql flag exists. Always use both fields together.
SQL also includes leads at Sales_Accepted status (not just Converted).';

-- ── Eloqua first-touch source — authoritative for attribution ───────
ALTER TABLE aon_mdl_test.marketing.eloqua_contacts
  ALTER COLUMN C_Lead_Source___Original1
  COMMENT 'First-touch lead source captured by Eloqua at lead creation.
This is the authoritative field for marketing attribution — NOT
lead_source from Aon Connect (which reflects sales-rep updates).
Allowlist: Event · Content Syndication · Paid Social · Webinar · Email Campaign';

name: mv_marketing_pipeline
catalog: aon_mdl_test
schema: marketing
comment: >
  Marketing pipeline and MQL metrics for AON MDL.
  Encodes the agreed attribution allowlist (Oct 2026) and MQL
  derivation rule so every BI tool returns identical numbers.

source:
  table: marketing.lead_attributes_it
  alias: l

joins:
  - table: marketing.opportunity_attributes_iit
    alias: o
    on: "l.lead_id = o.opty_lead_id"
    type: LEFT
  - table: marketing.eloqua_contacts
    alias: ec
    on: "l.lead_id = ec.lead_id"
    type: LEFT

measures:
  - name: marketing_sourced_pipeline
    expr: >
      SUM(CASE WHEN ec.C_Lead_Source___Original1 IN
        ('Event','Content Syndication','Paid Social','Webinar','Email Campaign')
      THEN o.opty_amount_usd ELSE 0 END)
    synonyms: ["marketing pipeline","mktg pipeline","marketing sourced revenue"]

  - name: mql_volume
    expr: >
      COUNT(DISTINCT CASE
        WHEN l.lead_status_code='QUALIFIED' AND l.lead_stage='MQL'
        THEN l.lead_id END)
    synonyms: ["MQLs","qualified leads","marketing qualified leads"]

  - name: mql_to_sql_conversion_rate
    expr: >
      ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN l.lead_stage='SQL' THEN l.lead_id END) /
        NULLIF(COUNT(DISTINCT CASE
          WHEN l.lead_status_code='QUALIFIED' AND l.lead_stage='MQL'
          THEN l.lead_id END), 0), 1)
    synonyms: ["conversion rate","MQL conversion","lead conversion"]

name: mv_event_attribution
catalog: aon_mdl_test
schema: marketing
comment: >
  Event-to-pipeline attribution. Encodes the agreed 90-day window
  (Oct 2026, CMO sign-off required to change) and the complete
  five-hop Cvent join chain as a single governed measure.

source:
  table: marketing.sys_cvent_event
  alias: ev

joins:
  - table: marketing.cmpgn_campaigns_it
    alias: c
    on: "ev.Event_Code = c.cvent_event_code"
    type: LEFT
  - table: marketing.campaign_member_it
    alias: cm
    on: "c.campaign_id = cm.campaign_id"
    type: LEFT
  - table: marketing.lead_attributes_it
    alias: l
    on: "cm.lead_id = l.lead_id"
    type: LEFT
  - table: marketing.opportunity_attributes_iit
    alias: o
    on: "l.lead_id = o.opty_lead_id"
    type: LEFT

measures:
  - name: post_event_pipeline_90d
    expr: >
      SUM(CASE WHEN o.opty_created_date
        BETWEEN ev.Event_Start_Date
            AND DATE_ADD(ev.Event_Start_Date, 90)
        THEN o.opty_amount_usd ELSE 0 END)
    synonyms: ["post-event pipeline","event pipeline","pipeline from event"]
    comment: "90-day attribution window. CMO sign-off required to change."

  - name: post_event_pipeline_30d
    expr: >
      SUM(CASE WHEN o.opty_created_date
        BETWEEN ev.Event_Start_Date
            AND DATE_ADD(ev.Event_Start_Date, 30)
        THEN o.opty_amount_usd ELSE 0 END)
    synonyms: ["30-day event pipeline"]

from databricks.sdk import WorkspaceClient
from databricks.sdk.service.dashboards import (
    GenieSpace, GenieSpaceDataset,
    GenieSpaceInstruction, GenieSpaceTrustedQuery
)

w = WorkspaceClient()

space = w.genie.create(GenieSpace(
    title="AON Marketing Data Lake — CE",
    warehouse_id="<your-sql-warehouse-id>",
))
space_id = space.space_id

for mv in ["mv_marketing_pipeline", "mv_event_attribution"]:
    w.genie.create_space_dataset(space_id=space_id,
        dataset=GenieSpaceDataset(display_name=mv,
            table=f"aon_mdl_test.marketing.{mv}"))

INSTRUCTIONS = [
    "ALWAYS use mv_marketing_pipeline for pipeline, revenue, or MQL questions.",
    "ALWAYS use mv_event_attribution for event or forum questions.",
    "For marketing-sourced pipeline use the marketing_sourced_pipeline measure. "
    "DO NOT filter on lead_attributes_it.lead_source — that is the Sales CRM field.",
    "For MQL counts use the mql_volume measure. DO NOT derive manually.",
    "For post-event pipeline use post_event_pipeline_90d. The 90-day window "
    "is agreed standard. CMO sign-off required to change it.",
]
for text in INSTRUCTIONS:
    w.genie.create_space_instruction(space_id=space_id,
        instruction=GenieSpaceInstruction(instruction=text))