How to cross-check and audit your revenue and sales conversions.
-
Why This Might Happen
-
Export Your Transactions from Your Order Source
-
Export Your Wicked Reports Order History
-
Compare the Results
-
Analyze and Interpret
-
What's left
-
Note for Infusionsoft Users
Note: These instructions apply if you have a single order source authorized in Wicked Reports. If you have multiple Order Sources authorized, please email support@wickedreports.com for additional guidance.
Why This Might Happen
There are a variety of reasons - some logical, some technical, and some logistical - why your revenue as reported by Wicked Reports may not exactly match the revenue reported by your CRM.
Some of the reasons revenue in Wicked Reports may not match revenue reported by your order source system include:
- Time zone differences between your source system and Wicked Reports
- How your source system calculates Net and Gross revenue as compared to how Wicked Reports does (e.g., For clients authorized with Shopify, our gross numbers will not match Shopify gross numbers as our gross numbers take the gross order total and subtracts discounts.)
- Orders entered for past sales might not be imported into Wicked Reports
- Orders that were changed or removed from the source system after the fact that aren’t removed from Wicked Reports
- Differences between Order Date (i.e., the date/time of the sale) and Order Create Date (i.e., the date/time the order was created in the source system)
- Handling of payment plans by the source system
- Clients’ Cash vs. Accrual reporting expectations
- Additionally, there are cases where there's just a bug in the integration, orders aren’t eligible for import, or something changed on the other end since it was implemented.
Overall, we're finding our ability to correctly handle refunds, returns, and failed payments is improving all the time.
The the first step is to figure out exactly where the problem is and identify some specific orders, by ID number, that can be investigated.
Export Your Transactions from Your Order Source
The first thing you need is a source list to compare against.
Your order source is usually your shopping cart or payment processor. Wherever your orders live before they are sent to Wicked Reports. This is your definitive order source and what we want to validate against.
Log into your order source and export all transactions for a little before and a little after the date range you want to validate.
Be sure to include the Order ID, Contact Email Address, Order Amount, Order Date, and Order Type (payment, refund, credit, failed billing . . .)
For example, if you want to validate revenue for December 2018, export transactions from November 27th to January 3rd. This will give you a little room for comparison in case you find the issue is a time zone or setting issue.
Export Your Wicked Reports Order History
First, navigate to the Customer LTV Report and click the Order / UTM search tab.
From here you're going to specify the desired date range. You can choose the exact range at this time because we're going to use the first and last transactions from this report to identify the exact start and end points for comparison with the file you exported from your Order Source.
Once you set the date range, make sure to unselect the option labeled Show ONLY Attributed Orders. Then click filter.
Once the report populates with all orders from the selected period. Click the Export to Excel button in the bottom-left corner of the report.
Compare the Results
Once you've downloaded your transactions you are ready to compare.
Start by sorting both files by transaction date.
Find the Start and End Points for Comparison
- With both files sorted by date, note the Order ID of the first order in the Wicked Reports file.
- Locate the corresponding Order ID in your source file. (For now, don't worry about the dates)
- Remove all prior orders from the source file.
Follow the same process for the last order.
- note the Order ID of the last order in the Wicked Reports file.
- Locate the corresponding Order ID in your source file. (For now, don't worry about the dates)
- Remove all following orders from the source file.
Analyze and Interpret Your Findings
The first thing you may notice is that the start and end dates for your Order Source transactions don't exactly match the date range you were trying to validate. They may be a couple hours earlier or later, sometimes longer.
This is usually due to timezone differences or possibly server configuration issues. On it's own it is not a problem and is nothing to worry about. If the following steps validate you will always see this variance for any given reporting period, but know that all the data is there and no transactions are being missed.
Validate the transaction counts.
The first thing to check is the number of transactions. Now that you've eliminated orders before and after the starting point of what Wicked Reports knows about, does the transaction count match. Good news if it does. You're off to a great start.
If it doesn't, you'll need to look deeper to find out which transactions are being included or excluded that shouldn't be. A side-by-side comparison is a good place to start. We would typically copy the order IDs from both files into a new spreadsheet, independently sort them alpha-numerically, and look for where they diverge.
As soon as you find an order that is in your order system, but not in Wicked Reports mark it down.
If you find this to be the case, you'll need to submit the order IDs to Wicked Reports for further investigation. Please zip up both files and email the file and a couple example product IDs for further investigation to support@wickedreports.com.
But what happens if the transaction counts match?
Validate the totals.
Again, Excel makes this very easy. Sum the order amount columns in each of the files and compare the totals.
If they don't match, you'll need to look deeper to find out which transactions are being over or under reported. Here again, a side-by-side comparison is a good place to start. We would typically copy the order ID and Order Total columns from both files into a new spreadsheet, independently sort them alpha-numerically by Order ID, and look for where they diverge.
It is unlikely that you will find the same Order ID in both files with different order totals. If that were to happen it would likely have to do with how refunds, discounts, or credits were handled by your Order Source.
More likely is that you'd find an Order ID is missing in one file and another Order ID is missing in the other, thereby making the order count match, but the order total not match.
As above, if you find this to be the case, you'll need to submit the order IDs to Wicked Reports for further investigation. Please zip up both files and email the file and provide a couple example product IDs for further investigation to support@wickedreports.com.
What's left?
Really, nothing. If your order counts and totals match for the same list of chronological Order IDs, then everything is working.
You're revenue totals may still not match completely. If it's by a small amount it's likely because of a timing or server configuration issue mentioned above that may be introducing a variance of a couple hours to a couple days.
If it's a large amount, please provide both validation files to support@wickedreports.com with a description of the discrepancy and a summary of what you've already found.
Note for Infusionsoft Users:
If your order source is Infusionsoft you'll want to check your settings to know whether you need to compare orders or payments. We offer support for both, but they are different and you need to make sure you're comparing apples to apples.
To check, log into your Wicked Reports account and navigate to https://my.wickedreports.com/settings
If this option is checked, you'll want to compare against your payment records. If it isn't you'll want to compare against your order records.