Tracking Product Returns

If you sell products or services on your website, you are probably working diligently to be sure that you are tracking the appropriate Orders, Units and Revenue associated with each product sold (you may even be doing some advanced stuff like I described here). Doing this allows you to see all sorts of wonderful things, like what pages lead to sales and what online campaigns have better ROI than others. However, one formidable challenge that web analysts don’t like to talk about is Product Returns. How often have you bought something online only to ship it back or return it to a brick & mortar store associated with the website? If customers return products in significant enough numbers, all of the great online data you have collected may be inaccurate.

I have seen some companies apply a “rule of thumb (dumb?)” in which they discount sales by 20% across the board to account for returns, but how does that help you determine if a specific marketing campaign is good or bad when your SiteCatalyst reports only show the good? By not tying product returns directly to their corresponding online sales, your web analytic reports will be inherently flawed. The truth is that I have seen very few clients who have adequately addressed this issue, so I thought I would suggest an idea that I think is an appropriate way to deal with Product Returns. Even if you don’t sell things through a shopping cart, I encourage you to read this post as its principles are applicable to any situation in which you have an online success that later is retracted in some manner offline.

Tracking Product Returns
The best way to understand the tracking of Product Returns, is through an example. Let’s pretend that are a web analyst for Apple and a first time visitor comes to the website from the Google paid search keyword “ipod” and purchases two iPods for $50 each. In SiteCatalyst, when we open the Products report, we would see $100 for the Product labeled “ipod” and if we broke it down by visit number, the same $100 would be attributed to visit number one. So far, so good…

However, let’s imagine that one of these iPods was returned to a local Apple Store. Now our reality has changed. The paid search keyword and first visit combination has now only led to $50, but SiteCatalyst still shows $100. If we create calculated metrics to compare our Revenue per Marketing spend, suddenly our ROI just got cut in half, but this is not reflected in SiteCatalyst. This might cause us to misallocate marketing dollars to campaigns that look to be good at first, but in reality are not as profitable as others when Product Returns are added to the mix (especially if we automate Paid Search using SearchCenter!). I don’t know about you, but I certainly wouldn’t want to be the one telling my boss to invest in marketing campaigns that turn out to be “duds!”

So how do we fix this mess? In order to track Product Returns, you’ll need to re-familiarize yourself with the Transaction ID feature of SiteCatalyst (I suggest re-reading this post!). At a high level, Transaction ID allows you to set an ID associated with a transaction and later upload offline metrics that are dynamically associated with any Conversion Variable (eVar) values which were active at the time the Transaction ID was set (phew!). In this case, you need to ensure that you are setting a Transaction ID value when the original online sale takes place. By doing this, you create a “key” that will allow you to upload Product Return data later and back it out of its corresponding online sale. Keep in mind that you will have to work with your Adobe account manager to get Transaction ID set-up and you’ll want to be sure that the Transaction ID table persists for as long of a time frame that you require to upload return data (default is 90 days, but this can be extended).

Once you have Transaction ID enabled and have started passing Transaction ID’s for online purchases, the next step is to create a new “Product Return Amount” [Currency] Incrementor Event. Transaction ID uploads are similar to Data Sources and, as such, import data as Incrementor Success Events. Setting up a new Incrementor Event is easily done through the Admin Console.

Once you have Transaction ID set-up and your new Product Return Amount currency Success Event, you need to use Data Sources to generate a Product Returns template which you can populate and upload on a daily/weekly/monthly basis. This file will contain the following columns:

1. Date – I suggest you use the date that the original purchase took place, not the date of the return, so there is no lag time. NOTE: Please keep in mind that there is currently a SiteCatalyst restriction that you cannot upload a Transaction ID file that has dates spanning more than 90 days. You can upload dates that are more than 90 days old, but the date ranges for the entire file upload cannot be more than 90 days (kind of lame in my opinion!).
2. Transaction ID – The ID associated with the original online sale
3. Product Name/ID – Same value that is passed to the Products Variable during the original online purchase
4. Product Return Amount – This is the total $$ amount per product that is being returned

When you are done, your upload file might look something like this:

Using Product Returns in SiteCatalyst Reports
Once you have successfully uploaded some Product Return data, it is time to see how all of this looks in SiteCatalyst. To do this, open the Products report and add Revenue and our new Product Return Amount metrics. The report should look like this:

Next, we can create a Calculated Metric which subtracts the Product Return Amount from Revenue to create a “Net Revenue” metric as shown here:

Finally, since Transaction ID allows you to apply the eVar values that were associated with the original transaction to the new “return” transaction, you can even use Subrelation break downs for the Products report by Visit Number and see both Revenue and Returns (and the Calculated Metric) by Visit Number (pretty cool huh?)!

Orders & Units (Advanced)
For those that are a bit more advanced, I wanted to let you know that the above solution does not back out Orders or Units for Product Returns. Backing out Orders is a bit tricky since you may only want to remove the Order if the entire Order is returned. Units is a bit easier as you can simply create a second Incrementor Success Event for “Returned Units” as we did above. However, I suggest that you start with Revenue since most of your questions around Product Returns will be related to Revenue.

Finally, SiteCatalyst does provide an out of the box Data Sources template for Product Returns which can be found in the Data Sources Manager:

However, I have not used this template myself and I would have the following potential concerns:

1. I don’t believe that this template uses Transaction ID which can be problematic as you will be unable to use the Product Return metric with all of your pre-existing eVar reports
2. It looks like this template uses the same Revenue, Orders and Units Success Events to back out Product Return data. I feel like this can be a recipe for disaster if something goes wrong. With my approach, the worst case scenario (if you upload some bad Product Return data) is that your new Product Return metrics are temporarily off. If you use the standard Revenue, Orders and Units metrics, a mistake can be fatal and hidden amongst your normal online metrics (I never mess with Revenue, Orders or Units!).

For these reasons, I suggest you talk to your Adobe Account Manager if you want to pursue this route.

Final Thoughts
So if Product Returns are something that you have to deal with, the above is my suggested way to handle them. Those of you who work in Retail day in and day out may have come up with some other ways to deal with Product Returns, so if there are other best practices out there, please leave a comment here. Thanks!