Dupple newsletter engagement and quiz roles: aggregation method
Snapshot extracted 5 November 2026. This script receives a SQLite path and publication name, reads the database without writing, and emits aggregate role counts only.

#!/usr/bin/env python3
"""Aggregate self-reported roles in selected newsletter engagement records."""
import argparse,csv,sqlite3,sys
from pathlib import Path
from datetime import datetime,timezone
p=argparse.ArgumentParser();p.add_argument('--db',required=True);p.add_argument('--publication',required=True,choices=['techpresso','aipresso','devshot','cyberpresso','finpresso','marketingshot']);a=p.parse_args()
start=int(datetime(2026,10,29,tzinfo=timezone.utc).timestamp()*1000);end=int(datetime(2026,11,5,tzinfo=timezone.utc).timestamp()*1000)
c=sqlite3.connect(Path(a.db).resolve().as_uri()+'?mode=ro',uri=True);c.execute('BEGIN')
q='''WITH quiz AS (SELECT LOWER(TRIM(email)) email, COALESCE(NULLIF(TRIM(role),''),'UNKNOWN') role, ROW_NUMBER() OVER(PARTITION BY LOWER(TRIM(email)) ORDER BY createdAt DESC,id DESC) rn FROM QuizResponse WHERE email IS NOT NULL AND TRIM(email)<>''), engaged AS (SELECT LOWER(TRIM(email)) email FROM SubscriberEngagement WHERE publication=? AND engagedSeenAt>=? AND engagedSeenAt<? AND email IS NOT NULL AND TRIM(email)<>'' GROUP BY LOWER(TRIM(email))) SELECT COALESCE(q.role,'NO_QUIZ') role,COUNT(*) FROM engaged e LEFT JOIN quiz q ON e.email=q.email AND q.rn=1 GROUP BY 1 ORDER BY 2 DESC,1'''
rows=c.execute(q,(a.publication,start,end)).fetchall();c.close(); unmatched=sum(n for role,n in rows if role=='NO_QUIZ');matched=sum(n for role,n in rows if role!='NO_QUIZ');eligible=matched+unmatched
if matched<100:raise SystemExit('Fewer than 100 matched records')
w=csv.writer(sys.stdout,lineterminator='\n');w.writerow(['publication','role','matched_respondents','share_matched_pct','eligible_resolved_emails','matched_total','unmatched_total'])
small=0
for role,n in rows:
 if role=='NO_QUIZ':continue
 if n<30:small+=n;continue
 w.writerow([a.publication,role,n,f'{100*n/matched:.1f}',eligible,matched,unmatched])
if small:w.writerow([a.publication,'OTHER_SMALL_GROUPS',small,f'{100*small/matched:.1f}',eligible,matched,unmatched])