How to build automated marketing reports in Google Sheet and Data Studio using BigQuery data
See how to create automated reports using BigQuery data with Google Sheets and Google Data Studio.
What you can learn about BigQuery in this article:
- Build your marketing reports in Google Sheets
- Build your marketing reports in Google Data Studio
- Discover how to connect BigQuery to non-Google data visualisation tools
In today’s digital economy, marketers have access to a wealth of data. For this information to be useful, it needs to be presented in an organized and digestible manner. This is where automated reports come into play. Google Sheets and Data Studio are two free tools that can help create these reports. By using BigQuery as your data warehouse, you can create automated reports that will save time and money while providing valuable insights into your marketing efforts.
Google BigQuery + Google Sheet with GSuite Connector
There’s two way to create your marketing reports in Google Spreadsheet.
First, use a built-in data connector with BigQuery that is available only for G Suite users.
If you have a paid version of Google Drive, you will find Data Connector under the Data section. When you click on Connect to BigQeury, a data connection window will pop out asking you to select a project where your billing is activated.
And then, you’ll see the query editor for query editing. By clicking on insert results, your data will appear in Google sheets.
Whenever you see the database icon, it means that the data is from BigQuery. So now, you can have your data in Google Sheet and reuse it to create monthly reports.
There are two drawbacks to using a built-in feature. First, you must be a paid user. Otherwise, it won’t show up on your free account. And second, it doesn’t provide the scheduling option, which means you will need to input your query manually, which is inefficient.
Google BigQuery + Google Sheet with Owox free connector
Luckily, there is a second option where you can use the free add-on connector, Owox BI.
To install, simply click on Extensions > Add-ons > Get Add-Ons. There will be a window popping out when you click on get add-ons. Type Owox BI BigQuery Reports and authorise the apps.
Once you have the apps installed, click on the OWOX BI and select Add a new report. Your RHS will appear a window asking to select the BigQuery project > select a query and click create > wipe off the original query and place your own query into the Add a new query window.
Click Save & run, and you will have your data created in the report. And now, you can work with this data to create different dashboards in Google Sheet.
If you want to automatically renew this data at a specific time, you can click Enable report for the scheduling options and click save when you are done.
Imagine you want to retrieve your revenue date to the number of months, you can edit the query with the built-in function, EXTRACT, to extract months from Order Date. And it executes as follow:
Now you learn how to edit the query to retrieve the data you like. What about creating a chart for total revenue per month per continent? To do so, go to your blank sheet and have the heading selected.
Using the SUMIF formula allows you to select the sum and criteria range for your use case.
In this case, the sum range will be your total_revenue (select all).
And if the region is equal to the Australia and Oceania of your previous sheet.
Your second criteria will be if the month equals 1.
Remember to put the $ on your revenue so that the data will be shown correctly.
And there, you should see your data populate on the first column. To replicate the same formula, simply copy and paste but remember to change $C$1 to $D$1 and the same to column E.
Once you have your data ready, select all data and click Explore on the bottom right, where Google Sheet will automatically suggest a few charts that best suits your criteria. And of course, you can always find your inspiration on google.
Google BigQuery + Google Data Studio
To go ahead with your Data Studio, please ensure that your datasets and view are set up. You can write the following query and save it as view.
Then head to your Data Studio and connect your Data Studio. Assume you have a basic understanding of using Data Studio, and if you do not, here is an article about connecting data sources. So, search BigQuery in the connectors, select, and authorise it.
You will be given several options to select your project:
- your project
- shared projects
- custom query
- public datasets
If you have the project set up in Google BigQuery, you will probably go with the first option by choosing the project directly. Otherwise, inserting the query into the custom query can be a way to go.
Once you connect the data source, you can easily create any charts you like. If you want to learn more about the rule of thumb for designing the chart, you can check out this post. Overall speaking, it is relatively easy to connect Data Studio to BigQuery.
Many different data visualisation tools are connected to BigQuery data, i.e. Looker, Tableau, Power BI, etc. Here is just a quick example of the Tableau connection with BigQuery.
Assume you have your Tableau online version account. This is usually the start page.
Click on the create Workbook, and Tableau will ask you to connect with your database. If you click on Connectors, you will see the BigQuery option and authorise the access permission.
And then, you will have the dashboard to create/upload your datasets, which is more or less the same you see in Google BigQuery.
To learn more about the BigQuery course, you can visit CXL.
