Calculate cost per conversion across uneven campaigns
A media buyer adds three campaigns to a portfolio report. The dashboard shows a cost per conversion for each campaign and then averages the values for the total row. That total can change when the analyst splits a campaign into two rows, even when spend and results are identical. Build the portfolio figure from the underlying amounts so it answers what the combined group paid for each recorded conversion.
Trace the numerator and denominator
Use a small example with fictional data. River spent USD 90 and produced nine conversions, for a cost of USD 10 per conversion. Hill spent USD 60 and produced two conversions, for a cost of USD 30. Field spent USD 45 and recorded zero conversions. Its cost per conversion is undefined for the selected window. It still belongs in the portfolio spend.
The sum of the three costs is USD 195 and the sum of their conversions is eleven. Divide those totals and round once for display. The portfolio result is USD 17.73 per conversion. Taking the arithmetic mean of River and Hill gives USD 20 and leaves Field's spend out of the calculation. Dividing only the two converting campaigns' spend by their conversions gives USD 13.64. Both numbers answer narrower questions than the portfolio report does.
Group the underlying values at the level the reader selected, such as a region, a campaign group or a whole account. Then divide total spend by total conversions for that group. PostgreSQL describes how aggregate functions compute values over groups of input rows and how sums treat null inputs. PostgreSQL aggregate functions
The same rule applies to the time axis. Use every day's spend and conversion count in the weekly calculation, including days with spend and zero conversions. A busy day can account for most of a week's conversions, even if it is one row in the daily report. Keep the unrounded totals with each daily record. A table with rounded daily ratios alone lacks enough information to reproduce the weekly figure when the sizes of the daily groups differ.
Keep zero and missing values distinct
A complete campaign report can show zero conversions. A late conversion export can leave the count missing. The first case contributes spend and a known zero to the group. In the second, the count remains uncertain until the export arrives or its completeness can be verified. Give the row a status that records that difference instead of treating a blank as a settled zero.
Add a similar rule for a group with total spend and no recorded conversions. Leave the ratio blank and show the spend and count separately. This keeps a spreadsheet chart or dashboard from displaying the blank as a free conversion. In BigQuery, SAFE_DIVIDE returns null when division would produce an error, including when the divisor is zero. BigQuery mathematical functions
Check that the counts use the same definition across campaigns. A form submission and an approved lead need separate metrics if they represent different results. Confirm the reporting currency and attribution window before adding amounts or counts. When a source is still delivering conversions after the date cutoff, report the cutoff and distinguish the preliminary period from a settled comparison. The FroggyAds campaign reporting guide explains how to reconcile qualified outcomes and review source-level results over a consistent window.
Give the total row a simple test
Save the fictional inputs with the report and check that the total shows USD 195 in spend, eleven conversions and USD 17.73 per conversion. Split River into two rows with USD 40 and four conversions on one row and USD 50 and five conversions on the other. The totals and the portfolio ratio should be identical. Remove Field as a separate report filter and check that both spend and the ratio reflect that explicit change in scope. Restore the filter when the test is done.
Finally, check the same numbers in the exported file and on the dashboard. A workbook may recompute its total from display values, while the dashboard calculates the raw ratio from source rows. Keep spend and conversions alongside the ratio in the export so someone reviewing a later bid decision can reproduce the total with the filters that were in force.
Prepared for FroggyAds with AI drafting assistance.