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
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
-
SEO Keyword Research
Is there a way to insert Google API into Excel? I want to have a search volume column that would automatically fill out with query Another question is that I have a list of keywords in one column in Excel with another column that have search volume. I then go and do some research on keyword groups with a grouping tool but if this tool doesn't find a group for a keyword it removes it. So then when I want to copy and past keywords and match keyword columns in excel that already exist with search volume I have to do it manually because some rows are missing. Is there a way this could be done automatically so that when I paste it in the column with keywords it automatically finds that the current column exists and adds a group column ?
Reporting & Analytics | | aliciaporrata10090 -
Traffic drop after analytics troubles
Hi For two weeks we had an artifical low bouncerate & high pageviews/visit in our Analytics reporting. The day we corrected the bug in Analytics - our bouncerate & pageviews/visit returned to normal levels - however we saw our search traffic go down massively(-50% in sessions). The bug in the Analytics was caused by a second Analytics tag which was included in an external script which interfered with our own tag. The drop in traffic occurred just after the removal of the second script (which was only on our site for two weeks). We didn't touch our own tagging - and there were no technical changes on the site during this period, and there is no seasonal trend which could explain the sudden drop of traffic. We double checked our tagging - and the analytics tag is present & working on all the pages of our site. On the organic traffic report from analytics you can clearly see the when the troubles with analytics started & ended (artificial low bounce rate) - and that the traffic drop starts right after the reporting issue ended. Webmastertools also indicated a lower number of views/clicks, but not to the massive 50% drop. Is it possible that Google uses the measurements from Analytics for it's SERP's? Or should there be another reason, and where should we start looking? Appreciate your help! e5GmoMM
Reporting & Analytics | | DirkC0 -
Improved keyword ranking but less traffic
Hello fellow mozzers! My collegue and I are a bit puzzled in regards to our recent website statistics. In november 2013, we upgraded the technological platform of our website to be fully HTML5 coded, and implemented the schema.org Products scheme to systematically tag all our products on the site. To prevent too much loss of visitors, we created a 301-redirect table from almost all our old URL's to the specific new ones, as we implemented a new URL structure as well. The first few months were bumpy as expected, making a huge drop in rankings before rising up again. Our keyword rankings are better then ever (60% of the keywords in top 3, average competition, 25% more on first SERP) but our number of visits dropped by about 10%. Our bounce rate went down from 20% to 14%, our returning visits are stable, but our new visitors stats dropped by 25% as well. This comparison was made between equal periods in the current year and last year, using organic data stats. (new technical platform vs. the old one) What could be the reason that our number of visits dropped 10% while our keyword ranking is better then ever? We don't have any manual penalties in GWT and can't understand why visits would drop so much while ranking improved. May it be so easy that there's just less search volume on our ranked content or does anyone have other ideas? Thank you all in advance!
Reporting & Analytics | | EconostoNL0 -
Strange Traffic / Viewed content in Analytics
Hi Im looking at the content viewed in December for a clients site and there's a high number of page views for some non-existent pages which received multiple hits eg. domain.com/dr-capability/ with 241 page views (54 unique page views) How can you have multiple page views (and many unique page views) for pages that don't exist ? Is it possible page is being generated automatically or something ? if so how come ? Any ideas as to whats causing this much appreciated ? All Best Dan ps - also many above average home page views
Reporting & Analytics | | Dan-Lawrence0 -
AHHH... Google Analytics just changed!
There's now an acquisitions tab.. but I can't figure out how to sort referral traffic by referral path.
Reporting & Analytics | | S.S.N0 -
Switching Google Analytics from Urchin to GA
I want to finally make the switch from Urchin to GA in Analytics, but the Google support page is no longer working (I realize I'm late to this so they likely took it down/moved it to where I can't find it). I've been searching on Google but can't find a straightforward guide to changing that won't disrupt my data. Anyone have a link to a working guide, or can you provide some guidance?
Reporting & Analytics | | Axios_Systems0 -
Tool to check pervious rankings for a keyword ?
I am looking a tool/site to check ranking history of specifc keyword . Is this possible ? Thanks
Reporting & Analytics | | bfletc40 -
How to change a url for google analytics account
We recently changed the url of a client's website. Is there a way to change the url on the ga account instead of creating a new account so that we don't lose comparative data? Thanks! Sorry- I know this is a novice question.
Reporting & Analytics | | marketing12340