So What? Universal Analytics data migration

Well, hey everyone. Happy Thursday. Happy June 1st. Uh, welcome to So What the Marketing Analytics and Insights Live Show. I'm Katie, joined by Chris and John.

How's it going, guys? Summer's there already. Instantly. Instant summer. Uh on today's episode, we are talking about universal analytics data migration.

If you have been living under a rock or have been uh willfully ignorant of the fact that Google has made a change to their web analytics platform from today, that where the show is live. You have 30 days to get your Google Analytics for instance set up because in 30 days you will not be able to collect data in universal analytics. If you need help with that, contact us or take our course at TrustInSights.ai slash GA4 force. Or you can contact us and talk with John, our chief statistician. That's it.

One standard deviation from getting it done for you. Your universal analytics data will not exist in the GA4 instance, and your universal analytics data will only be available for a few more months after July 1st. They have not set a date for that, but they're sort of hinting around like six ish months. For all we know, they're gonna cut that much shorter. So basically, if you want to do any kind of historical look back, you need to do something and get that data out of your universal analytics system into a different system, and that's what we're talking about today.

Uh, so we're gonna talk you through how to prepare for universal analytics data migration, how to extract your universal analytics and pitfalls to avoid with a universal analytics data migration. And so, John, you may be the chief statistician, but Chris is our chief technologist. So, Chris, where would you like to start? Well, here's where we should start. And this goes to what you were talking about in the newsletter yesterday, Katie.

Do you actually need the data? It's a really big question. And the reason I wanted to highlight that is because we're data hoarders. We're all data hoarders. We think we need the data, we might need the data, maybe you don't need the data today, but what if somebody asked me a question tomorrow and I already said I don't want the data?

So there's a lot of anxiety around leaving this universal analytics data behind. Forget the fact that it's not one to one with Google Analytics for, you know, that's a whole different issue. That's a whole different ball of wax. But, you know, God forbid someone asked me what happened on June 1st, 2016, and I don't have that data, what am I gonna do? Well, does anyone really care?

Probably not. It's true. And so there's there's three well, three ish four ish levels of data backup that you can do for universal analytics. And we'll talk through each of these. There's um quick snapshots, there's uh selected data through the API, and then there is full backup through a third party service of some kind.

Um, and it can either be cloud hosted or can be locally hosted, depending on the requirements. And so this is why we keep coming back to uh those user statements, right? Uh, what is the user statement behind Universal Analytics? So, Katie, for you as the CEO of the company, what would what is your your user statement around Universal Analytics or or do you not care? It's not that I don't care, but I'm having a hard time justifying the need to keep that data around.

It's not going, it's not data that I at this time would make a decision on. So present day data, month, you know, data from the past month, data from the past couple of months, I can look at that and make some decisions, but data from a couple years ago, I'm not gonna make a decision with that. I'm gonna look at it and go, huh, okay, that was interesting. But the company is so different in terms of our marketing tactics, our services, the maturity of the company, the audience of the company, like it's not the same as it was when we first launched. Plus, three years of that data was during the pandemic, and so the data is not what we would expect it to be anyway for us, and so I don't personally see the usefulness of having a backup of all that data.

What I would probably prefer is I could take some screenshots of existing dashboards and refer back to that and just say, oh, that's what the data looked like on that day. But that's about it. I don't really feel the need to keep all the data, but that's me. John, what about you since marketing over coffee's had a website since like 2020 seven where you have 16 years of universal analytics data? Yeah, you know, the data Katie nailed it with, you know, snapshots are good enough.

Like having a rough idea of, okay, here was how much traffic per year, maybe enough to break it down by month, and to know the channels, you know, that we would expect 20% to come from social and 20% to come from Apple. And that's really enough. You know, unless you have pretty much if you're an organization where you have a VP of marketing or a CMO and you have a team of at least three or four people, then you're getting to the point where you want to have that data to prove ad budgets. And you know, if you have to prove anything over three or four hundred thousand dollars, now you're talking about okay, you know, and our classic rule on that of 20% set aside for measuring. You know, if you're at 400 grand, well, now you can talk about spending 70, 80 grand building a database that you could go back and query and that would be worthwhile.

