Boost Your Performance With Supermetrics, Data Studio & Google Sheets

When I first started my company, about 12 years ago, everything was small and compact. A few big campaigns, a few decent clients, a handful of freelance support, affiliation sites – and everything was flowing smoothly. Then, slowly, the company started to grow. More and more clients joined, the number of managed campaigns grew fast, and most importantly – clients’ ad budgets grew into 5, 6, and even 7 figure numbers and the operational challenges were immense.

At the same time, I was developing products of all sizes, scripts for sale, short online courses, hundreds of affiliate supporting sites, and the overall mechanism became too complicated for me. I became my most demanding client. So I went “shopping” for “something” (but I didn’t know what) that would help me with that. I looked for systems that would help the team build campaigns, methods that would nudge clients who were behind on their payments, platforms that would manage the tasks at the office, take care of incoming leads, send reports to clients, and more. I had one rule: if the task is repetitive, there’s no reason for it not to be mechanized and automatic.

I’m writing this article to shed some light on one part of the vast world of automation and focus on campaigns, analytics, and the information I present my clients (myself included). I do that by using the triad of tools: Google Data Studio, Google Sheets, and of course, Supermetrics. I’ll give a short review of each tool, then jump right into implementation.

Google Sheets

Most of you are probably familiar with Google Sheets, Google’s alternative to Microsoft’s Excel. You can create data sheets, charts, and graphs, but behind it all is a strong engine that runs code (scripts) that let you run wild with your data, manipulate it and synch with other data extraction systems (we’ll get to that). This engine is based on a language called Google App Script, which is pretty much a slightly tricked-up version of JavaScript (Excel pros: it’s like Visual Basic).

Google Data Studio (DS, for short)

A while back, Google launched a free reporting tool and let you present information in convenient dashboards, from practically any source of information. You could say that it became an industry bon-ton, and has become an integral part of every ad agency and client who hadn’t previously worked with dashboards.

Supermetrics

In a world of hundreds of information sources, you need a tool that knows how to connect to them and pull out the right information at the right constellation. For years now, we’ve had tools that connect to, say, Google or Facebook’s ad systems and can not only draw out the information but also create beautiful reports and dashboards. Only a few of them that just connect and extract the data, leaving it up to you to decide where you want it to go.
Want to extract your advertising costs to Google Sheets daily or send out a text message whenever the conversion rates drop? Many tools do that, but each tool only connects to one or two systems, and you had to either use too many tools or develop them independently. And as if that wasn’t enough, some of these were outrageously priced and aimed at the enterprise level. That was all fine until the Finnish folks over at Supermetrics took over with their lean and efficient product that was significantly cheaper than their competitors’. It can take data from 40+ sources (various analytics, ad systems, etc.) and dump it all into Google Sheets, Excel, or even Data Studio. Their mechanism enables timed data extraction, data sent by email, and all sorts of other treats.

Putting It All Together

So now that I have Supermetrics, Data Studio, and Google Sheets, as well as some developers who back m up, it’s time to let them all play together. Here are a few of the things I implemented for my clients, as well as for myself. Some of these I presented before, but there are plenty more that I keep for myself.

“Bad alert” mechanism

Probably one of the most fundamental mechanisms I create. The logic is pretty simple. I gather a set of core KPI’s – they can be general, like daily sessions, transactions, conversion rate, etc., or they can be specific, such as the number of sales for a specific product on an average day, CPA for a specific campaign, etc.
Every morning, around 4 AM, Supermetrics extracts these data (for the previous day) into a specific file in Google Sheets. On a separate tab in that same file, we have the threshold values, the minimum, and maximum KPIs that any deviation from which triggers an alert. If the KPI deviates from the threshold, an automatic email is sent to a mailing list, according to the type of alert. The email includes a clear subject line (“cart conversion rate dropped below X%) in order to preserve the alert’s opening rates. The content of the email is also dynamic and includes reference to typical values, the nature of the deviation, etc.
One exception: sometimes, the mechanism works every day but considers data going five days back instead of one since for some KPI’s, the daily value is insignificant.

Campaign Optimization: Level 1

When a company runs hundreds of campaigns for dozens of cross-platform clients, you have to make sure that the campaign managers are in complete control over the campaigns and avoid any surprises. Still, all too often, campaign managers forget to perform an action or follow a certain method. So I decided to use Supermetrics to build a mechanism that, together with Google Sheets, runs all night long and gathers every account “problem” that requires immediate attention or tracking. Each morning, this sheet is sent out as a file to the campaign managers. It may include any of the following tabs:

