Marketing Dashboard Setup: GA4, Ad Platforms, CRM & Email to Looker Studio/Metabase

Purpose

This recipe produces a live, auto-refreshing marketing dashboard that unifies data from GA4 (web traffic and conversions), ad platforms (Google Ads, Meta Ads, LinkedIn Ads spend and performance), CRM (pipeline and revenue attribution), and email marketing (campaign engagement and conversion) into a single interactive view — deployed in Looker Studio or Metabase with automated weekly PDF reports to stakeholders. [src1]

Prerequisites

• GA4 property with active data collection (minimum 30 days of data) — configured with conversion events and UTM parameters
• Google Cloud project with BigQuery API enabled — Google Cloud Console (free tier: 10 GB storage, 1 TB queries/mo)
• Ad platform access — viewer/analyst role on Google Ads, Meta Ads Manager, and/or LinkedIn Campaign Manager
• CRM access — API key from HubSpot or Salesforce connected app
• Email platform access — API key from Mailchimp, Klaviyo, or HubSpot email
• Dashboard tool — Looker Studio (free) or Metabase (self-hosted or cloud)

Constraints

• GA4 BigQuery export adds latency: streaming export has ~10 min delay; daily export batches at end of day. Dashboard data is never truly real-time. [src1]
• Looker Studio blended data limited to 5 data sources per blend and 10 blends per report. Complex multi-source dashboards require pre-joined tables in BigQuery. [src2]
• Supermetrics costs compound: each destination (Sheets, Looker Studio, BigQuery) is a separate subscription at $39-299/mo. [src4]
• UTM parameter consistency is critical: campaign names must match exactly across GA4, ad platforms, and CRM for accurate attribution.
• CRM data exports must respect PII rules: aggregate pipeline and revenue data only, exclude personal emails and phone numbers.
• Dashboard must target under 10-second load time with 90 days of data. Pre-aggregate in BigQuery for historical views.

Tool Selection Decision

Which path?
├── User is non-technical AND budget = free
│   └── PATH A: Looker Studio + Native Connectors (GA4, Google Ads only)
├── User is non-technical AND budget > $0
│   └── PATH B: Looker Studio + Supermetrics (all sources)
├── User is semi-technical or developer AND budget = free
│   └── PATH C: Looker Studio + BigQuery + Free ETL (Airbyte/scripts)
└── User is developer AND budget > $0
    └── PATH D: Metabase + BigQuery + Paid Connectors (full control)

Execution Flow

Step 1: Data Source Inventory and Access Verification

Duration: 30-60 minutes · Tool: Spreadsheet

Catalog every data source, confirm access credentials, and document key metrics each source provides. This prevents mid-build discovery of missing access.

Verify: All data sources accessible and returning data. · If failed: Request missing credentials from the relevant team before proceeding.

Step 2: Connector Setup and Data Extraction

Duration: 1-2 hours · Tool: Looker Studio, Supermetrics, or BigQuery

Path A/B: Add GA4 and Google Ads via native Looker Studio connectors. For Meta/LinkedIn/CRM/email, add Supermetrics connector (Path B).

Path C/D: Enable GA4 BigQuery export (GA4 Admin > BigQuery Links), then set up extraction scripts or Airbyte pipelines for each non-Google source. [src1]

-- Verify GA4 BigQuery export is working:
SELECT COUNT(*) as event_count,
       FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d', event_date)) as date
