Don’t count your chickens before they hatch. Usually, people tend to count on something before it happens, or they “count their chickens before they hatch.” In paid search we do the opposite. Our phrase needs to be “Don’t optimize before all your revenue is counted.” But most accounts have lagging revenue and at the same time we need to optimize based on the most recent date ranges. Well, then, let’s figure out the best way to optimize based on all of the revenue that is to eventually come, but continue doing optimizations based on the most recent data.
Some people only track revenue that came in directly from the last click. Others like to track the entirety of what paid search is netting them in revenue. The most common strategy is to use a 30-day window for conversion tracking.
A minor hiccup in tracking through a 30-day window and using day of click tracking rather than day of conversion tracking is that you have to wait for lagging revenue to come in to get the complete picture. The phrase we often say or hear is “We can also expect these revenue numbers to increase with lagging revenue coming in.” But how do we optimize according to unfinished numbers? If you do bid changes based upon the past 30 days, how do you decide if lagging revenue will put you above your goals or not? For this reason I created a simple way of projecting revenue through Excel.
First thing you need to do is go to “Tools > Attribution > Time Lag” within Google AdWords. In order to get a complete picture of what past lag revenue has looked like you need to utilize a date that is 30 days previous of the current day. Since this was written on July 20th, that would mean June 20th would be the last day of use in this analysis. Using April 20th through June 20th the data shows as such:
The image above shows that after day one, 47.27% of our revenue has been accumulated. The next day 5.07% of revenue is accumulated, meaning that 52.34% of revenue has been accounted for after those first two days. This can be calculated in this manner through 30 days as seen below:
This was a simple formula pattern based off of the past lagging revenue numbers from Google as seen here:
Next step is to create a sheet set up as seen here:
You enter the current date in C2 on this sheet. Then at B5 enter the date you would like to go back to from a revenue reporting stand point. In the “Days Gone” column use the formula: “=$C$2-B5” and then drag that formula along with the date column down as far as you would like. In this case we are doing a last 30 day analysis so we will drag it down until July 19th. From here we can go to “Dimensions > Time > Day” and enter the current revenue being reported for each day in the report in column D. In the “Revenue Accounted For” column the following formula needs to be put into place:
=IF(ISNA(VLOOKUP(C:C,I:J,2,FALSE),100%,VLOOKUP(C:C,I:J,2,FALSE))
What this does is if 1-30 days have gone by according to column C, then it will take the % in the Lag Projections associated with that number. If over 30 days have gone by then the formula will assume that 100% of the revenue accumulated off of clicks for this day has been accounted for. Finally, the “Projected Revenue” column is: “=D5/E5.” This simply takes how much revenue has been made from the days worth of clicks and gives a projected revenue number based on previous lagging revenue projections.
Cell G2 in the example above will then equate to SUM(F:F) in order to get the total projected revenue for this date range. Then, in order to use this analysis on other reports at Campaign, Ad Group, Keyword level you can take the formula: =SUM(F:F)/SUM(D:D) on this sheet. This will give you a percentage that you can use to estimate the amount of revenue different areas of the account will end with from the date range used in the original report. In this case that number is 130%.
So, let’s give an example of how this number can help with bid changes based on the past 30 day performance. Previously, you may have been downloading the past 30 days performance and making bid changes based upon what you were seeing in ROAS by simply using: “=Total Conv Value/Cost.” Let’s say your ROAS goal is 300%. If a keyword is at 270% with this formula you typically use, then it is likely to really end at 350% (above goal), and you should actually be willing to raise the bids on this keyword as a growth initiative rather than lower the bids as a ROAS adjustment.
The snap shot above shows how this all works. Cost is at $100.00 for the time frame (and this will not change). Revenue is currently at $270, but as we just saw based off of the lagging projection estimates, on average each keyword in the account will end with 130% of what is currently being shown in the revenue column once lag revenue rolls in. So, “=Current Revenue*1.3” gives us the projected revenue. Once we use the projected revenue to calculate the projected ROAS we see that it is at 351%.
These same types of reports can be run at the campaign or ad group level in order to show the true projected performance of the account, rather than just knowing lagging revenue will be coming in. This helps with reporting and optimizing based upon more accurate numbers.
This system has been something I have used quite often and from what I have seen it typically has been within 5% of what the numbers end up being once the 30-day window is over unless drastic changes have been made to the account.
