Build Marketing Analytics Dashboards with AI

# B2B SaaS Marketing Performance Dashboard — Specification

## 1. Dashboard Overview
- **Purpose:** Give leadership a single view of paid channel efficiency, pipeline contribution, and CAC payback across Google Ads, LinkedIn, and content syndication.
- **Audience:** CMO (primary), VP Demand Gen, RevOps, CFO (secondary).
- **Refresh cadence:** Daily ETL sync at 6 AM; dashboard reviewed weekly (Monday 9 AM standup). Month-end snapshot archived.

## 2. Core KPIs

| KPI | Formula | Target |
|---|---|---|
| Blended CAC | Total S&M Spend / New Customers | < $9,000 |
| CAC Payback (months) | CAC / (ARR per customer × Gross Margin / 12) | < 14 mo |
| Pipeline Contribution ($) | Σ Opportunity Amount (attribution-weighted) | ≥ 4× monthly spend |
| Marketing-Sourced Pipeline ROAS | Weighted Pipeline $ / Spend | ≥ 5:1 |
| MQL-to-SQL Conversion | SQLs / MQLs | ≥ 35% |
| SQL-to-Opp Rate | Opps / SQLs | ≥ 60% |
| LTV:CAC | (ARPA × Gross Margin / Churn) / CAC | ≥ 3:1 |
| Cost per SQL by Channel | Channel Spend / Channel SQLs | < $1,200 |

## 3. Widget Layout (12-col grid)

| Pos | Widget | Chart | Metric | Dimensions | Filters |
|---|---|---|---|---|---|
| Row1 C1–4 | CAC Scorecard | KPI tile + sparkline | Blended CAC | Week | Date |
| Row1 C5–8 | CAC Payback | Gauge | Months | — | Segment |
| Row1 C9–12 | LTV:CAC | KPI tile | Ratio | — | Segment |
| Row2 C1–6 | Pipeline by Channel | Stacked bar | Weighted pipeline $ | Channel × Week | Stage |
| Row2 C7–12 | Spend vs. Pipeline ROAS | Dual-axis line | Spend, ROAS | Channel × Week | — |
| Row3 C1–6 | Funnel Conversion | Funnel chart | Visit→MQL→SQL→Opp→Won | Channel | Segment |
| Row3 C7–12 | Cost per SQL | Horizontal bar | CPSQL | Channel/Campaign | Date |
| Row4 C1–6 | Attribution Comparison | Bar | Pipeline $ (First/Last/W-shaped) | Channel | Model |
| Row4 C7–12 | Cohort CAC Payback | Heatmap | Months-to-recover | Acquisition cohort | Segment |

## 4. Data Sources & Joins

| Source | Tables | Key | Transform |
|---|---|---|---|
| GA4 | sessions, events | `client_id` → `hubspot_utk` | Session stitching, UTM normalization |
| HubSpot | contacts, deals, campaigns | `email`, `deal_id` | MQL/SQL stage flagging |
| Salesforce | Opportunity, Account, Campaign Influence | `account_id`, `sf_campaign_id` | Weighted attribution (W-shaped) |
| Ad platforms (Google, LinkedIn, syndication CSV) | Campaign spend | `utm_campaign` | Daily spend rollup |

Join path: **GA4 → HubSpot (utk/email) → Salesforce (contact→opp)**; spend joined on `utm_campaign`.

## 5. Drill-downs & Filters
- **Date ranges:** WTD, MTD, QTD, trailing 13 weeks, custom.
- **Segments:** ICP tier (SMB/Mid/Enterprise), region, industry.
- **Channel breakdown:** Google Ads → campaign → ad group; LinkedIn → campaign → creative; Syndication → vendor → asset.
- **Attribution toggle:** First-touch | Last-touch | W-shaped | Linear.

## 6. Alerts & Thresholds
- CAC > $10k for 2 consecutive weeks → Slack alert to CMO.
- Channel ROAS drops >25% WoW → email to Demand Gen lead.
- MQL-to-SQL < 25% for a week → RevOps review trigger.
- Spend pacing >110% of monthly plan by week 3 → finance notification.
- Pipeline contribution < 3× spend MTD → escalation.

