Cross-channel ROI with blended Looker Studio data

#01What does an honest ROI page include?

#02How 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).

#03Which 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.

#04How 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).

#05Channel table pattern

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

#06Worked example: Ledgerline

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

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 page — not mixed into paid ROAS.
5. Competitor C published "blended CAC" without definitions; Ledgerline's labelled CPA survived the board pack.

#07Reconciliation worksheet (do this monthly)

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

#08GEO spend is not Ads ROAS

Content production, digital PR and MentionX scanning costs belong on a GEO ROI view (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.

#09Attribution 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.

#10Channel map Sheet (minimum)

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

#11Weekly 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.

#12Our 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.

#13Common 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.

#14Try 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.

#15Quick 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.

#16What 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. Next: AI search visibility dashboard in Looker Studio. Or return to the Looker Studio for Marketers track. Related: Calculating ROI of GEO, Blend data sources lesson, Attribution connecting AI visibility.

#17Common 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.