ZenotiHelp

Package User Fact Consolidated

ZTZenoti Docs Team Updated April 10, 2026 9 min readGuide

Consolidated fact table capturing package-level user transactions including package sales, payments, redemptions, and associated guest/employee/service/product/class details.

Columns

Column Name Data Type Nullable Description
organization name VARCHAR(512) Yes Name of the organization. Sourced from DimOrganization.organization_name.
organization account name VARCHAR(256) Yes Account name of the organization. Sourced from DimOrganization.organization_account_name.
Package Sale center name VARCHAR(128) Yes Name of the center where the package was sold. Sourced from DimCenter.center_name.
Package Sale center Zone name VARCHAR(512) Yes Zone name of the package sale center. Sourced from DimCenter.zone_name.
Package Sale center company name VARCHAR(128) Yes Company name of the package sale center. Sourced from DimCenter.company_name.
Payment Center Name VARCHAR(128) Yes Name of the center where payment was made. Sourced from DimCenter.center_name.
Payment Center Zone Name VARCHAR(512) Yes Zone name of the payment center. Sourced from DimCenter.zone_name.
Item Sale Center Name VARCHAR(128) Yes Name of the center where the item was sold. Sourced from DimCenter.center_name.
Item Sale Center Zone Name VARCHAR(512) Yes Zone name of the item sale center. Sourced from DimCenter.zone_name.
Package Redemption Center VARCHAR(4096) Yes Name of the center where the package was redeemed. Sourced from DimCenter.center_name.
Package Redemption Center Zone VARCHAR(4096) Yes Zone name of the package redemption center. Sourced from DimCenter.zone_name.
user id VARCHAR(36) Yes Unique identifier (UUID) of the guest. Sourced from DimGuest.user_id.
guest first name VARCHAR(160) Yes First name of the guest. Sourced from DimGuest.first_name.
guest last name VARCHAR(256) Yes Last name of the guest. Sourced from DimGuest.last_name.
guest middle name VARCHAR(128) Yes Middle name of the guest. Sourced from DimGuest.middle_name.
guest Address 1 NVARCHAR(512) Yes Primary address line of the guest. Sourced from DimGuest.address1.
guest Address 2 NVARCHAR(512) Yes Secondary address line of the guest. Sourced from DimGuest.address2.
guest city VARCHAR(128) Yes City of the guest. Sourced from DimGuest.city.
guest zipcode VARCHAR(128) Yes Zip code of the guest. Sourced from DimGuest.zipcode.
guest type name VARCHAR(128) Yes Guest type classification name. Sourced from DimGuest.type_name.
guest email VARCHAR(256) Yes Email address of the guest. Sourced from DimGuest.email.
guest mobile phone VARCHAR(64) Yes Mobile phone number of the guest. Sourced from DimGuest.mobile_phone.
guest home phone VARCHAR(64) Yes Home phone number of the guest. Sourced from DimGuest.home_phone.
guest phone code INTEGER Yes Phone country code of the guest. Sourced from DimGuest.phone_code.
guest code VARCHAR(128) Yes Unique guest code identifier. Sourced from DimGuest.guest_code.
guest gender VARCHAR(14) Yes Gender of the guest. Sourced from DimGuest.gender.
guest state VARCHAR(4096) Yes State of the guest. Sourced from DimGuest.state.
guest base center VARCHAR(256) Yes Base center assigned to the guest. Sourced from DimGuest.base_center.
guest country name VARCHAR(4096) Yes Country name of the guest. Sourced from DimGuest.country_name.
is guest inactive BOOLEAN Yes Indicates whether the guest is inactive. Sourced from DimGuest.is_inactive.
guest tags VARCHAR(65535) Yes Tags associated with the guest. Sourced from DimGuest.tags.
employee first name VARCHAR(160) Yes First name of the employee (therapist). Sourced from DimEmployee.first_name.
employee last name VARCHAR(256) Yes Last name of the employee (therapist). Sourced from DimEmployee.last_name.
employee middle name VARCHAR(128) Yes Middle name of the employee. Sourced from DimEmployee.middle_name.
employee mobile phone VARCHAR(64) Yes Mobile phone number of the employee. Sourced from DimEmployee.mobile_phone.
employee code VARCHAR(128) Yes Code of the employee. Sourced from DimEmployee.employee_code.
employee - code VARCHAR(128) Yes Alternate employee code field. Sourced from DimEmployee.employee_code.
employee gender VARCHAR(14) Yes Gender of the employee. Sourced from DimEmployee.gender.
employee job name VARCHAR(1020) Yes Job title of the employee. Sourced from DimEmployee.job_name.
employee job code VARCHAR(1020) Yes Job code of the employee. Sourced from DimEmployee.job_code.
is employee inactive BOOLEAN Yes Indicates whether the employee is inactive. Sourced from DimEmployee.is_inactive.
service name VARCHAR(512) Yes Name of the service. Sourced from DimService.service_name.
service length INTEGER Yes Duration of the service in minutes. Sourced from DimService.service_length.
service category VARCHAR(256) Yes Category of the service. Sourced from DimService.category.
service parent category VARCHAR(256) Yes Parent category of the service. Sourced from DimService.parent_category.
service has variant VARCHAR(14) Yes Indicates whether the service has variants. Sourced from DimService.has_variant.
service is variant VARCHAR(14) Yes Indicates whether the service is a variant. Sourced from DimService.is_variant.
service businessunit VARCHAR(200) Yes Business unit of the service. Sourced from DimService.businessunit.
is service inactive BOOLEAN Yes Indicates whether the service is inactive. Sourced from DimService.is_inactive.
service tags VARCHAR(65535) Yes Tags associated with the service. Sourced from DimService.tags.
package benefit service tag VARCHAR(510) Yes Tag identifying the service benefit within the package.
product name VARCHAR(256) Yes Name of the product. Sourced from DimProduct.product_name.
product category VARCHAR(256) Yes Category of the product. Sourced from DimProduct.category.
product parent category VARCHAR(256) Yes Parent category of the product. Sourced from DimProduct.parent_category.
product business unit VARCHAR(200) Yes Business unit of the product. Sourced from DimProduct.business_unit.
product has variant BOOLEAN Yes Indicates whether the product has variants. Sourced from DimProduct.has_variant.
is product inactive BOOLEAN Yes Indicates whether the product is inactive. Sourced from DimProduct.is_inactive.
product tags VARCHAR(65535) Yes Tags associated with the product. Sourced from DimProduct.tags.
package benefit product tag VARCHAR(510) Yes Tag identifying the product benefit within the package.
class name VARCHAR(512) Yes Name of the class. Sourced from DimClass.class_name.
is workshop BOOLEAN Yes Indicates whether the class is a workshop. Sourced from DimClass.is_workshop.
class length INTEGER Yes Duration of the class in minutes. Sourced from DimClass.class_length.
class category VARCHAR(256) Yes Category of the class. Sourced from DimClass.category.
class parent category VARCHAR(256) Yes Parent category of the class. Sourced from DimClass.parent_category.
class businessunit VARCHAR(200) Yes Business unit of the class. Sourced from DimClass.businessunit.
is class inactive BOOLEAN Yes Indicates whether the class is inactive. Sourced from DimClass.is_inactive.
class tags VARCHAR(65535) Yes Tags associated with the class. Sourced from DimClass.tags.
package benefit class tag VARCHAR(510) Yes Tag identifying the class benefit within the package.
package name VARCHAR(512) Yes Name of the package. Sourced from DimPackage.package_name.
package type VARCHAR(14) Yes Type of the package (e.g., Membership, Series). Sourced from DimPackage.package_type.
package category VARCHAR(200) Yes Category of the package. Sourced from DimPackage.category.
package businessunit VARCHAR(200) Yes Business unit of the package. Sourced from DimPackage.businessunit.
is package inactive BOOLEAN Yes Indicates whether the package is inactive. Sourced from DimPackage.is_inactive.
package tags VARCHAR(65535) Yes Tags associated with the package. Sourced from DimPackage.tags.
package id VARCHAR(36) Yes Unique identifier (UUID) of the package.
package version id VARCHAR(36) Yes Unique identifier (UUID) of the package version.
package code NVARCHAR(128) Yes Code assigned to the package.
item type code VARCHAR(100) Yes Code representing the type of item in the package benefit.
Unit item type code INTEGER Yes Numeric code for the unit item type.
package start date TIMESTAMP Yes Start date and time of the package.
package end date TIMESTAMP Yes End date and time of the package.
template package versionid VARCHAR(70) Yes Version ID of the template package.
quantity INTEGER Yes Total quantity of the package benefit.
quantity redeemed INTEGER Yes Number of units redeemed from the package benefit.
payment option type VARCHAR(250) Yes Type of payment option used.
transactionid VARCHAR(36) Yes Unique identifier (UUID) of the payment transaction.
payment type VARCHAR(100) Yes Type of payment (e.g., Cash, Credit Card).
amount paid for package FLOAT Yes Total amount paid for the package.
package final sale price FLOAT Yes Final sale price of the package after discounts.
package sale price FLOAT Yes Original sale price of the package.
package item price FLOAT Yes Price of the item within the package.
sale price of item FLOAT Yes Sale price of the individual item.
final sale price of item FLOAT Yes Final sale price of the individual item after discounts.
prorated amount paid for item FLOAT Yes Prorated amount paid attributed to the item.
package sale sate And time TIMESTAMP Yes Date and time of the package sale.
package sale date DATE Yes Date of the package sale.
payment date DATE Yes Date of the payment.
payment date and time TIMESTAMP Yes Date and time of the payment.
package invoice no VARCHAR(80) Yes Invoice number for the package.
package invoice id VARCHAR(36) Yes Unique identifier (UUID) of the package invoice.
package invoice item id VARCHAR(36) Yes Unique identifier (UUID) of the package invoice item.
invoice status VARCHAR(14) Yes Status of the invoice (e.g., Closed, Open).
min redemption date DATE Yes Earliest redemption date for the package benefit.
max redemption date DATE Yes Latest redemption date for the package benefit.
business unit VARCHAR(200) Yes Business unit associated with the record.
redemption date TIMESTAMP Yes Date and time when the package benefit was redeemed.
record type VARCHAR(20) Yes Type of record (e.g., Sale, Redemption, Refund).
redemption amount FLOAT Yes Amount attributed to the redemption.
Package item discount FLOAT Yes Discount applied to the package item.
unit item discount FLOAT Yes Discount applied per unit item.
package sale by employee first name VARCHAR(160) Yes First name of the employee who sold the package. Sourced from DimEmployee.first_name.
package sale by employee last name VARCHAR(256) Yes Last name of the employee who sold the package. Sourced from DimEmployee.last_name.
package sale by employee code VARCHAR(128) Yes Code of the employee who sold the package. Sourced from DimEmployee.employee_code.
package sale by employee - code VARCHAR(128) Yes Alternate code of the employee who sold the package. Sourced from DimEmployee.employee_code.
package sale by employee job name VARCHAR(1020) Yes Job name of the employee who sold the package. Sourced from DimEmployee.job_name.
package sale by employee job code VARCHAR(1020) Yes Job code of the employee who sold the package. Sourced from DimEmployee.job_code.
redemption invoice item id VARCHAR(36) Yes Invoice item ID associated with the redemption.
refund invoice item id VARCHAR(36) Yes Invoice item ID associated with a refund.
resource name VARCHAR(1020) Yes Name of the resource (e.g., room, equipment). Sourced from DimResource.resource_name.
resource code VARCHAR(1020) Yes Code of the resource. Sourced from DimResource.resource_code.
Item List Price FLOAT Yes List price of the item before discounts.
Approval Number VARCHAR(256) Yes Payment approval/authorization number.
Credit Card Last Four Digits VARCHAR(128) Yes Last four digits of the credit card used.
is center franchise BOOLEAN Yes Indicates whether the center is a franchise. Sourced from DimCenter.is_franchise.
center cstn number VARCHAR(80) Yes CSTN number of the center. Sourced from DimCenter.cstn_number.
center tin number VARCHAR(80) Yes TIN number of the center. Sourced from DimCenter.tin_number.
center tot number VARCHAR(80) Yes TOT number of the center. Sourced from DimCenter.tot_number.
center service tax number VARCHAR(80) Yes Service tax number of the center. Sourced from DimCenter.service_tax_number.
center vat number VARCHAR(80) Yes VAT number of the center. Sourced from DimCenter.vat_number.
center luxary tax number VARCHAR(80) Yes Luxury tax number of the center. Sourced from DimCenter.luxary_tax_number.
employee additional field 1 VARCHAR(8000) Yes Custom additional field 1 for the employee. Sourced from DimEmployee.additional_field_1.
employee additional field 2 VARCHAR(8000) Yes Custom additional field 2 for the employee. Sourced from DimEmployee.additional_field_2.
Package sale by employee additional field 1 VARCHAR(8000) Yes Custom additional field 1 for the selling employee. Sourced from DimEmployee.additional_field_1.
Package sale by employee additional field 2 VARCHAR(8000) Yes Custom additional field 2 for the selling employee. Sourced from DimEmployee.additional_field_2.
Is Custom Package BOOLEAN Yes Indicates whether the package is a custom package.
Minutes To ADD INTEGER Yes Timezone offset in minutes for the center.
Unit Item Listed Discount VARCHAR(64) Yes Listed discount for the unit item.
factsegmentwid BIGINT Yes Fact segment warehouse identifier for ETL partitioning.
brand name VARCHAR(512) Yes Brand name associated with the record.
Package Sale Center Code VARCHAR(40) Yes Code of the package sale center. Sourced from DimCenter.center_code.
Payment Center Code VARCHAR(40) Yes Code of the payment center. Sourced from DimCenter.center_code.
Item Sale Center Code VARCHAR(40) Yes Code of the item sale center. Sourced from DimCenter.center_code.
Package Redemption Center Code VARCHAR(40) Yes Code of the package redemption center. Sourced from DimCenter.center_code.
benefit refund quantity INTEGER Yes Quantity of benefits refunded (added via ALTER).

Primary Key

No explicit primary key is defined. The combination of package invoice item id, record type, and redemption invoice item id can serve as a logical key.

Foreign Keys

  • user id references DimGuest
  • Package Sale center name / Package Sale Center Code references DimCenter (sale center)
  • Payment Center Name / Payment Center Code references DimCenter (payment center)
  • Item Sale Center Name / Item Sale Center Code references DimCenter (item sale center)
  • Package Redemption Center / Package Redemption Center Code references DimCenter (redemption center)
  • employee first name, employee code references DimEmployee (therapist)
  • package sale by employee first name, package sale by employee code references DimEmployee (selling employee)
  • service name references DimService
  • product name references DimProduct
  • class name references DimClass
  • package name, package id references DimPackage
  • resource name, resource code references DimResource

Enumerator Values

Column Name Known Values
package type Membership, Series, etc.
invoice status Closed, Open, etc.
record type Sale, Redemption, Refund
guest gender Male, Female, etc.
employee gender Male, Female, etc.
item type code Service, Product, Class

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.