Newsletter - June '08

Spreadsheet Tips - Google Analytics Presentation with FREE download Part 1 of 3.
If you want to build charts to present Google Analytics data to your team, why not use our free, user-friendly Excel spreadsheet download to turn Google Analytics data into professional presentation charts? Over the next 3 months, we are collaborating with Branch Out Internet Marketing to bring you a practical, usable guide, so you can take data out of Google Analytics and turn it into valuable information to share with your team. There are quite a few steps involved so we’ve broken this topic into 3 newsletters. The process might seem complex at first please bear with us. If you follow each step, we promise that you will soon have a PowerPoint presentation, with your key results, to show to your team.

Google Analytics offers a wealth of statistics so we will begin by focusing on the key metrics which are relevant to all sites: total visitors numbers, unique visitor numbers, the bounce rate, number or pages viewed. This will give your team a ‘feel’ for the overall performance of the site and help them to see this within context of your site’s trends, seasonal performance etc. For an overview on Google Analytics, read Branch Out's May '08 newsletter.

Google Analytics Presentation Chart with FREE download

For the next three month's we will be using Excel to compile and analyse statistical website information from Google Analytics, the free Web Stats package offered by Google. In order to reduce the size of these 'tips', we are offering a template spreadsheet for download. It is a powerful tool which you can use as you wish, although, this 'tips' and the next two month's newsletters will give you a good start. Download the spreadsheet in a handy zip file. (Always scan downloaded files for viruses, no matter where you download them from. It is impossible to 100% guarantee that the download will not be infected.)

The spreadsheet is colour coded. Sheets which have green tabs are where we paste in data from Google Analytics. The sheets with blue tabs show the automatically produced charts displaying the compiled information. The last five tabs in dark grey contain some background information it is best to leave alone. The tutorial below will take you through how to use each section. (If you are using a version of Excel which does not support coloured tabs, the first three tabs after the 'Welcome' page are for data entry and are named 'visitor_data', 'keyword_data' and 'referrer_data').

There are a few macros contained within the spreadsheet which support some of the automation. Within Excel, you can set whether to enable or disable these macros. If the push buttons on the data entry sheets are not working, the macro security level is set to high. If you reset it to medium (Tools menu > Macro > Security) and re-open the spreadsheet, you will then be given the choice of whether to enable or disable the macros.



Step 1: Prepare the spreadsheet.

Click on the tab called "visitor_data". Enter the first and last dates of the period you will be examining. In our example we are going to look from January the 1st, 2007 to the end of May, 2008. See Fig 1.

Fig 1


Click on the button marked "Generate Dates for Visitor Data". This will automatically add in the dates, one to each row, in the "visitor_data" worksheet. See Fig 2.

Fig 2


Step 2: Extract the data from Google Analytics.

The spreadsheet is now ready to paste in data directly from Google Analytics. Login to your Google Analytics homepage, also known as the dashboard. See Fig 3.

Fig 3


Click on the date range on the right hand of the screen and will open a calendar which you use to set the period you want to look at. In this example, we are looking at 01/01/2007 to 31/05/2008. You can either type your dates in, or select the range using the calendar, clicking on the first date and then the second date. See Fig 4.

Fig 4


Click on Apply and Analytics will return the results for that date range after a few seconds. First we will extract the basic visitor numbers. On the left hand side, click on "Visitors". This will bring up "Visitors Overview" as in Fig 5.

Fig 5


Below the graph, click on "Visits" and the graph titled "Visits for all visitors" will be displayed. This is the first set of data we want to extract. Below the title of the graph, click on "Export". This will open a small sub-menu, select "CSV". See Fig 6.

Fig 6


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

Fig 7


The downloaded data will open in a spreadsheet. Select and copy your data making sure to select to the final number at the bottom of the column, see Fig 8.

Fig 8


Paste this data into your original spreadsheet's "visitor_data" tab, under the column titled "Visitors". See Fig 9.

Fig 9


Return to Google Analytics. In the left hand panel, click "Absolute Unique Visitors". This will display the chart "Absolute Unique Visitors". As before, click on Export below the title and select CSV. See Fig 10.

Fig 10