## 7. Recommended Tool Stack
- **Looker (primary):** Native BigQuery modeling, LookML governance for attribution logic, strong row-level security for finance/sales splits.
- **Alternative — Power BI:** If org is Microsoft-centric; solid DAX for cohort CAC.
- **Avoid Looker Studio** as primary — insufficient for W-shaped attribution modeling and joins across 3 CRMs.
- **Pipeline:** Fivetran → BigQuery → dbt (attribution models) → Looker.

---

### Insight Questions the Dashboard Answers
1. **Which channel delivers the lowest CAC payback this quarter, and should we reallocate the $80k mix?**
2. **Where is the funnel leaking — is it MQL quality (channel problem) or SQL handoff (sales problem)?**
3. **Is marketing-sourced pipeline tracking to ≥4× spend, and which campaigns are dragging ROAS below target?**

# B2B SaaS Marketing Analytics Dashboard Specification

## 1. Dashboard Overview

**Purpose:** Weekly performance tracking of paid and organic pipeline contribution, channel efficiency, and CAC payback across Google Ads, LinkedIn, and content syndication.

**Audience:** CMO, VP Marketing, demand generation team

**Refresh Cadence:** Daily (with weekly snapshot view for CMO review every Monday 8 AM)

**Business Context:** $80k/month budget across three channels; need to optimize spend allocation and prove pipeline ROI.

---

## 2. Core KPIs

| Metric | Formula | Target | Rationale |
|--------|---------|--------|-----------|
| **Pipeline Contribution by Channel** | Revenue influenced by channel touch (multi-touch attribution) / Total pipeline | 25-30% per channel | Shows which channels drive revenue, not just leads |
| **CAC Payback Period** | (Channel spend MTD) / (MRR from customers acquired that month) | <12 months | Measures capital efficiency; <6 months ideal for SaaS |
| **MQL-to-SQL Conversion Rate** | SQLs created / MQLs generated | 25-35% | Quality gate; validates lead scoring |
| **Cost Per Qualified Lead (CPQL)** | Channel spend / SQLs generated | <$150 | Efficiency metric; flags overspend on low-quality channels |
| **Attribution-Weighted Conversions** | Sum of fractional credit per touchpoint / channel | +10% WoW | Reveals true multi-touch channel value |
| **ROAS (Revenue Attribution)** | Revenue influenced / Total channel spend | >3:1 | Ensures positive ROI; 3:1 is B2B SaaS baseline |
| **Velocity (SQL-to-Won)** | Avg. days from SQL to closed-won deal | <45 days | Identifies bottlenecks in sales handoff |
| **LinkedIn Engagement Rate** | Clicks + Shares + Comments / Impressions | >0.5% | Platform-specific quality check |

---

## 3. Widget Layout

| Position | Widget Name | Chart Type | Primary Metric | Dimensions | Filters |
|----------|------------|-----------|---|---|---|
| **Top Row, Left** | Pipeline by Channel (YTD) | Stacked bar | Pipeline Contribution | Channel, month | Date range, segment |
| **Top Row, Center** | CAC Payback Trend | Line chart | CAC Payback (months) | Channel, week | Last 13 weeks |
| **Top Row, Right** | ROAS by Channel | KPI cards (3) | ROAS | Google Ads / LinkedIn / Content Syndication | MTD |
| **Middle Row, Left** | MQL-to-SQL Waterfall | Funnel | MQL → SQL → Won | Channel | Week-over-week toggle |
| **Middle Row, Right** | CPQL by Channel | Horizontal bar | Cost per SQL | Channel | Last 4 weeks |
| **Bottom Row, Full** | Attribution Heatmap | Heatmap | Touch count | First channel → Last channel | Conversion date range |
| **Bottom Row, Right** | Velocity (Days to Close) | Box plot | Avg. days SQL-to-Won | Channel | Deal stage filter |

---

## 4. Data Sources & Joins

