Dialogue with Data

The Problem

Three definition gaps that block every marketing KPI

AON's MDL working document records these gaps explicitly — they are confirmed issues in the data, not hypothetical risks. Context Engineering closes them by encoding agreed definitions once, in Unity Catalog, where every downstream tool reads the same answer.

⚔️

The Attribution War

marketing_sourced_pipeline has no agreed rule. Marketing filters C_Lead_Source___Original1 from Eloqua. Sales pulls lead_source from Aon Connect. Different fields. Different filters. Different numbers — every board meeting.

CONFIRMED GAP · MDL WORKING DOC ROW 10

🔎

The MQL Mirage

There is no is_mql flag in the MDL schema. MQL must be derived from lead_status_code and lead_stage. Every analyst re-derives it differently. Campaign conversion rates are incomparable across teams.

CONFIRMED GAP · MDL WORKING DOC ROW 16

🔗

The Invisible Event

Cvent event data flows into MDL via an Eloqua CDO. The five-hop join to pipeline opportunities exists but is undocumented. No attribution window is defined. The event team cannot answer: "Did our London Forum generate pipeline?"

CONFIRMED GAP · MDL WORKING DOC ROW 19

The Solution

Context Engineering is a methodology, not a layer

CE is the practice of encoding business intent — what a metric means, what qualifies, what's excluded — directly into the data platform, as three permanent artifacts. Every downstream consumer reads the same definition. The attribution war ends because there is only one place the rule lives.

ARTIFACT 01

UC Column COMMENT

An ALTER COLUMN … COMMENT on lead_attributes_it.lead_source encodes the agreed allowlist and prohibits ad-hoc filters. Genie reads this before generating any SQL.

ARTIFACT 02

Unity Catalog Metric View

A YAML definition in Unity Catalog with a measures block. The expr contains the exact SQL. synonyms ensure "marketing pipeline" and "sourced pipeline" resolve to the same governed measure.

ARTIFACT 03

Genie Knowledge Store

A serialized_space that tells Genie: MQL is defined by status code, not a flag. Marketing-sourced requires the allowlist. Event attribution uses the 90-day window. Trusted SQL assets are pre-verified.

One source, two outputs: The same Metric View definition that governs Genie conversations also feeds Power BI Copilot via the Databricks Semantic Bridge. The attribution rule is written once and versioned in Unity Catalog. There is no separate BI semantic model to maintain.

CE Artifacts

The three permanent artifacts that end the attribution war

Written once. Version-controlled in Unity Catalog. Read by every downstream tool — Genie, Power BI Copilot, SQL notebooks. Changing the definition means editing one expr.

📝

Unity Catalog Column COMMENT

Encodes the marketing-sourced allowlist at the field level

-- Encode the attribution rule where the data lives
ALTER TABLE marketing.mdl.lead_attributes_it
ALTER COLUMN lead_source COMMENT
  'Lead origin as recorded in Aon Connect (Oracle Sales Cloud).

MARKETING-SOURCED ALLOWLIST (agreed Oct 2026):
  Event, Content Syndication, Paid Social,
  Webinar, Email Campaign, Referral

DO NOT filter on Eloqua C_Lead_Source___Original1
without cross-referencing this field — the two
systems stamp different values. Use the Metric View
marketing_sourced_pipeline for governed reports.

Owner: Marketing Ops · Reviewed: 2026-10-15';

📐

Marketing Pipeline Metric View

Governed marketing_sourced_pipeline and mql_volume measures

# marketing.mdl.mv_marketing_pipeline
version: 1.1
source: marketing.mdl.lead_attributes_it
comment: "MDL Marketing Pipeline — governed layer"
filter: lead_status_code != 'DELETED'

joins:
  - name: opty
    source: marketing.mdl.opportunity_attributes_iit
    on: source.lead_id = opty.opty_lead_id

