I recently wrote a review of SEO Rank Monitor and if you read it, you will know how much I like the software! Well I came across a small problem with exporting keyword ranking data into Excel. It was only a small problem which was easily fixed after I dropped a quick email to support. But I thought that it would help to show the process here as well in case others have the same problem.
Basically when I export a report I ended up all the data being combined into one cell – not very much use to anyone!
To overcome this problem you just need to import the data in a certain way and reformat it by following these steps.
1. Open a new Excel spreadsheet
2. Go to Data > Import External Data > Import Data
3. Navigate to your CSV file that you’ve just downloaded from SEO Rank Monitor and import it
4. Select Delimited and click next on this screen:
5. Select semicolon from this screen and click next:
6. Click Finish followed by Ok
Thats it, you should now have all the data seperated nicely into columns which is much easier to manage, sort and manipulate.
If you haven’t tried it yet, read my SEO Rank Monitor Review or go over and get a free trial and see what you think.
Tags: how to export data from seo rank monitor, keyword ranking software, seo rank monitor
I wrote some time ago about the top Google Base Ranking Factors and had some good feedback and opinions. One of the questions which kept coming up was how to monitor traffic you received as a result of listing your products on Google Product Search. I’ve found a pretty good way of doing this via Google Analytics so I thought I’d share this technique with you. You do get some stats by logging into your Google Merchant Centre but I wanted to see how much revenue was being generated in comparison to other traffic sources.
If you’ve used Google Base at all, you will know that it involves uploading a feed of all of your products to the system. Part of this feed is the URL of your product, all we are doing is adding a small piece of tracking code to each of your product URLs which will feed click data into Google Analytics.
I used the principle behind the Google URL Builder to get the correct tracking URL.
I’ve basically tagged all my product URLs with the following:
?utm_source=google&utm_medium=base&utm_campaign=products
The important parts are the source=google and medium=base which feed back into Google Analytics. If you use this format, it will show as this when you view traffic sources:
Now that you have your tracking code, you need to attach it to all of your product URLs. If you are a developer or have someone to call upon, they can do this automatically for you when you generate your product feed. However if you are limited to just the standard feed, you can do some tweaking with Excel to take care of this issue quickly.
1. Download your product feed and open with Excel
2. Insert two columns to the right of the column that contains your product URL (column header should be “link”)
3. Write your tracking code into the cell to the right of the URL:
4. In the next cell to the right that you’ve previously inserted, write the following formula, substituting cell numbers if appropriate:
=CONCATENATE(B2,C2)
You should see a result similar to the screenshot below showing the two cells merged together:
5. Copy the cell formula all the way down to cover all of your products so that all of them having the tracking added.
6. Highlight all of your product URLs which now have the tracking code added, copy and paste them into Notepad
7. Delete the columns you’ve just created so that you only have the ones you started with.
8. Paste in all of your new URLs over the top of the old ones – you have to do this in order to get the spreadsheet back in a format to be uploaded – the extra columns will be rejected.
9. Upload to Google
10. After a day or do, log into Analytics and go to traffic sources. You should be seeing traffic from Google / Base showing. Assuming you are getting traffic! It can take some time to start showing and build up a good ranking. Best to test by clicking on a few of your own first to see if the tracking is working properly.
Tags: google base, track traffic from google base, tracking clicks from google base
Finding a reliable keyword ranking tool has been quite a struggle for a while now, I’ve used quite a few and they’ve had various problems. One of which was running too many queries on Google in a short space of time and getting our IP address banned – which the other guys in the office weren’t happy about! Another problem was exporting results into a nice format and monitoring over time, it often took some trickery with Excel and some extra formatting for the results to make sense over time.
I stumbled across SEO Rank Monitor by chance really, in fact I can’t actually remember where I first saw it but I remember making a note to test it. I setup an account last week and put a few sites into the interface. I went for the $39 a month plan and got the following -
- 10 Domains to track
- 2500 Keywords
- 3 Competitors
- Analytics integration
After a day or so there was enough data for me to assess how the software worked. I was very impressed, the user interface is very smart, tidy and easy to use. But the best part was the fact that the software updates my rankings automatically, plots it on a graph and shows you progress over time. It took me ages to accomplish this with Excel! Here is an example of how you can see your overall rankings at a glance:
There is also an option to integrate with Google Analytics so that the software can record how many visitors you get and aligns this number with that keywords ranking. This involves giving user access to your Analytics account which I was a bit unsure about but after an email to the rank monitor guys I thought I’d give it a shot. Again this data is great and presented in a friendly interface.
My only slight disappointment was that you can only track three competitors as well but this isn’t a major problem. I can get over it!
As you can probably tell, I’m very impressed with the software hence I’m recommending it to you guys. If you want to take a look here is the website link – SEO Rank Monitor.
Tags: Google Analytics, keyword ranking tool, keyword research, SEO, seo rank monitor
As an SEO, you are going to have some kind of understanding and regular use of Excel. Whether its exporting keyword data or compiling stats for a client report. However most SEOs (including myself until recently) don’t understand the potential Excel has to make our lives so much easier. It can be integrated with a number of SEO/PPC tools and make sense of normally complicated data. So it can be utilized to plan future campaigns, review existing ones and visualise data for your boss or client.
I wanted to follow in the footsteps of Richard Baxter and Will Critchlow who first sparked my interest in Excel as an SEO tool back at the SEO Pro Training Seminar. So I’ve decided to do a series of blog posts showing how I use Excel in my SEO work. How often these series will be I can’t say as I don’t blog enough due to lack of time already. But I’ll do my best!
Using Google Analytics and Excel to Plan Keyword Targeting
Objective – See which pages to target with your keywords
First in this series of blog posts, I want to talk about exporting keyword data from Google Analytics and using it to plan future SEO campaigns. You can export keyword data and sort it so that you can see which keywords sent the most traffic to which pages. This data tells you exactly which pages are already ranking for your keywords, therefore you know which pages Google see as most relevant.
I actually came across this method whilst doing some research for a client, I wanted to know which keywords sent them the most traffic last summer. Some of their product lines are very seasonal and we saw a large increase over the summer, so I wanted to be prepared for this year. I figured the best place to start was to improve rankings for keywords they were already getting some traffic for.
After I’d exported their top referring keywords, I started to map these keywords to the pages I thought were most relevant. Then I realised – Analytics could tell me which pages had actually received traffic for these keywords. This data would make it much easier for me to plan my SEO campaign.
Note – Data is from my own blog
1 – Go to Traffic Sources > Search Engines
2. Click on Google followed by non-paid traffic to see the following screen (with keywords included!)
3. From the drop down menu select “landing page”
4. You should see something like the following
5. Go to the bottom of the page and select how many keywords you want to look at, I usually choose 500
6. Export your data into Excel
Now you have several approaches you can take at this point, you can sort by keyword, landing page or visitors. It took a bit of playing around but I decided to sort by landing page followed by visitors. This gave me a sorted list of which pages had received the most traffic and which keywords sent that traffic.
My next step was to use some internal processes and documents to map the keywords to landing pages and integrate this into the SEO strategy for the client. Its worth noting that its possible for keywords to send traffic to more than one page, commonly this will be your homepage and an internal page. Here you need to make a judgement call but I’d nearly always opt to optimise the internal page.
I’d start by going through my on-site SEO checklist followed by coming up with some link building strategies that can incorporate these keywords and landing pages.
Thats it! Nice simple way to get data into Excel, re-arrange it and use that data to plan your SEO campaign.
Tags: Google Analytics, keyword research, SEO, seo strategies
Very quick post on increasing your chances of being listed in the Open Directory Project. I’m not convinced that its essential for a site to be listed in order to rank well, however it certainly can’t hurt to be listed. Plus for the time it takes to submit its worth it.
If you find a category that has an editor listed at the bottom of it, then you can be reasonably sure that it is being maintained. If a category has “Volunteer to Edit this Category” at the bottom, then chances are that there is a big queue of submissions which aren’t being looked at.
Try using the follow advanced query on Google to find these pages -
site:www.dmoz.org “Category editor:” +your keywords
This should bring back a set of results that are more useful and will get you a better return for your time spent link building.
Another option to get listed in Dmoz is to become an editor, if you are interested in this then there is a great post over at SEOmoz.
Tags: advanced search queries, dmoz, link building, SEO
