I have been working on this for a while and I still can't make this work. I hope you can give me a hand.
It is a bit complicate to be explained but I will do my best.
So the objective is to get 3 things:
1. Spend at advertiser level = advertiser_spend
2. Spend at campaign level = campaign_spend
3. CPA at advertiser level = sum(total conversions for specific activities)/advertiser_spend (calculated in point 1)
4. CPA at campaign level = sum(total conversions for specific activities)/campaign_spend (calculated in point 2)
Data is not user friendly. Granularity goes like this:
Advertiser > Campaign > Placement > Platform > Activity
Spends are defined at placement level but due to the granularity and the joining we have, this spend appears duplicated (times combination of the number of platform and activity we have).
ISSUE 1: I don't manage to get spend at campaign level:
Steps I am following:
1. .Calculate the max at placement level per campaign and advertiser
2. Sum values in point 1
I almost manage to do it using running_total. Issue is I want only a number per campaign but the max is calculated at placement level: Wanted output: spend campaign1 = 20
ISSUE 2: CPA calculation at campaign level
CPA is calculated as sum(total conversion for specific activity values) / total_campaign_spend
There are two issues here:
1. total_campaign_spend is the issue number 1 I listed
2. I need to use activity as filter only affecting the total_conversion field in the CPA calculation: I mean, if the campaign_spend = 20£, it doesn't matter the activity I select, this shouldn't affect the campaign_spend.
Does it make sense?
I hope it does.
I attached the data I am playing with.
Thank you very much. Any help will be super welcome.
Data_TableauCommunity.csv.zip 12.5 KB