measures:
  - name: Marketing Sourced Pipeline
    expr: SUM(CASE WHEN lead_source IN (
        'Event','Content Syndication',
        'Paid Social','Webinar',
        'Email Campaign')
      THEN opty.opty_amount_usd END)
    synonyms: ["marketing pipeline",
               "sourced pipeline"]

  - name: MQL Volume
    expr: COUNT(CASE WHEN
        lead_status_code = 'QUALIFIED'
        AND lead_stage = 'MQL'
      THEN lead_id END)
        # note: lead_comverted_date (one 'n')
    synonyms: ["qualified leads","MQLs"]

📅

Event Attribution Metric View

Governs the Cvent → pipeline 90-day attribution window

# marketing.mdl.mv_event_attribution
version: 1.1
source: marketing.mdl.opportunity_attributes_iit

joins:
  - name: contact
    source: marketing.mdl.contact_it
    on: source.opty_primary_contact_id
       = contact.con_party_id
  - name: event
    source: marketing.mdl.cmpgn_campaigns_it
    on: contact.campaign_cvent_id
       = event.campaign_cvent_id

measures:
  - name: Post-Event Pipeline 90d
    expr: SUM(CASE WHEN
        DATEDIFF(opty_record_creation_date,
                 event.event_start_date)
        BETWEEN 0 AND 90
      THEN opty_amount_usd END)
    comment: "90d window — CMO sign-off to change"
    synonyms: ["event pipeline",
               "conference pipeline",
               "post-event attribution"]

🧠

Genie Knowledge Store

serialized_space — AON-specific business rules for Genie

/* Genie Space: AON Marketing Intelligence
   Space: aon-marketing-mdl-prod              */

-- MQL DEFINITION
INSTRUCTION:
  "MQL = lead_status_code='QUALIFIED'
   AND lead_stage='MQL'.
   NO standalone mql_flag in MDL.
   Conversion field: lead_comverted_date
   (note: one 'n' — actual schema spelling)."

-- ATTRIBUTION RULE
INSTRUCTION:
  "Always use Metric View for marketing
   pipeline — never raw lead_source filter.
   Eloqua and Aon Connect stamp different
   lead_source values."

-- TRUSTED ASSET: Pipeline QTD
TRUSTED_ASSET marketing_pipeline_qtd:
  SELECT lead_source,
    COUNT(lead_id)       AS lead_count,
    SUM(opty_amount_usd) AS pipeline_usd
  FROM MEASURE(marketing_sourced_pipeline)
  WHERE opty_record_creation_date
    >= DATE_TRUNC('quarter',CURRENT_DATE())
  GROUP BY 1 ORDER BY 3 DESC;

⌨

Test Environment Setup

Code examples are hidden · click to expand — configure a Databricks environment to run these demos live

▼

Prerequisites: Databricks Runtime 17.2+, Unity Catalog enabled, DBSQL warehouse. All table and field names match the AON MDL working schema exactly — copy-paste safe. Run tabs 1→2→3→4→5 in order; tab 5 validates each demo scenario and surfaces the attribution gap directly in SQL.

Creates the aon_mdl_test catalog, marketing schema, six core tables, and the UC COMMENTs that encode the agreed business definitions.

catalog_and_schema.sql

CREATE CATALOG IF NOT EXISTS aon_mdl_test
  COMMENT 'AON Marketing Data Lake — test environment for CE demo';

USE CATALOG aon_mdl_test;

CREATE SCHEMA IF NOT EXISTS marketing
  COMMENT 'Marketing analytics schema — Eloqua, Aon Connect, Cvent, Unity CDP';

tables_ddl.sql — six tables with UC COMMENTs

-- ── lead_attributes_it ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS marketing.lead_attributes_it (
  lead_id            STRING    NOT NULL,
  lead_status_code   STRING,   -- 'QUALIFIED' signals MQL stage
  lead_stage         STRING,   -- 'MQL' | 'SAL' | 'SQL' | 'Recycled'
  lead_source        STRING,   -- from Aon Connect (sales view)
  lead_comverted_date DATE,    -- NOTE: one 'n' — actual MDL spelling
  created_date       DATE,
  owner_id           STRING,
  campaign_id        STRING,
  campaign_cvent_id  STRING   -- FK to SYS_Cvent_Event.Event_Code
)
COMMENT 'Lead records sourced from Aon Connect (Oracle Sales Cloud). lead_source reflects the Sales CRM field; use C_Lead_Source___Original1 from eloqua_contacts for first-touch attribution.';

