fct_contract


Description

The contract between a customer and a gym (organization_unit). Includes different contract_types (CONTRACT, MODULE, FLAT_FEE see dim_rate.contract_type) and reversed contracts (see is_reversed).

Columns

Column Type Size Nulls Auto Default Children Parents Comments
contract_id int8 19 null
dim_customer_group.contract_id erp_v2_dim_customer_group_contract_id__erp_v2_fct_contract_contract_id_fkey_f3bb8a9435ed R
fct_contract.main_contract_id erp_v2_fct_contract_main_contract_id__erp_v2_fct_contract_contract_id_fkey_3bac2f809e19 R
fct_contract.subsequent_contract_id erp_v2_fct_contract_subsequent_contract_id__erp_v2_fct_contract_contract_id_fkey_067fa8c35887 R
fct_contract_price_history.contract_id erp_v2_fct_contract_price_history_contract_id__erp_v2_fct_contract_contract_id_fkey_7ec3efc38ad2 R
fct_contract_term_dates.contract_id erp_v2_fct_contract_term_dates_contract_id__erp_v2_fct_contract_contract_id_fkey_212b7c43ac65 R
fct_contract_voucher.contract_id erp_v2_fct_contract_voucher_contract_id__erp_v2_fct_contract_contract_id_fkey_ba952ea0cd6e R
fct_idle_period.contract_id erp_v2_fct_idle_period_contract_id__erp_v2_fct_contract_contract_id_fkey_310391a1376b R
fct_invoice.contract_id Implied Constraint R
fct_revenue.contract_id erp_v2_fct_revenue_contract_id__erp_v2_fct_contract_contract_id_fkey_c41c7b632840 R
fct_revenue_cash_based.contract_id erp_v2_fct_revenue_cash_based_contract_id__erp_v2_fct_contract_contract_id_fkey_c182b8d8ecd4 R
fct_revenue_invoice_based.contract_id erp_v2_fct_revenue_invoice_based_contract_id__erp_v2_fct_contract_contract_id_fkey_4059737dd498 R

The primary key of this table. System column calculated by the source System.
imported_contract_number varchar 256 √ null

The contract number as it was set in a previous system the contract was imported from. Only set if it was specifically required in the import-process.
third_party_id varchar 256 √ null

A unique identifier of the contract in a third-party system. This is used to link the contract to a third-party system, like a CRM or ERP system.
main_contract_id int8 19 √ null
fct_contract.contract_id erp_v2_fct_contract_main_contract_id__erp_v2_fct_contract_contract_id_fkey_3bac2f809e19 R

The related main (membership) contract, if this contract is a module or flat fee that can only be booked in addition to a membership.
is_reversed bool 1 √ null

Indicates if the contract has been reversed, effectively nullifying any associated payments and treating the contract as if it never existed in the originating system.
created_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

Date when the contract was added to the Systems database. Contracts might be imported into the System after the actual sign/conclusion date. There is no information when a contract was actually signed/concluded because it’s not provided on the migration stage to the System.
start_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

Date when the contract starts.
start_date_of_use_id int4 10 √ null

Date when the customer can already visit the facility. If the customer is allowed to use the gym before the actual contract starts this column contains a date which is before the start_date_id of the contract. Otherwise this column is equal to start_date_id.
end_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

Date when the contract ends.
first_booking_date date 13 √ null

The date scheduled for the contract’s first charge, based on the contract’s payment frequency and billing configuration. Is Null for a small share of contracts where this has not yet been calculated. For contracts migrated from a previous system, this value is known to be occasionally inconsistent with the contract’s own start and end dates, since it reflects the source system’s calculation at the time of migration rather than a live re-derivation. This value is populated as-is regardless of is_reversed; consumers should filter or flag reversed contracts (is_reversed = true) separately if they need to exclude them.
cancellation_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

Date when a contracts cancellation comes into effect, ending the contract. Is Null for contracts which are not cancelled.
cancellation_receipt_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

The date when the receipt of cancellation was provided. Is Null for contracts which are not cancelled.
pre_cancellation_end_date_id int4 10 √ null
dim_date.date_id Implied Constraint R

The contract end date before cancellation. Is Null for contracts which are not cancelled.
rate_id int8 19 √ null
dim_rate.rate_id erp_v2_fct_contract_rate_id__erp_v2_dim_rate_rate_id_fkey_6c24ad00ff02 R

The tariff of the contract generating an income.
organization_unit_id int8 19 √ null
dim_organization_unit.organization_unit_id erp_v2_fct_contract_organization_unit_id__erp_v2_dim_organization_unit_organization_unit_id_fkey_b1c3c75d1a29 R

