How deeper excel knowledge changed my workshops - about the potential of Google Analytics

8. August 2017
|In Howto
|By Franziska Kissling

You Content Strategists know the problem: You sit there together with your stakeholder. Discussing about numbers of google Analytics. But somehow you feel it would be so much easier if you could show them the numbers DIRECTLY in the sitemap these stakeholders are familiar with.

This is possible. A challenge in IT infrastructure gave me the possibility to investigate deeper into Excel videos on Youtube. And I would like to introduce you to a method which really changed my style of workshops. I had so many constructive discussions about the pages which are trouble-making. And due to automatization, it really saved a lot of time.
Here is a short description of the steps to prepare your analysis. Everything is also described in the video.

Export URLs out of Hyperlinks

  • Export your metrics for all pages from google analytics. I usually export up to 5000 items, but no worries, excel can deal with that
  • Copy your sitemap into excel
  • Open the developer tools, Klick on the visual basics and insert a modul with the code given to you below
  • Return to the Excel to the left of your sitemap. Click in der desired cell and add the formula =HyperlinkAdresse(A1) The value of A1 is the cell, where your hyperlink is placed
  • Drag the formula down to the end of your sitemap. Now you extracted all your URL
  • Click on the row, copy the values (with the formula) and paste it with the function “only values”

Prepare your Google data

  • You realize that the values from google just include the last bit of your URL /naturerlebnisreisen
  • To adapt it to the values you extracted from your sitemap, you need to replace the / with the domain of your website, e.g. https://www.pronatura.ch/

Mix the corresponding numbers to your sitemap

  • Click in der desired cell and add the formula =SVERWEIS(L29;$A$4:$H$844;2;FALSCH) L29 stands for the cell with the Hyperlink as value. ;$A$4:$H$844; is the realm where you have your google analytics data – keep the sign $ so that the realm is fixed. ;2; stands for the value you want Excel to return, e.g. page clicks
  • Drag the formula till the end. Now you have all the corresponding metrics side by side to the sitemap of your website.

Prepare the analysis for your stakeholders

  • Copy the realm of interest into a new sheet, only the values, without formula (rightclick)
  • Insert a new column, ad IDs for each page in order to restore the hierarchy after the next step
  • Insert filters
  • Filter after page clicks or every other value you wish
  • Highlight the numbers you are interested in with a certain color
  • Then restore the hierarchy in the sitemap with the ID Filter

Now you have an edited sitemap, fresh prepared for good discussion. My stakeholders were quite glad about that opportunity to see the problematic “zones” of the website within the familiar structure.

Code Snippets

Code for Developer Tools

Function HyperlinkAdresse(Zelle As Range)
Dim Link As String
Application.Volatile
If Zelle.Hyperlinks.Count Then
Link = Zelle.Hyperlinks(1).Address
End If
HyperlinkAdresse = Link
End Function

Code to extract the URL out of Hyperlinks

=HyperlinkAdresse(A1)

Code to Lookup for Values under a certain realm

=SVERWEIS(L29;$A$4:$H$844;2;FALSCH)

3 Comments

Add comment