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
-
Google Analytics - Average Position
Hi Just trying to get some clarity on Google Analytics Average Positions in "Aquistions/Search Engine Optimisation". For a very competitive keyword Google Analytics is saying i am on average position of 6. Is this Page 6? I am assuming position six would be 1.6?
Reporting & Analytics | | Cocoonfxmedia0 -
I am tracking my domain e.g www.xyz.com to an existing google analytics account . I want to track www.xyz.com/blog separately in the google analytics account. How can I do that?
I am tracking my domain e.g www.xyz.com to an existing google analytics account . I want to track www.xyz.com/blog separately in the google analytics account. How can I do that?
Reporting & Analytics | | Windlass0 -
How do you setup a test analytics account?
I have created a test view but that is not helping with skewed data. Do you all just create a total separate profile with a different GA account for a non-indexed domain? How can I test tag manager and other things without messing with our data? I have our IP filtered out so it doesn't show up in reports but other event tags from outsiders will.
Reporting & Analytics | | Sika220 -
How can I tell if I am using Universal Analytics?
On my Google Analytics page it says at the top "Upgrade to Universal Analytics Upgrade to get a deeper understanding of your users through new tools and more accurate data." Below that it talks about how to do it. I do not see what they are talking about for "Select an account and a property, then click Universal Analytics Upgrade." But if I can into my change history is shows Changed By-System Property "http://www123.com" started transfer to Universal Analytics processing." This was on May 22nd and it hasn't said anything since. Why does it keep telling me to do it if it already has? It doesn't say transfer complete anywhere so I don't know if it finished or not.
Reporting & Analytics | | EcommerceSite0 -
Google Analytics - Not provided info
Morning all, i am using Google analytics for some time now, One of the issues that i don't have a clear answer for is, when i am looking at Traffic Source --> overview In the Keyword column, that is a line (not provided) What we think here is that we cant see the keywords if someone arrived to our website but was logged-in to Google account Is this correct? is there a way to find the keywords behind this (not provided)? - i have many visits for this line (17.3% of all visits) and i don't know which keywords people used. Thank you SeoWise
Reporting & Analytics | | iivgi0 -
How to force keyword check?
I used to have only 5 keywords that I tracked, but I have just added about 100 more. But when I look in the overview, it says: "Rankings Updated Every: Tuesday Last Data Update: 01/07/2013" It is not Tuesday yet, but seems that it was updated on January 7th. Will I have to wait another week? How can I force a re-check? I'm in rush to get this data 😞
Reporting & Analytics | | Rank4Stuff1 -
Google analytics reality check?
Looking back over a 9 month period tracking analytics with getclicky my site showed a 29% bounce rate, with only about 1/4 of visitors spending 1 minute or less on my site. I've recently implemented GA (removed old clicky code) and although traffic is strong, my site now shows a bounce rate of about 82%. Engagement stats also show that 82% of visitors spend between 0-10 seconds on my site. My site is built on Wordpress and the GA tracking code wasn't placed directly in the footer, my developer built a field in the admin area to insert the UA number which automatically adds the code to all pages. I've checked the code and the tracking seems to appear on all pages. I took a look at AW Stats. It corroborates GA and says that 80% of visitors are spending 0-30 seconds on the site. Potential issues/clues: browser tests show small loading problems in Internet Explorer 7,8,9 (the phone number at the top of the header loads on the wrong side of the page) and major issues in Internet Explorer 6 (site doesn't load at all in IE 6). The thing is no one who uses IE 6 is coming to the site. Second, the site gets a grade of C in YSlow, it's not lightning fast at the moment. GA is showing average page load of 2.4 seconds, but don't think either of these issues should cause an 82% 0-10 seconds engagement number. My site is content rich/focused with very minimal advertising. Content is accessible well above the fold. My question: Does the fact that AW Stats and GA agree mean that those numbers are accurate, or is there a bug I should be looking for? How to explain the clicky numbers?
Reporting & Analytics | | JSOC0 -
Impressions in Google Analytics
I am trying to compare SERP impressions in Google Analtyics between two different time periods. I want to compare the last two months, with the previous two months. Now this works fine when I go to Traffic Sources > SEO > Queries. Our analytics has been set up since early last year, so I cannot understand why, for a couple of weeks at the start of the previous two months, it is showing that I have less than 10 impressions per day, then in one day, it jumps to 22,000 impressions, and starts to show 'real' information after that... Very frustrating when I am trying to show how effective my work has been. Can anyone shed any light on this?
Reporting & Analytics | | MirandaP0