| Source | Tables | Keys | Transformation |
|--------|--------|------|---|
| **HubSpot** | `contacts`, `deals`, `companies` | contact_id, deal_id | MQL/SQL creation date, deal value, lead source |
| **GA4** | `events`, `user_properties` | user_id, session_id | Conversion events mapped to HubSpot contacts via email matching |
| **Salesforce** | `Opportunity`, `Account` | opp_id, account_id | Won deal revenue, close date, amount |
| **Ad Platforms** | Google Ads, LinkedIn API | utm_source, utm_campaign | Cost per click, impressions, clicks |

**Key Joins:**
- GA4 user_id → HubSpot contact_id (via email hash)
- HubSpot deal → Salesforce Opportunity (via external ID)
- Ad platform cost → GA4 conversions (via utm parameters)

---

## 5. Drill-downs & Filters

- **Channel:** Google Ads, LinkedIn, Content Syndication
- **Date Range:** Last 4 weeks (default), custom range, YTD
- **Deal Stage:** MQL, SQL, Opportunity, Won
- **Segment:** Industry vertical, company size, product line
- **Campaign:** Dynamic dropdown per channel

---

## 6. Alerts & Thresholds

| Alert | Condition | Action |
|-------|-----------|--------|
| **Low ROAS** | Channel ROAS < 2.5:1 for 2 consecutive weeks | Email CMO; flag for budget review |
| **MQL Quality Drop** | MQL-to-SQL rate < 20% | Weekly standup agenda item |
| **CAC Payback Extension** | Payback period > 14 months | Pause channel spend; audit targeting |
| **SQL Velocity Delay** | Avg. SQL-to-Won > 60 days | Alert VP Sales; review handoff SLA |

---

## 7. Recommended Tool Stack

**Primary: Google Looker Studio**
- **Rationale:** Native GA4 + HubSpot connectors; cost-effective; weekly refresh cadence sufficient; CMO-friendly UI; no additional licensing.

**Secondary: Tableau or Looker** (if deeper attribution modeling needed)
- Looker: Best if HubSpot is source of truth; Salesforce LookML integrations.
- Tableau: Superior multi-source blending if data warehouse available (Snowflake/BigQuery).

---

## 3 Key Insight Questions This Dashboard Answers

1. **Which channel drives the most pipeline per dollar spent?** (ROAS + Pipeline Contribution comparison)
2. **Are we improving SQL quality week-over-week?** (MQL-to-SQL trend + CPQL)
3. **How quickly are SQLs becoming revenue?** (CAC Payback + Velocity by channel)

# Marketing Analytics Dashboard Specification

## 1. Dashboard Overview
**Purpose:**  
Provide a weekly executive view of how Google Ads, LinkedIn, and content syndication contribute to **pipeline**, **CAC payback**, and **channel efficiency**.

**Primary audience:**  
CMO, VP Marketing, Demand Gen lead, RevOps

**Refresh cadence:**  
- **Weekly executive reporting view**  
- Data refresh: **daily**
- Snapshot comparison: **WoW, MTD, QTD**

---

## 2. Core KPIs

| KPI | Formula | Benchmark/Target |
|---|---|---|
| **Attributed Pipeline ($)** | Sum of open + closed-won opportunity amount using attribution-weighted campaign touch credit | Target: **3–5x monthly spend** in sourced/influenced pipeline |
| **Attributed Revenue ($)** | Closed-won revenue × attribution weight by channel | Target: Positive WoW trend |
| **CAC** | Total channel spend / # new customers acquired | SaaS target: **< 33% of Year 1 ACV** |
| **CAC Payback (months)** | CAC / (Average monthly gross margin per customer) | Target: **< 12 months** |
| **ROAS** | Attributed revenue / ad spend | Target: **> 3.0** blended; higher for Google branded |
| **MQL-to-SQL Rate** | SQLs / MQLs | Target: **25–40%** |
| **SQL-to-Opportunity Rate** | Opportunities / SQLs | Target: **30–50%** |
| **Cost per Attribution-Weighted Conversion** | Spend / attribution-weighted conversions | Target: Decreasing WoW by channel |

---

## 3. Widget Layout

