ZenotiHelp

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 record
  • factcollectionid - Internal identifier for the collection fact
  • center wid - References Dim_Center.wid
  • user wid - References Dim_User.wid
  • invoice id - References the invoice record
  • invoice item id - References the invoice item record
  • Refby userwid - References Dim_User.wid (referring user)
  • Relmgr userwid - References Dim_Employee.wid (relationship manager)
  • transaction id - References the payment transaction
  • cc 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.