The Only World-Standard SEO Software

Download Now
SEO PowerSuite
SEO PowerSuite Hot-new version
Supported OS

5 Custom Google Analytics Reports: Track Changes in Traffic, Page Views, Bounce Rates, etc.

November 4th, 2010 | Link-Assistant.Com | Posted in category Analytics Search Engine Optimization

custom-google-analytics-reports

If you know anything about SEO you know it's not about rankings. And it's not about traffic either. No sir, SEO is all about sales/downloads/subscribes/comments/you_name_it. Getting decent rankings and traffic is part of the deal, but what really matters is how these people act on your site once they land there.

That said, it becomes obvious that tracking and analyzing your traffic is critical. And one of the best tools for the job is…? Right, Google Analytics. Love it or hate it, but it lets you do a lot of things and it's free, - the two features that put it on top of many webmasters' lists.

We sure know the major metrics to keep an eye on: from visits and page views to bounce rates and time on site. But what's even more important is how they change over time.

Case in point. What does 5,000 visitors a month tell you? Not much really, except that the site has decent/poor traffic depending on what your personal scale for traffic is. Now if we tell you the site has gone from 500 visits a month to 5,000. That same 5 thousand becomes a totally different number, doesn't it? Now you know the site is on the uptake and you have a reasonable right to expect that figure to keep on growing.

Keeping track of the change


Now the problem with Google Analytics is, while it does a good job letting you compare any metrics against a previous time period, it doesn't let you sort the results by the % change.

Suppose you want to see the keywords that started to bring you less search traffic, unfortunately there's no way to get a handy filter for that in Google Analytics. But don't worry, here's a workaround that lets you create custom Google Analytics reports with data filtered and sorted the way you want.

Custom Google Analytics Reports

The trick is pretty simple. All you need here is an Excel spreadsheet, a macro (see below) and a Google Analytics account with some data in it of course. And here's how you use them.

Custom Google Analytics Report #1: Sources Bringing Less/More Traffic

First let's see how you can quickly find out what sources have started bringing less traffic to your site.

1.       Log into your Analytics account and head over to Traffic Sources > All Traffic Sources

2.       Select the date ranges you want to compare and click Apply.

custom-google-analytics-reports-2

3.       Export the report as CSV

Note: Google Analytics will only export as many sources as are selected to be displayed. So if you want to export all the sources add &limit=20000 (up to 50000) at the end of the URL and hit return before you do the export. You won't see any changes in the online report, but you'll get all traffic sources in the CSV file.

custom-google-analytics-reports-4

Now hit export.

custom-google-analytics-reports-3

4.       Open the .csv file you've downloaded in Excel. Then go to View Tab > Macros

custom-google-analytics-report-5

5.       Copy and paste the following code into the macro creation window and press the green Run button (hitting F5 will work just as well):

Sub custom_report_1_traffic_sources()

maxRecords = 65536 ' Change this in case you've got more records in your Excel worksheet
j1 = 1

Set oldSheet = Sheets(1)
' Creating a new worksheet
Set NewSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))

' Setting the worksheet's header
NewSheet.Cells(1, 1).Value = "Source"
NewSheet.Cells(1, 2).Value = "% Change"
NewSheet.Cells(1, 3).Value = "Visits Before"
NewSheet.Cells(1, 4).Value = "Visits After"
NewSheet.Cells(1, 5).Value = "Difference"

For i1 = 1 To maxRecords
If oldSheet.Cells(i1, 1) = "% Change" Then
' Filling in the newly created sheet
NewSheet.Cells(j1 + 1, 1).Value = oldSheet.Cells(i1 - 3, 1)
NewSheet.Cells(j1 + 1, 2).Value = oldSheet.Cells(i1, 2)
NewSheet.Cells(j1 + 1, 3).Value = oldSheet.Cells(i1 - 1, 2)
NewSheet.Cells(j1 + 1, 4).Value = oldSheet.Cells(i1 - 2, 2)
NewSheet.Cells(j1 + 1, 5).Value = NewSheet.Cells(j1 + 1, 4).Value - NewSheet.Cells(j1 + 1, 3).Value