But yeah, for marketing over coffee, it's not, you know, we we have a rough idea of how much traffic we should have. And the the other thing is we're really only tactically focused, right? Like I only need to know, okay, I turned on the ads this week. Did we do better than last week? It's not like I'm not I'm not looking at seasonal podcast trends or you know, over multi-year analysis because that's just don't have the uh the bandwidth for that kind of stuff.

But I'm okay. What I'm likely gonna do is put together a couple of Looker Studio dashboards that are tables that are, you know, here's 2022, here's 2021, and break it down by month of like the top three metrics that I care about in Google Analytics, and then take screenshots of those, stick them in a deck, and if I ever need them, then they're sitting in my folder somewhere. Gotcha. Okay. So I think we can talk through the first couple of those scenarios uh pretty easily.

So the first scenario really is that whole, hey, I just want to know the basics. So you go into universal analytics, maybe you go into your audience, uh, go into your overview. And so Trust Insights, we've been around for quite some time. So I'm gonna crank this back to 2017. And here is, of course, our data for the last you know five-ish years.

From at this point, you can just hit export, punch out your CSV, and now you've got your your basic user data, right? It's stored in the format of your choice. Super easy to get at this data, right? Uh, very, very straightforward. And if that's all you need, that's that's probably good enough, right?

There's there's not a lot of of complexity to this. Same is true if you wanted to do uh this by channel, right? You can export this data by channel. Uh you'll get the same table, and then you'll get uh the individual channels. Now, this is not broken out over time.

These are just the total, so that's what's gonna come out in the in the table, tabular data. But again, very straightforward. This requires obviously zero technical skill, right? You just gotta define the charts that you care about. And I would suggest um if you've got the time and the inclination to do so, um, you can have you know someone go through each of the screen, the major screens.

So you'll you would identify which screens you want um to to have export, like the you know, maybe the all pages data, and one by one, boom, export CSV over and over and over again. This is arguably the least efficient way to do this, but it is also the lowest tech way to do this. You say arguably the least efficient, but it depends on how much data and how many properties you're working with. So for us, I would say this is good enough because what I would do is I would bring these CSV files into you know, Tableau, for example, put together my workbooks, and boom, done. It's all done.

I don't need to worry about it. I can refer to it whenever I need it. Um, so for us, I would say it probably is the most efficient because you know, we're not looking at landing pages and you know a lot of the other stuff that comes with it. Um, it's just not what we need for data. So I would say for us, it probably is the best option.

Okay. Um, I disagree, but that's okay. Well, google back to uh data hoarders. Yes, exactly. So the second way, which is kind of what John was talking about, if you're specifically interested in things like acquisition channels in the past, like if you wanted to see, like, hey, you know, here's when we ran ads, for example, it's gonna be very difficult to sew that all together from all the individual CSV exports.

It can be done, but it it's it's not gonna be pretty. Um, the this way requires some technical know-how. Um, it is you would be asking the Google Analytics API for specific information. So this is an R script that we write. This is actually a uh a data exporting facility.

And what we've done is you're allowed to choose up to 10 metrics in seven dimensions to export. So you would go through your user stories, right? As a podcast owner, I need to understand the marketing channel usage over time so that I can make decisions about uh you know what uh what uh platforms I should be continuing to run, something along those lines. From that user story, you then figure out what are the seven the seven dimensions. You can have up to seven, and there's like two hundred and ten, uh, but you have up to seven and then export up to ten different metrics, so numerical values.

So these are the ones that we see clients asking about the most. It is sessions, page views, new users, um, bounces, session duration, organic searches, users as the metrics and the the obviously the date, the source medium, the campaign, the page, the user type, the landing page, and the country as sort of being the that mix of of uh variables. And then what this script does is it goes week by week from the starting date, which we have set to 2017 through today and exports all that data into a local database. So it's uh there's a database system called SQLite that is a flat file database. You can obviously push it to Google BigQuery if you wanted to have it accessible in days to your dashboards.

You can push it to a MySQL database or the database platform you've choice. But this is sort of the second method, it's not the full data, but it's enough and it's granular enough for a lot of people's basic year-over-year decision making to be able to make decisions from it. So this is where in the newsletter yesterday, I was so I think a user story is good to start with, but the way to really focus that is to do a KPI map. Um, because that's really gonna help you, you know. For us, we're looking at the metrics that are most commonly asked for, but that's not going to be the same uh company to company.

