Insight Engine — AI Root-Cause Analytics System

An analytics system that doesn't just report that a KPI moved — it automatically diagnoses why, across every customer/channel/region dimension, and recommends an action. Built to demonstrate SQL, Python, AI/LLM integration, BI, and automation working together as one real pipeline, not five disconnected exercises.

The problem this solves

Most dashboards stop at description:

"Revenue dropped 14% this month."

This system goes further, automatically:

"Revenue dropped 4.45% between Aug 15–28. The primary driver is the Instagram acquisition channel (down 35.1%), concentrated in the Punjab region (down 38.0%). Recommended action: investigate and re-engage this segment directly — check for a paused campaign or delivery issue specific to this group."

That sentence isn't hard-coded. It's generated by a real decomposition algorithm running against real data, then narrated by an AI layer that is only allowed to describe facts the algorithm already verified (see "Why the AI can't hallucinate the cause" below).

Architecture

   CSV / synthetic data
          │
          ▼
   ┌─────────────┐        data/generate_data.py
   │   Python    │────►   src/db_loader.py
   │  Data Layer │        (loads into MySQL / SQLite)
   └─────────────┘
          │
          ▼
   ┌─────────────┐
   │   MySQL     │        sql/schema.sql
   │  (storage)  │        customers · products · orders · kpi_daily · ai_insights
   └─────────────┘
          │
          ▼
   ┌─────────────────────┐
   │  Driver-Analysis     │   src/driver_analysis.py
   │  Engine (the "brain")│   anomaly detection (z-score + magnitude)
   │                       │   automated root-cause drill-down
   └─────────────────────┘
          │
          ▼
   ┌─────────────┐
   │  AI Narrator │        src/ai_narrator.py
   │   (LLM)      │        turns a VERIFIED finding into plain English
   └─────────────┘        + src/ask_data.py — "ask your data" NL→SQL bot
          │
          ▼
   ┌─────────────┐
   │  Power BI /  │        dashboard/build_dashboard.py
   │  Dashboard   │        → dashboard/executive_dashboard.html
   └─────────────┘
          │
          ▼
   ┌─────────────┐
   │  Automation  │        src/automation_pipeline.py (orchestrator)
   │  + Alerts    │        src/email_alert.py (scheduled notification)
   └─────────────┘

Why the AI can't hallucinate the cause

This is the single most important design decision in the project, and the one worth explaining in an interview: the LLM is never asked "why did revenue change?" It's only ever handed a structured, already-verified finding — e.g. {dimension: "acquisition_channel", value: "Instagram", contribution_pct: 280.3} — and asked to write a sentence about that finding. The causal claim itself comes entirely from driver_analysis.py, which decomposes the KPI change across every dimension combination and ranks slices by their actual statistical contribution to the total move — the same thing a human analyst does manually in a pivot table, automated. The LLM can misphrase a true fact; it can't invent a false one.

What's in the repo

Path	What it does
sql/schema.sql	Full MySQL schema for production deployment
data/generate_data.py	Generates 6 months of realistic synthetic order data with a planted, findable anomaly
src/db_loader.py	Loads CSVs into a database (SQLite by default — zero setup; swap in MySQL, see below)
src/driver_analysis.py	The core engine — anomaly detection + automated root-cause drill-down
src/ai_narrator.py	LLM narration layer, with a rule-based fallback (works with zero API key)
src/ask_data.py	Natural-language "ask your data" query bot (needs an API key)
src/automation_pipeline.py	Orchestrates the full flow, logs insights, writes reports
src/email_alert.py	Sends the latest report by email (dry-run/print mode if no SMTP configured)
dashboard/build_dashboard.py + template.html	Builds the executive dashboard from live data
reports/	Generated Markdown + JSON insight reports (created when you run the pipeline)

Quickstart (zero setup required)

pip install -r requirements.txt