j1 = j1 + 1
End If
Next i1

' Applying sort
With NewSheet.sort
.SortFields.Clear
.SortFields.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:E")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Just adding some beauty
' Setting bold fonts for headers
NewSheet.Range("A1:E1").Font.Bold = True
' Colorizing the Difference column
Range("E:E").Select
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)

End Sub

6.       Switch back to the Excel spreadsheet to view your report.

You'll get a neat report with all your traffic sources and the change in the traffic they've generated over your specified period.

sample_custom_google_analytics_report

Changing the sorting options:

By default the macro sorts the data by the Difference column from smallest to largest. If you want to reverse the sorting and get the sources that are up on traffic at the top simply edit the following line in the macro code:

.SortFields.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscendingDescending, DataOption:=xlSortNormal

Coloring Options:

The coloring option only works in Excel 2007 or higher, if you're using an older version or simply don't want the coloring, remove this piece of code:

' Colorizing the Difference column

Range("E:E").Select

Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)

What do you do with this data?

As SEOs we're used to dealing with loads of data daily. The point here is not to become a search junky with fingers blistered from hitting the refresh button every second to see if there are any new stats up. You need to have a clear vision of how to analyze the data and most importantly how to act on it.

So you see that a certain source has been down on traffic recently. What would be you reaction? First you need to analyze what kind of source it is.

  • If it's a site you're advertising on, you would probably want to find out whether the traffic to that site is shrinking. You can do this by either using a special service like Compete or by contacting the webmaster. If it's not the reason, probably your ad is not performing as well as it used to and you should try a new one.
  • A thread on a forum where your product/service was being discussed died out? Go ahead and bump it by answering a question or shooting one.
  • In certain cases radical traffic drops are natural and expected. The effect of a viral social media campaign for instance will usually be short lived, so you don't try and resurrect it, you simply get another one ready.

Acting on the data is critical, always remember that. If you gather the data, analyze it and don't do anything - you're just wasting your time.

Custom Analytics Report #2: Keywords Bringing Less/More Traffic

1.       Go to Traffic Sources > Search Engines

2.       Pick your date range

3.       Add the keyword dimension to the report

custom-google-analytics-reports-7

4.       Export (don't forget to add &limit=20000 before the export to pump all of the data) and apply the following macro

Sub custom_report_2_keywords()

maxRecords = 65536 ' Change this in case you've got more records in your Excel worksheet
j1 = 1

Set oldSheet = Sheets(1)
' Creating a new worksheet
Set NewSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))

' Setting the worksheet's header
NewSheet.Cells(1, 1).Value = "Source"
NewSheet.Cells(1, 2).Value = "Keyword"
NewSheet.Cells(1, 3).Value = "Visits Before"
NewSheet.Cells(1, 4).Value = "Visits After"
NewSheet.Cells(1, 5).Value = "Difference"

For i1 = 1 To maxRecords
If oldSheet.Cells(i1, 1) = "% Change" Then
' Filling in the newly created sheet
NewSheet.Cells(j1 + 1, 1).Value = oldSheet.Cells(i1 - 3, 1)
NewSheet.Cells(j1 + 1, 2).Value = oldSheet.Cells(i1 - 3, 2)
NewSheet.Cells(j1 + 1, 3).Value = oldSheet.Cells(i1 - 1, 3)
NewSheet.Cells(j1 + 1, 4).Value = oldSheet.Cells(i1 - 2, 3)
NewSheet.Cells(j1 + 1, 5).Value = NewSheet.Cells(j1 + 1, 4).Value - NewSheet.Cells(j1 + 1, 3).Value

j1 = j1 + 1
End If
Next i1

' Applying sort
With NewSheet.sort
.SortFields.Clear
.SortFields.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:E")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Just adding some beauty
' Setting bold fonts for headers
NewSheet.Range("A1:E1").Font.Bold = True
' Colorizing the Difference column
Range("E:E").Select
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)

End Sub

For more details and advice on how to restore your traffic see this post.

