Full Funnel Ai Analytics

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/fullfunnelmarketingdashboard.html) | [[/attribution]](dashboards/attributiondashboard.html) | [[/campaign]](dashboards/campaignperformancedashboard.html) | [[/pipeline]](dashboards/pipelinedashboard.html) | [[/traffic]](dashboards/trafficga4_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: > "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/productionreadinessguide.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:

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/dataimportguide.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)

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:

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/dataimportguide.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/mcpservers.md](docs/mcpservers.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/claudedesktopproject_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
• Source: eduardocornelsen/full-funnel-ai-analytics
• License: MIT

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