🎯 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:
- Conformed Hierarchy: Establish a canonical taxonomy (
channel_group$ ightarrow$channel_medium$ ightarrow$channel_source). - Staging Mapping Engine: Create the mapping logic that ingests chaotic raw campaign UTM tags and resolves them into conformed keys.
- 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:
marketing_conformed_dim.sql: Production DDL creatingdim_marketing_channelandfct_marketing_touchpoints.- Staging Transformation Logic: SQL DDL/view demonstrating how raw UTM tags from disparate ad platforms map into the conformed dimension.
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 | 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
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:
-- 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;
-- stg_facebook_ads_touchpoints
SELECT
timestamp AS touch_time,
user_id,
campaign_id,
CASE
WHEN platform = 'facebook' THEN 5
WHEN platform = 'instagram' THEN 6
WHEN platform = 'messenger' THEN 7
END AS channel_key,
cost,
impressions
FROM raw.facebook_ads_clicks;
-- stg_in_app_touchpoints
SELECT
event_time AS touch_time,
user_id,
NULL AS campaign_id,
CASE
WHEN source = 'organic_search' THEN 8
WHEN source = 'paid_search' THEN 1 -- same channel_key as Google Ads' google_search
WHEN source = 'social_paid' THEN 5 -- same channel_key as facebook
WHEN source = 'social_organic' THEN 9
WHEN source = 'direct' THEN 10
WHEN source = 'referral' THEN 11
WHEN source = 'email' THEN 12
END AS channel_key,
NULL AS cost,
NULL AS impressions
FROM raw.events
WHERE event_type = 'page_view';
Step 5 — Union into one conformed fact
-- fct_marketing_touchpoints
SELECT 'google_ads' AS source_system, * FROM stg_google_ads_touchpoints
UNION ALL
SELECT 'facebook_ads' AS source_system, * FROM stg_facebook_ads_touchpoints
UNION ALL
SELECT 'in_app' AS source_system, * FROM stg_in_app_touchpoints;
Now any analytics query joins to one dim_channel and gets a unified view across all sources.
Step 6 — What this lets you do
Spend vs conversions by conformed channel
SELECT c.channel_l2, SUM(t.cost) AS total_spend, COUNT(DISTINCT t.user_id) AS unique_users
FROM fct_marketing_touchpoints t
JOIN dim_channel c ON c.channel_key = t.channel_key
GROUP BY c.channel_l2;
Cross-system "paid search" view
Combining Google Ads paid_search with the in-app tracker's paid_search records (both map to channel_key=1). Conformed dimension makes this trivial.
Roll-ups
channel_l1 aggregates paid vs organic vs owned. channel_l2 breaks into platform groups. channel_l3 shows the most-granular view. Same dim, three levels, depending on the query.
Step 7 — Talk through the design
You: "The key idea is one conformed
dim_channelwith a multi-level hierarchy. Every source system maps to channel keys in its staging layer. Downstream models and dashboards never see the source-specific strings — they query the conformed dimension and slice at whatever level they need.The mapping is the hard part. It's a business decision: 'Is the in-app tracker's paid_search the same as Google Ads' google_search?' Sometimes yes, sometimes no (they could be tracking different things). The mapping lives in code, version-controlled, with comments explaining decisions.
Adding a new source — say, LinkedIn Ads — is one new staging model and a few new dim rows. The conformed dim is the contract; the rest is mechanical."
Common follow-ups
Follow-up 1: "What if Facebook's instagram_paid and the in-app tracker's social_paid overlap but aren't identical?"
Two options:
- Map them to different channel keys with a parent rollup in
channel_l2 = 'Paid Social'. Thenchannel_l2rollups combine them, but granular views distinguish them. - Map them to the same channel key and accept some attribution noise. Document the decision.
Follow-up 2: "How do you handle a campaign that's tagged differently across systems?"
Conformed dim_campaign — same pattern as conformed channel. Each source's campaign IDs map to a unified campaign_key with attributes like start_date, end_date, channel_key, target_audience.
Follow-up 3: "How would you validate the mapping is correct?"
- Per-source touchpoint counts should equal per-source totals in the original raw tables.
- Cross-channel spend should reconcile to source-system reported spend (Google Ads dashboard, Facebook Ads Manager).
- Alerts when a new channel value appears in source that isn't mapped — surface it for human review.
📥 Download Starter Assets & Mock Database
Choose your preferred environment to get started immediately:
💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
⬇ Download case2_marketing_attribution.sqlite
Pre-loaded with raw source tables, mock transactional records, and schema skeletons. Double-click to open in DB Browser for SQLite, DBeaver, or VS Code.📄 ANSI / PostgreSQL SQL Starter Script:
⬇ Download case2_marketing_attribution_starter.sql
Contains clean DDL statements, table definitions, and mock INSERT statements ready to paste into PostgreSQL, DuckDB, Snowflake, or BigQuery.
🛠️ How to Complete & Run This Case Study
You can complete this hands-on project using whichever tool matches your local setup:
- Option 1: SQLite / DB Browser / DBeaver (Easiest — Zero Installation)
Downloadcase2_marketing_attribution.sqliteabove. Open it in DB Browser for SQLite, DBeaver, or VS Code (with the free SQLite Viewer extension). All source ingestion tables and sample records are already populated. Write your target dimensional models and verification queries directly against the database with 0 server configuration. - Option 2: Local PostgreSQL or DuckDB
Downloadcase2_marketing_attribution_starter.sql. Execute the script in your local PostgreSQL database (via psql, DBeaver, or pgAdmin) or in DuckDB. - Option 3: Cloud Data Warehouse (Snowflake / BigQuery)
Copy and paste the DDL and mock seed data into a Snowflake worksheet or Google BigQuery dataset.
💻 Starter Scaffold
Use this template to begin your implementation:
-- ====================================================================
-- DELIVERABLE 1: marketing_conformed_dim.sql
-- ====================================================================
-- 1. CONFORMED DIMENSION: dim_marketing_channel
CREATE TABLE IF NOT EXISTS dim_marketing_channel (
channel_key INTEGER PRIMARY KEY,
-- Standard Conformed Hierarchy
channel_group VARCHAR(100) NOT NULL, -- Paid Search, Paid Social, Organic Search, Email, Affiliate
channel_medium VARCHAR(100) NOT NULL, -- cpc, cpm, email, organic, referral
channel_source VARCHAR(100) NOT NULL, -- google, facebook, instagram, tiktok, bing, newsletter
is_paid BOOLEAN NOT NULL,
default_cost_type VARCHAR(50) NOT NULL -- CPC, CPM, Flat
);
-- 2. STAGING TRANSFORMATION: stg_ad_spend_mapping
-- TODO: Write view/CTEs mapping source-specific strings to dim_marketing_channel keys.
-- 3. UNIFIED FACT TABLE: fct_marketing_touchpoints
CREATE TABLE IF NOT EXISTS fct_marketing_touchpoints (
touchpoint_key BIGINT PRIMARY KEY,
touchpoint_id VARCHAR(255) NOT NULL,
touchpoint_timestamp TIMESTAMPTZ NOT NULL,
date_key INTEGER NOT NULL, -- FK -> dim_date
channel_key INTEGER NOT NULL, -- FK -> dim_marketing_channel (Conformed)
campaign_key INTEGER NOT NULL, -- FK -> dim_campaign
user_id BIGINT, -- Anonymous or resolved user
spend_amount_usd NUMERIC(12, 4) DEFAULT 0.0000,
impressions INTEGER DEFAULT 0,
clicks INTEGER DEFAULT 0,
conversions INTEGER DEFAULT 0,
attributed_revenue_usd NUMERIC(12, 2) DEFAULT 0.00
);
-- ====================================================================
-- DELIVERABLE 2: attribution_analytics.sql
-- ====================================================================
-- Query 1: Cross-Channel ROAS & Blended CAC by Channel Group
-- TODO: Write query computing Return on Ad Spend (ROAS = Attributed Revenue / Spend) by Channel Group.
📊 Evaluation Rubric (100 Points Total)
| Evaluation Criterion | Weight | Target Standard (Pass ≥ 80%) |
|---|---|---|
| 1. Conformed Hierarchy Design | 25 pts | dim_marketing_channel establishes an unambiguous 3-tier hierarchy (channel_group -> channel_medium -> channel_source) that normalizes Google, Meta, TikTok, and GA4 data. |
| 2. Clean Ingestion & Mapping Logic | 25 pts | Demonstrates explicit staging mapping/lookup logic that classifies raw UTM parameters into conformed dimension keys with a designated Unmapped / Unknown fallback key. |
| 3. Fact Table Unification | 25 pts | fct_marketing_touchpoints successfully unions ad spend (top-of-funnel) and conversion events (bottom-of-funnel) while preserving granular grain. |
| 4. Verification & ROAS Calculation | 25 pts | Accompanying SQL computes Blended CAC and Channel Group ROAS without double-counting cross-device conversions. |
📤 Submission Instructions & Guidelines
- Deliverable Formats: Upload your completed
.sqlfile or a.ziparchive containing your SQL scripts and documentation (.md,.pdf, or ERD diagrams). - Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
- Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
- Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
- Confirmation: Click Submit Assignment. Your submission will be recorded, your lesson and module progress will be marked complete, and you can download your submitted work at any time.