# Full Funnel Ai Analytics

> Full-Funnel AI Marketing Analytics. A modern data stack powered by dbt MetricFlow and MCP. Natural language insights across Google/Meta Ads, CRM, and 5 data warehouses. Includes XGBoost lead scoring and a $0/mo architecture.

- **Type:** MCP server
- **Install:** `agentstack add mcp-eduardocornelsen-full-funnel-ai-analytics`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [eduardocornelsen](https://agentstack.voostack.com/s/eduardocornelsen)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [eduardocornelsen](https://github.com/eduardocornelsen)
- **Source:** https://github.com/eduardocornelsen/full-funnel-ai-analytics

## Install

```sh
agentstack add mcp-eduardocornelsen-full-funnel-ai-analytics
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Full-Funnel AI Marketing Analytics Platform

[](https://github.com/eduardocornelsen/full-funnel-ai-analytics/actions/workflows/ci.yml)
[](https://github.com/eduardocornelsen/full-funnel-ai-analytics/actions/workflows/warehouse-deploy.yml)
[](https://github.com/eduardocornelsen/full-funnel-ai-analytics/actions/workflows/scheduled-refresh.yml)

**Natural language marketing analytics powered by MCP, dbt Semantic Layer, and ML lead scoring. Works with Claude Desktop, OpenCode, Gemini CLI, and Antigravity IDE.**

> *"Which channels actually drive revenue, not just clicks?"*
> This system answers that question in 15 seconds via natural language — backed by multi-touch attribution, a production ML scoring API, and dashboards fed from a single governed semantic layer running across 5 data warehouses.

---

## The Problem

Companies run ads across Google, Meta, and organic channels. Marketing claims leads. Sales says they're low quality. The CEO asks: *"Where should we spend next quarter?"*

Answering this requires joining data from 5+ platforms, building attribution models, scoring leads, and making it all accessible to non-technical stakeholders. Most teams cobble together spreadsheets and one-off queries. This project builds the production system — at $0/month base cost.

---

+ Show Table of Contents

- [Demo](#demo)
- [Who This Is Built For](#who-this-is-built-for)
- [The Core Insight](#the-core-insight-governance-is-what-makes-ai-analytics-reliable)
- [What This Project Does](#what-this-project-does)
- [CI/CD & Production Readiness](#cicd--production-readiness)
- [Data Architecture](#data-architecture--what-lives-where)
- [Importing Your Own Data](#importing-your-own-data)
- [MCP Servers](#mcp-servers)
- [AI Clients](#ai-clients)
- [AI-Powered Commands](#ai-powered-commands)
- [Metrics Governed by the Semantic Layer](#metrics-governed-by-the-semantic-layer)
- [Stack & Cost](#stack--cost)
- [Quick Start](#quick-start)
- [Project Structure](#project-structure)
- [Key Design Decisions](#key-design-decisions)
- [Swapping to Real Platform Data](#swapping-to-real-platform-data)
- [Multi-Warehouse Portability](#multi-warehouse-portability)
- [Documentation](#documentation)
- [Contributing](#contributing)
- [License](#license)

> **Full documentation index → [`docs/README.md`](docs/README.md)**

---

## Demo

> Predefined commands: type `/marketing` and see the magic happens:

**[▶ Watch the video →](demo/videos/marketing-query.mp4)**

> View the dashboards for all commands available: 
> [[/marketing]](dashboards/full_funnel_marketing_dashboard.html) | [[/attribution]](dashboards/attribution_dashboard.html) | [[/campaign]](dashboards/campaign_performance_dashboard.html) | [[/pipeline]](dashboards/pipeline_dashboard.html) | [[/traffic]](dashboards/traffic_ga4_dashboard.html)

### Hero Query

> *"Show me the complete marketing funnel for Q1 2025: ad spend across Google and Meta, website sessions by channel, lead conversion rates, and final revenue. Calculate blended CAC and ROAS."*

**[▶ Watch the demo video →](demo/videos/open-query.mp4)**

The AI queries the dbt semantic layer via MCP, pulls GA4 traffic and CRM pipeline data from mock platform servers, and returns a formatted analysis with KPI cards, charts, and recommendations — in ~15 seconds. Works from Claude Desktop, OpenCode, Gemini CLI, or Antigravity IDE.

### Other queries this system handles

- *"Compare first-touch vs last-touch attribution for our top channels"*
- *"Score this lead: came from Google Ads, visited 5 pages, 3 min on site"*
- *"Which product categories have the highest CAC but lowest LTV?"*
- *"What should we change about our ad spend next quarter?"*

---

## Who This Is Built For

| Role                              | What they see                                                                    |
| --------------------------------- | -------------------------------------------------------------------------------- |
| **Paid Media / Growth Analytics** | Multi-touch attribution (4 models), ROAS by channel, spend optimization          |
| **RevOps Analyst**                | Full-funnel pipeline, CRM integration, lead routing automation                   |
| **Data Scientist**                | XGBoost lead scoring, MLflow experiment tracking, FastAPI deployment             |
| **Analytics Engineer**            | dbt semantic layer, MCP architecture, multi-warehouse + multi-client portability |
| **BI / Data Analyst**             | Looker Studio dashboards, Streamlit app, React artifacts                         |
| **Marketing Analyst**             | CAC/LTV analysis, channel comparison, attribution model comparison               |

---

## The Core Insight: Governance is What Makes AI Analytics Reliable

Most AI-to-SQL tools fail because they lack a **source of truth.** When an AI writes SQL on behalf of a marketing manager who can't verify it, you need guaranteed correctness.

This project solves that with the **dbt Semantic Layer (MetricFlow)**: define "ROAS" once in YAML, and every AI client, dashboard, and ML pipeline consumes the exact same definition — `SUM(attributed_revenue) / SUM(ad_spend)` with the correct filters and joins, forever.

> According to [The 2025 Metabase Community Data Stack Report](https://www.metabase.com/data-stack-report-2025):
> *"Average confidence in AI-generated queries is just **5.5/10** without a semantic layer. Tools like dbt MCP (60+ tools) now provide production-grade MCP servers that give LLMs deterministic metric definitions, reducing hallucination and enforcing governance across platforms like Snowflake and Databricks."*
>
> **This project implements exactly that architecture** — moving from 5.5/10 confidence to deterministic, production-grade certainty.

---

## What This Project Does

### Architecture: Three Heads, One Spine

> Full-stack analytics portfolio — data ingestion through AI-powered natural language querying, built entirely on free/trial tiers.

View original architecture diagram (SVG)

| Pillar       | What it does                                               | Tech                                                              |
| ------------ | ---------------------------------------------------------- | ----------------------------------------------------------------- |
| **AI Layer** | Query marketing data in plain English, generate dashboards | 7 MCP servers + Claude Desktop, OpenCode, Gemini CLI, Antigravity |
| **ML Layer** | Predict which leads become high-value customers            | XGBoost + MLflow + FastAPI `/score` + n8n auto-routing            |
| **BI Layer** | Self-serve dashboards for marketing and sales teams        | Looker Studio + Streamlit + Claude React artifacts                |

For a detailed breakdown of every model, metric, and semantic definition see [docs/architecture.md](docs/architecture.md).

---

## CI/CD & Production Readiness

The project ships with four GitHub Actions workflows that enforce zero metric drift from source to dashboard.

### Workflows

| Workflow | Trigger | What it does |
| -------- | ------- | ------------ |
| [`ci.yml`](.github/workflows/ci.yml) | Every pull request | dbt compile + test on DuckDB → generate golden metrics → validate drift → pytest. No cloud creds needed. |
| [`warehouse-deploy.yml`](.github/workflows/warehouse-deploy.yml) | Push to `main` | Deploys dbt to BigQuery **and** Snowflake in parallel → regenerates `golden_metrics.json` → commits it back to the repo. |
| [`scheduled-refresh.yml`](.github/workflows/scheduled-refresh.yml) | Daily 06:00 UTC | Appends synthetic data → dbt run/test → regenerates golden metrics with `--live` flag → validates. |
| [`daily-synthetic-data.yml`](.github/workflows/daily-synthetic-data.yml) | Daily 05:00 UTC | Adds one new day of realistic synthetic data to mock CSVs. Can be manually triggered with a custom `--days` count. |

### The Zero-Drift Guarantee

```
Raw data
  → dbt staging/intermediate/mart models
  → generate_golden_metrics.py  (reads mart tables, writes dashboards/golden_metrics.json)
  → validate_metrics.py         (re-queries warehouse, diffs vs JSON — exits 1 if any drift)
  → HTML dashboards + Claude skills + Streamlit app  (all read golden_metrics.json)
```

Every PR gate and every warehouse deploy runs `validate_metrics.py`. If a metric in the dashboard diverges from the warehouse by more than the configured tolerance, the workflow fails and blocks the merge.

### GitHub Secrets Required

To enable BigQuery and Snowflake deploys, add these secrets in **Settings → Secrets and variables → Actions**:

| Secret | Platform |
| ------ | -------- |
| `GCP_PROJECT_ID` | BigQuery |
| `GCP_SERVICE_ACCOUNT_KEY_JSON` | BigQuery |
| `SNOWFLAKE_ACCOUNT` | Snowflake |
| `SNOWFLAKE_USER` | Snowflake |
| `SNOWFLAKE_PASSWORD` | Snowflake |
| `SNOWFLAKE_WAREHOUSE` | Snowflake |
| `SNOWFLAKE_DATABASE` | Snowflake |
| `SNOWFLAKE_SCHEMA` | Snowflake |

The DuckDB PR gate needs **no secrets** — it runs entirely locally inside the Actions runner.

See the [Production Readiness Guide](docs/guides/production_readiness_guide.md) for full setup instructions, including how to create a BigQuery service account and configure Snowflake.

---

## Data Architecture — What Lives Where

| Layer | Location | In git? | How it's created |
|-------|----------|---------|-----------------|
| Olist raw dataset | `data/olist/*.csv` | ❌ Never (400MB) | `python scripts/download_olist_data.py` |
| Mock marketing CSVs | `data/mock_marketing/*.csv` | ✅ Yes (~10MB) | Generated once; CI recreates without Olist via `--standalone` |
| DuckDB warehouse | `data/olist_analytics.duckdb` | ❌ Never | `load_duckdb.py` + `dbt run` locally |
| Golden metrics snapshot | `dashboards/golden_metrics.json` | ✅ Yes (~50KB) | CI commits it after every deploy |

**To update your local DuckDB after a `git pull`:**

```bash
git pull                                                   # get new CSVs + golden_metrics.json
python scripts/load_duckdb.py                             # load CSVs into DuckDB
cd dbt_project && dbt run --target duckdb && cd ..        # rebuild mart tables
```

Claude dashboards update after `git pull` alone (they read `golden_metrics.json`).
Streamlit and DuckDB queries need all three steps above.

See [Data Import Guide](docs/guides/data_import_guide.md) for the full explanation including how this maps to a real production pipeline (Fivetran → BigQuery → dbt Cloud).

---

## Importing Your Own Data

The platform supports three ways to bring in real data — from a spreadsheet, a CSV file, or a live warehouse.

### Streamlit Data Sources UI (easiest)

```bash
streamlit run streamlit_app/app.py
```

Click **Data Sources** in the sidebar to open the three-tab interface:

| Tab | What it does |
| --- | ------------ |
| **File Upload** | Drag-and-drop a CSV or Excel file, preview 20 rows, choose which table to replace, and save it to `data/mock_marketing/` |
| **Warehouse Connection** | Configure and test a connection to DuckDB, BigQuery, or Snowflake. Preview any mart table. Saves to `~/.full_funnel_connectors.json` (never committed to git). |
| **MCP Server Status** | Live view of all 6 MCP servers — CSV path, row count, and latest date in the data. |

After saving a file, rebuild the pipeline from the terminal:

```bash
python scripts/load_duckdb.py
cd dbt_project && dbt run --target duckdb && cd ..
python scripts/generate_golden_metrics.py
python scripts/validate_metrics.py   # must exit 0
```

### See Updated Data in Claude

Once `golden_metrics.json` is regenerated, every Claude skill (`/marketing`, `/attribution`, `/campaign`, etc.) reads the new numbers automatically. In Claude Code on the web, push the updated JSON to the repo so the container has the latest file.

See the [Data Import Guide](docs/guides/data_import_guide.md) for all three import methods, column mapping tables, and troubleshooting tips.

---

## MCP Servers

| Server                 | What it exposes                   | Key tools                                                                     |
| ---------------------- | --------------------------------- | ----------------------------------------------------------------------------- |
| **BigQuery**           | Warehouse queries                 | `execute_query`, `list_tables`, `get_schema`                                  |
| **dbt Semantic Layer** | Governed metrics + SQL generation | `text_to_sql`, `get_metrics`, `get_dimensions` (60+ tools)                    |
| **Google Ads**         | Campaign performance, keywords    | `get_campaign_performance`, `get_keyword_performance`, `list_campaigns`       |
| **Meta Ads**           | Ad sets, reach, purchases         | `get_campaign_insights`, `get_ad_set_breakdown`, `list_campaigns`             |
| **GA4**                | Sessions, channels, conversions   | `get_traffic_by_channel`, `get_daily_trend`, `get_device_breakdown`           |
| **HubSpot**            | Contacts, deals, pipeline         | `get_contacts_by_source`, `get_deal_pipeline`, `search_contacts`              |
| **Salesforce**         | Opportunities, accounts, revenue  | `get_opportunity_pipeline`, `get_revenue_by_source`, `get_quarterly_forecast` |

> All mock servers use the exact same tool interface as real platform APIs. Swap mock → production with zero code changes. See [docs/mcp_servers.md](docs/mcp_servers.md) for full server reference.

---

## AI Clients

| Client              | MCP Support        | Unique Strength                                              | Cost              |
| ------------------- | ------------------ | ------------------------------------------------------------ | ----------------- |
| **Claude Desktop**  | Native (best)      | Cowork plugin, React artifact rendering, multi-tool chaining | $20/mo (optional) |
| **OpenCode**        | Native             | 75+ models (Claude, Gemini, GPT, Llama, local), open source  | Free + API costs  |
| **Gemini CLI**      | Native             | Native BigQuery integration, free generous rate limits       | Free              |
| **Antigravity IDE** | Native (MCP Store) | Manager View with parallel agents, VS Code fork              | Free (preview)    |

> The same 7 MCP servers work with ALL 4 clients. No code changes between clients.

---

## AI-Powered Commands

Type these in Claude Code CLI or Antigravity to generate a deep-dive analysis artifact:

| Command        | What it does                                                 |
| :------------- | :----------------------------------------------------------- |
| `/marketing`   | Full exec dashboard — KPIs, spend, funnel, pipeline          |
| `/attribution` | Channel attribution deep-dive — scatter, waterfall, insights |
| `/pipeline`    | Sales pipeline — funnel stages, deal velocity, lifecycle     |
| `/campaign`    | Paid campaign performance — Google vs Meta, budget pacing    |
| `/traffic`     | GA4 traffic — sessions trend, channel breakdown, anomalies   |

Commands live in `.claude/commands/` (Claude CLI) and `.opencode/commands/` (OpenCode). Same logic, both formats. You can add your own commands as well.

### Claude Desktop

Since Claude Desktop doesn't support command files, use **Projects**:
1. Open Claude Desktop → **Projects** → **New Project**
2. Paste [`claude_desktop_project_instructions.md`](docs/guides/claude_desktop_project_instructions.md) into **Project Instructions**
3. Use natural language instead of slash commands:

| CLI Command    | Claude Desktop Equivalent     |
| :------------- | :---------------------------- |
| `/marketing`   | "marketing dashboard"         |
| `/attribution` | "which channels are working?" |
| `/traffic`     | "show me sessions"            |
| `/campaign`    | "google vs meta performance"  |
| `/pipeline`    | "show me deals"               |

---

## Metrics Governed by the Semantic Layer

| Metric              | Definition                                        | Category    |
| -------------------

…

## Source & license

This open-source MCP server is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [eduardocornelsen](https://github.com/eduardocornelsen)
- **Source:** [eduardocornelsen/full-funnel-ai-analytics](https://github.com/eduardocornelsen/full-funnel-ai-analytics)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** yes
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/mcp-eduardocornelsen-full-funnel-ai-analytics
- Seller: https://agentstack.voostack.com/s/eduardocornelsen
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.