Do you know how to set up your Facebook Ads Analytics in spreadsheets?

I’ve spent weeks talking with digital marketing, advertising, and even corporate organizations about their advertising analytics, their challenges, and ways to make their jobs easier. While Facebook’s own Ads Manager is supposed to make Ad analytics easy for its users, the overwhelming comment from all organizations we’ve spoken to is that they need to put in some serious work beyond Ads Manager to get the Ads KPIs ready for contextual analysis with other channels and, even more work for preparing campaign result presentations. Interestingly enough, even if companies have the ability to pay thousands of dollars for dashboard tools like Tableau or Domo, users continue to turn to their spreadsheets for analysis and data manipulation because of the ease of use and customization capabilities.

Last month, I got to know a corporate customer that was spending well over $30,000 a month on paid social media advertising. The company, very proudly, boasted that they were focusing on data every day. First thing each morning, they would go to Adwords, Facebook (and Instagram), etc to get a snapshot of their strategies. The problem that they were having (which hundreds of our users were also having), was twofold:

1) They were failing at creating a data analysis culture that understood data needed to be looked at consistently (24×7) and in context of all media channels. As a result, they would miss some clear signals (ie, my ad fatigue rate just went above the threshold where ROI requires us to pause the ad) and would waste money on campaigns (big $21K booboo in one incident!)

2) Ironically, even with all the right intentions, they were spending more time building reports in their spreadsheets than analyzing their data. With new KPIs that they needed to track, changes to their strategies, and individual campaign analytics, this was costing them in overhead.

Ever since Factivate started providing spreadsheet gurus who help our users automate their data analytics reporting, I’ve seen this problem come up over and over again. As a result, I’m writing this article to help you get started on how to set up your Facebook Ads analytics in a spreadsheet. In doing so, I will show you how to analyze your campaign KPIs using an Excel spreadsheet.

How to Build your Spreadsheet

There are two ways that you can build your Facebook Ads spreadsheet. The first one is to use the Facebook Ads KPI template we have created using Factivate (www.factivate.com), log in with your social media account, and then have your spreadsheet automatically update your data in real time. This takes about one minute. Then all you have to do is share your report and showcase how brilliant you really are.

The second one is to do it the old manual way in Excel. Note that you will be required to update your spreadsheet continuously in order to keep your data relevant.

Why do I need a Facebook Ads Analytics Spreadsheet?

Regardless of your budget, company size, or business objective, when it comes to paid social media analytics, you need to be conscious of how your campaigns can lead to an increase in the bottom line (ie., ROI).

To ensure that you continue to deliver relatable ads that resonate with your key demographics, you need to continuously track, test, and measure the reactions to your paid media campaigns. That’s what this spreadsheet will help you do.

Step 1: Build Your Spreadsheet

To begin, open your Excel spreadsheet and create the following tabs:

CampaignsWoW – this will be an internal company dashboard to view your client (or entire company’s) paid social media analytics
ClientDashboard – shared with the client and aggregates campaign kpis
IndividualCampaign – an individual campaign tab that looks at KPIs and helps you focus on the campaign’s impact and ROI. We will use this when we AB test as well.

Step 2: On the ClientDashboard

For the purposes of this article, this tab summarizes all of your paid Facebook Ads (or instagram ads) activities even though you could essentially do the same for Youtube, LinkedIn, Twitter, etc…

Start by creating the following rows:

• Impressions
• Leads (site clicks)
• Conv (CTA clicks)
• CTR
• Cost
• Cost/Conv.
• Conv. Rate
• Avg. CPC (if CPC campaigns…if CPM or CPP, replace CPC)
• Avg. CPA
• Avg. Relevance Score
• Avg. Frequency
• Revenue
• ROI

For your clients, you will decide whether you want to measure and report on a week-over-week or a month-over-month interval. Obviously, the more often you can build these reports, the better you will understand your data, and the higher your ROI will be in the end. When completed, you could end up with something like this:

Some clients monitor Relevance Scores and Frequency Rates (in conjunction with CTR) to determine an ad fatigue rate. We choose to monitor these two very carefully to make sure that ads stay relevant to a demographic. Consequently, our analysis might mention that that we maintained a high Relevance Score across all ads, which helped us maintain a healthy frequency rate and this is why CTR was high across all paid ads. If the ratios, on the other hand, were low (as in the example above), then we would likely realize it quickly, pause or change the ads, and ensure that the ratios come back up without wasting a lot of time and money.

You can then add a number of graphs to indicate growth trends based on your strategies. These can measure 1) Leads/Conv./Impressions on a bi-weekly basis for the year, 2) Revenue and ROI for the MoM or WoW period, and/or 3) Facebook Ad KPIs (leads, conv., CTR, cost). In the end, your tab could end up looking like the one below (if you’re solely reporting on Facebook Ads):

Note that the Analysis & Recommendations section is an important part of the report and should not be taken for granted. This is where you can tell your data story (or analysis) so you need to give it some thought.

Step 3: On the IndividualCampaign Tab

In this tab, you will need to consider the campaign objective because you need to run ads differently depending on your goals. As a result, your KPIs will also be different. In our company, we created six IndividualCampaign tab templates that track the following

1) Objective – build brand awareness and community growth

• # of website visits from ad
• # of followers or page likes from ad
• engagement rate from post (if promoting content)
• conversation rate (if promoting content)
• amplification rate (if promoting content)

2) Objective – Increase purchases (drive revenue)

• # of website clicks or leads from ad
• # of CTA clicks or conversions from ads
• # of purchases from ad
• # of return visits from ad
• # of purchases per person from ad
• ad CTR
• ad fatigue rate
• ROI

