The Semantic Layer Audit: Grounding Natural Language Queries in Real Metric Schemas
Objective: Audit a marketing data dictionary and semantic schema mapping across GA4 web sessions, CRM pipeline values, and ad platform spend tables to ensure natural language query (NLQ) tools produce mathematically accurate queries without hallucinated column joins or ambiguous metric definitions.
You are the analytics lead setting up an AI query interface over Snowflake data tables for a multi-channel growth marketing team. Before enabling self-serve NLQ access for non-technical marketers, you must audit the semantic layer to eliminate ambiguous metric definitions.
Evaluate 3 critical metric mappings (MQL vs Raw Contact, Blended CAC vs Paid CAC, Booked MRR vs Recognized Revenue) in the semantic dictionary, establish explicit SQL join rules, and document validation queries.
How do you build a semantic layer that prevents AI natural language query tools from hallucinating metric definitions and generating false marketing reports?
Before you start
What you'll need
- —Understanding of marketing funnels (leads, MQLs, CAC) and basic database table relationships
Free path (everything below is enough to finish)
Document business definitions, table relationships, and SQL validation rules
Test natural language prompts against table schemas and inspect generated SQL queries
Paid upgrades (optional, faster/deeper)
Google Analytics 4 with BigQuery export provides the raw event-level data warehouse schema.
Source table for event schemas, session counts, and conversion benchmarks
The process
3 steps
Step 01 of 03
Stage 2 of the AI analytics playbook emphasizes that without a governed semantic layer, NLQ engines guess metric meanings—often conflating top-of-funnel form fills with verified Marketing Qualified Leads (MQLs).
When a user asks 'How many leads did we generate in Q1?', how does the semantic layer ensure the AI queries `crm_contacts.is_mql = true` rather than raw form submissions in `ga4_events`?
Procedure
- Audit the field synonyms in the semantic dictionary for 'Lead', 'MQL', 'Prospect', and 'Sign-up'.
- Define explicit entity mappings: 'Lead' -> `crm_contacts` table where `status NOT IN ('junk', 'spam')`.
- Define 'Marketing Qualified Lead' (MQL) -> `crm_contacts` table where `is_mql = true AND mql_date >= '2026-01-01'`.
- Specify the default fallback behavior when a prompt uses ambiguous terminology.
| Business Term | User Prompt Synonym | Underlying Table | SQL Filter Condition | Common AI Trap | |---|---|---|---|---| | Raw Contact | 'Sign-ups', 'Submissions' | `raw_form_submissions` | `created_at IS NOT NULL` | Overcounts spam bot fills | | Valid Lead | 'Leads', 'New contacts' | `crm_contacts` | `is_valid_email = true AND status != 'spam'` | Standard pipeline count | | Marketing Qualified Lead | 'MQLs', 'Qualified leads' | `crm_contacts` | `is_mql = true AND mql_score >= 50` | Conflating raw leads with MQLs (inflates ROI 3.4x) |
Healthy
Every business metric has an unambiguous entity definition with explicit SQL filtering criteria documented in the semantic model.
Unhealthy
The AI guesses which table to query, counting every newsletter sign-up as a sales-qualified lead and generating inflated conversion reports.
What this means
A query for 'Leads' that pulls from `raw_form_submissions` reports 14,200 leads, whereas filtering for verified MQLs in `crm_contacts` yields 3,850 leads. Without semantic governance, the AI over-reports qualified acquisition by 268%.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| AI analytics tool reporting 3x more leads than sales CRM shows | Add strict synonym mappings in the semantic layer pointing 'Leads' to `crm_contacts.is_mql = true` | 30 min |
| Marketers confused by conflicting lead counts between dashboards | Configure the NLQ tool to prompt for clarification: 'Did you mean Raw Contacts (14.2k) or Qualified MQLs (3.8k)?' | half day |
Step 02 of 03
LLMs generate database queries by joining tables based on naming conventions. If multiple revenue columns exist across billing, CRM, and ad platforms, unguided models create incorrect table joins that distort ROI calculations.
How do you configure table join rules between `ad_spend`, `web_sessions`, and `stripe_invoices` so that natural language CAC calculations divide actual paid ad spend by verified closed customer count?
Procedure
- Map primary and foreign key relationships between `google_ads_spend`, `meta_ads_spend`, `ga4_sessions`, and `stripe_charges`.
- Write the standardized Blended CAC calculation formula: `SUM(all_ad_spend.cost) / COUNT(DISTINCT stripe_charges.customer_id)`.
- Write the Paid-Only CAC formula: `SUM(paid_campaign_spend.cost) / COUNT(DISTINCT paid_attributed_customers)`.
- Test the schema map with a prompt: 'What was our Paid CAC by channel last month?'
| Metric | Required Formula | Primary Table | Joined Tables | Join Key | Failure Mode if Ungoverned | |---|---|---|---|---|---| | Blended CAC | `SUM(spend) / COUNT(DISTINCT new_customers)` | `marketing_spend_daily` | `stripe_customers` | `date = charge_date` | Dividing spend by organic sign-ups | | Paid CAC | `SUM(ad_cost) / COUNT(DISTINCT first_paid_order)` | `ad_channel_spend` | `attributed_orders` | `campaign_id = utm_campaign` | Multi-touch double counting across channels | | Gross Margin ARR | `SUM(mrr_amount * (1 - cogs_pct))` | `subscription_mrr` | `product_cogs` | `product_id` | AI summing top-line revenue without COGS |
Healthy
Calculated metrics use pre-defined business formulas rather than letting the LLM construct ad-hoc mathematical expressions on raw column sums.
Unhealthy
AI performs an inner join between ad impressions and invoices, creating a cross-product multiplication that reports millions in fictitious revenue.
What this means
Standardizing metric definitions prevents the AI from mixing Blended CAC ($42) with Paid Channel CAC ($118), ensuring leadership receives accurate unit economics.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| AI report showing negative CAC or implausibly high ROAS | Lock calculated metrics into database views so NLQ queries pre-computed metrics instead of raw table joins | half day |
| Discrepancy between Google Ads reported conversions and Stripe actual paying customers | Define 'Conversion' in the semantic catalog to strictly mean confirmed payment settled in Stripe | 30 min |
Step 03 of 03
Mistake 1 in the lesson warns that AI analytics tools return wrong answers with complete visual confidence. A sanity-check protocol compares AI query outputs against one trusted baseline before publishing.
What step-by-step verification checklist should every marketing team member execute before presenting an AI-generated chart in an executive review?
Procedure
- Request the underlying SQL query generated by the AI tool alongside the visual chart.
- Verify the `WHERE` date clause matches the requested reporting timeframe (check for time zone offset errors).
- Cross-check the total row count or aggregate sum against a known static report (e.g. GA4 dashboard total monthly sessions).
- Check for `DISTINCT` operators on customer/order IDs to confirm no fan-out multiplication occurred.
Sanity-Check Validation Card: • Prompt: 'Show monthly recurring revenue by plan for Q1 2026' • AI Generated SQL: `SELECT plan_name, SUM(amount) FROM subscriptions WHERE start_date >= '2026-01-01' GROUP BY plan_name;` • Defect Spotted: AI summed all historical transactions for plans starting in Q1 rather than active MRR snapshots on the last day of each month. • Sanity Check Result: FAILED (AI reported $480k MRR vs actual $160k MRR). • Corrected Prompt: 'Show active subscription MRR as of March 31, 2026 grouped by plan tier.' • Corrected SQL: Uses `status = 'active' AND billing_cycle = 'monthly'`. • Re-check Result: PASSED ($160,450 matches Stripe billing dashboard).
Healthy
All AI-generated metrics are verified against at least one trusted source dashboard before sharing with stakeholders.
Unhealthy
Marketers copy-pasting confident-looking AI graphs directly into executive slide decks without checking the SQL logic.
What this means
The initial prompt caused the AI to sum all cumulative invoice line items across the entire quarter instead of computing active monthly recurring revenue, tripling the true MRR figure.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| AI metric looks 2x-3x higher than expected | Ask the AI: 'Show the SQL query used to calculate this number' and inspect the GROUP BY and SUM logic | 5 min |
| AI query omitting recent weekend or month-end transactions | Specify explicit UTC timestamp ranges in prompt instead of relative phrases like 'last month' | 5 min |
Final deliverable
A verified Semantic Layer Data Dictionary with business metric definitions, required SQL join criteria, and a 4-step sanity-check verification protocol.
See a reference example
Semantic Layer Configuration for Freshworks Marketing Data Hub: 1. Disambiguated 'Trial Sign-ups' (`auth_users` table, 18,200 events) from 'Product Qualified Leads' (`product_usage` table where `seat_count >= 3 AND active_days >= 5`, 2,840 PQLs). 2. Defined Paid CAC formula in warehouse semantic catalog: `SUM(google_ads.spend + linkedin_ads.spend) / COUNT(DISTINCT stripe_customers.new_paid_account)`. Fixed previous hallucinated join that was counting free trial accounts as paying customers. 3. Deployed 4-step sanity-check protocol for the marketing team, catching a time zone date-drift bug that previously misattributed $42,000 in month-end renewals.
Success criteria
You're done when you can:
- Identify and resolve ambiguity between raw contact creations and verified MQLs
- Define precise calculation formulas for Blended vs Paid CAC to prevent AI overcounting
- Establish baseline tolerance thresholds for comparing NLQ output against trusted reporting dashboards
Key takeaway
AI analytics tools are only as accurate as their semantic layer: without strict definitions for metrics like MQL and CAC, models guess column joins and generate confident but dangerously wrong numbers.