Newsletter - August '08

Spreadsheet Tips - Presenting Website Statistics from Google Analytics. Part 3 of 3.
In the third and final part of our collaboration with Branch Out Internet Marketing on extracting, compiling and understanding website statistics, we look at Keywords. This series of newsletter spreadsheet tips comes with a free download, the Google Analytics Spreadsheet Tool. Remember to scan it just in case.

Finding out which words and phrases people are using to find our site can be very useful and gives us a good insight into the way they are thinking about our businesses. The other valuable element of keyword analysis is to find out which keywords are bringing us the visitors who 'convert', i.e., do the things we want them to do. Identifying these phrases will help us to do more of what works and cut back on less effective areas of activity.

The general public does not always use the same words and phrases as we do to describe our business or our services. This can be very frustrating if we have spent time, money and effort on developing marketing messages to set us apart from the crowd. However, we cannot force people to adopt our 'jargon' so we need to understand which words they are actually using.

Another interesting discovery we often make when we investigate our keywords is that we often use words in our businesses in a very particular way. Unfortunately, the same word can be used totally differently by others. Alongside the misunderstandings that come from words with 1 spelling but 2 pronunciations, e.g. 'lead', there are differences in context. For example, a person would never think that 'massage parlour' was the same as 'sports massage therapist' but to a search engine, both phrases contain the word 'massage' and are therefore related.

Reviewing the keywords people use to find your site can help you make pragmatic choices about the words and phrases you use during optimisation and advertising and make you aware of any misunderstandings that are bringing you the wrong sort of traffic.

Preparing Website Statistics from Google Analytics for presentation.

Step 1: Extract the data from Google Analytics.

Log in to your Google Analytics account and bring up the dashboard. See Fig 1.

Fig 1


Click on Traffic Sources, then Keywords, as in Fig 2.

Fig 2


Now we set the date range. We will look at data concerning Keywords on a quarterly basis. In this example, we will use the 1st of July to the 30th of September, 2007. See Fig 3.

Fig 3


Below the timeline, we now click on the tab named Goal Conversion, as in Fig 4.

Fig 4


We want to sort the results by Goal Conversion Rate. Click on Goal Conversion Rate, so that the arrow is pointing downwards, as in Fig 5. This means that the results are sorted in descending order and we will have the highest results at the top.

Fig 5


At the bottom of the screen, click on the pull-down menu for 'Show Rows', and select 25, as in Fig 6.

Fig 6


We are now ready to export the data. Below the title of the graph, click on "Export". This will open a small sub-menu, select "CSV". See Fig 7.

Fig 7


A dialogue box will open asking if you want to open or download. Open in Excel. See Fig 8.

Fig 8


The downloaded data will open in a spreadsheet, as in Fig 9.

Fig 9


Scroll down the screen, beyond the single column of data, until you get to the table of data. Copy the data from the first two columns, named 'Keyword' and 'Visits', as in Fig 10. This is the first data we will copy and paste into our Google Analytics Spreadsheet Tool.

Fig 10


Now we turn to the Spreadsheet Tool. In the tab named 'keyword_data', go to the 1st Quarter and paste the data into the first two columns, also named 'Keyword' and 'Visits' as in Fig 11.

Fig 11


Go back to the spreadsheet with the Google Analytics data and copy the data for two of your goals. In this example, we have selected 'Goal 2' and 'Goal 3', as in Fig 12.

Fig 12


Paste these into the columns marked 'goal 1' and 'goal 2' in the Spreadsheet Tool, as in Fig 12b. The titles 'goal 1' and 'goal 2' can be changed in the Spreadsheet Tool to their proper names. We are using the names 'goal 1' and 'goal 2' here as an example.

Fig 12b


Go back to the Google Analytics spreadsheet and copy the data from the column named 'Goal Conversion Rate', as in Fig 13.

Fig 13


Paste this into the column with the same name in the Spreadsheet Tool, as in Fig 14.

Fig 14


In the final step, go back to the Google Analytics spreadsheet and copy the data from the four columns named 'Pages/Visit', 'Avg. Time on Site', '% New Visits' and 'Bounce Rate', as in Fig 15.

Fig 15


Paste this into the remaining four columns, of the same name, in the Spreadsheet Tool, as in Fig 16.

Fig 16.



That completes all the work required for the 1st Quarter results. Repeat the above steps for the remaining three quarters, pasting the data into the relevant section in the 'keyword_data' worksheet. All your data has been automatically converted into graph form.


Step 2: Interpreting the graphs.

Kate Barlow, of Branch Out will now take you through the Quarterly graphs and explain their significance to your website, and ultimately your business.



Visits by Keyword - (key_q1_visits for 1st Quarter), Fig 17.

This graph tells you which keywords are bringing the most visitors to your website. They key here is to identify the ones which are sending you lots of traffic and check whether or not they are the ones you would expect to be good sources. It�s also worth reviewing the keywords which you might class as under-performers. Both the good and bad sources of traffic give you an indication of how your own keyword list compares with that of real people out there using search engines to find you.

Fig 17




Average Time on Site by Keyword - (key_q1_avg_time for 1st Quarter), Fig 18.

From an e-marketing perspective, good keywords are ones which attract visitors who �like� your website. These people tend to spend longer on the site and often look at more pages. If you have keywords which consistently attract people who spend large amounts of time on your website, it might be worth reviewing your search engine rankings for these keywords and ensuring that you maintain a good position in the search results. You could also consider using them in your online advertising.

Fig 18




% New Visits by Keyword - (key_q1_new_visits for 1st Quarter), Fig 19.

The keywords that people use to find your site for the first time will give you the greatest insight into the way �outsiders� perceive your products or services. If your company name appears in this list it suggests that either your brand is well established in the market place or that many people hear about you from other sources and then seek you out online. You need to make sure that your site appeals to these �warm contacts� as these are likely to be your best website visitors.

The other keywords used to find your site will tell you what real people call your type of product or service. An example I often use to illustrate this is of a company which sold �pricing guns� or �click & stick pricing guns�. To differentiate their (superior) product from the mass market, they called them �handheld price labellers�. However, the web stats clearly showed that no one outside the company referred to the products in that way so their potential customers were unable to find them online.

Fig 19




Bounce Rate by Referrer - (key_q1_bounce for 1st Quarter), Fig 20.

It is very useful to identify the keywords which attract people who leave the site almost immediately (bounce). These might be ones where there is a misunderstanding about the word (like the �lead� and �massage� examples above) so you need to review them and consider what action needs taking. Do you need to tighten your advertising campaigns or your search engine optimization?

A further reason for a high bounce rate is that the page where people arrive doesn�t appeal to them for some reason. This can be because they are not your target market or because your website needs some work. It�s a good idea to check which pages appear in the search engine results when you search for one of these Bounce keywords. Ask yourself if it really relates to the word or phrase you searched for. If it doesn�t, you will need to revisit your search engine optimisation to get a more appropriate page to appear in the results.

Fig 20


There is lots to be learned from reviewing your keywords and we have only touched on the key points in this newsletter. If you would like more guidance or have a specific question, please do not hesitate to contact us.


back to top		View our tips archive.