During a recent crawl analysis and audit of a large-scale site that was negatively impacted by Google’s core updates, I surfaced an interesting SEO problem. I found many thinner and low-quality pages that were being canonicalized to other stronger pages, but the pages didn’t contain equivalent content. As soon as I saw that, I had a feeling what I would see next… since it’s something I have witnessed a number of times before.

Many of the lower-quality pages that were being canonicalized were actually being indexed. Google was simply ignoring rel canonical since the pages didn’t contain equivalent content. That can absolutely happen and I documented that in a case study a few years ago. And when that happens on a large-scale site, thousands of lower-quality pages can get indexed (without the site owner even knowing that’s happening).

For example, imagine a problematic page type that might account for 50K, 100K, or more pages indexed. And when Google takes every page indexed into account when evaluating quality, you can have a big problem on your hands.

In the screenshot below, you can see that Google was ignoring the user-declared canonical and selecting the inspected url instead. Not good:

In addition to just getting indexed, those lower-quality pages might even be ranking in the search results for queries and users could be visiting those pages by mistake. Imagine they are thin, lower-quality pages that can’t meet or exceed user expectations. Or maybe they are ranking instead of the pages you intend to rank for those queries. In a case like that, the problematic pages are the ones winning in the SERPs for some reason, which is leading to a poor user experience. That’s a double whammy SEO-wise.

Quickly Estimating The Severity Of The Problem
After uncovering the problem I mentioned above, I wanted to quickly gauge how bad of a situation my client was facing. To do that, I wanted to estimate the number of problematic pages indexed, including how many were ranking and driving traffic from Google. This would help build a case for handling the issue sooner than later.

Unfortunately, the problematic pages weren’t all in one directory so I needed to get creative in order to drill into that data (via filtering, regex, etc.) This can be the case when the urls contain certain parameters or patterns of characters like numerical sequences or some other identifying pattern.

In this post, I’ll cover a process I used for roughly discovering how big of an indexing problem a site had with problematic page types (even when it’s hard to isolate the page type by directory). The process will also reveal how many pages are currently ranking and driving traffic from Google organic. By the end, you’ll have enough data to tell an interesting SEO story, which can help make your case for prioritizing the problem.

A Note About Rel Canonical – IT’S A HINT, NOT A DIRECTIVE
For any site owner that’s mass-canonicalizing lower-quality pages to non-equivalent pages, then this section of my post is extremely important. For example, if you think you have a similar situation to what I mentioned earlier and you’re saying, “we’re fine since we’re handling the lower-quality pages via rel canonical…”, then I’ve got some potentially bad news for you.

As mentioned earlier, rel canonical is just a hint, and not a directive. Google can, and will, ignore rel canonical if the pages don’t contain equivalent content (or extremely similar content). Again, I wrote a case study about that exact situation where Google was simply ignoring rel canonical and indexing many of those pages. Google’s John Mueller has explained this many times as well during webmaster hangout videos and on Twitter.

And if Google is ignoring rel canonical on a large-scale site, then you can absolutely run into a situation where many lower-quality or thin pages are getting indexed. And remember, Google takes all pages indexed into account when evaluating quality on a site. Therefore, don’t just blindly rely on rel canonical. It might not work out well for you.

Walking through an example (based on a real-world situation I just dealt with):
To quickly summarize the situation I surfaced recently, there are tens of thousands of pages being canonicalized to other pages on the site that aren’t equivalent content-wise. Many were being indexed since Google was ignoring rel canonical. Unfortunately, the pages weren’t located in a specific directory, so it was hard to isolate them without getting creative. The urls did contain a pattern, which I’ll cover soon.

My goal was to estimate how many pages were indexed and how many were ranking and driving traffic from Google organic. Remember, just finding pages ranking and driving traffic isn’t enough, since there could be many pages indexed that aren’t ranking in the SERPs. Those are still problematic from an SEO standpoint.

The data would help build a case for prioritizing the situation (so my client could fix the problem sooner than later). It’s a large-scale with many moving parts, so it’s not like you can just take action without making a strong case. Volatility-wise, the site was impacted by a recent core update and there could be thousands (or more) lower-quality or thin pages indexed that shouldn’t be.