Custom Analytics Report #3:  Pages with increase in Bounce Rates

1.       Go to Content > Top Landing Pages

2.       Export and apply this macro

Sub custom_report_3_bounce_rate()

maxRecords = 65536 ' Change this in case you've got more records in your Excel worksheet
j1 = 1

Set oldSheet = Sheets(1)
' Creating a new worksheet
Set NewSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))

' Setting the worksheet's header
NewSheet.Cells(1, 1).Value = "Source"
NewSheet.Cells(1, 2).Value = "Bounce Rate Before"
NewSheet.Cells(1, 3).Value = "Bounce Rate After"
NewSheet.Cells(1, 4).Value = "% Change"

For i1 = 1 To maxRecords
If oldSheet.Cells(i1, 1) = "% Change" Then
' Filling in the newly created sheet
NewSheet.Cells(j1 + 1, 1).Value = oldSheet.Cells(i1 - 3, 1)
NewSheet.Cells(j1 + 1, 2).Value = oldSheet.Cells(i1 - 1, 4)
NewSheet.Cells(j1 + 1, 3).Value = oldSheet.Cells(i1 - 2, 4)
NewSheet.Cells(j1 + 1, 4).Value = Format(oldSheet.Cells(i1, 4), "Percent")

j1 = j1 + 1
End If
Next i1

' Applying sort
With NewSheet.sort
.SortFields.Clear
.SortFields.Add Key:=Range("D:D"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A:D")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Just adding some beauty
' Setting bold fonts for headers
NewSheet.Range("A1:D1").Font.Bold = True
' Colorizing the Difference column
Range("D:D").Select
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)
cfColorScale.ColorScaleCriteria(1).FormatColor.Color = RGB(0, 176, 80) 'green
cfColorScale.ColorScaleCriteria(2).FormatColor.Color = RGB(255, 235, 132) 'yellow
cfColorScale.ColorScaleCriteria(3).FormatColor.Color = RGB(248, 105, 107) 'red

End Sub

What do you do with this data?

There are two reasons why your bounce rates might have increased:

a.       Your content have become stale, was proved to be incorrect or is simply not informative enough anymore (which is all kinda synonymous to stale ;) )

If that is the case, you go and update your content.

b.       Your visitors have changed: which most likely means that the major sources sending traffic to this page are different now.

If that's your problem, visit these sites, see what kind of people hang out there and see how you can adapt your content to better meet your needs.

Maybe you allowed yourself some rude language in one of your latest posts and are now getting to rank for all kinds of human body parts. See how you can convert the new kind of traffic, and if you fail to find a way – get rid of it. You don't want the traffic that's not interested in whatever you offer on your site. It only raises your hosting fees.

Custom Analytics Report #4: Pages Getting Less/More Traffic

1. In the Dashboard go to Content > Top Landing Pages

2. Select your Date Ranges for comparison

3. Add the Source dimension (just like we added the Keyword dimension in the previous report)

4. Export and apply this macro

Sub custom_report_4_entrances()

maxRecords = 65536 ' Change this in case you've got more records in your Excel worksheet
j1 = 1

Set oldSheet = Sheets(1)
' Creating a new worksheet
Set NewSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))

' Setting the worksheet's header
NewSheet.Cells(1, 1).Value = "Pagee"
NewSheet.Cells(1, 2).Value = "Source"
NewSheet.Cells(1, 3).Value = "Entrances Before"
NewSheet.Cells(1, 4).Value = "Entrances After"
NewSheet.Cells(1, 5).Value = "Difference"

For i1 = 1 To maxRecords
If oldSheet.Cells(i1, 1) = "% Change" Then
' Filling in the newly created sheet
NewSheet.Cells(j1 + 1, 1).Value = oldSheet.Cells(i1 - 3, 1)
NewSheet.Cells(j1 + 1, 2).Value = oldSheet.Cells(i1 - 3, 2)
NewSheet.Cells(j1 + 1, 3).Value = oldSheet.Cells(i1 - 1, 3)
NewSheet.Cells(j1 + 1, 4).Value = oldSheet.Cells(i1 - 2, 3)
NewSheet.Cells(j1 + 1, 5).Value = NewSheet.Cells(j1 + 1, 4).Value - NewSheet.Cells(j1 + 1, 3).Value

