How to Validate Your Sales & Revenue

A step-by-step guide on how to validate that your orders are accurately populating inside of Wicked Reports.

• Introduction to Sales and Revenue Validation
• How Do I Validate My Sales and Revenue?
  
  • How to Export Order Data from Wicked Reports
  • How to Export Order Data from Your Order Management System
  • How to do a Sales and Revenue Spreadsheet Comparison
  • How to Analyze The Results

• FAQ's

Introduction to Sales and Revenue Validation 

There are times when the sales and/or revenue in your Order Management System (OMS) may not align with the revenue you are seeing in Wicked Reports. This could be alarming to you because it will undoubtedly impact your metrics within Wicked Reports. While some variation is expected, if you notice a variation greater than 3% then you'll want to follow the steps outlined in this article. These steps will help you identify what's causing the discrepancy and how to resolve it, ensuring you have the most accurate data possible.

Common Reasons for a Sales and/or Revenue Misalignment:

• OMS orders did not pass into Wicked
• OMS is sending duplicate orders to Wicked
• OMS orders were not processed in Wicked
• OMS orders were deleted from Wicked
• OMS calculates values differently than Wicked

Keep in mind that there is an expected variation of up to 3% between your Wicked Reports revenue and your OMS data. This is typically caused by the two software calculating net revenue slightly differently and timezone differences. If your revenue discrepancy is above this 3% margin, then please follow the steps below to validate your revenue.

How Do I Validate My Revenue?

The process is simple. Here are the steps to validate your revenue:

1. Export Your Order Data CSV From Wicked
2. Export Your Order Data CSV From Your OMS
3. Do a Sales and Revenue Spreadsheet Comparison
4. Analyze the Results

Fundamentally, what we need to do is export the order data from both platforms. Then, we must compare the spreadsheet's order IDs and gross sales columns. Lastly, we'll need to analyze the results. To learn more in-depth knowledge of each step, you'll want to scroll down to the corresponding section.

Let's get started. 

How to Export Order Data from Wicked Reports

You can follow these steps to export your order from Wicked Reports.

1. Inside of your Wicked account, go to Reports > Customer LTV. 
2. Select the ORDER / UTMSEARCH tab under the filter field.
3. Choose a timeframe that has order data discrepancy between Wicked and your order source that you want to analyze. Enter the chosen timeframe into the filter field.
4. Uncheck the Show ONLY Attributed Orders box, then click the green Filter button to apply filters. Note: The Attribution Model filter field is irrelevant when this box is unchecked. 
   
5. Scroll to the bottom left of the report, and click Export to Excel to download a CSV file of your Wicked order data. Note: The top of this file shows your total order count and gross revenue for the given timeframe.
   

That's it. You've completed your Wicked Reports CSV order export. You now have 1 of 2 CSV files that are needed to do a Sales and Revenue Spreadsheet Comparison. Continue on to learn how to export your data from your Order Management System. 

How to Export Order Data from Your Order Management System

Almost all Order Management Systems (OMS) have a method to export order data. However, some can be trickier than others. For your OMS, I recommend to first start by looking for a simple Reporting section where you can download an order/transaction CSV for the timeframe in question. If your OMS does not have an easy-to-download CSV export feature, then it might require setting up an API or a custom widget to download your orders. Contact your OMS service provider to learn how to export your order data.

Here is a list of helpful articles, from other OMS service providers, that walk you through how to export your orders from their system:

• Shopify Help Center - Export Orders
• Paypal Help Center - Download Transaction Activity
• Stripe Help Center - Export Payout Reports
• ThriveCart Help Center - Export Transaction Data
• WooCommerce Blog - Export Orders

That's it. You've completed your OMS CSV order export. You now have 2 of 2 CSV files that are needed to do a Sales and Revenue Spreadsheet Comparison. Continue on to learn how to do the Sales and Revenue Spreadsheet Comparison.

How to do a Sales and Revenue Spreadsheet Comparison

To complete a Sales and Revenue Spreadsheet Comparison, we'll need to:

• Prepare the CSV files
• Compare the Order IDs

How to Prepare the CSV Files

1. Open up your Wicked Reports Order CSV in Excel
2. Remove all the unnecessary columns from the file
3. Rename the tab to Wicked Data
4. Add a new tab to the Excel sheet
5. Import your OMS Order CSV into this tab
6. Remove all the unnecessary columns from the file
7. Rename the tab to OMS Data
8. In the OMS Data tab, right-click on the Gross Sales column and add a new column to the right
9. Label this column Wicked Gross Sales
10. Now, right-click on the Wicked Gross Sales column and add another new column to the right
11. Label this column Match
    

That's it! Now you're ready to compare your Order IDs and Gross Sales Values.

Here's what my excel file looks like, using only the required fields:

How to Compare Order IDs
Now that you're files are prepared, you'll want to execute a V-Lookup function within Excel. This function will compare the two OrderID columns together, and then tell you whether or not there is a match between the two spreadsheets.  

How do I execute the V-Lookup Function?