| Grid Position | Widget | Chart Type | Metric | Dimensions | Filters |
|---|---|---|---|---|---|
| Row 1, Col 1-3 | Executive KPI Strip | Scorecards | Attributed Pipeline, CAC, CAC Payback, ROAS | Total | Date, region, segment |
| Row 1, Col 4-6 | Weekly Spend vs Pipeline | Combo chart | Spend vs attributed pipeline | Week, channel | Channel, campaign type |
| Row 2, Col 1-3 | Funnel Conversion | Funnel chart | Lead → MQL → SQL → Opp → Customer | Channel | Date, audience, region |
| Row 2, Col 4-6 | Channel Efficiency | Scatter/bubble | CAC vs ROAS; bubble = spend | Channel | Date, segment |
| Row 3, Col 1-3 | Pipeline Contribution by Channel | Stacked bar | Sourced/influenced pipeline | Google Ads, LinkedIn, Content Syndication | Attribution model |
| Row 3, Col 4-6 | CAC Payback Trend | Line chart | CAC payback months | Week, channel | Segment, geography |
| Row 4, Col 1-3 | Campaign Table | Detail table | Spend, CTR, CPC, MQLs, SQLs, opps, pipeline, ROAS | Campaign | Channel, owner |
| Row 4, Col 4-6 | Landing Page / Content Performance | Table or bar | Sessions, CVR, MQLs, weighted conversions | Landing page, asset | Source/medium |

---

## 4. Data Sources & Joins

| Source | Key Tables | Join Keys | Transformations |
|---|---|---|---|
| **HubSpot** | Contacts, companies, campaigns, lifecycle stages, form submissions | Email, contact ID, UTM params | Standardize lifecycle stage dates; derive MQLs |
| **Salesforce** | Leads, accounts, opportunities, campaign members | Lead/contact ID, account ID, opportunity ID | Map SQL/opportunity/customer stages; revenue and close dates |
| **GA4** | Sessions, source/medium, landing pages, conversions | UTM source/medium/campaign, client ID where available | Channel grouping, attribution-weighted conversions |
| **Ad Platforms** | Google Ads, LinkedIn, syndication spend/export | Campaign ID, campaign name, date | Normalize spend, clicks, impressions, naming conventions |

**Primary joins:**  
- HubSpot contacts ↔ Salesforce leads/contacts via **email/contact ID**
- Opportunities ↔ accounts/contacts via **account ID/contact roles**
- GA4 ↔ CRM/ad data via **UTM campaign/source/medium + date**
- Standardized channel mapping: **Google Ads / LinkedIn / Content Syndication**

---

## 5. Drill-downs & Filters
- **Date:** Last 7 days, 30 days, quarter, custom
- **Channel:** Google Ads, LinkedIn, content syndication
- **Campaign type:** Brand, non-brand, retargeting, sponsored content, gated content
- **Audience/segment:** Enterprise, mid-market, SMB
- **Region:** NA, EMEA, APAC
- **Attribution model:** First-touch, last-touch, multi-touch weighted
- **Opportunity stage:** Open pipeline, closed-won, closed-lost

---

## 6. Alerts & Thresholds
- **CAC payback > 12 months** for any channel
- **ROAS drops > 20% WoW**
- **MQL-to-SQL rate below 25%**
- **Spend up > 15% WoW while pipeline flat/down**
- **Content syndication CPL or cost per weighted conversion exceeds target by 20%**
- **Google Ads or LinkedIn attributed pipeline falls below weekly pacing goal**

---

## 7. Recommended Tool Stack
**Recommended:** **Power BI** or **Looker**  
- **Power BI:** Strong Salesforce/HubSpot connectors, executive-friendly visuals, good for KPI scorecards and scheduled alerts.
- **Looker:** Best if you want governed metrics, reusable attribution definitions, and scalable modeling across GA4 + CRM + ad data.

**Best fit here:** **Looker** if RevOps wants one trusted semantic layer; **Power BI** if speed and Microsoft ecosystem integration matter more.

### 3 Insight Questions
1. Which channel generated the most **attributed pipeline and revenue** this week relative to spend?
2. Are we improving **CAC payback** and **MQL-to-SQL / SQL-to-Opportunity** efficiency by channel?
3. Where is spend increasing without proportional gains in **attribution-weighted conversions, pipeline, or ROAS**?

