ZenotiHelp

Sales Fact Consolidated

ZTZenoti Docs Team Updated April 10, 2026 25 min readGuide

Consolidated sales fact table combining invoice item details with guest, employee, service, product, membership, package, gift card, campaign, and resource dimensions for comprehensive sales analytics.

Columns

Column Name Data Type Nullable Description
fact invoiceitem wid BIGINT Yes Warehouse identifier for the invoice item fact record. Sourced from Fact_InvoiceItem.wid.
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 sale 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.
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 address 1 VARCHAR(2048) Yes Primary address line of the guest. Sourced from Dim_User.address_1.
guest address 2 VARCHAR(2048) Yes Secondary address line of the guest. Sourced from Dim_User.address_2.
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 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 code VARCHAR(128) Yes Unique code of the guest. Sourced from Dim_User.guest_code.
guest referral source VARCHAR(1600) Yes Referral source of the guest. Sourced from Dim_User.referral_source.
guest email state INTEGER Yes Email subscription state of the guest. Sourced from Dim_User.email_state.
guest receive transactional email BOOLEAN Yes Whether the guest opted to receive transactional emails. Sourced from Dim_User.receive_transactional_email.
guest receive marketing email BOOLEAN Yes Whether the guest opted to receive marketing emails. Sourced from Dim_User.receive_marketing_email.
guest receive transactional sms BOOLEAN Yes Whether the guest opted to receive transactional SMS. Sourced from Dim_User.receive_transactional_sms.
guest receive marketing sms BOOLEAN Yes Whether the guest opted to receive marketing SMS. Sourced from Dim_User.receive_marketing_sms.
No Sale Comments VARCHAR(MAX) Yes Comments on no-sale transactions. Sourced from Fact_InvoiceItem.no_sale_comments.
employee first name VARCHAR(160) Yes First name of the selling employee. Sourced from Dim_Employee.first_name.
employee last name VARCHAR(256) Yes Last name of the selling employee. Sourced from Dim_Employee.last_name.
employee middle name VARCHAR(128) Yes Middle name of the selling employee. Sourced from Dim_Employee.middle_name.
employee mobile phone VARCHAR(64) Yes Mobile phone of the selling employee. Sourced from Dim_Employee.mobile_phone.
employee code VARCHAR(128) Yes Code of the selling employee. Sourced from Dim_Employee.employee_code.
employee - code VARCHAR(128) Yes Alternate code of the selling employee. Sourced from Dim_Employee.employee_code_alt.
employee gender VARCHAR(14) Yes Gender of the selling employee. Sourced from Dim_Employee.gender.
employee job name VARCHAR(1020) Yes Job title of the selling employee. Sourced from Dim_Employee.job_name.
employee job code VARCHAR(8000) Yes Job code of the selling employee. Sourced from Dim_Employee.job_code.
is employee inactive BOOLEAN Yes Whether the selling employee is inactive. Sourced from Dim_Employee.is_inactive.
employee tags VARCHAR(65535) Yes Tags associated with the selling employee. Sourced from Dim_Employee.tags.
employee start date TIMESTAMP Yes Start date of the selling employee. Sourced from Dim_Employee.start_date.
employee last date TIMESTAMP Yes Last working date of the selling employee. Sourced from Dim_Employee.last_date.
close by employee first name VARCHAR(160) Yes First name of the employee who closed the invoice. Sourced from Dim_Employee.first_name (closed by).
close by employee last name VARCHAR(256) Yes Last name of the employee who closed the invoice. Sourced from Dim_Employee.last_name (closed by).
close by employee middle name VARCHAR(128) Yes Middle name of the employee who closed the invoice. Sourced from Dim_Employee.middle_name (closed by).
close by employee mobile phone VARCHAR(64) Yes Mobile phone of the employee who closed the invoice. Sourced from Dim_Employee.mobile_phone (closed by).
close by employee code VARCHAR(128) Yes Code of the employee who closed the invoice. Sourced from Dim_Employee.employee_code (closed by).
close by employee - code VARCHAR(128) Yes Alternate code of the employee who closed the invoice. Sourced from Dim_Employee.employee_code_alt (closed by).
close by employee gender VARCHAR(14) Yes Gender of the employee who closed the invoice. Sourced from Dim_Employee.gender (closed by).
close by employee job name VARCHAR(1020) Yes Job title of the employee who closed the invoice. Sourced from Dim_Employee.job_name (closed by).
close by employee job code VARCHAR(8000) Yes Job code of the employee who closed the invoice. Sourced from Dim_Employee.job_code (closed by).
is close by employee inactive BOOLEAN Yes Whether the closing employee is inactive. Sourced from Dim_Employee.is_inactive (closed by).
close by employee tags VARCHAR(65535) Yes Tags for the closing employee. Sourced from Dim_Employee.tags (closed by).
createdby employee first name VARCHAR(160) Yes First name of the employee who created the invoice. Sourced from Dim_Employee.first_name (created by).
createdby employee last name VARCHAR(256) Yes Last name of the employee who created the invoice. Sourced from Dim_Employee.last_name (created by).
createdby employee middle name VARCHAR(128) Yes Middle name of the employee who created the invoice. Sourced from Dim_Employee.middle_name (created by).
createdby employee mobile phone VARCHAR(64) Yes Mobile phone of the employee who created the invoice. Sourced from Dim_Employee.mobile_phone (created by).
createdby employee code VARCHAR(128) Yes Code of the employee who created the invoice. Sourced from Dim_Employee.employee_code (created by).
createdby employee - code VARCHAR(128) Yes Alternate code of the employee who created the invoice. Sourced from Dim_Employee.employee_code_alt (created by).
createdby employee gender VARCHAR(14) Yes Gender of the employee who created the invoice. Sourced from Dim_Employee.gender (created by).
createdby employee job name VARCHAR(1020) Yes Job title of the employee who created the invoice. Sourced from Dim_Employee.job_name (created by).
createdby employee job code VARCHAR(8000) Yes Job code of the employee who created the invoice. Sourced from Dim_Employee.job_code (created by).
is createdby employee inactive BOOLEAN Yes Whether the creating employee is inactive. Sourced from Dim_Employee.is_inactive (created by).
createdby employee tags VARCHAR(65535) Yes Tags for the creating employee. Sourced from Dim_Employee.tags (created 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.
service internal cost NUMERIC(19,4) Yes Internal cost of the service. Sourced from Dim_Service.internal_cost.
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.
product volume per item FLOAT8 Yes Volume per item of the product. Sourced from Dim_Product.volume_per_item.
product volume type name VARCHAR(36) Yes Volume type name of the product. Sourced from Dim_Product.volume_type_name.
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 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.
template package versionid VARCHAR(70) Yes Version identifier of the package template. Sourced from Dim_Package.template_package_versionid.
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 applied to the gift card. 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_InvoiceItem.item_name.
item code VARCHAR(256) Yes Generic code of the invoice item. Sourced from Fact_InvoiceItem.item_code.
businessunit VARCHAR(65535) Yes Business unit of the invoice item. Sourced from Fact_InvoiceItem.businessunit.
is item inactive BOOLEAN Yes Whether the item is inactive. Sourced from Fact_InvoiceItem.is_item_inactive.
item tags VARCHAR(65535) Yes Tags associated with the item. Sourced from Fact_InvoiceItem.item_tags.
item type code VARCHAR(100) Yes Type code of the item (e.g., Service, Product, Membership, Package, GiftCard). Sourced from Fact_InvoiceItem.item_type_code.
invoice id VARCHAR(36) Yes Unique identifier (GUID) of the invoice. Sourced from Fact_InvoiceItem.invoice_id.
invoice item id VARCHAR(36) Yes Unique identifier (GUID) of the invoice item. Sourced from Fact_InvoiceItem.invoice_item_id.
invoice no VARCHAR(40) Yes Invoice number. Sourced from Fact_InvoiceItem.invoice_no.
quantity sold INTEGER Yes Quantity of items sold. Sourced from Fact_InvoiceItem.quantity_sold.
invoice source VARCHAR(128) Yes Source of the invoice (e.g., Store, Online, App). Sourced from Fact_InvoiceItem.invoice_source.
sale price FLOAT8 Yes Sale price of the item. Sourced from Fact_InvoiceItem.sale_price.
discount applied FLOAT8 Yes Discount amount applied to the item. Sourced from Fact_InvoiceItem.discount_applied.
tax FLOAT8 Yes Tax amount on the item. Sourced from Fact_InvoiceItem.tax.
list price FLOAT8 Yes List/original price of the item. Sourced from Fact_InvoiceItem.list_price.
loyalty point redemption amount FLOAT8 Yes Amount redeemed via loyalty points. Sourced from Fact_InvoiceItem.loyalty_point_redemption_amount.
membership redemption amount FLOAT8 Yes Amount redeemed via membership. Sourced from Fact_InvoiceItem.membership_redemption_amount.
gift card redemption amount FLOAT8 Yes Amount redeemed via gift card. Sourced from Fact_InvoiceItem.gift_card_redemption_amount.
cashback redemption amount FLOAT8 Yes Amount redeemed via cashback. Sourced from Fact_InvoiceItem.cashback_redemption_amount.
campaign name VARCHAR(512) Yes Name of the campaign applied. Sourced from Dim_Campaign.campaign_name.
campaign code VARCHAR(4096) Yes Code of the campaign. Sourced from Dim_Campaign.campaign_code.
campaign type VARCHAR(11) Yes Type of the campaign. Sourced from Dim_Campaign.campaign_type.
is campaign active BOOLEAN Yes Whether the campaign is active. Sourced from Dim_Campaign.is_active.
campaign auto apply BOOLEAN Yes Whether the campaign is auto-applied. Sourced from Dim_Campaign.auto_apply.
campaign combine offers BOOLEAN Yes Whether the campaign allows combining offers. Sourced from Dim_Campaign.combine_offers.
Free Service Revenue Eligible BOOLEAN Yes Whether the item is eligible for free service revenue. Sourced from Fact_InvoiceItem.free_service_revenue_eligible.
discount code VARCHAR(32) Yes Discount code applied. Sourced from Fact_InvoiceItem.discount_code.
discount name VARCHAR(256) Yes Name of the discount applied. Sourced from Fact_InvoiceItem.discount_name.
custom discount id VARCHAR(36) Yes Custom discount identifier. Sourced from Fact_InvoiceItem.custom_discount_id.
custom discount type VARCHAR(512) Yes Type of custom discount. Sourced from Fact_InvoiceItem.custom_discount_type.
sale date and time TIMESTAMP Yes Date and time of the sale. Sourced from Fact_InvoiceItem.sale_date_and_time.
sale date DATE Yes Date of the sale. Sourced from Fact_InvoiceItem.sale_date.
non-financial custom payment discount FLOAT8 Yes Non-financial custom payment discount amount. Sourced from Fact_InvoiceItem.non_financial_custom_payment_discount.
is recurring invoice VARCHAR(100) Yes Whether the invoice is a recurring invoice. Sourced from Fact_InvoiceItem.is_recurring_invoice.
membership sold in invoice VARCHAR(5) Yes Whether a membership was sold in the invoice (Yes/No). Sourced from Fact_InvoiceItem.membership_sold_in_invoice.
guest member status VARCHAR(100) Yes Membership status of the guest at time of sale. Sourced from Fact_InvoiceItem.guest_member_status.
appointment cancel or no show status VARCHAR(21) Yes Cancellation or no-show status of the related appointment. Sourced from Fact_InvoiceItem.appointment_cancel_or_no_show_status.
invoice status VARCHAR(14) Yes Status of the invoice (e.g., Open, Closed). Sourced from Fact_InvoiceItem.invoice_status.
is membership discounted VARCHAR(100) Yes Whether a membership discount was applied. Sourced from Fact_InvoiceItem.is_membership_discounted.
is upgrade membership VARCHAR(100) Yes Whether the sale involves a membership upgrade. Sourced from Fact_InvoiceItem.is_upgrade_membership.
is downgrade membership VARCHAR(100) Yes Whether the sale involves a membership downgrade. Sourced from Fact_InvoiceItem.is_downgrade_membership.
guest purchased service before VARCHAR(5) Yes Whether the guest has previously purchased a service (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased product before VARCHAR(5) Yes Whether the guest has previously purchased a product (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased membership before VARCHAR(5) Yes Whether the guest has previously purchased a membership (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased package before VARCHAR(5) Yes Whether the guest has previously purchased a package (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased day package before VARCHAR(5) Yes Whether the guest has previously purchased a day package (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased prepaid/gift card before VARCHAR(5) Yes Whether the guest has previously purchased a prepaid/gift card (Yes/No). Sourced from Fact_InvoiceItem.
guest paid room charges before VARCHAR(5) Yes Whether the guest has previously paid room charges (Yes/No). Sourced from Fact_InvoiceItem.
guest purchased class before VARCHAR(5) Yes Whether the guest has previously purchased a class (Yes/No). Sourced from Fact_InvoiceItem.
guest paid membership freeze fee before VARCHAR(5) Yes Whether the guest has previously paid a membership freeze fee (Yes/No). Sourced from Fact_InvoiceItem.
invoice item created date DATE Yes Date when the invoice item was created. Sourced from Fact_InvoiceItem.invoice_item_created_date.
invoice item created date and time TIMESTAMP Yes Date and time when the invoice item was created. Sourced from Fact_InvoiceItem.invoice_item_created_date_and_time.
campaign applied date and time TIMESTAMP Yes Date and time when the campaign was applied. Sourced from Fact_InvoiceItem.campaign_applied_date_and_time.
campaign applied date DATE Yes Date when the campaign was applied. Sourced from Fact_InvoiceItem.campaign_applied_date.
invoice closed date and time TIMESTAMP Yes Date and time when the invoice was closed. Sourced from Fact_InvoiceItem.invoice_closed_date_and_time.
invoice closed date DATE Yes Date when the invoice was closed. Sourced from Fact_InvoiceItem.invoice_closed_date.
invoice includes service VARCHAR(5) Yes Whether the invoice includes a service item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes product VARCHAR(5) Yes Whether the invoice includes a product item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes membership VARCHAR(5) Yes Whether the invoice includes a membership item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes package VARCHAR(5) Yes Whether the invoice includes a package item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes day package VARCHAR(5) Yes Whether the invoice includes a day package item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes prepaid/gift card VARCHAR(5) Yes Whether the invoice includes a prepaid/gift card item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes room charges VARCHAR(5) Yes Whether the invoice includes room charges (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes class VARCHAR(5) Yes Whether the invoice includes a class item (Yes/No). Sourced from Fact_InvoiceItem.
invoice includes membership freeze fee VARCHAR(5) Yes Whether the invoice includes a membership freeze fee (Yes/No). Sourced from Fact_InvoiceItem.
invoice item last updated date and time TIMESTAMP Yes Last updated date and time of the invoice item. Sourced from Fact_InvoiceItem.last_updated_date_and_time.
invoice item last updated date DATE Yes Last updated date of the invoice item. Sourced from Fact_InvoiceItem.last_updated_date.
is payment received VARCHAR(100) Yes Whether payment has been received for the item. Sourced from Fact_InvoiceItem.is_payment_received.
is refunded VARCHAR(100) Yes Whether the item has been refunded. Sourced from Fact_InvoiceItem.is_refunded.
refund date DATE Yes Date of the refund. Sourced from Fact_InvoiceItem.refund_date.
refund date and time TIMESTAMP Yes Date and time of the refund. Sourced from Fact_InvoiceItem.refund_date_and_time.
is rebooked VARCHAR(100) Yes Whether the appointment was rebooked. Sourced from Fact_InvoiceItem.is_rebooked.
revenue (cash) FLOAT8 Yes Cash revenue amount. Sourced from Fact_InvoiceItem.revenue_cash.
total revenue FLOAT8 Yes Total revenue from the sale. Sourced from Fact_InvoiceItem.total_revenue.
total adjusted revenue FLOAT8 Yes Total adjusted revenue. Sourced from Fact_InvoiceItem.total_adjusted_revenue.
total payment value FLOAT8 Yes Total payment value received. Sourced from Fact_InvoiceItem.total_payment_value.
prepaid revenue FLOAT8 Yes Revenue from prepaid instruments. Sourced from Fact_InvoiceItem.prepaid_revenue.
revenue (membership credit redemption) FLOAT8 Yes Revenue from membership credit redemption. Sourced from Fact_InvoiceItem.
revenue (membership service redemptions) FLOAT8 Yes Revenue from membership service redemptions. Sourced from Fact_InvoiceItem.
revenue (membership initial recognized) FLOAT8 Yes Initially recognized revenue from membership. Sourced from Fact_InvoiceItem.
revenue (package initial recognized) FLOAT8 Yes Initially recognized revenue from package. Sourced from Fact_InvoiceItem.
revenue (package redemption) FLOAT8 Yes Revenue from package redemption. Sourced from Fact_InvoiceItem.
revenue (gift card redemption) FLOAT8 Yes Revenue from gift card redemption. Sourced from Fact_InvoiceItem.
revenue (pre paid card redemption) FLOAT8 Yes Revenue from pre-paid card redemption. Sourced from Fact_InvoiceItem.
redemption (membership) FLOAT8 Yes Membership redemption amount. Sourced from Fact_InvoiceItem.
redemption (loyalty points) FLOAT8 Yes Loyalty points redemption amount. Sourced from Fact_InvoiceItem.
redemption (gift card) FLOAT8 Yes Gift card redemption amount. Sourced from Fact_InvoiceItem.
redemption (cashback) FLOAT8 Yes Cashback redemption amount. Sourced from Fact_InvoiceItem.
payment (cash) FLOAT8 Yes Payment amount via cash. Sourced from Fact_InvoiceItem.
payment (card) FLOAT8 Yes Payment amount via card. Sourced from Fact_InvoiceItem.
payment (cheque) FLOAT8 Yes Payment amount via cheque. Sourced from Fact_InvoiceItem.
payment (custom) FLOAT8 Yes Payment amount via custom payment method. Sourced from Fact_InvoiceItem.
payment (membership) FLOAT8 Yes Payment amount via membership. Sourced from Fact_InvoiceItem.
payment (prepaid card) FLOAT8 Yes Payment amount via prepaid card. Sourced from Fact_InvoiceItem.
payment (gift card) FLOAT8 Yes Payment amount via gift card. Sourced from Fact_InvoiceItem.
payment (loyalty points) FLOAT8 Yes Payment amount via loyalty points. Sourced from Fact_InvoiceItem.
total payments (cash, card, cheque and custom) FLOAT8 Yes Total of cash, card, cheque, and custom payments. Sourced from Fact_InvoiceItem.
custom payment names VARCHAR(65535) Yes Names of custom payment methods used. Sourced from Fact_InvoiceItem.
card payment names VARCHAR(65535) Yes Names of card payment methods used. Sourced from Fact_InvoiceItem.
resource name VARCHAR(128) Yes Name of the resource/room. Sourced from Dim_Resource.resource_name.
resource type name VARCHAR(128) Yes Type name of the resource. Sourced from Dim_Resource.resource_type_name.
resource capacity INTEGER Yes Capacity of the resource. Sourced from Dim_Resource.capacity.
resource code VARCHAR(128) Yes Code of the resource. Sourced from Dim_Resource.resource_code.
room category name VARCHAR(1000) Yes Category name of the room. Sourced from Dim_Resource.room_category_name.
equipment name VARCHAR(1024) Yes Name of the equipment used. Sourced from Dim_Equipment.equipment_name.
is equipment active BOOLEAN Yes Whether the equipment is active. Sourced from Dim_Equipment.is_active.
equipment capacity INTEGER Yes Capacity of the equipment. Sourced from Dim_Equipment.capacity.
therapist first name VARCHAR(160) Yes First name of the therapist. Sourced from Dim_Employee.first_name (therapist).
therapist last name VARCHAR(256) Yes Last name of the therapist. Sourced from Dim_Employee.last_name (therapist).
therapist middle name VARCHAR(128) Yes Middle name of the therapist. Sourced from Dim_Employee.middle_name (therapist).
therapist code VARCHAR(128) Yes Code of the therapist. Sourced from Dim_Employee.employee_code (therapist).
therapist employee - code VARCHAR(128) Yes Alternate code of the therapist. Sourced from Dim_Employee.employee_code_alt (therapist).
therapist gender VARCHAR(14) Yes Gender of the therapist. Sourced from Dim_Employee.gender (therapist).
therapist job name VARCHAR(1020) Yes Job title of the therapist. Sourced from Dim_Employee.job_name (therapist).
therapist job code VARCHAR(8000) Yes Job code of the therapist. Sourced from Dim_Employee.job_code (therapist).
therapist request type VARCHAR(100) Yes Type of therapist request (e.g., Requested, Not Requested). Sourced from Fact_InvoiceItem.therapist_request_type.
loyalty tier name VARCHAR(2014) Yes Name of the guest loyalty tier. Sourced from Dim_User.loyalty_tier_name.
first guest status SMALLINT Yes Status indicating if this is the guest's first visit. Sourced from Fact_InvoiceItem.first_guest_status.
invoice date in center DATE Yes Invoice date in the center's local time zone. Sourced from Fact_InvoiceItem.invoice_date_in_center.
is invoice invalid BOOLEAN Yes Whether the invoice is marked invalid. Sourced from Fact_InvoiceItem.is_invoice_invalid.
comments VARCHAR(65535) Yes Comments on the invoice item. Sourced from Fact_InvoiceItem.comments.
factinvoiceitemwid BIGINT Yes Internal warehouse identifier for the fact invoice item. Sourced from Fact_InvoiceItem.wid.
appointment status VARCHAR(100) Yes Status of the related appointment. Sourced from Fact_InvoiceItem.appointment_status.
center company name VARCHAR(200) Yes Company name of the center. Sourced from Dim_Center.company_name.
is appointment rescheduled SMALLINT Yes Whether the appointment was rescheduled. Sourced from Fact_InvoiceItem.is_appointment_rescheduled.
initial appointment start time TIMESTAMP Yes Initial scheduled start time of the appointment. Sourced from Fact_InvoiceItem.initial_appointment_start_time.
action performed date time TIMESTAMP Yes Date and time when the action was performed. Sourced from Fact_InvoiceItem.action_performed_date_time.
Manual Discount INTEGER Yes Manual discount applied. Sourced from Fact_InvoiceItem.manual_discount.
User Creation Date TIMESTAMP Yes Date when the user/guest record was created. Sourced from Dim_User.creation_date.
Rebooked Appointment Count INT Yes Count of rebooked appointments. Sourced from Fact_InvoiceItem.rebooked_appointment_count.
Appointment Group PK INT Yes Primary key of the appointment group. Sourced from Fact_InvoiceItem.appointment_group_pk.
Appointment Category Code VARCHAR(1024) Yes Code of the appointment category. Sourced from Fact_InvoiceItem.appointment_category_code.
Appointment Category Name VARCHAR(1024) Yes Name of the appointment category. Sourced from Fact_InvoiceItem.appointment_category_name.
Is Appointment Category Active BOOLEAN Yes Whether the appointment category is active. Sourced from Fact_InvoiceItem.is_appointment_category_active.
Appointment PK INT Yes Primary key of the appointment. Sourced from Fact_InvoiceItem.appointment_pk.
is redo BOOLEAN Yes Whether this is a redo service. Sourced from Fact_InvoiceItem.is_redo.
is redone BOOLEAN Yes Whether the service has been redone. Sourced from Fact_InvoiceItem.is_redone.
redo therapist first name VARCHAR(160) Yes First name of the redo therapist. Sourced from Dim_Employee.first_name (redo therapist).
redo therapist last name VARCHAR(256) Yes Last name of the redo therapist. Sourced from Dim_Employee.last_name (redo therapist).
redo therapist middle name VARCHAR(128) Yes Middle name of the redo therapist. Sourced from Dim_Employee.middle_name (redo therapist).
redo therapist mobile phone VARCHAR(64) Yes Mobile phone of the redo therapist. Sourced from Dim_Employee.mobile_phone (redo therapist).
redo therapist code VARCHAR(128) Yes Code of the redo therapist. Sourced from Dim_Employee.employee_code (redo therapist).
redo therapist employee - code VARCHAR(128) Yes Alternate code of the redo therapist. Sourced from Dim_Employee.employee_code_alt (redo therapist).
redo therapist gender VARCHAR(14) Yes Gender of the redo therapist. Sourced from Dim_Employee.gender (redo therapist).
redo therapist job name VARCHAR(1020) Yes Job title of the redo therapist. Sourced from Dim_Employee.job_name (redo therapist).
redo therapist job code VARCHAR(8000) Yes Job code of the redo therapist. Sourced from Dim_Employee.job_code (redo therapist).
is redo therapist inactive BOOLEAN Yes Whether the redo therapist is inactive. Sourced from Dim_Employee.is_inactive (redo therapist).
redo therapist tags VARCHAR(65535) Yes Tags for the redo therapist. Sourced from Dim_Employee.tags (redo therapist).
redo therapist start date TIMESTAMP Yes Start date of the redo therapist. Sourced from Dim_Employee.start_date (redo therapist).
redo therapist last date TIMESTAMP Yes Last working date of the redo therapist. Sourced from Dim_Employee.last_date (redo therapist).
usage type VARCHAR(80) Yes Usage type of the item. Sourced from Fact_InvoiceItem.usage_type.
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 selling employee. Sourced from Dim_Employee.additional_field_1.
employee additional field 2 NVARCHAR(8000) Yes Custom additional field 2 for the selling employee. Sourced from Dim_Employee.additional_field_2.
therapist additional field 1 NVARCHAR(8000) Yes Custom additional field 1 for the therapist. Sourced from Dim_Employee.additional_field_1 (therapist).
therapist additional field 2 NVARCHAR(8000) Yes Custom additional field 2 for the therapist. Sourced from Dim_Employee.additional_field_2 (therapist).
createdby additional field 1 NVARCHAR(8000) Yes Custom additional field 1 for the creating employee. Sourced from Dim_Employee.additional_field_1 (created by).
createdby additional field 2 NVARCHAR(8000) Yes Custom additional field 2 for the creating employee. Sourced from Dim_Employee.additional_field_2 (created by).
closeby additional field 1 NVARCHAR(8000) Yes Custom additional field 1 for the closing employee. Sourced from Dim_Employee.additional_field_1 (closed by).
closeby additional field 2 NVARCHAR(8000) Yes Custom additional field 2 for the closing employee. Sourced from Dim_Employee.additional_field_2 (closed by).
redotherapist additional field 1 NVARCHAR(8000) Yes Custom additional field 1 for the redo therapist. Sourced from Dim_Employee.additional_field_1 (redo therapist).
redotherapist additional field 2 NVARCHAR(8000) Yes Custom additional field 2 for the redo therapist. Sourced from Dim_Employee.additional_field_2 (redo therapist).
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).
refund amount FLOAT Yes Amount refunded. Sourced from Fact_InvoiceItem.refund_amount.
refund quantity INTEGER Yes Quantity refunded. Sourced from Fact_InvoiceItem.refund_quantity.
refund source invoice item id VARCHAR(65535) Yes Source invoice item ID for the refund. Sourced from Fact_InvoiceItem.refund_source_invoice_item_id.
therapist Last Date TIMESTAMP Yes Last working date of the therapist. Sourced from Dim_Employee.last_date (therapist).
createdby employee Last Date TIMESTAMP Yes Last working date of the creating employee. Sourced from Dim_Employee.last_date (created by).
closeby employee Last Date TIMESTAMP Yes Last working date of the closing employee. Sourced from Dim_Employee.last_date (closed by).
is therapist inactive BOOLEAN Yes Whether the therapist is inactive. Sourced from Dim_Employee.is_inactive (therapist).
is sale price adjusted BOOLEAN Yes Whether the sale price was adjusted. Sourced from Fact_InvoiceItem.is_sale_price_adjusted.
original sale price FLOAT Yes Original sale price before adjustment. Sourced from Fact_InvoiceItem.original_sale_price.
Minutes To ADD INTEGER Yes Minutes offset to add for timezone conversion. Sourced from Dim_Center.minutes_to_add.
Center Code VARCHAR(512) Yes Code of the center. Sourced from Dim_Center.center_code.
Package Code VARCHAR(512) Yes Code of the package. Sourced from Dim_Package.package_code.
Product Code VARCHAR(512) Yes Code of the product. Sourced from Dim_Product.product_code.
next sale date DATE Yes Date of the guest's next sale. Sourced from Fact_InvoiceItem.next_sale_date.
last sale date DATE Yes Date of the guest's last sale. Sourced from Fact_InvoiceItem.last_sale_date.
next service sale date DATE Yes Date of the guest's next service sale. Sourced from Fact_InvoiceItem.next_service_sale_date.
last service sale date DATE Yes Date of the guest's last service sale. Sourced from Fact_InvoiceItem.last_service_sale_date.
follow up INTEGER Yes Follow-up indicator. Sourced from Fact_InvoiceItem.follow_up.
is upsell BOOLEAN Yes Whether the item is an upsell. Sourced from Fact_InvoiceItem.is_upsell.
days to next visit BIGINT Yes Number of days until the guest's next visit. Sourced from Fact_InvoiceItem.days_to_next_visit.
days from last visit BIGINT Yes Number of days since the guest's last visit. Sourced from Fact_InvoiceItem.days_from_last_visit.
Sale Tax FLOAT Yes Sale tax amount. Sourced from Fact_InvoiceItem.sale_tax.
Discount With Tax FLOAT Yes Discount amount including tax. Sourced from Fact_InvoiceItem.discount_with_tax.
Is Tax Exempted SMALLINT Yes Whether the sale is tax-exempted. Sourced from Fact_InvoiceItem.is_tax_exempted.
Custom Discount FLOAT Yes Custom discount amount. Sourced from Fact_InvoiceItem.custom_discount.
Daily Recurring Revenue(DRR) FLOAT Yes Daily recurring revenue value. Sourced from Fact_InvoiceItem.drr.
DRR With Discount or Order Setting FLOAT Yes DRR with discount or order setting applied. Sourced from Fact_InvoiceItem.drr_with_discount_or_order_setting.
Refund Tax FLOAT Yes Tax amount on refund. Sourced from Fact_InvoiceItem.refund_tax.
Rounding Adjustment FLOAT Yes Rounding adjustment amount. Sourced from Fact_InvoiceItem.rounding_adjustment.
Group Invoice Order INT Yes Order within the group invoice. Sourced from Fact_InvoiceItem.group_invoice_order.
Group Invoice Id VARCHAR(36) Yes Identifier of the group invoice. Sourced from Fact_InvoiceItem.group_invoice_id.
Membership Type VARCHAR(200) Yes Type of the membership. Sourced from Dim_Membership.membership_type.
Gift Card Template Name NVARCHAR(1024) Yes Template name of the gift card. Sourced from Dim_GiftCard.template_name.
Gift Card Template Description NVARCHAR(8192) Yes Template description of the gift card. Sourced from Dim_GiftCard.template_description.
ReStock Quantity FLOAT Yes Restock quantity for the product. Sourced from Fact_InvoiceItem.restock_quantity.
factinvoiceitemid INT Yes Internal integer identifier for the fact invoice item. Sourced from Fact_InvoiceItem.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_InvoiceItem.workshop_name.
Track Name VARCHAR(128) Yes Name of the track. Sourced from Fact_InvoiceItem.track_name.
WorkShop Code VARCHAR(32) Yes Code of the workshop. Sourced from Fact_InvoiceItem.workshop_code.
Track Code VARCHAR(32) Yes Code of the track. Sourced from Fact_InvoiceItem.track_code.
Session Name VARCHAR(256) Yes Name of the class session. Sourced from Fact_InvoiceItem.session_name.
Class Name VARCHAR(128) Yes Name of the class. Sourced from Fact_InvoiceItem.class_name.
Class Code VARCHAR(32) Yes Code of the class. Sourced from Fact_InvoiceItem.class_code.
Session Code VARCHAR(32) Yes Code of the session. Sourced from Fact_InvoiceItem.session_code.
add on guest membership status VARCHAR(300) Yes Membership status of the guest for add-on items. Sourced from Fact_InvoiceItem.
discount membership sale center name VARCHAR(300) Yes Center name where the discount membership was sold. Sourced from Fact_InvoiceItem.
discount membership name VARCHAR(512) Yes Name of the membership used for discount. Sourced from Fact_InvoiceItem.
discount membership category name VARCHAR(512) Yes Category of the membership used for discount. Sourced from Fact_InvoiceItem.
discount membership code VARCHAR(100) Yes Code of the membership used for discount. Sourced from Fact_InvoiceItem.
is membership discount BOOLEAN Yes Whether a membership discount was applied. Sourced from Fact_InvoiceItem.
discount user membership id VARCHAR(36) Yes User membership ID used for the discount. Sourced from Fact_InvoiceItem.
brand name VARCHAR(512) Yes Brand name associated with the item. Sourced from Fact_InvoiceItem.brand_name.
appointment id VARCHAR(36) Yes Unique identifier of the related appointment. Sourced from Fact_InvoiceItem.appointment_id.
fee name VARCHAR(1000) Yes Name of the fee applied. Sourced from Fact_InvoiceItem.fee_name.
Refund Reason VARCHAR(65535) Yes Reason for the refund. Sourced from Fact_InvoiceItem.refund_reason.
utm source NVARCHAR(128) Yes UTM source tracking parameter. Sourced from Fact_InvoiceItem.utm_source.
utm medium NVARCHAR(128) Yes UTM medium tracking parameter. Sourced from Fact_InvoiceItem.utm_medium.
bundle package name VARCHAR(1024) Yes Name of the bundle package. Sourced from Fact_InvoiceItem.bundle_package_name.
bundle package number INTEGER Yes Number of the bundle package. Sourced from Fact_InvoiceItem.bundle_package_number.
gift card redemption with tax FLOAT8 Yes Gift card redemption amount including tax. Sourced from Fact_InvoiceItem.gift_card_redemption_with_tax.
gc tax redeemed FLOAT8 Yes Gift card tax redeemed amount. Sourced from Fact_InvoiceItem.gc_tax_redeemed.
lp redemption with tax FLOAT8 Yes Loyalty point redemption amount including tax. Sourced from Fact_InvoiceItem.lp_redemption_with_tax.

Primary Key

No explicit primary key defined. The combination of fact invoiceitem wid and invoice item id can serve as a logical unique identifier.

Foreign Keys

  • fact invoiceitem wid / factinvoiceitemwid - References Fact_InvoiceItem warehouse ID
  • 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)
  • Group Invoice Id - References the group invoice record
  • appointment id - References the appointment record

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 0 = Active
1 = Voided
appointment status -2 = No Show
appointment cancel or no show status -2 = No Show
campaign type 1 = Email
therapist request type 0 = Any
usage type 0 = Service
guest member status 0 = Never a Member
invoice status 0 = Open
invoice source 0 = Store
package type 4 = Series Package

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.