- Sync your Facebook and Instagram ads data to Google Sheets with no code
- Sync that data every day or hour to Google Sheets
- Examples of what you can do with the data Monitor poorly performing ads in Google sheets
- We’ll use the best Addon for Google Sheets (used by 100,000s of people with 4.9⭐️ on 400+ reviews)
- Contact our support for fast help if you run into any issues
Follow along to get your Facebook ads data into Google Sheets:
In this reference we’ll look at how to setup a Facebook Ads API connection.
Used for Facebook and Instagram ads reporting
We’ll use SyncWith to get your Facebook and Instagram ads data into Google Sheets.
- SyncWith is a Google Sheets Add On
- SyncWith allows you to move data from any API into Google Sheets
- Learn more about why Marketers love SyncWith
- ⭐️⭐️⭐️⭐️⭐️ Reviewed by hundreds of Google businesses
- Check out our Google Workspace Listing and see why we’re the highest rated API add on with over 100,000 installs. Install today and get your data into Sheets in minutes.
In SyncWith if you search for
You can choose from a wide range of default reporting time frames as well as dynamic ones. Some marketers and agencies might report on the last 21 weeks of data. You may or may not want to see the current day in the reporting period.
All are possible with the date range picker. Popular options include:
Last 30 days
Last 90 days
Last 3 Months
Month to Date
You may create multiple connections with different date ranges, for instance if you’re building a dashboard you might want to know your total ad spend in the last month, 3 months and year. You can create 3 queries to pull each of these numbers.
Dimensions are important if you want to pivot your data to see how different groups of users perform, for example:
- I want to see how my ad sets perform across gender, age and country so I can prune poorly performing ones
- I want to differentiate between the performance of different ad types in my campaigns
- I want to understand which countries have the best engagement in my videos
- I want to group my daily ad spend by
Age is useful to understand which of age groups your ads resonate with, different age groups could be more applicable to certain services or products and as you advertise broadly reporting on age can help you tighten your targeting to where the ads are performing.
If you are reporting on multiple accounts, either because you’re an agency or managing multiple properties of your own then this can help ensure you identify which account the data belongs to.
Hopefully you’ve given your campaigns good naming conventions that help you identify the objectives and filter your data. If you’re reviewing campaign performance this is an obvious one to include.
If you’re running ads on both Instagram and Facebook this is a good one to choose to help you report on both seperately.
A unique ID for your ad campaign, although the campaign name will be more interesting to infer what’s happening the ID is unique so it can be a better way to cross identify the campaign in other FB Ads management tools or to export update campaigns.
Often daily reporting is too granular as you may not have enough data on a given dimension within a campaign or ad set so monthly reporting can be useful to dig into individual performance across a wider time frame. Monthly is also a good metric to look back over a longer time frame, eg the last year.
Useful if you want to analyze trends over time. If you’re choosing multiple dimensions Date is only useful if you have a sufficient amount of data per day (clicks, impressions, spend, etc.). For example if you choose country and ad set as dimensions along with date you may not have sufficient interactions on a given day on a given adset in a given country. Date is good if you want a graph showing your ad spend by day overall, or if you want to see ad spend at each stage of your funnel. If you have tonnes of volume for yourself or your clients then breaking things out by Date can help you see trend changes over time - eg is my CTR / ROAS going down over time. There are other time dimensions you could use like Week if you don’t have sufficient data daily.
Platform position helps you understand where your ads are showing up. Because it’s possible that certain placements will not perform as well as others this helps you see the data. Example placements include: Instagram Stories, Instagram Explore, Feed, Facebook Stories, Instream Video, Instant Article, Etc.
You’ve likely used your ad names to provide details about the format, copy, image or the CTA used in the ad. For example maybe you’re targeting “sign up” or “learn more”, etc. Basically something descriptive so you can see which ads are working and understand the type of ad it is.
Ad Set Name
You've likely used your ad set to define any audience, eg location, gender and age. So reporting on how an adset is performing across that audience to double check your targeting makes sense.
It’s generally recommended that you target your spend fairly narrowly, broad awareness campaigns across a region can help you understand where an ad is and isn’t performing. Often without localization ads can perform poorly. Using country as a parameter can help you identify where you might need to try new ads or restrict the serving of current ads.
Gender is useful to understanding which gender your ads resonate with.
Ads can have different objectives this helps you report on performance by objective. Example objectives include: Brand Awareness, Reach, Traffic, Engagement, Page Likes, Event Responses, App Installs, Lead Generation, Conversions, Store Traffic, etc.
This is not an exhaustive list of dimensions, there are 100s of available dimensions when reporting using the Facebook Ads API.
Now that you’ve selected the dimensions you want to report along - eg the things you’re interested in. Now you need to choose what you want to know about them. Eg you want to break things down by country, and now you need to know what you want to know about each Country. It’s important to note that some metrics are computed like CTR, CPM and Frequency are computed which means you won’t be able to roll them up directly - but you can if you compute them in a roll up. Eg if you have a data table with CTR by country you can’t just average all the CTRs to get your average CTR you need to sum all the link clicks and sum all the impressions and then calculate the average CTR.
Impressions tells you how many times your ad was seen
CPC (Cost Per Click) - tells us how much each Link Click costs. It’s calculated by taking Clicks / Spend - in case you want to calculate it on a rolled up basis in Sheets.
Purchases are a specific Facebook event specific to receiving an order or purchase. Usually you’d fire this event on an oder completion page.
Messaging Conversations Started
The number of conversations in FB owned messaging platforms (eg Facebook Messenger, Instagram Direct and WhatsApp Business). A user is counted if they clicked your ad in a messaging app or other Facebook-owned platform (such as Facebook News Feed) to send a message to your business. Only users who have not interacted with your business in the last 7 days are counted.
CPM (Cost Per Mille). M was a common abbreviation for thousand and it carried over, bu this is really telling you the cost per thousand impressions shown. It’s a somewhat useful metric do gauge the cost of showing an ad but it doesn’t speak much to the effectiveness of the ad. Ultimately Facebook and other publisher are selling impressions so this is ultimately how they are charging marketers.
A conversion occurs when your Facebook Pixel, App or offline event register a conversion, Facebook also does statistical modelling to estimate conversions in the cases that it can’t track all the conversions. Some examples of conversions could include a facebook pixel firing after a users has completed a form, signed up for your service or added an item to their cart.
The difference between Clicks and Link Clicks can be confusing to marketers. Think of Clicks as the superset - it includes all Clicks whether or no they drive traffic to you, when a users clicks Like, Comment, Share, Your Ad Image → those are all Clicks according to Facebook. If you care about Clicks to your landing experience then you want Link Clicks which includes CTA (calls to action) or links to your landing page. If you are also tracking visits in another platform the two numbers may not always line up because of ad blockers, iOS ATT and other privacy measures at the app and browser level.
This is the average number of times a person saw your ad. It’s the same as impressions / reach - in case you want to calculate it on a rolled up basis using sheets formula in a pivot table or query statement.
This is the number of new sessions that were recorded as events and attributed to your ads.
This is the total number of actions that users have with your ads, for example Like, Share, Comment.
Understand how much you’re spending across a given dimension. Eg how much of my budget in North America is going to Canada and the US or how much am I spending on video ads vs in feed placements.
Reach is similar to unique impressions, eg how many people saw your ad/brand even though they didn’t click. Showing the same ad 1 million times to 1 person will not be nearly effective as 1 million ads to 1 million people and probably best to show the ad 20 times to 50,000 of the right users. Use reach to understand how effective Facebook is at reaching your targeted demographic.
CTR (Click Through Rate) shows the percentage of impressions that receive a Link Click. It’s Link Clicks / Impressions - in case you want to calculate it on a rolled up basis using sheets formula in a pivot table or query statement.
Clicks tells you how many times the ad was clicked on. Be careful though because this is all clicks (including clicks that happen on the FB platform and don’t take a user off to your landing page - eg shares, comments, likes). If you want Clicks that result in a CTA, Visit, Install then you want to look at Link Clicks.
Filtering your requests can help ensure you’re only getting back the things you want.
- Filters act together so Filter1 AND Filter2 and so forth
- You can use filters even if you haven’t selected them as a dimension
- Filters ARE case sensitive
- Only dimensions can be filtered
- Not all dimensions are available
Of course you can use Google Sheets to filter the API request afterwards using filter views, pivot tables, query statements or filter function but if you’re building a dashboard using SyncWith you may prefer that your query simply returns the result. For example you may just want to see all the ad spend of your prospecting campaigns. If all your prospecting campaigns are prefixed with
Prospectingthen simply report on the metric Spend for all Campaigns that contain
Prospecting. Basically any time you don’t want to do the filtering in Sheets you can set up filters in your connection.
- Sort by a single dimension or metric
While you can filter data in Sheets often you want the data coming back to be just the way you prefer it.
For example maybe you want your Campaigns sorted by Spend so that the campaigns you see first are the ones you’re spending the most money on.
Some examples of how you might sort your data include:
- Sort by CTR to help prune low CTR Ads
- Sort by Frequency to understand if some campaigns are serving too many ads to the same person and wasting ad budget
- Sort by publisher platform to separate your instagram ads from your facebook ads
SyncWith supports 3 update modes:
- Replace contents of sheet
- Append rows to end of sheet
- Update or add rows as needed
The first two options are simple in nature, the last option to update or add rows as needed is more advanced
PREFERRED MODE for almost all Facebook Ads use cases
Every time you refresh or SyncWith refreshes based on your predefined schedule, all the cells will be deleted and the new data will be inserted.
- Any computational rows that you’ve added will not get deleted as long as they are outside of the range being updated. If you’ve inserted a computational row in between other data rows it will get over written
- Formatting you apply will stay in tact, eg if you specify a percentage, number, date that formatting for the column will persist
- This mode is ideal for when you don’t want to collect or store previous data. For example you just want the last 30 days of data.
NOT COMMONLY USED OR RECOMMENDED for Facebook Ads use cases
Every time you refresh or SyncWith refreshes based on your predefined schedule SyncWith will append the new rows to the last line with data
- The entire sheet will not be re-sorted based on any sort you have but the appended data will be sorted and appended according to the sort you specified.
- It will NOT prevent duplicate rows from being created
- It will NOT update existing rows
- If you want to only append new rows or properly update existing rows then you need to use Update or Add Rows as Needed
- This is not a popular updating mode for Facebook users
RARELY USED for Facebook Ads use cases
- This mode uses all of the columns dimensions columns as a key to determine if a row is unique.
- If the row is unique then it will add a new row
- If the row is not unique it will update the metrics if they’ve changed.
This update mode is not commonly used because often you just download and replace all the data you need.
It’s possible that you want to limit really large updates from Facebook - eg avoid downloading 10,000s at each refresh but you want to be checking often for new data (because the current day is interesting to you). In this case you could download all your historical data using the
Replace Contents of Sheetmode and then change the time frame to
last 2 days including todayand change the update mode to
Update or Add Rows as Neededand change the refresh period to
hourly. Now every hour it will check and update the last 2 days of data, but you’ll also have all the historical data present. This would be much better than downloading 10,000s of rows every hour when the data isn’t changing.
Manual RefreshIs the default scheduling for a connection. Data will not be updated until you actively refresh it. In order to refresh the data you must either:
- Refresh your data from in the SyncWith addon side bar via the refresh button - all connections can be manually refreshed
- Refresh via the Add-on menu, allowing you to refresh all connections setup, or all connection(s) pushing data to the current active sheet
- Refresh via the check box (located in the header above the data table)
There are 3 options for scheduled refreshes:
Automatically refresh dailywill refresh the data every day at the same time. The time is set based on when the connection was last updated. If you wish to set the refresh to happen at a specific time you would need to open the connection and save it at that time.
Automatically refresh hourlywill refresh the data every hourly
Automatically refresh every 5 minuteswill refresh the data every 5 minutes. This is not typically used for facebook data. A maximum of 100 connections can use 5 minute refresh.
Ad Set Nameand
Ad Nameare included so you can easily identify which Ad Sets and Campaigns a Ad is part of
Spendis both included and used as the default sort. This allows us to sort based on the ads we’re spending the most money on and prune accordingly. Alternatively using a pivot table in Google Sheets to pivot by Campaign Name and then Ad Name, could let you work through one campaign at a time ordered by most spend and then each ad one at a time ranked by spend.
CTRhelps us understand whether an ad is resonating with it’s audience, if it has a low CTR then we know there is something likely wrong with it
- Either are targeting needs to be changed
- Our ad needs to be changed
- If we’re reviewing ads within an ad set then we can look at which ads have a high vs low CTR, this will eliminate targeting as an issue and allow you to prune the low performing ads.
- Driving a high CTR is extremely important because it ensures that you make more money and get more reach
CPCgoes to our cost of acquisition, if we know what level we can profitably acquire users we can look at which Ads and Ad Sets are not profitable. Ad the Ad Set level the targeting could be wrong and at the Ad level the creative could be wrong. CPC is ultimately driven by the
CPMrefers to how much it costs you to display 1000 impressions. One thing that is really important to understand is this is how Facebook makes their money at the end of the day. The currency facebook has to sell is impressions, that’s what they can control for, ultimately they want to earn the highest CPM possible. So if your CPC bid is lower than someone else’s you can still have your ad take more inventory by having a much better CTR, because , which means if you have a 2X better CTR you can pay 1/2 the price and still make Facebook the same amount. This is important because you want to pay as little as possible and get as much reach. Looking at your CPM can help you understand if you’re not getting enough impressions. Even with a profitable Ad Set or Campaign, you’re CPM might need to go up (higher bids, or better CTR).
Impressionshelp you understand the volume of a campaign, adset or ad. There is no point having a profitable ad if you can’t get it scaled. If an ad is not scaling you may need to look at the targeting or bid amounts.