Your question is Unify CRM and Google Analytics View. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You’re a data engineer at a fast-growing e-commerce retailer (~3M monthly active users) that sells direct-to-consumer. Marketing spends heavily on paid search and email, but the company can’t reliably connect anonymous web behavior (Google Analytics-style events) to known customers (CRM) once they purchase or log in. This leads to mis-attributed CAC/LTV, broken retargeting audiences, and inconsistent executive reporting.
Your warehouse receives:
ga_client_id (cookie/device identifier).ga_client_id to customer_id when a user logs in or completes checkout.Because users can browse anonymously on multiple devices, a single customer may map to multiple ga_client_ids over time. You want a single customer view that rolls up web behavior and revenue.
Write a SQL query that produces a unified customer-level dataset for January 2025.
customer_id.customer_id, emailfirst_touch_channel: the channel of the customer’s earliest known web event (across all mapped GA client IDs)sessions_jan_2025: distinct session count in Jan 2025pageviews_jan_2025: total page_view events in Jan 2025orders_jan_2025: number of orders placed in Jan 2025revenue_jan_2025: sum of order totals in Jan 2025event_ts; if ties, choose lexicographically smallest channel.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | CRM customer primary key |
| VARCHAR(255) | Customer email address | |
| created_at | TIMESTAMP | Timestamp when the customer record was created |
| Column | Type | Description |
|---|---|---|
| order_idPK | BIGINT | Order primary key |
| customer_id | INT | Customer placing the order (FK to crm_customers) |
| order_ts | TIMESTAMP | Order timestamp |
| order_total | DECIMAL(10,2) | Total charged amount for the order |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Event primary key |
| ga_client_id | VARCHAR(64) | GA client/cookie identifier |
| session_id | VARCHAR(64) | Session identifier |
| event_ts | TIMESTAMP | Event timestamp |
| event_name | VARCHAR(50) | Event type (e.g., page_view) |
| channel | VARCHAR(50) | Marketing channel attribution for the event |
| Column | Type | Description |
|---|---|---|
| ga_client_idPK | VARCHAR(64) | GA client identifier (one side of the crosswalk) |
| customer_id | INT | Mapped CRM customer_id |
| first_seen_ts | TIMESTAMP | First time this mapping was observed |