Collections Fact Consolidated
ZTZenoti Docs Team Updated April 10, 2026 14 min readGuide
Consolidated collections fact table combining payment collection details with guest, employee, service, product, membership, package, gift card, and promotion dimensions for comprehensive payment and collections analytics.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| organization name | VARCHAR(512) | Yes | Name of the organization. Sourced from Dim_Organization.organization_name. |
| organization account name | VARCHAR(256) | Yes | Account name of the organization. Sourced from Dim_Organization.organization_account_name. |
| zone name | VARCHAR(800) | Yes | Name of the zone the center belongs to. Sourced from Dim_Center.zone_name. |
| center name | VARCHAR(128) | Yes | Name of the center where the collection occurred. Sourced from Dim_Center.center_name. |
| center Time Zone | VARCHAR(128) | Yes | Time zone of the center. Sourced from Dim_Center.center_time_zone. |
| center city | VARCHAR(128) | Yes | City where the center is located. Sourced from Dim_Center.center_city. |
| center zip | VARCHAR(32) | Yes | Zip/postal code of the center. Sourced from Dim_Center.center_zip. |
| center state name | VARCHAR(4096) | Yes | State name of the center. Sourced from Dim_Center.center_state_name. |
| country name | VARCHAR(4096) | Yes | Country name of the center. Sourced from Dim_Center.country_name. |
| center company name | VARCHAR(200) | Yes | Company name of the center. Sourced from Dim_Center.company_name. |
| operating center | BOOLEAN | Yes | Whether the center is an operating center. Sourced from Dim_Center.operating_center. |
| center wid | BIGINT | Yes | Warehouse identifier for the center dimension. Sourced from Dim_Center.wid. |
| user wid | BIGINT | Yes | Warehouse identifier for the guest/user dimension. Sourced from Dim_User.wid. |
| user id | VARCHAR(36) | Yes | Unique identifier (GUID) of the guest/user. Sourced from Dim_User.user_id. |
| guest first name | VARCHAR(160) | Yes | First name of the guest. Sourced from Dim_User.first_name. |
| guest last name | VARCHAR(256) | Yes | Last name of the guest. Sourced from Dim_User.last_name. |
| guest middle name | VARCHAR(128) | Yes | Middle name of the guest. Sourced from Dim_User.middle_name. |
| guest city | VARCHAR(128) | Yes | City of the guest. Sourced from Dim_User.city. |
| guest zipcode | VARCHAR(128) | Yes | Zip/postal code of the guest. Sourced from Dim_User.zipcode. |
| guest type name | VARCHAR(128) | Yes | Type classification of the guest. Sourced from Dim_User.guest_type_name. |
| guest email | VARCHAR(256) | Yes | Email address of the guest. Sourced from Dim_User.email. |
| guest mobile phone | VARCHAR(64) | Yes | Mobile phone number of the guest. Sourced from Dim_User.mobile_phone. |
| guest home phone | VARCHAR(64) | Yes | Home phone number of the guest. Sourced from Dim_User.home_phone. |
| guest phone code | INTEGER | Yes | Phone country code of the guest. Sourced from Dim_User.phone_code. |
| guest code | VARCHAR(128) | Yes | Unique code of the guest. Sourced from Dim_User.guest_code. |
| guest gender | VARCHAR(14) | Yes | Gender of the guest. Sourced from Dim_User.gender. |
| guest dob | TIMESTAMP | Yes | Date of birth of the guest. Sourced from Dim_User.dob. |
| referred by guestid | VARCHAR(36) | Yes | GUID of the guest who referred this guest. Sourced from Dim_User.referred_by_guestid. |
| guest anniversary date | TIMESTAMP | Yes | Anniversary date of the guest. Sourced from Dim_User.anniversary_date. |
| guest state | VARCHAR(4096) | Yes | State of the guest address. Sourced from Dim_User.state. |
| guest base center | VARCHAR(256) | Yes | Base/home center of the guest. Sourced from Dim_User.base_center. |
| guest country name | VARCHAR(4096) | Yes | Country name of the guest. Sourced from Dim_User.country_name. |
| is guest inactive | BOOLEAN | Yes | Whether the guest is inactive. Sourced from Dim_User.is_inactive. |
| guest tags | VARCHAR(65535) | Yes | Tags associated with the guest. Sourced from Dim_User.tags. |
| guest referral source | VARCHAR(1600) | Yes | Referral source of the guest. Sourced from Dim_User.referral_source. |
| No Sale Comments | VARCHAR(MAX) | Yes | Comments on no-sale transactions. Sourced from Fact_Collection.no_sale_comments. |
| employee first name | VARCHAR(160) | Yes | First name of the employee. Sourced from Dim_Employee.first_name. |
| employee last name | VARCHAR(256) | Yes | Last name of the employee. Sourced from Dim_Employee.last_name. |
| employee middle name | VARCHAR(128) | Yes | Middle name of the employee. Sourced from Dim_Employee.middle_name. |
| employee mobile phone | VARCHAR(64) | Yes | Mobile phone of the employee. Sourced from Dim_Employee.mobile_phone. |
| employee code | VARCHAR(128) | Yes | Code of the employee. Sourced from Dim_Employee.employee_code. |
| employee - code | VARCHAR(128) | Yes | Alternate code of the employee. Sourced from Dim_Employee.employee_code_alt. |
| employee gender | VARCHAR(14) | Yes | Gender of the employee. Sourced from Dim_Employee.gender. |
| employee job name | VARCHAR(1020) | Yes | Job title of the employee. Sourced from Dim_Employee.job_name. |
| employee job code | VARCHAR(1020) | Yes | Job code of the employee. Sourced from Dim_Employee.job_code. |
| is employee inactive | BOOLEAN | Yes | Whether the employee is inactive. Sourced from Dim_Employee.is_inactive. |
| employee tags | VARCHAR(65535) | Yes | Tags associated with the employee. Sourced from Dim_Employee.tags. |
| closed by employee first name | VARCHAR(160) | Yes | First name of the employee who closed the invoice. Sourced from Dim_Employee.first_name (closed by). |
| closed by employee last name | VARCHAR(256) | Yes | Last name of the employee who closed the invoice. Sourced from Dim_Employee.last_name (closed by). |
| closed by employee middle name | VARCHAR(128) | Yes | Middle name of the employee who closed the invoice. Sourced from Dim_Employee.middle_name (closed by). |
| closed by employee mobile phone | VARCHAR(64) | Yes | Mobile phone of the closing employee. Sourced from Dim_Employee.mobile_phone (closed by). |
| closed by employee code | VARCHAR(128) | Yes | Code of the closing employee. Sourced from Dim_Employee.employee_code (closed by). |
| closed by employee - code | VARCHAR(128) | Yes | Alternate code of the closing employee. Sourced from Dim_Employee.employee_code_alt (closed by). |
| closed by employee gender | VARCHAR(14) | Yes | Gender of the closing employee. Sourced from Dim_Employee.gender (closed by). |
| closed by employee job name | VARCHAR(1020) | Yes | Job title of the closing employee. Sourced from Dim_Employee.job_name (closed by). |
| closed by employee is inactive | BOOLEAN | Yes | Whether the closing employee is inactive. Sourced from Dim_Employee.is_inactive (closed by). |
| closed by start date | TIMESTAMP | Yes | Start date of the closing employee. Sourced from Dim_Employee.start_date (closed by). |
| closed by last date | TIMESTAMP | Yes | Last working date of the closing employee. Sourced from Dim_Employee.last_date (closed by). |
| service name | VARCHAR(512) | Yes | Name of the service. Sourced from Dim_Service.service_name. |
| service length | INTEGER | Yes | Duration of the service in minutes. Sourced from Dim_Service.service_length. |
| service category | VARCHAR(256) | Yes | Category of the service. Sourced from Dim_Service.service_category. |
| service parent category | VARCHAR(256) | Yes | Parent category of the service. Sourced from Dim_Service.parent_category. |
| service has variant | VARCHAR(14) | Yes | Whether the service has variants. Sourced from Dim_Service.has_variant. |
| service is variant | VARCHAR(14) | Yes | Whether the service is itself a variant. Sourced from Dim_Service.is_variant. |
| is addon | BOOLEAN | Yes | Whether the service is an add-on. Sourced from Dim_Service.is_addon. |
| service businessunit | VARCHAR(200) | Yes | Business unit of the service. Sourced from Dim_Service.businessunit. |
| is service inactive | BOOLEAN | Yes | Whether the service is inactive. Sourced from Dim_Service.is_inactive. |
| service tags | VARCHAR(65535) | Yes | Tags associated with the service. Sourced from Dim_Service.tags. |
| product name | VARCHAR(256) | Yes | Name of the product. Sourced from Dim_Product.product_name. |
| product category | VARCHAR(256) | Yes | Category of the product. Sourced from Dim_Product.product_category. |
| product parent category | VARCHAR(256) | Yes | Parent category of the product. Sourced from Dim_Product.parent_category. |
| product business unit | VARCHAR(200) | Yes | Business unit of the product. Sourced from Dim_Product.business_unit. |
| consummable | BOOLEAN | Yes | Whether the product is consumable. Sourced from Dim_Product.consummable. |
| product has variant | BOOLEAN | Yes | Whether the product has variants. Sourced from Dim_Product.has_variant. |
| retail | BOOLEAN | Yes | Whether the product is a retail product. Sourced from Dim_Product.retail. |
| is product inactive | BOOLEAN | Yes | Whether the product is inactive. Sourced from Dim_Product.is_inactive. |
| product tags | VARCHAR(65535) | Yes | Tags associated with the product. Sourced from Dim_Product.tags. |
| membership name | VARCHAR(512) | Yes | Name of the membership. Sourced from Dim_Membership.membership_name. |
| membership category | VARCHAR(200) | Yes | Category of the membership. Sourced from Dim_Membership.membership_category. |
| membership credit amount | FLOAT8 | Yes | Credit amount of the membership. Sourced from Dim_Membership.credit_amount. |
| membership recurring | VARCHAR(5) | Yes | Whether the membership is recurring (Yes/No). Sourced from Dim_Membership.recurring. |
| membership type | VARCHAR(50) | Yes | Type of the membership. Sourced from Dim_Membership.membership_type. |
| membership tags | VARCHAR(65535) | Yes | Tags associated with the membership. Sourced from Dim_Membership.tags. |
| package name | VARCHAR(512) | Yes | Name of the package. Sourced from Dim_Package.package_name. |
| package type | VARCHAR(14) | Yes | Type of the package. Sourced from Dim_Package.package_type. |
| package category | VARCHAR(200) | Yes | Category of the package. Sourced from Dim_Package.package_category. |
| package businessunit | VARCHAR(200) | Yes | Business unit of the package. Sourced from Dim_Package.businessunit. |
| is package inactive | BOOLEAN | Yes | Whether the package is inactive. Sourced from Dim_Package.is_inactive. |
| package tags | VARCHAR(65535) | Yes | Tags associated with the package. Sourced from Dim_Package.tags. |
| giftcard code | VARCHAR(256) | Yes | Code of the gift card. Sourced from Dim_GiftCard.giftcard_code. |
| giftcard recipient name | VARCHAR(1024) | Yes | Recipient name of the gift card. Sourced from Dim_GiftCard.recipient_name. |
| giftcard value | NUMERIC(19,4) | Yes | Value of the gift card. Sourced from Dim_GiftCard.giftcard_value. |
| giftcard purchase price | NUMERIC(19,4) | Yes | Purchase price of the gift card. Sourced from Dim_GiftCard.purchase_price. |
| giftcard validity | INTEGER | Yes | Validity period of the gift card in days. Sourced from Dim_GiftCard.validity. |
| Is Bonus Giftcard | BOOLEAN | Yes | Whether the gift card is a bonus gift card. Sourced from Dim_GiftCard.is_bonus_giftcard. |
| Promotion Name | NVARCHAR(2048) | Yes | Name of the promotion. Sourced from Dim_GiftCard.promotion_name. |
| Promotion Code | NVARCHAR(512) | Yes | Code of the promotion. Sourced from Dim_GiftCard.promotion_code. |
| Promotion Description | NVARCHAR(32000) | Yes | Description of the promotion. Sourced from Dim_GiftCard.promotion_description. |
| Prmotion Rule Criteria | NVARCHAR(2000) | Yes | Rule criteria of the promotion. Sourced from Dim_GiftCard.promotion_rule_criteria. |
| Promotion Rule Minimum Value | DECIMAL(19,4) | Yes | Minimum value for the promotion rule. Sourced from Dim_GiftCard.promotion_rule_minimum_value. |
| Promotion Rule Maximum Value | DECIMAL(19,4) | Yes | Maximum value for the promotion rule. Sourced from Dim_GiftCard.promotion_rule_maximum_value. |
| Promotion Rule Bonus Value | DECIMAL(19,4) | Yes | Bonus value for the promotion rule. Sourced from Dim_GiftCard.promotion_rule_bonus_value. |
| one time use | BOOLEAN | Yes | Whether the gift card is single-use. Sourced from Dim_GiftCard.one_time_use. |
| is giftcard | BOOLEAN | Yes | Whether the item is a gift card. Sourced from Dim_GiftCard.is_giftcard. |
| is giftcard inactive | BOOLEAN | Yes | Whether the gift card is inactive. Sourced from Dim_GiftCard.is_inactive. |
| item name | VARCHAR(512) | Yes | Generic name of the invoice item. Sourced from Fact_Collection.item_name. |
| item code | VARCHAR(256) | Yes | Generic code of the invoice item. Sourced from Fact_Collection.item_code. |
| businessunit | VARCHAR(65535) | Yes | Business unit of the invoice item. Sourced from Fact_Collection.businessunit. |
| is item inactive | BOOLEAN | Yes | Whether the item is inactive. Sourced from Fact_Collection.is_item_inactive. |
| item tags | VARCHAR(65535) | Yes | Tags associated with the item. Sourced from Fact_Collection.item_tags. |
| item type code | VARCHAR(100) | Yes | Type code of the item. Sourced from Fact_Collection.item_type_code. |
| invoice id | VARCHAR(36) | Yes | Unique identifier (GUID) of the invoice. Sourced from Fact_Collection.invoice_id. |
| invoice item id | VARCHAR(36) | Yes | Unique identifier (GUID) of the invoice item. Sourced from Fact_Collection.invoice_item_id. |
| invoice no | VARCHAR(40) | Yes | Invoice number. Sourced from Fact_Collection.invoice_no. |
| payment no | VARCHAR(80) | Yes | Payment number. Sourced from Fact_Collection.payment_no. |
| receipt no | VARCHAR(80) | Yes | Receipt number. Sourced from Fact_Collection.receipt_no. |
| invoice source | VARCHAR(128) | Yes | Source of the invoice. Sourced from Fact_Collection.invoice_source. |
| invoice status | VARCHAR(14) | Yes | Status of the invoice. Sourced from Fact_Collection.invoice_status. |
| amount | FLOAT8 | Yes | Collection amount. Sourced from Fact_Collection.amount. |
| adjusted amount | FLOAT8 | Yes | Adjusted collection amount. Sourced from Fact_Collection.adjusted_amount. |
| amount type | VARCHAR(100) | Yes | Type of the amount. Sourced from Fact_Collection.amount_type. |
| payment option type | VARCHAR(40) | Yes | Payment option type used. Sourced from Fact_Collection.payment_option_type. |
| payment type | VARCHAR(100) | Yes | Payment type used. Sourced from Fact_Collection.payment_type. |
| custom payment name | VARCHAR(400) | Yes | Name of the custom payment method. Sourced from Fact_Collection.custom_payment_name. |
| consider in financials | VARCHAR(5) | Yes | Whether to consider in financial calculations (Yes/No). Sourced from Fact_Collection.consider_in_financials. |
| invoice item created date | DATE | Yes | Date when the invoice item was created. Sourced from Fact_Collection.invoice_item_created_date. |
| invoice item created date and time | TIMESTAMP | Yes | Date and time when the invoice item was created. Sourced from Fact_Collection.invoice_item_created_date_and_time. |
| invoice closed date | DATE | Yes | Date when the invoice was closed. Sourced from Fact_Collection.invoice_closed_date. |
| payment date | DATE | Yes | Date of the payment. Sourced from Fact_Collection.payment_date. |
| payment date and time | TIMESTAMP | Yes | Date and time of the payment. Sourced from Fact_Collection.payment_date_and_time. |
| sale date | DATE | Yes | Date of the sale. Sourced from Fact_Collection.sale_date. |
| sale date and time | TIMESTAMP | Yes | Date and time of the sale. Sourced from Fact_Collection.sale_date_and_time. |
| custom payment additional details | VARCHAR(65535) | Yes | Additional details for custom payment. Sourced from Fact_Collection.custom_payment_additional_details. |
| factcollectionwid | BIGINT | Yes | Warehouse identifier for the collection fact record. Sourced from Fact_Collection.wid. |
| Membership Center Name | VARCHAR(200) | Yes | Center name where the membership was sold. Sourced from Fact_Collection.membership_center_name. |
| GiftCard Center Name | VARCHAR(200) | Yes | Center name where the gift card was sold. Sourced from Fact_Collection.giftcard_center_name. |
| Package Center Name | VARCHAR(200) | Yes | Center name where the package was sold. Sourced from Fact_Collection.package_center_name. |
| Base Center Name | VARCHAR(200) | Yes | Base center name. Sourced from Dim_Center.base_center_name. |
| Base Center Company Name | VARCHAR(200) | Yes | Company name of the base center. Sourced from Dim_Center.base_center_company_name. |
| Base Center Zone Name | VARCHAR(800) | Yes | Zone name of the base center. Sourced from Dim_Center.base_center_zone_name. |
| Gift Card Code | VARCHAR(800) | Yes | Gift card code used in the collection. Sourced from Fact_Collection.gift_card_code. |
| cctransactionfee | FLOAT | Yes | Credit card transaction fee. Sourced from Fact_Collection.cctransactionfee. |
| Amount By Payment | FLOAT | Yes | Amount attributed by payment method. Sourced from Fact_Collection.amount_by_payment. |
| transaction id | VARCHAR(36) | Yes | Transaction identifier. Sourced from Fact_Collection.transaction_id. |
| Invoice Closed Date and Time | TIMESTAMP | Yes | Date and time when the invoice was closed. Sourced from Fact_Collection.invoice_closed_date_and_time. |
| Is Redeption Instrument GC | BOOLEAN | Yes | Whether the redemption instrument is a gift card. Sourced from Fact_Collection. |
| Is Redemption Instrument Bonus Giftcard | BOOLEAN | Yes | Whether the redemption instrument is a bonus gift card. Sourced from Fact_Collection. |
| Redemption GC Promotion Name | NVARCHAR(2048) | Yes | Promotion name on the redemption gift card. Sourced from Fact_Collection. |
| Redemption GC Promotion Code | NVARCHAR(512) | Yes | Promotion code on the redemption gift card. Sourced from Fact_Collection. |
| Redemption GC Promotion Description | NVARCHAR(32000) | Yes | Promotion description on the redemption gift card. Sourced from Fact_Collection. |
| Redemption GC Prmotion Rule Criteria | NVARCHAR(2000) | Yes | Promotion rule criteria on the redemption gift card. Sourced from Fact_Collection. |
| Redemption GC Promotion Rule Minimum Value | DECIMAL(19,4) | Yes | Minimum value for redemption GC promotion rule. Sourced from Fact_Collection. |
| Redemption GC Promotion Rule Maximum Value | DECIMAL(19,4) | Yes | Maximum value for redemption GC promotion rule. Sourced from Fact_Collection. |
| Redemption GC Promotion Rule Bonus Value | DECIMAL(19,4) | Yes | Bonus value for redemption GC promotion rule. Sourced from Fact_Collection. |
| is center franchise | BOOLEAN | Yes | Whether the center is a franchise. Sourced from Dim_Center.is_franchise. |
| center cstn number | NVARCHAR(80) | Yes | CSTN number of the center. Sourced from Dim_Center.cstn_number. |
| center tin number | NVARCHAR(80) | Yes | TIN number of the center. Sourced from Dim_Center.tin_number. |
| center tot number | NVARCHAR(80) | Yes | TOT number of the center. Sourced from Dim_Center.tot_number. |
| center service tax number | NVARCHAR(80) | Yes | Service tax number of the center. Sourced from Dim_Center.service_tax_number. |
| center vat number | NVARCHAR(80) | Yes | VAT number of the center. Sourced from Dim_Center.vat_number. |
| center luxary tax number | NVARCHAR(80) | Yes | Luxury tax number of the center. Sourced from Dim_Center.luxary_tax_number. |
| employee additional field 1 | NVARCHAR(8000) | Yes | Custom additional field 1 for the employee. Sourced from Dim_Employee.additional_field_1. |
| employee additional field 2 | NVARCHAR(8000) | Yes | Custom additional field 2 for the employee. Sourced from Dim_Employee.additional_field_2. |
| closed by employee additional field 1 | NVARCHAR(8000) | Yes | Custom additional field 1 for the closing employee. Sourced from Dim_Employee.additional_field_1 (closed by). |
| closed by employee additional field 2 | NVARCHAR(8000) | Yes | Custom additional field 2 for the closing employee. Sourced from Dim_Employee.additional_field_2 (closed by). |
| credit card approval number | NVARCHAR(256) | Yes | Credit card approval number. Sourced from Fact_Collection.credit_card_approval_number. |
| credit card last four digits | NVARCHAR(32) | Yes | Last four digits of the credit card. Sourced from Fact_Collection.credit_card_last_four_digits. |
| Referred By First Name | VARCHAR(160) | Yes | First name of the referring guest. Sourced from Dim_User.first_name (referred by). |
| Referred By Last Name | VARCHAR(256) | Yes | Last name of the referring guest. Sourced from Dim_User.last_name (referred by). |
| Referred By User Code | VARCHAR(128) | Yes | User code of the referring guest. Sourced from Dim_User.user_code (referred by). |
| Relationship Manager First Name | VARCHAR(160) | Yes | First name of the relationship manager. Sourced from Dim_Employee.first_name (relationship manager). |
| Relationship Manager Last Name | VARCHAR(256) | Yes | Last name of the relationship manager. Sourced from Dim_Employee.last_name (relationship manager). |
| Relationship Manager User Code | VARCHAR(128) | Yes | User code of the relationship manager. Sourced from Dim_Employee.user_code (relationship manager). |
| Relationship Manager Employee - Code | VARCHAR(128) | Yes | Alternate employee code of the relationship manager. Sourced from Dim_Employee.employee_code_alt (relationship manager). |
| Dispute Amount | FLOAT | Yes | Amount of the dispute. Sourced from Fact_Collection.dispute_amount. |
| Dispute Status | VARCHAR(512) | Yes | Status of the dispute. Sourced from Fact_Collection.dispute_status. |
| Dispute Reason | VARCHAR(1028) | Yes | Reason for the dispute. Sourced from Fact_Collection.dispute_reason. |
| Dispute Date Time | TIMESTAMP | Yes | Date and time of the dispute. Sourced from Fact_Collection.dispute_date_time. |
| Dispute Date | DATE | Yes | Date of the dispute. Sourced from Fact_Collection.dispute_date. |
| Dispute Charge Back Amount | FLOAT | Yes | Chargeback amount from the dispute. Sourced from Fact_Collection.dispute_charge_back_amount. |
| Dispute Fee | FLOAT | Yes | Fee associated with the dispute. Sourced from Fact_Collection.dispute_fee. |
| Dispute Respond By Date Time | TIMESTAMP | Yes | Deadline to respond to the dispute. Sourced from Fact_Collection.dispute_respond_by_date_time. |
| Dispute Respond By Date | DATE | Yes | Deadline date to respond to the dispute. Sourced from Fact_Collection.dispute_respond_by_date. |
| closed by employee Last Date | TIMESTAMP | Yes | Last working date of the closing employee. Sourced from Dim_Employee.last_date (closed by). |
| employee last date | TIMESTAMP | Yes | Last working date of the employee. Sourced from Dim_Employee.last_date. |
| Minutes To ADD | INTEGER | Yes | Minutes offset to add for timezone conversion. Sourced from Dim_Center.minutes_to_add. |
| Center Code | VARCHAR(40) | Yes | Code of the center. Sourced from Dim_Center.center_code. |
| Payment Mode Number | VARCHAR(156) | Yes | Payment mode number. Sourced from Fact_Collection.payment_mode_number. |
| cc transaction id | VARCHAR(40) | Yes | Credit card transaction identifier. Sourced from Fact_Collection.cc_transaction_id. |
| factcollectionid | INTEGER | Yes | Internal integer identifier for the collection fact. Sourced from Fact_Collection.id. |
| Refby userwid | BIGINT | Yes | Warehouse identifier for the referring user. Sourced from Dim_User.wid (referred by). |
| Relmgr userwid | BIGINT | Yes | Warehouse identifier for the relationship manager. Sourced from Dim_Employee.wid (relationship manager). |
| WorkShop Name | VARCHAR(128) | Yes | Name of the workshop. Sourced from Fact_Collection.workshop_name. |
| Track Name | VARCHAR(128) | Yes | Name of the track. Sourced from Fact_Collection.track_name. |
| WorkShop Code | VARCHAR(32) | Yes | Code of the workshop. Sourced from Fact_Collection.workshop_code. |
| Track Code | VARCHAR(32) | Yes | Code of the track. Sourced from Fact_Collection.track_code. |
| Session Name | VARCHAR(256) | Yes | Name of the class session. Sourced from Fact_Collection.session_name. |
| Class Name | VARCHAR(128) | Yes | Name of the class. Sourced from Fact_Collection.class_name. |
| Class Code | VARCHAR(32) | Yes | Code of the class. Sourced from Fact_Collection.class_code. |
| Session Code | VARCHAR(32) | Yes | Code of the session. Sourced from Fact_Collection.session_code. |
| brand name | VARCHAR(512) | Yes | Brand name associated with the item. Sourced from Fact_Collection.brand_name. |
| Deposit Date | TIMESTAMP | Yes | Date of the deposit. Sourced from Fact_Collection.deposit_date. |
| Deposit Amount | FLOAT | Yes | Amount of the deposit. Sourced from Fact_Collection.deposit_amount. |
| Actual Deposit Date | TIMESTAMP | Yes | Actual date of the deposit. Sourced from Fact_Collection.actual_deposit_date. |
| Actual Deposit Amount | FLOAT | Yes | Actual amount deposited. Sourced from Fact_Collection.actual_deposit_amount. |
| Dispute Settled Date | TIMESTAMP | Yes | Date when the dispute was settled. Sourced from Fact_Collection.dispute_settled_date. |
| CC Processor | NVARCHAR(50) | Yes | Credit card processor name. Sourced from Fact_Collection.cc_processor. |
| Card Surcharge Fee | FLOAT | Yes | Surcharge fee on card payment. Sourced from Fact_Collection.card_surcharge_fee. |
| Apportioned CC Fee | FLOAT | Yes | Apportioned credit card fee. Sourced from Fact_Collection.apportioned_cc_fee. |
| Zenoti Merchant Id | VARCHAR(128) | Yes | Zenoti merchant identifier. Sourced from Fact_Collection.zenoti_merchant_id. |
| Is AVS Downgrade Fee Applied | BOOLEAN | Yes | Whether AVS downgrade fee was applied. Sourced from Fact_Collection.is_avs_downgrade_fee_applied. |
| cc transaction status | NVARCHAR(128) | Yes | Status of the credit card transaction. Sourced from Fact_Collection.cc_transaction_status. |
| utm source | NVARCHAR(128) | Yes | UTM source tracking parameter. Sourced from Fact_Collection.utm_source. |
| utm medium | NVARCHAR(128) | Yes | UTM medium tracking parameter. Sourced from Fact_Collection.utm_medium. |
| bundle package name | VARCHAR(1024) | Yes | Name of the bundle package. Sourced from Fact_Collection.bundle_package_name. |
| bundle package number | INTEGER | Yes | Number of the bundle package. Sourced from Fact_Collection.bundle_package_number. |
| Transactional Refund Date | TIMESTAMP | Yes | Date of the transactional refund. Sourced from Fact_Collection.transactional_refund_date. |
| Transactional Refund Amount | FLOAT | Yes | Amount of the transactional refund. Sourced from Fact_Collection.transactional_refund_amount. |
| Reversal Date | TIMESTAMP | Yes | Date of the payment reversal. Sourced from Fact_Collection.reversal_date. |
| Tax On Card Surcharge | FLOAT | Yes | Tax on the card surcharge fee. Sourced from Fact_Collection.tax_on_card_surcharge. |
| Redeemed Tax Amount | FLOAT | Yes | Tax amount that was redeemed. Sourced from Fact_Collection.redeemed_tax_amount. |
Primary Key
No explicit primary key defined. The factcollectionwid serves as the logical warehouse identifier.
Foreign Keys
factcollectionwid- Warehouse identifier for the collection fact recordfactcollectionid- Internal identifier for the collection factcenter wid- References Dim_Center.widuser wid- References Dim_User.widinvoice id- References the invoice recordinvoice item id- References the invoice item recordRefby userwid- References Dim_User.wid (referring user)Relmgr userwid- References Dim_Employee.wid (relationship manager)transaction id- References the payment transactioncc transaction id- References the credit card transaction
Enumerator Values
| Column Name | Known Values |
|---|---|
| item | -1 = None 0 = Service 2 = Product 3 = Membership 4 = Series Package 5 = Day/Promo Package 6 = Gift Card 8 = Employee 11 = Class 14 = Add-ons -90 = Service Refund -92 = Product Refund -93 = Membership Refund -94 = Series Package Refund -95 = Day Package Refund -96 = Gift Card Refund -911 = Class Refund |
| invoice status | 0 = Open |
| invoice source | 0 = Store |
| amount type | 1 = ToBeCollectedAmount |
| payment option type | Cash |
| payment type | 1 = Cash |
| consider in financials | 0 = No |
| Dispute Status | Pending |
| cc transaction status | Approved |
Standard ETL metadata columns (EtlLastUpdatedOn, EtlLastUpdatedBy, podname, groupid) are present in the underlying table but omitted from this reference for brevity.
Was this article helpful?
Your feedback helps us improve our documentation.