FROM `your-project.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
GROUP BY date ORDER BY date DESC;

Verify: Each data source returns recent data (within 48 hours). · If failed: GA4 BigQuery export takes up to 24 hours after enabling. For API errors, verify credentials.

Step 3: Data Modeling and Joining

Duration: 1-3 hours · Tool: BigQuery SQL (Path C/D) or Looker Studio blending (Path A/B)

Create a unified data model joining traffic, spend, conversions, pipeline, and email data on common keys (date, UTM campaign, source/medium).

-- Core marketing performance model (BigQuery)
CREATE OR REPLACE TABLE marketing_analytics.daily_channel_performance AS
WITH ga4_traffic AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) AS date,
    traffic_source.source AS source, traffic_source.medium AS medium,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNTIF(event_name = 'session_start') AS sessions,
    COUNTIF(event_name IN ('purchase','sign_up','generate_lead')) AS conversions
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX >= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
  GROUP BY 1, 2, 3
),
ad_spend AS (
  SELECT date, source, medium, SUM(spend) AS spend, SUM(clicks) AS ad_clicks
  FROM marketing_staging.unified_ad_spend GROUP BY 1, 2, 3
)
SELECT t.*, COALESCE(a.spend,0) AS spend, COALESCE(a.ad_clicks,0) AS ad_clicks,
  SAFE_DIVIDE(COALESCE(a.spend,0), NULLIF(t.conversions,0)) AS cpa
FROM ga4_traffic t LEFT JOIN ad_spend a USING (date, source, medium);

Verify: Joined table has spend aligned with conversions. Check for UTM mismatches where spend > 0 but conversions = 0. · If failed: Apply LOWER() and TRIM() to all join keys.

Step 4: Dashboard Design and KPI Widget Configuration

Duration: 1-2 hours · Tool: Looker Studio or Metabase

Build the dashboard with 4 pages: Executive Summary (spend, revenue, ROAS, CPA scorecards and trends), Acquisition Channels (traffic by source, ad performance tables), Conversion Funnel (visitors > leads > MQLs > SQLs > closed-won), and Email Performance (open rate, CTR, campaign table). [src5] [src6]

Verify: All widgets display data. Date range filter updates all charts. Compare totals to native platform numbers (expect 5-15% variance). · If failed: Check data source connections and date range coverage.

Step 5: Calculated Metrics and Goal Tracking

Duration: 30-60 minutes · Tool: Looker Studio calculated fields or BigQuery views

Verify: Calculated fields return reasonable values. · If failed: Wrap divisions in SAFE_DIVIDE or NULLIF.

Step 6: Automated Reporting and Alerting

Duration: 30 minutes · Tool: Looker Studio scheduled email or Metabase subscriptions

Schedule weekly PDF delivery (Mondays at 8 AM) to stakeholders. Set up anomaly alerts: spend exceeding daily budget by 20%, CPA exceeding target by 50% for 3 consecutive days, conversion rate dropping below historical average.

Verify: Trigger a test email. Confirm all pages render in PDF format. · If failed: Reduce widgets per page or switch to landscape orientation.

Output Schema

{
  "output_type": "marketing_dashboard",
  "format": "Interactive dashboard URL + scheduled PDF",
  "pages": [
    {"name": "Executive Summary", "widgets": ["total_spend","total_revenue","blended_roas","blended_cpa","spend_vs_revenue_trend","channel_breakdown"]},
    {"name": "Acquisition Channels", "widgets": ["traffic_by_source","ad_performance_table","platform_comparison","cpl_by_channel"]},
    {"name": "Conversion Funnel", "widgets": ["funnel_viz","stage_conversion_rates","pipeline_by_source"]},
    {"name": "Email Performance", "widgets": ["email_scorecards","campaign_table","list_growth_trend"]}
  ],
  "data_refresh": "daily (BigQuery export) or real-time (native connectors)",
  "report_schedule": "weekly PDF via email"
}

Quality Benchmarks

If below minimum: Expand data source connections, pre-aggregate in BigQuery to improve load time, or simplify dashboard to fewer widgets per page.

Error Handling

Cost Breakdown

Anti-Patterns

Wrong: Building the dashboard before standardizing UTM parameters

Campaign tracking codes that differ across platforms make it impossible to join spend data with conversion data. The dashboard shows disconnected silos instead of a unified view. [src2]

Correct: Enforce UTM naming convention first

Create a UTM naming document with exact formats: lowercase, hyphens as separators, consistent structure. Audit all active campaigns before building the dashboard. Use LOWER() in SQL as a safety net.

Wrong: Connecting raw GA4 event data directly to Looker Studio for complex dashboards

GA4’s event-based schema with nested arrays causes slow queries and forces complex calculated fields. Dashboards with more than 3-4 widgets from raw GA4 data become unusably slow. [src1]

Correct: Export GA4 to BigQuery and model the data first

Use BigQuery export to flatten and aggregate GA4 data into clean tables. Point the dashboard at pre-modeled tables for 2-3 second loads instead of 30+.

Wrong: Adding every possible metric to the dashboard

Dashboards with 50+ widgets across 10 pages get ignored. Stakeholders cannot find the metrics that matter and stop checking within weeks. [src5]

Correct: Start with 4 pages and 5-7 widgets per page

Focus on metrics tied to business decisions: spend, revenue, ROAS, CPA, funnel conversion rates. Add detail pages only when stakeholders request them.

When This Matters

Use when an agent needs to produce a working multi-channel marketing dashboard — not a document describing what metrics to track. Requires active GA4 data collection and at least one ad platform running campaigns. Best executed after marketing strategy is defined and campaigns are live with 30+ days of data.