how to connect facebook ads and data studio

How to connect Facebook Ads to Google Data Studio

Connecting your Facebook Ads data to Google Data Studio allows you to customize the reporting to suit your business’ unique needs.

The more efficient and effective your reporting, the sooner you can analyze the data and discover insights to improve your marketing and grow your sales.

Unfortunately Google does not have an out of the box connector in which to pull Facebook Ads data into Google Data Studio and the only current way to achieve this is through a third party service such as Supermetrics.

Step1 : Sign up to Supermetrics for Data Studio

The first step to visualizing and analyzing your Facebook Ads data in Google Data Studio is to sign up to Supermetrics

Step 2 : Create a new Data Studio report and choose Facebook Ads as your Data Source

Next, we can open up Google Data Studio and create a new report. We will then be asked to choose our data source for the report. We can select ‘Create new Data Source’. 

We can choose the ‘Facebook Ads by Supermetrics’ connector as our data source and click ‘Select’. 

If it’s your first time connecting Facebook Ads as a Data Studio data source, you’ll need to authorize Data Studio to connect. 

You’ll likely see two ‘Authorise’ buttons, one for Data Studio and one for Facebook Ads.

On the left it says that ‘Data Studio requires authorisation to use this community connector’. Click the Authorise button, and follow the instructions.

Step 3: Allow Data Studio and Supemetrics to connect

The next step is to connect your Google account to Supermetrics. As shown below, choose the Google account you wish to connect to Supermetrics. 

You’ll then be told that ‘Supermetrics wants to access your Google Account. You can click ‘Allow’.

Step 4: Allow Data Studio to connect to Facebook Ads

Once you’ve authorized Data Studio to connect to Supermetrics, you’ll need to authorize Facebook Ads to connect .

Once you click ‘Authorise’ you’ll be taken to a Supermetrics Login for Facebook Ads screen. You’ll need to click ‘Continue with Facebook’. 

You then log into your Facebook account using your email or phone number and password.

You’ll then see a screen saying that ‘Supermetrics would like to access your Facebook ads and related stats’. Click ‘Ok’ to continue. 

Step 5: Add your Facebook Ads data source to Google Data Studio.

Finally, after all the authorizing and clicking we can add our data. However, before we add our data we need to choose some options around the Parameters of our data.

How you choose these parameters will be up to you and the type of visualization you’d like to build in Data Studio. I’d recommend checking the option to allow the parameters to be modified in the reports.

You’ll then see all the different dimensions and metrics in your Facebook Ads data source!

The final step for adding your data is to click ‘Add to report’!

small business sales report google data studio

How to Build a Sales Report for your Small Business in Google Data Studio

Small businesses are acquiring treasure troves of data.  Google Data Studio is a fantastic tool that makes it easy for small businesses to visualise this data, discover insights and make informed business decisions.

In this article I’ll show you how you can create a report to analyze the sales from your small business.  You can access the finished Google Data Studio report here, it is a demo Restaurant Sales report

Setup difficulty: Medium

Reading time: 10 minutes

Quick links: 

Who should use this Data Studio report?

This report is intended for those who want a more efficient, visually interesting way of monitoring their sales and taking a look at the data.

Instead of static reports on PDF or Powerpoint, Data Studio allows you to send live, interactive reports to co-workers. And if you still prefer static reports, Data Studio has a built in function to save the report as a PDF, as well as to schedule email updates!

And while the sales data in this report may not be in the exact same format you use, the same principles of how to connect and display your data apply.

Introduction

While Data Studio is commonly used to visualize information from connectors, such as Google Analytics and Google Search Console, there is a wide variety of information that can be displayed using the platform.

Because Data Studio is free to use, and relatively intuitive to use it is the perfect match for small business owners who may want to explore their data but don’t require (or cannot afford) complex visualization software.

In this blog post I’ll give you an example of how you can visualize sales and revenue using Google Sheets and Google Data Studio.

Looking at the Data

Google Data Studio connects with a multitude of connectors. These include products from Google’s marketing platform (Google Analytics, Google Search Console, Google Ads), YouTube Analytics, Twitter and Facebook Analytics (through third-party software) and more.

Data Studio can also connect directly to Databases, with MySQL and PostgreSQL connectors supported.

However, one of the most versatile ways of adding data to Google Data Studio is using Google Sheets.

Google Sheets allows you to add any type of data to Data Studio. 

n this blog post I’ll go through a step by step process showing how you can add Sales Data to Data Studio.

I’ll use data from a hypothetical restaurant to give an example of how you can turn data into attractive, easily updated and easily shareable reports. 

Here’s a link to the Google Sheet with our demo sales data.

Formatting our Data Sources

Let’s imagine that our restaurant business lists all its sales very simply.

Our Data Source: A Basic Sale Sheet

DateSales CodeNumber SoldTakeaway / Sit Down
1 January 2019S_B_0111takeaway
1 January 2019S_B_021sit down
1 January 2019S_B_0312takeaway
1 January 2019S_P_010takeaway
1 January 2019S_P_026sit down

