Attribution data joins with send data to provide full-funnel view

Without a join that connects sends, opens, clicks, and conversions in a single query, every funnel question becomes a manual spreadsheet exercise — analysts export CSVs from three tables, paste them together, and produce metrics that drift between reports. Revenue-per-email-sent, send-to-conversion rate, and cost-per-acquisition cannot be computed reliably, which blocks executive reporting, A/B test readout, and the send-cadence decisions that depend on knowing which campaigns actually generate incremental revenue per delivered message.

Create a campaign_funnel SQL view that left-joins email_sends to email_events and conversions, aggregates unique opens (filtering machine_open = false), clicks, conversions, and summed revenue per campaign, then exposes open_rate_pct and conversion_rate_pct as computed columns. Keep the view in supabase/migrations/ alongside the tables it depends on.

CREATE VIEW campaign_funnel AS SELECT s.campaign_id, COUNT(DISTINCT s.id) AS sends FROM email_sends s LEFT JOIN email_events e ON e.campaign_id = s.campaign_id LEFT JOIN conversions cv ON cv.utm_campaign = c.slug GROUP BY s.campaign_id;