How To Automate Your Facebook Ads Reporting With SuperMetrics

If you’ve been doing media buying for any period of time, you’ve probably spent a bunch of time creating reports and inside spreadsheets.

But reporting shouldn’t take hours.

In this video posted on my YouTube channel I show you how to automate your Facebook ad reporting with SuperMetrics and easily get all the valuable insights you need.

Grab the spreadsheet here and follow along with the video!

Video Transcript

If you have been doing media buying for any period of time, you have probably spent a bunch of time creating reports and inside of spreadsheets. I know when I got first, first got started media buying, I would spend a ton of time inside of spreadsheets at the end of every month, like reporting would take hours cause I would have to make every one of my clients a report and I would have to make reports for us. And it was just this huge waste of time. And I didn’t know anything about spreadsheets or automation or any of that. And so I would waste a lot of time just doing this. And today I can say pretty confidently that we have figured out how to automate a lot of really valuable insights that we get for our clients. We’ve automated. Most of our reporting using some really simple tools and some really simple like Google sheets, formulas.

So that’s what I want to show you inside of today’s video is and this video and a couple of the next videos. I want to show you how we automate a lot of our reporting. And I think we’ve built some pretty cool, valuable dashboards and, and tools for our media buyers. We use a tool called Supermetrics. If you’ve ever heard about it, it’s just a little API tool to pull data from ad platforms. And it’s really cool because you can pull data from a, you know, Facebook, you can pull it from Google analytics, Google ads, all of different spots. And I find it very flexible and very useful, and we can use it to automate a lot of our stuff. So that’s what I’m gonna show you inside of this video. Just for some example, here’s like some dashboards that we build for our each one of our clients.

So we can quickly see how things are trending and performing over time. We’ve got weekly reports so we can see how things break down over over weeks, we’ve got monthly reports so we can see how things are doing on a month, like a day to day breakdown. And what I think is super cool about this is at the end of every month. Now when we need to update our reporting, it’s literally come in here, hit, duplicate change out this date and pull this down and you will see the formulas do all of the rest of the work. So what actually used to take me hours every single month to build new reports for myself and for our clients, we can now do in like five minutes. And so that’s super valuable. This is all because we use sheets and Supermetrics, so that’s what I’m going to show you how to set up right now today.

I’m just going to show you how to do a really simple KPI report. So this is what we give to our clients. Every single time they sign up with us and this way they can quickly track their spend and what kind of metrics they’re getting off of the, the ad platform. And and if you can get started with this one, it kind of show you like an introduction into what are all the cool things you can do with Supermetrics. And then from there we can get into more advanced reporting. We’ve also been able to build stuff like this, where we’ve got like creative breakdown, so we can see what copies, creatives, headlines, all that kind of stuff is working best. Again, all of this is just automated up with Supermetrics. So if you’ve never heard of Supermetrics before it is a Chrome extension plugin that you can use to pull data from all kinds of data sources.

So pretty much any ad platform, Google analytics, a lot of CRMs and stuff like that are all available on here. And they have a Google sheets connector. They also have a Google data studio connector. If you’re a dado studio person, I personally have, we’ve tried the data studio things so many times. I just don’t find it as flexible as I would like it to be. So anytime we have changes with, with with clients, for, you know, new campaigns, new lead, magnets, new, anything that we’re promoting, it’s pretty simple to like jump into a spreadsheet, add a few columns and set up a few things with data studio. I don’t find it as flexible as I would like it to be. So for that reason, we still use sheets for pretty much everything. We’ve tried a couple of times to move our data studio and I just keep ending up back in spreadsheets because that’s what worked best for me.

