TL;DR: Match Google Ads, GA4, and CRM data at one row per date, account, and campaign before blending. Check source totals after every join, keep unmatched records visible, and calculate ratios from totals only after the rows reconcile.
Why does Looker Studio data blending duplicate rows?
Looker Studio data blending duplicates values when a join key matches several rows in another table. A join matches records using shared fields; it does not know whether repeating a cost or revenue value makes business sense. Google documents this multiple-match behavior.
Google Ads holds spend and clicks. Google Analytics 4, or GA4, describes website activity. A customer relationship management system, or CRM, holds leads and sales records. Blending these sources can put their measures beside each other, but it does not turn their different definitions into one measure.
A search such as “looker studio blend google ads and ga4 duplicate rows” often begins with a simple symptom: cost is correct in a source chart and too high in a combined chart. Check the join before changing tracking tags. Reliable totals are also a prerequisite for a defensible business process automation ROI calculation: fixing a reporting error is not itself new revenue.
Here is an illustrative example, with all rows for the same date, account, and campaign:
| Input table | Rows at the selected dimensions | Values before joining |
|---|---|---|
| Google Ads | One campaign row | $100 cost |
| GA4 | Two device rows | 40 mobile sessions and 60 desktop sessions |
| CRM | Three lead rows | Three different lead IDs |
If device and lead ID remain dimensions in the blend tables, joining only on date, account, and campaign creates six matched combinations: one × two × three. Summing repeated values produces $600 cost and 300 sessions. There were still only $100 of ads and 100 sessions. A distinct lead count might remain three while the money is wrong, which makes this bug easy to miss.
The remedy is to prepare each input at the intended reporting level. For this report, combine the device rows into 100 sessions and the lead records into a lead count of three before the join. Do not add more detail merely to make rows look unique.
Where a campaign-level blend is useful
A campaign-level blend is useful when a small team needs spend, website activity, and sales outcomes in the same operational view. Use it to spot questions for investigation, with the date basis and source named beside each metric.
| Business | Useful comparison | Detail that belongs elsewhere |
|---|---|---|
| Home services | Campaign spend, sessions, and new estimate leads | Individual appointments and technician visits |
| B2B services | Paid traffic and CRM-qualified leads created that week | Every contact attached to one deal |
| E-commerce | Campaign spend and separately labeled order revenue | Order line items that repeat an order total |
| Local appointments | Ad clicks, booking events, and unique booking records | Reminder attempts and status history |
| Subscription business | Acquisition spend and new accounts | Recurring invoices across several billing periods |
Start with one reporting question, such as “Which campaigns spent money but produced no recorded leads?” That needs a different data contract from “Which acquisition cohort has paid back?” The broader marketing dashboard guide helps choose the weekly decisions before adding charts.
Which join keys should Ads, GA4, and CRM share?
For a daily campaign report, use a reporting date, Google Ads customer ID, and campaign ID in every prepared table. Each complete combination must identify at most one row per input. This level of detail is called the table's grain.
| Shared field | Google Ads side | GA4 side | CRM preparation |
|---|---|---|---|
| Reporting date | Account reporting date | Property reporting date | Chosen business date converted before grouping |
| Ads customer ID | Customer/account ID | Session Google Ads customer ID | Stored ID or verified account mapping |
| Campaign ID | Campaign ID | Session Google Ads campaign ID | Captured ID or reviewed campaign mapping |
GA4 defines sessionGoogleAdsCampaignId and sessionGoogleAdsCustomerId as session attribution fields. Its manual campaign ID is a separate field populated by utm_id. Match the actual values and scope, not just similar field names. These definitions are in Google's GA4 dimensions and metrics reference.
Check that your connector exposes the required fields together with the chosen metrics. If it does not, prepare a compatible export instead of silently substituting a campaign name. A manual campaign tag can be mapped to an Ads campaign ID, but the mapping must be explicit and unique. Keep IDs as consistently formatted text, remove accidental whitespace, and standardize customer-ID separators.
Campaign names are display labels. They can change, repeat across accounts, or differ from CRM tags. If labels are your only starting point, use a reviewed mapping table with an owner and validity dates. Resolve those dated mappings upstream: Looker Studio join conditions support equality, not date-range rules. See Google's blend configuration reference.
Do not map every missing campaign to one shared “unknown” ID and join those records together. Several unrelated unknowns can create a new multiplication problem. Keep unmapped rows in a separate exception table with their source, amount, date, and reason.
Decide what the date means
Use lead-created date for new-lead counts and close date for closed-won revenue. If you need both, roll up those two CRM facts separately and combine them into one unique campaign-date table before this blend. Label the columns “Leads created” and “Revenue closed”; they describe activity on that date, not one matched acquisition cohort.
Google Ads and GA4 also use different time-zone settings: the Ads account and Analytics property settings can differ. Review those reporting time zones before comparing daily totals. Changing a date's display format does not move an already aggregated day's activity into a different time zone. Rebuild the daily export from finer timestamps when available, or disclose the mismatch and use a coarser comparison.
Keep a separate field for refresh time. “Revenue closed on November 20” and “CRM export refreshed on November 21” answer different questions. A date control should never make stale source data look current.
How to blend data sources in Looker Studio?
Prepare unique campaign-date tables, join two sources first, reconcile them, and then add the third. Keep standalone source charts beside the blended chart during setup so every change has a visible control total.
This seven-step pattern assumes reporting access and a CRM export with usable campaign attribution.
1. Write the reporting contract
Record the accounts, date range, time zones, campaign mapping rule, and refresh timestamps. Define each metric in a sentence. For example, “Leads created counts distinct CRM lead IDs by creation date after excluding test records.”
Use one reporting currency before summing money across accounts. Convert mixed currencies upstream with a documented exchange-rate source and rate date, or keep separate currency views. Changing the field label to USD does not convert its values.
Choose one cost source and one revenue source. Do not add Google Ads cost to a GA4 imported ad-cost field: they may describe the same spend. Keep Ads conversions, GA4 key events, and CRM leads in separate columns. Their differences require conversion-count reconciliation, even after a join is correct.
2. Prepare the three input tables
For Ads, retain date, customer ID, campaign ID, cost, and clicks. For GA4, retain the matching session campaign dimensions, date, and sessions. Add key events only after verifying compatible fields and documenting which event is counted.
For CRM, deduplicate the underlying lead and deal records by their stable IDs before summarizing. Resolve which campaign owns each record using a written attribution rule. A deal with three contacts must not contribute its whole value three times. A multi-campaign model needs explicit allocation weights upstream.
In Google Sheets, use separate raw tabs and prepared tabs. A pivot table or QUERY can group by the three keys and sum additive values. Count leads from an input that has already been reduced to one eligible record per lead ID. If the CRM export contains status history, select the valid record for the reporting cutoff before grouping.
A compact prepared table could look like this:
| report_date | ads_customer_id | campaign_id | leads_created | revenue_closed_usd |
|---|---|---|---|---|
| 2025-11-20 | 1234567890 | 111 | 3 | 0 |
| 2025-11-21 | 1234567890 | 111 | 1 | 2400 |
These are illustrative values, not a customer result. The second row's revenue can come from a lead created earlier. It must not be described as revenue caused by that day's spend.
3. Test uniqueness and save control totals
In a prepared Sheet with date, customer ID, and campaign ID in columns A:C, use a helper formula such as:
=COUNTIFS(A$2:A,A2,B$2:B,B2,C$2:C,C2)
Every populated prepared row should return 1. Any value above 1 means the intended join key repeats. Filter out blank spreadsheet rows from this check, and also flag blank keys, invalid dates, and unrecognized accounts.
Record total cost, clicks, sessions, leads, and revenue from the prepared sources for one frozen date range. Save these source totals before creating the blend. For a scoped Ads campaign view, distinguish all-source totals from the subset with eligible Ads keys.
4. Join Ads to GA4 and inspect the result
In an editable report, create a blend from the source chart's data settings or the report's blend-management area. Make Ads the first table and GA4 the second. Add the three key fields to both sides, choose a left outer join, and match all three key pairs.
Only include the dimensions needed for this grain. Google explains that blend tables are grouped before joining. A device or landing-page dimension left inside the blend can still split rows even when the final chart hides it. Remove it from this blend or build a separate view at that detail level.
Compare Ads cost and clicks against the standalone Ads chart. Compare GA4 sessions against the GA4 subset whose full keys exist in the Ads table. Investigate every unexplained increase before proceeding.
5. Add CRM using the Ads keys
Join the prepared CRM table with another left outer join, matching CRM keys to the original Ads date, customer ID, and campaign ID. Do not make CRM matching depend on a GA4 field that is null when no tracked session exists. An Ads campaign can have a valid CRM record even when GA4 tracking is missing.
Repeat the totals check after adding CRM. If cost jumps at this stage, inspect the CRM input's key uniqueness first. If the connector or editor makes the required join arrangement unclear, build the final joined table in Sheets or BigQuery and connect that table instead.
6. Keep missing rows visible
This Ads-led view preserves Ads campaign-date rows, including spend without a GA4 or CRM match. It excludes records that exist only in the right-hand sources. Put unmatched GA4 keys, unmapped CRM leads, and CRM revenue without an Ads date match on an exceptions page.
For full business coverage, prepare a key spine: the distinct union of eligible date-account-campaign keys from all three sources. Put that spine first, then left join each prepared source to its keys. A sequence of full outer joins is not a substitute for deciding which keys later joins should use.
Track these identities in your audit export:
Ads-led blended cost = eligible Ads source cost
GA4 source sessions = matched sessions + unmatched sessions
CRM source revenue = matched revenue + unmatched revenue
Keep excluded accounts, unmapped keys, and currency exclusions in separate reason buckets so the equations use the same population. These are reconciliation checks, not promises that every source will have complete tracking.
7. Test dates, filters, and the handoff
Test one campaign-day, a multi-day range, an all-campaign total, an Ads-only row, and a CRM-only row. Repeat after adding each report control. A Looker Studio blend data date range can change which records qualify before joining; a chart filter can affect the joined result afterward. Inherited filters depend on compatibility, as Google's filter-order documentation explains.
Use a CRM status filter in the CRM preparation step when selecting closed-won revenue. A downstream filter that requires a matched CRM value can remove spend rows with no CRM match. Keep a control chart with the original population, and label filtered charts with the subset they represent.
Assign an owner to key duplicates, unmapped records, stale refreshes, and total mismatches. Require review when a new dimension or source is added. Save the last reconciled report copy so a broken edit has a clear rollback point.
Calculate ratios after the rows reconcile
Calculate cost per lead by dividing total eligible cost by total eligible leads. First confirm that both sources are complete for the same selected population and cutoff. Withhold the overall ratio while a required extract is stale, incomplete, or has unresolved campaign mappings.
A sum can silently skip null lead values and still produce a plausible number. Classify missing matches as confirmed zero activity or unresolved data before using the formula. The denominator check below prevents division by zero; it does not detect missing records.
For a chart based on prepared additive blend fields, create a chart-specific calculated field:
CASE
WHEN SUM(Leads_created) > 0
THEN SUM(Ads_cost_USD) / SUM(Leads_created)
END
Replace the field names with the actual fields in your chart. The missing ELSE leaves a nonpositive denominator without a value; display it as unavailable, not $0. A zero-lead campaign with spend should stay visible beside that unavailable ratio.
Google supports chart-specific calculations on blended data. Its aggregation guidance uses ratios of sums, while its calculated-field troubleshooting warns that already aggregated connector metrics cannot always be summed again. These formulas assume prepared additive numeric fields. If a connector rejects them, inspect the field aggregation or calculate from prepared source totals; do not change SUM to AVG just to clear the error.
In an illustrative two-campaign report, $100 for one lead and $100 for nine leads gives a total cost per lead of $200 ÷ 10 = $20. Averaging the two campaign ratios gives about $55.56. That average answers a different question and should not appear as the overall cost per lead.
Do not sum daily distinct users into a monthly unique-user total. One person can appear on several days. GA4's metric definitions distinguish user counts from event and session measures; retrieve non-additive totals at the required reporting scope.
A “revenue closed this month ÷ ad spend this month” calculation is a period ratio. It is not automatically campaign ROAS, or return on ad spend, because the sales can come from older leads. Before using a ROAS leakage estimate, establish the acquisition period, revenue basis, and attribution rule that belong in that calculation.
Operator composite: a $100 campaign becomes a $600 report
This operator composite is a hypothetical teaching case, not a public customer claim or a measured That'sGonnaHelp engagement. Consider a small home-service business using Google Ads, GA4, HubSpot exports, and Google Sheets. The brief is “Blending Ads, GA4, and CRM Data in Looker Studio Without Inflating Numbers.”
The owner sees $600 of campaign cost in Looker Studio, while Google Ads reports $100 for the same day. The report also shows 300 sessions rather than 100. Three lead IDs remain visible, so the team initially suspects a currency or tracking issue instead of a join problem.
The analyst reproduces the table near the start of this article. GA4 is grouped by device, and the CRM export includes one row per lead. Those dimensions are present in the blend even though the owner only sees a campaign name. Their combination explains all six repeated cost values.
The repair uses prepared campaign-day tabs in Sheets. Ads cost remains $100, GA4 sessions are grouped to 100, and the three deduplicated CRM leads become one numeric count. The analyst joins Ads to GA4 first, then adds CRM through the Ads keys, checking source totals each time.
A second problem appears during testing: a CRM deal closes on a day with no Ads activity for its campaign. It is absent from the Ads-led view. The analyst puts it in the unmatched-revenue ledger and uses a key spine for the separate complete activity report. No revenue is forced onto a nearby date to make the chart look complete.
After correction, the illustrative daily report shows $100 cost, 100 sessions, and three leads. Cost per lead changes from the erroneous $200 to about $33.33. The business did not save $500 or create extra leads; the report stopped overstating spend. It can now investigate campaign performance using the right inputs.
For a separate planning calculation, assume the repair takes 12 hours at $75 per hour, or $900. Assume future reporting work falls by two hours per month, worth $150 of staff capacity, and monitoring uses half an hour, worth $37.50. That leaves $112.50 of monthly capacity value before extra software costs, implying eight months of simple payback. These assumptions are not observed savings, and capacity becomes cash only if paid costs actually fall.
What does a reliable Looker Studio blend cost?
A reliable blend can use the no-charge reporting tier; preparation and testing are often the main work. Estimate the setup hours, connector charges, data storage, and ongoing reconciliation separately. The illustrative budget below is a planning worksheet, not a service quote.
Self-service Looker Studio has a $0 license cost for creators and report viewers. The Pro project subscription list price is $9 per user per project per month; subscription terms can affect the price. Both statements reflect Google's live product pricing page, checked September 9, 2026; they are not a historical November 2025 price quote.
| Cost item | USD amount | Basis |
|---|---|---|
| Self-service reporting license | $0 | Published Google price; external services are separate |
| Pro project subscription, if needed | $9 per user per project per month | Published list price; confirm subscription terms |
| Small prepared-data setup | $600–$1,200 | Illustrative 8–16 hours at an assumed $75/hour |
| Monthly validation | $37.50–$150 | Illustrative 0.5–2 hours at the same assumed rate |
| CRM connector, warehouse, and storage | Obtain a scoped quote or usage estimate | Depends on source, refresh, volume, and existing contracts |
Use your own loaded labor rate, which includes the relevant employment costs beyond wages. Then enter the one-time build cost and net recurring benefit in the automation ROI calculator. Keep staff capacity, reduced paid labor, and verified revenue effects on separate lines so they cannot be counted twice.
A paid tier cannot repair a many-to-many join. Approve more spending only when it solves a named need, such as managed data preparation or content administration. For unresolved differences between ad credit and business revenue, use an attribution reconciliation worksheet alongside the blend audit.
When should you prepare data outside Looker Studio?
Prepare data in Sheets or BigQuery first when raw records need deduplication, dated mappings, attribution weights, or repeatable validation. Use a blend only after those rules produce stable tables. A simple report does not require a warehouse, but a complex data model should not depend on hidden chart settings.
Direct blending is a poor fit in three situations:
- No trustworthy shared identity. Campaign labels cannot be mapped uniquely, or the CRM never captured acquisition information. Report unmatched records and improve capture; do not invent attribution.
- Conflicting levels of detail. You need campaign spend alongside each contact, product, or sales activity. Keep detail separate, or define and test an allocation rule before spreading money across rows.
- Several reports need the same transformation. Maintain the prepared model centrally when many editors would otherwise copy and change join logic. Add refresh monitoring and a documented reporting cutoff.
Common mistakes that inflate or hide numbers
| Mistake | Why it misleads | Better control |
|---|---|---|
| Joining by date alone | Several campaigns can match each date | Include the complete account and campaign key |
| Keeping device or lead ID in this blend | Splits the intended campaign-day grain | Aggregate to the agreed grain first |
Using MAX(cost) to suppress repetitions |
May lose legitimate cost across dates or campaigns | Fix the join and verify source totals |
| Replacing every null with zero | Hides failed matching and stale data | Distinguish confirmed zero from missing or failed data |
| Summing Ads, GA4, and CRM conversions | Counts different claims about overlapping activity | Keep separate, clearly named measures |
A Looker Studio cross join produces combinations without matching campaign keys. It is unsuitable for this report. Likewise, “merge data” should not mean stacking independent ad-platform rows through a join: prepare a union upstream when the goal is to append records into one common schema.
FAQ
Use these answers to choose the right setup and diagnose the remaining edge cases. A clean join protects arithmetic; it does not create missing campaign attribution or make different conversion definitions equal.
How to use blend data in Looker Studio?
Use a blend when one chart needs measures from different sources that share a tested reporting key. Select the blend as the chart's data source, then add only the dimensions and metrics appropriate to its grain. Keep separate source charts if you only need side-by-side totals and no shared calculation.
How to connect Looker Studio to Google Ads?
Add a data source, choose the Google Ads connector, authorize an account with reporting access, and select the intended account data. First create a standalone cost-and-clicks table for a known date range. Add GA4 and the prepared CRM source separately; connecting the account alone does not define a blend or campaign mapping.
Why does a Looker Studio blend return null values?
A null can mean that no row matched, the source field was empty, or the selected scope excluded a record. Compare the full key values, data types, account IDs, dates, and source freshness. Convert a missing measure to zero only when a successful, complete extract proves that no qualifying activity occurred.
Does Hide repeated join fields remove duplicate rows?
No. It controls whether repeated join-key columns are shown in the available blend fields. It does not deduplicate records or fix repeated cost. Google's description of repeated join fields concerns fields, not row counts.
How many data sources can Looker Studio blend?
A Looker Studio blend can contain up to five tables. That is the documented blend limit, not a guarantee that five raw sources will join safely. A key spine plus Ads, GA4, and CRM uses four tables in this pattern.
Can I blend CRM revenue by close date with ad spend?
Yes, as a clearly labeled activity report showing spend and revenue on their respective reporting dates. Do not present its ratio as acquisition-cohort ROAS. For an acquisition view, assign eligible revenue to a defined acquisition cohort upstream and document the attribution rule, revenue treatment, and maturity cutoff.
Why do conversions still differ after duplicate rows are fixed?
Removing repeated rows fixes the join's arithmetic. It does not align attribution scope, count settings, or timing: Google Ads can report conversions against the earlier ad interaction while GA4 reports the event date. Google's conversion comparison guidance explains why correctly joined source columns can still differ.
Can a CRM filter change my reported ad cost?
Yes. If a filter removes blended rows without a qualifying CRM match, it can also remove the spend carried on those rows. Compare the result with a standalone Ads control and label the remaining population. Filtering a CRM input and filtering a completed blend can answer different questions.
Answer clarity notes
These distinctions keep the examples and vendor facts from becoming claims they do not support. Use the article as an implementation guide, with a written reporting contract for your own accounts.
- Dates: November 29, 2025 is the article's catalog date. Technical documentation and pricing were checked September 9, 2026. The later check does not establish historical availability or pricing; current Google links may use updated product naming.
- Evidence: Linked Google documentation supports the stated product behavior and published prices. The setup sequence, validation worksheet, and limits are implementation recommendations inferred from those mechanics.
- Examples: The operator composite and all sample data are hypothetical. They are not a public customer claim, a measured That'sGonnaHelp result, or evidence that a reporting correction creates revenue.
- Costs and returns: Hours, labor rates, capacity value, and payback are explicit planning assumptions. They exclude unquoted connectors and infrastructure. These are planning assumptions, not guarantees of savings.
- Scope: This article concerns reporting implementation for US SMBs, not accounting, tax, legal, or platform-policy advice. A successful join does not prove causal attribution, complete tracking, or financial recognition of revenue.
Sources
Google's documentation supports the product facts used here. Recheck the relevant page when a connector field, interface label, or subscription term changes.
- Google Cloud: Blending tips and advanced concepts
- Google Cloud: How blends work in Looker Studio
- Google Cloud: About calculated fields
- Google Cloud: Add, edit, and troubleshoot calculated fields
- Google Analytics: API dimensions and metrics
- Google Ads: Create conversions from Google Analytics events
- Google Analytics: Select attribution settings
- Google Cloud: Product features and pricing
If your blended report will not reconcile, That'sGonnaHelp can help map the source records, test the join keys, and define an owner for each exception. Start with one campaign and a frozen date range before expanding the report.