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.
Meaghan.twbx.zip 53.1 KB
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!