Consolidating Email Link Performance report links using Excel & VBA

My buddy Todd says, “If you’re writing VBA, you’re probably doing it wrong.” (He means you should be using a built-in Excel function instead.)

I’d probably go further: if you opened Excel, you're probably doing it wrong.☺ But it’s lots of people’s default data tool, so you’ve gotta roll with it.

Once you’re fated to use Excel, some things are easier to do in VBA than via nested 𝒇𝒙. Especially true of functions built into Windows itself that can simply be called from VBA, needing only a couple lines of custom code to bridge the 2 worlds.

Case in point: simple URL parsing, something even very experienced programmers get wrong. If there’s a time-tested URL parsing routine, use that, don’t roll your own.

The problem

Marketo’s Email Link Performance reports are (rightfully) precise. Link URLs are considered as a whole — host, path, query string, and hash — so if you have one or more query params that’re different per lead, all your links will be broken out separately.

Take this example, where the unique  cid query param (derived from the person’s SFDC ID, by the way) is added to enable website personalization:

The report is technically correct, of course, but you can understand why someone would want all those Link entries to be grouped together.

And it would also be cool if the rest of the query string were stripped off, too, since UTM params are probably the same for every link in a single email and/or not important in such reports. (Assumptions that aren’t true 100% of the time, but for the purposes of this post, they are!)

The solution

All Windows machines have a built-in library called MSHTML (Microsoft HTML). In brief, it’s a bridge to IE’s rendering engine, and it has what we need: a function to correctly split a URL into its protocol, port, host, path, query string (a.k.a. search), and hash.

So open up VBA (I do assume a basic comfort level with Excel’s VBA UI, even if you’re not a coder) and first go to Tools » References and check off Microsoft HTML Object Library:

Then paste this code into VBAProject » Modules » Module1:

Dim doc As New MSHTML.HTMLDocument

Public Function hostAndPathFromURL(ByVal URL As String) As String

    Dim location As MSHTML.HTMLAnchorElement

    Set location = doc.createElement("a")
    location.href = URL

    hostAndPathFromURL = location.hostname + "/" + location.pathname

End Function

That gives you the custom function hostAndPathFromURL to use as a formula:

Now you can group cells by the formula column (i.e. Data » Group, gonna assume you know how to do that part since I’m no master at it!) and get much neater reports.