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
-
Magento Site "Title Missing or Empty"
Hi everyone . . ..bear with me as I am "noob". My moz analysis brought up a few "critical" issues, one of which was a missing or empty title for a page. That page is: http://www.thirdcoastsigns.com/sales/guest/form Which does not even appear to be a working page . . . so I'm at a bit of a loss ow to fix this. I suspect it's a little bit of SEO expertise and a little bit of Magento expertise. Thoughts and suggestions welcome! P.S. Look forward to my NEXT noob question about a bunch of pages I have flagged as "Pages with temporary redirects". Stay tuned!
Moz Pro | | damon12120 -
Listing of all Google Indexed Pages
I started managing a site that has about 391,000 indexed pages. I want to get to the bottom of why there are so many in preparation for a ecommerce Migration and improving SEO. Anyone know of a tool? Many tools I have came across can only take 100 at a time. I would love to get them in excel or a database. I look forward to the suggestions.
Moz Pro | | dhands170 -
Open site explorer
"Unable to retrieve linking pages on this anchor at this time." This is the notice I get when trying to see links for anchor text. Can someone help?
Moz Pro | | Joseph-Green-SEO0 -
Grade F pages - all first in Google
Any anyone explain what value the Grade pages are if, as in my case, I have 9 Grade F pages of which 4 are in first position on Google, 1 is in second and the rest are 4th! I understand why SEOM might be flagging them as Grade F (keyword use in tags etc), but it seems it must be looking at the wrong clues if it still thinks that its recommendations are the most relevant to improving position. These are not obscure terms (Spy devices, electronic surveillance, spy systems, surveillance systems) for a UK site (www.spycatcheronline.co.uk) I appreciate that the points highlighted may need to be addressed, but clearly the pages/site are doing something right to come first - does this not devalue the whole Grade system on SEOM? Charles.
Moz Pro | | MrFrisbee1 -
Google Places have been entirely replaced by new Google+
Read this! http://searchengineland.com/google-places-is-over-company-makes-google-the-center-of-gravity-for-local-search-122770 http://www.nydailynews.com/news/national/review-google-adds-zagat-reviews-local-article-1.1086985 Any ideas? How's your Google+ circle look like? What will happened to our SEO ranking? IS Rogger MOZ is ready for that change? What about SEOmoz will give us some Local/Pages/Google+ tools?
Moz Pro | | Elchanan0 -
Changing linked analytics account
When setting up a new campaign, I attempted to add GA. I entered the wrong GA account to this profile. How do I change the GA profile that is linked to this campaign?
Moz Pro | | sgavin0 -
"Special pricing" on SEO DVDs?
The section for buying your dvd training lists the price for everyone at $119 and the special price for pro members at - $119. Then the text says that the special price will be shown when the item is added to the cart where the special price is - $119. Am I missing something here?
Moz Pro | | IanTheScot1 -
"Opt Out" of free month through MozPoints
This is probably going to sound a bit strange but I'd like it if I could "opt out" of the 200 MozPoints in a month giving a free months worth of membership. The reason being that I am enjoying using the forum but feel like every time I click on a thumbs up for my stuff to get MozPoints, it's like I'm trying to take advantage of the offer... when in actual fact I have no interest in that. Don't get me wrong, I want MozPoints because I want an SEOmoz t-shirt lol, and maybe one day I'll get to find out what the "something really special" is (I don't want to have one of whatever it is, I just want to know what it is.... the curiosity is killing me!) Anyway, what I'm saying is that I would feel more free to use the forums without having this kind of uncomfortable feeling that it may look like I'm just in it for the points to skimp on cash lol. I use the service, I love the service, and so I am happy to pay for the service... not only that but the forums are part of the benefit of the service, so I'd rather not worry that every comment I make may look like I'm doing it for reasons of greed, rather than reasons of interest. So, can we have a thing where we opt out?
Moz Pro | | SteveOllington2