Tracking your order data

The following article will help you understand on a technical level how recurring orders are processed and placed by the Ordergroove system to assist you in tracking this within your internal systems. Keep in mind that this does not apply to the first or activation order, only for Recurring Orders. 

Overview

This document will detail how to:

• Create your own order transactional table called order_log
• Create your own dynamic order table called order_order
• How to calculate historical Orders Sent For Placement, Rejected Orders, and Rejection Rate metrics found in the Orders tab within the analytics section.

Reports required for calculation

To calculate the Ordergroove order related metrics specified in the later sections, we will be using the Orders Export Report with the Orders - All version found in the View Reports section of Ordergroove analytics.

Reminder: The Orders Export Report represents each row as an order item, not a unique order. This means that an order may contain items from multiple different subscriptions. Each row will contain an unique order identifier associated with the order item.

With that being said, to identify a unique set og orders, you will need to deduplicate the report using the OG Order ID as Order page calculations are done on the overall order level and not the order item level.

Metrics to Replicate

The following metrics are displayed on the Orders tab in the analytics sections and will be the subject of our replication today.

1 - Orders sent for placement

Definition: the count of orders that were sent by Ordergroove to the merchant’s payment processor

Includes the following statuses for each order:

• Rejected: Orders that were rejected when order processing was attempted by the merchant’s processor
  
  • These will have a rejected message and rejection code associated with the payment processors response
    
• Successful: Orders that were successfully processed and charged by the merchant’s processor
  
• Retry: Orders that are rejected by the merchant’s processor with a payment error code 140
  
  • These orders will ultimately receive a final status but are still considered ‘Order Sent for Placement’ as they were sent to the merchant’s processor and have not yet landed on a final status.
    
• Connection Error During Placement: SPOC was unable to reach the merchant's order placement service
  

2 - Successful Orders

Definition: The count of Successful orders from the orders sent for placement in a selected time period

3 - Rejection Rate

Definition: The % of orders sent for placement that were rejected from placement attempt and have a Rejected status logged in their Order Status field

Calculation: Count(Orders Rejected [Status = Rejected, Connection Error During Placement] ) / Count( Orders Rejected + Orders Successful [Status = ‘Successful’] )

4 - Rejection Counts

Definition: The sum of rejected orders across all rejection reasons

• Payment Issues: The segment of orders that were sent for placement and received a rejection status due to a payment Error Code (100, 110, 120, 130, 140, 150, 160, 170, 500).
• Order Creation Issues: The eCommerce platform was unable to create the order due to an error. Orders with a status of Connection Error During Placement or Rejection with an Error Code of 520 will be included in this group.
  

Building order tables for tracking

In order to track the Ordergroove order metrics, we will need to construct 2 order tables using the Orders - All Order Export Report.

The two types of tables will be:

• Transactional Table: The table will ingest the daily Orders - All file and will have the new data appended on the end. Do not deduplicate or modify the data in this table as this will store as a historical record for all orders.
• Current State Table: The table will be created by querying a subset of the data in the transactional table and will be updated to include only one entry per Order ID

Orders - All report download

The first step to creation of the tables is to download Orders - All report At a minimum the following fields should be included in the download:

• OG User ID
• Merchant User ID
• OG Order ID
• Public Order ID
• Place Date
• Order Status
• Rejected Error Code
• Rejected Error Message
• Order Sub Total

Constructing base order tables

After the download has been completed, tracking requires the creation of three follow up tables, one around an overall log, called - order_log, one for the actual order record, called - order_order, and the last for the status of the order placement, called - order_placement:

Step 1 - Download Orders - ALL

Each day, you will need to download two versions of the Orders - All report:

• • Previous Day - download an order report for the previous day to get all orders that reached their final order status
  • Next Day - download an order report for the following days orders in order to get the upcoming order Place Date
    
    • You will need this place date in order to construct our original place date column
    • In addition to Unsent status orders, you will see retry and cancelled statuses orders as well

Step 2 - Order_log

Create an order_log table in your database that will mimic Ordergroove’s `order_log` table which stores the order entries as they move through the order placement process

• • Take the two daily downloaded Orders - All files and append them into the order_log table. Note: Please make sure that you add an indexing column called transactional_id when you first create the order_log table so that it can act as the indexing id that is necessary to create the order_order table.
  • This will serve as your historical working table that allows you to look at the history of orders and log any processing changes

Step 3 - Order_order

Create an order_order table that will mimic Ordergroove’s `order_order` table. The table will be a dynamic table that only contains the most recent data for each order and should not contain any duplicate order id. Take a look down below at Code Example #1 for sample code to create a table with the latest to-date data on each order.

Step 4 - Order_placement

Next, create an order_placement table, which is where you will store the original place date that we will construct from the data in the order_log table

This data will include a creation of column for “Original Place Date”. This is done by using the dynamic place date and locking each order into the original day it was first sent for order placement. The reason we need to do this is because with the functionality of order retries, the recurring orders place date can be shifted forward in time as it moves through the retry process.

Order date shifting due to retry flow:

