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
-
Increase in Rankings, but search visibility is decreasing
I just started updating my site with the very basics, focus keyword, meta description, and page titles. I see that I am going up in my targeted keywords, however my search visibility has dropped from 3.34% to 2.78%. My assumption is that my keyword choice is a little off, however I see that I have increased in keyword rankings and dropped very little in what I was ranking for previously, plus the keyword volume seems relatively the same. In fact I've added far more keywords than I have dropped. Curious why my search visibility has dropped, however my keyword rankings only seem to increase.
Moz Pro | | kthomasd0 -
Find out what keywords a site is ranking for
I want to find out what keywords a site is ranking for. In other words, I want to find out how Google is currently seeing a site. I've used Google KW tool - not very accurate when you simply type in the URL. Is there anything in SEOMoz I can use? New to this. thanks
Moz Pro | | goodge0 -
Site Not On Google, SEOmoz shows as 43
The site I'm helping with was at one time a page 1, even #1 on page 1. Lots of changes, problems when someone else did something and dropped to page 4 for keyword. After some recent tweaking I did, it no longer shows on rankings. No penalty on Google Webmaster, in Webmaster tools it shows the sitemap processed with no errors. SEOmoz still shows it at #43 in Rankings. Why does SEOmoz see it, but I don't? Site is www.plussizeplum.com (sorry, plus size women's lingerie) and keywords are "plus size lingerie".
Moz Pro | | dlcohen0 -
Google analytics account not shown
At the point where I'm asked to connect my Google Analytics account, it only shows three of my accounts, and not the one I'm trying to connect.
Moz Pro | | skittish430 -
Why is Semoz sending me the wrong data
Hi i am very puzzled, i have just got a report saying about my keywords and the data seems to be wrong.It says that my keyword gastric band hypnotherapy is ranked at 9 but when i check online it is ranked at one. When i put in anger management therapy it says it is ranked at 25 but it is ranked at 10 Can anyone please explain what is going on. the url is www.clairehegarty.co.uk Google UK Keyword Current Rank Change
Moz Pro | | ClaireH-184886
gastric band hypnotherapy 9 5
anger management therapy 25 5
virtual gastric band hypnosis 21 5
gastric band on the nhs 46 > 4
hypnotherapy gastric band 23 3
hypno gastric band 17 3
gastric band hypnosis 16 3
gastric banding cost 50 3
gastric hypno band 32 2
gastric banding0 -
Links not appearing on Open Site Explorer
My site gained several new inbound links during December and only two of them are not all showing up on the latest Linkscape update. It seems to be the links that were created at the end of the month which are showing up, whereas a handful at the beginning of the month are nowhere to be seen. All the linking pages have been indexed by Google the links are do-follow, and one of the sites in particular is not obsure and has a DA in the 90's. I appreciate the Linkscape doesn't index everything, but I would have thought that more tof the results of my efforts would have shown up in OSE. I'd be really grateful if anyone could explain this to me please. Thanks Ben
Moz Pro | | atticus70 -
Campaign data via API
I am currently in the process of building an in house system for managing our clients SEO campaigns. One of the features that we will be including is a customer login where clients can view their reports and site progress. We plan on providing rankings and traffic stats within this which we will collect on a weekly basis to store historical changes. While I could integrate into analytics for traffic and create my own web crawler for rankings, this seems a waste since I already have that data in one central location. Looking through the moz API details it seems to have lots of functions for links but nothing relating to the campaigns. Is there any way or are there any plans to open up campaign data to an API? If I could do this I would certainly upgrade to allow each client to have their own campaign.
Moz Pro | | PPCnSEO0 -
Google analytics sync with SEOMOZ
Hey guys, I'm looking to sync our clients google analytic profiles to our clients campaigns in SEO MOZ. However all our clients google analytic profiles are on the one google account and not separate accounts. So I'm asking does SEO MOZ only allow you to grab the traffic data of one clients profile from a google account instead of multiple profiles from the same google account for different campaigns? If so has anybody got a workaround or any suggestions? If I haven't explained myself clearly let me know and I'll break it down further. Thanks again. Rob
Moz Pro | | daracreative0