1. In your OMS tab, select the first cell in the Wicked Gross Sales column
2. Press the "Insert Function" button and type =VLOOKUP()
3. Inside the parentheses "()" of the VLOOKUP function, we will insert four values and each of the values will be separated by a comma. Example: =VLOOKUP(A, B, C, D)
   
   Here are the values you'll enter:
   1. Lookup_value: The top cell of the column containing order IDs in your OMS Data tab. 
      Ex. A2
   2. Table_array: For this value, switch to your Wicked Data tab. Then, select the range of cells containing the entire column with order IDs, stretching to the column labeled orderTotals (Wickeds Gross Sales column). 
      Ex. 'Wicked Data'!A:B
   3. Col_index_num: The column number, within the range mentioned above, that has the corresponding value you're comparing against.
      Ex. If the range was 'Wicked Data'!A:B, then this value would be 2. That's because B, the second column within the range, is the orderTotals column which is the value you're comparing against. 
   4. Range_lookup: Using FALSE will help you find an exact match the the order IDs.
      Ex. FALSE
4. Drag and drop this function to all the remaining cells of this column. Simply double-click the bottom right of the cell, or click the bottom right of your populated cell and drag it downward until you've reached the bottom row of your data. That'll apply the formula to every cell in this column.
5. Now it's time to Analyze the Results! 

How to Analyze the Results

After completing a Sales and Revenue Spreadsheet Comparison, here are the most common results:

• I received an error. Now What?
• My Order IDs do not match. Now What?
• My Order IDs match, but my Gross Values do not match. Now What?
  
• My Order IDs match and my Gross Values match. Now What?

I received an error. Now What?
If you've done the function incorrectly, then you'll receive an error message from Excel. And, you'll need to examine your formula, update it, and then try again. Alternatively, for further assistance, you can contact our support team with the details of the discrepancy.

My Order IDs do not match. Now What?
If you've done everything correctly, yet some of your Order IDs do not match, then you'll see some of the Wicked Gross Sales cells populate with #N/A and others with the Wicked Gross Sale value.

• #N/A indicates that the order match was not found
• Wicked Gross Sales values indicate that your order is matching

For the orders that are missing (#N/A), we need to discover why the order is missing. Here are the four most common reasons Order IDs may not match:

• Timezone Differences
• Duplicate orders are being sent
• The order was not sent to/received by Wicked Reports
• The order was not processed by Wicked Reports

To identify what the root cause is, we'll need to check one of the orders that are missing (#N/A) by going directly to the LTV page:

1. Login to your Wicked Account
2. Navigate to the Customer LTV page
3. Click on the Contact/UTM search tab
4. For your filters:
   1. Add the email of the user to whom the order belongs
   2. Add the oldest time window possible (either Since First Tracking or Since First Order)
   3. Press the green "Filter" button
5. Select the user. This will direct you to the user's unique customer journey page.
6. Look for the order ID within the user's customer journey page

If the order is there, then the root cause it's most probably a timezone difference. Simply look at the date of the order to compare if it's outside of the previously exported CSV's time window.

If more than one of the same order is there, then you have a duplication issue. This is most commonly caused by having multiple OMSs integrated with Wicked Reports. So, you'll need to disconnect the source that's sending duplicated orders, then contact support to delete the previously duplicated orders.

If the order is not there, then it's most likely due to the order not being sent to/received by Wicked Reports or not being processed. If you're unable to identify how to resolve this then, for further assistance, you can contact our support team with the details of the discrepancy.

My Order IDs match, but my Gross Values do not match. Now What?
If you've done everything correctly, yet some of your Gross Sales values do not match the Wicked Gross Sales value, then you'll need to do another simple comparison.

1. In your CSV export, select the first cell available within your "Match" column.
   Ex. D2
2. Use the simple formula "Wicked Gross Sales - Gross Sales" to calculate the difference
   Ex. C2-B2
3. Now, drag and drop this cell downward. This will make it so the formula is applied to your whole Match column.

Now that you've applied this formula to the Match column, you'll be able to see the difference between the values of your orders. If you see, within the match column, that the value of your order is $0, then that means your Order ID and your Gross Value match. However, if you see the value of your order as something other than $0, then that indicates your Gross Value is not matching.

The root cause of this discrepancy is very likely due to the structure of your OMS and how they calculate gross values. For the specific orders that show a difference in Gross Value, you'll want to take a look at the OMS shipping, discount, taxes, and returns columns. Then, evaluate if any of these values could account for this discrepancy.

• If so, the overall order data should still be accurate and you're good to go.
• If not, and you are unable to justify this discrepancy then, for further assistance, you can contact our support team with the details of the discrepancy.

My Order IDs match and my Gross Values match. Now What?
If you've done everything correctly, and all of your Order IDs match and Gross Values, then you'll see the Wicked Gross Sales column populate with the order's corresponding values. This is the ideal scenario and indicates you do not have a revenue or sales discrepancy! That being said, if you still feel like something is not quite right, or if you can't make sense of the data, then you can always reach out to our support team and we'll be able to help. 

FAQ's

1. What is the VLOOKUP Function?
   It looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.
   
   Syntax:
   
   VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
   1. Lookup_value: is the value to be found in the first column of the table, and can be a value, a reference, or a text string.
   2. Table_array: is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.
   3. Col_index_num: is the column number in table_array from which the matching value should be returned. The first column of values in the table is column 1.
   4. Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE
      
2. Why aren't my orders entering into Wicked?
   Here are the three most common reasons orders do not enter into Wicked:
   1. If you have multiple OMSs and one of them is not integrated with Wicked Reports.
   2. If you have an order API as your integration method, and it's set up incorrectly.
   3. Your integration method has an error.