3) Objective – increase visits to a piece of content

• # of visits to the content from ad
• # of content downloads / signups/etc from ad
• # of shares of piece of content from ad

4) Objective – increase Facebook page likes

• # of new followers or likes from ad
  engagement rate, applause rate, amplification rate on posts
• # of shares from ad (referrals)

5) Objective – Drive attendance to an event or webinar

• # of visits from ad
• # of signups from ad
• # of shares from ad

6) Objective – Download mobile app

• # of app downloads from ad
• # of app engagements from ad

Your KPIs might be different and the list above is not an all-inclusive list (as you will see from the example below). So, once you have decided on your campaign objective and KPIs, it’s time to create your individual campaign report. First thing is to decide whether you’re going report on this data daily, weekly, bi-weekly, or monthly. In our case, we report it on a daily or weekly basis.

For the revenue objective, we add the following rows:

• Leads (website clicks) from ad
• Conversions (CTA clicks) from ad
• Google Analytics conv. data to match with the CTA clicks
• Purchase data (if available)
• Budget
• Amount Spent
• Avg Frequency
• Avg CTR
• Avg Relevance Score
• Cost metrics
  • CPC
  • Cost per total action
  • CPP
  • CPM
• Engagement Metrics
  • newsfeed impressions
  • impressions
  • total actions
  • clicks
  • reach
• Revenue
• ROI

Now it’s time to add your trending graphs. For this template, we track: cost metrics, investment metrics, campaign results, campaign effectiveness, and engagement metrics. Note that when we interviewed data centric marketing organizations we realized that they would not like to add internal ad ratios to these reports since they could confuse their clients. Again, there is a comments section to tell the data story during and after the campaign. When finished, your spreadsheet could look like the one below:

Step 4: Set up your CampaignsWoW tab

This is the heart and soul of your internal tracking and where you could track all of the campaigns for your client (with internal ratios) or all of your channel campaigns for all of your organization. When done, you should end up with a spreadsheet similar to the one below:

In order to get started, we created five different sections:

• Activate Campaigns WoW Summary
• Ad Efficiency
• Facebook Ads Aggregate Campaign Data (by campaign)
• FB Ads last 7 days
• Ad Activity last 7 days

For Active Campaigns WoW Summary – since we present this data on a week over week basis, we add the following rows:

• Ad Spend
• Avg CPC
• Avg CTR
• Website Clicks
• CTA Conversions
• Cost/Conversions
• Avg Frequency
• Avg Relevance Scores

For Ad Efficiency – this is where we focus on the effectiveness of our ads with our ad fatigue rate ratio. To get the ad fatigue ratio we have to calculate the frequency rate change and the ctr change. Thus, we added the following rows:

• All Ads Fatigue Rate
• Avg. CTR
• Avg. Frequency Rate Delta
• Avg. CTR delta

For Aggregate Campaign Data – this is where we look at all KPIs per campaign name. In it, we put campaign names in the Y axis and KPIs on the X axis. As a result, we add the following columns:

• Budget
• Amt Spent
• Balance
• Avg. Relevance Score
• Impressions
• Clicks
• Avg CTR
• Avg CPC
• Avg Frequency Rate
• CTA clicks

In the end, it ends up looking like the image below:

For Facebook Ads Last 7 Days – we simply compare our top KPIs per strategy. In the example below, we’re monitoring only Website Clicks and CTA conversions on a daily basis. This can vary greatly depending on your objectives.

For Ad Activity Last 7 Days – this is where you can compare your spending per channels. In the example below, we’re comparing Facebook with Adwords PPC campaigns.

Step 5: Collecting your data

Now that set up is completed comes the arduous task of collecting your information and putting it into your spreadsheet reports. There are ways to speed this up. You could write an ODBC connector (expensive) or pay for a query or other service to do this. It requires a certain skill to write the queries so most people resort to the manual import of data from different sources. Note that about 88% of spreadsheets have errors associated with these processes so be careful that you do this correctly. If, on the other hand, you want this data aggregation to be done automatically for you, be sure to sign up for Factivate. Once you’re a Factivate user, this process is done, in real time, for you automatically without writing any complex queries, ODBC connectors, or programming languages.

In the spreadsheet described in this article, we import information from Facebook Ads, Google Adwords, and Google Analytics. Be sure to devote at least 2 hours every time you want to import from these sources into Excel since you will need to format the imports and verify that there are no human errors.

Marketing Data Analysis

Once your data is in place, you will finally be able to analyze your data. Whether you do it by campaign, or on an aggregate basis, be sure to pay close attention that the KPIs are growing towards your campaign goal on a daily basis. Not doing so can severely affect your campaign’s performance.

Conclusion

You don’t need to be a data scientist to build your first paid social media analytics portal with Facebook Ads. With a little effort and a basic understanding of spreadsheets, you can find which ads perform best. Remember to keep iterating and trying different ads to make sure that your ads stay relevant and relatable. Be sure to use your spreadsheet, not to report vanity metrics, but to understand your data story. More importantly, be sure to keep your data updated continuously.

There are many tools out there that can help you understand your digital advertising campaigns. Hopefully, this post will help you get started in discovering your digital campaign KPIs. If you don’t want to continue updating your spreadsheet manually and would rather have a Factivate team member build you your spreadsheet that automatically updates with your Facebook Ads KPIs, simply sign up for Factivate and download the Facebook Ads spreadsheet template by clicking on the button below:

About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. Consequently, Factivate provides the most accurate data analysis for digital marketers and advertisers in order to increase their ROI and profitability.