An Elegantly Simple Guide to Help You Track Facebook Ads Analytics

Giorgos Psistakis

There are seemingly thousands of guides on the web talking about Facebook Ads, optimizing it and how to track Facebook Ads metrics and set up your analytics. You will be amazed at the breadth of the topic.

Some time ago I wanted to start experimenting with Facebook Ads for Blendo, and I failed miserably. The engagement I was seeing was hectic at least and not at all targeted.

Let me give you an example. Do you see the screen below? Seems good? Bad?

Track Facebook Ads Analytics

OK let me give you a hint… You probably can see it already, but the above Facebook Ads Boost gave me 2,225 paid impressions, and in turn, that gave me 31 link clicks.

Track Facebook Ads Analytics

Excellent work George, your post reached 2.5K people. Self-high-five.

The thing is that the number above is a vanity metric.

Who read the post? 31 people.

What was the conversion rate? Ahhh 31/2,488 = 1.2%. However, these people only read it?

What was my Facebook Ads ROI? I do not know, and that is the question.

Track Facebook Ads Analytics

There I said it. I thought that reading a few of the guides out there will make me a bit knowledgeable in Facebook Ads. It didn’t (yet), though there were things I learned:

  1. Track everything! Use Google Tag manager, use UTMs, anything.
  2. Add events on actions and CTAs
  3. Have to burn more money to try and fail to make it work.
  4. Measure, measure, measure.
  5. Vanilla Facebook Ads analytics have a high learning curve
  6. There are a lot of click farms out there

At the same time, we had users and customers from marketing agencies joining our ETL as a service tool that use our Facebook Ads or Google Adwords data integrations. They used it for getting their advertisement data into their data warehouse.

That was a revelation to me. I thought that there are super niche tools for optimizing Facebook Ads, why did someone need raw Facebook Ads data into a data warehouse?

So I did what I should have done originally. Pick up the phone and asked why? Over time I found out things that were interesting and I want to share my learnings with you.

Excel is King

It seems there is a huge amount of people doing everything on super bloated excel sheets! The sheer of this is unbelievable (and I am a fan of excel). Not only Excel but Google sheets too. There are companies backed up by google sheets scripts that make a humble worksheet a powerful business intelligence tool.

Moreover, many marketers do that too.

However, there is a shift. Marketing agencies shift from this endless exporting of data to CSV files or having a developer building custom ETL jobs every week (or less).

To the data warehouse and beyond

I was discussing with one of our first customers when he told me at the time he was evaluating Blendo that he needed to add 99+ Ads data sources to push all his data into a Microsoft SQL Server. Since then we have many analysts and data-savvy marketers that do something similar.

One of the significant advantages, is they have a granularity of their data to the ad-level and across campaigns, along with customized aggregated dashboards of their Facebook Ads accounts and campaigns.  So I started asking some of the important metrics these people measure with SQL.

Essential Facebook Ads Metrics

There are over 60 metrics available in Facebook Ads. Of course, the choice of a Facebook ad report differs between companies and campaign goals or objectives, but certain metrics were common.

Some of the most common are:

  • Frequency
  • Impressions
  • Social Reach
  • Amount Spent
  • Cost per Click
  • Unique Link Clicks
  • CTR
  • Engagement

At the same time, I needed to track some of them. Having a dashboard for my own Ads efforts with 60+ metrics would not work very well for me. Besides, I didn’t need all of these.

Where are my Facebook Ads Analytics data?

I was lucky enough that at the same time one of my favorite BI tools and dashboards, Chartio, posted on their blog a series of posts about custom dashboards for CMOs and data-driven marketers.

One of those was a Social Media Dashboard, which included metrics from social channels, ads, shares, engagements, followers, etc. To create such a dashboard, I needed to pull data from my marketing channels, and one of them that I experiment now is Facebook ads.

By combining valuable information from the above post along with a brainstorming of our Facebook Ads analytics needs we found 12 metrics we wanted to track.

However, I needed to get my data somehow first and then analyze it. I used Blendo to pull my Facebook Ads data into a PostgreSQL database first. Now that I solved one technical problem in a few minutes, I needed to analyze these data.

But as I am not an SQL expert in any way I used the help of our data sommelier, Eleni (where she describes her adventures on Predicting Email Churn and Clickstream Analysis and Data Mining and Lessons learned from building a Hello World Neural Network).

Tools used: All the dashboards you will see below are made by Chartio along with data pulled from Blendo.

Tracking Facebook Ads Analytics with SQL

So here is what we (currently) track along with the SQL code. I will not explain each metric as there are many places to find such info, but I will provide some of the links I used to get started. We have also separated in categories like Content Reach, Audience Engagement, Cost, and Audience.

Content Reach