Some companies may not care about page views or bounces, they may care about you know, specific cart fills or events or you know, whatever the thing is. So doing that KPI map is going to help focus that in, especially if you're limited to the number uh of data points that you can export through the API. Yep, exactly. Um, and I think those KPI maps will be super helpful to do. Um, and what you end up with is you end up with a database.

You end up with a database of some kind that has all the information that you've requested in all those different fields, and it's available for you to slice and dice. And so that that is sort of the net output. Now, when we ran this, this was about 104 megabytes worth of data for five years. So for trust insights. And those first two years, we didn't have a ton of traffic.

So this could get to be a very large database, uh, depending on the size of your Google Analytics installation, how many visit your website, and so on and so forth. Um, so this is uh method number two. So let me ask you, Chris. So when we were talking about method number one, and I said that those CSV exports were good enough, you said you disagreed. What is it that you feel in your role that we need from our data?

The challenge for me with the CSV exports is that many of the measures are not necessarily one-to-one on a given date. And to have those exports, you would unify them by the by the dates. Um and so extracting it all this way gives you the different dimensions, which may have different scopes and timescales, all unified into one table, so that you can you can look at okay at this uh session, these two sessions were from China on this day, which were two new users and so on and so forth. Um you won't get that level of granularity in the CSV export. So we get the very high level, which again for some companies is totally fine.

But if I wanted to look at only traffic from China or only new visitors or only visits to the home page from a specific channel, you can't do that for the CSV exports because they're not granular enough. To be fair, is that something you currently do? Um it is it is when we do our once-a-year anniversary uh posts, you know, we go back and say, like, here's what's changed over the last five years. Realistically, from a a marketing tactical perspective, we rarely look back more than a year. From a a historical anniversary, hey, you know, this is where we were five years ago.

Yes, that's when we we pull out this data. And I think for a lot of companies, those are the hard decisions that they are gonna wrestle with do we really need more than a year's worth of data? If you set up Google Analytics for, you know, a year ago, you have that year's worth of data. You don't need to worry about universal analytics at all. Um, and that's you know, companies are gonna have a hard time.

It's like cleaning, I talk about it before, it's like cleaning out your closet, you know, that blue sweater that's been sitting on the floor for the past five years that you haven't worn, you know, it's okay to get rid of it, but you know, in your brain, your brain is telling you, but I'm gonna need it tomorrow. If I get rid of it, I'm gonna need it tomorrow. And then it becomes an obsession, you start thinking about only that blue sweater, and you're convinced I can't get rid of it. I have to have it. And so bringing in an outside company who can ask you those questions and help you make those decisions might be a good, you know, way to go with this because you do end up data hoarding, you do end up taking unnecessary steps to hold on to things that you don't need.

Um, but this, I mean, one person's opinion, I love throwing things away. I hate extra stuff, I hate clutter. So for me, good, great, be gone, done. You can go full Marie Kondo. You can be like, Yes, I only keep data that brings me joy.

Everything else brings me joy. The accountants don't like that. That doesn't work for all businesses. That's uh not a best practice. It comes down to risk too, uh, risk mitigation, right?

Uh, because you could make the same general argument about things like health insurance, like nothing's wrong, nothing's been wrong for five years. I don't need this. Like, well, the risk of not having that is is very, very high, right? Now, with your at your universal analytics data, is there a substantial risk? To John's point, it depends.

If your auditors come knockingly, yeah, you might need that data. You might there you there might be a case where like you need to pull out this is how much traffic we got from this place. If you were working, for example, in a highly regulated industry, yeah, you're gonna be you might be asked, we need seven years worth of data from for traffic to your website from the United Arab Emirates. You're like, uh But I feel like this is information you would already know about your business. Like if you're just figuring out now that you are work in a regulated industry and you might be audited, you know, that's a whole different conversation if you aren't aware of this.

