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.

  • 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:

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:

  • Campaign Manager
  • Google Ad Manager
  • YouTube

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

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.

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.

// 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]
});

|Business Intelligence|Graduate-Northeastern|MS Information Systems|Reporting|Data Engineer|Database|Analytics|