I am trying to calculate a field Incremental Sales
Which is
Incremental Sales = Mailed Sales - Expected Sales
We have two groups Mailed and Control
Mailed sales is calculated as the sum of revenue of mailed converted customers .
Expected Sales = Expected Number of conversions * Control Average Basket Value
Expected Number of conversions = ifelse(mailed count campaign level)=0, 0, mailed count campaign level * min (control conversion rate campaign level )
This is calculated on a campaign level.
If there are no control customers for that campaign, the expected sales should be 0,
which should give
Incremental sales = Mailed sales
I am able to do this in a pivot table but not in KPI.
Here is the screenshot.
If a campaign does not have a control , then incremental sales should be same as mailed sales. How can I put this as overall KPI, since I want the sum of all of these rows.
KPI looks at overall and doest look at campaign level.
@liya101 thank you for sharing this screenshot. I was able to re-create a table similar to yours and test out the calculations and it seems like I was able to get it to work as a KPI if I manipulated the calculation code a little bit. Here is how I calculated the same fields you provided the code for. It seems like adding the SUM function and then the ‘Campaign’ field as the aggregation level worked in allowing me to create a KPI with Incremental Sales. Hopefully this helps and solves your problem:
Expected Number of Conversions: ifelse(sum({Mailed Count}, [Campaign]) = 0, 0, sum({Mailed Count}, [Campaign])*min({Control Conversion Rate}, [Campaign]))
Expected Sales: {Expected Number of Conversions} * sum({Control Avg Basket Value}, [Campaign])
I have created an analysis here in arena Incremental Sales When I was tryinng out the formula:
ifelse({Mailed Count if control is present} =0,0,sum({Mailed Count if control is present} ,[{Campaign Parent}]) *min({Control Conversion Rate},[{Campaign Parent}])) it gives me the error
Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed.
ah yes you are correct, unfortunately that is not allowed - it was not clear to me that the underlying fields in your table were also all calculated fields. However in your arena it does seem to be working as a KPI the way you ended up doing the calculations. I noticed the KPI was missing the same date filter as the tables and once I applied that the KPI number seems to match the total for incremental sales
The issue we are facing in the control dashboard right now is that we need the parent-level control conversion rate to correctly calculate incremental sales for each mailed campaign.
For example, in the Dormant campaign, there are two variants:
Dormant 35% → Control conversion rate: 1.01%
Dormant 50% → Control conversion rate: 0%
The parent-level control conversion rate, therefore, averages to 0.51%.
When calculating incremental sales for the mailed cohort, we need to compare the mailed cohort for these A/B variants to the overall 0.51% (the parent campaign rate) rather than to the individual conversion rates of 1.01% or 0%.
To address this, I created campaign-level metrics that work in the mailed summary table : these provide incrementality at the campaign level. In the screenshot below, you will notice two metrics at the end:
Incremental Sales - shows campaign-level metrics
Overall Incremental Sales - shows the overall incrementality across campaigns
I am seeing is that these two metrics (the one in the summary table and the overall KPI metric) don’t currently match.
Hope everything is well with you! Just checking in since this thread hasn’t received a response in a while. Were you able to find a workaround yourself or are you still trying to find a solution? If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
