May 20, 2024
How to use Google Sheets to build a paid marketing Google Data Studio report
AdStage Team 3 minute read
Quickly build a Google Data Studio report with your paid data in Google Sheets
Google Data Studio is an easy-to-use, free reporting tool but it has a key limitation. It is missing integrations with ad platforms like Facebook Ads, LinkedIn Ads, and Twitter Ads.
One way to solve this problem without paying for a data connector is by using Google Sheets as a data source. Since both Data Studio and Google Sheets are Google products, they work together seamlessly.
In this article I'll show you how to build your first paid marketing report in Google Data Studio using a Google Sheet as your data source. Let's get started!
TLDR;
These are the three areas we'll review in order to build a Google Data Studio report with Google Sheets as your data source:
- Create a Google Sheet filled with your campaign performance data..
- Start a Google Data Studio report and use your sheet as the data source.
- Fill your Google Data Studio report with beautiful charts and tables.
Bonus : Automate your Google Sheet to keep fresh data flowing into your GDS report.
Get Started
Step 1:
Create a new Google Sheet to hold all your marketing data.
Here's a magic link
š«. Name it something easy to search for in Step 2.
Gather up all of your campaign data to include as a simple table in your Google Sheet. Make sure to put your column headers in row 1.
There are a few different ways to pull and combine all of your marketing data:
a. ā¤ļø You can copy/paste from the different network's performance tables.
b. š Export CSV data from the different network's report builders.
c. š Use an automated method like
AdStage's add-on
(more on that below).
No matter what method you pick, make sure to breakdown your data by day . That way you can use time series charts like line/bar/area charts.
Here's an example:
Step 2:
Now that your sheet is set to jet, jump into
Google Data Studio
and create a new blank report.
Step 3:
Youāll see a list of Data Sources on the right-hand side. At the bottom, click 'Create New Data Source'.
Step 4: Select the Google Sheets app. Looks like this:
Step 5:
You'll see a list of your Google Sheets. Search for your new spreadsheet and select the proper tab, what Google calls a 'worksheet'.
Click 'Connect' in the top right corner.
Pro tip: You can open up the sheet directly from the worksheet list to make sure it's the right one. Love it when they make it easy for me.
Step 6:
Double check the field type and aggregation. As long as you named your column headers properly these should be accurate.
For example, if you name your date column 'date', it will select the typical date format and won't try to sum up all of your dates.
Pro tip: Missing a calculated metric like Cost per Click? Use GDS's calculated metric builder to build new metrics by clicking 'Add a Field' in the top right hand corner.
Review the additional settings like data freshness. With the the default setting, GDS syncs new data from your sheet every 15 minutes. These last settings should be good to go, but feel free to make any changes.
When you're ready, click āAdd to Reportā in the upper right hand corner.
Step 7:
Get building! Now that your data source is connected, use GDS's charts to visualize your data in interesting ways.
Not sure where to start? I always recommend starting with a table to get a feel for how editing works. Try adding and removing metrics.
Need inspiration? You can start with a marketing template , but you may need to adjust each chart's settings to fit your sheet's data source.
Pro tip: Missing key metrics or dimensions? I forgot 'Networks' my first time around. Jump back into your sheet and add in your missing headers and data. Then edit your data source by heading to the āResourceā tab in the top menu and selecting 'Manage added data sourcesā. From there, click 'Refresh fields' to bring in your new columns.
Here's a sample Google Data Studio report I came up:
Step 8:
Once you have your report looking fresh and, as always, up and to the right, click Share in the upper right hand corner. From there you can grab a link, schedule it to hit a coworker's inbox, and more.
Automate it!
Many marketers compile their paid data by exporting CSV files from Facebook, Google, and the other networks for ad hoc requests. That process works fine, but for regular reporting cadences, it can get tedious quick.
Luckily thereās an easier way. Instead of pulling data manually, use a Google Sheet add-on to do the heavy lifting.
Google offers straight forward add-ons for Google Analytics and Google Ads , but for other networks like Facebook Ads, they don't always have out-of-the-box integrations because, let's face it, only Google plays nicely with Google.
Another option is to use an inexpensive, cross-channel add-on like AdStage for Google Sheets . The AdStage add-on regularly updates your sheet with fresh search and social data saving you from making manual updates. AdStage has a free 14-day trial, and monthly pricing starts at $29/mo.
If you're ready to set up an automated sheet, get started by copying and pasting your first formula created specially for Google Data Studio. Here's how .
Now you're set with the tools to build paid marketing reports in Google Data Studio with Google Sheets as your data source. You've got this!