How to identify rising keywords in analytics?
-
Hi Guys
I just spotted in the Google Analytics > Traffic Sources > Search Engine Optimisation > Queries section a keyword that on the 27th Feb went from generating 35 impressions per day to 2000 impressions per day.
This was down to people searching for a newly launched product.
Does anyone know how I can identify any increases like this easily? I only came across this by accident, and I am 1 month behind the times. It would have been ideal to pick this up closer to the time so I could capitalise and write some great content on the topic.
Thanks
Paul
-
I totally dorked up the cells in step 9 - I fixed that in my answer above.
As far as the Step 10 - 11 thing, I don't think I did. I think you skipped the second half of step 10. Here's my original text, with bold.
- Highlight the cells from H1 to H12. Grab the black square in the bottom right of the border box. Drag this selection down all the way to H2000. **Copy all your cells from A1 to H2000 and paste values to remove any formulas. We want just text now. **
---------------------
Instead of copying and paste values for Column H, I just said do the whole thing. At this point you only want text so it makes sense. I was going to edit but I think the correct step is already in there.
-
I'm glad you liked the answer. Sometimes it's difficult to take the time to write up a long answer like that but once in awhile it just happens and comes out. I had a feeling others may like being able to mess with that sort of data so why not, right?
I'll write it up soon - I think I may do a bit on "playing with your food data in Excel."
I'm going to go make a few edits to my post so it has all the right steps. Thanks! I see that I messed up my Bs and Hs. After awhile I think I lost track of typing up each step and tried to recreate in my head what I had done. lol
-
Excellent, did it, got data, very happy.
Just incase anyone else comes across this there is 1 correction to the above, and 1 missing step:
- To match keywords to their change line, follow this specifically. In H4, type =B1 This should now say your keyword. Manually type in a few of these, so go to B8 and insert the formula =B5, then do the same with B12. The formula is =B9. You should see three keywords in Column H.
should be
- To match keywords to their change line, follow this specifically. In H4, type =B1 This should now say your keyword. Manually type in a few of these, so go to H8 and insert the formula =B5, then do the same with H12. The formula is =B9. You should see three keywords in Column H.
So you only put this formula in Column H, referencing column B.
Then, between Step 10 and 11 you need to copy column H and do Paste Values, otherwise when you sort in step 11 you will end up with #REF errors.
This has yielded some excellent data for me to slice and dice.
-
WOW!
That is the most detailed response I have seen in a Q&A. Thanks for going to so much detail.
Definitely write this up as a YouMoz, as I have a feeling this would be a great interest to a lot of people, and could well get promoted to the main blog.
Right, I'm off to fire up Analytics and Excel and see what I can see.
This truly is brilliant. Thanks so much.
Keri, thanks for the heads up on the alerts blog post. I have used alerts, but for some reason I just didn't think to use these to identify changing trends in keyword usage. So I will give this a look also.
Thanks Guys
-
Ahh, the problem is that the OP changed jobs, and the new person removed those custom alerts. I hadn't realized that until just now. Thanks for letting me know, and I'll see if we can do anything to get it fixed.
-
Thanks for the endorsement Keri! Appreciate that.
From the post you linked - it doesn't look like the Grab It link works with the new Analytics. Those are great alerts to setup but maybe someone can write up a 2013 version.
TY!
-
Awesome response here! I'd also like to add we did have a great YouMoz post two years ago about essential alerts for GA that would email you when certain events happened. It's at http://www.seomoz.org/blog/7-essential-google-intelligence-custom-alerts-that-keep-me-sane.
-
Sure, you mostly care about ones that have already jumped up in impressions or clicks, right? I'm going to be very specific on the steps but this isn't as hard as it looks.
-
Go to Traffic Sources > Search Engine Optimization > Queries
-
Select a date range and then use "Compare to" and use "Previous Period."
-
Set Show Rows to 500.
-
Sort the table by Impressions.
-
Copy all the data from the table starting from "1. keyword" and finishing with the end of the table (last change%). Paste this data directly into Excel. You should have columns A-F used, it'll look very messy. Something like the attached "sorting1.jpg"
-
Find & Replace "% Change" with "Change"
-
In Column G put this formula: =IF(LEFT(B1,6)="Change","Yes!","") Copy this formula from G1 to G2000. Every line that says "Change" should now say "Yes!" at the end.
-
Copy column G and use "Paste Values" to remove the formula and leave only the "Yes!" in place.
-
To match keywords to their change line, follow this specifically. In H4, type =B1 This should now say your keyword. Manually type in a few of these, so go to H8 and insert the formula =B5, then do the same with H12. The formula is =B9. You should see three keywords in Column H.
-
Highlight the cells from H1 to H12. Grab the black square in the bottom right of the border box. Drag this selection down all the way to H2000. Copy all your cells from A1 to H2000 and paste values to remove any formulas. We want just text now.
You should see a list of keywords in column H now, every 4 cells or so. Check Sorting2 attached. The rest is very simple.
-
Sort by Column G, Sort by Values, Z to A. This should be looking valuable now.
-
Delete Rows 501-2000. All rows should be "Yes!" now.
-
Delete Columns A, B and G. Feel free to move Column E to Column A if it makes you feel better.
-
Insert a new Row 1 at the very top. Label each like this:
Cell A1: Impressions
Cell B1: Clicks
Cell C1: Avg Pos.
Cell D1: CTR
Cell E1: Keyword
- Highlight Row A and push View > Freeze Panes > Freeze Top Row. Highlight all the data, press Ctrl T to create a Table. Now you can easily sort any row and sort through your data. See sorting3.jpg attached.
It's really not as complex as it looks. Just use the two formulas, follow the steps and boom, sortable and pretty data.
(I may write this up as a YouMoz post with better screenshots, maybe a video tutorial or something, so thanks for giving us the chance to help!)
-
-
The fact that you went from generating 35 impressions per day to 2000 impressions per day, could mean that one of your pages is on the first page of a SERP that has high traffic. However, that can also mean that it is in the last spot and not driving any conversion.
To find out the keyword go to secondary dimension and select: Traffic sources > Keywords
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
-
Unsolved Google Analytics (GA4) recommendations for SEO analysis?
Guides on Moz and elsewhere mostly refer to Google Analytics' Universal Analytics (UA). However, UA is being replaced with GA4, and the interface, options, and reporting are very different. Can you recommend a clear, thorough, and effective walkthrough of how to set up useful SEO reports in GA4? Is there a simple tool you recommend that will help connect historical data from UA to GA4 when GA4 is the only option available? If there's no simple tool, what values do you recommend retaining from UA for effective historical reporting? How would you use them? At minimum for reporting, I'd want to show month-to-month changes and year-to-year changes (in percentages and in real numbers) for the following: all site visits all organic visits organic visits as a percentage of all site visits organic visits that led to a specific goal completion organic visits that led to any goal completion Thanks in advance for your help!
Reporting & Analytics | | Kevin_P1 -
Google Analytics - Adding a sub-domain
Hi I have a google analytics query.
Reporting & Analytics | | Niki_1
I have a main site with a google analytics tag and I have 2 forms that sit on a subdomain with a different GA code. As I would like to measure end to end tracking, I would like the same GA code on the subdomain. What is the best way for me to implement this? Would I need to make some changes to the GA code that sits on the main site or can I add the the GA code from the main site onto the subdomain? Thanks0 -
Google Analytics: Trackbacks & Network Referrals?
Buon Pormeriggio from 15 degrees C mostly cloudy Wetherby UK 🙂
Reporting & Analytics | | Nightwing
Whats the difference between Trackbacks & Network referrals within Google analytics social media reporting? I'd like to specifically understand why a link to site i'm working on withing this post:
http://huddled.co.uk/huddled-interviews-nicola-schaefer-from-liverpool-fc-e-l-i-t-e-s-8335/ is classed as Trackback & not a Network refferral 😞 Illustrated here is the link thats being recorded as a track back:
http://i216.photobucket.com/albums/cc53/zymurgy_bucket/track-back-query_zpsbab2679b.jpg And here is the data:
http://i216.photobucket.com/albums/cc53/zymurgy_bucket/google-trackbacks-2_zps2861fa17.jpg But why is not showing up in Network referrals? Thanks in advance,
David0 -
How can I see keyword rankings past 50?
The rankings in the campaigns appear to be limited to tracking terms in the top 50. is there a way to view past that? I understand that for the most part no one is ever going to see past that so 50+ might as well be 5000. But a client has requested for a handful of key terms. If this is not possible, can someone recommend another tool I might use? Thank you! Kevin
Reporting & Analytics | | yandl0 -
Different metrics in analytics and adwords
Hi. My adwords campaigns and analaytics reports shows me slightly different results. Even for paused or disapproved ads in adwords, the analytics shows me some clicks?! I have automatic tracking enabled. Is it some kind of fault in configuration or its normal? Thank you for answer/link with answer.
Reporting & Analytics | | asystematic0 -
Entrance keyword for specific landing page Google Analytics V5
Hi all I cant seem to find a way to get Entrance keywords for a specific landing page in version 5 of Google Analytics. Any quick tips? Thanks a million Fredrik
Reporting & Analytics | | Resultify0 -
Why is Webmaster Tools not Linking Correctly Google Analytics?
I have a global site with .com as the US site and then /country for all other markets i.e. .com/uk .com/de etc. Each site has it's own WMT profile. Each site has it's own GA profile. Since Google added Search Engine Optimization to the GA interface, I can't track these sites separately under this new feature. It seems to me that it can only be associated with a single WMT profile at a time or rather that WMT can only be associated with a single GA profile at a time. The numbers for the UK and US site are identical, but when I try and link the UK WMT account with GA it removes it from the US profile and vice versa.
Reporting & Analytics | | Red_Mud_Rookie0 -
Setting up Analytics on a Site that Uses Frames For Some Content
I work with a real estate agent and he uses strings from another tool to populate the listings on his site. In an attempt to be able to track traffic to both the framed pages and the non-framed pages he has two sets of analytics code on his site - one inside the frame and one for the regular part of the site. (there's also a third that the company who hosts his site and provides all these other tools put on his site - but I don't think that's really important to this conversation). Not only is it confusing looking at the analytics data, his bounce rate is down right unmanageable. As soon as anyone clicks on any of the listings they've bounced away. Here's a page - all of those listings below " Here are the most recent Toronto Beaches Real Estate Listings" are part of a frame. http://eastendtorontohomes.com/toronto-beach-real-estate-search/ I'm not really sure what to do about it or how to deal with it? Anyone out there got any good advice? And just in case you're wondering there aren't any other options - apart from spending thousands to build his own database thingie. We've thought about that (as other agents in the city have done that), but just aren't sure it's worth it. And, quite frankly he doesn't want to spend the money.
Reporting & Analytics | | annasus0