And so for companies that you know don't have those regulations and rules already in place, companies like ours, who you know, we're not gonna be audited by the number of people coming to our website on May 21st, three years ago on a random Saturday, like nobody cares. So why are we holding on to that data? And those are the questions that we need to be asking ourselves, but answering honestly. I recall a certain former company we worked with when it was acquired, um, we were asked to produce five years of website data for the acquisition. So if it there's a the a remote but non-zero possibility, Katie, that in three years we sell trust insights for a billion dollars, and though and part of the due diligence says, hey, we need five years of website data.

And that all goes through that scenario planning as you're trying to figure out. And so, and that's a totally valid question. And so internally, the questions we would be asking are, you know, do we think that there's a chance that we might be acquired? Do we think that there's a chance that you know what are the different scenarios that would would exist that somebody would need five years worth of data? You know, and so those are the different exercises that you should be going through with your team.

Exactly. Um I will say, if uh if you if you do want to buy trust insights for a billion dollars, just email us and just ask from Katie. Ask for me directly. Okay. So method one for backup is just manual export.

Method two for backup is selective export from the the Google Analytics API for the key variables that you want to know. Method three is to use a third-party service of some kind that can rip all the data out of the Universal Analytics and store it somewhere else. The system that we have had success with and a lot of struggle with for doing this is a open source system called Motomo. So Matomo, uh which you can find at Motomo.org, uh allows you to set up a Google Analytics import and connect it to your existing Google Analytics account and vacuum up the data. And there's two flavors.

There is the cloud-hosted flavor where you can just sign up for the cloud account, um, and then you go in, for example, this is the dashboard. You go into the your setting, go into your system, choose Google Analytics Import, and then you run face first into the configuration of uh of this very complex process to export it. That's flavor one of Motomo. Flavor two of Motomo is to set up your own server that you know that you host with their software because it's open source software. Again, set up the importer and stuff like that, and then vacuum up the data.

The the two differences that you would want to tackle if the cloud hosted version has a limitation of two years, so it can only hold on to last two years' worth of data. The self-hosted version, you could have it all. So, for example, when I go to backup my website, um, I'm going to use the cloud host, the the server hosted one because I I would like to keep all 13 years of my website's uh data. No special reason. I just like having it because I'm a data hoarder.

Admitting it is the first step. It really is. Um, now the other thing is both methods, but particularly the self-hosted method has a very high technical bar. It is um I did a setup yesterday for for giggles. Um I documented the process, and it took me, who's a reasonably technically competent person, about two hours to do it.

Um we prepared a very special uh music video of the experience. It is not two hours long. Not two hours long. It's only ninety seconds, but uh, So that is the process. Got it?

You write it all down. No, and I think that that's it's worth noting that if you want something that captures, you know, there's a lot of companies that do have 13 years worth of data and they have valid reasons to keep all of that data, so they would need to go through a process like that. Um, but being clear about what specifically they're collecting and keeping and making decisions on. Um, you know, and again, we sort of keep joking, half joking, but it's the kind of thing that we can help with. Um, because, you know, if Chris, if someone came to me and said, All right, you have five years worth of trust insights data, you need to keep all of it.

I would I'd go to you. I'd be like, okay, Chris, good. Get get some time. Because I'm not the technical person on the team. And so either version of that in Matomo is going to be a struggle for someone like me.

Yes, particularly the API setup, because you have to go into your Google Cloud Platform account. If you don't have one, you need one of those first. Um, you have to go into Google Platform account, set up an app, and then take the app, set up all auth credentials, set up uh the endpoint, the OAuth endpoints, authorize them, then take the JSON authorization file and bring that into Motomo to authorize it. And then you go through the web auth for that. So there's this multiple steps for that apply to either version.

That sounded like words. Yeah, I mean it was words. I don't know what it is. Once that's built though, can't you just use that as your analytics too, though? I mean, or is that solely an export and database?

You absolutely can. And in fact, um, there are when we switched over to Google Analytics 4 back in October of 2020. Uh we installed Motomo Analytics on the Trusted Insights website as a backup because at that time we weren't really sure how well it was gonna work. Um, and so we run Motomo in parallel uh to our website. I've had it running on my own website for about five years now.

It's it there's actually a version for it. If you run a WordPress site, uh there's a version that runs right inside your WordPress instance. It requires much less setup and does a really terrific job. So yeah, you can use that as your web analytics as well. You should be using it as your web analytics if you are in the EU, right?

