Data Validation
      Back to home
      1. Wicked Help Center
      2. TROUBLESHOOTING
      3. Data Validation

      How to Validate your Order Data

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

       

      There is an expected variation of up to 3% between Wicked order data and source order data. This is typically caused by the two softwares calculating net revenue slightly differently, and timezone differences with order processing.

      Follow the steps below to validate your order data if your revenue discrepancy is above this 3% margin.

      Steps

      1. Download order data from Wicked Reports.

      2. Cross-reference Wicked data with order source data.

      1. Download order data from Wicked Reports. 

      1. Inside of your Wicked account, go to Reports > Customer LTV. 
      2. Select the ORDER / UTMSEARCH tab under the filter field.order-searchtab-2
      3. Choose a date/timeframe that has order data discrepancy between Wicked and your order source that you want to analyze. Enter chosen timeframe into the filter field.timeframeUpdated
      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. 
        showOnlyAttributedbox
      5. Scroll to the bottom 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.
        updatedOrderReport

       

      2. Cross-reference Wicked order data with order source data.

      1. Add a new tab to the excel sheet containing your exported Wicked order data. Import your order source data for the same timeframe to this tab.
      2. In the order source order data tab, right-click on the Gross Sales column, and add a new column to the right. Label this column Wicked Gross Sales.
      3. Use the VLOOKUP function to add the corresponding gross sales values from the tab with Wicked's order data, to this new column that you just created in the order source data tab. Drag this formula down the entire column. If you're unsure of how to execute this function, directions are below:
          1. VLOOKUP function in Excel/Sheets: VLOOKUP(A, B, C)
            1. A: The top cell of the column containing order IDs in your order source data tab.  Ex. A2
            2. B: Range of cells containing the entire column with order IDs/, stretching to the column with gross sales, inside of the Wicked order data tab. Ex. WickedTab!B2:D200
            3. C: The nth column within the range mentioned above, that contains the gross sales (in Wicked export, it will be labeled "orderTotal"). For example, if the range was B2:D200, and column D contains the gross sales that you are retrieving from the matching orderID, then this value would be 3, because this is the third column in the range (range is mentioned in the B parameter of VLOOKUP formula). 

      4. Compare data between order source Gross Sales and the Wicked Gross Sales values. 

        1. If there are some missing data values...
          1. A small amount of these could be due to timezone differences on when the data was processed. Look up the specific email associated with the order in Reports > Customer LTV report.
              1. Select ORDER/UTMSEARCH tab.
              2. In filters, expand the date range to include the day before and the day after the order date. Uncheck "Show ONLY Attributed Orders". Enter the email associated with the order in the email field and click "Filter". 
                1. If the correct order is there, the discrepancy is likely due to time differences. 
                2. If not, follow the directions below to contact support@wickedreports.com.
          1. If there is a large amount of these, or the order cannot be found in the Customer LTV Report, email support@wickedreports.com with details about the discrepancy so that our support team can assist. Be sure to include order source data for reference via CSV or shareable access to the data for us to review.
        2. If there are some discrepancies in the corresponding order values...
          1. This is very likely due to the structure of the softwares, and how they calculate values such as gross and net revenue differently. For those specific orders, look at the order source shipping, discount, taxes and returns columns, and evaluate if any of these values could account for this discrepancy.
            1. If so, overall order data should still be accurate. 
            2. If not, follow the directions below to contact support@wickedreports.com.
        3. If there are other or unidentifiable discrepancies between data, email support@wickedreports.com with details about the discrepancy so that our support team can assist. Be sure to include order source data for reference via CSV or shareable access to the data for us to review.