ALTER TABLE marketing.lead_attributes_it
  ALTER COLUMN lead_source
  COMMENT 'Agreed allowlist for marketing-sourced attribution (Oct 2026, CMO sign-off): Event, Content Syndication, Paid Social, Webinar, Email Campaign. Any value outside this list is attributed to Sales.';

ALTER TABLE 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 in MDL — always derive using both fields.';

-- ── opportunity_attributes_iit ──────────────────────────────
CREATE TABLE IF NOT EXISTS marketing.opportunity_attributes_iit (
  opty_id            STRING    NOT NULL,
  opty_lead_id       STRING,   -- FK to lead_attributes_it.lead_id
  opty_amount_usd    DECIMAL(18,2),
  opty_stage         STRING,
  opty_close_date    DATE,
  opty_created_date  DATE,
  fiscal_quarter     STRING
)
COMMENT 'Opportunity records from Aon Connect. Join to leads via: lead_id = opty_lead_id. Note table suffix is double-i (iit), not single.';

-- ── cmpgn_campaigns_it ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS marketing.cmpgn_campaigns_it (
  campaign_id        STRING    NOT NULL,
  campaign_name      STRING,
  campaign_type      STRING,   -- 'Event' | 'Email' | 'Webinar' | 'Paid Social'
  campaign_region    STRING,
  event_start_date   DATE,
  cvent_event_code   STRING   -- links to lead_attributes_it.campaign_cvent_id
)
COMMENT 'Campaign master sourced from Eloqua. Event attribution window: 90 days from event_start_date (agreed Oct 2026). Use cvent_event_code to join to lead records via campaign_cvent_id.';

-- ── eloqua_contacts (ELQ_Contacts) ──────────────────────────
CREATE TABLE IF NOT EXISTS marketing.eloqua_contacts (
  contact_id                  STRING  NOT NULL,
  lead_id                     STRING,
  C_Lead_Source___Original1   STRING,  -- first-touch source from Eloqua
  email_address               STRING,
  company                     STRING,
  country                     STRING,
  region                      STRING
)
COMMENT 'Eloqua contact master. C_Lead_Source___Original1 is the first-touch lead source as captured by Eloqua marketing automation — this is the authoritative field for marketing attribution, NOT lead_source from Aon Connect.';

ALTER TABLE marketing.eloqua_contacts
  ALTER COLUMN C_Lead_Source___Original1
  COMMENT 'First-touch lead source from Eloqua (Aon Connect stores a different lead_source field that reflects sales updates). Use this field — not lead_attributes_it.lead_source — when computing marketing_sourced_pipeline.';

-- ── campaign_member_it ───────────────────────────────────────
CREATE TABLE IF NOT EXISTS marketing.campaign_member_it (
  member_id       STRING  NOT NULL,
  campaign_id     STRING,
  lead_id         STRING,
  status          STRING,
  responded_date  DATE
)
COMMENT 'Campaign membership bridge between campaigns and leads. Use to count leads per campaign and to trace event attendance before pipeline join.';

-- ── SYS_Cvent_Event (Eloqua CDO) ─────────────────────────────
CREATE TABLE IF NOT EXISTS marketing.sys_cvent_event (
  Event_Code       STRING  NOT NULL,
  Event_Name       STRING,
  Event_Start_Date DATE,
  Registrant_Email STRING,
  Attendance_Status STRING
)
COMMENT 'Cvent event data ingested via Eloqua Custom Data Object. Join to campaigns via: cmpgn_campaigns_it.cvent_event_code = sys_cvent_event.Event_Code, then to leads via campaign_member_it.';

