📊 Marketing Analytics Assistant

A two-tab Streamlit application that combines a natural language SQL assistant with an AI-powered marketing recommendation engine. Built as a portfolio project demonstrating the full analytics-to-action loop: query data → surface intelligence → generate recommendations → track decisions.

---

🎯 What This Project Does

Most analytics tools stop at the dashboard. This project goes one step further — it takes the same data you can query and runs it through an AI layer that produces prioritized, data-backed recommendations with simulated impact estimates. A human reviews them, marks them applied or dismissed, and the system keeps an audit trail.

The loop:

Marketing Data (SQLite)
        ↓
  monitor.py — diagnostic queries → Claude Sonnet
        ↓
  Structured recommendations (JSON) → recommendations table
        ↓
  Streamlit app — review, action, audit trail

---

🏗️ Architecture

marketing_analytics_assistant/
├── app.py                  # Streamlit app (two tabs)
├── monitor.py              # Intelligence monitor — runs diagnostics, calls Claude
├── setup_db.py             # Database setup and seed data
├── marketing_analytics.db  # SQLite database (generated by setup_db.py)
├── requirements.txt
├── .gitignore
├── .streamlit/
│   └── secrets.toml        # API keys — never committed
└── README.md

---

✨ Features

💬 Tab 1 — Analytics Assistant

• Ask any question about your marketing data in plain English
• Claude translates it to SQLite SQL, executes it, and returns results
• Auto-renders bar or line charts based on result shape
• Multi-turn conversation memory — follow-up questions refine previous queries
• Persona-based question bank (CMO / Digital Marketing Manager / Analytics Engineer)
• 20-question session limit with counter

💡 Tab 2 — Recommendations

• Risk stance selector — Conservative, Neutral, or Aggressive. Changes the thresholds and language Claude uses when evaluating performance. Conservative flags only severe underperformance and suggests small changes. Aggressive has a lower tolerance and recommends bolder reallocation.
• Refresh Recommendations — runs monitor.py in the background with the selected stance, writes fresh recommendations to the DB, reloads the view
• Priority + status filters — filter cards by High / Medium / Low and Pending / Applied / Dismissed
• Status workflow — mark each recommendation as Applied (with a free-text notes field) or Dismissed (with a reason dropdown). Writes back to the DB instantly with a UTC timestamp.
• Action log — collapsible audit trail of everything actioned, with notes and timestamps
• Summary metrics row — total, high/medium counts, pending vs applied

---

🤖 How monitor.py Works

monitor.py is a standalone script that runs independently of the Streamlit app. It can be triggered manually from the terminal or via the Refresh button in the UI.

What it does:

1. Connects to marketing_analytics.db
2. Runs 7 diagnostic queries across ROAS by channel, CPA by channel, email engagement, lead velocity, top/bottom ads by ROAS, and revenue by campaign type
3. Packages results into a structured JSON snapshot
4. Sends the snapshot to Claude Sonnet with a stance-aware system prompt
5. Parses the JSON response into 5–8 prioritized recommendations
6. Writes them to a recommendations table (created automatically on first run)

Stance system:

The --stance flag changes the thresholds and language instructions in the Claude prompt — not just a label, but actual different evaluation criteria:

Stance	ROAS threshold	Budget change cap	Language bias
Conservative	< 1.0	15%	Monitor, review, consider
Neutral	< 1.5	30%	Balanced
Aggressive	< 2.0	50%	Pause immediately, reallocate now

Usage:

# Dry run — prints recommendations without writing to DB
python monitor.py --dry-run

# Run with default (neutral) stance
python monitor.py

# Run with a specific stance
python monitor.py --stance conservative
python monitor.py --stance aggressive

---

🗄️ Database Schema

The SQLite database covers a full B2B marketing stack:

Domain	Tables
Core	campaigns, customers, leads, orders, order_items, products, payments
PPC	ad_groups, ads, ad_performance
Email	email_campaigns, email_events
SEO	seo_keywords, seo_rankings, organic_traffic
Web	gtm_tags, web_events, web_sessions
Content	content_pieces, content_performance
Audience	audiences, audience_members, ab_tests, ab_variants
Intelligence	recommendations

---

🛠️ Tech Stack

Layer	Technology
Frontend	Streamlit
AI / LLM	Anthropic Claude (Sonnet for monitor, Haiku for chat)
Database	SQLite (local), Databricks-ready via toggle
Language	Python 3.12
Key management	.streamlit/secrets.toml (gitignored)

---

🚀 Setup

1. Clone the repo

git clone https://github.com/yourusername/marketing_analytics_assistant.git
cd marketing_analytics_assistant

2. Install dependencies

pip install -r requirements.txt

3. Add your API key

Create .streamlit/secrets.toml:

[anthropic]
api_key = "sk-ant-..."

4. Generate the database

python setup_db.py

5. Run the app

streamlit run app.py

6. Generate your first recommendations

python monitor.py

Or click 🔄 Refresh Recommendations inside the app.

---

⚠️ A Note on the Data

This project uses a synthetic dataset generated for portfolio purposes. Some metrics may appear extreme (e.g. very high CPAs, near-zero ROAS on certain channels). This is intentional — it demonstrates the system's ability to surface and prioritize real problems. The architecture, query logic, and recommendation workflow are directly transferable to production data.

---

🗺️ Roadmap

• Scheduled monitor runs via cron or Streamlit scheduler
• Email digest of weekly recommendations
• Recommendation history — compare results across stance and time
• Databricks production mode (toggle already in app.py)

---

👤 Author

Built by Steve Lopez — Analytics Engineer portfolio project.
LinkedIn · GitHub