Automate Reports in Google Sheets Using Data from Google BigQuery

Liubov Zhovtonizhko, Creative Writer at OWOX BI

Oleg Kharchenko, Senior Digital Analyst at OWOX BI

Aug 26, 2019

We continue our series on how to automate regular marketing reports. We’ve already talked about how to automatically update reports and send them by email to Google Analytics and Google Sheets. In this article, you’ll learn how to do the same with reports built in Google Sheets using data from Google BigQuery.

Find out the real value of campaigns

Automatically import cost data to Google Analytics from all your advertising services. Compare campaign costs, CPC, and ROAS in a single report.

Start trial

bonus for readers

30 handpicked Google Data Studio dashboards for marketers

Download now

Why Google BigQuery?

Before we talk about settings, a few words about the features and benefits of Google BigQuery for those who aren’t familiar with this cloud storage service.

Advantages of Google BigQuery:

A fast cloud solution that allows you to process terabytes of data in seconds
Suitable for both small and large companies
Cost-effective and easy to scale
Doesn’t require servers, capacity reservation, and maintenance
Transparent pricing policy – pay only for the data processed, with no hidden fees
Flexibility in data processing – access tables with data in SQL, and it’s also possible to use JavaScript functions
Reliability and safety is confirmed by numerous certificates – PCI DSS, ISO 27001, SOC 2 & SOC 3 Type II – so you can safely store your customers’ credit card numbers, email addresses, and other personal information

Easy to automate data exchange

Another feature of Google BigQuery that’s worth highlighting, is its convenient integration with external services. The repository has a large number of ready-made libraries and connectors that make it easy to automate data exchange. For example, with OWOX BI, you can import data from Google Analytics, advertising sources, call tracking services, email newsletters, etc. into BigQuery:

Here are some more useful tools for working with BigQuery:

BigQuery Data Transfer Service

BigQuery recently introduced a Data Transfer Service tool that automatically delivers data from Google’s advertising services. It currently works with these services:

Google Ads
Campaign Manager
Google Ad Manager
YouTube

You can set up the integration in a couple of clicks, after which all information from these services is automatically available to you in BigQuery.

Combining data in Google BigQuery

To build reports based on complete data and then automate them, you need to combine data from different sources in BigQuery. Here’s an example of how this can be done:

First, collect data from your site in Google Analytics.
Complement it with information from other sources using Google Sheets.
Add cost data from advertising services– for example, through OWOX BI.
Import all this data into BigQuery. If you’re a Google Analytics 360 customer, you can do this using the BigQuery Export feature. If you don’t have Google Analytics 360, you can connect OWOX BI and use it to import data from Google Analytics into Google BigQuery.
Transfer information from your CRM and ERP systems to BigQuery to combine it with the data from your site.
Also in BigQuery, download more information from Google Sheets of any kind
If you use call tracking, upload call and chat data to BigQuery. OWOX BI has integrations for five call/chat services.
Do the same for email newsletters.
Finally, use the Data Transfer Service, which imports data from Google and YouTube advertising services

Connect OWOX BI and try it free to combine all your data and set up end-to-end analytics.

TRY OWOX BI FOR FREE

Working with the OWOX BI BigQuery Reports Add-on

After you’ve combined all the data in Google BigQuery, linked it by a key parameter, and built the necessary reports, you can automate the uploading of these reports to Google Sheets. To do this, use the OWOX BI BigQuery Reports Add-on. It’s similar to the Google Analytics Sheets Add-on but requires knowledge of SQL syntax. To access data in BigQuery, you need to build an SQL query, after which you’ll see the data in the desired structure in Google Sheets.

How to create a report in Google Sheets based on Google BigQuery data

First, install the BigQuery Reports Add-on in your Chrome browser. To do this, open a Google Sheets document, go to the OWOX BI BigQuery Reports tab, and select Add a new report.

If this is your first time working with this add-on, you’ll need to provide access to your Google BigQuery account.

After that, specify the project whose data you want to see in the report. Then select an SQL query from the drop-down list (if you created queries earlier) or add a new query by clicking Add new query.

You can immediately add dynamic parameters that you previously specified to the report in the SQL query. Select the dates for the report and run the query by clicking the Add & Run button.

At this point, the add-on will access your data in BigQuery and perform calculations. Then, in your table, a separate sheet will appear with the query results.

Now you can visualize this data, create pivot tables, and so on.

Automatically update reports based on BigQuery data

To avoid having to run a query manually every time you need data, you can set up a scheduled report. To do this, go to Add-ons –> OWOX BI BigQuery Reports –> Schedule reports.

Select the frequency with which the report will be updated (once per hour, day, week, or month). Then specify the time to start the SQL query. If necessary, activate an email alert to update the report. Save the settings.

Done. Now your report will automatically be updated according to the set schedule.

Email reports using Google App Script

Finally, in order not to miss important changes in your KPIs, you can configure sending of reports by email using Google App Script.

To get started, ask your developers to prepare a script with the email addresses and conditions for sending messages, either regularly or in response to critical changes to specified metrics.

You can use this code as a template:

    
// Send an email with two attachments: a file from Google Drive (as a PDF) and an HTML file.
 var file = DriveApp.getFileById('abcdefghijklmnopqrstuvwxyz');
 var blob = Utilities.newBlob('Insert any HTML content here', 'text/html', 'my_document.html');
 MailApp.sendEmail('mike@example.com', 'Attachment example', 'Two files are attached.', {
     name: 'Automatic Emailer Script',
     attachments: [file.getAs(MimeType.PDF), blob]
 });

You can read the developer guidelines in Google Help for more on how to structure this code.

Then open the report you need in the table and go to Tools –> Script Editor. A new window will open in which you need to paste your script.

Click on the clock icon to set the schedule according to which the script will be launched. Now click the + Add Trigger button in the lower right corner. Then select the event source – Time Trigger and select from the list the frequency with which to email the report. Finally, click Save.

Done! Now reports will come to your email, you won’t miss anything, and you’ll be able to make changes to your marketing activities in time.

If you still have questions, ask in the comments and we’ll answer them.