Marketing Attribution Dashboard: Track Campaign ROI in Looker Studio

Marketing spend without clear attribution is money thrown into the void. In this guide, I’ll show you how to build a comprehensive marketing attribution dashboard in Looker Studio that answers the question every marketer dreads: “What’s the ROI on our campaigns?”

After building attribution dashboards for marketing agencies and in-house teams, I’ve developed a framework that works across industries. Let’s build one together.

What You’ll Track

By the end of this guide, your dashboard will answer:

• Which channels drive the most conversions?
• What’s the Customer Acquisition Cost (CAC) per channel?
• Which campaigns have the best ROI?
• How does the customer journey look across touchpoints?
• Where should we allocate our budget?

The Attribution Challenge

Before building, understand the attribution problem:

Example Customer Journey:

1. Day 1: Clicks Google Ad → Browses products
2. Day 3: Visits via Facebook retargeting → Adds to cart
3. Day 5: Direct visit → Purchases

Question: Which channel gets credit for the sale?

Attribution Models:

Model	Description	Best For
Last Click	100% credit to last touchpoint	Simple, conservative
First Click	100% credit to first touchpoint	Brand awareness focus
Linear	Equal credit to all touchpoints	Balanced view
Time Decay	More credit to recent touchpoints	Sales-focused
Position-Based	40% first, 40% last, 20% middle	Comprehensive

We’ll build a dashboard that supports multiple models.

Data Requirements

Your data source needs these fields:

Field	Type	Description
conversion_id	Text	Unique conversion/sale ID
conversion_date	Date	When conversion happened
conversion_value	Number	Revenue or value
channel	Text	Marketing channel
campaign	Text	Campaign name
touchpoint_date	Date	When touchpoint occurred
touchpoint_order	Number	1st, 2nd, 3rd touch, etc.
marketing_spend	Number	Cost per campaign

Sample Data Structure

Conversions Table:

conversion_id	conversion_date	conversion_value	customer_id
CONV-001	2024-01-15	150	CUST-123
CONV-002	2024-01-16	75	CUST-456

Touchpoints Table:

touchpoint_id	conversion_id	channel	campaign	touchpoint_date	touchpoint_order
TP-001	CONV-001	Google Ads	Brand Campaign	2024-01-10	1
TP-002	CONV-001	Facebook	Retargeting	2024-01-13	2
TP-003	CONV-001	Direct	-	2024-01-15	3

Spend Table:

date	channel	campaign	spend
2024-01-15	Google Ads	Brand Campaign	500
2024-01-15	Facebook	Retargeting	300

Step 1: Prepare Attribution Data in BigQuery

If you’re using BigQuery, here’s a query to prepare attribution data:

-- Create attributed conversions with last-click model
WITH conversions AS (
  SELECT
    conversion_id,
    conversion_date,
    conversion_value,
    customer_id
  FROM `project.dataset.conversions`
),

touchpoints AS (
  SELECT
    conversion_id,
    channel,
    campaign,
    touchpoint_date,
    ROW_NUMBER() OVER (
      PARTITION BY conversion_id 
      ORDER BY touchpoint_date DESC
    ) AS reverse_order,
    ROW_NUMBER() OVER (
      PARTITION BY conversion_id 
      ORDER BY touchpoint_date ASC
    ) AS touch_order,
    COUNT(*) OVER (PARTITION BY conversion_id) AS total_touches
  FROM `project.dataset.touchpoints`
),

-- Last Click Attribution
last_click AS (
  SELECT
    c.conversion_id,
    c.conversion_date,
    c.conversion_value,
    t.channel,
    t.campaign,
    'Last Click' AS attribution_model,
    c.conversion_value AS attributed_value
  FROM conversions c
  JOIN touchpoints t ON c.conversion_id = t.conversion_id
  WHERE t.reverse_order = 1
),