j1 = j1 + 1
End If
Next i1

' Applying sort
With NewSheet.sort
.SortFields.Clear
.SortFields.Add Key:=Range("E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:E")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Just adding some beauty
NewSheet.Range("A1:E1").Font.Bold = True
' Colorizing the Difference column
Range("E:E").Select
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)

End Sub

What do you do with this data?

Basically this is the same report as the first one, only from a different perspective. Instead of looking at who's sending you less traffic, you get to know what pages of your site are getting less traffic. Otherwise you act just the same: you find the reason for the traffic loss and seek ways to eliminate it.

Custom Analytics Report #5:  Pages Getting Less Time on Site

1.       Go to Custom Reporting > Manage Custom Reports

custom-google-analytics-reports-1

2.       Click Create New Custom Report in the upper right-hand corner

3.       Drag the blue Average Time on Page box from the Metrics section (Site Usage group) and drop it into the vacant Metric box on the right

4. Drag and drop the Page dimension (Content section) into the vacant dimension box on the right.

5. Click Create report

6. Export and apply the following macro

Sub custom_report_5_time_on_page()

maxRecords = 65536 ' Change this in case you've got more records in your Excel worksheet
j1 = 1

Set oldSheet = Sheets(1)
' Creating a new worksheet
Set NewSheet = Sheets.Add(After:=Worksheets(Worksheets.Count))

' Setting the worksheet's header
NewSheet.Cells(1, 1).Value = "Page"
NewSheet.Cells(1, 2).Value = "Time on Page Before"
NewSheet.Cells(1, 3).Value = "Time on Page After"
NewSheet.Cells(1, 4).Value = "Difference"

For i1 = 1 To maxRecords
If oldSheet.Cells(i1, 1) = "% Change" Then
' Filling in the newly created sheet
NewSheet.Cells(j1 + 1, 1).Value = oldSheet.Cells(i1 - 3, 1)
NewSheet.Cells(j1 + 1, 2).Value = oldSheet.Cells(i1 - 1, 2)
NewSheet.Cells(j1 + 1, 3).Value = oldSheet.Cells(i1 - 2, 2)
NewSheet.Cells(j1 + 1, 4).Value = NewSheet.Cells(j1 + 1, 3).Value - NewSheet.Cells(j1 + 1, 2).Value

j1 = j1 + 1
End If
Next i1

' Applying sort
With NewSheet.sort
.SortFields.Clear
.SortFields.Add Key:=Range("D:D"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:D")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Just adding some beauty
NewSheet.Range("A1:D1").Font.Bold = True
' Colorizing the Difference column
Range("D:D").Select
Set cfColorScale = Selection.FormatConditions.AddColorScale(ColorScaleType:=3)

End Sub

What do you do with this data?

There's really no golden rule to evaluate time on page. You can't say the more the better. If your page is a two-minute read and visitors spend 10 minutes on it, then you should probably look for a clearer way to express your thoughts.

However, if people are spending half the time on your content than they used to, it's probably not interesting/funny/useful anymore and it might be about time you updated it.

Create your own custom reports

As you prolly understand this method opens up unlimited possibilities of creating custom reports. Whatever metrics it is you want to watch, you can effectively do it with a spreadsheet and a little macro code.

Want to know what keywords are bringing less traffic and to what pages? No problems, simply select the Keywords and Landing Page as your preferred dimensions in Google Analytics and you're good to go. Interested in how the screen resolutions of your audience have changed over the last year? There's a way to do it as well.

Now we bet you can figure out how to create custom reports with the metrics that matter to you from now on. Still if you need any help editing the macro or have any other questions, feel free to ask us either here in the comments, or by contacting us at info[at]this-comain[dot]com (sorry for this conspiracy, the spammers have gotten really bad lately).



back to SEO blog