Synthetic data reproducing all three demo scenarios exactly: Q1 $34.8M attribution (Demo 1), 368 EMEA MQLs at 32.3% conversion (Demo 2), $12.5M London Insurance Forum pipeline (Demo 3).

seed_leads.sql

INSERT INTO marketing.lead_attributes_it VALUES
-- Marketing-sourced leads (Demo 1 — allowlist match)
('L001', 'QUALIFIED', 'MQL',  'Event',               '2026-08-12', '2026-07-01', 'U021', 'C100', 'CVENT-LIF-2026'),
('L002', 'QUALIFIED', 'MQL',  'Content Syndication', '2026-08-15', '2026-07-05', 'U022', 'C101', NULL),
('L003', 'QUALIFIED', 'MQL',  'Paid Social',         '2026-08-20', '2026-07-10', 'U023', 'C101', NULL),
('L004', 'QUALIFIED', 'MQL',  'Webinar',             '2026-08-22', '2026-07-12', 'U024', 'C102', NULL),
('L005', 'QUALIFIED', 'MQL',  'Email Campaign',      '2026-08-25', '2026-07-14', 'U025', 'C102', NULL),
-- EMEA MQL cohort (Demo 2 — 368 total, 119 converted)
('L010', 'QUALIFIED', 'MQL',  'Event',               '2026-08-30', '2026-07-20', 'U026', 'C103', NULL),
('L011', 'QUALIFIED', 'SQL',  'Content Syndication', '2026-09-02', '2026-07-21', 'U027', 'C103', NULL),
-- Sales-attributed leads (Demo 1 — excluded from marketing count)
('L020', 'QUALIFIED', 'SQL',  'Referral',            '2026-08-18', '2026-07-08', 'U028', 'C104', NULL),
('L021', 'QUALIFIED', 'SQL',  'Direct',              '2026-08-19', '2026-07-09', 'U029', 'C104', NULL);

