How To Track Clicks from your MS Word Resume

January 5th, 2010

Like many people with a technical background, I have lots of links in my resume to companies I have worked for and projects I have completed. When I send out a resume I would like to know whether people click on the links in the resume, but normally I can only get stats for sites I control (using Google Analytics Campaign Tracking). By using a URL shortening service I can embed links that are specific to the resume that I sent, and I can track every click on that link from that specific resume. The only problem here is that creating those links is time consuming, especially if you send out lots of resumes and/or have lots of links in your resume.

I created a macro that can automatically generate trackable links with one click. You need to be somewhat technical to use this macro, and I have only tested it in Word 2007 (please leave comments on whether it works for you in other versions). The macro is simple: it contacts a URL shortening service (either Cligs or Bitly at your option) and looks through all the links in your resume and shortens them using your account (so that you can track them).

You will need three things before you can use this VBA code:

  1. A copy of your resume with standard hyperlinks in Microsoft Word
  2. An account at either Cligs or Bitly
  3. An API Key for Cligs or Bitly

The macro also includes a place for you to enter Google Analytics campaign tracking which will help keep stats for sites you control (if you use Google Analytics).

The parameters at the top of the macro are things you need to set up before running the macro on a document. If you are using Bitly, then enter your Login and APIKey in the variables at the top. If you are using Cligs, enter your APIKey in that variable (leave the other API blank). If you are going to use Google Campaign tracking, then enter in those variables (leave them all blank otherwise).

If you are comfortable in VBA programming you can just create a new macro attached to your resume and paste the code below. You can also download a sample Word 2007 document with the macro already built in. Either way you will need to run the macro from your Word document once you have the variables set up. Note: It’s a good idea to delete the macro from the document once you have run it. Otherwise people opening the document will get a macro security warning when they open the document.

One other note: using a URL shortening service for your links could result in non-operative links if the URL shortening service shuts down (which happened to cligs before they were acquired). So use at your own risk. I also might be willing to add other URL shortening services that might be more reliable (and have an API).

Here is a short (4 min) screencast of how to use the macro. In the example I use Cligs, but the same process applies for using Bitly. Please comment with any questions.

Sub URLShortener()
'
' URL shortener/tracker
' Created by Dave Sohigian http://dave.sohigian.com 1/5/2010
' For more information go to http://www.techdemoguy.com/?p=192
' License: Creative Commons Attribution Share-Alike - http://creativecommons.org/licenses/by-sa/3.0/
'

    'Variables you should enter. Enter either BitlyLogin AND BitlyAPIKey
    'OR CligsApiKey, depending on which service you will use.
    'If you want to track the Google Campaign, then enter those three
    'variables. Otherwise leave the Google variables blank

    BitlyLogin = ""
    BitlyApiKey = ""
    CligsApiKey = ""
    GoogleCampaign = ""
    GoogleMedium = ""
    GoogleSource = ""
 
    If (GoogleCampaign <> "") Then
        GoogleQueryString = "?utm_source=" & GoogleSource & "%26utm_medium=" _
        & GoogleMedium & "%26utm_content=1%26utm_campaign=" & GoogleCampaign
    Else
        GoogleQueryString = ""
    End If
 
    'Loop through all of the hyperlinks and shorten the URLs

    For i = 1 To ActiveDocument.Hyperlinks.Count
        ApiUrl = ""
        LinkText = ""
        ' select the next hyperlink
        ActiveDocument.Hyperlinks(i).Range.Select
        ' store the name of the hyperlink
        LinkText = Selection.Range.Text
        ' store the url of the hyperlink
        LinkUrl = ActiveDocument.Hyperlinks(i).Address
 
        ' Make sure it is not a bit.ly or cligs url (would error out)
        If (Left(LinkUrl, 13) <> "http://bit.ly") And (Left(LinkUrl, 13) <> "http://cli.gs") Then
          ' Set up the API object
          Set ObjHttp = CreateObject("MSXML2.XMLHTTP")
 
          If (BitlyApiKey <> "") Then
            ' Create the API URL for Bitly
            ApiUrl = "http://api.bit.ly/shorten?version=2.0.1&login=" _
            & BitlyLogin & "&apiKey=" & BitlyApiKey & "&history=1&format=text&longUrl=" _
            & LinkUrl & GoogleQueryString
          ElseIf (CligsApiKey <> "") Then
            ' Create the API URL for Cligs
            ApiUrl = "http://cli.gs/api/v1/cligs/create?key=" & CligsApiKey _
            & "&appid=ResumeStats" & "&url=" & LinkUrl & GoogleQueryString
          End If
 
          If (ApiUrl <> "") Then
            ' Setup the call to the REST API
            ObjHttp.Open "GET", ApiUrl, False         ' False indicates the call is synchronous - wait for URL Result
            ' Send the request
            ObjHttp.send
 
            ' Get our results (the shortened URL) if the response was okay
            If (ObjHttp.StatusText = "OK") Then
                StrResult = ObjHttp.responseText
            Else
            'Otherwise just keep the original URL
                StrResult = LinkUrl
            End If
 
          Else
            StrResult = LinkUrl
          End If
 
          ' Update the Hyperlink in the document with the shortened URL
           If (LinkText <> "") Then
            'If it is a text link, then keep the Link Text
            ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
              StrResult, SubAddress:="", ScreenTip:=LinkUrl, TextToDisplay:=LinkText
          Else
            'Otherwise don't change the Link Text (if it is a linked image it will have "  " as the selection text)
            ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
              StrResult, ScreenTip:=LinkUrl
          End If
        End If
 
    Next i
 
End Sub

Learning, Software Tips

  1. stephanunrau
    March 9th, 2011 at 13:37 | #1

    Hey, I updated the code a bit and see you probably have too! http://stephanunrau.wordpress.com/?p=13

  1. No trackbacks yet.