Dupple issue-metric pairing audit, read-only extract on 5 November 2026.

The Sponsors metric sync matches a publication's ESP campaigns by calendar date and
writes those campaign-level sums to each matching slot. The query below checks pairs
of Techpresso Main Ad and Spotlight Ad slots on the same date when both have a
non-null unique-click count, for runs from 1 January through 4 November 2026.
It compares the stored unique-click and send fields, and outputs aggregate counts
and boundary dates only. It contains no advertiser names, slot identifiers,
individual links, email addresses, prices or deal values. It does not measure
ad-specific clicks, every publication, the completeness of a report, or GA4 data.

BEGIN READ ONLY;
COPY (
 SELECT COUNT(*) AS matched_slot_pairs,
        COUNT(DISTINCT x.date::date) AS matched_dates,
        COUNT(*) FILTER (WHERE a."uniqueClicks" = b."uniqueClicks") AS identical_unique_click_counts,
        COUNT(*) FILTER (WHERE a."totalSends" = b."totalSends") AS identical_send_counts,
        MIN(x.date)::date AS first_date, MAX(x.date)::date AS last_date
 FROM "Slot" x
 JOIN "SlotMetrics" a ON a."slotId" = x.id
 JOIN "Placement" px ON px.id = x."placementId"
 JOIN "Publication" pub ON pub.id = x."publicationId"
 JOIN "Slot" y ON y."publicationId" = x."publicationId" AND y.date = x.date
 JOIN "Placement" py ON py.id = y."placementId"
 JOIN "SlotMetrics" b ON b."slotId" = y.id
 WHERE pub.name = 'Techpresso' AND px.name = 'Main Ad' AND py.name = 'Spotlight Ad'
   AND x.date >= DATE '2026-01-01' AND x.date < DATE '2026-11-05'
   AND a."uniqueClicks" IS NOT NULL AND b."uniqueClicks" IS NOT NULL
) TO STDOUT WITH CSV HEADER;
COMMIT;