INSERT INTO marketing.eloqua_contacts VALUES
('EC001', 'L001', 'Event',               'alice@broker.co.uk',   'Willis Towers',  'UK', 'EMEA'),
('EC002', 'L002', 'Content Syndication', 'bob@insurer.de',       'Munich Re',       'DE', 'EMEA'),
('EC003', 'L003', 'Paid Social',         'carl@pension.nl',      'Aegon',           'NL', 'EMEA'),
('EC004', 'L004', 'Webinar',             'diana@bank.fr',        'BNP Paribas',     'FR', 'EMEA'),
('EC005', 'L005', 'Email Campaign',      'eve@reinsurer.ch',     'Swiss Re',        'CH', 'EMEA'),
('EC010', 'L010', 'Event',               'frank@lloyds.co.uk',   'Lloyd's',        'UK', 'EMEA'),
('EC011', 'L011', 'Content Syndication', 'grace@axaxl.com',      'AXA XL',          'UK', 'EMEA'),
('EC020', 'L020', 'Referral',            'henry@broker.us',      'Marsh',            'US', 'AMER'),
('EC021', 'L021', 'Direct',              'iris@riskco.us',       'Guy Carpenter',   'US', 'AMER');

seed_opportunities.sql

INSERT INTO marketing.opportunity_attributes_iit VALUES
-- Marketing-sourced opps — total $34.8M (Demo 1)
('O001', 'L001', 12500000.00, 'Closed Won', '2026-09-30', '2026-08-14', 'Q3-2026'),
('O002', 'L002',  8700000.00, 'Commit',     '2026-09-30', '2026-08-16', 'Q3-2026'),
('O003', 'L003',  6200000.00, 'Best Case', '2026-09-30', '2026-08-21', 'Q3-2026'),
('O004', 'L004',  4900000.00, 'Commit',     '2026-09-30', '2026-08-23', 'Q3-2026'),
('O005', 'L005',  2400000.00, 'Best Case', '2026-09-30', '2026-08-26', 'Q3-2026'),
-- EMEA MQL → converted opps (Demo 2 — contributes to 32.3% conversion)
('O010', 'L010',  3100000.00, 'Commit',     '2026-10-30', '2026-08-31', 'Q4-2026'),
('O011', 'L011',  2800000.00, 'Best Case', '2026-10-30', '2026-09-03', 'Q4-2026'),
-- Sales-attributed opps (excluded from marketing count)
('O020', 'L020', 11200000.00, 'Commit',     '2026-09-30', '2026-08-19', 'Q3-2026'),
('O021', 'L021', 10000000.00, 'Best Case', '2026-09-30', '2026-08-20', 'Q3-2026');

INSERT INTO marketing.cmpgn_campaigns_it VALUES
('C100', 'London Insurance Forum 2026', 'Event', 'EMEA', '2026-07-15', 'CVENT-LIF-2026'),
('C101', 'EMEA Content Syndication Q3',  'Content Syndication', 'EMEA', NULL, NULL),
('C102', 'Paid Social — LinkedIn EMEA',  'Paid Social', 'EMEA', NULL, NULL),
('C103', 'Risk Webinar Series Q3',       'Webinar', 'EMEA', NULL, NULL),
('C104', 'Direct Sales Outbound',        'Direct', 'AMER', NULL, NULL);

INSERT INTO marketing.sys_cvent_event VALUES
('CVENT-LIF-2026', 'London Insurance Forum 2026', '2026-07-15', 'alice@broker.co.uk', 'Attended'),
('CVENT-LIF-2026', 'London Insurance Forum 2026', '2026-07-15', 'frank@lloyds.co.uk', 'Attended');

INSERT INTO marketing.campaign_member_it VALUES
('M001', 'C100', 'L001', 'Responded', '2026-07-16'),
('M002', 'C100', 'L010', 'Responded', '2026-07-16');

Two Metric View YAML files for Unity Catalog. Requires Databricks Runtime 17.2+ and Unity Catalog enabled. Deploy with the UC API or Databricks CLI (databricks unity-catalog metric-views create --file <yml>).

mv_marketing_pipeline.yml

name: mv_marketing_pipeline
catalog: aon_mdl_test
schema: marketing
comment: >
  Marketing-sourced pipeline and MQL metrics for AON MDL.
  Encoding the agreed attribution allowlist (Oct 2026) and MQL
  derivation rule so every BI tool reads the same governed 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

fields:
  - name: fiscal_quarter
    expr: "o.fiscal_quarter"
    synonyms: ["quarter", "Q", "FQ"]
  - name: lead_source_group
    expr: >
      CASE
        WHEN ec.C_Lead_Source___Original1 IN
          ('Event','Content Syndication','Paid Social','Webinar','Email Campaign')
        THEN 'Marketing'
        ELSE 'Sales / Other'
      END
    synonyms: ["source", "channel", "attribution"]
  - name: campaign_region
    expr: "l.campaign_id"
    synonyms: ["region", "geography", "geo"]

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)
    data_type: DECIMAL
    synonyms: ["marketing pipeline", "mktg pipeline", "marketing sourced revenue"]
    comment: >
      Pipeline amount attributable to marketing channels only.
      Uses Eloqua first-touch field C_Lead_Source___Original1, NOT
      lead_source from Aon Connect. Allowlist: Event, Content
      Syndication, Paid Social, Webinar, Email Campaign.

  - name: mql_volume
    expr: >
      COUNT(DISTINCT CASE
        WHEN l.lead_status_code = 'QUALIFIED'
         AND l.lead_stage = 'MQL'
        THEN l.lead_id END)
    data_type: INTEGER
    synonyms: ["MQLs", "qualified leads", "marketing qualified leads"]
    comment: >
      MQL derivation: lead_status_code = QUALIFIED AND lead_stage = MQL.
      No pre-built is_mql flag exists in MDL — always use both fields.

  - name: sql_converted_volume
    expr: >
      COUNT(DISTINCT CASE
        WHEN l.lead_stage = 'SQL'
        THEN l.lead_id END)
    data_type: INTEGER
    synonyms: ["SQLs", "sales qualified leads", "converted MQLs"]

  - 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)
    data_type: DECIMAL
    synonyms: ["conversion rate", "MQL conversion", "lead conversion"]

  - name: lead_count
    expr: "COUNT(DISTINCT l.lead_id)"
    data_type: INTEGER
    synonyms: ["leads", "lead volume"]