With that out of the way, let’s dig in.

Gauging The Situation & The Limits Of GSC
To gauge the situation, it’s important to understand how big of a problem there is currently and then form a plan of attack for properly tackling the issue. In order to do this, we’ll need to rely on several tools and methods. If you have a smaller site, you can get away with just using Google Search Console (GSC) and Google Analytics (GA). But for larger-scale sites, you might need to get creative in order to surface the data. I’ll explain more about why in the following sections.

Index Coverage in GSC – The Diet Coke of indexing data.
The index coverage reporting in GSC is awesome and enables you to view a number of important reports directly from Google. You can view errors, warnings, pages indexed, and then a list of reports covering pages that are being excluded from indexing. You can often find glaring issues in those reports based on Google crawling your site.

Based on what we’re trying to surface, you might think you can go directly to the Valid (and Indexed) report, export all pages indexed, then filter by problematic page type, and that would do the trick. Well, if you have a site with less than 1,000 pages indexed, you’re in luck. You can do just that. But if you have more than 1,000 pages, then you’re out of luck.

GSC’s Index Coverage reporting only provides 1,000 urls per report and there’s no API (yet) for bulk exporting data. Needless to say, this is extremely limiting for large-scale sites. To quote Dr. Evil, it’s like the Diet Coke of indexing data. Just one calorie… not thorough enough.

Search Console API & Analytics Edge
Even though exporting urls from the Valid (and Indexed) category of index coverage isn’t sufficient for larger-scale sites, you can still tap into the Search Console API to bulk export Search Analytics data. That will enable you to export all landing pages from organic search over the past 16 months that have impressions or clicks (basically pages that were ranking and driving traffic from Google). That’s a good start since if a page is ranking in Google, it must be indexed. We still want data about pages indexed that aren’t ranking, but again, it’s a start.  

My favorite tool for bulk exporting data from GSC is Analytics Edge. I’ve written about Analytics Edge multiple times and you should definitely check out those posts to get familiar with the Excel plugin. It’s powerful, quick, reasonably priced, and works like a charm.

For our situation, it would be great to find out how many of those problematic pages are gaining impressions and clicks in Google organic. Since the pages are hard to isolate by directory or site section, we can export all landing pages from GSC and then use Excel to slice and dice the data via filtering. You can also use the Analytics Edge Core Add-in to use regex while you’re in the process of exporting data (all in one shot). More about that soon.

A Note About Regex For Slicing And Dicing The Data
Since the pages aren’t in one directory, using regular expressions (regex) is killer here. Then you can filter using regular expressions that target certain url patterns (like isolating parameters or a sequence of characters). To do this, you can use the Analytics Edge Core Plugin in conjunction with the Search Console connector so you can export the list of urls AND filter by a regular expression all in one macro.

I won’t cover how to do that in this post, since that can be its own post… but I wanted to make sure you understood using regex was possible.

You can also use Data Studio and filter based on regular expressions (if you are exporting GSC data via Data Studio). The core point is that you want to export all pages from GSC that match the problematic page type. That will give you an understanding of any lower-quality pages ranking and driving traffic (that match the page type we are targeting).

Now let’s get more data about landing pages driving traffic from Google organic via Google Analytics.

Google Analytics with Native Regex
In order to find all problematic page types that are driving traffic from Google organic, fire up GA and head to Acquisition, All Traffic, and then Source/Medium. This will list all traffic sources driving traffic to the site in the timeframe you selected. Choose a timeframe that makes sense based on your specific situation. For this example, we’ll select the past six months.

Then click Google/Organic to view all traffic from Google organic search during the timeframe. Now we need to dimension the report by landing page to view all pages receiving traffic from Google organic. Under Primary Dimension, click Other, then Commonly Used, and then select Landing Page. Boom, you will see all landing pages from Google organic.

But remember, we’re trying to isolate problematic page types. This is where the power of regular expressions comes in handy (as mentioned earlier). Unlike GSC, Google Analytics natively supports regex in the advanced search box, so dust off those regex skills and go to town.

