At Measurefest I shared some fundamental tips to help make using Google Sheets a little faster.
I took the audience through how to use Supermetrics and other Google add-ons as a way to make reporting, forecasting and auditing easier.
For any questions about using Google Sheets feel free to ping me on Twitter, @MichAndaya, or drop me an email on michelle@typeamedia.co.uk.
We filmed the whole talk so do watch along above or read the full transcript of the video below.
The next talk should be of interest to anybody if you’re looking into streamline reporting and it’s a hands on talk from Michelle Vickermam and about using Supermetrics and Google Sheets to get the best accurate analysis. Let’s hear it for Michelle.
What I want to say today is actually more about how I can kind of show you some other tools that are using Supermetrics to bring everything together into Google Sheets and how it can be a little bit more authentic and a little bit smarter with our reporting and it’s bit of a dirty word in search but maybe forecasting as well.
So in case there is any confusion, I am gonna be talking about Google Sheets, not Excel. I may mention Excel as well and stringing numbers if any of you guys use that I don’t know why but some people do.
Supermetrics is absolutely invaluable if you’re not using it I would really really encourage you to use it.
As we’ve already heard, I think it was Matthew who mentioned that you can use separate add ons for things like Search Analytics for Sheets and Google Analytics. They all have their own separate add ons but what Supermetrics does is actually just brings all the data into one place and allows you to actually manage it from one central place. The really really positive side of that is that you can then refresh all. So come reporting day, you can just refresh all of your data pools in one go and then it’s all sorted and you can manage those in one place. You can also set up scheduling and emailings. This is where the automation comes in. You can set up your scheduling for your, say reporting day first of the month. And set up your reports, set your schedule, and you can have your client report on your desk or in your email by the time you get into the office. They do also have a template gallery, I haven’t really used it much. There’s a lot of really good templates out there, I’ll share a few after this talk. Just some templates of reports, forecasts. I also use Supermetrics for things like keyword research, any kind of backlink analysis, competitor analysis, pretty much everything you can think of.
I’m also going to talk today about Majestic. I know some of you will be absolute diehard ahrefs fans. As far as I know ahrefs actually doesn’t have an add on for Google Sheets, either on its own or through Supermetrics. So Majestic, if you’re doing any kind of background analysis in Sheets, Majestic is really really invaluable.
So let’s start at the beginning. I know that some of you guys this’ll be way way too basic for you but.
One of the first things, we talked about data a lot today. I think it was Matthew again who said, or Azeem maybe, that you can never have too much data. But it’s all well and good having loads and loads of data if you haven’t cleaned it up.
So this is something that I think often you can fall down. Particularly if you’re pulling data sources from different places make sure your cursor is in the right place. Things like that. Just make sure you’re using clean data.
So, this is actually a new feature that Sheets added quite recently, the remove duplicates. I would say it’s not as good as using some of the add ons. I use an add on called Remove Duplicates in case that’s confusing. The Sheets one I don’t find very good just because if you highlight the column and you wanna remove the duplicates it will shift delete those cells and shift everything up so all your rows will become very mismatched. Whereas some of the add ons actually just allow you to things like clear values or highlight some of the duplicates and you can kind of manage it your own way.
Strip to unique, you can use the unique formula so that if you wanna keep your raw data with all of the duplicates in a separate tab you wanna pull only the unique data into a new tab you can use the top example which will pull through either a column or a full range of data. Or you can use the bottom example which will pull through unique data from multiple data sources. So multiple data tabs using an array with the curly braces split with a semi colon. This is particularly used for if you’re doing things like working with the marketing team to merge your CRM data. So you’ve got your sales calls in one tab, Mail Chimp in another, and then a random Excel spreadsheet of email addresses. You can kind of use that to pull all the clean ones into one tab.
Trimming the whitespace. So this again is a new thing that Sheets added. I think only in the last couple of months. You used to have to use formula to do this. It’s particularly useful if you’re trying to gather loads of URL’s and you’ve pulled through and you know that there’s spaces on the ends of URL’s. This just trims all of that out really really easily.
Filtering, so slicers have recently been added in the last kind of month or so I think. Really really useful finally we’ve got them. I think actually Excel was better than Sheet’s for this. So slices you can put them, once you’ve got your clean data and you’ve got your pillar table and things like that you can add as many slices as you want just to kind of play around with your data there.
Named ranges. So this is one that I think most people actually don’t use. But if you know that you’re gonna be writing lots and lots of formula and you know you’re gonna need to reference back to your specific data sets multiple times, just name that data set. So for example something that I would do if I called into a tab GA data, just name that whole range GA data and then you know that you’re calling on the right thing. It also auto fills in the formula bar. So if you start typing GA you can just select it instead of having to manually click onto a tab, highlight the range that you need and then click back into the formula.
Another really good thing about Sheets, obviously the guys in the previous track just talked about it.
You can obviously go use Big Query. So Adam talked about this this morning. I won’t go into any real detail. I don’t tend to use it that much purely because none of the data sets I’m working with need to go that big. But obviously it’s there if you need to.
Using Data Studio, so my second caveat of the talk is that I tend to use Data Studio for most of my reporting. The reason I’m talking about Sheets to you today is because we want to try and pull most of the data into Sheets, manipulate it how we want and then set it up as a data source into Data Studio. And then you’ve got everything in one place but it’s just about being a little smarter and a little bit more efficient with how we’re using Sheets into Data Studio.
Asking for help, so this is one thing that’s very very basic but I still use all the time. I would definitely consider myself quite good at Google Sheets and I am talking to you here today so hopefully I’m better than most but I still have to use the help functions so just toggle F1 on and off. It’s particularly useful. So in this example where you’re layering multiple formula over the other and you want to make sure that your structure of each one is correct. Just toggle it on and off if you get errors.
This one is my favourite error. Tell me Sheets what’s the error? It’s a formula error. So that’s always an annoying one to get so again that’s where I would kind of toggle F1 on and off. I’m gonna share a little bit of a cheat sheet afterwards on just how to diagnose some of the common errors and how to sort of get them less visible on your Sheets as well.
Another helpful hint. If you know you’re gonna be copying and pasting formula to different cells but you know that you always need to reference one cell every single time you copy and paste that formula. You can lock it using function F4. So in this example I know that for every sum I want to do I want it to be added to B2 so I’ve just selected B2 and hit function F4 and so B2 is always locked. And you can funnel through function F4 and it will lock it either to the whole cell, just to the column, just to the row, or a whole range if that’s what you’re calling onto the formula.
So now we’ve got all the fundamentals right, we can start building up our spreadsheets and just making sure that our templates are ready so come reporting day everything can be refreshed and everything’s done at the click of a button.
I always like to start at the end. We’ve heard a lot again how we want to show data to our clients and for me I always like to start at the end and think about what’s the story. Who am I going to be showing this report to? Is it the SEO manager? Are they going to then report into the CMO? Is it the CEO who needs to see the numbers? What’s the story that we want to tell? And how are we gonna tell that story?
Once we know that we can start pulling our data. So, I always use Supermetrics as I said before because it pulls in through one place. I can then refresh it very very easily. I can also schedule the refreshers so it’s not even manual, I don’t have to think about it at all. I tend to use semrush, search console, and analytics for monthly reports. Again any of our clients we’re doing link building for then we would use Majestic as well. If we want to see a cause and effect on some of the technical and on page changes I would use screaming frog as well. Just paste it in. I know you can have that. Again you can your screaming frog crawl to go kind of scheduled in so you can just paste once it’s on.
So this is how you pull your data. I know some of you guys are familiar with this if you’re using it already. Go to your add ons. As you can see I do still use Search Analytics in Sheets and Analytics, Google Merchant Center is also a good one. Once you click on Supermetrics you’ll launch your sidebar which is the little pop out on the right. And this is where we can start getting smarter and automating things. So again, as this has recently been added or sort of changed in Data Studio so you can now make it relative. Exactly the same in Supermetrics. So always set things to last month or last 12 months if you’re doing annual reviews or quarterly reviews the last three months. Try not to use the custom date range ’cause obviously then that it locked to your previous month. So if you come to refresh the following month it’s gonna be completely out of whack.
Name your tabs. This is such a basic one but the amount of spreadsheets that I’ve tried to go in and play around with and figure out what’s going on that have been named like, “Sheet 1, Sheet 5, GA Data.” And no one knows what’s going on and everything’s all over the place. If you’re as OCD as me you can also colour coordinate all of them obviously and I will go through and do that for every single one. I also like to have an instructions tab. You never know if you’re gonna be away for the next reporting day. Or if you might have gotten a new job and you need someone to do an end of year forecast and you’re not there. Just to have a little bit of a cheat sheet guide for whoever might be picking that up after you.
Name your ranges. So again we’re cycling back to name your ranges. Try and use a consistent naming convention to what you’ve named your tabs so you actually know what you’re talking about. The amount of times that I’ve done this myself I might have three different search console tabs. One for URL data, one for keyword level data, and one for site-wide data. And my ranges are GSC one two three. That’s not very helpful so try and be consistent. You can also name specific columns so in an example I’ll show you later if I know that I only need to know the H1 length column for example, just name that column H1 length and then you know what you’re referencing at all times.
So using basic formula. I’m sure some of you guys are aware how to use basic formula so I won’t kind of linger on this too much but it’s really just about combining some of your formula so you can speed yourself up. So instead of having specific single formulas you can actually pull things through in multiple columns using multiple formula. So in this example we just want to highlight which pages are ranking on page two and which are not. Very very simple. We’re using IF and AND super super straight forward.
Similarly if we wanted to have a count of what’s ranking on page one and page two we would use the count IF and count IFS unsurprisingly is the plural of count IF and then a count A which is just the total values of the data sets. So in this instance in our report we just want to see a very very top level view what’s ranking page one, page two, and then what’s the total ranking keywords that currently have.
So now moving a little bit beyond some of the really really basic ones I’m sure you’re all familiar with Vlookup. If you aren’t you should familiarize yourself ’cause it’s great. I’ve yet to have a spreadsheet that doesn’t use it. Vlookup is basically just looking for a key identifiers that could a cell or it could be a string in a range and then it returns you a subsequent column relative to what you requested. What I discovered fairly recently is actually you can do this in an array. I know that Excel has recently bought out the Xlookup or it’s in beta form at the moment if any of you guys use Excel. What using an array formula with a Vlookup kind of does a similar thing. You can now pull through multiple columns using one Vlookup. Which is particularly useful again if you’re trying to save time. You want reporting data sheets sometimes struggles to cycle through all the formula that you’ve requested. What this does is just one Vlookup and now you’ve pulled through three columns or as many columns as you need. So in this instance I’m looking for organic data. I want to see traffic goals and revenue. I’ve also paired it with the transpose if you guys are familiar with that. What transpose does is basically it swaps over if you’re returning columns and you want to see it as rows transpose flips it and vice versa.
Now some slightly more complex formulas. So people get a little bit nervous about using things like regex replace and regex extract. I certainly don’t know regex. A lot of my formula is copied and pasted from what I can find on Stack Overflow. I’ll definitely share a cheat sheet of some of my favourite ones. Things like strip URL’s to a root domain. Or strip URL’s to a slug. Particularly useful if you’re doing things like URL mapping. Or anything like that during a migration. Import XML very very useful if again during migrations you want to make sure you gathered all of your URL’s. You can actually pull through your sitemaps directly into Sheets. And I think from memory you can have up to about 10 sitemaps all pulled through into one sheet using import XML. I think also if anyone works across pay channels you can also pull through your shopping feed as it’s a live CSV. What I’m going to talk to you more about is query. Again much like Vlookup I would use query probably more than anything in my reports and particularly forecasts.
So again I’m using transpose but what query, the sort of structure of it is. Look in the data range so in the same instance I’ve named it semrush data and I want to see the search volume and competition where the keyword matches one of my target keywords. Just to see if that’s still a target keyword and how’s that looking this month. Now we want to look at combining everything.
So we’ve got all of our fundamentals, we’ve got the kind of general overview of what our report is looking like. Now we want to take it a step further. I think it was Matthew who talked earlier about using weighted sort. I wish I knew that then but what I want to do, so my brief is I can see there’s been low engagement on some of our target URL’s.
So that’s from my top level view. Now what we wanna do is break that out and say, “Okay that’s my reporting sheet for the you know, senior “people and that’s my CMO report.” Now my next report is, “Okay the SEO manager “who I’m reporting into who might ask me the difficult questions. What am I going to say to them?
So I want to see the high value pages that’s had low engagement in the last month, showing their page title and meta description. So for that I’ll be combining IF, OR Vlookup, and query in one. It looks overwhelming but it’s very very straight forward.
Really all I’m saying is, “If this URL has had less than 500 clicks or less than a 5% click through rate then show me their page title and meta description. Otherwise say that the “SERP engagement is fine.”
What I’m trying to do with showing you this isn’t the specifics of the formula but it’s showing you how we can start automating some of our reports and layering on formula on top of each other to try and tell a different story. And this is where we wanna circle back.
What’s the story you want to tell? As I said, you have your top level numbers for your CMO, then you might have a slightly more granular detail for your SEO manager.
And what the beauty of using Supermetrics is, you can refresh that data, attach that to Data Studio, and have that all clean in one report ready to show whoever it is you need to show.
So let’s recap.
So when you start the fundamentals have you cleaned your data?
Is everything in the right place?
Have you refreshed it?
Have you set it up in a way that it’s really easy to manage?
Have you named your ranges?
Is it clear?
Is it consistent with your tabs?
Have you checked that all your formulas working?
If you’re happy with how it goes you can sit back and relax, wait for reporting day, hit refresh and then you’re done.
Any last minute questions from the floor? Anybody? Oh there’s a gentleman over there.
In your experience how hard or how easy has it been to convince a business to move to Google Sheets? And what’s the most sort of common objection that you get? How do you overcome that?
That’s actually a really good question. So, as an agency we’ve always used big Google Sheets so it’s very much for me an easy adoption but we’ve noticed a lot of the guys we’ve been hiring in will come from bigger agencies who are very very accustomed to using Excel. That has actually been , it’s been quite a sharp learning curve. And for a while we were actually running them both in tandem and then we just cut that Excel license and they had to kind of like bull it. But I think that the breadth of opportunity that comes with Google Sheets, not just obviously using with Supermetrics and some of the add ons that I talked about today, but using things like script. Obviously the ability to plug straight into Data Studio. I think once people understand the breadth of opportunity it’s actually quite an easy sell. The sort of mindset of running them both in tandem for a while was actually getting them to see the difference between them. They need to come around to the idea in their own time and they’ll see the positive sides of Google Sheets.
I don’t think Bill Gates is gonna starve.
Sorry Michelle again.
Oh, forgive me.
Yeah sorry. It was really interesting your talk. It immediately went over my head the initial examples were frightening. Not frightening but complicated. And I was just wondering, I’m very interested in pursing that line of inquiry. You’re clearly an expert of Excel before you moved over to Sheets. What kind of resources are available for like SEO and Metrics in Sheets?
So there’s loads. There’s loads of templates. Off the top of my head I’m gonna struggle. I should have written these down.
There are Sheets for marketers, that is a really really powerful tool. There’s just loads and loads of templates and a bunch of them I’ve actually ripped and sort of Frankensteined my own versions of.
Ben Collins has a website. He’s really good for some of the basics. He tends to take you to everything from kind of sums right up to Vlookups and then onto query which I’ve covered. Again some of his examples I tend to use quite a bit.
Off the top of my head I’m going to struggle to think of anymore, but yeah. If you have any kind of spreadsheets that you’re stuck with ping me on, what did I give you? Twitter, (@MichAndaya). I love a good puzzle so yeah just send that over there.