So that’s what I’m gonna show you how to do here. So if you don’t have one of these, you can grab, I believe you can grab a free trial just below this video. I’ll put a link and this is basically the spreadsheet that I’m going to show you, how do we update? So this is one of the spreadsheets that we give to our clients every single time that we get started with them. And again, this takes just a little bit of time to set up in the very beginning and then what they have access to is they can quickly come in and see on a daily basis. Hey, what is their spend? You know, and the high level metrics that they need to see like clicks leads and sales is pretty much it. I will also make sure you have a link to grab this spreadsheet right below this.
So if you want to get ahold of this again, it’ll, the link will be just below what I find with this, the easiest way I’ve ever found to set this up.

So this template, you can see, this is like a monthly breakdown that just links to these a month, one month, two tabs. And then what I have found works best is have one tab for every source of data that you want to pull. So you could pull Facebook, Google analytics, Google ads, if you have a CRM data. And again, especially with Facebook and iOS 14, and all of the challenges that have been coming with iOS 14. One thing that is super valuable with Supermetrics is you can pull key ad platform metrics, like spend clicks, impressions, CPMs, all that stuff from the ad platform. Then you can actually pull leads and sales from Google analytics, which will likely give you a much clearer reporting for what kind of performance metrics you’re actually getting.

And then your clients will love you for that because all business owners really want to see how much money they’re actually making and not what the Facebook pixel or Google ads is reporting, which we have found to be wildly off and getting even worse in the last couple of months. So this is a really cool way to just do this through automated spreadsheets. And then definitely you want to make sure you have some platforms set up like hieros or wicked reports or something like that on the other end. But again, for our media buyers, we need to be able to see just quickly, like spend clicks, general leads, general sales, how are things flowing again? Our clients also really like to see this. So that’s what this spreadsheet is all about. So let’s talk about how you can actually pull this data from the ad platform and automate that with Supermetrics.

So once you have Supermetrics, you will find it up here in the ad-ons. You just go to Supermetrics launch this sidebar. Okay. Once you get Supermetrics open and loaded here, you will find that you have a couple of things you can choose from. So basically, what is the data source you want to pull from? And this is where you can see, you know, you’ve got a ton of options here. In this example, we’re just gonna be pulling from the Facebook ad platform, because that’s where we want to pull it from you select the ad account. So what is the ad account? You could select multiple ad accounts here. So especially if you’re running multiple ad counts for the same business, for any reason, you can pull those all there. What is the date range that we want to set this up? So we typically start this with the first day that we start running ads for the end date, you can actually just type in day.

So you could type in something like yesterday, today. I always type in yesterday because I find that that way we can have one full day of data. If you put today, what you’ll find is when it refreshes, it’ll just refresh to that much of the day. Cause it’s pretty instantaneous. And then what are the things that we want to pull from this pixel? So what is the data sources that we want to, the data metrics we want to pull? So amount spent reach impressions, CPM, link clicks, click through rate leads, complete registrations purchase, purchase, conversion value. These are pretty much the basic things. You can also pull any kind of custom conversions you’ve set up. So if you’ve set up custom conversions for any kind of event, you can get those from here as well. I do find that what works best for, for for the way we set things up.

I typically pull metrics here and then calculated metrics like click through rate, conversion rates, all of that stuff costs per rates. I will typically formula those because sometimes it can get weird if you’re trying to do sums and averages through through date range for the dimension. So this is where you could split by date, campaign, name, ad name, ad set, name. There’s a ton of options here. And once you kind of understand how to use this, it gives you a lot of flexibility because you can pull reports for campaigns, ad sets, ads, all kinds of really great stuff. But for this one in particular, we’re just gonna pull by date. Cause that’s just, we want to see, I do typically switch this to sort by descending and then for filter down here, we don’t need to put anything, but again, this is where you could filter by campaign name, ad set, name, ad name.