Repeat the previous steps and paste the data into your original spreadsheet in the column entitled "Unique Visits". Repeat this process for "Average Page Views" and "Bounce Rate", both of which can be selected in the left hand panel. Choose CSV from the Export option each time and copy the results into the relevant column in your spreadsheet. See Fig 11.

Fig 11


We have one final column to fill with data, the Goals column. Go back to Google Analytics and select Goals from the left hand menu. Fig 12.

Fig 12


Select your goal from below the chart to display the chart "Goal Detail". Click on Export and select CSV. See Fig 13.

Fig 13


Paste these results into the Goals column in your original spreadsheet, which should now look like the one in Fig 14.

Fig 14


Step 3: Refresh the data in the charts.

On the "visitor_data" tab, select click the button called "Refresh Data". This will ensure that the data in the charts is the most up to date and ready to be presented. See Fig 15.

Fig 15


Step 4: Understanding and presenting your findings.

The blue tabs in the worksheet contain the charts which compile and display your data. We will now copy the charts into some presentation software. The charts are created from the data we have pasted into the "visitor_data" tab.

Visitor Numbers, see Fig 16.

Having got your chart showing the number of new visitors and repeat visitors, you might be wondering if your results are 'good' or 'bad'. The answer is that it really depends or your situation, so you need to consider a number of questions: How widely do you promote your site? Do you rely solely on word of mouth? Do you get much search engine traffic? If you don't invest much time or money in promotion offline or online (search engine optimisation, online advertising or e-newsletters) then you are likely to get hundreds, rather than thousands, of monthly visitors. If you are promoting your site extensively and still the visitor numbers are low, you need to review your activities. Are you optimising for the correct search terms, are you targeting the right audiences, are your 'ads' appealing to people? Is your site is designed to bring online sales or provide membership services? If so, you would hope to see a good proportion of visitors coming to your site on multiple occasions during any given month. Do you send an e-newsletter which drives traffic to your site? If you send it monthly, you might spot peaks in traffic for the months when your newsletter really caught your readers' attention. If your newsletter goes less frequently, you would hope to see a peak in the months when you send it.


Fig 16


Average page views, see Fig 17.

The number of pages your website visitors look at give you a good indication of how interesting they find your site. In our example, visitors look at over 9 pages in one month, which is excellent. A typical visitor will click 3 times to get to the information s/he is seeking. If people look at fewer than 3 pages on your site they are Getting all the information they need very quickly and easily The wrong sort of people (i.e., not your potential customers) so they filter themselves out quickly and leave or They are getting irritated because they can't find their way around the site or don't like what they see. To assess which scenario is taking place, take a critical look at the landing pages where people arrive and consider if they might get all the information they need in just a couple of clicks. Review your promotional activity and consider who you are attracting - are they genuine potential customers or people who've misinterpreted your advertisements? Also, look at the bounce rates for the individual landing pages. If the bounce rate is high, people are probably getting fed up and leaving the site very quickly.

Fig 17


Bounce rates, See Fig 18.

Your bounce rate is made up of people who come to your site and leave almost instantly. The average bounce rate is a broad brushstroke indicator. You are very unlikely to have an average below 20% but if it's above 30-35%, you need to investgate. Look at the individual landing pages and review the bounce rates for different sources of traffic. This part of the analysis links quite closely to the work on understanding the average page views (above). As you make changes to your site and your promotion, use your average bounce rate to indicate whether or not your efforts are taking you in the right direction. If your bounce rate increases, stop what you're doing and change your strategy.

Fig 18



Goals, See Fig 19.

You can set up goals in Google Analytics to measure the number of people who have taken specific actions on your website. Typical goals are things like signing up to your newsletter, visiting your online shop, buying from your online shop, signing into an account, opening an account, etc. Monitoring the number of goal conversions you achieve each month is an excellent way to monitor how well targeted your promotional activity has been and how effective your website is at 'converting' people once they arrive. Normally, we like to see 1- 3% of visitors fulfiling our goals. If your site falls below this level, begin making tweaks both on and off your website to see if you can attract more of the right kind of visitor and turn those people into profitable customers.

Fig 19



back to top		View our tips archive.