SQL Conversion Rate by Channel

MediumSQL · PostgreSQL

Problem

L.L.Bean marketing analysts need daily conversion reporting for campaign channels such as email, SMS, paid search, and social media. A customer is considered converted when they have a completed order on the same date as a delivered campaign touch.

Write a PostgreSQL query that calculates campaign conversion rate by touch date and channel for March 1 through March 3, 2026.

Requirements

Include every delivered campaign channel and date, including groups with zero completed orders.
Count distinct reached customers and distinct converted customers.
Calculate conversion_rate as converted customers divided by reached customers, expressed as a percentage rounded to two decimals.
Display missing channel values as Unassigned, and sort by date followed by channel.

Schema

campaign_touches

Column	Type	Description
touch_idPK	INT	Unique identifier for a campaign touch
customer_id	INT	Customer who received the touch
touch_date	DATE	Date the campaign touch was sent
channel	VARCHAR(40)	Marketing channel used for the touch
campaign_name	VARCHAR(100)	Name of the L.L.Bean campaign
delivered	BOOLEAN	Whether the touch was successfully delivered

orders

Column	Type	Description
order_idPK	INT	Unique identifier for an order
customer_id	INT	Customer who placed the order
order_date	DATE	Date the order was placed
order_status	VARCHAR(20)	Current order status
order_total	NUMERIC(10,2)	Total value of the order

Tablescampaign_touchesorders