1 Reply Latest reply on Jul 26, 2018 1:32 AM by Archana Ganeshalingam

    Calculation without filters affecting it

    Patricia Martin

      Hello all,


      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.