# Cross-channel ROI with blended Looker Studio data

> Blend Ads, GA4 and Sheets targets for an honest marketing ROI scorecard without inflated ROAS.

Source: https://mentionx.ai/learn/looker-studio/looker-studio-cross-channel-roi-blend  
Author: Dipuck Jones, founder of MentionX  
Updated: 5 November 2026  
Level: Practitioner

**Cross-channel ROI in Data Studio is a blend problem dressed as a strategy problem. You need Ads cost, GA4 (or CRM) outcomes, and a targets Sheet — joined on a grain that does not invent money. This lesson builds a weekly ROI scorecard, a channel efficiency table and a reconciliation checklist. For the GEO angle, pair with [Calculating ROI of GEO](https://mentionx.ai/learn/measurement/calculating-roi-of-geo).**

> **Key takeaways**
>
> - Join on **Date** first; add campaign keys only with a mapping Sheet.
> - Put **targets in Sheets**, not hard-coded chart text.
> - Keep **pure-source control scorecards** on the page.
> - Label the **outcome metric** (Ads conversion vs GA4 key event vs CRM opportunity).
> - Cap complexity: three tables beat five clever ones.

*Checked 5 November 2026 (CET) against Google Cloud Data Studio documentation, the Data Studio product page, the Looker Studio-is-Data-Studio blog post, connector help pages and the Analytics Help pages cited in Sources. Google renames and moves features often, so confirm labels and limits in your own account. "Our view" marks MentionX opinion. Ledgerline is a fictional company used for illustration.*

## What does an honest ROI page include?

| Block | Purpose |
|---|---|
| Spend (Ads) | What we paid |
| Outcome (chosen source) | What we got |
| Efficiency | CPA / ROAS / cost per key event |
| Target | What good looks like |
| Variance | Actual vs target |
| Footnotes | Join grain + conversion definition |

## How do you structure the blend?

**Recommended three-table blend**

1. **Left:** Google Ads — Date, Cost, Clicks, Conversions (primary), Conversion value.
2. **Middle:** GA4 — Date, Key events, Sessions (optional).
3. **Right:** Sheets `targets` — Date (week start), Target CPA, Target spend, Target key events.

Join Ads ⟕ GA4 on Date. Join result ⟕ targets on week-start Date (or use a week dimension calculated the same way on both sides).

| Join | Operator | Why |
|---|---|---|
| Ads → GA4 | Left outer | Keep spend even when GA4 lags |
| → Targets | Left outer | Keep actuals when a week lacks a target row |

## Which ROI formulas belong at chart level?

Because blend metrics become unaggregated numeric dimensions, build rates in the chart:

- `CPA_ads = SUM(Cost) / NULLIF(SUM(Conversions), 0)`
- `Cost_per_key_event = SUM(Cost) / NULLIF(SUM(Key events), 0)`
- `ROAS = SUM(Conversion value) / NULLIF(SUM(Cost), 0)`
- `Spend_vs_target = SUM(Cost) / NULLIF(SUM(Target spend), 0)`

Write the formula names into a metric dictionary Sheet linked from the cover.

## How do you stop inflated ROAS?

1. Control scorecard: Ads Cost (no blend).
2. Control scorecard: Ads Conversions (no blend).
3. Blended Cost must match control within rounding.
4. Only then compute ROAS.
5. If you join on Campaign name, run a mismatch count (full outer join diagnostic page).

## Channel table pattern

| Channel (from Sheets map or Ads channel type) | Cost | Outcome | CPA | Target CPA | Status |
|---|---|---|---|---|---|
| Search | | | | | |
| Performance Max | | | | | |
| Demand Gen | | | | | |
| Organic / AI Assistant (GA4 only) | — | Key events | — | — | Report separately |

Organic and AI Assistant have no Ads Cost. Do not force them into ROAS. Show them as volume and conversion-rate rows.

## Worked example: Ledgerline

*Ledgerline is a fictional accounts-payable software company. Details are illustrative.*

| Week | Ads cost | Ads conv. | GA4 key events | Target CPA | Actual CPA (illustrative) |
|---|---|---|---|---|---|
| W40 | $12,100 | 78 | 290 | $160 | $155 |
| W41 | $11,400 | 61 | 275 | $160 | $187 |
| W42 | $13,200 | 84 | 310 | $160 | $157 |

### Walkthrough

1. First blend used Campaign name and invented a miracle ROAS. Control Cost exposed the fan-out.
2. Finance insisted on Date-level ROI for the exec page.
3. Targets Sheet lived in a shared Drive folder with version notes.
4. GEO spend from content and MentionX scans stayed on a separate [GEO ROI](https://mentionx.ai/learn/measurement/calculating-roi-of-geo) page — not mixed into paid ROAS.
5. Competitor C published "blended CAC" without definitions; Ledgerline's labelled CPA survived the board pack.

> **Pro move:** add a boolean scorecard or traffic-light calculated field: `CPA status = CASE WHEN Actual CPA <= Target CPA THEN "On track" ELSE "Over" END`. Executives skim status before numbers.

## Reconciliation worksheet (do this monthly)

| Check | Pure source | Blended | Pass? |
|---|---|---|---|
| Cost 28d | Ads UI / Ads chart | Blend Cost | |
| Conversions 28d | Ads chart | Blend Conversions | |
| Key events 28d | GA4 chart | Blend Key events | |
| Target spend 28d | Sheet sum | Blend target | |

If any pure vs blend gap exceeds an agreed tolerance (for example 1%), freeze ROAS publishing until fixed.

## GEO spend is not Ads ROAS

Content production, digital PR and MentionX scanning costs belong on a GEO ROI view ([Calculating ROI of GEO](https://mentionx.ai/learn/measurement/calculating-roi-of-geo)), not as a fake channel inside paid ROAS. Show them as separate investment lines on the exec page if leadership asks for "all marketing efficiency" in one glance.

## Attribution humility footnote (paste on the page)

```
This page joins Ads cost to outcomes at {grain}. It is a planning scorecard, not a user-level attribution model.
GA4 key events and Ads conversions are different instruments. Expect gaps.
GEO / content investment is reported separately.
```

Without that footnote, someone will present blended CPA as scientific truth in a budget meeting.

## Channel map Sheet (minimum)

| source_system | raw_value | report_channel |
|---|---|---|
| ads | SEARCH | Search |
| ads | PERFORMANCE_MAX | Performance Max |
| ga4 | Organic Search | Organic Search |
| ga4 | AI Assistant | AI Assistant |
| ga4 | Direct | Direct |
| sheet | geo_program | GEO program |

Force every efficiency chart to use `report_channel`. Otherwise Ads "Search" and GA4 "Organic Search" get mashed in stakeholder storytelling.

## Weekly ROI meeting script (20 minutes)

1. Control scorecards match? (2 min)
2. Spend vs target (3 min)
3. CPA / cost per key event vs target (5 min)
4. Channel table outliers (5 min)
5. Actions with owners (5 min)

If step 1 fails, stop. Debugging a blend during the meeting trains leadership to distrust the page. Fix offline, then reconvene.

## Our view

Our view: blends are for scorecards, not for inventing a company-wide attribution religion. When leadership wants user-level truth, graduate to warehouse models and an agreed attribution approach — keep Data Studio honest about grain.

## Common mistakes

- **ROAS with no conversion label.**
- **Joining paid and organic into one efficiency metric.**
- **Editing targets inside chart text boxes.**
- **Five-table blends for a weekly scorecard.**
- **Hiding the control scorecards after the first month.**

## Try it yourself

Build a three-table Date blend. Prove blended Cost equals pure Ads Cost for last 28 days. Only then add CPA vs target.

## Quick quiz

**1. Where should marketing targets live?**

*Answer:* In a Google Sheet (or warehouse table) editable by finance/ops — not hard-coded in charts.

**2. What is the first join grain to trust?**

*Answer:* Date (or ISO week), before campaign keys.

**3. Why keep organic out of ROAS?**

*Answer:* There is no Ads Cost; forcing a ROAS invents a denominator problem.

## What should you check before the next lesson?

- Control scorecards visible.
- Outcome metric labelled on the cover.
- Targets Sheet owners documented.

Previous: [Build a Search Console dashboard in Looker Studio](https://mentionx.ai/learn/looker-studio/looker-studio-search-console-dashboard). Next: [AI search visibility dashboard in Looker Studio](https://mentionx.ai/learn/looker-studio/looker-studio-ai-visibility-dashboard). Or return to the [Looker Studio for Marketers track](https://mentionx.ai/learn/looker-studio/looker-studio-for-marketers). Related: [Calculating ROI of GEO](https://mentionx.ai/learn/measurement/calculating-roi-of-geo), [Blend data sources lesson](https://mentionx.ai/learn/looker-studio/looker-studio-blending-data-sources), [Attribution connecting AI visibility](https://mentionx.ai/learn/measurement/attribution-connecting-ai-visibility-pipeline).

## Common questions

**Can I blend Google Ads cost with GA4 revenue in Data Studio?** Yes, within the five-table blend limit. Join on a shared grain such as Date or a mapped campaign key, use left outer carefully, and keep control scorecards for each source.

**Why does blended ROAS look higher than the Ads UI?** Often join fan-out or mismatched conversion definitions. Compare blended Cost to a pure Ads Cost scorecard before you trust ROAS.

**Should target CPA live in the blend?** Keep targets in Google Sheets and blend or join them on Date or Channel so finance can edit targets without touching Ads or GA4 connectors.
## Sources

- Google Cloud, [How blends work in Data Studio](https://docs.cloud.google.com/data-studio/how-blends-work)
- Google Cloud, [About calculated fields](https://docs.cloud.google.com/data-studio/about-calculated-fields)
- All accessed 5 Nov 2026.

## Frequently asked questions

### Can I blend Google Ads cost with GA4 revenue in Data Studio?

Yes, within the five-table blend limit. Join on a shared grain such as Date or a mapped campaign key, use left outer carefully, and keep control scorecards for each source.

### Why does blended ROAS look higher than the Ads UI?

Often join fan-out or mismatched conversion definitions. Compare blended Cost to a pure Ads Cost scorecard before you trust ROAS.

### Should target CPA live in the blend?

Keep targets in Google Sheets and blend or join them on Date or Channel so finance can edit targets without touching Ads or GA4 connectors.

---

Written and reviewed by Dipuck Jones for MentionX Learn. The HTML version of this page: https://mentionx.ai/learn/looker-studio/looker-studio-cross-channel-roi-blend