What are Google Apps Scripts and how can you use them to automate your reporting?

Category: Data and analytics

Unless you are an avid Google follower, you may not have come across Google Scripts. I tend to keep my ear to the ground with such things but I recently stumbled across Google Apps Scripts while researching how to solve a specific problem for a customer. My surprise at finding it by accident matched my surprise at how easy it was to use and the power it gives users to integrate with any of the G-Suite products.

In a nutshell, Google App Scripts is a simple scripting language. If you have had experience with Javascript it will be familiar to you. In the past few years, Google have added loads of features into the language to enable you to easily manipulate data in most of the G Suite products. It’s an immensely strong tool and allows you to amend data in spreadsheets, all the way through to automatically creating calendar events or sending emails.

The reason I was poking around the Google toolbox in the first place, was to solve a specific problem for one of our customers. They are using Mediahawk to track all their marketing channels and are using our Google Analytics integration to get data into their reporting. However, their Marketing Manager wanted to delve even deeper into their data. He had produced several dashboards through Looker Studio and Google Sheets, but he was manually entering his Mediahawk call tracking data to pull together reports for his team and KPIs.

Using Google Scripts, we collaborated to get Mediahawk data automatically into a Google Sheet. Once we’d done this, it opened up the whole G-Suite set of products to produce graphs and Looker Studio reports. Because it uses our SOAP API  and he has the Google Script in his control, he can tweak the script however he wants. For instance, if alternative data or different functionality is needed, it’s a simple process to do this himself without needing a techie to help. For example, you could pull the location, campaign, number pool, call length and so much more.

Here’s how you set up Google Scripts with Mediahawk

To get the script set up, the marketing manager took our example script that pulls data into Google Sheets. Within 10 minutes and five easy steps, he had a Google Sheet automated to pull daily calls straight into a Google Sheet:

  1. Create a new Google Script
  2. Add Code.gs
  3. Set the configs at the top of the file
  4. Run the main method and follow the authentication to give the script permission
  5. Set up a trigger to run the main method on a schedule

This has saved our customer a lot of time each day.

“By automatically populating my Google Sheet, I save time and have streamlined my processes so I can focus on the things that matter. I can automatically generate team reports and KPI metrics using the G Suite of products with no IT team intervention.”

All of this was possible out-of-the-box using standard Google Scripts features and standard Mediahawk features.

If you haven’t stumbled across Google Apps Scripts yourself, they are worth a look into to give you back a few hours in your week. It can automate reporting for your teams and to easily produce KPIs. It also gives you the ability to pull in Mediahawk call tracking data using our APIs to produce a myriad of information you want.

See the Guides and Samples provided by Google for more information.

About the author - Murray Picton

Head of Development at Mediahawk with over 10 years’ experience leading high performing development teams. Murray is an expert in call tracking and marketing attribution, working with customers to build outstanding products that increase revenue through accurate marketing analytics.

Latest news and blogs

Driving growth in a flat economy: How to still hit your marketing objectives

Despite the recent news that Britain’s economy fell into a recession at the end of 2023, budget growth for UK marketing…

How to identify what’s generating poor-quality leads in your PPC ad campaigns

Every pay-per-click (PPC) marketer will have experience with poor-quality leads. Whether you have leads engaging about a service or product you don’t…

The dealership’s guide to creating an effective electric vehicle marketing strategy

The shift towards sustainable transportation is gaining momentum. The government’s Zero Emission Vehicle (ZEV) mandate has set an ambitious target, requiring manufacturers…