Dupple offer placements: aggregation method
Snapshot extracted 5 November 2026. The script reads a SQLite database supplied through --db and emits aggregate event counts only.

#!/usr/bin/env python3
"""Aggregate offer impression and click logs by surface and recorded rank."""
import argparse,csv,sqlite3,sys
from pathlib import Path
from datetime import datetime,timezone
p=argparse.ArgumentParser();p.add_argument('--db',required=True);args=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(args.db).resolve().as_uri()+'?mode=ro',uri=True)
c.execute('BEGIN')
def groups(table):
 q=f'''SELECT source, CASE WHEN position BETWEEN 1 AND 10 THEN CAST(position AS TEXT) WHEN position IS NULL THEN 'unknown' ELSE 'other' END, COUNT(*) FROM {table} WHERE createdAt>=? AND createdAt<? AND source IN ('quiz_result','us_interstitial') GROUP BY 1,2'''
 return {(source,pos):n for source,pos,n in c.execute(q,(start,end))}
impressions=groups('OfferImpression'); clicks=groups('OfferClick');c.close()
w=csv.writer(sys.stdout,lineterminator='\n');w.writerow(['surface','position','impressions','clicks','clicks_per_100_impressions'])
for source,pos in [('quiz_result',str(i)) for i in range(1,11)]+[('quiz_result','other'),('quiz_result','unknown'),('us_interstitial','1'),('us_interstitial','other'),('us_interstitial','unknown')]:
 v=impressions.get((source,pos),0); n=clicks.get((source,pos),0)
 w.writerow([source,pos,v,n,f'{100*n/v:.1f}' if v else ''])