Weighted-rate calculator

Why average CTR is usually wrong

Enter two rows of impressions and clicks. The correct combined CTR is total clicks divided by total impressions. The simple average weights both rows equally even when their exposure differs.

Metric taxonomy

Aggregation depends on the kind of fact

Metric kind	Example	Typical behavior	Failure to avoid
Additive	Spend, impressions, clicks, item quantity at compatible scope	Sum across dimensions that do not change the fact’s meaning.	Summing across currencies, overlapping reports, or duplicated joins.
Ratio or derived	CTR, CPC, CPM, conversion rate, ROAS	Sum compatible base metrics, then recompute the formula.	Averaging row-level ratios or summing them.
Distinct or non-additive	Unique users, customers, orders, reach	Deduplicate at the target grain when identity is available; otherwise retain source grain.	Summing daily unique counts into monthly unique counts.
Fixed-window estimate	Platform reach or frequency for a defined report window	Keep the source’s requested window, dimensions, and estimator contract intact.	Summing or averaging overlapping reach windows.
Semi-additive snapshot	Inventory on hand, account balance, daily state	Sum across compatible entities; use last, first, average, or another explicit rule across time.	Summing daily inventory levels to describe current inventory.
Attributed	Platform-attributed conversions or revenue	Aggregate only within compatible attribution settings and source scope.	Summing claims across platforms as unduplicated business outcomes.
Modeled	Forecast, incrementality estimate, media-mix contribution	Preserve model version, horizon, uncertainty, inputs, and output grain.	Mixing model outputs with observed facts without labels or compatible definitions.

Grain statement

What one row represents is part of the metric

A useful grain statement names the time unit, entity identities, attribution or reporting window, and partitions such as currency. “Campaign performance” is not a grain. “One row per date, ad account, campaign, attribution setting, and reporting currency” is.

Example ad graindate × ad_account × campaign × currency

Adding ad set or ad changes the grain. Joining a campaign total to every ad row duplicates the campaign fact.

Example commerce grainorder_id × order_line_id

Order-level revenue repeated on every order line will be multiplied when summed. Allocate it deliberately or keep it at order grain.

Example snapshot grainsnapshot_date × location × product_variant

Quantity can be additive across locations on one date but is normally not additive across snapshot dates.

Join diagnostics

Four joins that silently change the answer

Join	What goes wrong	Safer pattern
Orders to order lines	One order-level amount repeats for every line, multiplying revenue.	Aggregate line metrics to order grain, use line-native amounts, or define an allocation that sums back to the order.
Campaign daily to ad daily	Campaign totals repeat across all matching ad rows.	Aggregate both sides to the same keys or join descriptive dimensions only where the relationship is many-to-one and time-valid.
Customer to orders	A customer snapshot or lifecycle value can repeat for every historical order.	Choose customer grain for customer metrics or an explicitly dated customer snapshot before joining.
Facts with multiple currencies	Numerically summing USD, EUR, and SEK creates a meaningless total.	Group by original currency or convert every compatible base fact under one documented FX policy before aggregation.

Safe aggregation checklist

Run this before publishing a total

State the source grain.Name every key that makes a row unique, including time, entity, currency, window, attribution setting, and snapshot identity where relevant.
State the target grain.Decide whether the answer should be per day, campaign, order, customer, product, store, or another entity before writing the query.
Classify each metric.Mark additive bases, derived ratios, distinct facts, snapshots, attributed metrics, and modeled outputs.
Test join cardinality.Prove whether the relationship is one-to-one, many-to-one, or many-to-many at the selected date and scope. Compare row counts and base sums before and after.
Recompute derived metrics.Calculate CTR, CPC, CPM, conversion rate, and ROAS after aggregation from compatible numerators and denominators.
Preserve partitions.Do not collapse currency, attribution window, model version, source report, or incompatible account scope without an explicit policy.
Validate invariants.Check that line allocations sum to orders, child entities sum to parents where expected, and regrouping does not change additive base totals.

Definition evidence

Use the report contract, not the column type

The Semantic Contract Library lets you inspect source report grains, fields, metric behavior, and semantic capability. Compare real contracts for Shopify, Google Analytics, Meta Ads, and Google Ads. “Numeric” does not mean “safe to sum,” and source availability does not imply an approved cross-source join.

Inspect metric contracts

Primary sources: verify report scope and metric behavior

These provider examples show why grain and aggregation must come from report semantics rather than a numeric data type. Recheck current documentation whenever a provider changes a report, export, or metric definition.

Related guides

Diagnose the business discrepancy behind the aggregation

Why marketing numbers disagree

Use the decision tree to separate identity, definition, timing, and aggregation gaps.

Platform ROAS vs. blended ROAS

See correct ratio aggregation in a common marketing-efficiency comparison.

Shopify vs. GA4 revenue

Apply grain and identity checks to transaction-level reconciliation.

Encode the rules once

Make invalid aggregation harder to ship

Metric Hive treats grain, base entity, aggregation behavior, date semantics, currency, tenant scope, and approved relationships as product contracts. That can prevent many silent reporting errors; it does not make every source report safely joinable or every platform estimate additive.

Start free Explore the contract library