2 Replies Latest reply on Sep 16, 2012 3:25 PM by meaghan.cunningham.0

    Multiple time periods in the same worksheet?

      I'm trying to create an optimization dashboard that will show for an online ad placement, with 4 different creatives running, what the KPI is for three different time periods.  User generated ideally, but to keep it simple, I'll start with yesterday, average last three days and average last 7days .


      I'm attaching an example of what I'm trying to achieve.  It's in an datacell format to easily show what the data is I'm needing.


      I've tried table calcs, created my own calculated metrics, dragging in multiple dates with filters.


      Also, the metric is CTR, which is a calculation of clicks/impressions, so the average shouldn't just be an average of each days' CTR, but rather the sum of 3 days clicks/sum of 3 days impressions.


      Every time I feel like I'm getting close or the right track, the data doesn't make sense or hold up, so any help would be greatly appreciated.



        • 1. Re: Multiple time periods in the same worksheet?
          Shawn Wallwork



          I didn't really understand your spread sheet (actually I didn't really try), but I work with CTR every day, so I just through some data together and created the attached workbook. Let me know if you need help adapting it to different creatives. It uses these table calculations:



          WINDOW_SUM(SUM([Clicks] ),-1,0)/WINDOW_SUM(SUM([Impressions]),-1,0 )


          Past 3-Day Running Avg:

          WINDOW_SUM(SUM([Clicks] ),-3,0)/WINDOW_SUM(SUM([Impressions]),-3,0 )


          Past 7-Day Running Avg:

          WINDOW_SUM(SUM([Clicks] ),-7,0)/WINDOW_SUM(SUM([Impressions]),-7,0 )


          You get the idea. BTW, you could substitute a parameter value for the start value so it can be user-defined. Don't miss the little 'Null if not enough values' check box in the Table Calculation dialog box, otherwise you get skewed/erroneous results.



          • 2. Re: Multiple time periods in the same worksheet?

            Thanks Shawn.  This is good to see. I had tried something similar, but where I was running into problems was need to see this for a number of placements and creatives.  I really only want line per placement & creative, but to use the window avg solution, it seems like I need to have all the additional rows of prior day data shown too.



            Maybe I can export to excel and then filter out the data I don't need.  Thanks for the quick reply!