Validating Orders & Revenue

I recently received an e-mail from a blog reader who was having issues tying their Orders in SiteCatalyst to Orders in their back-end system. Here is a snippet from the e-mail:

I have a little issue in my own SiteCatalyst setup that I recently discovered. Sad for me I had trusted the number of Orders for each day’s Conversion Funnel and recently I decided to validate the numbers in SiteCatalyst against what our back-end system has. SiteCatalyst is 5%-10% understated each day which makes for a heck of a difference at the end of the month! I’d rather be understated than overstated, but can you give me some ideas where I should look first?

Unfortunately this is an all too common problem I hear out there. In this post I am going to share some ideas on how you can tackle this Order/Revenue validation issue head-on and make sure you can trust your critical Orders/Revenue data in SiteCatalyst.

Order ID eVar
If you have an online shopping cart, you should already be setting the s.purchaseID variable with a unique Order ID when an Order takes place on the website. This variable is used by SiteCatalyst to ensure Order uniqueness. Unfortunately, the downside of this variable is that it is not readily available in the SiteCatalyst interface. It is available in DataWarehouse but not in regular SiteCatalyst reports or Discover. Carmen Sutter (@c_sutter) has submitted an idea in the Idea Exchange to change this, but until then, I recommend that you set what I call an Order ID eVar variable. To do this, all you need to do is set the same value you pass to the PurchaseID variable to a custom eVar. This will allow you to see all Orders and Revenue by Order ID from within SiteCatalyst and Discover as you would any other eVar. Once you have done this, you can open up this new Offer ID eVar and add your Orders or Revenue Success Event as needed:

In the example above, we can see that most Orders have only one Order ID, which is what we want. However, in this case, we can see that one ID was counted twice. That may require some research and I like to schedule a report like the one above to be sent to me weekly so I can make sure nothing strange is going on.

Data Sources Setup
However, while adding an Order ID eVar is helpful in seeing if you are over counting Orders in SiteCatalyst, it won’t tell you if you are under counting Orders or how close your SiteCatalyst data is to your back-end systems. To do this, I recommend you use Data Sources. As a quick refresher, Data Sources allows you to import external data/metrics into SiteCatalyst (see post link for more details). In this case, I recommend that you import in a file from your back-end system into SiteCatalyst which contains your unique Order ID, the number of Orders (which should always be “1”) and the Revenue Amount. When you import data via Data Sources, you include the date that you want the data to be associated with so it doesn’t matter if you import the data on a daily, weekly or monthly basis, but the more frequently you upload it, the better so you can find issues quickly.

Here are step-by-step instructions on how to do this:

• Create the Order ID eVar described above
• Create two new Incrementer Success Events and name them “Back-End Orders” (Type=Numeric) and “Back-End Revenue” (Type=Currency)
• Create a new Data Sources upload template (ClientCare or Omniture Consulting can assist with this). You want to be sure to map the two new “Back-End” Success Events to the Data Sources template. Even more critical, is that you want to include the newly created Order ID eVar in the Data Sources template. If you do not do this, then you will not be able to see these two new Back-End metrics in the same Order ID eVar report that you have in SiteCatalyst (more on this later).

• When you are done, you should have a Data Sources template that looks something like this:

• Now all you have to do is work with your developers to have this file sent via FTP to the Data Sources FTP on a regular basis.

The Payoff
So by now, you are probably saying to yourself: “That’s a lot of work!” No argument here! However, hang with me as I share what the ultimate payoff is for doing this. As you recall, our primary objective was to see if our online Order and Revenue data was matching what our back-end systems indicated. Now that we have the Order ID eVar and two new “Back-End” Order and Revenue metrics, we have everything we need. This is where the fun begins and we put it all together!

All you have to do now is to open the new Order ID eVar report and add all of the relevant metrics. First, we will add the SiteCatalyst Orders and Revenue so we can see online Orders and Revenue by Order ID:

Next, we will add the two new “Back-End” metrics to the report and, since we were smart enough to include the Order ID eVar value in the Data Sources upload, SiteCatalyst knows which “Back-End” Order ID and dates line up with our online data:

Cool huh! As far as SiteCatalyst is concerned, these offline metrics are connected to your Order ID eVar values just as if they had happened online. Using this report, we can see if there are any differences between our online and offline data. In the example above, it looks like the “Back-End” system had an order with $2,350 in revenue that wasn’t captured online. Having this information makes it much easier to troubleshoot order submission issues. You can even use DataWarehouse or Discover (only if you use Transaction ID Data Sources) to break down Order ID by browser, domain, IP address, etc… to see if you can figure out what is happening. In addition, you can export this data to Excel and look at the totals to see how far off you are in general.

Finally, for the true SiteCatalyst geeks, you can create a Calculated Metric that divides Orders by Back-End Orders and/or Revenue by Back-End Revenue to see a trended % that each is off and set up Alerts to notify you if they deviate too much! When you take into account this level of assurance all of a sudden the Data Sources work above might not seem like all that much in the long run!

Final Thoughts
If you sell products online, nothing is more critical than believing in your key metrics. Even if you don’t sell online, the same principles here can be applied to lead generation forms, subscriptions or any other metrics you store in SiteCatalyst and also in your back-end systems.