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;

• ID: campaign-analytics-attribution.attribution-conversion.attribution-full-funnel

• Severity: low

• What to look for: Examine reporting queries or database views that compute campaign performance metrics. Check whether a single query or view can join: the email send table (how many emails were sent), the open event table (how many unique contacts opened), the click event table (how many unique contacts clicked), and the conversion table (how many contacts converted and what total value was generated). This full-funnel view is what enables computing send-to-conversion rate, revenue-per-email-sent, and cost-per-acquisition from email campaigns.

• Pass criteria: A query, database view, or reporting model exists that joins sends → opens → clicks → conversions into a cohesive funnel view for each campaign. The join must cover at least 4 stages (sends, opens, clicks, conversions). Count all funnel stages represented in the view.

• Fail criteria: No join query or view exists that connects sends to conversions. Attribution and send data are analyzed separately with no unified funnel report.

• Skip (N/A) when: The project does not store send and conversion data locally (relies entirely on ESP reporting).

• Detail on fail: Example: "No query or view joins sends, opens, clicks, and conversions — funnel metrics cannot be computed without manual joining" or "send_analytics and conversion tables never joined in any query file"

• Remediation: Create a campaign funnel view:

  CREATE VIEW campaign_funnel AS
  SELECT
    s.campaign_id,
    c.name AS campaign_name,
    COUNT(DISTINCT s.id)                                          AS sends,
    COUNT(DISTINCT CASE WHEN e.event_type = 'open'  AND NOT e.machine_open THEN e.contact_id END) AS unique_opens,
    COUNT(DISTINCT CASE WHEN e.event_type = 'click' THEN e.contact_id END) AS unique_clicks,
    COUNT(DISTINCT cv.id)                                         AS conversions,
    COALESCE(SUM(cv.value), 0)                                    AS revenue,
    ROUND(COUNT(DISTINCT CASE WHEN e.event_type = 'open' AND NOT e.machine_open THEN e.contact_id END)::numeric
          / NULLIF(COUNT(DISTINCT s.id), 0) * 100, 2)            AS open_rate_pct,
    ROUND(COUNT(DISTINCT cv.id)::numeric
          / NULLIF(COUNT(DISTINCT s.id), 0) * 100, 4)            AS conversion_rate_pct
  FROM email_sends s
  LEFT JOIN campaigns c ON c.id = s.campaign_id
  LEFT JOIN email_events e ON e.campaign_id = s.campaign_id
  LEFT JOIN conversions cv ON cv.utm_campaign = c.slug
    AND cv.converted_at BETWEEN s.sent_at AND s.sent_at + INTERVAL '30 days'
  GROUP BY s.campaign_id, c.name;