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 |