# Unified Subscription Schema (USS)

## 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. 

<figure><img src="https://content.gitbook.com/content/amTMWiPne1v1V3L7mbuj/blobs/T5TGXNChQJczPYajNSL3/image.png" alt=""><figcaption></figcaption></figure>

## Unified Subscription Schema Tables

* [Order Line Items](#order-line-items) \[`uss.order_line_items]`
* [Orders](#orders) \[`uss.orders]`
* [Subscription Lines](#subscription-lines) \[`uss.subscription_lines]`
* [Subscriptions](#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](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/amazon/1814_USS_BAS_AMAZON_order_lines.sql)
* [SKIO](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/skio/1804_USS_BAS_SKIO_order_lines.sql)
* [Stay Ai](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/stay_ai/1804_USS_BAS_STAY_AI_order_lines.sql)

<table data-full-width="true"><thead><tr><th>Column</th><th>Description</th></tr></thead><tbody><tr><td>SUBSCRIPTION_ORDER_LINE_ID🔑</td><td>MD5 of platform identifier, order ID, and line ID</td></tr><tr><td>SOURCE_ORDER_LINE_ID</td><td>Line ID from the source</td></tr><tr><td>SUBSCRIPTION_ORDER_ID</td><td>MD5 of the platform identifier and order ID</td></tr><tr><td>SUBSCRIPTION_ID</td><td>MD5 of the platform identifier and subscription ID</td></tr><tr><td>CUSTOMER_ID</td><td>MD5 of the platform identifier and customer ID</td></tr><tr><td>SOURCE_PRODUCT_ID</td><td>Product ID from the source</td></tr><tr><td>PRODUCT_TITLE</td><td>Product title from the source</td></tr><tr><td>SOURCE_VARIANT_ID</td><td>Variant ID from the source</td></tr><tr><td>VARIANT_TITLE</td><td>Variant title from the source</td></tr><tr><td>SKU</td><td>SKU value from the source</td></tr><tr><td>UNIT_PRICE</td><td>Unit price from the source. May be a calculation, depending on source</td></tr><tr><td>QUANTITY</td><td>Quantity ordered from the source</td></tr><tr><td>IS_ONETIME</td><td>Flag indicating if the product is a recurring product purchase or a one-time add-on</td></tr><tr><td>CUSTOM_ATTRIBUTES</td><td>Custom attributes from the source system</td></tr><tr><td>PLATFORM</td><td>Identifier for the source platform</td></tr><tr><td>__LOADED_AT</td><td>UTC timestamp of when this data was transformed from the source table into this table</td></tr><tr><td>__SYNCED_AT</td><td>UTC timestamp of when this data was loaded from the external source into the database</td></tr></tbody></table>

### **Orders**

Purpose: Normalizes order metadata from multiple subscription platforms into a single table

Table Name: `uss.orders`

Related transform code:

* [Amazon](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/amazon/1813_USS_BAS_AMAZON_orders.sql)
* [SKIO](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/skio/1803_USS_BAS_SKIO_orders.sql)
* [Stay Ai](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/stay_ai/1803_USS_BAS_STAY_AI_orders.sql)

<table data-full-width="true"><thead><tr><th>Column</th><th>Description</th></tr></thead><tbody><tr><td>SUBSCRIPTION_ORDER_ID🔑</td><td>MD5 of the platform identifier and order ID</td></tr><tr><td>SOURCE_ORDER_ID</td><td>Order ID from the source</td></tr><tr><td>SUBSCRIPTION_ID</td><td>MD5 of the platform identifier and subscription ID</td></tr><tr><td>CREATED_AT</td><td>Timestamp of when the order was created. This is converted into the Daasity account timezone</td></tr><tr><td>UPDATED_AT</td><td>Timestamp of when the order was last updated. This is converted into the Daasity account timezone</td></tr><tr><td>CUSTOMER_ID</td><td>MD5 of the platform identifier and customer ID</td></tr><tr><td>CHARGE_STATUS</td><td>Status of the charge for this order</td></tr><tr><td>CURRENCY</td><td>Currency code from the source system</td></tr><tr><td>STATUS</td><td>Status from the source system</td></tr><tr><td>TYPE</td><td>Indicates whether the order is recurring or placed on the site</td></tr><tr><td>TAGS</td><td>Order tags</td></tr><tr><td>TOTAL_PRICE</td><td>Total price of the order</td></tr><tr><td>TOTAL_DISCOUNTS</td><td>Total discount amount for the order</td></tr><tr><td>TOTAL_SHIPPING_PRICE</td><td>Total shipping amount for the order</td></tr><tr><td>TOTAL_TAX</td><td>Total tax amount for the order</td></tr><tr><td>ADDRESS_CITY</td><td>Billing/delivery address city</td></tr><tr><td>ADDRESS_COUNTRY</td><td>Billing/delivery address country</td></tr><tr><td>ADDRESS_COUNTRY_CODE</td><td>Billing/delivery address country code</td></tr><tr><td>ADDRESS_FIRST_NAME</td><td>Billing/delivery address first name</td></tr><tr><td>ADDRESS_LAST_NAME</td><td>Billing/delivery address last name</td></tr><tr><td>ADDRESS_LINE_1</td><td>Billing/delivery address line 1</td></tr><tr><td>ADDRESS_LINE_2</td><td>Billing/delivery address line 2</td></tr><tr><td>ADDRESS_PROVINCE_CODE</td><td>Billing/delivery address province code</td></tr><tr><td>ADDRESS_ZIP</td><td>Billing/delivery address ZIP code</td></tr><tr><td>PLATFORM</td><td>Identifier for the source platform</td></tr><tr><td>__LOADED_AT</td><td>UTC timestamp of when this data was transformed from the source table into this table</td></tr><tr><td>__SYNCED_AT</td><td>UTC timestamp of when this data was loaded from the external source into the database</td></tr></tbody></table>

### **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](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/skio/1802_USS_BAS_SKIO_subscription_lines.sql)
* [Stay Ai](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/stay_ai/1802_USS_BAS_STAY_AI_subscription_lines.sql)

<table data-full-width="true"><thead><tr><th>Column</th><th>Description</th></tr></thead><tbody><tr><td>SUBSCRIPTION_LINE_ID🔑</td><td>MD5 of the platform identifier and the source subscription line ID</td></tr><tr><td>SOURCE_SUBSCRIPTION_LINE_ID</td><td>Subscription line ID from the source system</td></tr><tr><td>SUBSCRIPTION_ID</td><td>MD5 of the platform identifier and the source subscription ID</td></tr><tr><td>CUSTOMER_ID</td><td>MD5 of the platform identifier and the source customer ID</td></tr><tr><td>IS_PREPAID</td><td>Indicates whether the subscription line is prepaid</td></tr><tr><td>SOURCE_PRODUCT_ID</td><td>Product ID from the source system</td></tr><tr><td>PRODUCT_TITLE</td><td>Product title from the source system</td></tr><tr><td>QUANTITY</td><td>Quantity for the subscription line</td></tr><tr><td>SKU</td><td>SKU from the source system</td></tr><tr><td>UNIT_PRICE</td><td>Unit price for the subscription line</td></tr><tr><td>SUBTOTAL_PRICE</td><td>Subtotal for the subscription line</td></tr><tr><td>SOURCE_VARIANT_ID</td><td>Variant ID from the source system</td></tr><tr><td>VARIANT_TITLE</td><td>Variant title from the source system</td></tr><tr><td>PLATFORM</td><td>Identifier for the source platform</td></tr><tr><td>__LOADED_AT</td><td>UTC timestamp of when this data was transformed from the source table into this table</td></tr><tr><td>__SYNCED_AT</td><td>UTC timestamp of when this data was loaded from the external source into the database</td></tr></tbody></table>

### **Subscriptions**

Purpose: Normalizes subscription metadata from multiple subscription platforms into a single table

Table Name: `uss.subscriptions`

Related transform code:

* [SKIO](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/skio/1801_USS_BAS_SKIO_subscriptions.sql)
* [Stay Ai](https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/1800_uss/stay_ai/1801_USS_BAS_STAY_AI_subscriptions.sql)

<table data-full-width="true"><thead><tr><th>Column</th><th>Description</th></tr></thead><tbody><tr><td>SUBSCRIPTION_ID🔑</td><td>MD5 of the platform identifier and the source subscription ID</td></tr><tr><td>SOURCE_SUBSCRIPTION_ID</td><td>Subscription ID from the source system</td></tr><tr><td>CREATED_AT</td><td>Timestamp of when the subscription was created. This is converted into your Daasity account timezone</td></tr><tr><td>UPDATED_AT</td><td>Timestamp of when the subscription was last updated. This is converted into your Daasity account timezone</td></tr><tr><td>PAUSED_AT</td><td>Timestamp of when the subscription was paused. This is converted into your Daasity account timezone</td></tr><tr><td>PAUSED_UNTIL</td><td>Timestamp of when the subscription is paused until. This is converted into your Daasity account timezone</td></tr><tr><td>CHURNED_AT</td><td>Timestamp of when the subscription was cancelled. This is converted into your Daasity account timezone</td></tr><tr><td>CANCELLED_AT</td><td>Timestamp of when the subscription was cancelled. This is converted into your Daasity account timezone</td></tr><tr><td>CANCELLATION_REASON</td><td>Reason from the source system of why the subscription was cancelled</td></tr><tr><td>CANCELLATION_REASON_COMMENTS</td><td>Additional information from the source system of why the subscription was cancelled</td></tr><tr><td>CUSTOMER_ID</td><td>MD5 of the platform identifier and the customer ID from the source system</td></tr><tr><td>SOURCE_CUSTOMER_ID</td><td>Customer ID from the source system</td></tr><tr><td>EMAIL_ADDRESS</td><td>Email address of the subscriber</td></tr><tr><td>FIRST_NAME</td><td>First name of the subscriber</td></tr><tr><td>LAST_NAME</td><td>Last name of the subscriber</td></tr><tr><td>DELIVERY_ADDRESS_LINE_1</td><td>Delivery address line 1 from the source system</td></tr><tr><td>DELIVERY_ADDRESS_LINE_2</td><td>Delivery address line 2 from the source system</td></tr><tr><td>DELIVERY_ADDRESS_ID</td><td>Address ID from the source system</td></tr><tr><td>DELIVERY_ADDRESS_CITY</td><td>Delivery address city from the source system</td></tr><tr><td>DELIVERY_ADDRESS_COMPANY</td><td>Delivery address company from the source system</td></tr><tr><td>DELIVERY_ADDRESS_COUNTRY_CODE</td><td>Delivery address country code from the source system</td></tr><tr><td>DELIVERY_ADDRESS_PHONE</td><td>Delivery phone number from the source system</td></tr><tr><td>DELIVERY_ADDRESS_PROVINCE</td><td>Delivery address province from the source system</td></tr><tr><td>DELIVERY_ADDRESS_ZIP</td><td>Delivery address ZIP Code from the source system</td></tr><tr><td>DELIVERY_PRICE</td><td>Cost to the consumer for delivering the order</td></tr><tr><td>DUNNING_STARTED_AT</td><td>Timestamp of when the subscription entered dunning. This is converted into your Daasity account timezone</td></tr><tr><td>DUNNING_STORY_ID</td><td>Dunning story ID from the source system</td></tr><tr><td>DUNNING_EXITED_AT</td><td>Timestamp of when the subscription exited dunning. This is converted into your Daasity account timezone</td></tr><tr><td>LAST_CHARGE_DATE</td><td>Timestamp of when the last charge was made related to the subscription. This is converted into your Daasity account timezone</td></tr><tr><td>NEXT_CHARGE_DATE</td><td>Timestamp of when the next charge will be made for this subscription. This is converted into your Daasity account timezone</td></tr><tr><td>IS_SKIPPABLE</td><td>Indicates whether the subscription can be skipped</td></tr><tr><td>IS_SWAPPABLE</td><td>Indicates whether products in the subscription can be swapped out</td></tr><tr><td>CURRENCY</td><td>Currency for the dollar values for this order</td></tr><tr><td>PRICE</td><td>Price of the subscription</td></tr><tr><td>NOTE</td><td>Note for the subscription</td></tr><tr><td>ORDER_DAY_OF_MONTH</td><td>Scheduled day of the month for this order</td></tr><tr><td>ORDER_DAY_OF_WEEK</td><td>Scheduled day of the week for this order</td></tr><tr><td>ORDER_INTERVAL_FREQUENCY</td><td>How often the subscription is fulfilled</td></tr><tr><td>ORDER_INTERVAL_UNIT</td><td>How often the subscription is fulfilled</td></tr><tr><td>STATUS</td><td>Indicates whether the subscription is cancelled, paused, or active</td></tr><tr><td>STATUS_CONTEXT</td><td>Additional context for the status</td></tr><tr><td>PLATFORM</td><td>Identifier for the source platform</td></tr><tr><td>__LOADED_AT</td><td>UTC timestamp of when this data was transformed from the source table into this table</td></tr><tr><td>__SYNCED_AT</td><td>UTC timestamp of when this data was loaded from the external source into the database</td></tr></tbody></table>