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 idreferences DimUser (sales guest)guest wid/opportunity guest idreferences DimUser (opportunity guest)invoice id/invoice item idreferences FactInvoice / FactInvoiceItemfactcollectionwidreferences FactCollectionfactinvoiceitemwidreferences 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
issuewidreferences 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.