Spot a repeated revenue total after a one-to-many join

A revenue query can inflate a correct purchase table when a join produces several rows per transaction. This lab models a uniform fan-out so the failure is easy to see. The repair belongs in the query’s grain, not in an arbitrary discount applied to the final dashboard.

Define the calculation before using it

Define the intended output grain, such as one row per transaction, before joining campaign or item metadata. Count joined rows against distinct transaction keys and inspect whether each purchase amount repeats. This worksheet assumes every transaction repeats the same number of times solely to illustrate the error. A real query needs transaction-level analysis when fan-out varies.

Work through the synthetic example

One thousand transactions at an average value of five should sum to 5,000. Repeating every transaction across three joined rows produces 15,000, an overstatement of 10,000. Dividing the aggregate by three works only under the stated synthetic assumption; it is not a safe general repair for a real join.

Use the artifact and preserve its assumptions

Open the editable calculator to change the inputs and inspect the sensitivity view. The CSV records synthetic inputs and expected outputs; the JSON fixture keeps the equations available for reproduction. These calculations have been checked against the stated example. No measured campaign data is included.

The sensitivity rows vary only fanout by 20% below and above the entered value. They are scenarios, not confidence limits or a forecast distribution. A row outside the model’s constraints is labelled rather than turned into a plausible-looking result. Save the chosen inputs with the decision so another reader can distinguish a changed assumption from a changed formula.

Evidence and limits

Google’s export schema separates the reporting date, UTC timestamp and event parameters. The worksheet uses simplified aggregate inputs; it does not claim that an export already contains a correctly reconciled cohort. See Google Analytics BigQuery Export schema, especially “event_date, event_timestamp, event_value_in_usd and event_params fields”.

Never deduplicate revenue by amount alone: different legitimate transactions can share a price. Validate keys, join cardinality and aggregation order against a small inspected sample before trusting a repaired query.

Background: UA metrics explained: CPI, ROAS, LTV and payback and Reading an MMP dashboard without fooling yourself. These existing articles provide context; the present calculation does not verify every archived claim.