# 1. Generate synthetic data (6 months, 6000 customers, planted anomaly)
python3 data/generate_data.py

# 2. Load into the local database
python3 src/db_loader.py

# 3. Run the full pipeline: detect anomalies, find root causes, generate insights
python3 src/automation_pipeline.py

# 4. Build the dashboard
python3 dashboard/build_dashboard.py
# open dashboard/executive_dashboard.html in a browser

Everything above runs with no API key and no MySQL server — the AI narrator falls back to a rule-based generator, and the data layer uses SQLite. This matters if someone (a recruiter, a grader) clones the repo and wants to run it immediately.

Turning on the real LLM layer

cp config/.env.example config/.env
# edit config/.env and set ANTHROPIC_API_KEY
export $(cat config/.env | grep ANTHROPIC_API_KEY)

python3 src/automation_pipeline.py     # now narrated by Claude instead of the rule-based fallback
python3 src/ask_data.py "Which region had the biggest revenue drop last month?"

Going to real MySQL (for the "production" story)

1. Install MySQL locally or use a free-tier cloud instance (PlanetScale, Railway, AWS RDS free tier).
2. Run sql/schema.sql against it.
3. In src/db_loader.py, replace the sqlite3.connect(...) call with:

   import mysql.connector
   conn = mysql.connector.connect(
       host=os.environ["MYSQL_HOST"], user=os.environ["MYSQL_USER"],
       password=os.environ["MYSQL_PASSWORD"], database=os.environ["MYSQL_DATABASE"]
   )

   (pip install mysql-connector-python)
4. Everything downstream — driver_analysis.py, automation_pipeline.py — uses plain SQL that works unchanged against MySQL; only the connection line differs.

Connecting Power BI

1. Point Power BI's MySQL connector (Get Data → MySQL database) at your MySQL instance, database insight_engine.
2. Build these visuals directly from the tables:
   • KPI cards from kpi_daily (latest row) — Revenue, Repeat Purchase Rate, AOV
   • Trend line from kpi_daily over snapshot_date
   • AI Insight table from ai_insights, sorted by generated_at descending — this is your "AI Anomaly Detected" panel
   • Drill-down matrix: orders joined to customers/products, sliced by acquisition_channel, region, price_bucket — lets a viewer manually explore what the AI engine already automated
3. Set a scheduled refresh (Power BI Service → dataset settings) to match how often you run automation_pipeline.py, so the dashboard reflects the latest AI-generated insights automatically.

Alternatively, the included dashboard/executive_dashboard.html is a fully working standalone dashboard (dark "diagnostic terminal" design, live revenue chart, and a root-cause trail visual showing the causal chain) if you want something demoable without a Power BI license.

Automating the whole thing

Point a scheduler at the pipeline so it runs unattended:

# cron example — every morning at 7am
0 7 * * *  cd /path/to/insight-engine/src && python3 automation_pipeline.py && python3 email_alert.py

Extending it

• Model AOV/repeat-rate decomposition properly, instead of always decomposing by revenue (documented as a known simplification in driver_analysis.py).
• Add a churn-prediction model (scikit-learn classifier) that flags at-risk customers before they lapse, feeding the same ai_insights table.
• Swap the rule-based fallback narrator for a fine-tuned prompt template per KPI type, so the language reads more specifically for revenue vs. retention vs. AOV.
• Add Slack alerting alongside email_alert.py using an incoming webhook.

Talking points for interviews

• "Why not just let the LLM figure out the cause directly?" → explain the hallucination-prevention design above; the LLM narrates, it doesn't diagnose.
• "How does the drill-down actually work?" → contribution/decomposition analysis: slice by every dimension, compare normalized daily averages between a baseline and recent window, rank by share of the total change.
• "What would you change for production scale?" → move the drill-down from Python loops over sqlite3/mysql cursors to a warehouse-native SQL decomposition (or dbt models), and add caching so re-running anomaly detection doesn't rescan the full orders table every time.