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
-
Major site overhaul
Hi guys and girls After reading all the content It became apparent that our website is a bit of a dog. I'm about to update our site with a new theme and content on moz.com I've found myself becoming so much more confident at all this. I've read all the material I can on Moz re: Site migration but still have one question. How do I find out all the links that I need to 301 before I make the new site live? Please don't worry about patronizing me! I'm really new at this! Ben
Moz Pro | | SussexChef830 -
Site Crawl Error
In moz crawling error this message is appears: MOST COMMON ISSUES 1Search Engine Blocked by robots.txt Error Code 612: Error response for robots.txt i asked help staff but they crawled again and nothing changed. there's only robots.XML (not TXT) in root of my webpage it contains: User-agent: *
Moz Pro | | nopsts
Allow: /
Allow: /sitemap.htm anyone please help me? thank you0 -
Estimating Search Volume An Impossibility?
I use Moz's handy Keyword Difficulty Tool to get a relative feel for difficulty and traffic and specif competition. At the same time, my experience is that a term may show a local (U.S.) search volume of, for instance, 30 visits and in the end produce hundreds of visits for position #3. This is of course accomplished through the miracle of all the other searches the page may be judged by Google to be relevant for. Hundreds at times and some times few if any. Here is my two part question: What tools or steps do you use to get a better handle on this on the front side of going for a term? What tools or steps do you use to broaden the meaning to related terms/searches over time? Thanks... Darcy
Moz Pro | | 945010 -
How could I improve my "brand" and "mentions" using the Moz tool?
Hello I am a bit stuck on what to put into this section of MOZ. As you can see from the attached image I have setup to catch any mention of my business name and root url. But beyond that I am uncertain of what to use to monitor who is talking about us. I'm also uncertain of what other abilities or usefulness this function would have. aOq7wVg.png
Moz Pro | | infinart0 -
Accurate Google Keyword Ranking for a domain
Hi Folks, SEO N00b here, in a new job so please go easy on me if i ask stupid questions. I'm looking for some clarification on Google Keyword rankings. I am looking for accurate Google Keywords rankings as when I log into Google Webmaster Tools I am given values like (6-10) for a ranking? When I used SEOMoz (Research Tools -> Keyword & Ranking -> Rank Tracker) it gives me an even less accurate range by simply saying >#50?Why is SEOMoz giving a completely useless metric? Why is its actual rank not shown? I know rankings are becoming less useful as a metric with Google moving toward personalized search but I still have reports to deliver to clients so the conversation of what metrics are going in the report is to be had another day. Right now I just want to get accurate rankings, from what I've found online Google Webmaster tools is recommended as the de-facto ranking tool but it doesn't provide accurate results so how can that be (yes I know it averages out the ranking but 6-10 is a range of values not a single value that represents an average) Dave
Moz Pro | | icanseeu0 -
Google Local results ranking
Are Google Local position results included in Moz ranking reports? If not, how can these be tracked and monitored? Is a Google Local result likely to achieve a similar CTR as previous organic results? Some of our previous good organic search rankings seem to have been replaced by Google Local results for our restaurant and hotel clients. Is this likely to achieve the same traffic and if not, is there a way to get back to organic results? Thanks!
Moz Pro | | ignitehospitality0 -
How do you search for mentions of your brand without searching within your own site
We have a social network site and we monitor mentions. However, when we search we are finding content from our site being included. How do i filter the content to exclude my site.
Moz Pro | | seoworx1230 -
Company Name in Page Title creating thousands of "Duplicate Page Title" errors
I am new, and I just got back my crawl results (after a week or more). The first thing I noticed is that the "duplicate page title" is in the thousands, my urls and page titles are different. The only thing I can see is that our company name is at appended to the name of every title. I did search and found one other person with this problem, but no answer was given. Can anyone offer some advice? This doesn't seem right... Thanks,
Moz Pro | | AoyamaJPN0