They list the date, the Sales Code and the Number of Items sold. They also list whether or not the order was Takeaway or SitDown.

However, there are more details we’d like to find out about each of the items sold.

To  add these details, we’ll need to create a second sheet in Google Sheets.

Sales CodeItemTypeMeat, Vegetarian, VeganPrice
S_B_01BurgerBeef BurgerMeat10
S_B_02BurgerChicken BurgerMeat10
S_B_03BurgerVegan BurgerVegan10
S_P_01PizzaMargarita PizzaVegetarian12
S_P_02PizzaPepperoni PizzaMeat14
S_P_03PizzaTomato and Basil PizzaVegetarian14
S_S_01SaladGrilled Chicken SaladMeat10
S_S_02SaladCouscous saladVegan9
S_S_03SaladGreek SaladVegetarian9
S_D_01DessertChocolate CakeVegan6
S_D_02DessertCheese CakeVegetarian5
S_D_03DessertIce CreamVegetarian4

So we have our Sales sheet, with just the basic information. And we have our list of items with more detailed information.

We could combine these two data sets in Google Data Studio, using the Blend Data function. But I prefer the method of combining the data in Google Sheets.

To do this we will use some Google Sheets formulas. If you have ever used Microsoft Excel before then some of these formulas will be familiar to you.

The function we use is VLOOKUP.

Without getting into too much detail, VLOOKUP (also known as Vertical Lookup) allows you to search another table for details that match a cell in your table.

A standard VLOOKUP formula goes like this;

=VLOOKUP(The item you’re searching for, the range of cells, the column number, whether you’re looking for an exact match or not)

We can use the VLOOKUP function as part of a formula, as shown below.


=VLOOKUP($B2,’Menu Items’!$A$1:$F$13,2,FALSE)

This formula lets us bring in information from the Menu sheet to the Sales sheet. It means that just by having the Sales Code, we can add in lots of extra information.

For example, we can add in what type of food it was, whether it was vegetarian or vegan, how much revenue it brought in, and add in a link to an image of the food. This will come in handy when creating the Data Studio report.

Adding our data to Google Data Studio

After we’ve built our data sheet, we can now move onto the fun part of creating our Data Studio report!

Since we used the VLOOKUP formula to bring across data to our Sales sheet, we only need to connect the Sales sheet to Data Studio.

We can Choose Google Sheets as our Connector.

Then choose the Sales Sheet from the Spreadsheet.

We’re then able to see the fields.

Because colourful photos make everything better, we will create a Photo field.

We can click on ‘Add a Field’ in the top right corner of the screen.

And then we can use the IMAGE function to create an image field.

We just need to choose our existing Image URL field in the formula. It’s also possible to add in Alternative Text info, by adding in the Item field after a comma.

Now that we’ve created our Image field we are good to go!

Building our tables and charts in Data Studio

Let’s go through a breakdown of some of the main charts on the page.

One thing we can see on the report is an image that changes depending on which item on the menu you select. As shown below if we click on an item its picture appears on the right.

To create the list of items table, we can create a table with the Dimension being “Type” (as in the type of food ordered) and the Metrics being the Number of items sold (Number sold) and Revenue. It is also important with this table to make sure we have the “Apply Filter” checkbox ticked under “Interactions”. This will allow us to filter the image table on the right.

We now need to create the Image table, which will show a single image of the food item selected. We can do this by creating a table with just “Photo” as our Dimension and no metrics at all. We also need to resize the table so that it displays just one image at a time.

The other charts in this report are fairly simple to set up, and we can go through a brief summary of each to see how it’s made.

For the Pie Chart showing a breakdown of Revenue of each type of food sold, we can setup the Dimension as Type and the Metric as Revenue. We can also enable filtering (by selecting the checkbox labeled “Apply Filter” under Interactions.


In terms of the style of the Pie Chart we can use one colour to keep it in the style of the rest of the report.

The time series line graph showing the number of items sold compared to the previous month is fairly simple to set up. Simply create a time series chart with the Time Dimension being Date and the Metric being Number Sold. 

In order to see a comparison to the previous period simply select click on Comparison rate range and choose Previous period. 

Next on the report we have three pie charts showing a breakdown of orders by various Dimensions. You’ll notice that I’ve decided to colour these pie charts with specific colours.

In order to choose which colours you want to appear in these charts open up the Style tab of the chart. Then click on “Manage dimension value colours”. You then have the open of changing the values to specific colours. 

For example, we can decide what colour “Pizza” should be. Our pie chart displaying what percentage of orders were “Pizza” then displays this colour.

The final chart on our report is a summary of revenue by month. 

To get this time series to display the revenue by Month (and not by the default Date) click on the Edit Option for the Date dimension. Then click “Show as” to display it as Month.

Summary

And that is how you can build a Sales dashboard for your small business! It’s important to note that the data you have from your CRM or from your point of sale might be in a different format than the examples given in this post.

However, if you’re able to spend some time putting your data into a format that Data Studio can understand, you’ll be able to create effective reports and dashboards to help understand your business’ performance.