• An order is sent for placement on 05/01/23 however, it comes back from the processor with a 140 error code and is put into the order retry flow.In the case of a merchant’s retry settings (2 order retries, 3 days in between attempts)
• The order then will have its place date in the `order_order` table moved to 05/04/2023 and its status changed to 18 (Retry)
• The Ordergroove system will create an entry in the `order_placementfailure` which logs the original place date for a retry order
  
  • If the order gets rejected on 05/04/23, the place date will move 1 more time to 05/07/23. The attempt on 05/07/2023 will be the order’s last retry attempt (max retry of 2) and its 3rd overall order placement attempt
  • If the order rejects on 05/07/23, it will:
    
    • Be moved out of the order retry flow
    • Have its Retry status changed to Rejection
    • Have its order place date lock at 05/07/23
  • Now, if one were to just query the `order_order` table, the order place date would show 05/07/23, but the order was originally part of the 05/01/23 order cohort sent for placement

Take a look down below at Code Example #2 for a sample query used to create your order_placement table. Please make sure to run this query every day after you down the 2 Orders - All reports.

SELECT og_order_id, public_order_id, og_user_id, merchant_user_id, place_date, order_status, rejected_error_code, rejected_error_message, sub_total
FROM order_log
WHERE transactional_id in 
( SELECT latest_transaction.latest_entry
FROM
( SELECT order_id, max(transactional_id) as latest_entry
FROM order_log
GROUP by order_id
) latest_transaction
)

SELECT distinct og_order_id, public_order_id, min(place) as original_place_date
FROM order_log
WHERE status = ‘Pending’
GROUP by order_id, public_order_id

Order Metric Calculations

Orders Sent For Placement

Query to use for calculating the count of orders sent for placement:

SELECT COUNT( DISTINCT order_log.og_order_id)
FROM order_log 
LEFT JOIN order_placement
ON order_log.og_order_id = order_placement.og_order_id
WHERE order_placement.original_place_date between ‘YYYY-MM-DD’ and ‘YYYY-MM-DD’
AND order_log.order_status in (‘Retry’, ‘Successful’, ‘Rejected’ , ‘Connection Error During Placement’)

Using the query will allow you to calculate the total orders sent for placement on their original place date. This can be compared to the value in the Orders tab of the Ordergroove Analytics section.

Orders Rejected

In order to calculate the Orders Rejected which is needed as the numerator for the % Rejection Rate calculation, use the following query:

SELECT COUNT( DISTINCT order_log.og_order_id)
FROM order_log 
LEFT JOIN order_placement
ON order_log.og_order_id = order_placement.og_order_id
WHERE order_placement.original_place_date between ‘YYYY-MM-DD’ and ‘YYYY-MM-DD’
AND order_log.order_status in (‘Rejected’ , ‘Connection Error During Placement’)

Using the query will allow you to calculate the total rejected order count for the time period. This can be compared to Rejection section of the Overall Order metrics table.

Successful Orders

In order to calculated the Orders Rejected which is needed as part of the denominator for the % Rejection Rate calculation, use the following query:

SELECT COUNT( DISTINCT order_log.og_order_id)
FROM order_log 
LEFT JOIN order_placement
ON order_log.og_order_id = order_placement.og_order_id
WHERE order_placement.original_place_date between ‘YYYY-MM-DD’ and ‘YYYY-MM-DD’
AND order_log.order_status = ‘Successful’

Using the query will allow you to calculate the total successful order count for the time period. This can be compared to the Successful Order count in the Orders tab.

% Rejection Rate

Now that you have calculated the Orders Rejected and Successful Orders, you will be able to calculate the % Rejection Rate

% Rejection Rate = ( [Orders Rejected] / [Orders Rejected] + [Successful Orders] )

Compare the result of the calculation with the Rejection number in the Orders tab of the Analytics page.

Example using data pictured above:
In the original_place_date range of May 4, 2023 to May 11, 2023, there were:

• 2,991 Successful Orders
• 31 Orders Rejected

Using the above two metrics, we can do [Orders Rejected] / ( [Orders Rejected] + [Successful Orders] ) for a rejection rate:

1.03% Rejection Rate = (31 Orders Rejected / ( 2,991 Successful Orders + 31 Orders Rejected ) ) * 100

Successful Order Revenue

After creating the above tables, you will also be able to track and match your Successful Recurring Order Revenue with Ordergroove Analytics.

SELECT SUM( DISTINCT order_log.sub_total)
FROM order_log
LEFT JOIN order_placement
ON order_log.og_order_id = order_placement.og_order_id WHERE order_placement.original_place_date between ‘YYYY-MM-DD’ and ‘YYYY-MM-DD’
AND order_log.order_status = ‘Successful’

Using the query will allow you to calculate the total successful recurring order revenue for the
time period. This can be compared to the Total Revenue tile in the Revenue tab.

To get the total successful recurring revenue from the Total Revenue tile using the example shown:

Total Revenue - New Revenue = Total Successful Recurring Revenue $68.03K - $27.1K = $40.93K (Total Successful Recurring Revenue)