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.
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.