Let’s say all of the problematic page types have two sets of five-digit numbers in the url. They aren’t in a specific directory, but both five-digit sequences do show up in all of the urls for the problematic page type separated by a slash. By entering a regular expression that captures that formula, you can filter your report to return just those pages.

For this example, you could use a regex like:
\d{5}.\d{5}

That will capture any url that contains five digits, any character after that (like a slash), and then five more digits. Now all I need to do is export the urls from GA (or just document the number of urls that were returned). Remember, we’re just trying to estimate how many of those pages are indexed, ranking and/or driving traffic from Google. The benefit of exporting is that you can send them through to your dev team so they can further investigate the urls that are getting indexed by mistake.

Note, you can also use Analytics Edge to bulk export all of your landing pages from Google Analytics (if it’s a large-scale site with tens of thousands (or more) pages in the report. And again, you can combine the Analytics Edge Core Plugin with the GA connector to filter by regex while you are exporting (all in one shot).

Third-party tools like SEMrush
OK, now our case is taking shape. We have the number of pages ranking and driving traffic from Google organic via GSC and GA. Now let’s layer on even more data.

Third-party search visibility tools provide many of the queries and landing pages for each domain that are ranking in Google organic. It’s another great data source for finding pages indexed (since if the pages are ranking, they must be indexed).

You can also surface problematic pages that are ranking well, which can bolster your case. Imagine a thin and/or lower-quality page ranking at the top of the search results for a query, when another page on your site should be there instead. Examples like this can drive change quickly internally. And you can also see rankings over time to isolate when those pages started ranking, which can be helpful when conveying the situation to your dev team, marketing team, CMO, etc.

For example, here’s a query where several pages from the same site are competing in the SERPs. You would definitely want to know this, especially if some of those urls were lower-quality and shouldn’t be indexed. You can also view the change in position during specific times.

For this example, we’ll use one of my favorite SEO tools, SEMrush. Once you fire up SEMrush, just type in the domain name and head to the Organic Research section. Once there, click the Pages report and you’ll see all of the pages that are ranking in Google from that domain (that SEMrush has picked up).

Note, you can only export a limited set of pages based on your account level unless you purchase a custom report. For example, I can export up to 30K urls per report. That may be sufficient for some sites, while other larger-scale sites might need more data. Regardless, you’ll be gaining additional data to play with including the number of pages ranking in Google for documentation purposes (which is really what we want at this stage).

You can also filter urls directly in SEMrush to cut down the number of pages to export, but you can’t use regex in the tool itself. Once you export the landing pages, you can slice and dice in Excel or other tools to isolate the problematic page type.

Query Recipes – Hunting down rough indexing levels via advanced search operators
OK, now we know the number of pages indexed by understanding how many pages are ranking or receiving traffic from Google. But that doesn’t tell us the number of pages indexed that aren’t ranking or driving traffic. Remember, Google takes every page indexed into account when evaluating quality, so it’s important to understand that number.

Advanced query operators can be powerful for roughly surfacing the number of pages indexed that match certain criteria. Depending on your situation, you can use a number of advanced search query operators together to gauge the number of pages indexed. For example, you can create a “query recipe” that surfaces specific types of pages that are indexed.

It’s important to understand that site commands are not perfectly accurate… so you are just trying to get a rough number of the pages indexed by page type. I’ve found advanced search queries like this very helpful when digging into an indexing problem.

So, you might combine a site command with an inurl command to surface pages with a certain parameter or character sequences that are indexed. Or maybe you combine that with an intitle command to include only pages with a certain word or phrase in the title. And you can even combine all of that with text in quotes if you know a page type contains a heading or text segment in the page content. You can definitely get creative here.

If you repeat this process to surface more urls that match a problematic page type, then you can get a rough number of pages indexed. You can’t export the data, but you can get a rough number to add to your total. Again, you are building a case. You don’t need every bit of data.

Here are some examples of what you can do with advanced query operators:

Site command + inurl:
site:domain.com inurl:12/2017
site:domain.com inurl:pid=

Site command + inurl + intitle
site:domain.com inurl:a5000 intitle:archive
site:domain.com inurl:tbio intitle:author

Site command + inurl + intitle + text in quotes
site:domain.com inurl:c700 intitle:archive “celebrity news”

Using advanced query operators enable you to gain a rough estimate of the number of pages. You can jot down the number of pages returned for each query as you run multiple searches. Note, you might need to run several advanced queries to hunt down problematic page types across a site. It can be a bit time-consuming, and you might get flagged by Google a few times (by being put in a “search timeout”), but they can be helpful:

Summary – Using The Data To Build Your Case
We started by surfacing a problematic page type that was supposed to be canonicalized to other pages, but was being indexed instead (since the pages didn’t contain equivalent content). Google just wasn’t taking the hint. So, we decided to hunt down that page type to estimate how many of those urls were indexed to make a case for prioritizing the problem.

Between GSC, GA, SEMrush, and advanced query operators, we can roughly understand the number of pages that are indexed, while also knowing if some are ranking well in Google and driving traffic. In the real-world case I just worked on, we found over 35K pages that were lower-quality and indexed. Now my client is addressing the situation.

By collecting the necessary data (even if some of it is rough), you can tell a compelling story about how a certain page type could be impacting a site quality-wise. Then it’s important to address that situation correctly over the long-term.

I’m sure there are several other ways and tools to help with understanding an indexing problem, but this process has worked well for me (especially when you want to quickly estimate the numbers). So, if you ever run into a similar situation, I hope you find this process helpful. Remember, rel canonical is just a hint… and Google can make its own decisions. And that can lead to some interesting situations SEO-wise. It’s important to keep that in mind.

GG

I was helping a company a few months ago that got hit hard by recent algorithm updates. When digging into the audit, there were a number of problems that were surfaced, including content quality problems, technical SEO problems, user experience issues, and more. From a content quality perspective, the site had an interesting situation.

Some of the content was clearly lower-quality and needed to be dealt with. But they also had a tricky issue to figure out. They target a younger audience and a lot of the content was long-form. And I mean really long-form… Some of the articles were over three thousand words in length. I asked my client if they did any type of user testing in the past to determine if their target audience enjoyed their long-form content or if they wanted shorter and tighter articles. It ends up they never ran user testing and didn’t really know their audience’s preference for content length. Like many site owners, they were pretty much guessing that this was the right approach.

The site owner said, “I wish there was a way to determine how far people were getting into the content…” That’s when I responded quickly with “you can do that!” Google Analytics is a powerful tool, but many just use the standard setup. But if you leverage Google Tag Manager, you can set up some pretty interesting things that can be extremely helpful for understanding user engagement.

That’s when I recommended using a three-pronged approach for helping identify user engagement, content consumption, etc. I told my client we could triangulate the data to help identify potential problems content-wise. And the best part is that it doesn’t take long to set up and we used one of the most ubiquitous tools on the market – Google Analytics (with the help of Google Tag Manager).

Note, there’s nothing better than running actual user testing. That’s where you can watch users interacting with your site, receive direct feedback about what they liked or didn’t like, and more. It’s extremely powerful for truly understanding user happiness. But that shouldn’t stop you from leveraging other ways to understand user engagement. What I’ll explain below can be set up today and can provide some useful data about how people are engaging with your content (or not engaging).

Triangulating The Data
The three analytics methods I recommend using to help identify problematic content include Scroll Depth Tracking, Adjusted Bounce Rate (ABR), and then Average Time On Page. I’ll go through each of them below so you can get a feel for how the three can work together to surface potential issues.

Note, there isn’t one metric (or even three) that can 100% tell you if some piece of content is problematic. It’s part art and part science. There are times you can easily surface thin or low-quality content (like thousands of pages across a site that were mistakenly published containing one or two lines of text). But then you have other times where full articles need to be boosted since they are out of date or just not relevant anymore.

Therefore, don’t fully rely on one method to do this… Also, it’s not about word count, it’s about value to the user. Google’s John Mueller has explained this several times over the past few years. Here’s a post from Barry Schwartz covering John’s comments where he explains that it’s about value versus word count. Here’s the tweet that Barry is referring to:

I agree with you & Mihai :). Word count is not indicative of quality. Some pages have a lot of words that say nothing. Some pages have very few words that are very important & relevant to queries. You know your content best (hopefully) and can decide whether it needs the details.

