Using Data as the Backbone to Driving ROI by Noah Learner (Two Octobers)
Webinar recorded on September 1, 2022
Join our weekly live webinars with the marketing industry’s best and brightest. It’s 100% free. Sign up and attend our next webinar.
Noah Learner the VP of Product at Two Octobers joined the Clearscope webinar to discuss how he uses data to drive ROI.
He walked us through a crash course on critical thinking before sharing one of the most impactful Google Data Studio setups I’ve seen (called Explorer).
Here are our biggest takeaways from Noah’s talk:
Use “what happens if” questions to guide your experiments
Drill into Google Search Console to identify cannibalization issues
Use Google Search Console to identify keywords a page is ranking for and add them to clearscope to
Join our weekly live webinars with the marketing industry’s best and brightest. It’s 100% free. Sign up and attend our next webinar.
Watch the full webinar
If you’re interested in checking out Two Octobers Explorer tool that Noah highlights in the webinar then you can get a free onboarding here: https://branch.tools/promo/
Check out the resources Noah’s shared below:
About Noah Learner:
Noah is a technical marketer, nicknamed the Kraken, who is happiest building SEO tools, automations, data pipelines, and communities.
When not in the lab, he loves skiing, fly fishing, camping with his family, and walking his dogs, Shadow + Max.
Noah sometimes comes up for air to speak at events like MozCon, SearchLove, LocalU, and more.
Follow Noah on Twitter: https://twitter.com/noahlearner
About Two Octobers:
Two Octobers is a Denver, CO Digital marketing agency and B Corporation that offers the deeply strategic approach necessary to drive serious growth for value-driven organizations.
Follow Two Octobers on Twitter: https://twitter.com/twooctobers
Read the transcript
So last year we built this cool product called Explorer. And what was neat about Explorer was that it helped us solve a lot of different problems. But in the process of building it, we learned a ton about challenges that SEOs have with data. And so that gave me a lot of different challenges for how to build products in ways that enabled people to easily derive meaning out of what they were looking at. And so today what we're going to be talking about is data, and data as the backbone of driving our ROI. And part of this is going to be a presentation of the framework that we use the Two Octobers is to drive ROI for our clients. And then afterwards we're going to drift into some pretty cool stuff that you can do when you combine search data coming out of Google Search Console, and even combining that a little bit with Clearscope data which I thought would be pretty fun for today's chat.
Some of it will be like me building things in Data Studio and some of it will be me showing you stuff in Data Studio. Okay, so today we're going super deep into data analysis. What exactly is that? From my perspective, data analysis is all about the art of asking great questions and learning how to answer them. And I think that right there, that curiosity and the ability to think critically is the skill that we as managers, and directors, and VPs, people leading teams, we need to start with critical thought and critical thinking skills as the foundational skill for people to be able to succeed in digital. And so I have found when building Explorer that when I show people tabular data that's in a spreadsheet or in a data studio report, some people really, really struggle with it. And some people find that they can find stories really quickly in that data.
So I kind of want to go deep into that with us. And I'm just turning on my little clicker here, so hopefully it won't make me... Okay. Cool. So we all struggle with data. It's a lot like this sometimes, right? I want to introduce you to the framework that I learned when I joined Two Octobers. I used to have a little agency called bike Shop SEO, we merged it with Two Octobers in March, of 2020, the week that everyone in Colorado was forced to start working at home. So I was pretty lucky about timing there. When I joined the team, immediately we had to start building tools. We built this Google Sheets add on called Post Systematic, which is a free sheets add on if you do stuff with Google business profile data. It's a really cool tool. As we started building it, I found myself always jumping into solution mode first.
It's like the exact wrong way to solve problems. And so let's go through the framework. It's pretty simple. It starts with this concept of defining the problem, really going deep to be able to define it. The next step is to figure out the different questions that you need to ask, and also where you might actually find those answers. And write down all of the questions, identify where the answers to those questions might be so that later on you can build tools or visualizations or spreadsheets, whatever works for you to be able to explore those questions and find your answers. You then go into a process of either building or utilizing tools that are already in place that are off the shelf to be able to find the answers and explore the data. We're going to go really deep here into an example of me doing this so that you can kind of see how I think and maybe learn a little bit about how you could kind of apply those lessons too.
Next, after we have those tools built, we explore the data and we glean insights. And we write them down either on paper or in a spreadsheet or in a Google doc so that you can compile them later. And then after you have your insights you can form a hypothesis for how you might want to solve the problem. I believe that if we do X, the results will be Y, and then you execute a test. And it could be small, it could be a chunk of pages, it can be a page, it can be a section of your website, sub directory, whatever. And then you track the results of that. And then you continuously improve outcomes by rinsing and repeating over and over and over, so that's the basic framework. If you were to take anything away from today, this is what I think we need to do to train all of our new teammates to be exceptional digital marketers. I think that when people come into digital, they get treated like children oftentimes and they're slapped in the face with a checklist to get going.
But they're not exactly taught how to ask questions, how to be curious and how to find answers. And I talked with my friend named Arnold Hellemans, and I asked him, what is the most important thing that you look for when you're hiring? And he's like, I only hire people who enjoy solving puzzles. And that got me really kind of going deep into this concept. So be curious. Let's go deep into this concept of defining the problem. And we're going to actually go into the example now. We are working with a specific client. It's a bike shop that we've worked with since 2015. When we started working with them, they made something around $45,000 a year online. And due to the pandemic and other market conditions and also the quality of our work, we're able to drive revenue to a place where they were doing more than five million dollars a year in revenue last year. And so we entered 2022 with a really aggressive goal. We wanted to hit 40% revenue growth over 2021.
And in the years before the pandemic, we had accelerated growth at that clip. And during the pandemic we had experienced hundreds of percent revenue growth. And so we felt like we could fall back down to a place of normal. We as an agency accepted that challenge and started building a strategy last fall and started executing in January, February, March. What we found was that January, February and March, were going great. We were above goal by four or 5%. When we got through April, all of a sudden we started to see the market completely changed to a place where... What we saw was that we were trending in a way that looked like we weren't going to be able to hit goal, so that's the problem. How do we get back our revenue growth? So we started asking questions, why aren't we hitting the goal? What is it, is it a brand? Is it a category? Is it location? This is a bike shop chain that's located in the Bay Area of California.
We're shipping products all over the United States. We're not shipping to Canada, we're not shipping to any other countries. We know that there are specific brands that we crush with, and we know that there are specific categories that we crush with, specifically mountain bikes. And we also know that we do a lot of business locally, and we'd seen changes in local algorithms. So we needed to understand potentially more about location data. And so we decided to think about different ways of exploring that data. And I started to ask myself the questions like where can I get answers to this? What sources of data would help me? And I started to identify Google Analytics, Google Search Console, and I also knew that I had a lot of access to their point of sale data. And that got me incredibly excited. And I'll show you why in a couple of minutes.
So I started to ask questions like this, how can I aggregate it? And when we get into our point of sale data in a minute, what's really neat is that I could do an export out of that system starting in January, of 2019 and do a monthly export of the data. And we're going to actually look at the shape of the data in a minute so that I could build a time series analysis that helps me explore the data by brand, by categories and five different levels of the category. Also down to the skew level so I could explore sales for every single product in the catalog. Super powerful for doing this type of analysis. All right, so we know that we can get data out of these different sources, and then I needed to know how did I need to set up the data in order to be able to explore it. And in a minute we're going to go into that actual Data Studio report, so I can walk through it with you.
This is what the export looks like. I can see my five categories, I can see my brand, I can see my skew, I can see the name or the description of that specific item. I can see the sales volume and I can also see the quantity or the unit sold. When I saw this in the export coming out of the point of sale, I was like, oh my God, I can filter by any one of these different columns and I'll show you how I did it in the Data Studio report. And I started to play in a big way with that data. Okay, so I started to think through how I should structure the data, and I knew that I was going to want to see that sales data over time. I was also going to want to see Google Analytics data. And one of the things that we're going to get in touch with is you don't know exactly how deep you're going to need to go in your analysis when you get started sometimes.
And so I built both of these to be able to show you both the GA data and the Point Of Sale data. But it turned out that we really only needed to have access to our Point Of Sale data. And I'll show you why in a minute. All right, so another foundational concept that goes with almost every single data studio report that I build is a concept called cross filtering. And I'll show you how to execute it. It basically enables you to very quickly have multiple different tables of data and you could select something in one table and it filters the other table down so that you quickly see insights at scale. And we also knew that based on those different levels of categories, that I'd probably need to be able to drill down from one to the next. And this is kind of an advanced data studio technique and I'll show you how to do it. I think it's super powerful. Both of these two things like drill downs and cross filtering kind of work together.
And also for the win, if we needed to be able to highlight specific things that we know in advance are going to be meaningful, we can use conditional formatting. I don't think I go into a lot of detail in the report with conditional formatting, but your mind will be your guide to kind of give you a lot of insights into how to use it. All right, so these are the things that I knew I was going to want to be able to drill down by. I felt like I didn't know in advance if it was going to be a brand problem, but I felt like if it was a brand problem I was going to need to then figure out the ability to go down to category level one in the experimentation. And this is why we experiment. I found that category level two wasn't useful, neither was category level four. But category level one, three and five was useful as was the skew data.
Now, I also felt like in advance that I'd probably need to do stuff with Google Analytics data, and that I wanted to be able to utilize cross filtering across almost all the different dimensions that were going to be able to give me insights about what was going on from a traffic perspective. I wanted to be able to explore new versus returning users. I wanted to be able to explore differences by gender, by device, by channel, by directory. And I built a calculated field to be able to explore the directory. And I'll show you guys how to build a calculated field. And then I also felt like I'd want to be able to explore information by geography. Turns out none of this was needed, which is really cool when you're doing analysis. You can think and plan and scope up front, but then as you build you might find that only parts of it are necessary.
So then I built the dashboard. And I'm actually going to shift out of the deck now and show you the dashboard directly, because I think you'll really get a kick out of this. Travis, can you give me feedback, is this legible? Can you read it?
Alrighty. Do you remember how I set up front that when we got to April we were just barely below goal? Well, at the end of April I was at 39.2%. I was up a bunch, but I wasn't quite hitting goal. And if I was to go back so that I was only through March, we can see that the trend was not moving in the right direction. And through February we were up a lot. So it's pretty clear that the trend was really becoming a monster and we really weren't liking where things were going. Okay, so what are we looking at? We have two tables. I've got my brand data over here. I've got revenue. I've changed the name of that operation sales that was coming out of the CSV export, I changed it to revenue.
I have some change columns here which are super useful for kind of comparing data from year one to year two. And when I'm doing e-Com revenue comparisons, I'm looking at 2022 versus 2021. And the reason why is that the products that I'm selling are super seasonal. So I know that having year over year is going to be critically important. Now I have the ability to cross filter so I can click on a brand, and I can see all of the exact items that were sold that were that particular brand. And not only that, but I have built into this tool this concept of drill down. And when I click on the brand track, I can then drill down to see all of those category level one. And I can see that my problem, my loss is all happening in one specific category. It's all in bikes.
And then I can drill down even more and I can see which of the next level categories are having issues. And I can see that it's front suspension, hard tail, road bikes, hybrid. It's pretty much across the board. It's the entire brand. In the back of my mind, I knew that when we work with this particular client, that we typically build out these really cool brand guides for every single client that we work with, every single brand that they sell. And so we had built a brand guide for that particular brand, but it wasn't built quite the way that I like to build them. Typically, I like to use lots of jump links, so that hopefully I can get a bigger SAP presence. I just find that using jump links oftentimes will build site links inside the SAP, which I'm pretty excited about. I also have all the different categories built out and this was not built out exactly the way that I wanted in advance.
When I looked at the content for that particular brand I also found that these particular links were leading to search results pages instead of model specific landing pages. And I was really losing my mind because I don't want my search results pages to be internal site search. It was like, no, this has to go to an optimized page. So I instantly found opportunities. So we get back to our report. Here's the thing that's fascinating about doing analysis, I was up 39.2% and I could look at all my data and I could look at all my brands and I could see that almost all of them were crushing it at that point. But I had three particular brands that were not doing well and one of them was my most important brand, it was down 32%. I also found that my historically second place brand was down 25%, and my historic third place brand was down almost 60%. And I identified based on that point of sale data opportunities for improvement and I came up with the strategy. And we're going to get back to that in a minute.
But lets sort of like look at how these things work together just so that you can kind of glean potentially some ways of working with data that maybe are kind of cool. So when you want to do drill downs, the way to set them up is you can select the page or select the chart and then you can over here on the right... Can you give me feedback? Can you see this stuff? I don't know that I can pin on the right panel.
Yeah, we can see it.
I guess, let me zoom in. Okay-
Yeah, that's better.
The way to execute it is you turn this drill down little switch on, and then you build your dimensions in a stack. And then you select which you want to be your default. And then it'll drill down in the order that you build these in. So it allows you to go from brand to category level one, to three, to five. And you could totally do it brand to category five and three to one. Whatever works for you for how you want to look at the data, that's how you can build your drill downs in. And then the cross filtering is really simple. You select all the stuff that you want to be able to cross filter. And in this case I'll just like click that chart and I turn this cross filtering switch on. And it's always at the bottom of this setup panel, which is this thing right here. So if I go back to view mode, what happens is if I click this it'll cross filter everything else. I can also do the same thing where I can do something... I guess I don't have cross filtering turned on these charts.
But oftentimes in a time series chart you can do something called brushing, and I'll show you how to do that in another Data Studio report in a minute. But basically you can select a time range and it'll update all of your other metrics in the other charts. I also love to use a date picker, big surprise so that I can see my stuff over time. And I also knew that I wanted to be able to explore things specifically based on either model or skew. And that's really the bees knees. And it was really bizarre to me that as a marketer and as an SEO, I was coming up with a content strategy just by looking at point of sale data. I don't know if y'all have ever tried that, but I find it to be super interesting. Okay, here's the Google Analytics version of this. And I don't recommend you build this but if anyone wants access to this, please reach out to me, I'm happy to share it with you. No problem.
Basically, same concept, we're using cross filtering. And this is why I don't recommend you build this way, is that the Google Analytics native connector is incredibly slow. But the way that I used it was that whenever I work in e-Com, I always like to work with a super simple formula. And you're probably all going to roll your eyes at this but for me, revenue equals traffic or sessions times the conversion rate times the average order value. And the way that I tried to isolate what was problematic was to by pick a specific dimension and watch what its impact was on the overall trends. And that would give me a sense of where the problem was. Is it in returning visitor? Is it in gender? Is it in device? And you could get really specific in the segmentation by using this way of working. Typically, I always like to get data up into Google BigQuery, if I can. And the reason for that is that Google BigQuery and Data Studio together are just an incredible powerhouse of technology. And it's super, super fast and it's inexpensive to store your data up there.
And that's exactly how I built this particular tool. I did those monthly exports coming out of their Point Of Sale system, and I uploaded each CSV up to Google BigQuery one at a time. And then all of a sudden I had three plus years of data to build or reference, which was super powerful, so that's that data studio. And I gleaned my insights. I just want to get back to make sure that I'm not... Yeah, so I already showed you cross filtering. I showed drill downs, I showed how to build them, gleaned insights. I already showed the fact that these particular brands were the problem. This is this concept of happy accidents, which is like you don't quite know where the value is going to be until you build the ways of performing the visualization. And for me it was like, oh my God, I can do all of this just with my Point Of Sale data.
So it's not going to surprise you what my hypothesis was. I felt like big surprise if I build better content around these brands, we'd be able to grow traffic and revenues. Specifically when you're forming a hypothesis, depending on how scientific you want to be there's tons of great tools around it. There are tools like Forecast Forage, there's also tools like SEO, the tool that Jeremy Rivera is building, which is really cool. Maybe someone can add that in the chat for everybody. It's like SEO or Arcade. He's got a cool SEO forecasting tool. So you could say things like, hey, if I build X content, I think I'll be able to grow traffic by Y value and if so, I'll be able to grow my revenue by Z. And you can get pretty scientific. I'm not going to go into that today because we just don't have enough time.
But at a sort of simplistic level, it was clear to me that if I built better content for these brands I'd be able to get better topical authority. I'd be able to get more traffic to my collection pages. I'd also be able to get more traffic to optimize landing pages for the specific models. And I'll show you that in a minute, the sort of output that we built. And then I would also see more clicks and more traffic going direct to our product detail pages, which would grow revenue for that brand and help turn that story around. So we built the test, what we actually did because I think you'll get a kick out of it. So we built the brand guide and we improved it significantly. We also built pages that took the user directly to a collection page for each brand and category. And then we had an internal linking widget at the top so that it would take you to all trek bikes, mountain, road, electric, city, and kids' bikes.
And I'll show you why that's important or why we found it to be important in just a couple of minutes. So we get to also optimize our intro, our page title, our meta description, et cetera, for every single model. So what was the outcome of this test? I'll get back into that. This is how we get into our actually tracking and analyzing. The number of brand inquiries in top five went from 550 to 920. And we saw the amount of stuff that we ranked in position one go up substantially, which is pretty cool. I hate talking about ranking changes, I think it's awful but it was a metric that we could look at. This is the weird thing, we saw clicks drop but we also saw our impressions explode. So this is crazy, right? I just showed you that our amount of things that were in the top five almost doubled. And the stuff that was in position one more than doubled but our clicks dropped. And that's like a head scratcher. And that's where we get into market forces.
And I think I mentioned that the market totally tanked. But here's the thing that we saw, we turned revenue around. And it's because I think we were able to have a more enjoyable, easier to shop user experience for that particular brand. In the next two months after we performed the test we saw 18% revenue growth year over year. And then we're rinsing and repeating that across different brands that they sell on that site. Before we get into the next section, this might be a good point to stop for questions. I haven't looked at any of these. Can you tell me what's going on there?
No, yeah, we're good. We can keep moving. That's great.
Are we doing good?
Is everybody having fun? Can I get a little feedback? Little love? Oh, look at this. We're hiring, growing, I love it. Okay, cool, thanks for adding the SEO Arcade link to. I think that's super good. Okay, so let's take it up a notch. What are we going to do next? Keyword expansion, and this is where we get to combine Google Search Console data with Clearscope. And I'm going to show you how to do some cool stuff on Data Studio so that you can get some interesting data out and then join it with Clearscope to build some fun content. Whoops-a- daisy, I did it again. So again, we're talking all about mountain bikes and one of the key things when we look at mountain bikes... And I'm going to go a little bit smaller, is I want to go to one particular page and I want to go to one particular time range. I want to look at mountain bikes for sale. And this is why I was targeting for sale with those specific mountain bike pages.
And this is the value of performing tests with our content. When I went... Yes, I use Google Search Console. Oh, I'm not allowed to vote, you're killing me man. Okay, so when we built this tool called Explorer, this is one of the tools that I love to use every single day when I'm doing SEO for clients. Basically we connect Google Search Console data, we pipe it into Google BigQuery, we transform it like crazy to make it super useful so that we can split all of our search data up by stages in the funnel. One of the things we like to do is to say, hey, if I'm building content in an e-Comm capacity, I want to focus on the bottom of the funnel because it's closest to selling stuff. And when we went to build this, we focused a lot on selling stuff. So one of the modifiers that I thought was going to be really important was for sale. And when we look at the data, we can see why that's the case. If you notice here, I get 4.2 million impressions between February one and September one in for sale queries.
The next closest one has a near me or location specific kind of bent to it. This jumped off the page at me. This is the importance of seeing things and changing your context. In Data Studio when you're using dropdowns like this, you'll notice on the left it says funnel keywords and on the right it says impressions. Well, impressions gives us a really deep sense of opportunity. Well, I can also change that to be clicks so I can see what my current traction is. And when I saw the impression value, I immediately did this and I said show me just my for sale queries.
So I ask myself the question, what happens if I add as a page title modifier for sale, just mountain bikes for sale to my mountain bike collection page? Look at the impact. We went from zero clicks to hundreds of clicks per week. We saw massive impression growth and we saw a really nice impact on position. So it's like simple things like that, that you can get just by changing your perspective and sort of the ways that we see things. Okay, I'm going to close that. Yay, everybody uses Google Search Console, I think that's amazing. Okay, so this is the point that I wanted to be able to say, which is when we're conceptualizing how we're going to build a visualization we want to think about what is meaningful for the specific client or the specific website that we're working with. And then we need to build our visualization in a way that helps us see those things.
If I know that my client, what's important for their particular website is this concept of the journey, then I'm going to build my data analysis in a way that allows me to explore the different stages in the funnel. I'm going to learn what terms actually matter for that funnel stage by looking at my data. And then I'm going to build visualizations that allow me to segment that data using things like calculated fields. And I'll show you how to build a calculated field if we have time. Looks like we have 25 minutes left. So for bottom of the funnel, for sale, model year, best, near me, for shop, oh, these are all of them. Bottom of the funnel, for sale, near me, shop, buy, discount, service, where to buy, hire, which isn't really relevant for a bike shop. So I'm not sure why that's there other than the fact that they're trying to hire people and we have content around that, under, best x under Y price.
People are really curious about shipping price, closeout deal, credit, et cetera. And I wanted to be able to have the way to be able to look at all of these different data points and to be able to have the ability to filter. Same thing for middle of the funnel. We're going to see all different types of queries there, model year, best review, top, cheap, good, value, how much, types, which, affordable, et cetera. And then we have top of the funnel type queries too. And if you guys reach out to me, I'm happy to share documentation around how we think about funnel stuff so that you can build your own list if you want to. Top of the funnel we get more question wordy type of modifiers, whether it's for, guide, with, how, what is, where, issue, remove, et cetera. And this is where we have those same concepts at play cross filtering, and I can cross filter by date as well. And remember before I talked about brushing, well, we can go like this and we can focus on just those particular date ranges if we think that there's important value there.
And we can also change the way that we see time series data by changing the grain from week to month to year, all the way down to day. Which enables us to change our context which is really critical as we change the time from a lot of time to a little bit of time. We'll notice here that I'm going from April, I think it's the last couple of months of this year. Oh no, it's last year February to September. And if I'm only looking at a month's worth of data, I'm probably going to want to be at the daily grain versus the monthly grain. Okay, cool. We can also filter our data by query, by page, by funnel stage, by question words. And I'm going to just reset this so you can kind of see how powerful that is, so that we can find the specific question words in our data.
And we can also filter by position bucket, which I think is incredibly important for looking into low hanging fruit stuff. So keyword expansion stuff. Let's say we want to work on that mountain bikes page that we built. And just to kind of put it in perspective, we're going to get back to our mountain bike page. This is the money collection page for our website, this drives a huge amount of revenue. And if I want to do keyword expansion on this particular page, one of the things that I can do is I can go to a page that we built, and I built this specifically to give us deep insights into stuff that's happening at the page level. I can find my mountain bike page, I can then see all of the queries that are happening on that page and there's 2,448 queries that are getting impressions for that page inside the last three months.
I've got all these different ways of filtering the data if I want. And you'll notice this search query, and I'll get back to that when we get to our algorithm stuff in a minute. But I can select this page and then I can look at all of these different queries, and I can look at my specific piece of content and I can determine if that's already on the page in a meaningful way. And then I can think about, hey, do I need to build sub collection pages off of this main page or does it make sense to also combine it into this one main page. For a collection page it doesn't necessarily make sense to go to try and serve multiple intents. But if it was more of a long form guide type content piece, clearly we'd have to make that decision a little bit more carefully. Okay, so next, how do we combine this with Clearscope? This is the fun part. We can click this three dots and go export, and export this data into a Google Sheet.
And then what's fun about this, if you're working with Clearscope is that you can put in a search term and I've already done that, which is mountain bikes for sale. And Clearscope will show you what's important and what all the competition is using and how much they're using those particular terms. And then I can look at my export and I can grab the terms that matter to me. And so I can go into my Google Sheet and I can create it so that it filters. I can sort by any column that I want to get a view and probably I'm going to want to sort by clicks or impressions. And for this, let's sort impressions in a downward fashion, sort of descending. And I can grab a whole bunch of these terms, I can copy them. And again, I'm not using a lot of analysis to get to this stage.
If I was actually building the content I'd be way more granular with how I'm doing it. I can go back into Clearscope and I can go plus, and I can paste all the terms that matter to me into there. And you don't have to worry here, you don't have to worry about, hey, Clearscope is already telling me that these terms matter. If it's duplicative, Clearscope is super smart, it won't automatically add those. But what's neat is if I scroll down, I'm going to see all of my custom terms that I've added. And some of these will show that they're not generally added and some of these will show that they're added X quantity of times per page. And so I can see that there's some interesting opportunities that Clearscope didn't offer out of the box as I go to build my piece of content.
Now, that's not it. We also know that when we're building content, oftentimes people ask questions that are super important. Think about like FAQ sections or FAQ pages. Well, I care a ton about questions. So I have a page that I built for looking at data, and depending on the type of website that I'm working in, I might want to be able to look at how people talk about specific things. So I'm going to say my page contains 1006, that's this page right here. And I'm going to hit enter. And then I think what we're going to find are all of the questions that people ask, that are already happening on that particular page. Or actually, can I do an ends with, no, I'd have to do advanced rejects which is fine. I could go like this. I could say like that and what this will do is it'll give me a pattern that means show me any page that has any characters in front and ends with this 1006 trailing slash, and the dollar sign means ends with.
And I think I can go enter here and it'll show me all the questions that are just for that mountain bike page. And then I can export out all my questions to a Google Sheet and I can pull those into Clearscope. Are you digging this? This is pretty cool, right?
This is awesome.
Yeah, so we can go like that and this will populate my question ideas as I'm getting into using Clearscope. And we can see if anyone else is talking about these questions or not. And I can scroll down and see if any of these are being talked about by any of my competition. And I'm not seeing anyone else doing that. And so I'm starting to think that there's probably some opportunities. Oh, where can I buy a mountain bike? That's already being talked about by one to two people. But that looks like some interesting opportunities, so that's keyword expansion. I think that's a pretty cool way to then get in here and to work with a content brief that you've already got in place. And then start building your content inside of the editor for Clearscope, which I think is really neat. Any questions around that, around content expansion?
We have a question from Susie. It's kind of a multiple steps back, but it was about when you added for sale for the bike page, just kind of asking why did it eventually decline?
Yeah, 100%. The bike industry goes like this, march, April, May, June, July. And then also the other thing, Susie, that happened was that all of our competition saw what we were doing and started to mimic them. And so no one else had for sale in their page titles. And after we did it, everyone else started to mimic us. Okay, so that was keyword expansion, keyword cannibalization, which I think is a huge problem for websites. We can highlight it in multiple ways. I think one of the neatest ways to highlight it is to do it in a couple of different steps. We can start with our query, we can see... And then in the right hand side we've got our URLs. And then this is a pattern that I use all the time at Data Studio. I love to do a stacked area chart. And in this particular scenario, this is a stacked area chart that's showing clicks by page. So I can see what pages actually matter, right?
And down here it's impressions by page. And then I can also see... And this is pretty muddy, I have a hard time visualizing this, I can see position for each of the pages. And so I can see here on the left, here are all of the queries and they're sorted by the amount of pages that are ranking for that query. I can totally sort of change it by click. I can say like, hey, I want to see the queries that matter based on click volume. I could say bike builder and there's only one page that ranks for that. So clearly it's going to be highlighted green. That's super cool. But if I scroll down and I see the trek Roscoe 9, I can see that there are three pages that are ranking for that. And luckily, one page is getting more than half of the clicks and more than half of the impressions.
And there are also scenarios... And as I click through, we're going to see some different things on the right, we're going to see that most of the time one of the pages is highlighted as the keeper page... And y'all give me one, one of my dogs is freaking out. So we can see that in scenarios where more than when this one page is getting more than half of the clicks and more than half of the impressions, the whole thing is highlighted green. But in scenarios where I'm going to click through to a couple of these, we might find that one of the pages only gets more than half of the clicks, and in that scenario just that column will be green. And it looks like my conditional formatting's changed. That's why this page isn't showing green in this one column.
But if I click to a couple of these, we might see a scenario where one of the columns will be green just for the impressions. And so in scenarios where one page isn't getting more than half of both, then you have to make decisions. And so I thought it would be a useful hint if one of the columns was highlighted if it got more than half. So there's three scenarios. No green, it's a show and you have to make a determination based on which page maps to the intent the best. And you have to act as a marketer to make that decision of where you think it makes the most sense to funnel users. If it's all green, that's telling you that that page is mapping to the intent the way that Google thinks it should. And you're probably going to want a 301 redirect pages to that one and de-optimize the others by maybe looking at your page title, your H1 tags, your URLs, your H2s to determine if maybe you need to optimize those. And if you want to be really aggressive, you can 302 redirect everything to your keeper page.
And then if only one of the columns is highlighted, that's giving you a hint that, that would be your keeper page potentially. And then algorithm stuff. Are we doing good question wise?
We actually got one from Guy Macy's asking, where do you get started? He's not a developer. Are there any things like off the shelf he can kind of grab and start using or should he find a developer?
Great questions. So we built this tool because we had bike shops that we were super concerned about that their data was going to be not useful after the pandemic. And that's what we're looking at now as a product called Explorer. And so if you think this is baller, you can reach out to me and I can show you, give you the full demo and stuff. But it's a combination of data going into BigQuery plus the visualization of the front end. And it costs 30 bucks a month for the first property. Property is Google Search Console property, and then it's 20 bucks each additional property that gets pulled in. Okay, I wanted to show you guys algorithm stuff and another way that you can use Data Studio for doing forensic analysis. And Guy, reach out to me if you've got questions. I'm stoked to chat about this.
I wanted to show you guys how you can use Data Studio also to find change, because a lot of times we don't know why things are changing. Why performance end date is earlier than... No, I guess I got to hit September there. Why things are changing on our website. Is it a directory? Is it a page, Is it a query? Why are things changing? And this is a great example of change on a site. Now keep in mind this is a bike shop, so it's seasonal. We see super busy summers, quiet falls, et cetera. But pretend you've built some content and you're like, whoa, my traffic is tanking. Why is it tanking? I could look at these particular dates. I could drop into November 22nd and I could go... Let's just go to here for funsies. We'll go November 22nd to December 26th. And the reason why is I want to compare that period of time to the period before that.
And I want to be able to look at this data based on directory. And this is like using a calculated field to be able to split up our website's URLs by directory. And then I want in this page show pages and then in this section show queries. And that's going to enable me to see what's going on my website. Check this out, do you guys see a trend? I do. I see that products are up, articles are down, collection pages are down. And so if I go into my articles to see what's going on there, I very quickly see a story. This one page is the cause of that drop and I can... Whoops, and that's this page. We build custom mountain bikes for people and this page was starting to perform less well, not enough content, it's not helpful, it's not useful enough, et cetera.
And if I click on that page, it'll filter all my query data down. And then there are times where it's just one query that's driving all the changes. And then other times it's more of a general problem. And in this particular case, it's a general thing. And really what's happening is that the position has dropped for this. Google determined that this page is no longer as helpful or useful. And that's why we're seeing so much of our clicks changing and our impressions changing. So I just thought that this would be super interesting for you guys. And to take it a step further, I showed you that there was a polling filter and here's why. We have a page that we built to enable us to see changes in algorithms. When algorithm changes hit our site. And we work with Marie Haynes, their team gives us SEO data so that we can track algorithm changes over time.
And what we noticed that was really weird was that, right around here when this algorithm impact changed, what we started to see was that one particular page in general. Look at the shape of this, look at how there are some new queries that have a new shape. And if I click on this one particular page, we can see this is a page that has the brand of Bontrager, it has the level of model of triple X and then it's a bike helmet. The wave cell bike helmet. But if you look at this, Google when they rolled out this algorithm, they were targeting spam. And so what did they do? It's a broad spam update that got rid of all these not safe for work websites and all kinds of PB&E type stuff. And then all of a sudden, websites that are safe for work started to get clicks and impressions around not safe for work queries.
And this helped us see that. And if we look at all of the top queries that page is getting, this is the first one that has anything to do with bike that I've seen. And as I keep scrolling down, eventually I get to something with a model name, but almost all of it is polling query related. And that's why we build filters using things called billions that enable us to see, like this one would enable us to see Google business profile stuff. But we can also see brand, we can isolate by brand, funnel stage, question terms and low hanging fruit. More questions because we're almost done.
Yeah, we got one more come from Guy. So do you guys can start jumping up and down at your desk when you see something like that? I guess that obvious.
Yeah, all this stuff jumps off the page. And so the way that I approach this stuff is I build tools that enable me to slice and dice data any way I want so that I can flexibly look at data. And then I can build workflows around it. For example, when we get into trending data, I think this is really neat. I can look at data by year over year, last seven days, last 14 days, 28, 30, 90 or by previous. And I can align that data based on day of the week. So I can say like, hey, I want to look at my data 52 weeks ago, Monday to Monday. And then I can also do stuff like this, I can build those drill downs like I showed you in that other report. So I can say, hey, show me all the pages that are experiencing negative change in the last seven days.
And then show me the buckets. Am I down 10%, 20%, 40%? And I can pick a specific bucket and I can keep drilling down and I can see all the pages that fall into that bucket. So are you like this man? Did you have fun?
That's awesome. Yeah, I think everybody's brains are blown. It's been fantastic.
Yeah. So this is sort of like you get these when you start playing with Data Studio, start with a table. Simple, get data in a table. And then ask yourself what questions do I want to answer? And then see, all right, well, is my answer going to be in clicks? Is it going to be in impressions? Is it going to be at the page level? Or is it going to be at the directory level? What can I highlight that'll help me find answers? How do I want to work? How do I want to see things? And then you can use things like drill down to be able to build a workflow out. Hey, I want to see trending data the last month over last year, last 30 days versus last year. These are all the queries, the colors imply performance, green is good, yellow bad. This page is doing better last month over year, over year.
That's pretty interesting. But let's say I want to say, alright, well, which pages are doing well? Drill down. And then which bucket? Ooh, this is cool, one of these is performing 300% better by clicks. Let's go look at that. And then I can see all of the pages that are doing better. How do I improve them? How do I apply leverage to make them even better? Page title modifications, descriptions, H1 tags, et cetera. All right.
Wow, that's an impressive tool.
So anyway, I just wanted to say thanks. And I really wanted to wrap this up just by saying critical thinking. Learn how to work with data, learn how to see it, build visualizations. And when I say build, that's scary to people. It's really just like getting information into a Google Sheet, exporting data out of one tool into another. Learn how to join and blend it together so that you can have more context and more meaning. And the ability to create meaning is what enables you to create a hypothesis which you can then test, which you can then look at later to analyze if it's working or not. More of those performed faster and faster will drive ROI more and more for your clients.
Yeah, this is awesome. Thanks, Noah.
Yeah, definitely great job. I think everybody's kind of blown away with just elaborate Data Studio set up. And I think if everyone's interested in the Explorer tool, just reach out to you? That's the best.
Happy to do a demo of the whole thing.
And everyone else, definitely drop Noah a note on Twitter thanking him for the time he spent with you today. We'll send out the recording tomorrow. And Noah, is there anything else you want to share before we give everybody back their day?
Yeah. Last thing, in terms of building your careers and really taking it to the next level, I think the most important thing is building a huge network of people who really care about you. If you're looking to grow your network, please reach out to me. I know people throughout the industry in all kinds of corporations, whether it's in-house or agency. And if you're looking to build your network and build connections, or you have specific holes or gaps that you need to fill, reach out to me. I know everybody in SEO, so hopefully I can help you really take your career to the next level. I'd love to help.
Yeah, thanks for that, Noah. I really appreciate that.
Well, everybody have a great rest of your day and we'll talk soon. Bye.
Bye everybody. Cheers.
Director of Marketing, Clearscope
Join thousands of marketers who receive our weekly emails.
We share content marketing best practices and SEO strategies from the brightest minds in marketing. You’ll also be the first to learn about and join our next weekly webinar with the industry’s best.Join today