Combining Data in Looker Studio: Avoiding Incorrect Totals and Duplicate Rows

Data blending combines tables from several sources in a chart. A join links their rows using matching values, such as the same date and campaign ID. Plausible totals can still be wrong when a join repeats values, drops unmatched keys or combines incompatible metrics. A reliable review checks both individual keys and totals.

Three stages matter: each table is grouped by its selected dimensions, the resulting rows are joined, and the chart can aggregate the joined result again. Table filters, date ranges and chart filters must also be checked. The final number is not determined by the join type alone.

1. Define what one row represents

Before choosing fields, the intended row should be written down: one day, one campaign per day, or one order. The join key must represent that level of detail. A composite key can contain separate date and campaign fields; concatenation is unnecessary.

If one date row contains 100 sessions and matches three campaign rows, the join produces three copies of that session value. A later SUM produces 300. The factor is three only for keys with exactly three matches. Across keys, the total depends on both metric values and match counts.

One-to-many joins are not inherently invalid: a campaign total can intentionally accompany daily detail. The total must then be labelled and aggregated appropriately. AVG or MAX is not a universal deduplication rule and can lose contributions from other campaigns. Reducing both inputs to one row per key is a straightforward option when the chart requires a one-to-one comparison.

2. Choose a join for the reporting question

Left outer retains left-side rows and attaches matches from the right. Right outer reverses that choice. Inner retains matching pairs only. Full outer also retains unmatched rows from either side. Cross combines every row with every other row and requires no key.

Full outer does not prevent repeated values and does not create dates absent from both inputs. A calendar with one row per required date can anchor left joins, provided all joins use that calendar date and later filters do not remove unmatched rows. In a full outer result, the displayed date may need IFNULL(left_date, right_date).

Multiple joins are evaluated in order; later joins operate on earlier results. The first physical table is not the entire left input of every subsequent join. A blend supports at most five tables, including a calendar. Several independent charts can use separate blends; a chart needing more inputs may need upstream preparation.

3. Check keys and effective input rows

Date and date-time fields need compatible types and an explicit reporting timezone. Midnight conversion can place the same event on different days. LOWER and TRIM can normalize campaign labels only when the business meaning permits it. Distinct identifiers must not be merged merely because their formatting looks similar.

A key can be prepared in the source or in a supported table-level calculated field. Calculations inside an individual blend table can reference only that table’s fields. Calculations combining fields from different tables belong after the blend, for example in the chart.

Record Count commonly counts underlying source records, not the number of pre-aggregated join rows. A one-to-one daily join can therefore show a count greater than one. A diagnostic table should expose every selected dimension, count rows per complete key and inspect missing keys. A connector timeout can produce an error; a blank chart alone does not identify the cause.

4. Name fields clearly without changing their meaning

Aliases such as sessions_a and sessions_b make formulas easier to review. The actual source field must still be selected correctly. Identical display names do not by themselves prove that the software silently calculated a wrong sum.

Source metrics can be reaggregated after blending. SUM is suitable for additive values at the intended level; it is not a repair for duplicated rows or distinct-user counts. Automatic aggregation should be understood before being replaced. Two charts can reuse a blend at different levels when the metrics support that change.

5. Separate users, events and ratios

Adding user counts from regional properties counts property memberships, not necessarily distinct people. Likewise, daily distinct-user counts do not establish a monthly distinct count: one person visiting on Monday and Tuesday appears in both daily counts but should count only once for the month. Counts are additive only when the populations are known to be disjoint. Event exports alone do not solve identity across properties: deduplication needs a valid, consistently scoped identifier and a defined identity policy.

For two non-overlapping sources with compatible definitions, a rate can be computed from summed components. In the following example, converted_sessions counts sessions with at least one qualifying outcome. Replacing that numerator with the number of events changes the meaning to events per session and may exceed 100 percent.

Missing values are not automatically zero. A failed import, an unmatched key and a confirmed day without activity have different meanings. The formula below deliberately leaves incomplete data unresolved; zero substitution belongs only where the source contract establishes zero activity.

CASE
  WHEN SUM(
    CASE
      WHEN sessions_a IS NULL OR sessions_b IS NULL
        OR converted_sessions_a IS NULL
        OR converted_sessions_b IS NULL
      THEN 1 ELSE 0
    END
  ) > 0 THEN NULL
  ELSE
    (SUM(converted_sessions_a) + SUM(converted_sessions_b))
    / NULLIF(SUM(sessions_a) + SUM(sessions_b), 0)
END

This chart formula assumes one complete row per shared key in both inputs, disjoint populations, aligned periods and nonnegative session counts. Field names are example aliases. Any incomplete joined row leaves the result empty; a separate completeness check must also detect dates or keys absent from both sources. The numeric output is a fraction, displayed as a percentage.

6. Test with counterexamples

Suppose A has 100 sessions and 10 outcomes, while B has 900 sessions and 9 outcomes. The combined rate is 19/1000 = 1.9%. If A matches once and B three times, a SUM after the join gives 37/2800, about 1.32%. A numerator and denominator from the same source do not preserve the overall rate when match counts differ between keys.

Equal grand totals are not proof of correctness. Original values A=100 and B=100 total 200; a result containing A twice and omitting B also totals 200. The comparison must include key coverage and individual values. Larger or smaller totals are clues, not unique diagnoses: dates, filters, metric scope and source updates can also differ.

A useful acceptance check compares identical date ranges, filters and metric definitions in the original source and the blend, then inspects representative keys, unmatched keys and zero-denominator cases. All joined rows must be evaluated at the intended chart aggregation.