— John (@JohnMu) July 24, 2018

And once you identify potential issues content-wise and dig in, you can figure out the best path forward. That might be to enhance or boost that content, you might decide it should be noindexed, or you might even remove the content (404).

Scroll Depth Tracking
In October of 2017, Google Tag Manager rolled out native scroll depth tracking. And the analytics world rejoiced.

Using scroll depth tracking, you could track how far down each page users were going. And you have control over the triggers percentage-wise. For example, you could track if users make it 10, 25, 50, 75, and then 100 percent down the page. And then you could easily see those metrics in your Google Analytics reporting. Pretty awesome, right?

For my client, this alone was amazing to see. Again, they wanted to make sure their core audience was reading each long-form article. If they saw that a good percentage of users stopped 25% down the page, then that wouldn’t be optimal… And if that was the case, then my client could adjust their strategy and potentially break those articles up and craft shorter articles moving forward.

I won’t cover the step-by-step instructions for setting up scroll depth tracking since it’s been covered by many people already. Here’s a great post from Simo Ahava on how to set up scroll depth tracking via Google Tag Manager.  It doesn’t take long and you can start collecting data today.

Here is a screenshot of the tag in Google Tag Manager when I set this up. Just remember to set Non-interaction hit to true so scrolling events don’t impact bounce rate. We’ll use Adjusted Bounce Rate (ABR) to address that instead:

