ZenotiHelp

Sales Opportunities Consolidated

ZTZenoti Docs Team Updated April 10, 2026 11 min readGuide

Consolidated table storing sales opportunities data combining opportunity lifecycle details, associated invoice/collection records, guest information, employee roles (therapist, created-by, owner), and payment details.

Columns

Column Name Data Type Nullable Description
sales organization name VARCHAR(512) YES Name of the organization where the sale occurred. Sourced from DimOrganization.organization_name.
sales organization account name VARCHAR(256) YES Account name of the organization where the sale occurred. Sourced from DimOrganization.organization_account_name.
sales zone name VARCHAR(800) YES Zone name where the sale occurred. Sourced from DimZone.zone_name.
sales center name VARCHAR(128) YES Center name where the sale occurred. Sourced from DimCenter.center_name.
sales center company name VARCHAR(200) YES Company name of the center where the sale occurred. Sourced from DimCenter.center_company_name.
sales user wid BIGINT YES Warehouse ID of the guest in the sales context. Sourced from DimUser.userwid.
sales user id VARCHAR(36) YES Unique identifier of the guest in the sales context. Sourced from DimUser.userid.
sales guest first name VARCHAR(160) YES First name of the guest in the sales context. Sourced from DimUser.firstname.
sales guest last name VARCHAR(256) YES Last name of the guest in the sales context. Sourced from DimUser.lastname.
sales guest middle name VARCHAR(128) YES Middle name of the guest in the sales context. Sourced from DimUser.middlename.
sales guest code VARCHAR(128) YES Code of the guest in the sales context. Sourced from DimUser.usercode.
sales guest zipcode VARCHAR(128) YES ZIP/postal code of the guest in the sales context. Sourced from DimUser.zipcode.
guest tags VARCHAR(65535) YES Tags associated with the guest. Sourced from DimUser.usertags.
sales guest email VARCHAR(256) YES Email address of the guest in the sales context. Sourced from DimUser.email.
item type code VARCHAR(100) YES Code identifying the type of item sold (e.g., Service, Product).
service businessunit VARCHAR(200) YES Business unit of the service. Sourced from DimService.service_businessunit.
service name VARCHAR(512) YES Name of the service. Sourced from DimService.service_name.
service category VARCHAR(256) YES Category of the service. Sourced from DimService.service_category.
service parent category VARCHAR(256) YES Parent category of the service. Sourced from DimService.service_parent_category.
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.product_category.
product parent category VARCHAR(256) YES Parent category of the product. Sourced from DimProduct.product_parent_category.
product business unit VARCHAR(200) YES Business unit of the product. Sourced from DimProduct.product_business_unit.
package name VARCHAR(512) YES Name of the package. Sourced from DimPackage.package_name.
package category VARCHAR(200) YES Category of the package. Sourced from DimPackage.package_category.
package businessunit VARCHAR(200) YES Business unit of the package. Sourced from DimPackage.package_businessunit.
businessunit VARCHAR(65535) YES General business unit associated with the invoice item.
invoice id VARCHAR(36) YES Unique identifier of the associated invoice.
invoice item id VARCHAR(36) YES Unique identifier of the associated invoice item.
invoice no VARCHAR(40) YES Invoice number.
quantity sold INTEGER YES Quantity of items sold.
sale price DOUBLE PRECISION YES Sale price of the item.
invoice item created date DATE YES Date when the invoice item was created.
invoice item created date and time TIMESTAMP WITHOUT TIME ZONE YES Date and time when the invoice item was created.
sale date DATE YES Date of the sale.
sale date and time TIMESTAMP WITHOUT TIME ZONE YES Date and time of the sale.
invoice includes service VARCHAR(5) YES Whether the invoice includes a service item (Yes/No).
invoice includes product VARCHAR(5) YES Whether the invoice includes a product item (Yes/No).
invoice includes membership VARCHAR(5) YES Whether the invoice includes a membership item (Yes/No).
invoice includes package VARCHAR(5) YES Whether the invoice includes a package item (Yes/No).
invoice includes day package VARCHAR(5) YES Whether the invoice includes a day package item (Yes/No).
invoice includes prepaid/gift card VARCHAR(5) YES Whether the invoice includes a prepaid or gift card item (Yes/No).
invoice includes room charges VARCHAR(5) YES Whether the invoice includes room charges (Yes/No).
invoice includes class VARCHAR(5) YES Whether the invoice includes a class item (Yes/No).
invoice includes membership freeze fee VARCHAR(5) YES Whether the invoice includes a membership freeze fee (Yes/No).
guest purchased class before VARCHAR(5) YES Whether the guest has purchased a class before (Yes/No).
guest purchased day package before VARCHAR(5) YES Whether the guest has purchased a day package before (Yes/No).
guest purchased membership before VARCHAR(5) YES Whether the guest has purchased a membership before (Yes/No).
guest purchased package before VARCHAR(5) YES Whether the guest has purchased a package before (Yes/No).
guest purchased prepaid/gift card before VARCHAR(5) YES Whether the guest has purchased a prepaid/gift card before (Yes/No).
guest purchased product before VARCHAR(5) YES Whether the guest has purchased a product before (Yes/No).
guest purchased service before VARCHAR(5) YES Whether the guest has purchased a service before (Yes/No).
appointment cancel or no show status VARCHAR(21) YES Cancellation or no-show status of the appointment.
appointment status VARCHAR(100) YES Current status of the associated appointment.
is invoice invalid BOOLEAN YES Indicates whether the invoice is invalid.
discount applied DOUBLE PRECISION YES Total discount applied to the invoice item.
factinvoiceitemwid BIGINT YES Warehouse ID of the invoice item fact record.
list price DOUBLE PRECISION YES List price of the item before discount.
tax DOUBLE PRECISION YES Tax amount on the invoice item.
employee first name VARCHAR(160) YES First name of the sale-by employee. Sourced from DimEmployee.employee_first_name.
employee last name VARCHAR(256) YES Last name of the sale-by employee. Sourced from DimEmployee.employee_last_name.
employee middle name VARCHAR(128) YES Middle name of the sale-by employee. Sourced from DimEmployee.employee_middle_name.
employee mobile phone VARCHAR(64) YES Mobile phone of the sale-by employee. Sourced from DimEmployee.employee_mobile_phone.
employee code VARCHAR(128) YES Code of the sale-by employee. Sourced from DimEmployee.employee_code.
employee gender VARCHAR(14) YES Gender of the sale-by employee. Sourced from DimEmployee.employee_gender.
employee job name VARCHAR(1020) YES Job title of the sale-by employee. Sourced from DimEmployee.employee_job_name.
employee job code VARCHAR(8000) YES Job code of the sale-by employee. Sourced from DimEmployee.employee_job_code.
employee last date TIMESTAMP WITHOUT TIME ZONE YES Last working date of the sale-by employee. Sourced from DimEmployee.employee_last_date.
therapist first name VARCHAR(160) YES First name of the therapist. Sourced from DimEmployee.employee_first_name.
therapist last name VARCHAR(256) YES Last name of the therapist. Sourced from DimEmployee.employee_last_name.
therapist middle name VARCHAR(128) YES Middle name of the therapist. Sourced from DimEmployee.employee_middle_name.
therapist code VARCHAR(128) YES Code of the therapist. Sourced from DimEmployee.employee_code.
therapist job name VARCHAR(1020) YES Job title of the therapist. Sourced from DimEmployee.employee_job_name.
therapist job code VARCHAR(8000) YES Job code of the therapist. Sourced from DimEmployee.employee_job_code.
createdby employee first name VARCHAR(160) YES First name of the employee who created the record. Sourced from DimEmployee.employee_first_name.
createdby employee last name VARCHAR(256) YES Last name of the employee who created the record. Sourced from DimEmployee.employee_last_name.
createdby employee middle name VARCHAR(128) YES Middle name of the employee who created the record. Sourced from DimEmployee.employee_middle_name.
createdby employee mobile phone VARCHAR(64) YES Mobile phone of the employee who created the record. Sourced from DimEmployee.employee_mobile_phone.
createdby employee code VARCHAR(128) YES Code of the employee who created the record. Sourced from DimEmployee.employee_code.
createdby employee job name VARCHAR(1020) YES Job title of the employee who created the record. Sourced from DimEmployee.employee_job_name.
createdby employee job code VARCHAR(8000) YES Job code of the employee who created the record. Sourced from DimEmployee.employee_job_code.
follow up INTEGER YES Follow-up indicator or count.
is upsell BOOLEAN YES Indicates whether this item was an upsell.
no sale comments VARCHAR(65535) YES Comments recorded when no sale was made.
center name VARCHAR(128) YES Name of the opportunity center. Sourced from DimCenter.center_name.
organization account name VARCHAR(256) YES Account name of the opportunity organization. Sourced from DimOrganization.organization_account_name.
zone name VARCHAR(800) YES Zone name of the opportunity. Sourced from DimZone.zone_name.
business unit name VARCHAR(65535) YES Business unit name associated with the opportunity.
center company name VARCHAR(200) YES Company name of the opportunity center. Sourced from DimCenter.center_company_name.
guest id VARCHAR(36) YES Unique identifier of the opportunity guest.
opportunity guest code VARCHAR(128) YES Code of the guest associated with the opportunity.
opportunity guest zipcode VARCHAR(128) YES ZIP/postal code of the opportunity guest.
opportunity no INTEGER YES Opportunity sequence number.
opportunity start date TIMESTAMP WITHOUT TIME ZONE YES Start date of the opportunity.
opportunity end date TIMESTAMP WITHOUT TIME ZONE YES End date of the opportunity.
opportunity title VARCHAR(16000) YES Title of the opportunity.
opportunity desc VARCHAR(16000) YES Description of the opportunity.
opportunity type VARCHAR(16000) YES Type of the opportunity.
sales stage VARCHAR(64) YES Current sales stage of the opportunity.
opportunity organization name VARCHAR(512) YES Organization name for the opportunity. Sourced from DimOrganization.organization_name.
opportunity organization account name VARCHAR(256) YES Organization account name for the opportunity. Sourced from DimOrganization.organization_account_name.
opportunity zone name VARCHAR(800) YES Zone name for the opportunity. Sourced from DimZone.zone_name.
opportunity center company name VARCHAR(200) YES Company name for the opportunity center. Sourced from DimCenter.center_company_name.
opportunity operating center BOOLEAN YES Indicates whether the opportunity center is an operating center. Sourced from DimCenter.operating_center.
guest wid BIGINT YES Warehouse ID of the opportunity guest. Sourced from DimUser.userwid.
opportunity guest id VARCHAR(36) YES Unique identifier of the opportunity guest.
opportunity guest first name VARCHAR(160) YES First name of the opportunity guest. Sourced from DimUser.firstname.
opportunity guest last name VARCHAR(256) YES Last name of the opportunity guest. Sourced from DimUser.lastname.
opportunity guest middle name VARCHAR(128) YES Middle name of the opportunity guest. Sourced from DimUser.middlename.
opportunity guest mobile phone VARCHAR(64) YES Mobile phone of the opportunity guest. Sourced from DimUser.mobilephone.
opportunity guest phone code INTEGER YES Phone country code of the opportunity guest. Sourced from DimUser.phonecode.
opportunity guest gender VARCHAR(14) YES Gender of the opportunity guest. Sourced from DimUser.gender.
opportunity owner middle name VARCHAR(128) YES Middle name of the opportunity owner. Sourced from DimEmployee.employee_middle_name.
opportunity owner gender VARCHAR(14) YES Gender of the opportunity owner. Sourced from DimEmployee.employee_gender.
disposition name VARCHAR(1600) YES Disposition or outcome name for the opportunity.
created by middle name VARCHAR(128) YES Middle name of the user who created the opportunity.
created by mobile phone VARCHAR(64) YES Mobile phone of the user who created the opportunity.
created by code VARCHAR(128) YES Code of the user who created the opportunity.
created by gender VARCHAR(14) YES Gender of the user who created the opportunity.
created by job name VARCHAR(1020) YES Job title of the user who created the opportunity.
is created by inactive BOOLEAN YES Indicates whether the creating user is inactive.
oppurtunity voided VARCHAR(14) YES Indicates whether the opportunity has been voided (Yes/No).
payment type VARCHAR(100) YES Type of payment collected.
payment option type VARCHAR(40) YES Payment option used for collection.
collections center name VARCHAR(128) YES Center name where the collection was made. Sourced from DimCenter.center_name.
payment date DATE YES Date of the payment.
amount DOUBLE PRECISION YES Payment amount collected.
amount type VARCHAR(100) YES Type of the amount (e.g., Payment, Refund).
consider in financials VARCHAR(5) YES Whether this record is considered in financial reporting (Yes/No).
issuewid BIGINT YES Warehouse ID of the associated issue.
min invoice item created date DATE YES Earliest invoice item creation date for this opportunity.
is non candidate INTEGER YES Indicates whether this is a non-candidate record.
factcollectionwid BIGINT YES Warehouse ID of the fact collection record.
opportunity center name VARCHAR(128) YES Center name associated with the opportunity. Sourced from DimCenter.center_name.
opportunity id VARCHAR(36) YES Unique identifier for the opportunity.
collections invoice item created date TIMESTAMP WITHOUT TIME ZONE YES Date and time when the collection invoice item was created.
collections business unit name VARCHAR(65535) YES Business unit associated with the collection.
created by first name VARCHAR(160) YES First name of the user who created the opportunity.
created by last name VARCHAR(256) YES Last name of the user who created the opportunity.
created by job code VARCHAR(8000) YES Job code of the user who created the opportunity.
opportunity created date TIMESTAMP WITHOUT TIME ZONE YES Date and time when the opportunity was created.
converted or legacy INTEGER YES Indicates whether this is a converted or legacy opportunity.
opportunity owner first name VARCHAR(160) YES First name of the opportunity owner. Sourced from DimEmployee.employee_first_name.
opportunity owner last name VARCHAR(256) YES Last name of the opportunity owner. Sourced from DimEmployee.employee_last_name.
opportunity owner job name VARCHAR(1020) YES Job title of the opportunity owner. Sourced from DimEmployee.employee_job_name.
opportunity owner job code VARCHAR(8000) YES Job code of the opportunity owner. Sourced from DimEmployee.employee_job_code.
optional field 1 VARCHAR(14) YES Custom optional field 1.
optional field 2 VARCHAR(16000) YES Custom optional field 2.
optional field 3 VARCHAR(16000) YES Custom optional field 3.
optional field 4 VARCHAR(16000) YES Custom optional field 4.
opportunity source code VARCHAR(256) YES Source code of the opportunity.
opportunity source name VARCHAR(256) YES Source name of the opportunity.
booked date DATE YES Date when the opportunity was booked.
package sale date DATE YES Date of the associated package sale.

