HI there, i've attached a reporting template I'm creating for people at my company. Purpose is to make their jobs easier by just copying and pasting an excel output from Facebook Ads manager and the sheet will auto-update graphs. Note that this will be used in Google Sheets, but I just exported it into excel since I have trouble sharing Google Sheets links.
This workbook has 4 sheets
- Summary--> People will type in their Primary, secondary, tertiary KPI based on Column I, and also Campaign spend. Rows 12-14 auto populate based on KPI
- Don't touch- Reference Sheet --> Sheet with formulas that will be hidden. This is the sheet i need help with
- Charts --> Charts that will auto update based on the PASTE sheet
- PASTE platform/placement --> People download Facebook Ads manager excel workbook and paste here
When you download metrics like CPC, CPE, Cost per 1000 people reached, CPM etc from Facebook, the Cost used is what's actually spend. However, in some cases with clients, there's a markup my company charges.
I want a formula in all the Cost per columns (i.e. Column G, I & K) in "Don't touch-Reference Sheet" that will essentially, re-calculate the metric with this markup price.
For example, if Primary KPI is Reach, I want the Cost per Primary KPI to be Client Facing Spend*1000/Reach vs. the current method, which is (Media Cost*1000/Reach) — in the workbook, the client facing spend = media cost, but this isn't always the case.
I also want the formula above to be dynamic so if the PRimary KPI is cost per engagement, it won't have to multiply spend by 1000, so the calculation will just be (Client Facing Spend/ Engagements). I've written the Formulas I want to use in the Summary tab in Column M
Please let me know what the best way of doing this is. I'm currently spinning my wheels trying to figure out the best way!
Bookmarks