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.
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
-
My index URL was removed from Google, but all others remain in the search engines
HI All, My site was ranking very well and was in 1st page of google for most of my keywords. Couple of weeks back we did some update to the site and moved it to new hosting and from then onwards I dont see my site home page in Google ranking . My Website Name is : royalevents.com.au. It used to be in 1st of Google for keywords like wedding Mandaps, Indian Wedding Mandaps etc, Would be great if some one helps us to figure out whats gone wrong .. I also did Webmaster Fetch as Google but nothing happened. Thanks
Moz Pro | | Verve-Innovation0 -
Is it possible to block Moz from crawling sites?
Hi, is it possible to stop Moz from crawling a site at the server level? Not that I am looking to do this or anything, but here's why I'm asking. I have been crawling a site that is managed (currently by 2 parties), and I noticed that this week pages crawled went from 80 (last week) to 1 page!! I know, what? See my image attached... and the issues all went to zero "0"....! So is it possible that someone can't prevent Moz from crawling the site at the server level? I checked the robots.txt file on the site, but nothing there. I'm curious. dYNUwjd.jpg
Moz Pro | | co.mc0 -
No data in Moz
Hello, Since last tuesday there has been no data in Moz anymore. The error code for the homepage says: '608 : Page not decodable as specified Content-Encoding:' I checked this list http://moz.com/help/pro/why-can-t-rogerbot-crawl-my-site but it doesn't tell me more.
Moz Pro | | MarcelMoz
Hopefully anyone can help me. Thanks, Marcel0 -
Not provided Data in SEOMoz reports
Hi How do SeoMoz reports deal with 'not provided' data I see my total visits from organic search for a month are same as total of both my branded and non branded keyword traffic combined yet GA is reporting 157 visits from non provided data so is SEOMoz being very clever and finding a way to decipher this not provided data and allocate it accordingly in the reports ? Or if not what ? Many Thanks Dan
Moz Pro | | Dan-Lawrence0 -
Search Traffic Declining
Hello, My search engine traffic has been declining recently - I saw one decline last August and another this past April. I'm trying to figure out what caused the decline and would love advice on where I should start digging. Any recommendations would really help me! Thanks in advance. Jodi momsfavoritestuff.com
Moz Pro | | JodiFTM0 -
How do YOU use site explorer?
I normally use open site explorer to identify links that competitors of my clients have and sometimes this gives me what I call 'some low hanging fruit' to go after. (and of course links that are more challenging to get) I don't know why this didn't occur to me sooner. If my client is a chiropractor why not look at the links for 50 or 100 of the top rankings chiropractic sites all over the US? This would HAVE to uncover a wealth of blogs to comment on that have good authority, great industry associations, publications, forums - a whole wealth of items. It made me wonder how many people use site explorer like I have been (top 3-4 competitors that your client has) or identifying links pointing to LOTS of competitors? How do you use it? Couldn't you almost base an entire link building campaign using OSE? Why would this be a bad idea if not? Just some random thoughts. THE WEEKEND IS ALMOST HERE - Have a great day everybody! 🙂
Moz Pro | | Mrupp441 -
Changing the Timeframe of Historical Crawl Data
Hello, Just read a great post about the implications of duplicate content for sites after the most recent Panda update: http://www.seomoz.org/blog/duplicate-content-in-a-post-panda-world?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+seomoz+(SEOmoz+Daily+Blog) In the post is an image or crawl data history that shows months, not days or weeks, worth of trending data as it relates to duplicate content. So my question is this: How do I change my view/date range on my own campaigns so that I can view the trailing months of data rather than only what seems to be the past 4 weeks or so? This would really help me identify the impact of some on page changes we've recently made for a client. Many Thanks, Jared
Moz Pro | | surjm0