Joseph K Berman

Data-Driven Digital Marketer. MBA 2019

Trajectory Adjusted Projection For Better Data

By admin | July 19, 2017 | 0 Comment

What is Trajectory Adjusted Projection?

When I first started working as a Performance Marketing Coordinator at Randall-Reilly, I was impressed with the large tool set we were given to accomplish our jobs.  I had to learn a dizzying array of utilities from AdReport to Zapier within my first couple of weeks on the job.  Once I started getting assigned PPC campaigns of my own, I learned that good deal of my time would be spent in a good old fashioned spreadsheet recording campaign stats like impressions, clicks, ad spend, etc.  Even these spreadsheets were highly customized with custom JavaScript running in the background performing any number of tasks.  There was one place where I found the tools at our disposal lacking: end of month/campaign forecasting.

When you are simultaneously managing 50 – 100 PPC campaigns with varying goal metrics across multiple platforms like AdWords, Facebook, Twitter, and LinkedIn – having the ability to quickly understand where your campaign is going is crucial.  The standard end of month projection used in our campaign tracking sheets looked something like this:  (KPI / Days Campaign Has Run ) * 30.4 = EOM Pace

So, if you are on the 15th of the month and have gotten 30 conversions, the formula will see that you’re averaging 2 conversions per day and project 62 conversions by the end of the month.  Simple enough.  But is it good enough?

What this formula fails to take into consideration is a campaign’s trajectory.  When a marketer makes adjustments to a PPC campaign, the entire trajectory of a campaign can change dramatically.  That’s the whole point of optimizing, isn’t it?

I needed a way to quickly understand how the campaign optimizations I made were going to affect me down the road.  That’s when I came up with the idea for Trajectory Adjusted Projection or TAP for short.

The basic projection tells us that we’re averaging 2 conversions per day over the course of the month, and therefore we’ll end up with about 60 by the end of the month.  But what if in the first 10 days of the month I only had 10 conversions, and the other 20 had come in the past 5 days?  I’m not averaging 2 conversions per day.  I’m averaging 4 conversions per day now!  Let’s see how big of a difference calculating the TAP makes.

Trajectory Adjusted Performance Campaign Stats

Sample campaign stats with trajectory adjusted projection

The first step is to find the difference between our conversions on the 15th & 10th of the month (when I made some killer optimizations).

30 -10 = 20

That gives me 20 conversions in the past 5 days.  So we divide 20 by 5 to get our conversions per day since optimization.

20/5 = 4

So now we know that we’re averaging 4 conversions per day.  There are 15 days left in the campaign, so multiply our days remaining by KPI per day.

16 * 4 = 64

We are projected to get 64 conversions in the next 16 days at our current pace.  So add that to the conversions we’ve already achieved this month…

30 +64 = 94.

And there you have it.  Now, this is a very simplified case study, but its principle holds true across the board.  Check out this real life campaign I’m running  right now to see the difference utilizing TAP makes.

Trajectory Adjusted Projection Chart

Example of trajectory adjusted projection in a real life AdWords campaign I’m running

If you’d like to utilize TAP formulas in your spreadsheets, you can adapt this formula to your needs to track your AdWords or Facebook campaigns.  The formula below assumes that your reporting/check dates are in column A.

=(EOMONTH(A2,0)A2)*((B2-B1)/(A2A1))+B2)

0 Comments