So if you just wanted to look at like cold traffic, top of the funnel, traffic, whatever that is, you could filter by campaign name or something here, which makes it super valuable. And then one tip is, you know make sure that this is set for on conversion date. And also I do always just use this advanced setting, keep results on refresh because basically what that does is if the API ever has an error, if you don’t use this, it’ll just remove all the data and say, Hey error. If you do use this, all of the data will stay and then it will just try to refresh it again. So one thing to know with Supermetrics, you’ll see here, it says data will be inserted in cell a one. So wherever you have yourself selected is where that query will start. So if I had it here in Aesics, it would just drop it all right here.

That’s what we want. We wanted to start in the corner. And so yeah, just to get data to table, this is going to run. So now we’ve got our data here and you can see right here, this is where it kind of tells us everything about how this is set up. Now we can see how all of this is set up. And then we would just want to do is come over here to our tab breakdown. And this is the formula that we’re going to use to pull this data from that source. So if you’ve never used a V lookup before V lookups are a super useful formula for media buyers to know, cause you can use this all the time for a lot of really great analysis, but basically what this is going to say is, Hey, go look for this date range, this cell here, which is a date, go look for this.

And I want to go look for this in my data, Facebook tab. And you can just select all of this here. I created a named range, so it’s just a little easier to find, and that would be in this template right here already. So you can just use this template. And then what I want to do is go look for where am I going to find the ad spend? So what column is that in? So date is column one cost is column two. So I’m going to call them two. And then the answer is false. I don’t know. That’s just what the answer is. And so once you do this, you’ll see that what it’s doing is it’s going and looking for the date of 71. And it is pulling that into this this tab right here. So if we look right there, we’ll see 2 57 38.

And if we look right here, we will see 2 57 38. The only thing I’m going to do is I’m going to put a, I’m going to wrap this in a so called if error, cause that just makes for a much prettier spreadsheets later. And if error just means, if there’s an error, don’t put the like hashtag error, just put nothing. And so there we go. And then I want to go find clicks. So I’m going to just take that same formula and where am I clicks? So my clicks are in column 1, 2, 3, 4, 5, 6 column six is where my clicks are. So what I can do is just copy that exact same thing and change this to six CPC. So this can just, again, this is where I was saying you can just use calculated formulas for the best way I’ve found to do this.

So if air just makes it, so our spreadsheets aren’t ugly and then we just do this divided by this, that will give us our cost per click leads. I’m just gonna grab this same thing and I’m going to say, okay, what are the call them are my leads in? So my leads are in column 1, 2, 3, 4, 5, 6, 7, 8, 9 column nine. So here we go. I’m just going to drop that and you’ll see in a second, how easy, like once you do this, once it becomes very easy to keep it updated, which is my favorite part. Because again, I used to spend hours doing this. Now I spend minutes doing this, which is super valuable, actually like a VA spends minutes doing this, which is even more valuable. So the last one, so what number is our sales in? So sales are a number 1, 2, 3, 4, 5 6, 7, 8, 9, 10, 11, 11 is what the number is.

So I’m just going to put this and I would change this to 11 because we’re just using that same formula. And this was in 12 cause I saw this is a calculator range. This one was 12 boom. And this answer is what’s my CPA. So I just take my spend and divide that by my sales. And again, if there’s an error, it doesn’t show that if you didn’t do this, you would see that ugly thing. That’s what we want to avoid. And if air just makes that go away.

So if there is a great formula for pretty spreadsheets, so this is the cool part, right? Once we set this row up, once we literally just drag it down and we’re good to go. Now we have just calculated this entire month. When we go into month two the easiest thing to do here is you can actually just copy and paste this row right here, take this, copy it, Paste it right there, drag this down.Now we’ve just done this entire month. And again, the coolest part right at the end of this month when we want to duplicate this for the third month, come in here, quick click duplicate. And all we have to do is change this date and that be lookup formula. We’ll do everything else for us.

So again, I used to, once I discovered Supermetrics I used to rebuild these queries every single month and that would also take me 20, 30 minutes per report. It was just an inefficient way to do this. This is the fastest, most efficient way I’ve found to do this. We just built this entire report in like 20 minutes. And now this can be updated in minutes. And if I, again, if I want it to, what I could do is I could pull YouTube data into here, Google ads data.

