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:

• Amazon

• SKIO

• Stay Ai

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:

• Amazon

• SKIO

• Stay Ai

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:

• SKIO

• Stay Ai

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:

• SKIO

• Stay Ai

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