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.
Sorry for the long delay in replying back. Just for clarification purposes, for the Dormant campaign example you provided, I saw in the control summary table that both of the conversion rates are 0%. I know you mentioned that one is 1.01% and the other 0%, but is that how it is intended for your use case or is that what you are trying to fix?
If you havenât tried this yet, you might be able to create a new calculated field that averages out each of the sections so you can apply them to the mailed summary. Please let me know if I misunderstood anything at all and Iâd be happy to further answer!
The reason why the KPI number is different is because you are using the âOverall incremental Salesâ calculated instead of âIncremental Sales Campaignâ (Picture 1). Swapping them out should give you the intended number (Picture 2).
Sorry for the confusion on my part. In the most recently sent Arena link I saw that the sum for incremental is equal to 307.95 so I went based on that. Could you please send me a new Arena link for the Mailed Summary table displayed in your picture so I can better see how everything is calculated?
Just checking back in since we havenât heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.
If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update youâre able to provide within the next 3 business days would be helpful for the community.
Since I havenât received any further updates from you, Iâll treat this inquiry as complete for now. If you have any additional questions, feel free to create a new post in the community and link this discussion for context.
