Building PPC Forecasts and Projections Sheets

Key Takeaways

projection sheets can be a very important tool in PVC campaign management it's always easy to know what your recent performance has been by going into the interface and checking out stats all that good stuff but it's not as easy to know where you expect to end a certain time frame maybe end of the week most commonly end of the month so today we're going to talk about projection sheets and how you can set them up for your PVC campaigns to get some estimate of where you might end let's say a month in terms of performance based on the performance you're currently seeing in the account once we get into our Google sheet we need to put together a couple different sources of information we need to put together some performance data as well as some date information to be able to extrapolate what our projected performance will look like at the end of the month so let's start with performance data placeholders we're gonna start with past seven days month to date and then we'll extrapolate a projection based on that now let's put in some places for date range we're gonna need today's date yesterday's date the last day of the month that we are in or projecting for and in the number of days left in the month in Google sheets we can use some pretty easy formulas to be able to fill in the date information to make our data sheets just that much more actionable and easy to manage so let's start off by putting in some formulas there's a very simple one for today you just type in equals today and then open and close bracket now yesterday unfortunately there's not a formula for it but you can pretty easily just type in equals click your today cell minus one pretty simple right so now month-end is a little bit different we're gonna need to put in equals e Oh month it's gonna be end of month but we need to be able to actually build a formula so you'll see that in the formula builder it starts off with start date you can easily just click your today cell that way it knows that you want it to be this month that you're looking for the end of right you then hit comma and the second part of the argument is how many months out you want to extrapolate in this case I'm gonna put in zero because I want it to be January but if for some reason you wanted to extrapolate to the end of February you could at least take that away out of one and you'll see that it shows up as February 28 2019 is the end of month but again for today we want to just do January so we're going to put in zero then we need to calculate the number of days left like I said about the partial data we don't want to use partial data for our calculation so we're going to only use the month end cell and yesterday to be able to figure out how many full days are left in the month so to do that we're gonna pop in here cook equals and then put an end of month - yesterday and that shows that there are eight full days left in the month of January to be able to extrapolate based on performance okay so now let's put in some information for performance let's say we want to estimate the number of clicks that we're gonna have by the end of the month so for past seven days let's just say that we had 70 clicks for the month that'll make some math easy in here in a little bit and then let's just say up until yesterday we've had a thousand clicks for the month so now what we're gonna do is put together a projection based off of that set of stats so what we're gonna do is we're gonna start off by finding the average performance for the past seven days so we're gonna take seven days divided by the number of days which is seven pretty simple right so on average we've seen about 10 clicks per day over the past seven days and that's how we have 70 clicks what we need to do is be able to use that to extrapolate for the remainder of the month we're going to assume that the last few days of the month are going to look similar to the most recent days that we've seen so we're gonna take that number and multiply it by the number of days left in the month you'll notice that that popped up and showed that we had 80 estimated clicks because we took 10 times 8 to give us 80 right so then what we need that gives us what we are extrapolating for the end of the month so what we need to do is add that to the performance that we've already seen so far this month to basically get before-and-after and once we do that we're able to come up with this 1080 clicks the best part about this formula is that we can actually use it to extrapolate any number of different stats that you have in the account so we can use it for impressions we could use it for spend you could use it for conversions whatever you want as long as it is an actual metric that is a value not click-through rate or impression share or anything like that but it is a solid metric a value like clicks and impressions and spend you can utilize your past 7 days worth of data plus the month to date data to be able to come up with a projected performance for end of the month for any of those stats pretty simple right with just a couple different pieces of performance data as well as some pretty easy formulas around dates you can easily start to extrapolate what your performance will look like at the end of the date range based on recent performance now this is just a projection a forecast an estimate if you will but it has some foundation in recent performance so it's a little bit better than just sticking your finger out the window and hoping you can figure out what do your performance will look like at the end of the month it's based on some data it's certainly not a promise or an expectation that that's the performance you're gonna see but it at least gives you an educated guess as to what performance will look like at the end of your date range happy projecting thanks for watching our video make sure to subscribe to the paid media pros channel to see more videos