SUBMISSION: Case 2 — Conformed Marketing Attribution Dimension

# SUBMISSION: Case 2 — Conformed Marketing Attribution Dimension > 🎯 **Hands-on Portfolio Deliverable:** In this project, you will solve one of the most widespread engineering headaches in growth analytics: reconciling conflicting channel taxonomies across ad platforms (Google Ads, Meta, TikTok, LinkedIn) and tracking tools into a unified **Conformed Dimension** and attribution fact model. ## 🏢 Business Scenario & Executive Prompt You are the Senior Analytics Engineer at **Velox Commerce**, a multi-brand direct-to-consumer retailer investing over $750,000 per month across digital advertising platforms. The VP of Marketing and CFO are locked in weekly arguments: Google Ads claims $1.2M in revenue, Meta claims $900K, and Google Analytics 4 reports only $1.4M total company revenue. Because each platform invents its own naming standards (`facebook` vs `fb_ad` vs `Meta-Paid-Social`), department heads cannot determine their true blended Customer Acquisition Cost (CAC) or Return on Ad Spend (ROAS). Your task is to build a conformed dimensional marketing mart: 1. **Conformed Hierarchy:** Establish a canonical taxonomy (`channel_group` $ ightarrow$ `channel_medium` $ ightarrow$ `channel_source`). 2. **Staging Mapping Engine:** Create the mapping logic that ingests chaotic raw campaign UTM tags and resolves them into conformed keys. 3. **Cross-System Fact Table:** Union marketing ad spend with conversion events into a unified fact model that powers real-time ROAS dashboards. --- ## 🎯 Required Deliverables You must upload a single `.sql` or `.zip` file containing: 1. **`marketing_conformed_dim.sql`**: Production DDL creating `dim_marketing_channel` and `fct_marketing_touchpoints`. 2. **Staging Transformation Logic**: SQL DDL/view demonstrating how raw UTM tags from disparate ad platforms map into the conformed dimension. 3. **`attribution_analytics.sql`**: Verification SQL queries calculating Blended CAC and ROAS by Channel Group. --- > *"Your warehouse has three marketing sources: Google Ads (channel = 'google_search'), Facebook Ads (channel = 'fb_paid'), and your in-app tracker (channel = 'organic_search', 'paid_search', 'social_paid', 'social_organic'). Build a conformed channel dimension that all three feed into."* This case tests conformed-dimension thinking — the core of warehouse design — and the pragmatic problem-solving every analytics engineer faces. --- ## Step 1 — Inventory the source channels | Source | Source channel values | |---|---| | Google Ads | `google_search`, `google_display`, `google_youtube`, `google_pmax` | | Facebook Ads | `fb_paid`, `instagram_paid`, `messenger_paid` | | In-app tracker | `organic_search`, `paid_search`, `social_paid`, `social_organic`, `direct`, `referral`, `email` | 13 distinct channel strings across 3 systems. Some overlap (Google Search vs the tracker's `paid_search`), some don't. ## Step 2 — Define the conformed channel hierarchy The conformed dimension should let analysts roll up at multiple levels: | Level 1 (broad) | Level 2 (medium) | Level 3 (granular) | |---|---|---| | Paid | Paid Search | Google Search, Bing Search | | Paid | Paid Social | Facebook Ads, Instagram Ads | | Paid | Paid Display | Google Display, Programmatic | | Paid | Paid Video | YouTube, TikTok | | Organic | Organic Search | Google Organic, Bing Organic | | Organic | Organic Social | Facebook Organic, LinkedIn Organic | | Owned | Email | Marketing emails, Drip campaigns | | Owned | Direct | Direct URL entry, App opens | | Earned | Referral | Other websites linking | This hierarchy lets analysts roll up to Paid vs Organic vs Owned vs Earned, or drill down to specific platforms. ## Step 3 — Build the conformed channel dim ```sql CREATE TABLE dim_channel ( channel_key INTEGER PRIMARY KEY, channel_id VARCHAR, -- e.g., 'google-paid-search' -- the hierarchy channel_l1 VARCHAR, -- 'Paid' channel_l2 VARCHAR, -- 'Paid Search' channel_l3 VARCHAR, -- 'Google Search' -- attributes is_paid BOOLEAN, is_brand_search BOOLEAN, -- e.g., for branded-keyword campaigns is_remarketing BOOLEAN, -- audit created_at TIMESTAMPTZ ); ``` Pre-populate the dim with all distinct values from each source plus the hierarchical assignment. ## Step 4 — Build the staging mapping Each source needs a staging model that maps its native channel string to the conformed `channel_key`: ```sql -- stg_google_ads_touchpoints SELECT timestamp AS touch_time, user_id, campaign_id, CASE WHEN source_channel = 'google_search' THEN 1 -- channel_key for Google Search WHEN source_channel = 'google_display' THEN 2 WHEN source_channel = 'google_youtube' THEN 3 WHEN source_channel = 'google_pmax' THEN 4 END AS channel_key, cost, impressions FROM raw.google_ads_clicks; ``` ```sql -- stg_facebook_ad