Putting Last Touch Attribution to Rest

Campaign Attribution: Using Webtrends Visitor Data Mart to get weighted multi-touch attribution results.

How Webtrends Visitor Data Mart (VDM) makes multi-touch attribution easy

(From Rocky – We met Gary Kralicek last month and he had some great things to say about campaign attribution.  An expert user of both Webtrends Analytics and Webtrends Visitor Data Mart (now sometimes called Segments), he was kind enough to put some of his experience into this guest post.)

Limitations of Last Touch Attribution in Webtrends Analytics

We’ve all been there.   We’ve all been asked some form of this question from marketing:  How are my campaigns performing?

If we’ve been good and faithful Webtrends analysts, we’ve been careful to ensure all of our incoming campaign traffic included the WT.mc_id parameter, populated our campaigns.csv file and enabled the Campaigns report or any number of custom campaign reports in Webtrends Analytics.   These reports typically assign full credit to the last campaign before conversion.  These reports provide some value and are certainly better than nothing, but they fail to tell the full story of a customer’s journey to conversion.

Let me start off by saying that I feel your pain.

After years of saying, “that’s the best we’ve got” in reference to our last touch campaign reporting in a world of long sales cycles over multiple visits, I’m happy to report that there is hope even for us Webtrends analysts.

Setting the Stage – A Lifelike Example

Before we jump in to a Visitor Data Mart Solution, let’s look at a lifelike scenario for an online retail site and explore how a purchase would be represented in Webtrends Analytics.

Bob (a fictional shopper) would like to buy some Blue Widgets and is shopping online.  Over the course of 5 days, Bob visits your site 5 times as follows:

  1. March 1st – Paid Search Brand Keyword
  2. March 2nd – Organic Google Search – Keyword Not Provided (of course)
  3. March 4th – Email Click through
  4. March 4th – Organic Google Search – Keyword: Acme Blue Widgets
  5. March 5th – Direct Visit (No Referral)

On that 5th visit, Bob makes a purchase of 100 Blue Widgets.

Here’s how the Traffic Source, Most Recent Organic Search Engine and Campaigns reports represent that purchase in WT Analytics.

Traffic Source Report

The traffic source report will show the conversion only on the visit in which the conversion occurred, Direct Traffic in this example.


Most Recent Organic Search Engine

The Most Recent Organic Search Engine report will give full conversion credit to the last organic search that occurred prior to sale.


Campaigns Report

The campaigns report will give full credit to the last “campaign” visit, ignoring organic search or other sources.


These reports seem great, especially to the individual managers who manage the various channels.  For instance, the SEO person will celebrate a sale attributed to Organic Search (most recent organic search report).  In the same way, the email manager will celebrate a sale being attributed to an Email campaign (campaigns report).  Finally, the old school guy who thinks marketing investments are silly will cite the Traffic Source report and say that Direct Traffic got the sale.

The problem in this example arises when the VP that oversees these channel managers begins to add up their results and compare them to an online sales report.  In this example, the sum of the channel manager “victories” would be 3 and the online sales report would show 1 sale.  Assuming that VP is pretty good at math, he or she will quickly reach the conclusion that 3 does not equal 1.  This runs the risk of discrediting your whole web analytics program because that VP simply does not believe the data.

  • The only way to deal with this situation is to apply an appropriate attribution model to assign credit where credit is due.

Visitor Data Mart – a Brief Introduction

I recognize that many readers of this blog may not be familiar with Webtrends Visitor Data Mart (VDM) so a brief introduction of VDM is appropriate and necessary before moving on.  VDM is a Webtrends product that makes use of the same Webtrends SDC Logs as Analytics.  The difference is in the way VDM processes the log data.  While Analytics populates reports in profiles and makes some visitor history available, VDM populates database tables that represent events.  Each event has a Visitor ID, Visit ID and a Timestamp (very important as you will see) along with a number of additional attributes that describe the event.  For example, a purchase event includes the product(s) that were purchased, price, invoice number, etc.

A few examples of VDM events that come out of the box are:

  • Ad Event View (aka Campaign Visit)
  • Product View
  • Cart Add
  • Purchase Event
  • Search Click through
  • Scenario Step Event

Like Webtrends Analytics, VDM can also be used to capture information about custom events like lead form submissions, newsletter signups and any other interesting event that happens on your website.  In addition to events, Visitor Data Mart also keeps a good amount of Visitor level data like Lifetime Value, Lifetime Page Views, Recency and Frequency information.

  • Basically, Visitor Data Mart gives us a Visitor-centered view of website events over time and across visits.

 Putting it all together – Using VDM to Enable Multi-Touch Attribution

