{"slug":"analytics-stack","title":"Analytics Stack — Self-Hosted Supabase + Metabase","tags":["analytics","supabase","metabase","reporting","plaid","data"],"agent_summary":"Self-hosted analytics architecture using Supabase as single source of truth with nightly sync from GHL, Signal House, Instantly, Stripe, Plaid, and Meta Ads into Metabase dashboards.","trigger_phrases":["analytics stack","self-hosted analytics","supabase metabase","agency reporting","plaid claude","ghl data sync","agency dashboard","metabase dashboards"],"runnable":true,"markdown":"\n# Analytics Stack — Self-Hosted\n\n## Components\n- **Supabase** — single source of truth (Postgres + Auth + Edge Functions)\n- **Metabase** — self-hosted BI, dashboards, scheduled email reports\n- **Sync scripts** (Python, cron-driven) — pull from each source nightly\n\n## Data Sources → Supabase Tables\n| Source | Sync Method | Table |\n|---|---|---|\n| GHL (per sub-account) | Public API + webhook | `ghl_contacts`, `ghl_opportunities`, `ghl_conversations` |\n| Signal House | REST pull | `sms_events` |\n| Instantly | API pull | `email_events`, `email_campaigns` |\n| Stripe | Webhook + backfill | `stripe_charges`, `stripe_subscriptions` |\n| Plaid | Webhook | `bank_transactions`, `cc_transactions` |\n| Meta Ads | API pull | `meta_ad_insights` |\n\n## Why Plaid\nFeeds Claude with live bank + CC data. Use cases:\n- Payroll decisioning (\"can I make payroll this Friday?\")\n- P&L categorization\n- Cash runway alerts\n- Subscription audit (find forgotten SaaS)\n\n## Metabase Dashboards (core set)\n1. **Agency P&L** — Stripe MRR vs expenses (Plaid) by month\n2. **Funnel** — leads → booked → showed → closed, per client\n3. **SMS Health** — delivery %, reply %, opt-out % per sending number\n4. **Ad ROAS** — Meta spend vs Stripe revenue by UTM\n5. **Client Scorecard** — one page per client, monthly PDF emailed\n\n## Not Chosen (and why)\n- **Looker Studio** — query caching is slow, no version control\n- **Power BI** — Windows-centric, bad for remote team\n- **Segment** — $$ + event model doesn't fit the batch SMS world\n","html":"<h1>Analytics Stack — Self-Hosted</h1>\n<h2>Components</h2>\n<ul>\n<li><strong>Supabase</strong> — single source of truth (Postgres + Auth + Edge Functions)</li>\n<li><strong>Metabase</strong> — self-hosted BI, dashboards, scheduled email reports</li>\n<li><strong>Sync scripts</strong> (Python, cron-driven) — pull from each source nightly</li>\n</ul>\n<h2>Data Sources → Supabase Tables</h2>\n<p>| Source | Sync Method | Table |\n|---|---|---|\n| GHL (per sub-account) | Public API + webhook | <code>ghl_contacts</code>, <code>ghl_opportunities</code>, <code>ghl_conversations</code> |\n| Signal House | REST pull | <code>sms_events</code> |\n| Instantly | API pull | <code>email_events</code>, <code>email_campaigns</code> |\n| Stripe | Webhook + backfill | <code>stripe_charges</code>, <code>stripe_subscriptions</code> |\n| Plaid | Webhook | <code>bank_transactions</code>, <code>cc_transactions</code> |\n| Meta Ads | API pull | <code>meta_ad_insights</code> |</p>\n<h2>Why Plaid</h2>\n<p>Feeds Claude with live bank + CC data. Use cases:</p>\n<ul>\n<li>Payroll decisioning (\"can I make payroll this Friday?\")</li>\n<li>P&#x26;L categorization</li>\n<li>Cash runway alerts</li>\n<li>Subscription audit (find forgotten SaaS)</li>\n</ul>\n<h2>Metabase Dashboards (core set)</h2>\n<ol>\n<li><strong>Agency P&#x26;L</strong> — Stripe MRR vs expenses (Plaid) by month</li>\n<li><strong>Funnel</strong> — leads → booked → showed → closed, per client</li>\n<li><strong>SMS Health</strong> — delivery %, reply %, opt-out % per sending number</li>\n<li><strong>Ad ROAS</strong> — Meta spend vs Stripe revenue by UTM</li>\n<li><strong>Client Scorecard</strong> — one page per client, monthly PDF emailed</li>\n</ol>\n<h2>Not Chosen (and why)</h2>\n<ul>\n<li><strong>Looker Studio</strong> — query caching is slow, no version control</li>\n<li><strong>Power BI</strong> — Windows-centric, bad for remote team</li>\n<li><strong>Segment</strong> — $$ + event model doesn't fit the batch SMS world</li>\n</ul>\n"}