It can feel impossible to get data exports to match the numbers you see on your Ordergroove Analytics Dashboard. There is a method to the madness, this guide will discuss the reports you can run to get the two in sync.
Before you start
Ordergroove offers a number of standard exported reports you can download onto your local machine and manipulate or upload into your own data visualization software. These reports have the option to drop on an SFTP server unique and secure to you, and include:
- Subscription Report (Created or Updated)
- Subscription Cancelled
- Order Report
- (Legacy) Inventory Report
- (Legacy-Not available in product) CRM Subscriber Events
- (Legacy-Not available in product) CRM Subscription
These reports are available to be dropped on a merchant’s Ordergroove-hosted SFTP Server each day in a folder titled Outgoing.
What reports are we going to use?
To calculate and track the number of active base subscriptions and subscribers, we need to use a combination of Subscription (Created) Report, Subscription Cancelled Report, and CRM Subscriber Events Report.
Note: The Subscription (Created) Report and Subscription Cancelled Report on the SFTP folder will only contain data for the previous day. This means that each report must be picked up and ingested at a consistent time daily to keep up with the latest status of creations and cancels to maintain proper calculations.
Subscription Report
The Subscription CSV file report contains a record of every new subscription created the previous day. For example, SubscriptionCSV_03022023080530 will contain data for subscriptions created on 3/1/2023.
Each daily report will then allow you to download and ingest a copy of all the New Subscriptions that were created and part of the database Subscription table.
The report contains the following columns:
Column Header |
Column Definition |
| Merchant | Name of the merchant |
| User ID | Customer ID used by Ordergroove to identify customers |
| Merchant User ID | Customer ID that can be used in merchant’s system to identify the customer/subscriber |
| Create Date | Date the new subscription was created |
| Create Time | Timestamp the new subscription was created |
| Start Date | Will reflect either 1) the date an import subscription was created in the original program or 2) the date the subscription was created in the Ordergroove system |
| Customer Status | Reflects if the Customer is still an active subscriber or become inactive |
| Subscription ID | Subscription identifier |
| Offer ID | The offer that the subscriber used to create the subscription |
| Merchant Order ID | Order ID in your system under which the initial subscription order was placed |
| Guest Checkout | Whether or not the customer completed checkout as a guest. Since Ordergroove does not support subscription checkouts for guests, this would only apply to subscriptions from other systems |
| Email Address | Customer’s specified email address |
| First Name | Customer’s first name |
| Last Name | Customer’s last name |
| Product | Name of the subscription product |
| Product ID | OG Product Identifier |
| SKU | Merchant provided product SKU |
| Frequency | The ordering cadence for the recurring orders in days |
| Reminder | The number of days prior to an order that the customer will receive an upcoming order reminder email |
| Status | Identifies if Subscription is active or inactive |
| Quantity | Unit quantity for each subscription order |
| Price | Unit price of the product |
| Discount Price | Unit price of the subscription product before all incentive discounts have been applied |
| 24 Hour Cancel | Will be marked Y if the subscription was cancelled within 24 hours of being created |
| Cancel Date | Date the subscription was cancelled and marked inactive |
| Cancel Reason | Customer’s specific reason for cancelling the subscription |
| Orders Placed | Number of orders placed over the life of the subscription (includes successful and failed orders) |
| Last Order Date | Most recent date of a recurring order (successful or failed) |
| Extra Data - Reporting | Any extra stored data for custom reporting or offers |
| Public Subscription ID | Public hexadecimal id for subscriptions |
Subscription Cancelled Report
The Subscription Cancelled CSV file report contains a record of every subscription that was cancelled on the previous day. For example, SubscriptionsCancelledCSV_03022023080511 will contain data for subscriptions cancelled on 3/1/2023.
Each daily report will then allow one to download and ingest a copy of all the Subscriptions that were cancelled and integrate into their Subscription table.
The report contains the following columns:
Column Header |
Column Definition |
| Merchant | Name of the merchant |
| User ID | Customer ID used by Ordergroove to identify customers |
| Merchant User ID | Customer ID that can be used in merchant’s system to identify the customer/subscriber |
| Create Date | Date the new subscription was created |
| Create Time | Timestamp the new subscription was created |
| Start Date | Will reflect either 1) the date an import subscription was created in the original program or 2) the date the subscription was created in the Ordergroove system |
| Customer Status | Reflects if the Customer is still an active subscriber or become inactive |
| Subscription ID | Subscription identifier |
| Offer ID | The offer that the subscriber used to create the subscription |
| Merchant Order ID | Order ID in your system under which the initial subscription order was placed |
| Guest Checkout | Whether or not the customer completed checkout as a guest. Since Ordergroove does not support subscription checkouts for guests, this would only apply to subscriptions from other systems |
| Email Address | Customer’s specified email address |
| First Name | Customer’s first name |
| Last Name | Customer’s last name |
| Product | Name of the subscription product |
| Product ID | OG Product Identifier |
| SKU | Merchant provided product SKU |
| Frequency | The ordering cadence for the recurring orders in days |
| Reminder | The number of days prior to an order that the customer will receive an upcoming order reminder email |
| Status | Identifies if Subscription is active or inactive |
| Quantity | Unit quantity for each subscription order |
| Price | Unit price of the product |
| Discount Price | Unit price of the subscription product before all incentive discounts have been applied |
| 24 Hour Cancel | Will be marked Y if the subscription was cancelled within 24 hours of being created |
| Cancel Date | Date the subscription was cancelled and marked inactive |
| Cancel Reason | Customer’s specific reason for cancelling the subscription |
| Orders Placed | Number of orders placed over the life of the subscription (includes successful and failed orders) |
| Last Order Date | Most recent date of a recurring order (successful or failed) |
| Public Subscription ID | Public hexadecimal id for subscriptions |
(Legacy) CRM Subscriber Events
The CRM Subscriber Events CSV file contains a record of changes that subscribers have made during the previous day. For example, crm_subscriber_events_03072023080044.csv will contain changes made on 3/6/2023.
Each daily report will then allow one to download and ingest a copy of all the changes that were made to subscriptions in the Subscription table.
The report contains the following columns. Please note headers are absent from this report:
Column Header |
Column Definition |
| Column A: Public Subscription ID | Public hexadecimal id for subscriptions |
| Column B: Merchant User ID | Customer ID that can be used in merchant’s system to identify the customer/subscriber |
| Column C: Email Address | Subscriber’s associated email address |
| Column E: Event ID |
The numeric identifier that corresponds to an action on the customer’s subscriptions. For a complete list take a look at Member Activity File. Example: 9 - Reactivated Subscription |
| Column F: Event Date | Date the Subscriber change occurred |
Calculating Active Subscriptions
Step 1
Ingest the Subscription (created) Report into your system and append each row to a given table as a newly added subscription.
A separate table of subscription reports should be maintained in your system to keep track of a historical record of new subscriptions.
Step 2
The table can be queried based on the product id and created date (or created time) to understand the number of new subscriptions within a particular time period to track new subscriptions created over the program’s lifetime.
Step 3
Grab the Subscription Cancelled Report CSV, which is used to map the cancelled entries to determine which subscriptions are inactive.
A separate table of subscription cancels should be maintained in your system to keep track of a historical record of cancellations.
Note: The Subscription Cancelled report will always record the date and time of final cancellation of a subscription in the previous 24-hour period. A subscription may be cancelled and reactivated within any varying time period. Ex: within the 24-hour period of the file, or before and after. This means there can be more than one entry for a given subscription ID in the subscription cancelled table created from ingesting the Subscription Cancelled Report CSV.
Step 4
For each row in the subscription cancelled table, query against the subscription ID in your master subscription table. The cancellation date will determine the date on which the subscription ID has become inactive.
You can maintain the max cancelled date in your master subscription table.
Example Query for maximum cancelled date:
SELECT subscription_report.subscription_id, max(subscription_cancelled) as max_cancelled_date
FROM subscription_report
LEFT JOIN subscription_cancelled
ON subscription_report.subscription_id = subscription_cancelled.subscription_id
GROUP by subscription_report.subscription_idStep 5
Once the Subscription Report and Subscription Cancelled tables have been created and mapped, you can use the maximum cancelled date across the entire Subscription Cancelled table to determine the status of the subscription from the Subscription Report.
Step 6
Final step to determine count of Active Subscriptions. Take a look below at Example Query 2 for current active subscription base.
SELECT count(distinct subscription_report.subscription_id)
FROM subscription_report
WHERE subscription_report.maximum_cancelled_date is nullCalculating Reactivated Subscriptions
Let's make sure we're on the same page, what are reactivated subscriptions? Ordergroove enables subscribers to reactivate an individual subscription which means a previously existing or previously cancelled subscriber can go into their customer dashboard and change the status of cancelled subscription thereby changing it from inactive to active. If the subscriber was previously cancelled, the subscriber will be reactivated as well.
Reactivations are best maintained through the use of Webhooks. Ordergroove does not recommend doing this through exports.
With that being said, calculation of reactivations is possible through the use of the CRM Subscriber Event report:
Step 1
Ingest the CRM Subscriber Events report into your system and append each row to a given table as a log of subscriber changes.
A separate table of subscriber changes should be maintained in your system to keep track of a historical record of changes/modifications.
Step 2
The CRM Subscriber Events report and table can be mapped to both the Subscription Report/Table and the Subscription Cancellation Report/Table using the `public_subscription_id`
It is recommended that an event_mapping table be created using the KC Article to map the event_id in Column E to the event action name.
Step 3
Find the maximum reactivation date grouped by the public_subscrition_id. The date will allow you to compare the maximum reactivation date to the maximum cancellation date to determine the current status for the subscription.
You can store the maximum reactivation date in the master subscription table to always have a record of the latest reactivation date.
Example Query for maximum reactivation date:
SELECT crm_subscriber_events.subscription_id, max(crm_subscriber_events.activity_date) as maximum_reactivation_date
FROM crm_subscriber_events
WHERE crm_subscriber_events.event_id = 9
GROUP by crm_subscriber_events.subscription_idNow on the master subscription table, there are the two derived columns maximum cancellation date and maximum reactivation date.
Step 4
To determine if the subscription is currently reactivated, remember that a subscription can be cancelled and reactivated multiple times, compare the maximum reactivation date to the maximum reactivation date to determine which is the latest event.
Example current subscription status query:
SELECT subscription_report.subscription_id,
if(subscription_report.maximum_cancellation_date is null, “Active”,
if(subcription_report.maximum_reactivation_date >
subscription_report.maximum_cancellation_date, “Active”, “Inactive”) as current_status
FROM subscription_report
GROUP by subscription_report.subscription_idThe current_status column should be appended to the master subscription table and recalculated daily.
Step 5
Now that the current_status is present in the master subscription table, you are able to query the Active Subscription Bases.
SELECT subscription_report.subscription_id,
if(subscription_report.maximum_cancellation_date is null, “Active”,
if(subcription_report.maximum_reactivation_date >
subscription_report.maximum_cancellation_date, “Active”, “Inactive”) as current_status
FROM subscription_report
GROUP by subscription_report.subscription_idComparing to Ordergroove Active Subscriptions
Once the steps in Active Subscriptions and Reactivated Subscriptions have been completed and the Current Active Subscriptions calculated, you can use the previous day’s Active Subscription Count in the Subscription Count table of the Subscriptions tab in the Analytics dashboard for your program.
Note: The SFTP reports ingested will only provide full day for the previous day, so the most accurate Ordergroove metric for checking data integrity is the previous day’s Active Subscription at Day’s End field.
Subscription Cancellation Rate
We calculate Subscription Cancellation Rate by:
[Cancels in Time Period] / ( SUM[Active Subscriptions Per Day] / [Days in Time Period] )
Note: In order to account for the change in Active Subscriptions over the time period, you need to take an average of the Active Subscriptions each day of the time period.
Calculating Cancellation Rate
Step 1: Calculate your subscription cancellations for the time period using the same instructions as Step 4 & 5 for Active Subscriptions.
Step 2: Find the Active Subscription Base for each day in your cancellation time period. For your Cancellation Rate denominator, calculate the average Active Subscription Base across each day in the time period. This is your Average Active Subscription Base for the calculation.
Step 3: Finally, divide the calculated Subscription Cancels by the Average Active Subscription Base. The calculation will yield the Subscription Cancellation Rate found in the Cancellations tab of RC3 Analytics.
Take a look at the example chart and calculations below, pulled from the Ordergroove Analytics Cancellation tab, to help validate your own results against Ordergroove’s calculation.
Example Cancellation Rate
Example calculation
Formula: Subscription Cancels / Average Active Subscriptions
Numerator Calculation: 75 + 117 + 232 = 424 Subscription Cancels
Denominator Calculation: 15,349 + 15,620 + 15,882 = 163,041 / 3 Days = 54,347 Average Subscription Base
Cancellation Rate (Jan. 1 - Jan. 3rd, 2022): 424 / 54,347 = 0.78% Subscription Cancellation Rate
Calculating Active Subscribers
Step 1
Find the date the subscriber was created in the system (subscriber created date) by querying the subscription records to get the minimum date (1st subscription created date) in the system when the subscriber was created.
Example Query for subscriber creation date:
SELECT subscription_report.customer_id, min(subscription_report.created)
FROM subscription_report
GROUP BY subscription_report.customer_id This determines when a subscriber is created. A separate table of subscriber data should be maintained in your system to keep track of a historical record of new subscribers.
Step 2
Once the subscriber creation date (min(subscription_report.created) has been determined, the table can be queried based on the date to get the number of new subscribers created within a particular time period.
Step 3
Using the Subscription master table with the maximum cancellation date, maximum reactivation date, and current subscription status, we can now determine the current subscriber status.
In order for a subscriber to be labeled as a cancelled subscriber, every subscription associated with the subscriber must be cancelled, or inactive.
If every subscription for a subscriber has a cancel_date entry, then the subscriber can be marked as inactive and assigned a subscriber cancelled date based on the latest subscription cancel_date.
SELECT subscriber_subscriptions.user_id, if(
subscriber_subscriptions.subscription_count =
subscriber_subscriptions.cancelled_count, “Inactive”, “active”) as
current_subscriber_status
FROM
(
SELECT user_id, COUNT(DISTINCT(subscription_report.subscription_id)) as
subscription_count, COUNT(DISTINCT case when
subscription_report.current_subscription_status = “Inactive” then
subscription_report.subscription_id end) as cancelled_count
FROM subscription_report
) subscriber_subscriptionsA derived query should be set up to run the above query and the data stored in a separate master subscriber table or appended as current_subscriber_status to each of the subscriber’s subscriptions in the master subscription table.
Note: A cancelled subscriber can create new subscriptions, which would cause them to become active again. It is important to make sure you are consistently updating the master subscription table and ingesting the SFTP reports daily.
Step 4
Now that you have the current subscriber status assigned to each subscriber, you can now query master subscription or master subscriber table to get the current Active Subscribers count.
SELECT count(distinct subscription_report.customer_id)
FROM subscription_report
WHERE subscription_report.current_subscriber_status = “Active”Comparing to Ordergroove Active Subscribers
Once the steps in Active Subscribers have been completed and the Current Active Subscribers calculated, you can use the previous day’s Active Subscriber Count in the Subscriber Count table of the Subscribers tab in the Analytics dashboard for your program.
Note: The SFTP reports ingested will only provide full day for the previous day, so the most accurate Ordergroove metric for checking data integrity is the previous day’s Active Subscriber at Day’s End field.
Subscriber Cancellation Rate
We calculate Subscriber Cancellation Rate by:
[Cancels in Time Period] / ( SUM[Active Subscribers Per Day] / [Days in Time Period] )
Note: In order to account for the change in Active Subscribers over the time period, you need to take an average of the Active Subscribers each day of the time period.
Calculating Cancellation Rate
Step 1: Calculate your subscriber cancellations for the time period using the same instructions as Step 4 & 5 for Active Subscribers.
Step 2: Find the Active Subscriber Base for each day in your cancellation time period. For your Cancellation Rate denominator, calculate the average Active Subscriber Base across each day in the time period. This is your Average Active Subscriber Base for the calculation.
Step 3: Finally, divide the calculated Subscriber Cancels by the Average Active Subscriber Base. The calculation will yield the Subscriber Cancellation Rate found in the Cancellations tab of Ordergroove Analytics.
Take a look at the example chart and calculations below, pulled from the Ordergroove Analytics Cancellation tab, to help validate your own result against Ordergroove’s calculation.
Example Cancellation Rate
Example calculation
Formula: Subscriber Cancels / Average Active Subscribers
Numerator Calculation: 23 + 42 + 85 = 150 Subscriber Cancels
Denominator Calculation: 23,294 + 23,339 + 23,384 = 70,017 / 3 Days = 23,339 Average Subscribers
Cancellation Rate (Jan. 1 - Jan. 3rd, 2022): 150 / 23,339 = 0.64% Subscriber Cancellation Rate