The gym (organization_unit) to which the customer and therefore the contract belongs.
employee_id int8 19 √ null
dim_employee.employee_id erp_v2_fct_contract_employee_id__erp_v2_dim_employee_employee_id_fkey_54e92a4e8fd8 R

The employee responsible for making the customer sign the contract. Falls back to the employee whose user account created the contract when no explicit employee assignment exists. Is Null only when neither can be resolved.
employee_id_source varchar 256 √ null

The source of employee_id: CONTRACT_EMPLOYEE when explicitly associated with the contract, or CONTRACT_CREATOR when resolved from the user account that created the contract. Is Null when no employee can be resolved.
customer_id int8 19 √ null
dim_customer.customer_id erp_v2_fct_contract_customer_id__erp_v2_dim_customer_customer_id_fkey_37033086980d R

The person who the contract belongs to. Is set to Null when privacy settings come into effect.
contract_property_id varchar 256 √ null
dim_contract_property.contract_property_id erp_v2_fct_contract_contract_property_id__erp_v2_dim_contract_property_contract_property_id_fkey_ed2c046c6e2a R

ID of the contract property, e.g signature_status, disabled, etc.
contract_term_id varchar 256 √ null
dim_contract_term.contract_term_id erp_v2_fct_contract_contract_term_id__erp_v2_dim_contract_term_contract_term_id_fkey_313196804702 R

ID of the contract term.
contract_cancellation_reason_id varchar 256 √ null
dim_contract_cancellation_reason.contract_cancellation_reason_id erp_v2_fct_contract_contract_cancellation_reason_id__erp_v2_dim_contract_cancellation_reason_contract_cancella_2593f16b1f8a R

The reason why the contract was cancelled. Is Null for contracts which are not cancelled.
company_id int8 19 √ null
dim_company.company_id erp_v2_fct_contract_company_id__erp_v2_dim_company_company_id_fkey_8ca1239364d9 R

The partner company (Corporate Fitness) assigned to this contract directly or by a voucher effective at the current date.
discount_campaign_id int8 19 √ null
dim_discount_campaign.discount_campaign_id erp_v2_fct_contract_discount_campaign_id__erp_v2_dim_discount_campaign_discount_campaign_id_fkey_acd88b13dfa5 R

The discount campaign that was active when this contract was concluded and whose discount was applied. NULL for the vast majority of contracts (feature not yet widely used in production). Use dim_discount_campaign to access campaign details such as name, validity window, and voucher interoperability mode.
contract_payment_frequency_id varchar 256 √ null
dim_contract_payment_frequency.contract_payment_frequency_id erp_v2_fct_contract_contract_payment_frequency_id__erp_v2_dim_contract_payment_frequency_contract_payment_freq_9af692569944 R

The setting of how often and how much is paid for this contract.
rate_bundle_term_id int8 19 √ null
dim_rate_bundle_term.rate_bundle_term_id erp_v2_fct_contract_rate_bundle_term_id__erp_v2_dim_rate_bundle_term_rate_bundle_term_id_fkey_4651a84a9af7 R

The rate bundle term the contract was sold with, resolved from the accepted contract proposal. NULL for imported or legacy contracts that were not created through a proposal.
subsequent_contract_id int8 19 √ null
fct_contract.contract_id erp_v2_fct_contract_subsequent_contract_id__erp_v2_fct_contract_contract_id_fkey_067fa8c35887 R

The follow-up contract created after this contract expires, linked via a contract switch. NULL if no subsequent contract exists.
cancellation_fee numeric 23,6 √ null

Amount of fee that a customer must pay for the cancellation. Is Null for contracts which are not cancelled.
original_monthly_price float8 17,17 √ null

The estimated price, amortized over the contracts (initial) term, that has to be paid for this contract each month. By the original contract settings, without taking adjustments or contract starts in between payment periods into account. See fct_revenue for more accurate data.
current_monthly_price float8 17,17 √ null

The estimated price, amortized over the contracts term, that has to be paid for this contract each month. Taking adjustments until the current date but not contract starts in between payment periods into account. See fct_revenue for more accurate data.
original_total_price float8 17,17 √ null

The estimated price, that has to be paid for this contracts (initial) term in total. By the original contract settings, without taking adjustments or contract starts in between payment periods into account. See fct_revenue for more accurate data.
last_date_before_contract_renewal date 13 √ null

The last date that the user can cancel the contract ordinarily before automatic renewal or contract switch. Value is null for non-recurring contracts.
last_updated timestamptz 35 √ null

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

System column. UTC timestamp assigned when a table batch is applied on Redshift. It represents warehouse landing order at table-batch granularity, not source event order. Usable to filter for incremental extractions.

Relationships

Close relationships within degrees of separation