Automate reporting from SQL to Google Sheets
As marketers, some of you will also be using SQL every day to check your sales figures directly from your database — but are you aware of the automation possibilities that can make your life easier?
I recently joined a French company that sells car parts online. As their campaign manager, I have to manage online campaigns and partnerships with multiple advertisers. One of the missions that take quite a large part of my time is to check several KPIs by coding SQL queries and extracting data from Google Big Query. Then, I update daily, weekly and monthly reports for each of our advertising channels like Google Display Network, Facebook, Affiliation…
Sounds familiar? Yes, it could take you a whole morning to refresh the queries and input data into yourreports one by one. After a couple of days at work, I told myself this doesn’t seem right. If this process of updating data never changes, why not automate it?
Many marketing automation tools can help us deal with this, but the problem is that they are just so expensive that the company might not be willing to pay.
One of the useful tools I discovered is OWOX BI, an API that includes three marketing analytics solutions: Pipeline, Attribution, and Smart Data.
I’m only using the pipeline to collect raw data from Google big query and store them in google sheets for my reports. So yes, not paying! If you are also struggling with this repetitive work from your organization, this add-on might help you save lots of time!
Where to start?
To do this, you need to use the OWOX BI BigQuery Reports Add-on to connect Google Big Query with Google sheet so that we can launch queries directly from the google sheet. It’s similar to the Google Sheets Add-on but requires knowledge of SQL syntax.
After installation, specify the database you would like to use and add a new query; here, you can already copy the ones you are using on Big query.
If you want to specify a parameter instead of any expression, value, or name in a query, you can try to code this before your query. Here’s the reference :
This process helps us extract data from SQL directly to google sheet, the source table will appear after the calculations.
Now you can visualize this data, structure a pivot table of KPIs you usually check. You can even make a macro per report you aim to produce if the reports are structured differently.
Why the pivot table?
KPIs in digital marketing are always interesting to analyze but sometimes not easy to handle when there is a large quantity of data. A pivot table helps to summarize data quickly and easily.
In our case, it helps to create reports out of thousands of rows and columns of unorganized data in an efficient way. You can add calculated fields in the value sector and filters to quickly analyze the data and therefore take decisions faster.
How to code the macro?
If you are using Microsoft Excel to do your report, you might need to code the macro in VBA. Here I am using google sheets, so it’s Javascript.
In this example, we are coding a macro to copy-paste the exact lines of data to the relevant part of the report so that I only have to click on the macro button to get my report ready. Thanks to the macro you are avoiding generating multiple pivot tables for each report. Instead, you can grab pieces of data you need and paste them into the desired report.
How to schedule automatic data updates?
To avoid running the query manually for your reports, you can set up a scheduled report. To do this, go to Add-ons –> OWOX BI BigQuery Reports –> Schedule reports.
Does it look complex? Yes, a bit. But how much time do I need for data preparation now? Less than 5 minutes! Remember that before it took me a whole morning!
About this article
This article has been written by a student on the Grenoble Ecole de Management’s Advanced Masters in Digital Strategy Management. As part of a content creation assignment, students are given the task of writing articles based on their digital interests and disseminate the articles online. Articles are marked but we make minimal changes to the content. Thanks for reading! James Barisic, Programme Director, MS DSM.
