# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Project Overview

This is a data analytics project for **Fansifter email campaign SLA analysis**. It contains analytical documentation and SQL queries — not a traditional software project with a build system, tests, or package manager.

- **Platform**: Sigma Computing (BI/analytics tool)
- **Data Warehouse**: Snowflake
- **Content**: Markdown documentation with embedded Snowflake SQL queries
- **Workbook**: [Fansifter Email Campaigns](https://app.sigmacomputing.com/sony-music-entertainment/workbook/Fansifter-Email-Campaigns-74kyWEJw5iRB4PtWYeMAQh) (ID: `e84b267a-6a1b-46be-9fbb-65279698d819`)

## File Structure

- `sla_analysis.md` — SLA metric definitions, methodology, results, and all SQL queries for execution in Sigma Computing custom SQL elements.
- `growth.md` — Email volume growth analysis by business unit (SME and The Orchard). Monthly sending volume, campaign counts, delivery events, and engagement metrics. Last updated 2026-02-22.
- `ui/` — Microsoft Deliverability Dashboard (React + Vite + MUI). See [UI Dashboard](#ui-dashboard) section below.

## SQL Conventions

All queries target Snowflake and are designed to run as Sigma Computing custom SQL elements.

- **Primary database**: `FANSIFTER_APP_REPORTING` (schemas: `PROD`, `DEV_DURUKOV`)
- **Secondary database**: `DELPHI_CRM_DATA` (schema: `RAW_SALESFORCE_MARKETING_CLOUD`)
- **Key event source**: `FANSIFTER_APP_REPORTING.PROD.SENDGRID_WEBHOOK_GENERAL` — SendGrid webhook events
- **Statistical functions**: Uses `PERCENTILE_CONT` for percentile calculations (P50/P90/P95/P99)
- **Active window filter**: Minutes with >= 100 deliveries (filters ramp-up/cool-down noise)
- **Event type**: Always use `delivered` (not `processed`) for SLA metrics — measures actual delivery to recipient mail servers
- **Scope filter**: `CAMPAIGN_ID IS NOT NULL AND NOT IS_TEST` to exclude test sends

## SLA Metric Definitions

Three levels of SLA analysis exist in this project:

1. **System-level P95**: Weekly aggregate P95 emails delivered per minute across all campaigns
2. **Per-campaign SLA**: Time to deliver 95% of a campaign's emails vs. expected time at target rate (100K or 90K/min). Uses 95th percentile (not 100%) because the last 5% trickles in due to ISP throttling/retries.
3. **Per-ISP SLA**: Per-campaign-per-ISP compliance using `EMAIL_DOMAIN_TO_ISP_MAPPING` to classify deliveries by recipient email provider

## Timestamps

- `SEND_AT` in `EMAIL_CAMPAIGN` is stored in **UTC**. The `SEND_AT_TIMEZONE` column is display metadata only.
- SendGrid webhook timestamps (`SENDGRID_WEBHOOK_GENERAL.TIMESTAMP`) are in **UTC**.
- The Snowflake session timezone may display as Pacific (-08:00) on `CURRENT_TIMESTAMP()`, but `TIMESTAMP_NTZ` values in tables are UTC.

## Key Domain Context

- A ~20x throughput increase occurred around Jan 11-18, 2026 — queries scoped to post-scaling use `>= '2026-01-18'`
- SLA misses are primarily driven by **ISP-level throttling** (especially UK/EU/ANZ providers), not infrastructure capacity
- Comcast, Orange.fr, and German ISPs (t-online.de, freenet.de, vodafone.de) have 0% SLA compliance due to aggressive rate limiting
- US large campaigns (1M+) deliver at 140-163K/min and consistently meet SLA
- Outlook.com uses a **burst/throttle cycling pattern** — queues mail then releases in batches of 150-280K, rather than steady rate limiting
- iCloud throttling is **binary** — accepts mail initially, then fully blocks for hours
- Deferral counts inflate due to retries and are not a reliable indicator of unique undelivered emails

## Microsoft Reputation Recovery (Feb 2026)

Per Twilio SendGrid weekly report (Denis O'Sullivan, Feb 17, 2026):

- **Microsoft SNDS IP reputation reached 'high'** for both subusers:
  - `fansifter-sme`: First 'high' since October 2025
  - `fansifter-theorchard`: First 'high' since June 2025
- **Inbox Monster seedlist results** showed dramatic inboxing improvement:
  - `e.sonymusicfans.com`: 0% → 99%+ inboxing at Microsoft
  - `fans.theorchard.com`: 0% → 40%+ inboxing at Microsoft
- **The Orchard** had a Microsoft block spike on Feb 13 — resolved same-day via ticket, 99%+ delivery restored next day
- **Caveat from SendGrid**: "This by no means we are in the clear" — need consistent volume, avoid spikes, and mail engaged recipients. Microsoft reacts quickly to old/inactive subscriber lists.
- **Engagement trends (week of Feb 16)**:
  - Sony Music Fans: Open rate (ex-MPP) 7.00% → 7.37%, Gmail opens 10.83% → 13.60%, clicks 1.83% → 3.37%
  - The Orchard: Open rate (ex-MPP) 5.55% → 5.94%, clicks 0.67% → 0.94%
- Two subject lines flagged for elevated complaint rates

This improvement may reduce Outlook.com's burst/throttle cycling behavior and improve per-ISP SLA compliance (previously 47.3% MET). Queries scoped to post-Feb 5 (`>= '2026-02-05'`) can isolate the impact of this reputation change.

## Harry Styles "PopUps" Send Incident (March 4, 2026)

4 regional campaigns (NA/UK/EU_IE/ANZ) fired simultaneously at ~20:37 UTC, totaling 1.54M recipients with 730K Microsoft-destined emails.

- **118K Microsoft deferral events** — 99.9% caused by SendGrid hitting ISP-suggested max connection limits (SendGrid-imposed throttle, not Microsoft rejection)
- **100% of Microsoft mail delivered within 30 minutes**; 99.5% within 15 min
- **UK campaign hit hardest**: 90K of 118K deferrals (77%), but 100% delivered within 10 min
- **NA campaign slowest**: 0% at 5 min → 29.6% at 10 min → 98.9% at 15 min → 100% at 30 min (last to fire, connection pool already saturated)
- Remaining deferrals: TCP i/o timeouts and `451 4.3.2 Temporary server error` from Outlook servers
- No mail lost — all 730K processed = delivered
- **Mitigation**: Staggering regional campaigns by 5-10 min would reduce Microsoft connection saturation

## UI Dashboard

A live Microsoft Deliverability Dashboard under `ui/` that queries Snowflake directly via browser-based OAuth. Built to replace ad-hoc SQL queries for weekly Microsoft deliverability analysis.

### Tech Stack

- **React 19** + **TypeScript** + **Vite 7** (port `5174`)
- **MUI 7** (`@mui/material`) + **@mui/x-charts v8** for data visualization
- **Snowflake browser-based OAuth** via PKCE flow (local: `LOCAL_APPLICATION`, Vercel: custom integration)
- **Vite dev server proxy** (local) + **Vercel serverless proxy** (`ui/api/snowflake-proxy.js`) for Snowflake token + SQL API endpoints
- **Design system**: Solfege (Orchard's internal design system) — Rubik font, midnight sidebar, gray scale tokens, skeleton loading animations

### Running the Dashboard

```bash
cd ui && npm install && npm run dev
# Opens at http://127.0.0.1:5174/
# Click "Connect Snowflake" → OAuth redirect → authenticate → data loads
```

### UI File Structure

```
ui/
├── .env.local              # VITE_SNOWFLAKE_ACCOUNT=SME-DELPHI, warehouse=DEV_OWS_WH, role=FANSIFTER_ENGINEERING
├── vercel.json             # Build config + rewrites (snowflake-api proxy, SPA fallback)
├── vite.config.ts          # Snowflake proxy config (local dev)
├── api/
│   ├── snowflake-proxy.js  # Vercel serverless proxy for Snowflake token + SQL API
│   └── auth/
│       └── callback.js     # OAuth callback — bypasses Vercel deployment protection
└── src/
    ├── main.tsx
    ├── App.tsx             # Router + OAuth handler (reads code from query params OR hash fragment)
    ├── theme.ts            # Solfege MUI theme (gray scale, midnight scale, chart colors)
    ├── api/
    │   └── snowflake.ts    # OAuth PKCE + SQL API client (adapted from apify-tiktok/ui)
    ├── queries/
    │   └── microsoft.ts    # 6 SQL queries for Microsoft deliverability metrics
    ├── components/
    │   ├── SnowflakeConnect.tsx  # Connect/disconnect button with token refresh polling
    │   ├── KpiCard.tsx          # Metric card with delta arrows + shimmer skeleton loading
    │   └── WeekPicker.tsx       # Week range selector with "vs previous week" context
    └── pages/
        └── MicrosoftDashboard.tsx  # Main dashboard page with all sections
```

### Snowflake Connection

- **Instance**: `SME-DELPHI` (not SME-ORCHARD)
- **Warehouse**: `DEV_OWS_WH`
- **Database**: `FANSIFTER_APP_REPORTING`
- **Schema**: `PROD` (also uses `DEV_DURUKOV` for `EMAIL_DOMAIN_TO_ISP_MAPPING`)
- **Role**: `FANSIFTER_ENGINEERING`
- **OAuth storage prefix**: `sf_email_` (distinct from other apps)
- **SQL API**: Uses `POST /api/v2/statements` with async polling for results
- **Column names**: Snowflake SQL API returns **UPPERCASE** column names — all field access uses `r.FIELD_NAME ?? r.field_name` pattern

### Vercel Deployment

- **URL**: https://poc-email-campaigns.vercel.app
- **Team**: Songwhip (`--scope songwhip`)
- **Project**: `poc-email-campaigns`
- **Deployment protection**: Password-protected (Vercel deployment protection)
- **Snowflake OAuth integration**: `FANSIFTER_EMAIL_DASHBOARD_OAUTH` (custom, public client, PKCE)
  - Client ID: env var `VITE_SNOWFLAKE_CLIENT_ID` (set on Vercel, not in code)
  - Redirect URI: `https://poc-email-campaigns.vercel.app/api/auth/callback`
  - Refresh token validity: 86400s (24 hours)
- **OAuth callback pattern**: Vercel deployment protection strips query params from OAuth redirects. The `/api/auth/callback` serverless function bypasses protection and converts `?code=&state=` to hash fragment `/#code=&state=`. The app reads OAuth params from both query params (local dev) and hash fragments (Vercel).
- **Serverless proxy**: `/api/snowflake-proxy.js` proxies `/snowflake-api/*` to Snowflake. Must NOT set `Origin` header (Node 20 undici rejects it). Form-encoded bodies (OAuth token requests) are re-serialized via `URLSearchParams`.
- **Env vars**: Set via `printf 'VALUE' | vercel env add KEY production --scope songwhip` — never use `echo` (adds trailing `\n` that breaks Snowflake role/account lookups)
- **Deploy command**: `vercel --prod --scope songwhip` from `ui/` directory

### Dashboard Sections

1. **Sticky header** — Title + WeekPicker + SnowflakeConnect + last-refreshed timestamp
2. **KPI strip** — 6 cards: Delivery Rate, Bounce Rate, Open Rate, Click Rate, Total Delivered, Campaigns (each with WoW delta arrows)
3. **Weekly Comparison table** — Side-by-side "Selected Week" vs "Previous Week" metrics
4. **Throughput table** — Active minutes, avg/P50/P90/P95/peak deliveries per minute
5. **Daily Delivered bar chart** — Volume + bounced over 14 days
6. **Microsoft Deferrals bar chart** — Daily deferral counts
7. **Campaigns with Microsoft Deferrals table** — Clickable rows; clicking shows a popover with per-minute Outlook.com delivery timeline bar chart
8. **Daily Rates line chart** — Delivery %, Open %, Click % over 14 days
9. **ISP Breakdown table** — Top 15 ISPs by volume (Outlook.com row highlighted)
10. **Sending Domain Split table** — SME (`e.sonymusicfans.com`) vs The Orchard (`fans.theorchard.com`)

### SQL Queries

All in `ui/src/queries/microsoft.ts`, parameterized by date range:

| Query | Purpose | Microsoft filter |
|---|---|---|
| `weeklyComparisonQuery` | Delivery/bounce/open/click rates + counts | Yes — joins `DEV_DURUKOV.EMAIL_DOMAIN_TO_ISP_MAPPING`, filters `ISP = 'Outlook.com'` |
| `throughputSpeedQuery` | P50/P90/P95/peak deliveries per minute | Yes |
| `dailyTrendQuery` | Daily aggregates for 14-day charts | Yes |
| `ispBreakdownQuery` | Top 15 ISPs by volume with rates | No — shows all ISPs |
| `subuserSplitQuery` | SME vs Orchard via `EMAIL_CAMPAIGN` + `EMAIL_DOMAIN` join | Yes |
| `deferralCampaignsQuery` | Campaigns with deferrals + counts | Yes |
| `campaignDeliveryTimelineQuery` | Per-minute delivery timeline for a campaign | Yes |
| `deferralTrendQuery` | Daily deferral counts | Yes |

### ISP Mapping

- **Table**: `FANSIFTER_APP_REPORTING.DEV_DURUKOV.EMAIL_DOMAIN_TO_ISP_MAPPING`
- **Columns**: `DOMAIN`, `ISP` (not `EMAIL_DOMAIN` / `ISP_NAME`)
- **Join pattern**: `LOWER(SPLIT_PART(e.EMAIL, '@', 2)) = LOWER(m.DOMAIN)`
- **Microsoft ISP value**: `Outlook.com` (not `Microsoft`)

### Sending Domain Split

The `SUBUSER` column on `SENDGRID_WEBHOOK_GENERAL` is mostly null. Instead, use the sending domain via `EMAIL_CAMPAIGN` → `EMAIL_DOMAIN` join:
- `EMAIL_CAMPAIGN c ON e.CAMPAIGN_ID = c.ID`
- `EMAIL_DOMAIN d ON c.EMAIL_DOMAIN_ID = d.ID`
- Group by `d.DOMAIN`: `e.sonymusicfans.com` = SME, `fans.theorchard.com` = The Orchard

### Design System Notes

- **Solfege tokens** defined in `theme.ts`: gray scale (`#ffffff` to `#0a0c0d`), midnight scale (blue-tinted, from Fansifter production CSS vars) for sidebar
- **Sidebar**: Collapsible (240px expanded, 56px collapsed). Toggle button is a circular dark midnight icon positioned on the sidebar edge. Midnight scale sourced from Fansifter production (`--midnight-950: #0A1124` etc.)
- **Loading state**: All sections show simultaneously with shimmer skeleton animations (not deferred loading). Entire content dims to `opacity: 0.45` during refresh.
- **Skeleton animation**: `linear-gradient(90deg, #d5d9dd 25%, #f1f3f4 50%, #d5d9dd 75%)` with `background-position` keyframe
- **Chart colors**: Use `chartColors` export from `theme.ts` (teal, royalBlue, darkPurple, orange, red, sapGreen)
- **Tables**: 12px uppercase headers with gray-150 background, 8px/12px cell padding
- **Reference repos** for Solfege patterns: `theorchard/orchard-suite`, `theorchard/frontend-audience-development`