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

How marketing agencies can optimise PPC performance and deliver showstopping results

For marketing agencies that provide pay-per-click (PPC) advertising services, performance is everything. It’s how you demonstrate return on investment (ROI) to your…

How to measure the effectiveness of digital marketing campaigns without third-party cookies

Marketing agencies are fighting tooth and nail for their share of the digital advertising boom. Now, more than ever, the need for…

How to increase your leads to hit target this year

You’ve put together a well-thought-out marketing strategy and you’re executing it perfectly, but you’re still nowhere near hitting your lead target. Does…