The task and original case
Build a campaign-efficiency table that retains spend when a campaign has no recorded conversions. The invented campaign B has an undefined cost per conversion, but its cost still belongs in a combined scoped total. Excluding that spend would make the summary misleading.
| Case element | Value or requirement |
|---|---|
| Inputs | Fictional campaign A costs 100 and records 4 conversions; B costs 90 with 0. |
| Build | A campaign-efficiency table with undefined values and attribution limits. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
Campaign A costs 100 and records four conversions; B costs 90 and records zero. Declare the currency, period and attribution scope as common across both campaigns. Keep spend and conversion counts as separate inputs. Calculate a campaign ratio only when its conversion denominator is positive.
For the combined ratio, sum spend across both campaigns and divide by the sum of conversions. A PivotTable can summarize the numeric inputs; verify Sum rather than an unintended Count using Microsoft's guidance. Do not average campaign ratios or remove undefined rows before summing spend. The calculation is descriptive under a declared attribution rule, not proof of incremental campaign value.
Sources[1]
Check the worked result
A's cost per conversion is 100 ÷4 =25. B's is undefined because 90 ÷0 has no finite value. Combined spend is 190 and recorded conversions total four, producing 190 ÷4 =47.5.
If you drop B because its ratio is undefined, the reported combined ratio becomes 25 and hides 90 of included spend. Display B's spend, zero conversions and undefined marker together so the summary's47.5 remains explainable.
Make the failure visible
Make B's conversion count missing instead of zero. The combined count is then incomplete, so a definitive 47.5 ratio is no longer supported by the inputs. Test different currencies or observation windows too; they require conversion or separate reporting before aggregation. A single universal fill value cannot repair these distinct conditions.
Revise it and ask someone to reproduce it
Have a peer add two recorded B conversions under the same stated scope. B's ratio becomes 45, and the combined ratio becomes 190 ÷6, approximately 31.67. Keep the original zero-conversion report beside the update, and ask the peer to explain why the combined result is not the simple average of 25 and 45.
- B retains its 90 spend and explicit undefined ratio.
- Combined 190 spend and four conversions produce 47.5.
- Missing counts and incompatible scopes are distinguished from zero.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Built an independent campaign-cost report preserving undefined zero-conversion ratios and reconciling combined efficiency from total spend and counts.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.