Content Reach focuses on how many people are aware of what we publish on our blog.  The metrics we measure are:

Impressions

The Impressions metric tells us the number of times an ad was viewed. People may see an ad multiple times and so the impressions counter does not refer to unique individuals.

Read more about Impressions in Facebook’s help section here.

The SQL code is below along with News Feed Impressions.

News Feed Impressions

The News Feed Impressions are even more important.The news feed is the primary location where branded content is consumed and this is where we would love to be. A like from someone and their friends might see our story.

Facebook Ads: Impressions & News feed ImpressionsThis Month: Impressions & Newsfeed Impressions

SELECT left(date_trunc('day', date_start::date)::text, 10) AS DAY,
   	sum(impressions) AS "Impressions",
   	sum(newsfeed_impressions) AS "Newsfeed Impressions"
FROM "fb-ad_insights"
WHERE date_trunc('month', date_start) = date_trunc('month', now())
GROUP BY left(date_trunc('day', date_start::date)::text, 10)
ORDER BY left(date_trunc('day', date_start::date)::text, 10)

Reach

Another thing I wanted to see is who saw my ad at least once. That is something I could achieve with Reach, and it does not take into consideration the multiple views.

Read more about Reach in Facebook’s help section here and here.

You may find the SQL code we use along with the Social Reach below.

Social Reach

Have you seen ads with social information like a “2 friends like it”? Social Reach measures this. I am not sure if this metric gives us many insights yet but we measure it.

Read more about Social Reach in Facebook’s help section here.

Facebook Ads: Reach & Social Reach

This Month: Reach & Social Reach

SELECT left(date_trunc('day', date_start::date)::text, 10) AS DAY,
   	sum(reach) AS "Reach",
   	sum(social_reach) AS "Social Reach"
FROM "fb-ad_insights"
WHERE date_trunc('month', date_start) = date_trunc('month', now())
GROUP BY left(date_trunc('day', date_start::date)::text, 10)
ORDER BY left(date_trunc('day', date_start::date)::text, 10)

Facebook Ads: All Time Reach & Impressions All Time Reach & Impressions

SELECT left(date_trunc('month', date_start::date)::text, 7) AS DAY,
   	sum(impressions) AS "Impressions",
   	sum(newsfeed_impressions) AS "Newsfeed Impressions",
   	sum(reach) AS "Reach",
   	sum(social_reach) AS "Social Reach"
FROM "fb-ad_insights"
GROUP BY left(date_trunc('month', date_start::date)::text, 7)
ORDER BY left(date_trunc('month', date_start::date)::text, 7)

 

Frequency

In one of my first experiments with Facebook Ads, I got an email from a friend of mine that told me that he “sees” my ad many times. I explained to him that this way he will never forget us (or me) but jokes aside this is an important metric.

Seeing an ad once is like never happened, seeing it a few times more can help us build awareness, but seeing it more than that could be irritating. So, Frequency is the metric we use along with Relevance score to make sure that our ads do not appear too often.

Read more about Frequency in Facebook’s help section here. You may also read the nice post from AdEspresso here.

In Chartio we build a small part that has the Latest Campaign Frequency along with Relevance Score, Reach and Impressions and here you can find the SQL code.
Facebook Ads: Frequency

Latest Campaign

SELECT avg(frequency)
FROM "fb-account_insights" AS a,

  (SELECT max(date_stop) AS MostRecentStop,
      	max(blendo_imported_at) AS MostRecentImp,
      	blendo_id
   FROM "fb-account_insights"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at = tmp.MostRecentImp

Latest Campaign: Relevance Score

SELECT avg(relevance_score_score)
FROM "fb-ad_insights" AS a,

  (SELECT max(date_stop) AS MostRecentStop,
      	max(blendo_imported_at) AS MostRecentImp,
      	blendo_id
   FROM "fb-account_insights"
   GROUP BY blendo_id)tmp
WHERE (a.blendo_id = tmp.blendo_id)
  AND a.date_stop = tmp.MostRecentStop
  AND a.blendo_imported_at = tmp.MostRecentImp

This Month: Total Reach

SELECT sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN reach
           	ELSE NULL
       	END) AS curr_reach,
   	sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN reach
           	ELSE NULL
       	END) AS pr_reach
FROM "fb-ad_insights"

 This Month: Total Impressions

SELECT sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN impressions
           	ELSE NULL
       	END) AS curr_impressions,
   	sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN impressions
           	ELSE NULL
       	END) AS curr_impressions
FROM "fb-ad_insights"

 

Audience Engagement

One of the essential metrics our users’ measure is the engagement of their ads. So we did too and the metrics that helped us are:

Clickthrough Rate (CTR)

CTR is the percentage of people who clicked an ad out of the people who saw the ad. CTR is an important metric for understanding at what extend the ads we publish drive traffic to our website or data analytics blog.