If you were doing business in the EU, Matomo is one of the very self-hosted is one of the few web analytics packages that is fully GDPR compliant. As of today, June 1st of 2023, Google Analytics still is not GDPR compliant. G GA4 is still not compliant. Um, and so at any time the EU could drop the hammer on Google and say, Nope, you're not allowed to use this here. Um Matomo would give you that option.

So technical hurdles aside, what are there pros and cons to holding on to your Google Analytics data other than you know, it makes you feel comfortable? So I mean the the pros is you have the data if you do need it, uh, even if it's just for that once a year anniversary look back blog post, right? Um there if you want to do any kind of long-term trend analysis, like multi-year analysis, you would have you want to have that available. Uh, if you look just like data as a security blanket, it's yeah, it's available. And you know, five years of data for us worked out to about 104 megabytes.

So it's not exactly a huge piece of information. You could stick that on a flash drive, you know, and and and just put it away somewhere, and and it's it's there in case you need it. Um the cons are, yeah, it there's the the technical hurdles to get set up and get the data extracted is sizable. Even for a technically proficient person, it is sizable to go through all the steps. It's not something that people would have a lot of practice doing, because it's not something you have to do all of it very much.

Um there is there's no downside risk to your data. Obviously, go you can't erase data from Google Analytics. Um and if you store it in an open database format of some kind, then you know there's no risks there. But there it does consume some space, not a lot, but but some. Um and the big thing for folks is it consumes time, right?

It consumes time and resources to do the process. Um Matomo takes backs up about seven days every hour, is what it was pacing at. So it's gonna be a couple of weeks before it finishes with our five years worth of data. It's gonna take you know about a month for my website with you know 13 years of data. And so if you're if you need it and you have not gotten started, um, and say Google announces January 1, 2024, it's all going away, and you start this Christmas week, you are in a lot of trouble, right?

So this this would be one of those things where if you know you're going to need it sooner is better to get this going. Is there a risk? So we've seen this with uh past clients using universal analytics. Um is there a risk that you might accidentally export and store uh PII information, so personally identifiable information. Um and so how do you make sure you don't do that?

Well, first, if you have PII or SPI within your Google Analytics, you're in violation of the terms of service, and you can be kicked off of Google Analytics literally any moment if they discover it. Um so you definitely want to audit your Google Analytics instance to make sure you're not capturing that data in the first place. The place where that happens the most is in query parameters. So if you have an older form on your website that passes things through what's called a get post, a get versus a post um mechanism, you'll have like an email address in the URL of the site, and then that gets trapped. Um the easiest way to deal with that, once you're done with the extraction and you have in a database of some kind, you can write a short script that can look for like URLs with the at symbol.

Because there's no U, there's no website URL that reasonably should have the at symbol in it, right? And that's the that's one of the easiest tests to look for, is like look for the at symbol in a URL. If there is, you probably have PII in there somewhere. Um and then scrub that out and you know cautiously you know write some scripts to to remove that information from from your website. You can substitute it with like a numerical ID if you happen to need IDs of some kind or an MD5 hash, but um but you definitely would want to scrub that out.

And then as with anything you'd want to secure that data. Like you know you you you probably should not run this on a uh a public web host with no passwords. That would be a really bad idea. And I think that though those are some of the pitfalls that we need to think about because it's you know to your point the technical hurdles aside there's other considerations with exporting and storing this data. I can tell you horror stories of uh what a company I used to work for did with protected health information because they just you know we were all ignorant to what those regulations were in terms of proper data storage.

Um you know and so that's probably step one is get educated on data storage securely and what is considered personally identifiable information and if you are accidentally collecting any of it because it does come with big federal fines like it's not an inexpensive uh issue if you get caught you know even if even if you didn't mean it it's still the federal government doesn't care they're still gonna fine you you know at least five or six figures. Yeah. If you are are in a highly regulated industry, um, your best bet for safety is to buy one of these like little mini PCs that you can get like on Amazon for like 200 bucks. So as long as it's got you know 512 gig of storage and like 16 gig of RAM, uh like 300 bucks. Or preferably have your IT team do this, right?

