Unified Subscription Schema (USS)
This page provides a detailed description of the Unified Subscription Schema (USS) within the Daasity Data Model and defines each table and column in this schema
Overview
The purpose of the Unified Subscription Schema is to normalize and load data from various subscription platforms into a single set of tables. From USS, other scripts will push that data down into other tables that can be used for analyzing your subscription business.
Unified Subscription Schema Tables
Order Line Items [
uss.order_line_items]Orders [
uss.orders]Subscription Lines [
uss.subscription_lines]Subscriptions [
uss.subscriptions]
Order Line Items
Purpose: Normalizes order-line-level data from multiple subscription platforms into a single table
Table Name: uss.order_line_items
Related transform code:
| Column | Description |
|---|---|
SUBSCRIPTION_ORDER_LINE_ID🔑 | MD5 of platform identifier, order ID, and line ID |
SOURCE_ORDER_LINE_ID | Line ID from the source |
SUBSCRIPTION_ORDER_ID | MD5 of the platform identifier and order ID |
SUBSCRIPTION_ID | MD5 of the platform identifier and subscription ID |
CUSTOMER_ID | MD5 of the platform identifier and customer ID |
SOURCE_PRODUCT_ID | Product ID from the source |
PRODUCT_TITLE | Product title from the source |
SOURCE_VARIANT_ID | Variant ID from the source |
VARIANT_TITLE | Variant title from the source |
SKU | SKU value from the source |
UNIT_PRICE | Unit price from the source. May be a calculation, depending on source |
QUANTITY | Quantity ordered from the source |
IS_ONETIME | Flag indicating if the product is a recurring product purchase or a one-time add-on |
CUSTOM_ATTRIBUTES | Custom attributes from the source system |
PLATFORM | Identifier for the source platform |
__LOADED_AT | UTC timestamp of when this data was transformed from the source table into this table |
__SYNCED_AT | UTC timestamp of when this data was loaded from the external source into the database |
Orders
Purpose: Normalizes order metadata from multiple subscription platforms into a single table
Table Name: uss.orders
Related transform code:
| Column | Description |
|---|---|
SUBSCRIPTION_ORDER_ID🔑 | MD5 of the platform identifier and order ID |
SOURCE_ORDER_ID | Order ID from the source |
SUBSCRIPTION_ID | MD5 of the platform identifier and subscription ID |
CREATED_AT | Timestamp of when the order was created. This is converted into the Daasity account timezone |
UPDATED_AT | Timestamp of when the order was last updated. This is converted into the Daasity account timezone |
CUSTOMER_ID | MD5 of the platform identifier and customer ID |
CHARGE_STATUS | Status of the charge for this order |
CURRENCY | Currency code from the source system |
STATUS | Status from the source system |
TYPE | Indicates whether the order is recurring or placed on the site |
TAGS | Order tags |
TOTAL_PRICE | Total price of the order |
TOTAL_DISCOUNTS | Total discount amount for the order |
TOTAL_SHIPPING_PRICE | Total shipping amount for the order |
TOTAL_TAX | Total tax amount for the order |
ADDRESS_CITY | Billing/delivery address city |
ADDRESS_COUNTRY | Billing/delivery address country |
ADDRESS_COUNTRY_CODE | Billing/delivery address country code |
ADDRESS_FIRST_NAME | Billing/delivery address first name |
ADDRESS_LAST_NAME | Billing/delivery address last name |
ADDRESS_LINE_1 | Billing/delivery address line 1 |
ADDRESS_LINE_2 | Billing/delivery address line 2 |
ADDRESS_PROVINCE_CODE | Billing/delivery address province code |
ADDRESS_ZIP | Billing/delivery address ZIP code |
PLATFORM | Identifier for the source platform |
__LOADED_AT | UTC timestamp of when this data was transformed from the source table into this table |
__SYNCED_AT | UTC timestamp of when this data was loaded from the external source into the database |
Subscription Lines
Purpose: Normalizes subscription-line-level data from multiple subscription platforms into a single table
Table Name: uss.subscription_lines
Related transform code:
| Column | Description |
|---|---|
SUBSCRIPTION_LINE_ID🔑 | MD5 of the platform identifier and the source subscription line ID |
SOURCE_SUBSCRIPTION_LINE_ID | Subscription line ID from the source system |
SUBSCRIPTION_ID | MD5 of the platform identifier and the source subscription ID |
CUSTOMER_ID | MD5 of the platform identifier and the source customer ID |
IS_PREPAID | Indicates whether the subscription line is prepaid |
SOURCE_PRODUCT_ID | Product ID from the source system |
PRODUCT_TITLE | Product title from the source system |
QUANTITY | Quantity for the subscription line |
SKU | SKU from the source system |
UNIT_PRICE | Unit price for the subscription line |
SUBTOTAL_PRICE | Subtotal for the subscription line |
SOURCE_VARIANT_ID | Variant ID from the source system |
VARIANT_TITLE | Variant title from the source system |
PLATFORM | Identifier for the source platform |
__LOADED_AT | UTC timestamp of when this data was transformed from the source table into this table |
__SYNCED_AT | UTC timestamp of when this data was loaded from the external source into the database |
Subscriptions
Purpose: Normalizes subscription metadata from multiple subscription platforms into a single table
Table Name: uss.subscriptions
Related transform code:
| Column | Description |
|---|---|
SUBSCRIPTION_ID🔑 | MD5 of the platform identifier and the source subscription ID |
SOURCE_SUBSCRIPTION_ID | Subscription ID from the source system |
CREATED_AT | Timestamp of when the subscription was created. This is converted into your Daasity account timezone |
UPDATED_AT | Timestamp of when the subscription was last updated. This is converted into your Daasity account timezone |
PAUSED_AT | Timestamp of when the subscription was paused. This is converted into your Daasity account timezone |
PAUSED_UNTIL | Timestamp of when the subscription is paused until. This is converted into your Daasity account timezone |
CHURNED_AT | Timestamp of when the subscription was cancelled. This is converted into your Daasity account timezone |
CANCELLED_AT | Timestamp of when the subscription was cancelled. This is converted into your Daasity account timezone |
CANCELLATION_REASON | Reason from the source system of why the subscription was cancelled |
CANCELLATION_REASON_COMMENTS | Additional information from the source system of why the subscription was cancelled |
CUSTOMER_ID | MD5 of the platform identifier and the customer ID from the source system |
SOURCE_CUSTOMER_ID | Customer ID from the source system |
EMAIL_ADDRESS | Email address of the subscriber |
FIRST_NAME | First name of the subscriber |
LAST_NAME | Last name of the subscriber |
DELIVERY_ADDRESS_LINE_1 | Delivery address line 1 from the source system |
DELIVERY_ADDRESS_LINE_2 | Delivery address line 2 from the source system |
DELIVERY_ADDRESS_ID | Address ID from the source system |
DELIVERY_ADDRESS_CITY | Delivery address city from the source system |
DELIVERY_ADDRESS_COMPANY | Delivery address company from the source system |
DELIVERY_ADDRESS_COUNTRY_CODE | Delivery address country code from the source system |
DELIVERY_ADDRESS_PHONE | Delivery phone number from the source system |
DELIVERY_ADDRESS_PROVINCE | Delivery address province from the source system |
DELIVERY_ADDRESS_ZIP | Delivery address ZIP Code from the source system |
DELIVERY_PRICE | Cost to the consumer for delivering the order |
DUNNING_STARTED_AT | Timestamp of when the subscription entered dunning. This is converted into your Daasity account timezone |
DUNNING_STORY_ID | Dunning story ID from the source system |
DUNNING_EXITED_AT | Timestamp of when the subscription exited dunning. This is converted into your Daasity account timezone |
LAST_CHARGE_DATE | Timestamp of when the last charge was made related to the subscription. This is converted into your Daasity account timezone |
NEXT_CHARGE_DATE | Timestamp of when the next charge will be made for this subscription. This is converted into your Daasity account timezone |
IS_SKIPPABLE | Indicates whether the subscription can be skipped |
IS_SWAPPABLE | Indicates whether products in the subscription can be swapped out |
CURRENCY | Currency for the dollar values for this order |
PRICE | Price of the subscription |
NOTE | Note for the subscription |
ORDER_DAY_OF_MONTH | Scheduled day of the month for this order |
ORDER_DAY_OF_WEEK | Scheduled day of the week for this order |
ORDER_INTERVAL_FREQUENCY | How often the subscription is fulfilled |
ORDER_INTERVAL_UNIT | How often the subscription is fulfilled |
STATUS | Indicates whether the subscription is cancelled, paused, or active |
STATUS_CONTEXT | Additional context for the status |
PLATFORM | Identifier for the source platform |
__LOADED_AT | UTC timestamp of when this data was transformed from the source table into this table |
__SYNCED_AT | UTC timestamp of when this data was loaded from the external source into the database |
Last updated