Read more about Frequency in Facebook’s help section here and this great thread in Quora.

FAcebook Ads: Clickthrough Rate (CTR)

This Month: CTR

SELECT sum(CASE
           	WHEN date_trunc('month', date_start) = date_trunc('month', now()) THEN ctr
           	ELSE NULL
       	END) AS "Current Cost",
   	sum(CASE
           	WHEN date_trunc('month', date_start) = date_trunc('month', now() - interval '1' MONTH) THEN ctr
           	ELSE NULL
       	END) AS "Previous Cost"
FROM "fb-ad_insights"

CTR Per Month

SELECT date_trunc('month', date_start) AS MONTH,
   	sum(ctr) AS "Total Cost Per Month"
FROM "fb-ad_insights"
GROUP BY date_trunc('month', date_start)
ORDER BY date_trunc('month', date_start)

Website Clicks

I was confused with this terminology in the beginning. There are Clicks and Website Clicks. In its general case, a Click represents all the number of times someone has clicked on one of our ads. A Website Click is like when someone is clicking to actions like a CTA that sends him to that URL.

Read this from Facebook and this.

Facebook ads: Website Clicks and CTR

This Month: Website Clicks and CTR

SELECT left(date_trunc('day', date_start::date)::text, 10) AS DAY,
   	sum(website_clicks) AS "Clicks",
   	sum(ctr) AS "CTR"
FROM "fb-ad_insights"
WHERE date_trunc('month', date_start) = date_trunc('month', now()) 
GROUP BY left(date_trunc('day', date_start::date)::text, 10)
ORDER BY left(date_trunc('day', date_start::date)::text, 10)

 

Clicks, Social Clicks & Unique Clicks

We also wanted to have together the Clicks, Unique Clicks and Social clicks of our ads

You may also check this post from Facebook Ads.

Facebook ads: Clicks Per CampaignClicks Per Campaign

SELECT campaign_name,
   	sum(clicks) AS "Clicks",
   	sum(unique_clicks) AS "Unique Clicks",
   	sum(website_clicks) AS "Website Clicks",
   	sum(social_clicks) AS "Social Clicks"
FROM "fb-ad_insights"
GROUP BY campaign_name

 

Post Engagement

Another metric we use is the Post Engagement that shows us the number of actions people take involving our ads and it is closely related to Post Reactions. The higher the engagement then, the more relevant our content is and the more people likely to react.

Read more about Frequency in Facebook’s help section here.

Post Reactions

When I saw the new Post Reactions like Love or haha I thought that the most important could be the Sad or Angry. That is a strong indicator of content relevance.

Read more about Post Reactions in Facebook’s help section here and Hubspot’s post here.

Here is how we calculate in SQL the Post Reactions, Post Engagement, Page Engagement and Link Clicks.

Facebook Ads: Insight ActionsAll Time: Insight Actions

SELECT left(date_trunc('month', date_start::date)::text, 7),
   	count(CASE
             	WHEN action_type = 'like'
                  	OR action_type = 'post_reaction' THEN 1
             	ELSE NULL
         	END) AS post_reactions,
   	count(CASE
             	WHEN action_type = 'page_engagement' THEN 1
             	ELSE NULL
         	END) AS page_engagement,
   	count(CASE
             	WHEN action_type = 'post_engagement' THEN 1
             	ELSE NULL
         	END) AS post_engagement,
   	count(CASE
             	WHEN action_type = 'post' THEN 1
             	ELSE NULL
         	END) AS post,
   	count(CASE
             	WHEN action_type = 'link_click' THEN 1
             	ELSE NULL
         	END) AS link_click
FROM "fb-insights_actions"
GROUP BY left(date_trunc('month', date_start::date)::text, 7)
ORDER BY left(date_trunc('month', date_start::date)::text, 7)

 

Cost

As I said, in the beginning, we had to do experiments, and that costs money. Ads will always cost money. Facebook has different bidding policies, like Costs per Action (CPA), Cost per Impression (CPM), Cost per Click (CPC).

I found an excellent post from Adespresso here.

Here are some queries for such bidding policies:

CPA Bidding

For the cost of actions on our ad then we use Costs Per Action (CPA).  With the SQL code below, we monitor CPA and have control of how much we pay per action.

Read more about CPA in Facebook’s help section here.

facebook ads: CPAAll Time: CPA