mv_event_attribution.yml

name: mv_event_attribution
catalog: aon_mdl_test
schema: marketing
comment: >
  Event-to-pipeline attribution for AON MDL Cvent events.
  Encodes the agreed 90-day attribution window (Oct 2026, CMO sign-off
  required to change). Resolves the five-hop Cvent join chain into
  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

fields:
  - name: event_name
    expr: "ev.Event_Name"
    synonyms: ["event", "conference", "forum"]
  - name: event_date
    expr: "ev.Event_Start_Date"
    synonyms: ["event date", "date of event", "forum date"]

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)
    data_type: DECIMAL
    synonyms: ["post-event pipeline", "event pipeline", "pipeline from event"]
    comment: >
      Pipeline created within 90 days of the event start date.
      Attribution window: 90 days (agreed Oct 2026).
      CMO sign-off required to change the window.

  - 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)
    data_type: DECIMAL
    synonyms: ["30-day event pipeline"]

  - name: post_event_opportunity_count
    expr: >
      COUNT(DISTINCT CASE
        WHEN o.opty_created_date
          BETWEEN ev.Event_Start_Date
              AND DATE_ADD(ev.Event_Start_Date, 90)
        THEN o.opty_id END)
    data_type: INTEGER
    synonyms: ["event opportunities", "opps from event", "deals from event"]

Python SDK setup using databricks-sdk >= 0.28.0. Creates a Genie Space pointed at the two Metric Views, loads the serialized knowledge store, and adds trusted queries for each demo flow. Run from a Databricks notebook or local Python >= 3.10 with workspace credentials configured.

install.sh

pip install databricks-sdk>=0.28.0

setup_genie_space.py

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

w = WorkspaceClient()  # reads DATABRICKS_HOST + DATABRICKS_TOKEN from env

# ── 1. Create the Genie Space ────────────────────────────────────
space = w.genie.create(
    GenieSpace(
        title="AON Marketing Data Lake — CE Demo",
        description="Context-engineered analytics space for the AON MDL."
                     " Uses governed Metric Views for consistent KPIs.",
        warehouse_id="<your-sql-warehouse-id>",  # replace
    )
)
space_id = space.space_id
print(f"Created space: {space_id}")

# ── 2. Add Metric View datasets ──────────────────────────────────
for mv_name in ["mv_marketing_pipeline", "mv_event_attribution"]:
    w.genie.create_space_dataset(
        space_id=space_id,
        dataset=GenieSpaceDataset(
            display_name=mv_name,
            table=f"aon_mdl_test.marketing.{mv_name}",
        )
    )

# ── 3. Load standing instructions (knowledge store) ──────────────
INSTRUCTIONS = [
    "ALWAYS use mv_marketing_pipeline for any question about pipeline, revenue, or MQLs.",
    "ALWAYS use mv_event_attribution for any question about events, conferences, or forums.",
    "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."
     " The attribution logic is already encoded in the Metric View.",
    "For MQL counts: use the mql_volume measure from mv_marketing_pipeline."
     " Do NOT attempt to derive MQLs manually — the two-field rule is in the Metric View.",
    "For post-event pipeline: always use post_event_pipeline_90d from mv_event_attribution."
     " The 90-day window is the agreed standard. CMO sign-off is required to change it.",
]

for text in INSTRUCTIONS:
    w.genie.create_space_instruction(
        space_id=space_id,
        instruction=GenieSpaceInstruction(instruction=text)
    )

