Dupple observed newsletter issue counts per deal, read 5 November 2026.

The source is Sponsors operational data. A run is a distinct publication and
calendar date with a Main Ad, Spotlight Ad or Dedicated Send slot linked to a
deal and currently marked "Performance recap sent". Runs without a deal, other
formats and other statuses are excluded. For each deal, the query takes its first
observed qualifying run from 1 January through 5 October 2026, then counts its
qualifying distinct publication/date pairs during that date and the next 29 days.
The latest source date is 4 November 2026, giving every included first run a full
30-day calendar window. An issue with both Main and Spotlight for the same deal
and publication on the same date counts once. Runs in different newsletters on
the same date count separately. The first observed run can follow earlier activity
outside this window; missing deal links or statuses can omit runs. A deal is not
necessarily a first-time advertiser or an independent customer. Counts do not
measure results, causal effects, audience overlap or a recommended test size.
Only bucketed aggregate counts are exported, with no deal or customer identifiers.

BEGIN READ ONLY;
COPY (
 WITH runs AS (
  SELECT DISTINCT s."dealId" AS deal_id, s."publicationId" AS publication_id,
         s.date::date AS run_date
  FROM "Slot" s JOIN "Placement" p ON p.id = s."placementId"
  JOIN "Status" st ON st.id = s."statusId"
  WHERE s."dealId" IS NOT NULL
    AND p.name IN ('Main Ad', 'Spotlight Ad', 'Dedicated Send')
    AND st.name = 'Performance recap sent'
    AND s.date >= DATE '2026-01-01' AND s.date < DATE '2026-11-05'
 ), starts AS (
  SELECT deal_id, MIN(run_date) AS first_run FROM runs GROUP BY deal_id
  HAVING MIN(run_date) < DATE '2026-10-06'
 ), counts AS (
  SELECT a.deal_id, COUNT(*) AS issues FROM starts a JOIN runs r
    ON r.deal_id = a.deal_id AND r.run_date >= a.first_run AND r.run_date < a.first_run + 30
  GROUP BY a.deal_id
 )
 SELECT CASE WHEN issues >= 4 THEN '4+' ELSE issues::text END AS issue_bucket,
        COUNT(*) AS deals FROM counts GROUP BY 1 ORDER BY 1
) TO STDOUT WITH CSV HEADER;
COMMIT;