-- First Click Attribution
first_click AS (
  SELECT
    c.conversion_id,
    c.conversion_date,
    c.conversion_value,
    t.channel,
    t.campaign,
    'First Click' AS attribution_model,
    c.conversion_value AS attributed_value
  FROM conversions c
  JOIN touchpoints t ON c.conversion_id = t.conversion_id
  WHERE t.touch_order = 1
),

-- Linear Attribution
linear AS (
  SELECT
    c.conversion_id,
    c.conversion_date,
    c.conversion_value,
    t.channel,
    t.campaign,
    'Linear' AS attribution_model,
    c.conversion_value / t.total_touches AS attributed_value
  FROM conversions c
  JOIN touchpoints t ON c.conversion_id = t.conversion_id
)

-- Combine all models
SELECT * FROM last_click
UNION ALL
SELECT * FROM first_click
UNION ALL
SELECT * FROM linear

This gives you attributed values for three models. Connect this query to Looker Studio.

Step 2: Set Up the Dashboard Layout

+--------------------------------------------------+
|        📊 Marketing Attribution Dashboard         |
|  [Date Range] [Attribution Model] [Channel]       |
+------------+------------+------------+------------+
| Total      | Total      | Avg        | ROAS       |
| Revenue    | Spend      | CAC        |            |
+------------+------------+------------+------------+
|           Revenue by Channel                      |
+-----------------------+---------------------------+
| Campaign Performance  | Channel Trend Over Time   |
+-----------------------+---------------------------+
|           Attribution Path Analysis               |
+--------------------------------------------------+

Step 3: Create KPI Scorecards

Scorecard 1: Total Attributed Revenue

1. Add a Scorecard
2. Configure:
   • Metric: attributed_value (SUM)
   • Format: Currency

Scorecard 2: Total Marketing Spend

You’ll need to join spend data or create a separate data source:

SELECT
  SUM(spend) AS total_spend
FROM `project.dataset.marketing_spend`
WHERE date BETWEEN @start_date AND @end_date

Scorecard 3: Customer Acquisition Cost (CAC)

Create a calculated field:

SUM(spend) / COUNT_DISTINCT(customer_id)

Or in BigQuery:

SELECT
  SUM(spend) / COUNT(DISTINCT customer_id) AS cac
FROM ...

Scorecard 4: Return on Ad Spend (ROAS)

Create calculated field:

SUM(attributed_value) / SUM(spend)

Display as a number with 1 decimal place (e.g., “3.5x”).

Step 4: Build Channel Performance Chart

1. Add a Bar Chart
2. Configure:
   • Dimension: channel
   • Metrics:
     • attributed_value (SUM) - labeled “Revenue”
     • spend (SUM) - labeled “Spend”
3. Add a calculated metric for ROI:

   (SUM(attributed_value) - SUM(spend)) / SUM(spend) * 100

4. Sort by attributed_value, descending

Color Coding for Quick Insights

In the Style panel:

• Revenue bars: Green
• Spend bars: Gray
• Add conditional formatting for ROI (green > 100%, red < 0%)

Step 5: Campaign Performance Table

1. Add a Table
2. Configure:
   • Dimension: campaign
   • Metrics:
     • attributed_value (SUM)
     • spend (SUM)
     • conversion_id (COUNT DISTINCT) - “Conversions”
     • CAC (calculated)
     • ROAS (calculated)
3. Add:
   • Sorting by revenue
   • Conditional formatting (highlight top performers)
   • Pagination for many campaigns

Calculated Metrics for the Table

Cost per Conversion:

SUM(spend) / COUNT_DISTINCT(conversion_id)

ROAS:

SUM(attributed_value) / SUM(spend)

Step 6: Add Attribution Model Filter

This is key! Let users switch between attribution models.

1. Add a Drop-down list filter
2. Configure:
   • Control field: attribution_model
3. Position at the top of the dashboard

Now users can see how metrics change with different attribution models.

Why This Matters

Channel	Last Click Revenue	First Click Revenue	Linear Revenue
Google Ads	$50,000	$80,000	$65,000
Facebook	$30,000	$15,000	$22,500
Direct	$40,000	$5,000	$22,500

