#19 Sheets, the unsung hero of GA
This week Dan and Dara discuss the relevance of the Google Sheets connector for GA, and if GA4 not having one will be a bid deal or not. They get into the use cases it fulfils for UA and how/if tools like Google Data Studio, BigQuery and the GA4 explore workspace will be able to fill the gap.
Check out the GA Google Sheets add-on over at https://bit.ly/3IdEFNJ, or find it in Google Sheets under Extension>Add-ons>Get add-ons.
In other news, Dan pots some balls and Dara gets magical!
Please leave a rating and review in the places one leaves ratings and reviews. If you want to join Dan and Dara on the podcast and talk about something in the analytics industry you have an opinion about (or just want to suggest a topic for them to chit-chat about), email podcast@measurelab.co.uk or find them on LinkedIn and drop them a message.
Transcript
[00:00:17] Dara: Hello, and thanks for joining us in The Measure Pod. A podcast for analytics enthusiasts, where each week we dive into an analytics topic, discuss an opinion or a challenge, and we try to have a little bit of fun along the way. I’m Dara, I’m MD at Measurelab. I’m joined as always by Dan, Lead Analytics Consultant also here at Measurelab. Hey Dan, what’s new in the world of analytics?
[00:00:39] Dan: Hey Dara, it’s been a slow news week this week, so how about we jump straight into the topic of the week?
[00:00:45] Dara: Sounds good to me. Let’s get down to business. So what is our topic?
[00:00:48] Dan: So this week we are going to talk all things Google Sheets, and how that is the unsung hero of google Analytics.
[00:00:56] Dara: Love this, big fun at the GA add-on for sheets. Haven’t actually had the pleasure of using it in maybe, I don’t know, I could say weeks, but it’s probably more like months. But I’ve used it a lot, and I do like it.
[00:01:08] Dan: What ends up happening is the more you get familiar, the more you use GA, especially in our jobs where we’re using it almost daily. You get to a point where you’re kind of sick of the slow sluggishness of the UI, and you know exactly what you need and how you need it, and you can just sit in a Google Sheet, which is probably where you’ll look at the data anyway, and just pull the data straight in. So it just saves that middleman really.
[00:01:29] Dara: And to give a quick intro to it for anyone who hasn’t had the pleasure of using it. Basically it’s using the reporting API, and there is a dedicated add-on for Google Sheets for this. But there are other equivalents for Excel things like Analytics Canvas, and there’s also other add-ons even within Sheets like Supermetrics, that let you pull in other the data, as well as Google Analytics data. But the good old trusty GA for sheets add-on has come in handy on many occasions.
[00:02:01] Dan: Exactly. And it’s those occasions that I think it would be good to go through. The reason why I think is a good topic of conversation for today is more around GA4, actually. And how there isn’t a Sheets connector for GA4 currently. So it’s made me really think recently around how much will be missing by not having it in GA4, and how quite integral the sheets connector was. Before Data Studio was a thing, it was where we did all of our reporting. And it’s how we shared reports or dashboards with clients, and how they share that internally. Nowadays you’ve got Data Studio which covers a lot of the gap, but there’s still a big dependency for Google Sheets. And I don’t think that’s ever going to get replaced fully with something like Data Studio.
[00:02:42] Dara: But is there, I’m going to be devil’s advocate and say, is there still the same need? Because maybe we’re just stuck on the requirement that we used to have, or the fact that it was the most available best available option to get data out of GA into some accessible platform where you could manipulate it, add other data to it, share it with people. Is it just the case that we’re clinging on to a feature that we did like, and maybe that feature isn’t needed going forward with GA4? Can we do everything between Google Data Studio, the GA4 interface, and BigQuery?
[00:03:20] Dan: I think there is, and there’s very much a big part of me that’s trying to justify this and not just because I’m used to it, and we liked it in the good old days. But I think there are some strong arguments of why it’s important and why we’ll be missing something if it’s not transitioned over to GA4 properties. Back to something you mentioned Dara around the BigQuery connector, and that’s actually a big justification nowadays of why it may not be as relevant, because GA4 properties have the BigQuery connector for free. It’s part of the free license which previously was gated behind 360 for universal analytics. The way I like to think about it is like sheets on steroids. There’s no cell limit in there, you just throw data in and you can do some cool, clever stuff with it. The thing about BigQuery though, is it’s a big skill gap. We’re using a different language to query the data, it’s a different entire platform, it’s a different interface. Not only are you getting into BigQuery, but you’re getting into the Google Cloud Platform as a whole. I feel very strongly that there’s a middle ground. There are people, and I definitely class myself as this, that are maybe more advanced or want to do more advanced analysis than what the UI will allow me to do, but I’m not a SQL analyst. I don’t know SQL well enough to be able to query and do I want it to do within a database. And I think this is where the Sheets connector comes into its own. It’s fulfilling this gap before you become proficient with something like SQL. There are a lot of people that don’t know SQL, need to do more than what the UI allows, and Sheets fills that gap.
[00:04:48] Dara: Yeah, I’m going to stop my turn as devil’s advocate, I never liked playing that role. So I’m going to say, why not both? So they’re different use cases, aren’t they. And actually it can be beneficial to have the data. Well, let me go back a step. Some people aren’t going to be making use of the BigQuery option. It just might be over kill for what they need, and people are familiar with Sheets. A lot of the data historically from GA might’ve been getting exported either through the Sheets add-on, or just manually, or through some other automated way, and might’ve ended up in spreadsheets anyway. And this is the kind of more universally accessible tool to marketers, as opposed to trying to figure out SQL and use BigQuery. So they’re not really the same purpose, are they? There has been a need and there probably will still continue to be a need for both. So, some work will be done in Sheets, and it makes it a lot quicker if you can access the data from the interface via the API. And then for a very different set of use cases, you would use BigQuery as well. So it’s not a case of, can we have one or the other. Really we want to have both don’t we.
[00:05:59] Dan: Yeah, absolutely. And the tools are developed to work together somewhat as well. In BigQuery, you can actually read Google Sheets data to store BigQuery datasets, and query it directly from the BigQuery interface using SQL. And on the other side, you can actually directly query and pull in BigQuery data into a Google Sheet via an automatic connector, somewhat similar to the GA connector, but there’s a dedicated BigQuery connector in Google Sheets. So they are tools that are designed to be worked in parallel with each other. What we find is actually quite a lot of other data that we might want to pull into BigQuery, even if that is the final destination, might go via a Google Sheet using extensions like Supermetrics or other add-ons that pull in different data sets from different data sources. Even then we will have a system that depends on both. It is definitely not one or the other, it’s a system that will need the Sheets to get data into BigQuery, and also to get BigQuery data out, sheets is almost like an obvious end point for anyone that just wants a data dump. Where do you put it? You know, I’ll put it into a Google Sheet and send that over and I can start playing with it. But going back to Universal Analytics data and using the reporting API, what you actually find you actually miss a bunch of data that the interface has, that the BigQuery export doesn’t. And that’s very much around the Google Signals data. So that’s going to be things like the demographics and interest reports, things like age, gender, and the interest data that Google Signals applies on top of the Universal Analytics data. That’s actually extended slightly within GA4, where you can actually start stitching cookies together based on locked in Google users. So it’s used as a reporting identity as well as eventually conversion and behavioral modeling that GA4 is going to be integrating. Over time, there is going to be more and more data that’s going to be inaccessible from BigQuery. And if we’re limiting ourselves by only going through BigQuery, we’re actually gonna be missing a big part of that data. The data that is actually going to be really important for building reports, doing analysis and dashboards off the back of.
[00:07:56] Dara: I think you mentioned demographics, which is a big one, but correct me if I’m wrong, but I think the Ads data that you get via the API from linking your Ads account to Google Analytics, that’s not available through the GA to BigQuery schema either.
[00:08:11] Dan: Yeah, you’re quite right. The more extensions you add on to Universal Analytics, the more you’ll be missing from the BigQuery export. And I always keep going back to GA4 now, because we think about user privacy and consent management, and what you don’t get in either the UI or BigQuery is unconsented users that haven’t opted in to analytics tracking. On paper, that’s fine. But what GFR is starting to do, or will be doing very shortly, is starting to model that data that has been opted out of, and starting to use data modeling to fill those differences. That’s actually going to be really useful for people to build their reports and dashboards. And if you can’t get access to any of the data modeling, then I think there’s a big gap there. And I don’t know how you would fill that without using the API and pulling that into Google Sheets somehow.
[00:09:00] Dara: Something else that I’ve been thinking recently is, in my opinion at least, the GA4 interface itself is more limited than the Universal Analytics interface. If anything, there’s more of a need than ever to be able to export that data into sheets. So the requirements for the sheets add-on, is potentially greater than ever. Would you think that’s fair to say Dan?
[00:09:24] Dan: I agree completely. The only thing I will say as me being devil’s advocate now is that, that’s what the explore workspace is for in the UI. GA4 has that explore workspace, and it’s meant to be a way to do that exploration, to create those reports. In a way you kind of appease us for not having a sheets connector. The thing is quite often, you just want the data out. You just need the data out to then play in something familiar. And that’s the key thing, Sheets whether we like it or not, Excel, Sheets, these tools are more familiar to the masses, to the audience, to most people in this industry. So you can, yes, you can do it in the Explore workspace in GA4. Yes, you can do it in Data Studio maybe. Yes, of course you can figure out a way of doing it through BigQuery, but it’s just not as familiar. And that’s a blocker. You’re, you’re creating a blocker, a barrier of entry for people to use this data.
[00:10:18] Dara: Yeah. And one of the, um, if I think about the use cases I’ve used over the years with the sheets add-on. Often, it’s not something that would be replaced with either bigQuery or Data Studio anyway. It’s not it’s although going back a few years, especially pre Data Studio. We were building actual dashboards in Sheets, but they were never a great solution anyway. It’s more the kind of ad hoc cases, and often it’s when you’re pulling maybe a fairly large number of rows. Maybe reasonably large number of rows, and not something you’d necessarily want to have in a dashboards. And it can often be for one-off case, it’s not always something that you would need to have on a regular occurrence like you would use a dashboard for, where it’s for like weekly or monthly reporting. Similarly with BigQuery again, some of the same reasons we’ve already mentioned. There can be a skill gap, but even if there isn’t, sometimes if it’s for something relatively quick where you just want a bunch of dimensions, some metrics and you want to pull maybe a thousand rows or something. You want to just be able to do that quickly, and there has in the past, but no quicker way than just doing that through the sheets add-on. So there are cases that would fall through the gaps where you’re not going to necessarily want to create a dashboard just for this one-off purpose. By and large, it’s going to end up in a Sheet anyway. So you might as well just do it straight from within the Sheet you’re already working in.
[00:11:39] Dan: Yeah, 100%. Is just stay within one tool. If I’m doing a piece of analysis, if I’m doing a QBR, or if I’m doing an annual review of a client’s web performance and media performance, I’ll be pulling all that data out into a Sheet and then kind of, not stitching all the data together, but kind of pulling it all out in batches from Universal Analytics, mainly around sampling. I’ll be putting it all together in a Google Sheet so that I can look at it all in one place. The UI does not enable me to do that. The API on its own, it doesn’t enable me to do that. And imagine if I was creating segments, like filtering out spam data that wasn’t filtered out at the time in the view and I needed to backdate that for five years, I can’t do that. Even if I had BigQuery set up, it won’t go that far back. So the thing about using the API, using Google Sheets, is I can apply it to historical data and I can export it. And I honestly wouldn’t know where else to put the data. If I have to pull out data in batches, where am I going to stick that it’s going to be in a Google Sheet. So why not just stay in a Google Sheet, pull it into the Google Sheet and then do my reporting, my pivots, my visualizations, whatever I need to do that presentation to do that insight. So it just feels like an obvious choice that we’re going to be missing within the GA4 world.
[00:12:52] Dara: Is it, there’s a question though. Is it going to be missing entirely? Is it just Google’s own Sheets connector that they’re discontinuing, so can you get GA4 data using something like Supermetrics?
[00:13:04] Dan: Good point, it is just the official Google API connector for Google Sheets that we’ll be missing. The API exists already, there’s a data API for GA4 and that’s even shown in the fact that it connects the Data Studio. That’s using the same API that the Sheets connector would itself use. The thing about using third party solutions is actually that’s the support may be lacking. In which case it may make your solution quite fragile, if they decide to cease trading or stop working. If it was an officially supported extension from Google, you know 99.99% of the time, it will be working and updated if changes are made to the API. But also is, I kind of expect it for free and you’ve put it behind a third party, they have quite absolutely the right to charge for their connector. There are tools ready right now that support the G4 API. But I, I really was looking for a quick, easy, license free way. It just feels like they’re pushing us, by not having it, they’re pushing us into these third parties hands, which then comes with license fees. And then rather than learning one extension, it’s then learning five extensions. Depends on what tool we’re using at the time or why our clients are using.
[00:14:12] Dara: Keyword to add is we don’t have it yet, because we don’t know that it’s not ever going to be available do we. This is maybe me being optimistic.
[00:14:21] Dan: Yeah, Google are very secretive about all this stuff of course. And we have no inside knowledge on yes or no, to that question. I suppose I was coming at this quite pessimistically because the language that Google are using now around GA4 has evolved slightly. The language they’re using is no longer, get ready and parallel track with GA4, but it’s now time to move to GA4. Even our own clients Dara, some of them we’re now using GA4 as the primary analytics tool. And then Universal Analytics is either non-existent or taking a back seat. I think that right now, we and the industry and Google are pushing us to adopt this tool more and more, but I feel like there’s that gap to fully do that. To fully replace a lot of the reports and the automated systems, the connectors that we have with Universal. If we really wanted to fully replace everything, we have a Universal, we need that connector to exist within Google Sheets. I feel like it would be a loss if we never got it, hopefully fingers crossed we will get it and it will be fine.
[00:15:23] Dara: Okay, to summarize, we don’t know yet one way or the other if it is going to be available or not. We just know that it’s not available now, the Sheets connector for GA4 data. We’re hopeful, we’re optimistic. We can live without it if we don’t get it, but we would love to see it. And it’s in addition to the other options available. So obviously we’ve got Data Studio and obviously we’ve got BigQuery. But as we’ve kind of covered, we still feel there are use cases that don’t quite fall into the Data Studio or the BigQuery categories. And for those use cases, it would still be really useful to have the ability to get the data from GA4 straight into Sheets to do some further manipulation or to add it to other data sources that exist within that sheet. Or even just because that might be the platform, the format that the client or that your colleagues are used to working with.
[00:16:38] Dan: Please, for my sake, can we have it? Please Google, if you are listening.
[00:16:44] Dara: As a Christmas present. All right, so what have you been doing Dan aside from worrying about the lack of a Google Sheets connector? What have you been doing outside of work to wind down?
[00:16:55] Dan: I was going to say worrying is a strong word, but I think it has borderline become worry.
[00:17:00] Dara: And also aside from worrying, what have you been doing to wind down?
[00:17:04] Dan: Well, um, what have I been doing? Actually in the office, we’ve got a pool table. We had space for a mini pool table and we got one, and it has been wicked. It’s not an amazing table, it’s not a professional or full-size or anything. But just to kind of have a five-minute break and pot some balls, or knock around after work with a bear has been really fun. Really fun, actually.
[00:17:23] Dara: A real physical pool table?
[00:17:26] Dan: A physical table in the physical office, and I’ve played with real people. Yeah, it’s, it’s been really, really fun. So how about you Dara, what have you been doing to get your head out of analytics this week?
[00:17:38] Dara: Well, I sadly, I’ve been forced to get my head out of analytics because I’ve actually been ill with the dreaded COVID. So I tested positive on Friday last week and I am currently in self isolation. And it was, sadly it was symptomatic. And I’ve been pretty unwell for, which explains why, if I sound different this time round that would explain why. Or if I’ve, um, made less sense than usual even, then that would explain why. So , I’m just about starting to feel semi-normal again now after kind of four days of kind of bad flu like symptoms.
[00:18:17] Dan: Well, I’m glad you’re feeling better enough even to do this Dara.
[00:18:21] Dara: Well, I should actually add in something that I did to help rest and recover. So, I’ve watched all the Harry Potter films again, which I think was medicinal. Definitely helped with the symptoms. Dobby RIP, no spoilers. Okay, that’s us for this week. As always, you can find out more about us on our website, measurelab.co.uk. You can get in touch via email on podcast@measurelab.co.uk, or just look us up on LinkedIn. And please do get in touch if you would like to come and join us on the show and discuss, an opinion or a topic of your choice. Otherwise, join us next time for more analytics, chit-chat. I’ve been Dara, joined by Dan. So it’s bye from me.
[00:18:59] Dan: And bye from me.
[00:19:00] Dara: See you next time.
Daniel Perry-Reed
Subscribe to our newsletter:
Further reading
A step-by-step guide to migrating scheduled queries to Dataform
How to set up a Dataform repository with GitHub & Google Cloud integration
