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
-
My "tag" pages are showing up as duplicate content. Is this harmful?
Hi. I ran a Moz sitecrawl. I see "Yes" under "Duplicate Page Content" for each of my tag pages. Is this harmful? If so, how do I fix it? This is a Wordpress site. Tags are used in both the blog and ecommerce sections of the site. Ecommerce is a very small portion. Thank you. | |
Moz Pro | | dlmilli1 -
Search Volume in Keyword Rankings Page
Is there a way to show search volumes for keywords in the Keyword Rankings tab page? A lot of times keywords we are ranking for are added to our campaign, and it would be very useful to see the search volumes within that grid. That way we could sort by the search volume and determine which keywords are worth targeting and optimizing for. I've had this ability with other software in the past and would be a great option for either Moz Pro version or the new Beta that is coming out. Which I'm still waiting for my invite BTW:)
Moz Pro | | agencypromogroup0 -
What do you use for site audit
What tools do you use for conducting a site audit? I need to do an audit on a site and the seomoz web crawler and on page optimization will takes days if not a full week to return any results. In past Ive used other tools that I could run on the fly and they would return broken links, missing htags, keyword density, server information and more. Curious as to what you all use and what you may recommend to use in conjunction with the moz tools.
Moz Pro | | anthonytjm0 -
How do I retrieve crawl and ranking data about a site from the past?
Hey. One of my main clients has asked to see the crawl data and rankings data for the past eight months. He wants to have tangible evidence of the effects of Penguin. I would like that info too. Is it possible to retrieve that information on a weekly crawl and ranking basis through SEO Moz and if so, how do you do it? I simply want to show a graph, timeline and brief explanation across several main keywords... Help me as you guys always do - You rock Best Ben
Moz Pro | | creativeguy0 -
Data from Open Site Explorer to Excel
Hello, Im having a problem with the data I pull off from Open Site Explorer. Everytime I download a report in CSV, when I open it in Excel 2007, all the information is like this: http://i.imgur.com/rwMxO.png What can I do to extract the information exactly as it appears in the Open Site Explorer with all the fields in the right place? Tks guys, Regards, Pedro Pereira [](<a href=)" target="_blank">a> rwMxO.png
Moz Pro | | PedroM0 -
Link Diagnosis and Open Site Explorer
I work at a web design firm that's starting to offer SEO to its clients. They want to keep costs down until the SEO side becomes more established, so I've been using mostly free tools to do the SEO. I've been using a website called LinkDiagnosis.com to check backlinks for clients. It says the data is provided by SEOmoz, so I thought it must be pretty reliable. However, I just signed up for the PRO trial to test out all of SEOmoz's tools (& hopefully convince my company to sign us up), and I find that the link information between the 2 tools can be vastly different! Sometimes the number of unique linking domains is very close, but other times it can be off by hundreds, even thousands, with Open Site Explorer typically providing the higher numbers. Is the Link Diagnosis tool really powered with data from SEOmoz, or does it just receive a portion of the data, since it's a free tool? I'm just trying to figure out what will be the most reliable solution for me to keep using, and the discrepancies between the two tools has caused me to question the reliability of both. Thanks, Hector
Moz Pro | | hmunoz0 -
Site metric tool
Is there a tool on here (or anywhere else) where you can upload a list of sites (say 500 sites) and be given things like mozrank on it?
Moz Pro | | thefresh0