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
-
Site Not Cached By Google
Hi My name is Apoorwa and i have my own website, My website is not cached by google, Why is this problem happening with my site.can somebody help me please? its urgent....this is my site - http://www.holifestival.org/Please assist me.......
Moz Pro | | Packersmove0 -
Site explorer Issue
Hello, I'm looking to see in the Site Explorer the links coming from directories such as BOW, yahoo etc. I'm listed there from almost 1 year and these links are not listed, the same with my competitors. I'm missing something? Thank you Claudio
Moz Pro | | SharewarePros0 -
Solution to "Exact Match Domain" issue?
My website bayjobs.com has done well for years, then virtually disappeared from google on September 28, which is the day of the emd (exact match domain) penalty. Meanwhile, similar sites like bajobs.com and bayareajobfinder.com have fared just fine for keywords "bay area jobs". I was previously around #5 for those keywords, but now not even in top 200. Does anyone have any solution for this? Thanks for any insight!
Moz Pro | | UhOh0 -
Lots of site errors after last crawl....
Something interesting happened on the last update for my site on SEOmoz pro tools. For the last month or so the errors on my site were very low, then on the last update I had a huge spike in errors, warnings, and notices. I'm not sure if somehow I made a change to my site (without knowing it) and I caused all of these errors, or if it just took a few months to find all the errors on my site? My duplicate page content went from 0 to 45, my duplicate page titles went from 0 to 105, my 4xx (client error) went from 0 to 4, and my title missing or empty went from 0 to 3. On the warnings sections my missing meta description tag went form a hand full to 444. (most of these looking to be archive pages.) Down in the notices I have over 2000 that are blocked by meta robots, meta-robots nofollow, and Rel canonical. I didn't have any where near this many prior to the last update of my site. I just wanted to see what I need to do to clean this up, and figure out if I did something to cause all the errors. I'm assuming the red errors are the first things I need to clean up. Any help you guys can provide would be greatly appreciated. Also if you'd like me to post any additional information, please let me know and I'd be glad to.
Moz Pro | | NoahsDad0 -
Links from External Sites not Showing on OpenSite or Google Webmaster Tools
Hi, I asked this in another post, but didn't get a solid answer. For several websites I manage, I have access to their Google Webmaster Tools. When I put the site through there, or the link:// or SEOMoz's OpenSite link reporter, I get not even a fraction of the links that I know are pointing to the websites in question. Some of these links are from home pages or side columns, and some are from articles keep within a website, like at Inc.com. I wondered if the links in question were not themselves being indexed, but if I do a very specific search, I can pull them up in the search results. Is it just common knowledge that these link tools are not accurate? And to assume that somehow, search engines really do see all of the inbound links, even if our reporting tools don't. Thanks in advance!
Moz Pro | | lilactree0 -
External Links through Open site explorer
I have just ran an Open site explorer query on our site due to having one of a constant ranking keywords drop, and found that we have 48 new links appear on the report under the anchor text 'home' These are not normal links, and I cannot see where or how these have been connected to our site for example: <colgroup span="1"><col span="1" width="696"></colgroup>
Moz Pro | | hickboy5
| http://regvac.com/TrIsland.swb <colgroup span="1"><col span="1" width="696"></colgroup>
| http://meteonorm.com/fileadmin/tmy3/722066ndry3.rdm | <colgroup span="1"><col span="1" width="696"></colgroup>
| http://www.sexintheshower.net/downloads/SS960-12-pack.psd?ActDo=ShowArt&Information_ID=2396&Parent_ID=&type=&Langue_ID=An&rubID=3068473 | My question is how and why are these appearing on the seomoz open site explorer report? What are they? How can I get them removed, and let google know this was nothing to do with us? Thanks S |0 -
What happened to OSE/Linkscape data?
Can any member of the Moz team or of the community comment on the most recent OCE/linkscape update as of 09/08/2011. It appears that the link data capture for one of our platform is dramatically different than previously and that there are some huge discrepancies? Any update on the change would be appreciated as we use all these updates as benchmarks. Thanks
Moz Pro | | OlivierChateau0