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.

To identify day passes: filter on contingent_type = ‘CHECKIN_BASED’ AND num_of_usage = 1. There is no explicit DAY_PASS enum value; day passes are distinguished from other CHECKIN_BASED packages by having num_of_usage = 1 and validity_type = ‘DAY’. Day passes are sold at POS (not as contracts) and will have price IS NOT NULL when linked to a sale_position. To find day pass check-in redemptions, join to fct_service_usage on purchased_contingent_id.

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 dim_purchased_contingent_service_id_fkey 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 dim_purchased_contingent_customer_id_fkey 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 dim_purchased_contingent_organization_unit_id_fkey 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 dim_purchased_contingent_product_id_fkey 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). Day passes use CHECKIN_BASED with num_of_usage = 1 — there is no separate DAY_PASS type.
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.
valid_from timestamp 29,6 √ null

The date and time from which this purchased contingent becomes available for use, shown in the gym’s local timezone. If not set, the contingent is available immediately upon purchase. Paired with expiry_date to define the overall activation window of the contingent.
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