At its base, multi-touch event attribution requires you to have the ability to tie visits and events to the same visitor and to put them in chronological order.  Once you can identify all of the visits that belong to a visitor in the order they occurred AND you understand the source of each visit (direct, campaign, organic search, referral, etc.), you can then apply whichever attribution model you choose.  This is done to give proper credit to those sources (campaigns, organic search, referrals, etc.) that contributed to a conversion (sale, lead submit, newsletter subscription, etc.)

Choosing your attribution model and building the process to apply it to the data may be a discussion for another time and each has much to consider but I will very briefly discuss.  Most simply, an attribution model will assign full or partial credit to the source or source(s) that led to conversion.  Things to consider include amount of time between campaign visit and conversion, whether you want to give higher weights to first and/or last touch, how to divide remaining credit to middle touches, adjust for channel, etc.  Attribution models can be simple or complex and can easily warrant their own discussions. Regardless of which attribution model you choose, you are essentially striving to give proper credit where credit is due.

VDM view of Bob’s Purchase

Let’s look at our example again and see it the way VDM would view it.  Please be aware, we are using VDM as an analysis enabler, not a reporting tool here.


Each of Bob’s Visits is recorded in the Visit table with a Visit ID.  Bob is also recorded in the Visitor table with a Visitor ID, which corresponds to Bob’s Webtrends visitor id from the Webtrends cookie on his browser.  In addition to each Visit, various events are also recorded.  In this case, we see 2 Ad Events and 2 Organic Search Events.  Each event contains the Visitor ID, Visit ID, Timestamp and various additional descriptive attributes about the event.

As we said, on the 5th visit, Bob made a purchase and it was recorded in a Purchase Event, making use of the same tags as would be used for WT Analytics – WT.pn_sku, WT.tx_u, WT.tx_e, etc.

A simple representation of the purchase data is shown here:


At this point, we have identified the relevant events that make up Bob’s history.

Applying Your Attribution Model to the Data

For example purposes, I will illustrate a simple Attribution approach that will allocate 50% of the credit to the last non-direct touch prior to conversion and evenly distribute the remaining 50% to the preceding non-direct visits with a maximum consideration window of 30 days.

This illustration walks us through the basic steps that you could follow to apply an attribution model.

In order to perform these steps, you will likely have to extract the data out of the VDM database and write a program (in SAS for example) or get fancy with Excel to clean and manipulate the data. This section is not depicting a functional aspect of Visitor Data Mart.

Step 1 – Assemble Your Data

In this step, you must build your working dataset.  In this case, we will create a simple data set that combines the purchase with the other events in chronological order, latest first.  The data is pulled from the VDM database using SQL Queries.

Our sample data looks like this at this point:


Step 2 – Discard Rows That Do Not Qualify for Credit

In this step, we will be removing rows that do not qualify to receive credit.  In our example, we will remove any direct visit rows (since our model specifies non-direct visits only) and any rows whose event timestamp falls outside the consideration window.


Step 3 – Apply Your Model’s Rules to the Data

In this step, we will divide up the credit for the sale according to the rules stated in our model.  The “pseudo-logic” for this follows:

For each invoice:
     RowCount = # of Rows getting Credit for that Purchase
     For i=1 to RowCount {
	If i=1   
		If RowCount = 1
			RowCredit = .5
			RowDollars = PurchaseTotal *.5
		RowCredit = .5 / (RowCount-1)
		RowDollars = .5 * PurchaseTotal / (RowCount-1)

After applying this logic to our data, we can see that the most recent qualifying event received 50% credit for the conversion and the other 3 qualifying events each received 16.67% credit for the conversion.  For ROI calculations, we also divided the purchase revenue according to those percentages as well.  The results are shown here:


A New Type of Reporting Required

There is no report in Webtrends that can reflect the post-attribution distribution of credit so you will need to build custom reports and/or dashboards to distribute.  You will also need to educate your customers about “full sale equivalents” to represent the credit given to your various channels.  Obviously we showed an example that contains only one purchase.  This method still works if you have thousands of purchases.  As seen in our example, email received 16.67% of a sale.  It may receive 50% of another sale, 10% of another and on and on and on.  At the end of the month, you add up the values in “Row Credit” to determine how many full sale equivalents the channel accounted for.

Here is a sample attribution report that would be useful for channel managers as well as executives.  This type of reporting would not be possible without applying an attribution model to your data.


Concluding Thoughts

The purpose of this post is to give a brief introduction to VDM and a brief explanation of how it can be used to drive a multi-touch event attribution solution.

Hopefully it gives a little insight into some of the possibilities that VDM enables and as always, I hope it got the gears turning in your head.  This post just scratches the surface of what VDM offers.   Unfortunately there is additional cost to license VDM, but in many cases the cost can easily be justified by the richness of analysis it enables.

I invite you to share your own experiences with solving campaign attribution problems in a Webtrends environment and/or other analysis problems you’ve been able to solve using VDM.