Tulo Engage BI

Order model

Timestamps

All timestamps are converted to local time for the organisation.

The order model in Engage BI can be found in schema engage_bi and is available in the view pw_orders_view. The data in pw_orders_view is materialized nightly into the table pw_orders which is the table queries should be executed against.

The order model shares some data with the subcription model and also includes information from related tables, minimizing the need for complex joins with other tables.

The orders model contains the following columns.

column	type	comments
id	string	Id of the order, this is the id from the payway_data.pw_orders table.
organisation_id	string	The organisation that owns the subscription
order_id	string	The user-friendly orderId of the order.
account_id	string	Reference to the account owning or receiving the subscription. This is the id in the pw_accounts table.
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 orders.
product_name	string	Product name for the order.
product_type	string	Type of product in this order. Codes and values.
product_title_code	string	Title this order belongs to.
product_title_name	string	Name of title this order belongs to.
product_tags	string	Comma separated list of tags for the product
campaign_product_code	string	The product code the campaign is based upon if order is campaign.
campaign_product_name	string	Campaign product name if order is campaign.
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 order/subscription is started.
order_type	string	Type of order, "normal", "no_order", or "voucher" if order was created through redeeming a voucher.
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.
subscription_category	string	Indicates if the order is an actual "subscription" or a "lead"
subscription_type	string	Indicates if the order is "recurring", "limited" or "free"
payer_company_name	string	Name of payer company, if any.
payer_email	string	Email of payer.
traffic_source	string	Order traffic source
merchant_reference	string	Order merchant reference
paywall_id	string	Paywall ID if order was created from a Paywall purchase
paywall_name	string	Paywall name if order was created from a Paywall purchase