Automating Reports is need for Present & Future —Step by Step by Google BigQuery & Google Sheets

How to set up automatic updating and sending email reports built in Google Sheets based on data from Google BigQuery

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:

  1. First, collect data from your site in Google Analytics.
  2. Complement it with information from other sources using Google Sheets.
  3. Add cost data from advertising services — for example, through OWOX BI.
  4. 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.
  5. Transfer information from your CRM and ERP systems to BigQuery to combine it with the data from your site.
  6. Also in BigQuery, download more information from Google Sheets of any kind
  7. If you use call tracking, upload call and chat data to BigQuery. OWOX BI has integrations for five call/chat services.
  8. Do the same for email newsletters.
  9. Finally, use the Data Transfer Service, which imports data from Google and YouTube advertising services

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.

Image courtesy of the author

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.

Image courtesy of the author

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.

Image courtesy of the author

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.

Image courtesy of the author

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('', '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 your Product gives great value, you will live immortal !

Love podcasts or audiobooks? Learn on the go with our new app.

Making a Competition-Spec Counter-Strike: Source Bunnyhop Server for Debian Linux


Why “FREE” Isn’t Your Answer to Help Authoring Tools

Woz and the Founding of Apple

Weeks 14, 15, 16 && Demo Day: School of Code changed my life!

Cloud Technology is New Normal

cs371 blog

Google foobar: Google’s secret hiring process

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Akash M Dubey

Akash M Dubey

If your Product gives great value, you will live immortal !

More from Medium

Data Analytics for Better Decision Making in the Workplace

Why is a Data Warehouse the First Step Toward Business Intelligence?

Dehumanized Data Points

Google Data Analytics — Summary Report