How to sift "site search" data from Google Analytics for trends
-
I apologize in advance if this has been asked a million times but I'm just not able to find anything on it for some reason. Probably the words "site" and "search" come up a lot in this area... Anyhow, my question:
How do I find trends in "site search" data from Google Analytics?
I set up "site search" a long time ago. I have thousands and thousands of searches people have made on my site logged and squirreled away. The plan was to review them on a weekly basis, find the trends and start writing content to address interests people seem to be having but not finding on our site. Sounded great at the time.
The problem I have, of course, is that among my 10,000 searches (many shown in Google Analytics as "no-results:cats and dogs", etc), there are slight differences that make it difficult to total up search trends. Let's say the list is like this:
Term | Search Count
Cats | 500
Dogs | 500
Cat | 250
Dog | 250
Cat food | 5
Dog food | 5
Birds | 1
Bird | 1
Cats are great | 1
Cats are really great | 1
Dogs are great | 1
I like birds | 1
Seriously, I like Cats | 1
Turtles | 1... 10,000 more entries, every single one only 1 search per term.
OK, so it looks like people like Cats and Dogs a lot, but also Birds and Turtles. But maybe there are snake searches. Maybe there are "cat pajamas" searches and variations on all of the above. Who knows what else is really trending in there??? The review of this data is MIND-NUMBING. Especially when you get into plurality and misspellings, this rabbit hole has no bottom.
Is there a tool people in the SEO jam use to take a big ole CSV dump and have it magically sorted by at least potential trends?
I mean, there's gotta be, right? And I'm silly for not already knowing what it is.
-
Hi Thom,
Like Mike mentioned, I think that culling through this information yourself with Excel will probably be your best bet for analyzing the data you mentioned.
I'll give you a few ideas on how I would start if I was you:
You mentioned that many of the queries were only searched once. I would start by organizing your information into two columns: one with the query, and one with the search volume. Then, I would filter those down to only queries that have been searched more than [a certain threshold]. I think that while queries that have only been searched once can give you valuable information, especially compared to others and analyzed in aggregate, you will be less overwhelmed if you start with the most important (and a smaller set of) queries first.
After you've done that, I would start to filter your data by query. You can sort by queries that contain, begin with, end with, or do not contain a term, or any sort of custom filter. Given your example, I would probably start with filtering by queries that contain the word "cat" or "dog" or "turtle," and then try to find trends within that smaller set of data. They will be much easier and less overwhelming to find when you are not dealing with so much data.
This next step is a little more tedious, but I would also recommend adding another column that you manually populate. You mentioned that there are misspellings; you could account for those by creating another column in which you populate the word "cat" for all queries that contain "cat" and also all queries that contain "cta" or any other misspelling. That way, later, you can easily sort by keywords that were about cats, but didn't necessarily contain the exact word "cat." Or, you could populate the column by grouping all queries that were about one animal, two animals, three animals, etc.
Once you have a ton of information, I would recommend creating pivot tables and charts that help you compare the data.
Ultimately, what I'm recommending is probably very time intensive and tedious, but I really think it will be rewarding once it's completed, because you will very thoroughly understand the trends going on in the data. One of the benefits of doing it yourself, rather than using a tool, is that you will understand the context around these words; you will be able to better interpret trends or make connections than an automated tool would be.
Here is a great article that walks you step by step through some of the steps I mentioned, and it also goes into depth about other ways you can use Excel to analyze this kind of data: http://www.sitevisibility.co.uk/blog/2015/09/11/4-excel-tips-applied-to-keyword-research/
Hope this helped!
-
Honestly I do it all by hand in excel using conditional formatting to highlight various core terms and change text color/bold/italicize for certain common combinations that accompany core terms. Then I sort by those variations, copy/paste them into new tabs, and break them down further as needed from there.
Got a burning SEO question?
Subscribe to Moz Pro to gain full access to Q&A, answer questions, and ask your own.
Browse Questions
Explore more categories
-
Moz Tools
Chat with the community about the Moz tools.
-
SEO Tactics
Discuss the SEO process with fellow marketers
-
Community
Discuss industry events, jobs, and news!
-
Digital Marketing
Chat about tactics outside of SEO
-
Research & Trends
Dive into research and trends in the search industry.
-
Support
Connect on product support and feature requests.
Related Questions
-
Site Not Cached By Google
Hi My name is Apoorwa and i have my own website, My website is not cached by google, Why is this problem happening with my site.can somebody help me please? its urgent....this is my site - http://www.holifestival.org/Please assist me.......
Moz Pro | | Packersmove0 -
Page with "Missing Title Tag" isn't a page
Hello, I am going through the various errors that the Moz Pro Crawl report and some non-existent pages keep coming up in the report. For example, one error category is "Missing Title Tag" with one page identified. But this page http://www.immigroup.com/news/“http%3A/crs.yorku.ca”?page=2 isn't real. It would have been a 404 were there not a redirect for everything that is /news/gobbledygook to /news. So my question is: when moz (or GA for that matter) identifies these pages as "real" and having errors, do I need to take this seriously? And what do I do about it? Thanks! George
Moz Pro | | canadageorge0 -
Moz vs google data conflict?
Hi there, I am doing an SEO site audit for a client(giveaway, and here is the problem: when performing site:domain.com on google --> 13,800 pages were found When I see this number it seems to be a bit too much compare to the links i checked on integrity(link check for broken links) which gave me a result of 1291. I digged in more into the Google results and saw hundreds(maybe thousands) of pages that are blocked by robots.txt. So I am thinking, ok this is it, thousands of pages can't be crawled by the search engines. Here is the big BUT though, then I check at my moz crawl (see attachment) and no pages are blocked by the SEs, and then look at the dups, only 23 recorded?? Is Moz not crawling properly the 13,800 results that google finds or is this some magical phenomenon happening here? I am really confused here that is why I need some help here! Thank you guys! A990Hu4.png k842AOn.png
Moz Pro | | Ideas-Money-Art0 -
Working with Open Site Explorer
Hi everyone, I'm new to keyword analysis, and am in the process of consuming a lot of SEOmoz articles and resources on the subject. I wanted to see if I'm correct in my analysis of two compared sites, and hope you can shed some light on the matter. I've been to the Google Keyword Tool and looked for my informational keywords for the project I'm working on, since the user intent is all about information. A not-so-great keyword phrase I've found with 12,100 local monthly searches is: "programa de inglês" (english programme) I'm just using this as a quick example. I have performed a Google search query for the above phrase from google.com.br (Brazil), and I'm comparing the #2 and #4 results from the 1st page of the SERPs which are: #2) www.programa-ingles.net and #4) http://www.baixaki.com.br/categorias/educacao-e-diversao.htm. What's confusing me is that in Open Site Explorer, the #4 result gets a much higher page authority compared to the #2 result, and beats #2 on every category except for internal–external link ratio and all the social categories. Here's an image attached of the comparison. Is it the fact that the external links of #2 account for 100% of the links pointing to it, or that the #2 position beats (rather pitifully) #5 on social sharing, or is it something that I've not stumbled across yet? Thanks in advance for helping out a n00b. 6HU6hBi.png
Moz Pro | | featherseo0 -
"Too many on page links" phantom penalty? What about big sites?
So I am consistently over the recommended "100 links" rule on our site's pages because of our extensive navigation and plentiful footer links (somewhere around 300 links per page). I know that there is no official penalty for this but rather that it affects the "link juice" of each link on there. I guess my question is more about how places like Zappos and Amazon get away with this? They have WAY over 100 links per page... in fact I think that Zappos footer is 100+ links alone. This overage doesn't seem to affect their domain rankings and authority so why does SEO moz place so much emphasis on this error?
Moz Pro | | kida12meyer0 -
Open site Explorer Findings??
On OSE, I am comparing two websites(A and B) and one has a page authority higher than the other (35(A) vs 36 (B)), but all the linking metrics are in favor for website A except for the linking C block (A has 11 and B has 12). The website B is ranked in page 1 for couple competitive keywords whereas the website A don't show up in the search at all. Why is that? Does B have better content and is more kw focused?
Moz Pro | | Ideas-Money-Art0 -
"Rank Tracker Tool" is not agreeing with "Keyword Difficulty Tool"
I usually don't sweat a lot for ranks and such but last couple of days, our rankings have been moving drastically. 'Rank Tracker' shows 1st position for many keywords and the "keyword difficulty tool" shows 2nd and 3rd positions. Is is just me or this is a common thing?
Moz Pro | | Syed10 -
Open Site Explorer Question!
Hi, I have performed a search on a root domain and the page auth is higher then the domain auth? I would have thought they would have been the same or at least the other way around!
Moz Pro | | activitysuper0