And here are two example of scroll depth tracking in action. The first is a post where many readers are engaged and a good percentage are making their way down the page. Note, the values are events and not sessions or users. That’s important to understand. Also, the screenshots below are from two different sites and each site owner has chosen different scroll depth thresholds.:

And on the flipside, here’s a piece of content where many aren’t making their way down the page. It’s a lower quality page that isn’t seeing much engagement at all. There’s clearly much less traffic as well.

Adjusted Bounce Rate (ABR)
Ah, an oldie but goodie. In 2014 I wrote an article about how to set up Adjusted Bounce Rate (ABR) via Google Tag Manager. You can check out that post to learn more about the setup, but ABR is a great way to get a stronger feel for actual bounce rate. My post explains the problems with standard bounce rate, which doesn’t take time on page into account. So standard bounce rate is skewed. ABR, on the other hand, does take time on page into account and you can set whatever threshold you like based on your own content.

For example, if you write longer-form content, then you might want to set the threshold to longer (maybe a minute or longer). But if you write shorter articles, then you might want to set the ABR threshold to shorter (like 30 seconds or less). Once the time threshold is met, Google Analytics will fire an event causing the session to NOT show up as a bounce (even if the person only visits one page).

It’s not uncommon to see Bounce Rate in Google Analytics drop off a cliff once you implement ABR. And that makes complete sense. If someone visits your article and stayed on the page for six minutes, then that shouldn’t really count as a bounce (even if they leave the page without visiting any other pages). The person was definitely engaged. Here’s what that drop looked like for a client that implemented adjusted bounce rate this summer:

Here is an example of two highly-engaged posts of mine about major algorithm updates. The adjusted bounce rate is just 12% for one and 13% for the other. Many people visiting these pages spend a lot of time reading them. So even if that’s the only page they read, it shouldn’t be counted as a bounce.

So, now we have two out of three metrics set up for helping gauge user happiness. Next, I’ll cover the third, which is Average Time On Page (a standard metric in Google Analytics). When combining all three, you can better understand if visitors are staying on a page for a certain amount of time, how far they are scrolling down the page, and then how long they are staying overall on that page.

Average Time On Page
I find there’s a lot of confusion about time metrics in Google Analytics. For example, why Average Time On Page might be one time, and then Average Session Length is shorter than that. How can that be? Well, Mike Sullivan from Analytics Edge wrote a post about this a while ago. I recommend reading that article to get a feel for how the metrics work. In a nutshell, Average Time On Page excludes bounces (one-page visits). That’s because Google Analytics needs a page hop to calculate how long somebody remained on a page. Remember, we are setting up scroll depth tracking to be a non-interaction hit, so it won’t impact bounce rate or time metrics

