Big Query
===
[00:00:00] Marker
---
[00:00:00] Olga: Hi, everyone. It's SEO Cashflow. It's Olga Zarr and Miriam just here. Miriam, how are you doing?
[00:00:09] Myriam: I'm happy because I know what we're talking about today, Olga.
[00:00:13] Olga: I'm excited. Yes, you do. You do. And I will be this person, this newbie who will be asking you all kinds of maybe stupid questions. So what are we talking about today?
[00:00:26] Myriam: So, first of all, I really like the word stupid because it's a bit ableist, but on top of that, it, this is the problem with BigQuery. Like, everybody thinks that we're supposed to magically know all of this. Why? Why are we supposed to know all of this? This is analytics type stuff, right? So when did SEOs get really, really good at analytics?
[00:00:53] Myriam: Like, why are we supposed to know all of this? We're not, it's a brand new skill in the sense that it requires thinking in different ways and knowing different bits of code. So, this is why I got started with, um, Big query and writing an article for a search engine journal. I was like, Hey, let me be your big query pal.
[00:01:20] Myriam: Let me explain this to other people because it's very opaque and we're all feeling like, I have to know this. I have to take the time. And then you look at it and you're like, what is it concretely? What is it? It's a drop box for data. Mm hmm. Yeah. So do you use Google Analytics 4,
[00:01:44] Olga: Olga? Yes. I, I'm kind of forced, but
[00:01:49] Myriam: I do.
[00:01:50] Myriam: This is why I'm talking about BigQuery. This is exactly why, because nobody likes Google Analytics 4. So I'm here thinking to myself, I don't like it and I have to teach people how to use it. Nobody likes it. On top of that, the data expires much faster because of new regulations, right? So if you don't like Google Analytics 4, this is not a situation where, you know, how Twitter kind of is dying actively and we have nowhere else to go, even though there's plenty of alternatives.
[00:02:25] Myriam: This is the same thing with Google Analytics 4. We could all be going to different platforms, but here we are still with Google Analytics 4. There is a light at the end of the tunnel. Big query. But it's not necessarily for everyone. And it's not necessarily for everything. And it's not even necessarily something every single SEO should know.
[00:02:46] Olga: Okay, so, so my question is, let's start with this data retention in ga. How does it differ in GA four, to what we had, uh, before in GA three in universal analytics?
[00:02:59] Myriam: So, the first thing, and, and we're gonna keep this beginner friendly. Like, I'm not going to go into details, that's not the problem. Yeah, sure.
[00:03:06] Myriam: I, I really want people to evaluate first and foremost. Is this potentially for me? Is this potentially for my company? And is this potentially, like, something that's worth our time period? So, the problem with Google Analytics 4 is that if you want to do, let's say, seasonal analysis, like trend analysis, Hey, how did we do this Black Friday compared to the previous one?
[00:03:31] Myriam: Compared to the previous one and compared to the previous one, you, you can't do that anymore in GFR, right? Data expires. So that's why I said it's kind of a drop box for your data because it's one of those things where you can Legally export your data from GA4 into BigQuery and keep it there. So you have to pay, but it's not very expensive.
[00:03:58] Myriam: It's kind of like either a Dropbox or a Google Drive for your data. So if you want to do, for example, seasonal analysis, it's nice to have this, but there's something else too. So if you use Google Search Console, And you're like, I'm so annoyed. I have to click 50 times to get this information. And then I have to do this for 500 pages.
[00:04:25] Myriam: And I saw you smile. Yes, that's the solution. So you can go about it two ways. You can either use the API, which means that you have to have a developer help you, or you can go the BigQuery route and connect it and start making your queries to get the information you want. Bypassing some issues. So, I know that you and I are annoyed at one thing in Google Search Console.
[00:04:49] Myriam: Like, the first thing everybody hates. The limit to a thousand keywords. Yeah. Yeah. No. Or
[00:04:57] Olga: a thousand URLs, right?
[00:04:59] Myriam: Yes. So, if you have these limits and you're like, Okay, I need to overcome this because my website is huge. BigQuery is a solution. BigQuery is also a solution if you have, you know, like, not all of us have this, but some of us do.
[00:05:18] Myriam: Imagine you're working for an international brand and they have different websites. Or imagine you're working, let's say, for L'Oréal. Parce bien. Sorry, French brand, had to. Run with the slogan, but imagine them. They own many different brands, right? And these brands, they kind of cannibalize each other. So some of them will cannibalize each other because two brands are fighting each other even though they're owned by the same house or They it's the same brand that's cannibalizing each other like according to markets So the u.
[00:05:50] Myriam: s market is cannibalizing the canadian market for example, but you can't really know That easily, right? Because this lives into two different Google search console properties. Well, when I say properties, you would just have to imagine that they're like two different houses. Okay. You bring these houses into one big place where you can compare stuff.
[00:06:12] Myriam: BigQuery is amazing for that, but there's a third case as well. So, um, and I actually, there's more than three cases, but imagine if you're looking for, um, content decay. Imagine if you're looking for content cannibalization within your site, normally you would have to go look at like which URLs have dropped and then click individually and see what happens.
[00:06:39] Myriam: No, you can't do that at scale with BigQuery, or, you know, finding cannibalization, you get told, okay, I have to go into Google Search Console. I have to look at keywords and then I have to like figure out if this keyword is a problem between two or three pages. And you have to do like so much clicking to get there.
[00:07:00] Myriam: No, not in BigQuery.
[00:07:03] Olga: Okay, so how do I get started? Do I need a lot of technical knowledge to find my way? in BigQuery to get it connected to GSC to at least start collecting data? How do I do it? How do I do all that?
[00:07:19] Myriam: That's, that's the problem with BigQuery. So if you're a beginner and you're wondering, should I do this?
[00:07:25] Myriam: You need to understand a few things when you get into the console. You're like, what do I do? I don't know if you've connected it and you've looked at it going what's going on So let me be concrete here because everybody talks about it. Like it's easy. It's not it's kind of like Google Analytics for it's not easy to figure out your brain has to think about it.
[00:07:49] Myriam: So the first thing you have to know to be successful at this, and by the way, it's really easy for me to talk about because I'm writing an article about this right now. So if anybody has questions, now's the time.
[00:08:01] Olga: Okay. So
[00:08:04] Myriam: the reality is that you have to understand three big elements. Number one, how is the thing structured?
[00:08:12] Myriam: So the way it works is your, your data will live in tables. And these tables have schema. So taxonomy, how they're organized. And then you'll have fields. So fields are like. individual columns in your Excel spreadsheet, for example, okay? So, you have to understand what kind of data you are importing into BigQuery, how it's organized, because if you don't, how are you going to create queries that say, I want you to get this data this way, from this place?
[00:08:44] Myriam: You always have to know what you're asking. And where you're getting it from. So you have to know, you have to have a map. You have to know like, okay, how is my data housed? But also what kind of data do I have? And once you have this, once you understand this, then you go, Hey, I kind of want to know this, and I kind of want to know that, and I kind of want to know this, so this is the bit where everybody.
[00:09:13] Myriam: It's going to be easy. No, you have to know all three. You have to know how the data is living in there. Like what does it look like? What you need to ask. And then you need to know how to ask it.
[00:09:30] Olga: So how do I ask SQL?
[00:09:33] Myriam: Yes. Yes. So this is one of the issues. If you know, for example, Python, because you're used to doing data analysis.
[00:09:42] Myriam: Some people say, hey, Python is actually much. Easier, you just have to connect the API and do this. And I'm like, that's great, honey. Not everybody knows Python. Okay. Like we're not all pros at this. And maybe some of us prefer BigQuery and SQL, and here's why you can get reports that are one offs, you can get people, um, you can get reports that are automated, recur every so often.
[00:10:15] Myriam: So you get to automate a lot of stuff. Okay. And then it plugs in super nicely into Google Looker Studio. So of course we like this now for SQL. The thing about SQL is that it's genuinely not hard, and I say this as someone who's really not that great at SQL, okay? However, and I'm here to talk about the caveats, because once you learn, there's different ways to do things.
[00:10:47] Myriam: You know how in SEO there's some of us that can do some stuff super fast? And for some others, it takes forever. Exactly. Those queries, it's the same. The way you build them, it can cost you a lot of money because it takes a long time to process and get you that, a lot of power. Or you can have a streamlined query that will keep costs down and get the information.
[00:11:13] Myriam: In a really efficient manner. So when I say that SQL isn't hard, it's not, you can use chat GPT to help you learn and help you create specific queries. Are they going to be perfect? Of course not. You, you really do need to learn this little bit by little bit, but you can. Now, the key when it comes to BigQuery is figuring out the stuff that can be done.
[00:11:37] Myriam: Well, and for cheap, and that's useful, and oh, that's the problem with Google Analytics 4, isn't it? We don't really have cool reports. We have to create these explorations, and we have to think about it. That thinking doesn't go away. However, You have SQL cookbooks, you have people that will share queries that they use.
[00:12:01] Myriam: So you can use this, just like, hey, little promo moment. Didn't you create a Looker Studio template? I
[00:12:09] Olga: did.
[00:12:11] Myriam: Yeah, and it's helping people, right?
[00:12:15] Olga: Yeah, helping people understand GA4 better.
[00:12:20] Myriam: Exactly, so BigQuery is the same. You have to figure out the queries that you want, the SQL queries that you want. But there's already people that are sharing some of them.
[00:12:31] Myriam: And what's cool about this is, you know how in GF4 you have to figure stuff out, and then in Google Ads, if you go into the interface, you have to figure stuff out, and then you go into your CRM, and you have to figure stuff out, and you go into GSC, and you have to figure stuff out, you can simplify things, because once you know how the data is organized in BigQuery, you can make your queries, And you're good, but you get to mix it, you get to do mashups, so you get to mashup, um, GSC data with GA4 data or CRM data with GSC data, etc, etc.
[00:13:09] Myriam: Like, you can actually join all of these things, there's caveats, there's You have to figure out, okay, do we have things that can pivot that, like, it has to make sense. You can't compare tomatoes to potatoes, but if you have something that makes sense and you can join the two, you can do mashups, you can do really cool stuff.
[00:13:31] Myriam: The problem with that, once again, is, but what do I do? How do I consider this? That's where SEOs are amazing. That's where you don't necessarily need to be good at SQL. You don't need to be necessarily good at big query, the mindset, like figuring out what type of stuff you want to look at. That's where we're amazing.
[00:13:54] Myriam: And BigQuery opens all these doors. Every time an SEO goes, I wish we didn't have that thousand word limit. I wish in GA4 I could blah blah blah. I wish I could have the CRM data tied to this so I actually know you can do it in BigQuery. You can.
[00:14:14] Olga: Okay, but if someone like doesn't know anything about BigQuery SQL, how do we get started?
[00:14:19] Olga: Do we get started with your search engine article?
[00:14:25] Myriam: I'm going to be honest, um, you need to first read the article because it's meant to help you figure out do I have time for this? Can I get buy in from my boss to do this? Is there somebody in the team that actually knows SQL? And can help me explore the data like you have to map all of this out.
[00:14:46] Myriam: So the article is an amazing way to get started, but it mainly talks about Google Search Console data. It doesn't talk about any other data. So if you're an SEO, you should start with Google Search Console data because that's your jam. You already know. You know how I said like. Tables and like schema and fields, and if that scares you, let me make it simple for you.
[00:15:12] Myriam: The tables that you will see in Google Search Console, in BigQuery, they're the same as the reports that you see in Google Search Console. You know them by heart. You know them. So it's easy for you to get started. So the first thing I would say is, Get started with this article, read this, figure out, can I afford this?
[00:15:34] Myriam: Can I get the buy in? Can I invest my time? The second thing is, figure out what you want to use it for, huh? And then, and then, only then do you go, okay, how do I build my queries? How do I get started? And oh, oh, I, I feel so touched that you're showing this because you know, this is my first search engine article, search engine journal article.
[00:15:59] Myriam: Yeah, I feel really so okay.
[00:16:01] Olga: Okay. It is a very good one. Yeah, a very good article I've
[00:16:05] Myriam: been talking a lot. So let me preface this and then I pass it off to you Okay, so the way I wrote this article was not meant to make you a pro It was meant to answer the questions that all seos ask themselves like is it worth my time?
[00:16:21] Myriam: How does it work? What do I get? So now I want to know what did you learn from it? And What do you recommend to others? What makes you consider yes or no for BigQuery?
[00:16:35] Olga: Huh, that's a good question. I was this kind of, uh, SEO, I, I had, I learned some SQL back in the day.
[00:16:43] Olga: I knew something, but I wasn't actively using that because I didn't really have to, but when GA4 kind of, we were forced to use it, I started to hear more, more, more and more about BigQuery. Then I landed on your article and I, the thing I did is I put it on my to do list for December to start doing that for at least most of my clients and my websites.
[00:17:13] Olga: Because I know this is something I have to be doing. But since I was working with not that huge projects, at least as I am an SEO consultant, I don't work with such huge websites as I used to work when I was doing the agency work. So I didn't really need that. And I kind of, I was able to bypass that, limit with other tools,
[00:17:34] Myriam: That's the thing.
[00:17:37] Myriam: Not everyone needs BigQuery. But the thing is But I feel
[00:17:41] Olga: that I, I want, uh, this is the ambition, ambitious part that kind of I fear, I have the fear of missing out.
[00:17:51] Myriam: Oh, okay. No, no, no, no. Let's transform this. Okay. If you absolutely hate GA4 and if you want to overcome some limits in Google search console, and if you're the type of person go, Oh, I wish I could actually like mix and match different.
[00:18:05] Myriam: Like data that I have stuck in my CRM, stuck in Google search console, stuck somewhere else. Then BigQuery is worth looking into, okay? It's not about FOMO. Let's address that FOMO. It's SEOs want to learn all the things. But here, it's literally Okay, Olga, do you know how to code? Do you know how to build a website?
[00:18:28] Myriam: Yes. Okay. Do you think everybody that does SEO should know what you know? No, I
[00:18:36] Olga: don't think so.
[00:18:37] Myriam: Exactly. But do you think all of us have had this moment either in the shower or at 2am going, okay, I have to learn how to code and become a developer because otherwise I'm never going to be good enough at SEO.
[00:18:47] Olga: Yeah. Yeah. I had it many times. I
[00:18:54] Myriam: know. Well, now this moment is happening with BigQuery and the problem with this is BigQuery is a skill, well SQL and like learning how that platform works. It's a skill in and of itself. And I'm going to be honest here. Some people that are really good at BigQuery, they don't get paid much.
[00:19:17] Myriam: They don't. So if you're going to learn that skill, you should know how you can make money off of it. When does it make sense? What type of clients do you want? Because, for example, very big e commerce, very big news websites, okay, they benefit from this. International brands that have different domains, oh yeah.
[00:19:38] Myriam: Different markets, like different regions, yeah. But Not everyone needs BigQuery. And if you think you're going to be paid more because you can do this type of analysis, it's not always true. Yeah. So we need to figure out exactly like. What is worth knowing and what are people willing to pay for and what kind of client is interested because I don't know about you, but convincing clients to pay to just keep their data in big query.
[00:20:10] Myriam: I see you smiling because and what people don't understand is that there's two costs. There's storage, like I want my data to live somewhere, like the Dropbox, Google Drive analogy, and then there's the compute pricing. So if you want to process, if you want to ask things of the data, okay, so within these costs, it can quickly add up, like it can quick, like the more things you ask.
[00:20:41] Myriam: And if you ask it in a way that requires a lot of power, instead of being elegant and like figuring out the cheapest way to do it. Hmm, that's going to quickly rack up, but the reality is usually like for 2 a month. You're good. So okay See see the relief in your face. The problem is that it really depends.
[00:21:05] Myriam: Um, Oh my gosh, i'm just realizing that big query is such an seo thing at the end of the day. Yeah, uh, but but The truth is, what I would do, if I were considering this, I would try to understand how the thing works, and I would not try to be a professional, I would try to copy pasta, some queries that other professionals have found, and try to learn, and figure out, okay.
[00:21:29] Myriam: What can I do quickly for my clients? Do I offer this as like the little extra you want to go further? You want to analyze more? I can
[00:21:36] Olga: sell this to you. Okay, and what's exactly the topic of your next article about that?
[00:21:44] Myriam: Actually, I have two articles coming. Oh, I made you a memo. Okay, so the first one is Now that we spoke about, okay, Google search console, um, data, that's really cool.
[00:21:56] Myriam: How, how can you plug it in? Is it worth your time? What does it do for real? Well, the next step is get started with the freaking thing. Like when you open the console, you've done it all. You've plugged it, you've put in your credit card, and then you look at the console and you're like, uh, and, and you know how there's this.
[00:22:18] Myriam: There's this, uh, description, like, um, analysis paralysis, we're not even there.
[00:22:23] Olga: Yeah, I know that, I know that very well.
[00:22:27] Myriam: Yeah, but here it's even worse, you just look at it and it's like the blank page right before the paralysis. So, I want to help people going, hey, here are the baby steps that you can take, steal some of my queries, some of the queries with the folks that I work with, try it out, see if it's cool for you, and decide if You know, that's really cool.
[00:22:47] Myriam: And you want to learn more or thank you very much. I'm happy. I know this one day it may be useful. And the next one is all the cool mashups that you could do. So we're just going to talk about, Hey, as an SEO, here's what at one time or another, you wish that you could do some stuff and you couldn't now you can like.
[00:23:08] Myriam: The world is your oyster. Here are the mashups that are cool that you could do.
[00:23:12] Olga: Can you, like, uh, to wrap up, give some real life examples of things you did? Uh, well You want to do
[00:23:23] Myriam: The issue right now is that it's a bit complicated. Let me explain why. Um, I'm supposed to be promoting something here, so I didn't We just wanted to talk about BigQuery, but I'm realizing it's time for me to promote this.
[00:23:36] Myriam: I'm actually working with, um, three other specialists. So, um, one BigQuery GA4 specialist. One specialist who is more on the, like, BigQuery, but for big websites. Hi, Chris Green! And, uh, my partner, Augustin Delporte, who's also specialized more in the setup. Of BigQuery. And, uh, we have a Brighton SEO course coming up, actually, if you want to learn.
[00:24:03] Myriam: Wow.
[00:24:04] Olga: Wow. Cool.
[00:24:06] Myriam: Cool. So I, it's, it's one of those situations where I'm kind of like, okay, do I tell them to wait for the article? Do I tell them to sign up for the course? What's going on? So give
[00:24:15] Olga: me a bit of time.
[00:24:16] Myriam: Okay. Well, what, what, what I'm going to say is, um, I'm going to be publishing the articles next year like January and February.
[00:24:24] Myriam: So this is coming up soon You will have time to first and foremost before you get excited before you get FOMO before you get anything Please read the first article figuring out figure out if it's for you at least start thinking about this for me the key thing that I I want to bring out the stuff that is important is You know how?
[00:24:45] Myriam: We know that we don't know, so let me explain. Remember when we talked about, like, queries? Google used to give us a lot more keyword data, right? So, the thing about this is that we don't know how much of the iceberg is hidden underneath. We just know what Google gives us, but we have no clue about what Google doesn't.
[00:25:14] Myriam: Give us so that's the first step for me. I think that's one of the things that I think is really cool is that you can deal with these anonymized queries and this is the type of stuff that you would not be able. To see if you're going into Google search console as a regular SEO. So I think if you have a large website, this can be super, super useful.
[00:25:44] Myriam: I'm going to circle back onto another thing. It really means that you are stretching your muscles way beyond your zone of comfort. Okay. So this involves. You knowing how to Google cloud platform operates. It involves you learning the how BigQuery operates and the interface and all of this. It means that you're going to learn some SQL, but you also will have to dig deep into your Google search console knowledge as well.
[00:26:16] Myriam: So that requires a lot of. They're from bits and pieces, right? So, if you're going to learn this, what I would recommend is first and foremost, get a client that agrees. So, first of all, tell them, hey, we're going to do this project and figure out what the pricing is. And that's the hard part, get them on board, like maybe start small and tell them, Hey, we're not going to go over this amount spent in BigQuery and then try to do your best.
[00:26:45] Myriam: Talk to friends, check articles to figure this out, but get your first client and then start playing with it because I can tell you some cool stuff that I've done. Like every single SEO, did you notice how we all bring the cool stuff that we do that works 100 percent and we never talk about the failures?
[00:27:04] Myriam: No, we're not doing this. Okay. I really, I really, really want SEOs to think, do I need to learn this? Is this useful for me? Is there someone in house next to me that can actually help me get started? Before going, all the cool kids are doing it and look what they can do and then you look at your data and you're like, that doesn't make sense for me and now I'm jealous and I'm sad.
[00:27:25] Myriam: No, we're not doing this.
[00:27:27] Olga: Okay. Okay, you made your point. Okay, Miriam. So any final tips, thoughts?
[00:27:35] Myriam: Yes. So I want to know, Olga, like what kind of client do you think you're going to be offering this BigQuery service to?
[00:27:43] Olga: I cannot really reveal my clients. I know, but like, I have one, I have one e commerce bigger, maybe not bigger, they are like medium.
[00:27:54] Olga: So I'm thinking maybe, maybe they will be a good fit. But they aren't like huge, huge in like millions of pages, so.
[00:28:03] Myriam: No, but maybe they have a lot of queries that are underneath like the iron bird. Yeah, this is what I was thinking, yeah. What I would recommend is if you have an e commerce client, maybe try, and when I say e commerce, like not a little mom and pop shop, but you know, like solid mid size e commerce, it's starting to be interesting.
[00:28:24] Myriam: If you have news organizations or like really large, let's say, government websites or anything that has like a lot of information that keeps getting updated, this also could be very interesting. If you are in a highly competitive field, This would also be very interesting because you can do, like I said, trend analysis, like you can actually know some stuff that you wouldn't be able to know because your GSC data stops at 16 months, for example.
[00:28:53] Myriam: If you're dealing with clients that you know have at least a mid size to large size website, you know that there's potential cannibalization issues. Or that there's potential content decay. Yes, you should be looking into this. So that's why I asked you like which type of client and then, and then sometimes you will run into issues.
[00:29:15] Myriam: So I have a friend of mine who works for a very big. In an industry that I can't say, so I'm censoring myself. And he was like, I'm so frustrated. I know the queries I want, I know how to build them and they will not give me access to the console because in house it's another team. That's why I said like.
[00:29:36] Myriam: Figure out, is it worth my time? And do I have to jump through hoops internally to get this done? Yes or no. And if you're on the consultant side, it's not jumping through hoops. It's figuring out how to get them on board with the fact that they are going to have to pay for data storage and the power it takes to query the data to do analysis.
[00:29:58] Olga: Okay. Cool. Cool. I, I really learned a lot today.
[00:30:04] Myriam: I'm sorry. I, I spoke a lot, but this
[00:30:07] Olga: was your show. You were supposed to talk about BigQuery and I was just a newbie asking questions. So this is how this, uh, this episode was supposed to be. It's, it's a lot of
[00:30:18] Myriam: info dumping. So I went a bit fast. If you have more questions, do not hesitate to reach out because I am writing these articles.
[00:30:25] Myriam: And I would like to also say that if you want to learn more about what you can do with BigQuery and GA4 specifically, Sarah Crook in Australia is amazing. So we're collaborating together on the upcoming training that we're going to be offering to agencies and in house marketing teams, but, um, Sarah has been amazing because her specialty is to figure out how to optimize everything so it costs less.
[00:30:52] Myriam: So check her out. Her name is C R O O K E, and, um, I, I can only say really, really good things about her, so I will
[00:31:03] Olga: link her somewhere below.
[00:31:06] Myriam: And if you want, her company is called, uh, Melorium, so you can also check that out. If you do not find her directly, you may find her company. So I Highly recommend you check her out.
[00:31:18] Myriam: Milorium is M E L I O R U M. And yeah, I think, I think for, for this first part, we're good. If you want to go deeper, I may come back and talk about
[00:31:32] Olga: this. Yeah, we will go deeper once I go deeper into that.
[00:31:37] Myriam: Awesome. And maybe, who knows, maybe we will be sharing our queries on your website soon. Who
[00:31:43] Olga: knows? Yeah, exactly.
[00:31:45] Olga: So, Milorium, thank you so much. Thank you. Bye bye. Bye.
[00:32:00]