• List of campaigns with a CPA > X (going five days back).
• List of keywords with a CPA > X.
• List of ad groups or ads the overall click-rate of which is > X, without conversions (going five days back) – mostly used to track new campaigns and ad groups and reminds us to check up on their performance.
• Ads with zero conversions in the last X days.
• Keywords with a quality score of less than 7.
• List of ad groups for which the impression share is lesser than X.
• And so on.

This file works as an insurance policy, and it keeps campaign managers in the loop, letting us feel the pulse of the campaign.

Sending Performance Reports To Clients

Up until a few years, we had a regular end-of-the-month procedure where each campaign manager would prepare a month/quarter summary presentation and send it out to their clients. These reports took quite a bit of time to create and would waste dozens of cumulative hours – not to mention that it’s manual work that often comes with typos and horrible, but human errors. We tried to work with ready-made reporting solutions, but most of them were too expensive or very limited and forcing us to work with specific queries we didn’t need. Plenty has come and gone since, and the task is no longer on the agenda. Today, not only do we use Data Studio to create amazing dashboards for our clients, but we also migrated the complex reports over to Google Sheets. Add to that the alert mechanism, and you have a comprehensive service solution and complete transparency with the client. Not only does the client get a beautifully wrapped bundle of monthly information, but they can also log into the dashboards and see what’s up at any time.
Me being me, I had to take it one step further. Our clients receive a daily Telegram push notification that includes all cross-platform campaign costs (that is, we calculate Facebook, Google, etc. altogether) compared with sales, leads, and conversions. The notification is personalized, and some clients receive it along with additional information from their CRM. And all that with just Supermetrics and Google Sheets. It’s written in Hebrew, but it starts with a good morning blessing, total cost, total transactions, total revenue, and CPA.

Budget Consumption Rate

This is one of the most popular analysis we run. We want to see if the monthly budget consumption rate matches the progress. It doesn’t have to be a rule of thumb, but if I have a monthly budget of 50K and it’s already the 20th of the month, and we didn’t even scratch 10K, there’s a problem.
We can also see our goals compared to actual performance. So, for example, we might expect to meet 50% of our goal by the middle of the month. To calculate it, we use Google Sheets together with Supermetrics, which extracts the data. The client can go into the sheet to update the goals and even add monthly goals going forward one year; the data will be automatically updated. You can read more about it here.

A Zapier/Integromat Replacement

We’ve used Zapier for many years, and a while ago, we switched over to Integromat (trickier, but with a friendlier price tag). But I noticed that Zapier fibs all too often: leads that never arrive failed automation, and the support doesn’t always have all the answers. Add a hefty price tag, and you can see why we started developing these logics on our own, as best as we could. How? Again, by writing Google Sheets scripts that extract existing information, study it and if certain conditions are met, trigger the next mechanism (send an email, enter a lead into the CRM, etc.).

Data Aggregation Cross Platform

With these tools, I can take the cost data from all platforms, putting them together ($100 from Google + $100 from Facebook) and getting the total cost for each day, then divide it by the number of conversions which usually comes from Google Analytics, and get the aggregated cross-platform CPA! Finally, a central, easy-to-calculate index that only takes 5 minutes of work with Google Sheets and Supermetrics! Yes, it’s not 100% accurate because it “force” the attribution window to 1 day, but it does give us a sense of performance.

Identifying Purchasing Trends

Imagine that you have an analyst who spends all day looking over your thousands of products catalog, reads the data from Magento or analytics, and tells you if:

• A specific product that used to sell maybe twice a day tripled in numbers, which is worth looking into;
• Some products get zero exposure;
• A specific product sells very well daily, but lately has been dropping;
• New search keywords

Uptime Alerts

Do you use all sorts of tools to check if your website is up? Me too. But I stopped, and now I run all of these tests from Google Sheets (this time with a similar script for that) and extract the data to the Data Studio dashboard. The data is refreshed every five minutes. Combined with this extension that refreshes Data Studio dashboards – you’ve got yourself a pretty sweet real-time dashboard.

Alerts On New Pages That Get Search Engine Exposure

Over here, Supermetrics runs once a week on every Search Console page and documents them into Google Sheets. Once finished, we run a procedure using Google Apps script, which compares them to last week’s list, to find new pages that Google finally put its eye on, deviations, a rise in the number of new page exposures. I have to admit that the data here is less consistent and far from being precise, and it definitely won’t replace SEMrush and co., but it’s starting to look good.

Final Note

I was going to write a quick post, but somehow it turned out to be a +2000 word article, and I have tons of other examples, but I’ll save them for next time. You can join our Data Studio & Supermetrics group on Facebook.