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 that are built into Windows itself and 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.
.png)