Searchmetrics is a brilliant SEO tool, the amount of insight that it gives on client and competitor sites is incredibly useful. One of my favourite reports, along with some manipulation in Excel is to run a quick rankings comparison report on your competitors so you can gain insight into what they’re ranking for, more importantly what they’re ranking for and you’re not, and also how your site matches-up a full range of industry keywords.
For this sample report I’m going to take a look at some of the bigger sites in the insurance sector.
Other good insurance companies do exist, along with quite a few terrible ones.
Run each domain through Searchmetrics and run a long-tail keyword report on each of the sites that you wish to compare.
Export and download each of these reports.
In Excel create different sheets for each of the exports along with the first sheet which should be named ‘comparison’ this is where all of the magic happens and your data will be pulled-in.
Paste each sites data into onto it’s own sheet, as well as cumulatively into the ‘comparison’ sheet.
Then under Data > Remove Duplicates remove duplicated keywords on the ‘comparison’ sheet.
Then delete the following columns in the ‘comparison’ sheet - URL, Pos, Title, and Traffic Index. This should leave just Keyword, Search Volume and CPC.
Next add columns for each of the sites that you wish to compare. This should leave you with a sheet that looks something like this.
Then, using VLOOKUP you’ll need to pull the ranking data from the other sheets into the comparison sheet. So for example into Column C all of the rankings for Aviva will appear.
The formula you’ll need is =VLOOKUP(A:A,Aviva!A:G,3,0) The easiest way to generate this is to use the insert formula function,
Lookup value – Is the value that you’re looking up, in this case is column A, the keyword.
Table array – is the table you’re finding the value in, which is the Aviva sheet, so click in the table array entry field, then go to the Aviva sheet and highlight all of the columns.
Col index num – is the column with the data in that you wish to import, so column 3, the ranking position.
Range lookup – Enter FALSE or 0 here to find an exact match. This will cause #N/A to be returned if the site isn’t ranking for the keyword.
Repeat this for each site. And then expand the selection by dragging the corner of the box down to apply to each of the cells in the sheet.
Tidy the sheet up by formatting as a table, and (hopefully) you should have something that looks like this.
If the #N/A results are annoying you can easily remove them by modifying the VLOOKUP formular from
You can also colour-code the rankings using conditional formatting.
If you would like to download this example sheet I have added it here - CompetitorReport
August 17th, 2011.
Goals and transactions don’t match in Google Analytics? The most common problem if goal conversions don’t match ecommerce transactions in Google analytics, is that your urls (pages) can be visited/accessed using both upper case and lower case characters.
This then causes Google analytics to report this page as two different pages, even though it was the same page, plus it automatically inflates the number of transactions. Moreover, your transactions then don’t match your order management system, which sooner or later will cause loss of faith in your web analytics.
Example of how this might look in your report:
To fix this, all you need to do is to set up a filter in Google Analytics, ‘Force url to lower case’. The filter looks like this:
Filter Name:Force Lowercase
Filter Type: Custom Filter, Lowercase
Filter Field: Request URI
After you set this filter, check back in a few days and your goals and transactions numbers in Google analytics should match as in this example below. NOTE: this will only fix the issue from the day this lower case filter was applied. Unfortunately, the data prior to the date when this filter was applied will stay unchanged.
When was the last time you had your Google Analytics tracking code audited? Can you 100% rely on your data? If in doubt, request your Free Google analytics tag audit.
Event tracking in Google analytics can be a painful task and it gets more complicated when your site has hundreds of outbound links, or downloadable documents you would like to track.
The good news is there are 2 options for how to track events (outbound links or files), manually or automatically with auto-tracking.
Option 1: Manual Tagging (complex, slow, prone to errors– recommended only for websites with a few outbound links or files to be tracked).
In this case we manually tag each link you would like to track. To log every click on a link to, for example, www.outbound.com, you would add an onClick event to outbound urls you wish to track:
<a href=”http://www.outbound.com” onClick=”recordOutboundLink(this, ‘Outbound Links’, ‘outbound.com’);return false;”>
Option 2: Auto-tracking - very easy & fast. Recommended for any website with large number of outbound links or files to be tracked.
Solution A; AnalyticsEngine provides you with a piece of script which you paste into your website just after your Google analytics and you’re done. No additional work or tagging required. In less than a minute you will be able to track thousands of outbound links or file downloads on your website.
This is how you see your results using solution from AnalyticsEngine
For small websites with a page views up to 100,000 per month this script is free. By the way, don’t confuse page views with visits. For example: 10,000 visits to your site can generate 4 page views per visit = 40,000 page views.
For websites with up to 1,000,000 page views the cost is $75 / month, plus there is an enterprise solution available as well. Considering how much time this saves you, it’s excellent value. Especially sites having a lot outbound affiliate links or downloads will greatly benefit from it.
Solution B; Or you can get similar script from advanced-web-metrics.com, for around $75 per domain (one off payment) and with no restriction on page views. No freebies here, however there is a 30 days money back guarantee and support from Brian Clifton, who is the author of an excellent book ‘Advanced Web Metrics with Google Analytics’ and former Head of Web Analytics for Google.
This solution won’t allow you to see clicked outbound links and file downloads under ‘Events’, but instead under ‘Content’ as page views. See the screenshot below.
And here when used auto-tracking used from advanced-web-metrics.com
(screenshot used from advanced-web-metrics.com)
July 14th, 2011.
Is brand awareness really important, or is it just hype? After reading a case study on ‘how catalogues drive online sales’, I did my own research with a focus on brand awareness, and analysed brand awareness on 2 small ecommerce sites, one of them with a catalogue, another without.
The result? Overall conversion rate on the site with a catalogue was 5.95%, while on the site with no catalogue, only 0.52%, a difference of 1,044%.
In order to calculate the impact of catalogues sent out to clients (offline marketing), we have to firstly establish where or how we find these people in your analytics tool. As these visitors receive your catalogue, we can therefore expect several ways how they get back to your site:
- type directly your specifically created vanity url for your catalogue (e.g. www.mysite.com/10off)
- or type your site url without the campaigned parameter
- or search for your company name using a search engine
- or dial your number on the catalogue and place a phone order. To be able to track this, you will need a call tracking solution which links with your Google analytics. Highly recommended, as it will allow you to assign your marketing to a relevant channel.
After we establish catalogue user behaviour, we need to find them in our web analytics. In Google analytics go to ‘traffic sources’ and get data for you ‘direct traffic’ and ‘search engine – paid and non paid brand keywords’. The data we need are: traffic amount and revenue generated. Let’s define what brand and direct traffic is first.
What is brand traffic? – These are the keywords people use to find your brand. For example, for Datadial.net, brand keywords could be ‘data’, ‘datadial’, ‘data dial’ and any misspellings of the brand name.
What is direct traffic? – These are visitors who type your domain name into their browser. NOTE: make sure your own company traffic is excluded by using filters in Google Analytics.
By combining brand traffic and the direct traffic you get ‘brand awareness index’ as a percentage of your overall traffic. So you will end up with number like xx% of visitors who visited my site used either my brand keyword or typed my url directly (direct traffic).
Exact calculation would be:
In terms of benchmarking, the higher the ‘brand awareness index’, the better. If your brand awareness index is 20-30% or less, that means people don’ t remember your company, site, product or service.
If your ‘brand index awareness’ is around 60+% then give yourself a high five, as everybody in the universe knows you! Well, not quite, as you would also like to find out what your market share is. Then give yourself high five if you also discovered that your market share is sound too.
Let’s jump now to some real examples.
In the example below, site A (no catalogue), with a ‘brand awareness’ of 20.92%, converted at 0.52%. Site B (with catalogue), with a ‘brand awareness’ of 65%, converted at 5.95%.
Notice one thing, site B received significantly less traffic, but the site revenue on both sites was almost identical. Also notice, that these visitors brought in both cases approx. 80% of site revenue.
So that leaves us with a question. What if I increased my site brand awareness from 20% to 60%? Is it going to have any impact on my revenue? And the answer is YES, because as we already know, direct and brand traffic conversion rate is higher, as you can see in this table below.
I hope you will find this useful and apply it to you next marketing strategy.
If you found this idea intriguing and would like to test it, call Jan at 0208 6000 500 and we will help you with calculating, planning and testing.
Goals in web analytics allow you to measure conversions on your website. So before you can start setting up goals, you need to know what actions (KPIs) are important for your business.
The most commonly used goals for ecommerce stores are:
- Sale completed
- Email sign up
- Help page visited
- About us page visited
- Return & delivery page visited
- Visitors who placed their order over the phone
NOTE: to be able to track revenue, average order value and what products you sold in Google Analytics, you will need a developer. See our Google analytics set up and audit prices.
Non-ecommerce websites goals could be:
- Contact us form completed
- About us page visited
- Contact us page visited
- Support page visited
- White paper download
- Pages/visit (useful for non-profit or news sites)
- Phone call tracking
Setting up goals for ecommerce stores – checkout:
Let’s say, your goal url is www.mysite.com /pages/checkout_thankyou.aspx. Below are screenshots illustrating how exactly to set up the goal in Google Analytics so you can track your sales.
URL – don’t use the full url. Instead of www.mysite.com /pages/checkout_thankyou.aspx, use /pages/checkout_thankyou.aspx
Goal value – leave it as 0.0 (for goals directly tracking ecommerce transactions), as additional ecommerce tracking codes installed on an order confirmation page will give us the exact value of each order.
Any other goals should have a value assigned to them. If you can’t calculate an exact value of each goal, then you can use value of 10.
Always set up a funnel, as this feature allows you to visualize each step and where your visitors are bailing out. Name it so you can easily associate with actual pages.
After we saved the goal, we need to install ecommerce tracking code on your order confirmation page (job for a developer). See our Google analytics set up and audit prices.
For more instruction regarding the code above go to http://code.google.com/apis/analytics/docs/tracking/gaTrackingEcommerce.html.
If any sales were made, then usually after 4-12 hours you should be able to see your transactions being tracked. If you made some sales within 24 hour period, but still can’t see any conversions being tracked in your analytics, then your goal isn’t set up properly.
With the goal and the funnel being tracked, you can see at which step your visitors are leaving and then with A/B testing you can try to fix it.
The process to set up goals for non-ecommerce sites is very similar, but with the following differences:
- No ecommerce tracking code required on your confirmation page
- Goal value – Very important. Either calculate your actual value per lead if you are able to monetize it, or try to use a goal value of 10, 50 or 100 (never leave at zero on non-ecommerce sites). This way you will be able to see in the analytics how valuable each page is in terms of contributing towards a goal. This will also help during conversion optimisation to find the lowest & highest performing pages.