Of course. Here is a complete marketing analytics dashboard specification tailored to your context.

### **Marketing Pipeline & Efficiency Dashboard Spec**

---

### 1. Dashboard Overview
*   **Purpose:** To provide a single, consolidated view of marketing's contribution to pipeline, customer acquisition cost efficiency, and payback period. This dashboard will inform weekly strategic decisions on channel investment and budget allocation.
*   **Primary Audience:** Chief Marketing Officer (CMO)
*   **Refresh Cadence:** Weekly (refreshed every Monday at 8 AM)

---

### 

# B2B SaaS Marketing Analytics Dashboard

## 1. Dashboard Overview
**Purpose**: Monitor pipeline contribution from Google Ads, LinkedIn, and content syndication; track CAC payback and channel efficiency to optimize $80k/mo spend.  
**Audience**: CMO and executives.  
**Refresh Cadence**: Weekly (Sun EOD).

## 2. Core KPIs
| KPI | Formula | Target Benchmark |
|-----|---------|------------------|
| Total Spend | SUM(Google Ads + LinkedIn + Content Syndication spend) | <$80k/mo |
| Marketing Influenced Pipeline | SUM(Opportunity Amount WHERE Original Source = Marketing channels) | >$500k/mo |
| CAC | Total Spend / New Customers (Closed-Won) | <$10k |
| CAC Payback (Months) | CAC / (Avg MRR per New Customer) | <12 mo |
| Channel ROAS | (Attribution-Weighted Revenue / Channel Spend) | >3x |
| MQL-to-SQL Rate | (SQLs / MQLs) x 100 | >30% |
| LTV:CAC Ratio | (Avg LTV / CAC) | >3x |
| Attribution-Weighted Conversions | SUM(Conversions weighted by attribution model) | >200/mo |

## 3. Widget Layout
12-column grid (mobile-responsive).

| Position | Chart Type | Primary Metric | Dimensions | Filters |
|----------|------------|----------------|------------|---------|
| 1-4 (Top KPI Row) | KPI Cards | Total Spend, Marketing Influenced Pipeline, CAC Payback, Channel ROAS | None | Channel toggle |
| 5-8 (Mid Row) | Line Chart | Spend vs. Pipeline over time | Week | Channels |
| 9-12 (Mid Row) | Bar Chart | ROAS, MQL-to-SQL by Channel | Channel | WoW |
| 13-24 (Bottom) | Table | Attribution-Weighted Conversions | Channel, UTM Source | Date range |

## 4. Data Sources & Joins
- **HubSpot**: Marketing > Contacts/Leads (MQLs via forms), key: `hs_analytics_source`.
- **GA4**: Events/Conversions, export to BigQuery, key: UTM params (`utm_source`, `utm_medium`).
- **Salesforce**: Opportunities/Accounts, key: `Original_Source__c`.
**Joins**: HubSpot `Contact ID` → SF `Contact ID`; GA4 UTMs → HubSpot `hs_analytics_source` (transform: parse UTM to channel: Google Ads/LinkedIn/Content Syndication). Aggregations: Weekly via `week_start_date`.

## 5. Drill-downs & Filters
- **Segments**: Channel (Google Ads, LinkedIn, Content Syndication), Deal Stage (MQL/SQL/Opp).
- **Date Ranges**: WoW, Last 13 Weeks, Custom.
- **Breakdowns**: Click → MQL → SQL → Pipeline funnel; Channel ROAS table.

## 6. Alerts & Thresholds
Email/Slack notifications:  
- Spend >$85k/wk.  
- ROAS <2x any channel.  
- CAC Payback >15 mo.  
- Pipeline drop >15% WoW.  
- MQL-to-SQL <25%.

## 7. Recommended Tool Stack
**Google Looker Studio** (primary) + BigQuery connector. *Rationale*: Native GA4/HubSpot/SF integrations, free for $80k-scale, handles attribution joins, weekly scheduling, executive polish; scales to Looker if needed.

**Insight Questions Answered at a Glance**:  
1. Which channel drives most pipeline per $ spent?  
2. Is CAC payback trending under 12 months?  
3. Where is ROAS slipping WoW?

*(Word count: 478)*