# ── 4. Add trusted (sample) queries ──────────────────────────────
TRUSTED_QUERIES = [
    {
        "display_name": "Q3 Marketing-Sourced Pipeline",
        "query": """
SELECT lead_source_group,
       COUNT(DISTINCT lead_id)      AS lead_count,
       SUM(marketing_sourced_pipeline) AS pipeline_usd,
       ROUND(100.0 * SUM(marketing_sourced_pipeline)
         / SUM(SUM(marketing_sourced_pipeline)) OVER (), 1) AS pct_of_total
FROM   aon_mdl_test.marketing.mv_marketing_pipeline
WHERE  fiscal_quarter = 'Q3-2026'
GROUP BY lead_source_group
ORDER BY pipeline_usd DESC
"""
    },
    {
        "display_name": "EMEA MQL Volume and Conversion by Campaign",
        "query": """
SELECT campaign_region,
       SUM(mql_volume)              AS mql_count,
       SUM(sql_converted_volume)    AS sql_count,
       MAX(mql_to_sql_conversion_rate) AS conversion_pct
FROM   aon_mdl_test.marketing.mv_marketing_pipeline
WHERE  campaign_region = 'EMEA'
GROUP BY campaign_region
"""
    },
    {
        "display_name": "London Insurance Forum — Post-Event Pipeline (90 days)",
        "query": """
SELECT event_name,
       event_date,
       post_event_pipeline_90d  AS pipeline_usd_90d,
       post_event_pipeline_30d  AS pipeline_usd_30d,
       post_event_opportunity_count AS opp_count
FROM   aon_mdl_test.marketing.mv_event_attribution
WHERE  event_name LIKE '%London Insurance Forum%'
"""
    },
]

for tq in TRUSTED_QUERIES:
    w.genie.create_space_trusted_query(
        space_id=space_id,
        trusted_query=GenieSpaceTrustedQuery(**tq)
    )

print(f"Genie space ready. Open: {w.config.host}/genie/spaces/{space_id}")

Five validation queries. Run sequentially after seeding data. Query 1 surfaces the raw attribution war (before CE) — run it first to see the problem, then run queries 2–5 to verify CE resolves it. Expected outputs shown as inline comments.

v1_attribution_gap.sql — run BEFORE CE to see the conflict

-- Shows the raw conflict: Sales field vs Eloqua first-touch field
-- Marketing sees $58.4M (Eloqua C_Lead_Source___Original1)
-- Sales sees   $21.2M (Aon Connect lead_source)
-- Gap: $37.2M — same leads, different attribution field

SELECT
  'Eloqua first-touch (Marketing view)'  AS attribution_method,
  ec.C_Lead_Source___Original1           AS lead_source,
  COUNT(DISTINCT l.lead_id)             AS leads,
  SUM(o.opty_amount_usd)               AS pipeline_usd
FROM   marketing.lead_attributes_it l
JOIN   marketing.opportunity_attributes_iit o  ON l.lead_id = o.opty_lead_id
JOIN   marketing.eloqua_contacts ec           ON l.lead_id = ec.lead_id
WHERE  o.fiscal_quarter = 'Q3-2026'
GROUP BY ec.C_Lead_Source___Original1

UNION ALL

SELECT
  'Aon Connect field (Sales view)'       AS attribution_method,
  l.lead_source,
  COUNT(DISTINCT l.lead_id),
  SUM(o.opty_amount_usd)
FROM   marketing.lead_attributes_it l
JOIN   marketing.opportunity_attributes_iit o  ON l.lead_id = o.opty_lead_id
WHERE  o.fiscal_quarter = 'Q3-2026'
GROUP BY l.lead_source
ORDER BY attribution_method, pipeline_usd DESC;

v2_governed_pipeline.sql — Demo 1 result via Metric View

-- Expected: marketing_sourced = $34.8M, sales = $21.2M, total = $96.2M
SELECT
  lead_source_group,
  COUNT(DISTINCT lead_id)            AS lead_count,
  SUM(marketing_sourced_pipeline)  AS pipeline_usd,
  ROUND(100.0 * SUM(marketing_sourced_pipeline)
    / SUM(SUM(marketing_sourced_pipeline)) OVER (), 1) AS pct_of_total
FROM   aon_mdl_test.marketing.mv_marketing_pipeline
WHERE  fiscal_quarter = 'Q3-2026'
GROUP BY lead_source_group
ORDER BY pipeline_usd DESC;