Therefore, Average Time On Page will tell you how long users are staying on a piece of content when they visit another page on the site. Sure, it excludes bounces (so it’s not perfect), but it’s still smart to understand time on page for users that click through to another page on the site.

As an example, here’s my post about the August 1, 2018 algorithm update. The Average Time On Page is 12:48 for the responsive page and 17:52 for the amp version. In web time, that’s an eternity.

And on the flip side, here’s a page from a different site that needs some help. It hasn’t been updated in a while and users are identifying that pretty darn quickly. Even for people that click through to another page, the Average Time On Page is just 0:55. That’s a major red flag for the site owner.

Some final tips:
Now that I’ve covered three methods for better understanding user happiness and engagement, I wanted to cover some final tips. Once you are collecting data, you can slice and dice the information in Google Analytics several ways.

• First, review all three metrics for each piece of content you are analyzing. If you find high adjusted bounce rate, low scroll depth, and low average time on page, then there’s clearly an issue. Dig in to find out what’s going on. Is the content old, is there a relevancy problem based on query, etc.?
• You might find content where scroll depth looks strong (people are scrolling all the way down the page), but adjusted bounce rate is high. That could mean people are quickly visiting the page, scrolling down to scan what’s there, and then leaving before your ABR time threshold is met. That could signal a big relevancy problem.
• You can use segments to isolate organic search traffic to see how users from Google organic are engaging with your content. Then you can compare that to other traffic sources if you want.
• You can also segment mobile users and view that data against desktop. There may be some interesting findings there from a mobile perspective.
• Heck, you could even create very specific segments to understand how each one is engaging with your content. For example, you could create a segment of female visitors ages 18-34 and compare that to male users. Segments in Google Analytics can be extremely powerful. I recommend reviewing that topic in detail (beyond just what I’m covering today with scroll depth tracking, ABR, etc.)

Summary – Using Analytics As A Proxy For User Engagement, User Happiness, and Content Quality
I always recommend conducting user studies to truly find out what your target audience thinks about your content. You can watch how they engage with your content while also receiving direct feedback about what they liked or didn’t like as they browse your site. But you can also use analytics as a proxy for engagement and user happiness (which can help you identify content quality problems or relevancy issues).

By combining the three methods listed above, you can better understand how users are engaging with your content. You’ll know if they are staying for a certain amount of time, how far they are scrolling down each page, and then you’ll see average time on page (excluding bounces). It’s not perfect, but it’s better than guessing.

And once you collect the data, you may very well choose to refine your content strategy. And the beautiful part is that you can start collecting data today. So go ahead and set up scroll depth tracking and adjusted bounce rate. Then combine that with average time on page so you can use the three-pronged approach I covered in this post. Leverage the power of Google Analytics and Google Tag Manager (GTM). You never know what you’re going to find.

GG

 

I help a lot of companies with website redesigns or CMS migrations (from an SEO standpoint). As part of that process, it’s extremely important to download all of a website’s top landing pages so you can check them in staging and when the redesign or migration goes live. By doing so, you can test the pages to ensure they resolve correctly before you pull the trigger, and then right after you launch. Basically, you can nip serious SEO problems in the bud by crawling those urls.

I also help a lot of large-scale websites with redesigns or migrations. For example, sites with 500K, 1M, or more pages that need to be downloaded, crawled, and analyzed. For situations like that, it’s not simple to export the top landing pages from Google Analytics. Unfortunately, you cannot export urls in bulk from the UI. You are limited to 5K urls per report. Then you would need to click to the next page in the pagination to retrieve the next 5K rows. That’s not efficient, to say the least.

You could also tap into the GA API, but you need programming skills. That’s unless you leverage a tool or plugin that has the functionality built for you. More on that soon. :)

The GA rowCount Hack is Gone
There used to be a hack in Google Analytics that let you manually set the row count in your reports via the url in your browser. I loved that feature, since it enabled me to get around the issue I mentioned above. For example, I could add rowCount%3D15000 in the url, and boom, I had a report with 15K rows of data.