Primary Key

opportunity id (VARCHAR(36)) serves as the distribution key; no explicit PK constraint defined. Records are uniquely identified by a combination of opportunity id, invoice item id, and factcollectionwid.

Foreign Keys

  • sales user wid / sales user id references DimUser (sales guest)
  • guest wid / opportunity guest id references DimUser (opportunity guest)
  • invoice id / invoice item id references FactInvoice / FactInvoiceItem
  • factcollectionwid references FactCollection
  • factinvoiceitemwid references FactInvoiceItem
  • Sale-by employee columns reference DimEmployee
  • Therapist columns reference DimEmployee
  • Created-by employee columns reference DimEmployee
  • Opportunity owner columns reference DimEmployee
  • Service columns reference DimService
  • Product columns reference DimProduct
  • Package columns reference DimPackage
  • Center columns reference DimCenter
  • issuewid references issue tracking records

Enumerator Values

Column Name Known Values
item type code Service, Product, Membership, Package, Gift Card, Day Package
sales stage Sales pipeline stage values (e.g., Prospect, Qualified, Closed Won, Closed Lost)
invoice includes service Yes, No
invoice includes product Yes, No
invoice includes membership Yes, No
invoice includes package Yes, No
invoice includes day package Yes, No
invoice includes prepaid/gift card Yes, No
invoice includes room charges Yes, No
invoice includes class Yes, No
invoice includes membership freeze fee Yes, No
guest purchased class before Yes, No
guest purchased day package before Yes, No
guest purchased membership before Yes, No
guest purchased package before Yes, No
guest purchased prepaid/gift card before Yes, No
guest purchased product before Yes, No
guest purchased service before Yes, No
oppurtunity voided Yes, No
consider in financials Yes, No
payment type Cash, Credit Card, Check, Custom
amount type Payment, Refund
appointment cancel or no show status Cancelled, No Show, and other status values
employee gender Male, Female, Other
opportunity guest gender Male, Female, Other
opportunity owner gender Male, Female, Other
created by gender Male, Female, Other

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.