So when we’re in BigQuery, how do we actually build this dashboard? So BigQuery uses SQL, which is just a programming language. If you’ve used something like XL, you can think about it like that. It’s essentially an XL formula, but within BigQuery. And what this statement is doing is it’s saying when our URL, which is just a column of data within BigQuery, is like, and all we’re saying with this “like” is, if we think about it in XL and filtering, contains en-gb, is just the URL slug, then we want to categorize it as the UK. We just do this several times over for each URL or country language that we target.
Now you might be looking at this and thinking, hey, that looks kind of familiar. If you’ve done any sort of Looker Studio reporting, where you’ve had to separate your brand and generic queries, you’ve probably used this. So you already have a lot of the skills that you need to build this dashboard.
So here we’ve just got some examples of what those columns look like. So our URL column, that comes straight from Search Console. We get that for free. We don’t need to do anything there. Here, we’re saying when this column has a URL that contains this specific phrase, then I want it to target the UK. And what that does is then it builds another column for that target country. What we then do with this target country is we run another case statement to compare it to the actual country that the user landed from, and again that’s data that comes straight from Search Console. You need to do a little bit of manipulation of that data, just because Google provides the data in alpha-3 country codes, so you need to use a lookup table. But really that’s really simple to get. And once you’ve understood this case statement, you’ve understood essentially the whole report.
Here, we’ve used the example of a country language subfolder. But even if you’re using just a country subfolder, the beauty of SQL is it gives us a lot of flexibility, and it allows us to target even just “en”. Here, we could say if en, then we’re targeting the UK and the U.S. We can have our target country as United Kingdom and United States and update the code so then it would send the correct targeting.
What this then does is allows us to visualize this data in Looker Studio. And all we’re doing is we’re creating a column that has our target country. We’re using the column from Search Console that has the country that the user landed on. And all we’re doing is comparing the two. Did the user come from the UK? Yes. Is the URL the UK? Yes. Then our targeting is working. If the user comes from France, then targeting isn’t working.
This then gives us a broad example of which countries we’re getting the most mistargeted traffic from, which can then help us drive our content strategies, and it also helps us measure the effectiveness of the strategies for international SEO. Now this is whether you’ve already implemented these or you’re thinking about implementing these.
Now if you’re at the beginning of your international targeting journey, this dashboard can support in giving you a benchmark of where are we right now with no implementation. Then when we go to the business and ask for the investments in our SEO, we can then show, using data, how effective that investment is and why it’s so important.
My name has been Tanvir Ali. If you would like to connect with me, I’m on LinkedIn, or if you would like to discuss this report further. Thank you.
