Tulo Engage BI¶
Subscription model¶
Timestamps
All timestamps are converted to local time for the organisation.
The subscription model contains all subscriptions, including shared subscriptions. It also includes information from related tables, minimizing the need for complex joins with other tables.
The subscription model in Engage BI can be found in schema engage_bi and is available in the view pw_subscriptions_view which in turn consists of data from views pw_normal_subscriptions_view and pw_shared_subscriptions_view. The data in pw_subscriptions_view is materialized nightly into the table pw_subscriptions which is the table queries should be executed against.
The subscriptions model contains the following columns.
| column | type | comments |
|---|---|---|
| subscription_id | string | Reference id of the subscription, this is the id from the pw_user_products table. |
| organisation_id | string | The organisation that owns the subscription |
| order_id | string | Reference id on the order for the subscription, this is the id in the pw_orders table. |
| order_order_id | string | The user-friendly orderId of the order. |
| created_from | string | Indicates how the subscription was created, from "migration" or a "purchase" for example. Codes and values. |
| subscription_type | string | Indicates if the subscription is "recurring", "limited" or "free". Codes and values. |
| subscription_category | string | Indicates if the subscription is an actual "subscription" or a "lead". Codes and values. |
| subscription_status | string | Indicates current status of subscription, could be "activated", "deactivated", "cancelled" or "pending". Codes and values |
| subscription_valid_from | timestamp | Timestamp indicating the date and time the subscription is valid from. |
| subscription_valid_to | timestamp | Timestamp indicating the date and time the subscription is valid to. |
| cancel_reason | string | Reason for cancellation, if subscription has been cancelled, the reason codes are organisation specific. |
| subscription_cancel_date | timestamp | Timestamp indicating when subscription has been cancelled. |
| deactivation_reason | string | The reason for deactivation of the subscription if it is deactivated. Codes and values. |
| subscription_deactivation_date | timestamp | Timestamp indicating when the subscription has been deactivated. |
| subscription_share_status | string | Indicates if the subscription is shared. Codes and values. |
| subscription_business_model | string | Indicates if the subscription belongs to a company ("b2b") or a private person ("b2c"). Determined by the package property "Company package" which is accessible from "Field configuration" on a package. |
| subscription_created | timestamp | Timestamp indicating when subscription was created. |
| account_id | string | Reference to the account owning or receiving the subscription. This is the id in the payway_data.pw_accounts or engage_bi.pw_customers tables. |
| customer_age | int | Age of customer if known. |
| customer_gender | string | Gender of customer if known, "M" = Male or "F" = Female, "?" if unknown. |
| product_code | string | Product code for the subscription. |
| product_name | string | Product name for the subscription. |
| campaign_product_code | string | The product code the campaign is based upon if subscription is campaign. |
| campaign_product_name | string | Campaign product name if subscription is campaign. |
| product_type | string | Type of product in this subscription. Codes and values. |
| product_title_code | string | Title this subscription belongs to. |
| product_title_name | string | Name of title this subscription belongs to. |
| product_tags | string | Comma separated list of tags for the subscription. |
| order_category | string | Category of order. Codes and values. |
| order_date | timestamp | Timestamp for when the order was created. |
| order_start_date | timestamp | Timestamp for when the subscription is started. |
| order_type | string | Type of order, "normal", "no_order", or "voucher" if order was created through redeeming a voucher. Subscriptions received through sharing has empty "order_type". |
| order_period_price | decimal | Price on current order |
| order_period_type | string | Period type on current order. "year", "month", "week" or "day". |
| order_period_length | int | Length of period |
| order_payment_method | string | Payment method used when purchasing order. Codes and values. |
| parent_order_date | timestamp | Order date for parent order. |
| provision_service | string | Indicates which service is responsible for the lifecycle of the subscription. |
Subscription periods¶
A subscriptions "valid_from" and "valid_to" timestamps indicates when the subscription is considered to be "active" and give access to the subscriptions contents. What it doesn't tell is the entire story about what has happened with the subscription during it's lifetime. A subscription can be deactivated and reactivated due to different actions the customer makes, such as forgetting to pay in time.
To get a historic view of how the subscription has been active or inactive, the subscription periods needs to be investigated. A subscription period record illustrates between which dates a subscription as either been activated or deactivated.
Subscription periods are located in schema engage_bi and is available in the view pw_subscription_periods_view which is materialized nightly into the table pw_subscription_periods.
Columns:
| column | type | comments |
|---|---|---|
| subscription_id | string | The subscription ID |
| state | string | Indicates which state for period. Either "activated" or "deactivated". |
| start_date | date | Start date of subscription period |
| end_date | date | End date of sub subscription period |
Period rules¶
The subscription periods are determined based on a set of rules.
- Periods are only operating on dates and not on timestamps.
- Periods don't overlap.
- If there are multiple changes to a subscription's state during a day, it is the last state that is the valid for that day.
- A subscription cannot be active on the same day as it is deactivated, so if a subscription is deactivated on one day, the active period ended the previous day.
- Following the rule above, if a subscription has been created/activated and deactivated the same day, it is removed since it cannot be deactivated without ever being active.
- If a subscription is still active, the end date for the period will always be todays date.
Querying¶
To find if a subscription has ever been active on a specific date, the following query can be used:
SELECT subscription_id
FROM engage_bi.pw_subscription_periods
WHERE state = 'activated'
AND 'YYYY-MM-DD' BETWEEN start_date AND end_date