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:
- 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!).
- Transaction ID – The ID associated with the original online sale
- Product Name/ID – Same value that is passed to the Products Variable during the original online purchase
- 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:
- 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
- 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!
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Adam,
Great post. I just wanted to add that the approach you detailed can also be used in verticals other than retail. We’re a financial services lead-gen site, and use data sources to upload downstream bounties. Some of these bounties may be erroneous or reversed, so we have a “negative adjustment” data source (absolutely dependent on Transaction ID). A calculated metric then nets the negative revenue events (one for volume, another for dollars) out of the positive adjustments.
Data sources are fantastic, but “garbage in, garbage in FOREVER;” so the return/reversal approach also allows you to back out incorrectly loaded data.
In any case, great blog, and really useful post
Cheers
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Hmm,
before I start pushing back a lot of external business data (such as returns) back into a Web Analytics tool – doesn’t it make much more sense to extract the web analytics data needed (such as channel, conversion, etc.) and pull it into my other Business intelligence tools?
Otherwise I’m at pushing back a lot of data into a web analytics tool, that is
a) not part of my secure internal IT environment
b) making me more dependant on the web analytics vendor month by month
I realize that it feels nice for web marketers and analysts to do also deeper business calculations on the web analytics tool – but I don’t consider pushing back all sorts of business data into WA tools the right solution.
Best, Bjoern
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Bjoern,
That is a good point and one I covered. My reasoning for having it in the web analytics tool is to improve the accuracy of web analysis. Web Analytics will not often be the “system of record” but keeping the data as close as possible is important to doing good web analysis…
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
John – Agreed! I used retail as the example as it is the easiest to grasp. I was debating about another post just on using Transaction ID/Data Sources to back out bad data, but most of the priciples would be the same as in this post. I believe the ability to remove data or have Data Sources not be “all or nothing” is in the Idea Exchange somewhere…Thanks!
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Where was this post a week ago!! I wish you could train customer care on this. I had a bad data issue that i had to retroactively clean up and had also been wanting to get return data in. I didnt find a single person in customer care to help me =-( just let me checks. Wound up figuring it all out on my own but it took a ton of time. Thanks for your post!
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
@Bjoern – since the web analytics tool is also often the main source of marketing campaign data, it’s very useful to have returns/cancels numbers in that system. If a particular email is really driving sales, but has a high return rate, we can act on the messaging and improve performance, or we can work harder to target products to our various email audiences (as Adam pointed out with paid search as an example). I know you can push that data into a BI system, but my marketers prefer to get their reporting in SiteCatalyst.
Plus, as a web analyst, it makes my job so much easier to not have as many caveats in my web analytics numbers. Being able to have straight “sales” data is fine, but to get closer and closer to actual revenue is so valuable.
Anyway, big plus on sharing the nuts and bolts, Adam. We add a few variables; returned units, canceled units, and canceled revenue (quirks of our system). Lets us pull return rate for our various brands/departments/product categories.
Great post.
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Hi Adam,
In your example template, each transactionID(purchaseID) is only responsible for 1 single product. What if user purchase multiple product in 1 single transaction? how would you structure the template? which field should be the primary key? Can a single field contain multiple product name if transactionID has to be unique.
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
It is my understanding that you would need a separate row for each product, but that you can have the same transaction ID multiple times. I will see if @benjamingaines can verify this with a comment here…
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Adam is right. You can use the same Transaction ID on multiple lines with separate products.
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Is it possible to data source negative revenue to reduce inflated revenue in SiteCatalyst? For example – revenue on 1/1/14 is $100,000 in SiteCatalyst, but it is supposed to be $10,000. Can I data source “-$90,000”?
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Adobe has traditionally discouraged using negative numbers in Data Sources. You would have to check with ClientCare to see if that has changed…
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
The images of this blog are not getting loaded. Developers console is also showing some errors for them. Can you please get this rectified?
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Hey Adam, Thanks for the info. But in real situation, Solution is not working well. What about the cases when we have partial cancellation? There are many cases in which one transaction id have mutiple SKUs and only few of them cancelled. Can you please me to sort out such cases in Omnuture?
regards
Lalit
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
Warning: strlen() expects parameter 1 to be string, object given in /home/analytics/public_html/wp-includes/formatting.php on line 3263
It is possible to do partial cancellations via the Products variable.