dim_purchased_contingent


Description

Service packages purchased by customers that grant access to specific services for a defined number of uses or duration. These are standalone purchases (not included in membership contracts) such as day passes, class packs, personal training packages, or time-based access. Tracks purchase details, remaining availability, expiration dates, and usage rates for revenue analysis and customer service optimization.

Columns

Column Type Size Nulls Auto Default Children Parents Comments
purchased_contingent_id int8 19 null
bridge_revenue_to_purchased_contingent.purchased_contingent_id Implied Constraint R
fct_class_event.purchased_contingent_id Implied Constraint R
fct_customer_appointment.purchased_contingent_id Implied Constraint R
fct_service_usage.purchased_contingent_id Implied Constraint R

Unique identifier for the purchased service package.
name varchar 256 √ null

Display name of the service package as shown to customers at point of sale (e.g., ‘10-Class Pass’, ‘Day Pass’, ‘60-Minute Personal Training’). NULL for packages not created through a sale transaction.
variant_name varchar 256 √ null

Product variant specification indicating size, duration, or quantity (e.g., ‘10 sessions’, ‘30 days’, ‘Single Entry’). NULL for packages not created through a sale transaction.
item_number varchar 256 √ null

Product identifier used for inventory and sales tracking across the organization. NULL for packages not created through a sale transaction.
num_of_usage int4 10 √ null

Total number of times this package can be used as originally purchased (e.g., 10 for a 10-class pass, 1 for a day pass). For time-based packages, represents duration in minutes.
price numeric 19,2 √ null

Amount the customer paid for this service package at the time of purchase, potentially including discounts applied during the transaction. NULL for packages not created through a sale transaction.
service_id int8 19 √ null
dim_service.service_id Implied Constraint R

The specific service or benefit that this package grants access to (e.g., group fitness classes, personal training, sauna access).
customer_id int8 19 √ null
dim_customer.customer_id Implied Constraint R

The customer who owns this service package and can use the remaining credits.
organization_unit_id int8 19 √ null
dim_organization_unit.organization_unit_id Implied Constraint R

The gym location where this package was purchased. May determine where the package can be used depending on service access rules. NULL for packages not created through a sale transaction.
purchase_time timestamp 29,6 √ null

Exact date and time when the package was purchased, shown in the gym’s local timezone for accurate business hours analysis. NULL for packages not created through a sale transaction.
product_id varchar 256 √ null
dim_product.product_id Implied Constraint R
contingent_type varchar 256 √ null

How usage is measured: APPOINTMENT_BASED (counted by number of bookings/visits), TIME_BASED (measured in minutes of usage), CHECKIN_BASED (entry-based access), USAGE_BASED (general usage counting), BUNDLE_BASED (package of services), or BUDDY_BASED (allows bringing guests).
available_usages int4 10 √ null

Current number of remaining uses or minutes available. Decreases each time the customer uses the service. NULL if expired or fully consumed.
duration_in_minutes int4 10 √ null

For time-based packages, the total minutes of access included. NULL for count-based packages like class passes or day tickets.
validity_type varchar 256 √ null

How the usage validity window is calculated: ONE_TIME (single use with no extended validity), DAY (valid until end of day), or PERIOD (valid for a specified duration).
validity_term_value int4 10 √ null

The numeric portion of the validity duration (e.g., 30 for ‘30 days’, 3 for ‘3 months’). Combined with validity_term_unit. NULL for packages without time-based validity.
validity_term_unit varchar 256 √ null

Time unit for validity period: DAY, WEEK, MONTH, or YEAR. Combined with validity_term_value to determine how long each usage remains valid after consumption.
expiry_date date 13 √ null

The last date this package can be used. After this date, any remaining usage credits are forfeited and cannot be redeemed.
usages_valid_until timestamp 29,6 √ null

For packages with per-usage validity windows, the timestamp when already-consumed uses expire in the local timezone of the studio. Applies to packages where each use has its own validity period.
usage_rate numeric 5,2 √ null

Proportion of the package that has been consumed, from 0.0 (completely unused) to 1.0 (fully exhausted). Useful for identifying abandoned or underutilized packages.
last_updated timestamptz 35 √ null

System column. UTC Timestamp at which entry was calculated (lags shortly behind source system).

Relationships

Close relationships within degrees of separation