Well, Google Analytics removed the ability to do that and now the most you can get in one batch is five 5K rows. Sure, you can click to the second page of results and keep exporting, but that gets old quickly. Exporting up to 5K pages is fine for some smaller sites, but not if you have hundreds of thousands of pages, or more.

So, wouldn’t it be great if there was some plugin or add-in that could help with this? Well, there is. Read on!

Analytics Edge for Excel
While researching options last year, I came across an Excel add-in called Analytics Edge. The add-in is packed with functionality, including the ability to export pages from Google Analytics with ease. And not just export pages, but export them in bulk! That’s exactly what I was looking for.

Again, the plugin can do many things for you, but I’ll just focus on exporting top landing pages from Google Analytics. If you are going through a redesign or migration, and want to export all of the top landing pages from organic search, then this could be a great solution for you. It has worked extremely well for me.

Note, there is a free version of the add-in that contains the Google Analytics connector. You can also pay more for the “Core” add-in, which gives you even more functionality. The screenshots below are from the core add-in, so you might see slightly different options with the free version. Now let’s get started.

How To Export Top Landing Pages From Google Analytics (Beyond 5K)

1. Download and install the Analytics Edge add-in for Excel. Then register the add-in to get started.

2. Click the Analytics Edge menu at the top of the worksheet to access the various options available to you.

3. Click “Free Google Analytics” in the menu and then “Accounts”. You will need to connect the add-in to your Google account so you can access various properties and views in GA. You should enter the account that’s tied to your Google Analytics properties and views. Note, you can add several Google accounts for future access, if needed.

4. Next, click “Free Google Analytics” and then “Analytics Reporting”. A small macro box will show up, which will capture the commands you are using via the add-in. Simply name your macro to move on.

5. Here’s where the magic takes place. This is where you will choose the settings for your new report. First, select a login and then a view. The options will match the Google account(s) you entered previously.

6. Next, choose the dimensions and metrics you want to see in your report. For this example, I’ll keep it simple. We’ll choose landing pages, sessions, and bounce rate.

7. Instead of clicking finish, let’s select the Dates tab. There are some presets here like “Last 30 Days”, but you can enter a custom date as well. I’ll choose “Last 30 Days” to keep this simple.

8. If you click the “Sort” tab, you can sort by sessions (which is typically what we would want for top landing pages).

9. Last, but not least, we want organic search data for this example. So head over to the “Segments” tab and select “Organic Traffic” in the list. This will limit the data to landing pages from organic search.

10. Then click Finish. Analytics Edge will begin pulling the data from Google Analytics. Note, for larger sites, this could take a while. Be patient. You’ll notice the plugin says, “Running…” and you might see the Query Status window showing the number of rows downloaded.

11. Once completed, you’ll see a “Step Results” worksheet filled with data. You’ll also notice there are only 50 records in the sheet. This is where most people freak out and think the plugin screwed up. It didn’t. It’s simply showing you a sample of the data that was pulled. If all looks ok, then you need to write the data to a new worksheet.

12. To write the data to a new worksheet, you need to click “File” in the Analytics Edge menu, and then “Write Worksheet”. You can name your new worksheet whatever you like and then click OK. Once you do, you’ll notice a new worksheet with all of your data. For the view I used, I retrieved 98K top landing pages from organic search over the past 30 days. Awesome.

Bonus: The only thing left to do here is to use the concatenate function to build the full url. Once you do, you can export that list and crawl it via Screaming Frog, Deep Crawl, etc. You can read more about checking top landing pages in a recent post of mine. There are some helpful tips in that post once you have a final set of urls.

Summary – Breaking Through The 5K Limit
For those of you that are working on large-scale sites, the process I documented above can help you export many more urls than you could via the UI in Google Analytics. By using an Excel add-in like Analytics Edge, you can export hundreds of thousands of urls, if needed (and all in one shot.) That will save you time, and your sanity.

So gather your intel now. Then crawl away. It’s a great way to check urls in bulk when going through a redesign or CMS migration. Good luck.

GG