SELECT left(date_trunc('month', date_start::date)::text, 7),
   	sum(CASE
           	WHEN action_type = 'like'
                	OR action_type = 'post_reaction' THEN action_cost::float
           	ELSE 0
       	END) AS post_reactions,
   	count(CASE
             	WHEN action_type = 'page_engagement' THEN action_cost::float
             	ELSE 0
         	END) AS page_engagement,
   	count(CASE
             	WHEN action_type = 'post_engagement' THEN action_cost::float
             	ELSE 0
         	END) AS post_engagement,
   	count(CASE
             	WHEN action_type = 'post' THEN action_cost::float
             	ELSE 0
         	END) AS post,
   	count(CASE
             	WHEN action_type = 'link_click' THEN action_cost::float
             	ELSE 0
         	END) AS link_click
FROM "fb-insights_actions"
GROUP BY left(date_trunc('month', date_start::date)::text, 7)
ORDER BY left(date_trunc('month', date_start::date)::text, 7)

 

CPM & CPP Bidding

We originally paid for impressions, but it seems it was useful for brand awareness and this is where I found many click farms around the net. Cost per Thousand Impressions (CPM) is the cost per 1,000 impressions.

Read more about CPM in Facebook’s help section here.

Very close to CPM is also the Cost per Pixel (CPP) which helps follow up with people who have visited our website and allows for targeted adverts.

CPC bidding

Then we have Cost per link Click (CPC) to track the number of times someone clicked on our ad and landed on our website.  I use CPC as my objective is to drive traffic to our site.

Read more about CPC in Facebook’s help section here.

Total Amount Spent

Having all the cost data for our campaign, we also use the Total Amount Spent metric where Facebook calculates the total amount we have spent during the campaign.

Read more about Amount Spent in Facebook’s help section here.

Bellow you can see the SQL code to calculate all the above in one small dashboard in Chart.io.

facebook ads: CPM, CPP, CPC & Total SpentCPM, CPP, CPC & Total Spent

SELECT left(date_trunc('month', date_start::date)::text, 7),
   	avg(cpm)AS "CPM",
   	avg(cpp)AS "CPP",
   	sum(spend) AS "Amount Spent",
   	avg(cpc) AS "CPC"
FROM "fb-ad_insights"
GROUP BY left(date_trunc('month', date_start::date)::text, 7)
ORDER BY left(date_trunc('month', date_start::date)::text, 7)

facebook ads: This Month: CPM, CPP, CPC & Total Spent

This Month: CPM

SELECT avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN cpm
           	ELSE NULL
       	END) AS curr_cpm,
   	avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN cpm
           	ELSE NULL
       	END) AS pr_cpm
FROM "fb-ad_insights"

This Month: CPP

SELECT avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN cpp
           	ELSE NULL
       	END) AS curr_cpp,
   	avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN cpp
           	ELSE NULL
       	END) AS pr_cpp
FROM "fb-ad_insights"

This Month: CPC

SELECT avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN cpc
           	ELSE NULL
       	END) AS curr_cpc,
   	avg(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN cpc
           	ELSE NULL
       	END) AS pr_cpc
FROM "fb-ad_insights"

 This Month: Total Spent

SELECT sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()) THEN spend
           	ELSE NULL
       	END) AS curr_spend,
   	sum(CASE
           	WHEN date_trunc('month', date_start)= date_trunc('month', now()- interval '1' MONTH) THEN spend
           	ELSE NULL
       	END) AS pr_spend
FROM "fb-ad_insights"

Audience

Last but not least many of the companies I talked gather information about their audience. They measure age, gender, location, etc. Apparently, it is important if I correlate it with my first Facebook Ads campaign that I was targeting every man and woman in the world.

Age

If you want to measure age then here is a small SQL script you may use.

facebook ads: AgeAge

SELECT age,
   	count(*)
FROM "fb-ad_age_gender"
GROUP BY age

 

Gender

For gender, it is also simple like:

fb ads: genderGender

SELECT gender,
   	count(*)
FROM "fb-ad_age_gender"
GROUP BY gender

 

Location

Also, finally location. The SQL code is:

facebook ads: country locationLocation

SELECT country AS "Country",
   	count(*) AS "Recipients"
FROM "fb-ad_country"
GROUP BY country

 

Moving Forward with your Facebook Ads analytics

Eleni and I did our best not to overwhelm you with too much information. We know that there’s no one-size-fits-all, but it seems, for now, these metrics help us in our everyday work.
On the other side, it appears that our customers are excited to have all their Facebook Ads data into a database and to work with their raw data for exceptional granularity.

In one of our next blog posts, we will try to show how we combine information that comes from Facebook Ads with data from our product on Mixpanel, Intercom, and Stripe. How easily was to sync all these data into one place and with the help of a data integration tool like Blendo and how we get more data insights.

So if you are serious about your Facebook Advertising data, Sign Up for Blendo. We offer 14-days free trial, allowing you to integrate your Facebook Ads accounts and sync your data with your data warehouse saving time and money in resources from our IT budget.

 

Header image by Stocksnap.io, Canva.

Leave a Comment