Different models tell different stories. Last Click undervalues awareness channels.

Step 7: Channel Trend Over Time

1. Add a Time Series Chart
2. Configure:
   • Dimension: conversion_date
   • Breakdown Dimension: channel
   • Metric: attributed_value (SUM)
3. Style:
   • Use distinct colors for each channel
   • Add legend
   • Enable comparison if needed

This shows how channel performance changes over time.

Step 8: Build the Customer Journey Analysis

For a multi-touch view, create a Sankey-style visualization or a Table showing paths:

Journey Path Table

First, prepare the data in BigQuery:

SELECT
  STRING_AGG(channel, ' → ' ORDER BY touchpoint_order) AS journey_path,
  COUNT(DISTINCT conversion_id) AS conversions,
  SUM(conversion_value) AS total_revenue
FROM touchpoints t
JOIN conversions c USING (conversion_id)
GROUP BY conversion_id
ORDER BY conversions DESC
LIMIT 20

Then in Looker Studio:

1. Add a Table
2. Configure:
   • Dimension: journey_path
   • Metrics: conversions, total_revenue
3. This shows common paths like:
   • Google Ads → Facebook → Direct: 150 conversions
   • Facebook → Google Ads → Email: 89 conversions

Step 9: Add Spend Efficiency Metrics

Cost per Channel Chart

1. Add a Combo Chart
2. Configure:
   • Dimension: channel
   • Bar Metric: spend (SUM)
   • Line Metric: ROAS (calculated)
3. This shows spend alongside efficiency

Budget Allocation Recommendations

Create a calculated field for budget recommendations:

CASE
  WHEN ROAS > 3 THEN "Increase Budget"
  WHEN ROAS BETWEEN 1 AND 3 THEN "Maintain"
  ELSE "Review/Reduce"
END

Add this as a column in your campaign table.

Advanced: Multi-Touch Attribution Weights

For position-based attribution (40/20/40):

SELECT
  conversion_id,
  channel,
  campaign,
  CASE
    WHEN touch_order = 1 THEN conversion_value * 0.4  -- First touch: 40%
    WHEN reverse_order = 1 THEN conversion_value * 0.4  -- Last touch: 40%
    ELSE conversion_value * 0.2 / (total_touches - 2)  -- Middle: split 20%
  END AS attributed_value,
  'Position Based' AS attribution_model
FROM touchpoints
JOIN conversions USING (conversion_id)

Dashboard Filters Summary

Add these filters for flexibility:

1. Date Range - Filter all data by time period
2. Attribution Model - Switch between models
3. Channel - Focus on specific channels
4. Campaign - Drill into specific campaigns

Key Metrics Reference

Metric	Formula	What It Tells You
ROAS	Revenue / Spend	Efficiency of ad spend
CAC	Spend / New Customers	Cost to acquire a customer
CPC	Spend / Clicks	Cost per click
Conversion Rate	Conversions / Sessions	% who convert
Attributed Revenue	Model-based revenue	True channel contribution

Common Issues and Fixes

Issue	Cause	Solution
Revenue doesn’t match	Attribution splits value	Check model selection
Missing channels	Tracking gaps	Audit UTM parameters
Zero spend	Join failed	Check date alignment
ROAS too high	Organic included	Filter to paid channels

What Makes a Great Attribution Dashboard

Based on building dozens of marketing dashboards:

1. Support multiple models - No single model is “right”
2. Show the path - Customer journeys matter
3. Connect spend to results - Always show ROI
4. Enable action - Include recommendations
5. Be flexible - Users need to explore data

Need Help With Your Attribution Dashboard?

Marketing attribution is complex. If you need help:

• Connecting multiple data sources (GA4, ad platforms, CRM)
• Building custom attribution models
• Creating executive-level reporting
• Integrating with BigQuery for advanced analysis

Schedule a free consultation to discuss your marketing analytics needs.

---

Want more marketing analytics guides? Check out our E-commerce Dashboard Tutorial and BigQuery Cost Optimization.