A portfolio analytics project that models a real problem I've worked on as a deliverability / trust & safety engineer: an email SaaS suffers a mid-year deliverability incident, and a minority of abusive accounts quietly degrades the whole platform's sending reputation. The project generates a realistic, internally consistent dataset, ships a star-schema data model, a SQL analysis layer, and the DAX that powers a Power BI dashboard.
The headline finding: a deliverability incident in June–July 2024 drops the platform delivery rate from ~96% to ~92%, while spam complaints, blacklist hits, and Deliverability support tickets all spike in the same window — then recover through August. A cluster of ~19% abusive accounts (concentrated on the Free tier) is the underlying driver. Healthy senders deliver at ~99%; the abusive tail is what pulls the blended platform rate down to ~96%.
Power BI Desktop report. The June–July delivery dip, the complaint/blacklist/ticket spikes, and the Free-tier abuse cluster are all visible on one page.
The report is built from four visuals:
- Delivery-rate trend line (by month) — shows the incident dip and recovery.
- Abuse scatter — hard-bounce rate vs complaint rate per account; the abusive cluster sits in the top-right, colored by risk level.
- Plan donut — account distribution across Free / Starter / Pro / Enterprise.
- Ticket stacked bar (by month, by category) — the Deliverability surge in June–July.
Plus KPI cards (Delivery Rate %, Complaint Rate %, High-Risk %, Churn Rate %, MoM Delivery Change) and slicers (date, plan tier, risk level, incident period).
Email platforms live and die by deliverability — the share of mail that actually reaches the inbox instead of bouncing or getting filtered. Deliverability is a shared resource: because senders often share IP pools and the platform's overall reputation, a small number of abusive senders (spammers, compromised accounts, poor list hygiene) can drag down delivery for everyone. When reputation slips, mailbox providers start deferring and bouncing mail, blacklists light up, and support gets flooded.
This project asks the questions a Trust & Safety / Deliverability / Fraud analyst is paid to answer:
- Is delivery healthy over time, and if not, when did it break and how badly?
- Who are the abusive accounts, and what signals identify them?
- Does the business shape (plan tiers) explain where abuse concentrates?
- Do the operational signals (complaints, blacklists, tickets) corroborate each other?
email-deliverability-abuse-intelligence/
├── generate_data.py # Seeded, modular data generator + validation
├── data/ # CSV exports (7 tables) — produced by the script
│ ├── dim_date.csv
│ ├── dim_account.csv
│ ├── dim_domain.csv
│ ├── fact_email_events.csv
│ ├── fact_spam_complaints.csv
│ ├── fact_support_tickets.csv
│ └── fact_domain_reputation.csv
├── sql/
│ ├── load.sql # Build the DuckDB database from the CSVs
│ └── queries.sql # 8 analytical queries (the SQL skill showcase)
├── dax_measures.md # Power BI measures, explained, ready to paste
├── requirements.txt
├── .gitignore
├── LICENSE
└── README.md
1. Generate the data (Python 3.10+):
pip install -r requirements.txt
python generate_data.pyThis writes the seven CSVs to data/ and prints a validation summary that confirms
the incident is visible and the tables reconcile. It is fully reproducible (seed = 42).
2. Load the SQL layer (optional but recommended — DuckDB):
duckdb deliverability.duckdb < sql/load.sqlThen run the analysis queries:
duckdb deliverability.duckdb
D .read sql/queries.sql3. Build the dashboard — open Power BI Desktop, load the CSVs from data/, wire up
the relationships and measures per dax_measures.md, and build the
four visuals.
Three dimensions, four facts. Facts are at different grains (event, complaint, ticket,
monthly snapshot) but all share the dim_date and dim_account conformed dimensions.
┌───────────────┐
│ dim_date │
└───────┬───────┘
│ (date_id)
┌───────────────┐ ┌───────┴────────────────┐ ┌────────────────────┐
│ dim_account ├────┤ fact_email_events ├────┤ dim_domain │
│ (account_id) │ │ (send-level grain) │ │ (domain_id) │
└──────┬────────┘ └────────────────────────┘ └─────────┬──────────┘
│ │
├───────► fact_spam_complaints │
├───────► fact_support_tickets │
│ │
└───────────────────► fact_domain_reputation ◄────────┘
(monthly snapshot)
- Star schema, not flat files — separating dimensions from facts is what makes the model queryable in SQL and sliceable in Power BI without re-joining strings everywhere.
fact_email_eventsat send grain — the base fact. Every complaint, every monthly rate, every risk score reconciles back to individual sends. If youSUMthe events, you get the aggregates; nothing is pre-baked in a way that could contradict the detail.- Domain reputation as a monthly snapshot fact, not a
dim_domaincolumn — reputation drifts over time, so it's a measure, not a static attribute.dim_domainholds the things that don't change (name, category, baseline);fact_domain_reputationholds the monthly reputation score and blacklist counts. This is the textbook way to model a slowly-changing metric and keeps the dimension clean. risk_levelis derived, not assigned — see Methodology.
| Column | Type | Description |
|---|---|---|
date_id |
int | Surrogate key, YYYYMMDD. Join key for all facts. |
date |
date | Calendar date. |
year |
int | Year (2024). |
month |
int | Month number 1–12. |
month_name |
text | Month name (January…). |
quarter |
int | Calendar quarter 1–4. |
day_of_week |
text | Weekday name. |
is_weekend |
bool | True for Saturday/Sunday. |
is_incident_period |
bool | True for June & July 2024 (the incident window). |
| Column | Type | Description |
|---|---|---|
account_id |
int | Primary key. |
plan_tier |
text | Free / Starter / Pro / Enterprise. |
signup_date |
date | When the account was created (2022-06 → 2024-09). |
industry |
text | Customer's industry vertical. |
is_abusive |
bool | Ground-truth abuse flag (the hidden driver behind bad signals). |
churn_status |
text | Active / Churned. |
churn_date |
date | Date of churn/ban (null if Active). |
risk_level |
text | Derived Low / Medium / High from realized bounce + complaint rates. |
| Column | Type | Description |
|---|---|---|
domain_id |
int | Primary key. |
account_id |
int | Owning account (FK → dim_account). |
domain_name |
text | Sending domain. |
domain_category |
text | Transactional / Marketing / Newsletter / Notifications / Mixed. |
baseline_reputation |
float | Starting reputation score 0–100 (abusive senders start lower). |
| Column | Type | Description |
|---|---|---|
event_id |
int | Primary key. |
event_timestamp |
datetime | When the send was attempted. |
date_id |
int | FK → dim_date. |
account_id |
int | FK → dim_account. |
domain_id |
int | FK → dim_domain. |
outcome |
text | delivered / soft_bounce / hard_bounce / deferred. |
smtp_code |
int | SMTP reply code (250, 421, 450, 550, 554 …). |
smtp_enhanced_code |
text | Enhanced status code (2.0.0, 4.2.2, 5.1.1 …). |
recipient_provider |
text | Recipient mailbox provider (Gmail / Yahoo / Microsoft / Other). |
| Column | Type | Description |
|---|---|---|
complaint_id |
int | Primary key. |
complaint_timestamp |
datetime | When the complaint was received (0–72h after send). |
date_id |
int | FK → dim_date. |
account_id |
int | FK → dim_account. |
domain_id |
int | FK → dim_domain. |
feedback_loop_provider |
text | FBL source (Gmail / Yahoo / Microsoft / AOL). |
complaint_type |
text | abuse / fraud / unsubscribe-as-complaint / not-spam-reversal. |
Every complaint is generated as a probabilistic subset of that account's delivered events, so complaints always reconcile with the event table.
| Column | Type | Description |
|---|---|---|
ticket_id |
int | Primary key. |
created_timestamp |
datetime | When the ticket was opened. |
date_id |
int | FK → dim_date. |
account_id |
int | FK → dim_account. |
category |
text | Deliverability / Billing / Technical / Onboarding / Abuse. |
priority |
text | Low / Medium / High / Urgent. |
status |
text | Open / Pending / Resolved / Closed. |
resolution_hours |
float | Hours to resolve (null while unresolved). |
| Column | Type | Description |
|---|---|---|
domain_id |
int | FK → dim_domain. |
date_id |
int | First of month, YYYYMM01 (FK → dim_date). |
snapshot_month |
text | YYYY-MM label. |
reputation_score |
float | Domain reputation 0–100 that month (drops in the incident). |
blacklist_count |
int | Blacklist hits that month (spikes in the incident). |
(Exact row counts print when you run the generator; the ranges above reflect
seed = 42.)
The data is driver-based, not random. A few shared drivers feed every table so the story stays consistent across the whole model.
A single function, incident_delivery_penalty(), lowers the per-send delivery
probability platform-wide during the window (ramp in June, peak ~4 points in July,
recovery through August). The same window drives:
- higher spam-complaint rates (a complaint-rate multiplier peaking ×6 in July),
- lower domain reputation and more blacklist hits (in
fact_domain_reputation), - a surge of Deliverability-category support tickets.
Because these all key off calendar month, the four operational signals move together — which is exactly what a real incident looks like, and what makes the finding credible.
~19% of accounts carry is_abusive = True, drawn with a tier-dependent probability
(Free 25% → Enterprise 2%). Abusive accounts sample from a much worse outcome
distribution (hard-bounce ~11% vs ~0.3% healthy) and a higher complaint rate, and — like
real spammers — they blast ~2× the volume of a normal account on their tier. The
extra volume also makes each abusive account's rates statistically stable, so on the
bounce-vs-complaint scatter they separate cleanly into a top-right cluster. Because
risk_level is derived from those realized rates, the High-risk population it recovers
(~19%) lands right on top of the hidden is_abusive label — the classifier reconstructs
the ground truth.
Plan mix is Free-heavy (65% / 20% / 12% / 3%), and because abuse probability is highest on Free, the Free tier carries most of the abuse and the worst delivery — as real freemium products do.
risk_level is not assigned by hand. After the events exist, each account's realized
hard-bounce rate and complaint rate are computed from the fact tables and mapped:
| Condition | Risk |
|---|---|
| hard-bounce rate > 10% or complaint rate > 1.0% | High |
| hard-bounce rate > 5% or complaint rate > 0.3% | Medium |
| otherwise | Low |
The generator's validation step recomputes these rates from the events, so the risk column is a transparent function of the data — I can whiteboard the exact rule.
- Day-of-week seasonality (business mail peaks midweek, collapses on weekends) and mild monthly seasonality (summer lull, Q4 ramp).
- Volume scales by plan tier (Enterprise sends ~20× a Free account) and by how long the account has been active.
- SMTP reply codes map to outcomes the way real mail servers behave (250 delivered, 4xx soft/deferred, 5xx hard) with matching enhanced status codes.
- Churn is higher for abusive accounts (bans), and reputation starts lower for them.
generate_data.py finishes by asserting the story is present and printing a summary:
row counts, referential integrity (zero orphan foreign keys), no unexpected nulls,
the monthly delivery-rate curve (with an assertion that July is ≥2 points below the
Jan–May baseline), the complaint-rate spike, blacklist hits by month, Deliverability
tickets by month, and the abuse/plan mix. If the incident ever fails to show up, the
script errors — the dataset can't silently drift away from its own story.
- Domain expertise: SMTP outcomes, hard vs soft bounces, feedback loops, blacklists, sender reputation, and how abuse propagates through a shared platform.
- Data modeling: a proper star schema with conformed dimensions and a snapshot fact.
- Python: modular, seeded, vectorized data generation with built-in validation.
- SQL (DuckDB): window functions (
LAG),FILTERaggregates, CTEs, ranking. - DAX / Power BI: time-intelligence measures and a defensible semantic model.
- Analytical storytelling: one coherent finding corroborated across four signals.
I used Claude Code to scaffold the data pipeline and draft the SQL and DAX. I directed the design (the incident shape, the abuse model, the star schema, the risk thresholds), reviewed and adjusted the generated code and queries, and did the data modeling and dashboard build in Power BI myself. The AI accelerated the boilerplate; the analytical decisions, the interpretation, and the final report are mine.