You are given customer, order, and channel activity data from DISH digital touchpoints. Write a PostgreSQL query that compares customer behavior across customer segments and acquisition channels for Q1 2024. Return one row per segment and channel_name, including the number of customers in that segment-channel combination, how many placed at least one completed order in Q1, total completed order revenue, average completed order value, and the conversion rate as the share of customers with at least one completed order. Only include segment-channel groups with at least 2 customers, and sort the results by conversion rate descending, then total revenue descending.
customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer identifier |
| segment | VARCHAR(50) | Customer segment such as New, Existing, or At-Risk |
| signup_date | DATE | Date the customer signed up |
channel_events| column | type | description |
|---|---|---|
| event_id | INT | Unique event identifier |
| customer_id | INT | Customer tied to the channel touchpoint |
| channel_name | VARCHAR(50) | Acquisition or engagement channel |
| event_date | DATE | Date of the channel event |
orders| column | type | description |
|---|---|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Order date |
| order_status | VARCHAR(20) | Order status |
| order_amount | DECIMAL(10,2) | Order amount |
customers| customer_id | segment | signup_date |
|---|---|---|
| 1 | New | 2024-01-03 |
| 2 | Existing | 2023-11-10 |
| 3 | At-Risk | 2023-06-15 |
channel_events| event_id | customer_id | channel_name | event_date |
|---|---|---|---|
| 101 | 1 | MyDISH App | 2024-01-05 |
| 102 | 2 | dish.com | 2024-01-08 |
| 103 | 3 | Retail Store | 2024-02-11 |
orders| order_id | customer_id | order_date | order_status | order_amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-20 | completed | 120.00 |
| 1002 | 2 | 2024-02-14 | cancelled | 80.00 |
| 1003 | 3 | 2024-03-02 | completed | 60.00 |
| segment | channel_name | customer_count | converted_customers | total_revenue | avg_order_value | conversion_rate |
|---|---|---|---|---|---|---|
| Existing | dish.com | 2 | 2 | 350.00 | 116.67 | 1.0000 |
| New | MyDISH App | 2 | 1 | 120.00 | 120.00 | 0.5000 |
| At-Risk | Retail Store | 2 | 1 | 60.00 | 60.00 | 0.5000 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| segment | VARCHAR(50) | Customer segment classification |
| signup_date | DATE | Date the customer signed up |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique channel event identifier |
| customer_id | INT | Customer associated with the event |
| channel_name | VARCHAR(50) | Channel where the customer interacted |
| event_date | DATE | Date of the channel event |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(20) | Order lifecycle status |
| order_amount | DECIMAL(10,2) | Order amount |
| order_id | customer_id | order_date | order_status | order_amount |
|---|---|---|---|---|
| 1008 | 8 | 2024-02-22 | completed | 0 |
| 1001 | 1 | 2024-01-20 | completed | 120 |
| 1002 | 2 | 2024-02-14 | cancelled | 80 |
| 1003 | 2 | 2024-03-01 | completed | 150 |
| 1004 | 3 | 2024-03-02 | completed | 60 |
| 1005 | 4 | 2024-01-18 | completed | 90 |
| 1006 | 4 | 2024-03-25 | completed | 110 |
| 1007 | 5 | 2024-03-28 | pending | 75 |
| 1009 | 9 | 2024-01-15 | completed | 200 |
| 1010 | 10 | 2023-12-30 | completed | 55 |
| 1011 | 10 | 2024-02-05 | cancelled | |
| 1012 | 2024-03-10 | completed | 40 | |
| 1 | 36 | 2024-01-06 | completed | 361.05 |
| 2 | 76 | 2024-02-09 | draft | 997.09 |
| 3 | 5 | 2024-01-13 | expired | 186.24 |
| 4 | 3 | 2024-02-22 | fulfilled | 491.29 |
| 5 | 9 | 2024-01-16 | authorized | 216.64 |
| 6 | 43 | 2024-03-27 | completed | 579.80 |
| 7 | 34 | 2024-01-24 | expired | 916.61 |
| 8 | 34 | 2024-01-25 | reversed | 621.57 |
| 9 | 74 | 2024-03-25 | pending | 728.65 |
| 10 | 98 | 2024-02-02 | fulfilled | 131.48 |
| 11 | 35 | 2024-02-11 | failed | 839.51 |
| 12 | 11 | 2023-12-31 | failed | 463.38 |
| 13 | 39 | 2024-01-21 | fulfilled | 380.12 |
| 14 | 36 | 2024-02-21 | submitted | 459.77 |
| 15 | 10 | 2024-03-01 | submitted | 844.35 |
| 16 | 60 | 2024-01-31 | awaiting_payment | 819.43 |
| 17 | 61 | 2024-01-22 | refunded | 775.51 |
| customer_id | segment | signup_date |
|---|---|---|
| 4 | Existing | 2023-09-20 |
| 1 | New | 2024-01-03 |
| 7 | At-Risk | 2023-04-01 |
| 2 | Existing | 2023-11-10 |
| 9 | Existing | 2024-02-20 |
| 3 | At-Risk | 2023-06-15 |
| 6 | New | 2024-03-10 |
| 8 | 2024-01-25 | |
| 5 | New | 2024-02-05 |
| 10 | Existing | 2023-08-08 |
| 1 | Existing | 2023-12-06 |
| 2 | Promo Eligible | 2023-06-10 |
| 3 | Early Lifecycle | 2023-08-13 |
| 4 | Win-Back | 2023-12-21 |
| 5 | Win-Back | 2023-04-23 |
| 6 | Existing | 2023-08-23 |
| 7 | Family | 2024-02-25 |
| 8 | Promo Eligible | 2023-09-24 |
| 9 | Loyal | 2023-09-14 |
| 10 | Recently Upgraded | 2023-10-14 |
| 11 | Promo Eligible | 2023-08-25 |
| 12 | Urban | 2024-03-02 |
| 13 | At-Risk | 2024-01-16 |
| 14 | Satellite-Only | 2023-11-13 |
| 15 | Urban | 2023-06-21 |
| 16 | Streaming-First | 2023-08-25 |
| event_id | customer_id | channel_name | event_date |
|---|---|---|---|
| 110 | 10 | Call Center | 2024-01-12 |
| 101 | 1 | MyDISH App | 2024-01-05 |
| 111 | 1 | MyDISH App | 2024-02-01 |
| 102 | 2 | dish.com | 2024-01-08 |
| 103 | 3 | Retail Store | 2024-02-11 |
| 104 | 4 | dish.com | 2024-03-03 |
| 105 | 5 | MyDISH App | 2024-03-15 |
| 106 | 6 | 2024-03-20 | |
| 107 | 7 | Retail Store | 2024-01-30 |
| 108 | 8 | 2024-02-10 | |
| 109 | 9 | dish.com | 2023-12-28 |
| 112 | MyDISH App | 2024-02-18 | |
| 1 | 67 | null | 2024-02-23 |
| 2 | 91 | Affiliate | 2024-02-19 |
| 3 | 97 | Social | 2023-12-31 |
| 4 | 34 | null | 2024-02-26 |
| 5 | 84 | Retail Store | 2024-02-28 |
| 6 | 75 | Paid Search | 2024-03-20 |
| 7 | 90 | YouTube | 2024-02-21 |
| 8 | 46 | Retail Store | 2024-01-31 |
| 9 | 14 | null | 2024-03-22 |
| 10 | 71 | YouTube | 2024-02-08 |
| 11 | 2 | Outbound Sales | 2023-12-26 |
| 12 | 77 | MyDISH App | 2024-03-11 |
| 13 | 42 | Dealer | 2024-02-29 |
| 14 | 4 | Xfinity Switch Campaign | 2024-01-20 |
| 15 | 46 | 2024-02-25 |
| segment | channel_name | customer_count | converted_customers | total_revenue | avg_order_value | conversion_rate |
|---|---|---|---|---|---|---|
| Existing | dish.com | 2 | 2 | 350.00 | 116.67 | 1.0000 |
| New | MyDISH App | 2 | 1 | 120.00 | 120.00 | 0.5000 |
| At-Risk | Retail Store | 2 | 1 | 60.00 | 60.00 | 0.5000 |
