The OMGosh Spreadsheet

Ok, I know I owe a lot of updates for my startup experiment. I promise I’ll get to that. For now, though, I’m compelled to share this, because it was massively eye-opening and valuable to me.

I run Facebook ads. A lot of them. Doing some rough math on just 2 of my accounts, in the past 6 months my ads have been seen by more than 15,000,000 people. About 4,9000,000 people have engaged with them in one form or another (like, share, comment, click). And people have clicked on my links just a tick under 1,500,000 times… in SIX months.

All if which makes it even more surprising that I’m just building the OMGosh Spreadsheet (aptly named after my immediate reaction to seeing the spreadsheet for the first time). I’m kicking myself for not doing this earlier. But, when things are working, I usually just let them work.

This is all a result of getting one of my primary Facebook ad accounts disabled a few weeks back. There’s a whole other story there. But, for now, it’s enough to know that it happened. And I was surprised. I don’t run the kind of business that leads to trouble with Facebook. But, again, that’s another story.

As strange as this may sound, there’s a part of me… a fairly prominent part, in fact… that enjoys getting kicked in the teeth and worked over a bit. If only because I almost always come back stronger and better in the ensuing rebuild. Some of the most painful experiences of my business life have been the direct catalysts to creating something even better in the aftermath.

So, after doing my fair share of pointless complaining about Facebook, I got to work doing better. And one of the things I wanted to focus on was getting to a really efficient ad spend. As a rule, the most granular level of profit/loss reporting I get is daily. And, as a rule, that tends to be ok.

But I wanted to see what kind of a difference there is between the various hours of the day. I know that I tend to do well in the early mornings until about 10:00. And I know I tend to do well in the evenings until about 10:00. But I’d never really quantified it.

My gut told me there was probably a 30-40% efficiency variance to be found throughout the day. Ummm… no. Only taking into account meaningful advertising hours, there’s a 9.5x difference in return on ad spend (ROAS). 9.5x! Can I just repeat that once more? 9 point 5 times!

Let me put it another way… $1,000 in ads running at the prime time of 8:00 PM would return about $1,260. Not bad. Not great. But not bad.

Now, take that exact same $1,000 on the exact same day and spend it between 8:00 and 9:00 AM. Guess what? You just made $2,480.

Just for effect, go back and read those last 2 paragraphs again and let that sink in. What if you could instantly improve your bottom line 3x? How about 5x? 10x? That’s massive, right?

So for months I’ve known there was something there. But I way (way, way) underestimated the sheer size of that something. Truth be told, I should have done this earlier just for the 30+% I thought there might be there. Why didn’t I? Because I naively assumed the difference would only be that big in the off hours. So I thought it would be 30% of the smallest numbers.

But look at that chart again. Many of my worst hours, from an earnings per visitor standpoint, are the highest volume hours! So I’m actually spending a disproportionate amount of my budget right at the time when I don’t want to. I spent about $55,000 in inefficient ads during prime time, but only about $39,000 on my most efficient ads during prime time.

Now, there’s a lot going on here. I don’t know how much more budget Facebook would even allow me to spend during my desired hours (about 6:00 AM – 11:00 AM). So it’s not necessarily valid to think I could re-purpose my entire ad spend to my preferred window (although I’m testing that as I write). And there are a handful of other scenarios here that add further depth and complexity to the equation.

But I still can’t believe it took me this long to do this. No matter how I look at it, this is incredibly actionable data… which makes it ridiculously valuable to me (and also the only kind you should be looking at in your business… everything else is vanity). Even if it just leads to splitting my campaigns into time ranges and treating them differently, I’m so much better off knowing this.

But what if there’s even more to it? Let’s assume my entire audience is demographically consistent (because Facebook says it is). But is it possible my morning and evening audiences are psychographically disparate? If that’s the case, I can treat it like a new audience and split test ads to the evening audience that are different from the morning audience. I might be able to transform BOTH audiences into premium audiences.

For example, what if my morning audience is looking BACK at an awful night and has a strong desire for a solution (I’m making stuff up to not reveal my audience). That view makes them great converters for my change-based ad and funnel.

But what if my night audience is a FORWARD-looking audience with hope for a bright future? That’s an entirely different message for them to optimize conversions. That simple difference would likely mean the evening audience never has the urgency of the morning audience. But it also implies I can present a different message to them and get better results than I’m currently getting.

I’m so incredibly stoked! This will certainly become a regular report in my arsenal. It’s fantastic data to know and ACT on.

What do you need to get the same? There are some gotchas here, so pay attention…

I first wanted to get this report months ago. But I had some non-obvious obstacles to get over before I could make it happen.

First, I routinely send visitors to video sales letters (VSLs). Those could take as long as an hour to watch and convert. Longer if they wait to watch the video later. But I need to correlate the hour in which I SPENT the money, with its effectiveness, not the hour in which I MADE the money. So my existing analytics weren’t helpful. I suppose I could have made a rough approximation of prior-hour attribution, but I don’t like to do those sorts of things (even though I would have been far better off having taken that action than waiting as long as I did for more accurate data). So I started recording the time of the original page view into my sales analytics. That took a little tech magic to make happen.

That created my next challenge. Facebook allows me to day-part my ad spend… but only based on the time zone where the VISITOR is. So all my timing info didn’t actually give me the data I needed to run the report I just ran. I needed everything (including my ad spend) time-shifted into the visitor time zone and hour. That was another piece of data I couldn’t recreate from past analytics, so I had to start tracking it and let it season before it was valuable.

Up to that point, I was hot on this report. But stuff happened, and I never got back to building the report once I started collecting all the data I needed.

Once I started putting the report together last week, I had all the revenue side of things, but no corresponding expense data. I assumed I would have to do some serious Facebook API gymnastics to get that side of things in the format I needed. But that ended up being, literally, the easiest part of the entire process. The final piece fell into place when Chris discovered I could get the exact Facebook data I wanted by clicking “Breakdown”, then “Time of Day (Impression Time Zone)”.

Voila! My OMGosh Spreadsheet is born. And cue the image of me falling out of my chair.

I don’t know how well I’ve explained this whole thing. But I’m pretty dang sure this is the most valuable spreadsheet I’ve ever built. Hit me up if you have questions regarding the how, what, and why. I can be slow getting to them… but I’ll try and get them answered as best I can.

P.S. DON’T take this to mean these patterns will in any way apply to you and your business. It would be a mistake to take my sales data and try and overlay it on top of your advertising strategy. This is simply meant to show the method, not the answer.