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

- **Pattern:** `ab-000593` (`campaign-analytics-attribution.attribution-conversion.attribution-full-funnel`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000593
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000593)

## Why it matters

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.

## Severity rationale

Low because the underlying events are captured; only the reporting join layer is missing and can be added without data backfill.

## Remediation

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.

```sql
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;
```

## Detection

- **ID:** `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:

  ```sql
  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;
  ```

---

Taxons: observability

HTML version: https://auditbuffet.com/patterns/ab-000593