I recently had a QBR scheduled for mid-January, where we would typically cover performance from the previous quarter—in this case, Q4 from the previous year. However, the VP of marketing had asked for an update on Q1 projections, including the results of the MTD ad spend for January. This was an atypical request, but she mentioned that if we were returning above a 5x ROAS, she would be able to convince the CFO to increase their PPC budget for Q1, effective immediately.
However, Time Lag complicates our ability to report on ROAS in real-time. As of that day, the attributed MTD ROAS was 4.65x, far below the 5x goal and not a complete representation of final revenue that would be earned in the coming days and be reflected by a net increase in the actual MTD ROAS.
Ideally, we would let Time Lag run its course and retroactively report on the updated numbers (which is exactly why we typically schedule our QBRs nearly a month after a quarter ends). However, this was a short-term opportunity for increased budgets that we didn’t want to miss out on, so we needed to confidently project out our MTD performance and visualize it in a way that the CFO would understand—with spreadsheets and formulas.
Projected ROAS = (Current Attributed Revenue + Projected Incremental Revenue) / Cost
As per their Time Lag report, this client’s customers average 7 days to conversion, with 54 percent of revenue being realized within one day after first-click and 22 percent of revenue being earned 12+ days after first-click.
The following numbers were pulled on January 28th:
- MTD Spend: $56,370.59
- Current Attributed Revenue: $261,922.06
- Current Attributed ROAS: 4.65x
The screenshot below depicts their average Time Lag results:
If 54 percent of revenue is generally earned within one day of first-click, then we can assume the revenue that is currently attributed to yesterday’s ad spend (which, at the time we pulled this report was $7,692) is 54 percent of the total amount of revenue that will eventually be attributed to yesterday’s ad spend once Time Lag has run its course.
Based on this information, we can project that, once Time Lag runs its course, we will see approximately $14,245 in revenue attributed to yesterday’s spend. That is, if on the morning of March 15th 2020, long after the Time Lag cycle ends, we look at the performance from January 27th, 2020, we are likely to see $14K in revenue attributed to the ad spend that took place that day. $7,692 is 54 percent of $14,245.
The spend numbers from that day will remain the same, as you cannot retroactively spend more. Spend is reported in real-time, whereas the revenue attributed to that spend is accrued over time and attributed back to the original ad clicks that you spent that budget on.
The difference between these two numbers, the 7K we see attributed in the account and the 14K that we are projecting as the sum total, is the projected incremental revenue that we expect to realize between now and the end of the Time Lag cycle.
The report above shows an additional 6.7 percent of revenue was earned on the second day, after a user clicked on an ad. We can assume that by the end of the second day, approximately 60.7 percent of total-attributed revenue will already be realized in the account.
In this case, we can conclude that the current-attributed revenue for January 26th is 60.7 percent of the total-attributed revenue. At the time the report was pulled, January 26th was showing $6,430 in attributed revenue. We can project that the total attributed revenue for January 26th’s ad spend will be $10,532.76.
If we run this model for the remainder of the month, going backward toward January 1st, we end up with a spreadsheet that looks like the following:
Therefore, we are projecting an additional $42K in incremental revenue that will eventually be earned and back-attributed to the current MTD ad spend.
If this plays out the way we anticipate, our adjusted ROAS comes to 5.40x—far above the 5x goal and clearly worthy of a budget increase!
Any experienced PPC professional will see that this model is filled with flaws. We cannot assume that every dollar we spend in the future will have a similar Time Lag and return to our historical trends.
Before we report these projections to our client, we factor in a Pessimistic Outlook Variable (POV). The POV is a conservative adjustment that you can make to your projections so that you don’t overpromise and underdeliver. The POV is flexible and should be determined by your own rationale and experience within an account. For example, if a broken-cart functionality prevented customers from checking out on the site for twenty-four hours, you would be more pessimistic than normal.
The POV will adjust your incremental revenue projections to a more conservative estimate.
I chose to add a POV of 20 percent before reporting to this client and outlined it further down the spreadsheet as such:
The 20 percent POV outlined above basically says that even if my original projections are off by 20 percent, we will still back out at a 5.25x ROAS for MTD ad spend.
The CFO agreed with the data and granted an increase in Q1 budget. A month later, I checked back on the actual results that are attributed to the 56K in ad spend incurred between January 1st and January 27th, and the adjusted revenue is currently up to $301,111. We’ve picked up nearly 40K in incremental revenue since I pulled those numbers, bringing our ROAS up to 5.34x.
Next time I run this simulation, I guess I won’t have to be so pessimistic.