And I could add that into this spreadsheet. So we could see Facebook and YouTube combined and separate. If we wanted to look at Google analytics data, we could really easily create a tab here that pulls Google analytics data. We could sort by paid ads data only, or we could store by Facebook ad data, only something like that. And then that way we could pull leads and sales directly from Google analytics. Again, the power here becomes very flexible and it becomes just a really great way to automate a lot of this stuff. Once you just know how this simple, you know, simple tool works. And again, this saves us hours and hours and hours every single month. Once we want to automate this. So we just opened this up and once it’s done loading, we’re going to set it to update every single day at midnight.

And that has been the fastest way to keep this up to date that I have found. So what you can, what you can do to make sure that this automates is you just come here to modify it. You go to schedule, you create a trigger and you just schedule this to refresh every day. So I just do this daily and I do this at midnight. Our, so I just do this at midnight. Usually California time, you can see I’m in Europe. So I just want to make sure that we have one complete day. That’s the biggest goal. So I save changes and now this will refresh daily every single day at midnight. And it will refresh automatically with yesterday’s data. So you can see, I already had one of these triggers cause I already set this up, but this will refresh daily at midnight.
Just so you know where this lives. Once you create a Supermetrics query, you’ll find a little hidden tab right here called Supermetrics queries. And basically this little interface, all this is doing is creating this row for you. And what this row does is this tells you, Hey, this is when this query was created. This was when it was last updated. You can see if there’s any errors right here, because it will tell you when it was last refreshed. And this will tell you, Hey, here’s the date range where you start, where you end. So if you ever want to come in here, just make a quick change. You could change this to oh five and that would update the next time this runs here’s all the other data that you will see. And so when you create multiple queries, they will all live right here. And basically when Supermetrics refreshes, what it’s doing is it’s just refreshing everything it finds right here. And then for the client’s sake, you know, all we do is come in here and hide these tabs at the end. I’m just going to update this really fast.

So what we did here in like 25 minutes was we set up this automated report. So now we’re pulling from the Facebook API through Supermetrics right into a spreadsheet, and then this will automatically be kept up to date. So we can just send clients one link and it just say, Hey, if you ever want to check your spend, how things are going, you can jump in here quickly. See what’s going on. It’s automatic, automatic updated automatically daily. So we don’t have to mess with it. And then updating a new month or adding some new columns become super easy. It’s very flexible. And then again, the other thing that I think is super valuable about this is once you understand how this works, you can create more in depth reports for yourself. So you can see in this example, we have a very similar report, but here we have all of these extra metrics that would be really valuable for a media buyer to know.

So we can look at click through rates, conversion rates, CPMs, all of this stuff, stuff that our clients don’t need to see, but our media buyers definitely need to see. And then going forward, you can start getting more advanced, like, cool, how do we pull this data so that we’re looking at ad platform metrics on the front end. And then we’re looking at Google analytics data or a different source of data on the backend. So we’re getting a much clearer picture of how many leads in sales we are actually getting. Because again, we do find that a platform pixels are not as accurate as they used to be for sure. And and so there’s definitely conversations to have around that, but this is the basics, right? Let’s just get set up with a few automated reports. Let’s understand how we can pull this data and use really simple formulas to start setting up valuable spreadsheets and like valuable reporting tools, more than anything.

And then from there, you know, you can really start to get really flexible with what you’re doing. I hope you found this valuable. If you did again, you can grab this template and you grab a free trial of Supermetrics just below this, use this automated stuff, get better, spend less time reporting more time doing analysis and making good decisions with that data. If you have any questions, drop them in the comments below, happy to help out as much as I can. And once I get a few more of these Dawn on how we can do dashboards and creative analysis, I’ll link those around this video too. So that’s all I got. I hope you have a great day out there and I’ll catch you next time.