You have them set this up in your data center, you run Ubuntu Linux on it, the 2204 LTS version, uh, which is long-term stu uh long-term uh safety, and then the you you install like everything that you just saw in the in the musical video, you install that on that machine. That machine is then behind your firewall in your data center, your data is on your network, doesn't leave your network, and that is the safest way to protect it. That way it's it's totally under your control. And uh if you're audited, if there's a security, you know, uh security team comes by, that's the way to handle it. That way you you you know for sure where your data lives.

It's in your data center on a little box. Yeah, don't just put it into this little box and then stick it in the top drawer of your desk. That's a bad idea. Yes, that's a bad idea. I mean even if you have like an old laptop laying around, you could do that.

You know, the if the laptops, you know, less than five years old, um again, work with your corporate IT team, get it into your data center, um, and and it's a good way to repurpose old hardware. There are versions of Matomo that you can scale down to even run on a little Raspberry Pi, you know, those little hobbyist boxes. And uh I might not go that route because the you know they're not built for enterprise grade reliability, but if you got one laying around and you know you got a small website and you want to have it under your control, that's that's not a bad way to do it. But no matter what you do, having a clear plan of of data security is a good idea. Like, you know, we have one of those server boxes uh in my office here and stuff, is it because I want it on my local network so I can work with it quickly, but it's also behind a firewall and you know it's got its own firewall uh on it as well, so that data that's stored on it can't go anywhere.

Unless somewhere breaks into your house and steals it, which is a different conversation that we should probably handle. It is, but again, uh as long as you practice good data security and you don't have short passwords and stuff like that. You know, uh all systems nowadays have encryption built into them. If you're not using it, you should. But you know, even if someone wanders off with your like your your laptop computer, at least the the computers we use, we use Macs, and these things have file vault, and you know, good luck getting data off of there.

The federal government can't. Um they've repeatedly asked Apple for backdoors, and Apple's been like, no. John, questions, thoughts, considerations. Yeah, no, I would definitely rob Chris's neighbors before I would rob him. Like I know that that's a huge mistake to invade the pen household.

So yeah, his data. Um migrating universal analytics data. First, you have to decide whether or not you're gonna need it. If you're gonna need it, there's a few different ways to handle it. I personally feel like exporting a few CSVs and sticking them into some kind of a presentation and then filing it away for a rainy day is gonna be the best bet for us.

Chris disagrees, and so he went down a more technical route where he actually exported data from the API so he could have a full database table. And then the third most technical option is to import it into a system like Matomo, um, which does take a lot of technical expertise. And if that is not you, you should definitely find a consultant who can do that for you. Exactly. I think most organizations will be fine with option two.

You name your your key variables that you need to save, you export from the API and you store that in a database somewhere. For for the kinds of analysis most people need to do on a year-over-year basis, that's probably gonna be good enough. Um but again, you have probably six-ish months, seven-ish months to make the decisions. Make the decision soon, like make the decision before July 1 about what you're gonna keep and then do the migration. Because you don't have to worry, once July 1 hits, there's no new data to add, right?

So you don't have to worry about um uh you know keeping up with it. But when July 1 hits for for trust insights, I'm gonna rerun these scripts for us. I'm gonna run them for marketing over coffee. I'm gonna run them for my personal website. We're gonna have the data um stored safely, and then uh we will we will have a a nice uh drinking party for universal analytics.

We'll send off with a champagne toast and uh and and all continue to suffer with Google Analytics for. So um to what uh Katie was saying, if this is something that you would like to have done for you, um, hit us up. We're happy to help. If you want to do it yourself, we have a course for that. Go go take it.

Um, we probably should add a an export chapter um to the course, right? Maybe maybe we'll do that in July as as our our as our farewell ceremony uh for that. But any any parting words? Get your egg together, clock's ticking. Those are gonna be my parting words too, so I think we're good.

Alright. Thanks, folks. We will see you all next time. Thanks for watching today. Be sure to subscribe to our show wherever you're watching it.

For more resources and to learn more, check out the Trust Insights Podcast at TrustInsights.ai slash TI podcast. And our weekly email newsletter at TrustInsights.ai slash newsletter. Got questions about what you saw in today's episode? Join our free Analytics for Marketers Slack group at TrustInsights.ai slash analytics for marketers. See you next time.