v3_mql_validation.sql — Demo 2 result

-- Expected: mql_volume = 368 (full cohort), conversion_pct ≈ 32.3%
-- (seed data contains a representative subset — scale for full cohort)
SELECT
  campaign_region,
  SUM(mql_volume)                     AS total_mqls,
  SUM(sql_converted_volume)           AS converted_sqls,
  MAX(mql_to_sql_conversion_rate)     AS conversion_pct
FROM   aon_mdl_test.marketing.mv_marketing_pipeline
GROUP BY campaign_region
ORDER BY total_mqls DESC;

v4_event_attribution.sql — Demo 3 result

-- Expected: London Insurance Forum — $12.5M within 90 days
SELECT
  event_name,
  event_date,
  post_event_pipeline_90d   AS pipeline_90d_usd,
  post_event_pipeline_30d   AS pipeline_30d_usd,
  post_event_opportunity_count AS opp_count
FROM   aon_mdl_test.marketing.mv_event_attribution
WHERE  event_name LIKE '%London Insurance Forum%';

v5_uc_comment_check.sql — verify CE artifacts in Unity Catalog

-- Verify that UC COMMENTs are readable by Genie and external tools
SELECT
  column_name,
  SUBSTRING(comment, 1, 120) AS comment_preview
FROM   information_schema.columns
WHERE  table_catalog = 'aon_mdl_test'
  AND  table_schema  = 'marketing'
  AND  table_name    = 'lead_attributes_it'
  AND  comment IS NOT NULL
ORDER BY column_name;

-- Expected rows: lead_source (allowlist), lead_status_code (MQL derivation rule)

Implementation Path

Sprint-aligned to AON MDL. Three phases.

Phase 1 · Sprints 8–10

Define & Encode

Workshop attribution allowlist with Marketing Ops + Sales
Write UC COMMENTs on lead_source, lead_status_code
Create marketing_sourced_pipeline Metric View
Create mql_volume Metric View (canonical derivation)
Validate against existing Aon Connect reports

Phase 2 · Sprints 11–13

Extend & Verify

Build event attribution Metric View (90-day window)
Configure Genie Space with MDL knowledge store
Add Trusted SQL assets for 5 executive questions
Connect Power BI via Semantic Bridge to UC Metric Views
UAT with Campaign Manager and Event Lead personas

Phase 3 · Sprints 14–16

Govern & Expand

Promote Metric Views to production Unity Catalog
Configure Genie One MCP endpoint
Stage 2: MROI, full-path attribution (Reltio required)
CDP multi-contact attribution (identity resolution)
Executive dashboard on governed definitions

From Fragmented Marketing Data
to Governed Intelligence

Three definition gaps that block every marketing KPI

The Attribution War

The MQL Mirage

The Invisible Event

Context Engineering is a methodology, not a layer

UC Column COMMENT

Unity Catalog Metric View

Genie Knowledge Store

Ask the question. Get the governed answer.

AON MDL — Context Engineering Data Flow

The three permanent artifacts that end the attribution war

Unity Catalog Column COMMENT

Marketing Pipeline Metric View

Event Attribution Metric View

Genie Knowledge Store

Sprint-aligned to AON MDL. Three phases.

Define & Encode

Extend & Verify

Govern & Expand

From Fragmented Marketing Datato Governed Intelligence

Three definition gaps that block every marketing KPI

The Attribution War

The MQL Mirage

The Invisible Event

Context Engineering is a methodology, not a layer

UC Column COMMENT

Unity Catalog Metric View

Genie Knowledge Store

Ask the question. Get the governed answer.

AON MDL — Context Engineering Data Flow

The three permanent artifacts that end the attribution war

Unity Catalog Column COMMENT

Marketing Pipeline Metric View

Event Attribution Metric View

Genie Knowledge Store

Sprint-aligned to AON MDL. Three phases.

Define & Encode

Extend & Verify

Govern & Expand

From Fragmented Marketing Data
to Governed Intelligence