Social Media Analytics Dashboard
Talk-to-Your-Metrics Social Analytics Pipeline
A medallion-architecture pipeline that turns four messy social APIs into clean trends — and lets non-technical staff query them in plain English.
The Problem
The digital marketing team had to put together reports regularly, but the numbers lived scattered across each platform — TikTok in one place, Instagram in another, Facebook and YouTube each in their own. Nothing was consolidated, so building a single report meant logging into four dashboards and stitching it together by hand every time. The first fix was obvious: give them one dashboard that pulls everything together. But once that shipped, I hit the ceiling of dashboards — charts only ever answer the questions you built them for. The moment someone needed something the charts didn't already show, they were stuck.
Architecture
The Solution
An end-to-end medallion pipeline (bronze → silver → gold) that lands raw API responses in S3, normalizes four platform shapes into one star schema via a config-driven mapper, and pre-computes every trend into a gold layer so nothing aggregates at query time. On top of the clean data sits a natural-language layer: an MCP server exposes the gold schema and a read-only query tool, and Gemini turns 'how did TikTok do this week' into correct SQL, runs it, and answers in plain English.
Key Infrastructure Decisions
🥉 Medallion Architecture & Immutable Replay
Raw platform responses get dumped verbatim to S3 (bronze) under a date-only path with a fixed filename, so a same-day re-run overwrites instead of duplicating. A design choice that gave me idempotency end-to-end.
🗺️ Config-Driven Field Mapper
Instead of writing four separate parsers for all platforms, I built a single, config-driven ingestion engine. It translates all four different data formats into one unified database using a simple mapping file. Updating metrics requires zero code changes, and it automatically alerts us if a platform changes its data format.
⚠️ Baseline NULLs — Killing the False Spike
To prevent historical posts from triggering false 1-day growth spikes upon initial ingestion, I engineered a strict baseline rule that marks first-seen snapshots as NULL rather than zero. This explicitly decouples true daily velocity (gained while observed) from lifetime cumulative volume without corrupting analytics charts.
🧠 Natural-Language Layer via MCP
To enable natural-language analytics, I built an MCP-driven AI layer where Gemini writes and executes its own SQL against Postgres. To prevent the LLM from executing slow, expensive runtime aggregations on fine-grained fact tables, I embedded table routing directives directly into database metadata (COMMENT ON TABLE) and reinforced them with system prompt examples. This automatically steers the AI to query pre-aggregated Gold rollups.
🔒 Read-Only by Construction
To prevent security breaches, the query tool connects as a dedicated Postgres role that has SELECT granted on exactly three gold tables and nothing else. "The guardrail isn't a hopeful prompt; it's enforced by the database itself, so even a hallucinated query physically can't do damage.
Impact
✓
Four incompatible platform APIs unified into one queryable star schema via config
✓
Idempotent at every layer: partial runs self-heal, re-runs never duplicate, no manual cleanup ever needed
✓
Baseline-NULL modeling eliminated day-one false spikes and cleanly separated "growth while observed" from "lifetime totals"
✓
Non-technical marketers can now ask questions in plain English and get correct, pre-aggregated answers
✓
LLM query layer made safe by a read-only role proven at the database level, not trusted to prompt discipline
✓
Table-routing fix (directive schema comments + few-shot examples) got Gemini reliably choosing the right pre-aggregated table
Tech Used