ZenotiHelp

Opportunities Fact Consolidated

ZTZenoti Docs Team Updated April 10, 2026 7 min readGuide

Consolidated fact table capturing sales opportunity records including opportunity lifecycle dates, pricing, dispositions, associated guest details, and employee roles (owner, updater, creator).

Columns

Column Name Data Type Nullable Description
issuewid BIGINT Yes Warehouse identifier for the parent issue.
opportunity id VARCHAR(36) Yes Unique identifier (UUID) of the opportunity.
opportunity no INTEGER Yes Sequential opportunity number.
opportunity created date TIMESTAMP Yes Date and time the opportunity was created (center local time).
opportunity created date utc TIMESTAMP Yes Date and time the opportunity was created in UTC.
opportunity start date TIMESTAMP Yes Start date and time of the opportunity.
opportunity end date TIMESTAMP Yes End date and time of the opportunity.
opportunity title VARCHAR(16000) Yes Title of the opportunity.
opportunity desc VARCHAR(65535) Yes Detailed description of the opportunity.
opportunity type VARCHAR(16000) Yes Type classification of the opportunity.
sales stage VARCHAR(256) Yes Current sales stage of the opportunity.
list price DOUBLE PRECISION Yes List price associated with the opportunity.
offered price DOUBLE PRECISION Yes Offered/quoted price for the opportunity.
follow up date TIMESTAMP Yes Scheduled follow-up date and time.
expected close date TIMESTAMP Yes Expected close date of the opportunity.
optional text 1 VARCHAR(16000) Yes Custom optional text field 1.
optional text 2 VARCHAR(16000) Yes Custom optional text field 2.
optional text 3 VARCHAR(16000) Yes Custom optional text field 3.
issue type name VARCHAR(1020) Yes Type name of the related issue.
priority name VARCHAR(256) Yes Priority level of the opportunity.
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.
zone name VARCHAR(800) Yes Zone name of the center. Sourced from DimCenter.zone_name.
center name VARCHAR(128) Yes Name of the center. Sourced from DimCenter.center_name.
center city VARCHAR(128) Yes City of the center. Sourced from DimCenter.city.
center zip VARCHAR(32) Yes Zip code of the center. Sourced from DimCenter.zip.
center state name VARCHAR(4096) Yes State of the center. Sourced from DimCenter.state_name.
country name VARCHAR(4096) Yes Country of the center. Sourced from DimCenter.country_name.
operating center BOOLEAN Yes Indicates whether the center is operating. Sourced from DimCenter.operating_center.
center wid BIGINT Yes Warehouse identifier for the center. Sourced from DimCenter.center_wid.
guest wid BIGINT Yes Warehouse identifier for the guest. Sourced from DimGuest.guest_wid.
guest id VARCHAR(36) Yes Unique identifier (UUID) of the guest. Sourced from DimGuest.guest_id.
guest first name VARCHAR(256) 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(256) Yes Middle name of the guest. Sourced from DimGuest.middle_name.
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(512) 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 gender VARCHAR(14) Yes Gender of the guest. Sourced from DimGuest.gender.
guest dob TIMESTAMP Yes Date of birth of the guest. Sourced from DimGuest.dob.
referred by guestid VARCHAR(36) Yes Guest ID of the referrer. Sourced from DimGuest.referred_by_guestid.
guest anniversary date TIMESTAMP Yes Anniversary date of the guest. Sourced from DimGuest.anniversary_date.
guest state VARCHAR(4096) Yes State of the guest. Sourced from DimGuest.state.
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.
guest code VARCHAR(128) Yes Unique guest code identifier. Sourced from DimGuest.guest_code.
guest referral source VARCHAR(3200) Yes Referral source of the guest. Sourced from DimGuest.referral_source.
guest email state INTEGER Yes Email subscription state of the guest. Sourced from DimGuest.email_state.
guest receive transactional email BOOLEAN Yes Whether the guest receives transactional emails. Sourced from DimGuest.receive_transactional_email.
guest receive marketing email BOOLEAN Yes Whether the guest receives marketing emails. Sourced from DimGuest.receive_marketing_email.
guest receive transactional sms BOOLEAN Yes Whether the guest receives transactional SMS. Sourced from DimGuest.receive_transactional_sms.
guest receive marketing sms BOOLEAN Yes Whether the guest receives marketing SMS. Sourced from DimGuest.receive_marketing_sms.
no sale comments VARCHAR(65535) Yes Comments provided when opportunity results in no sale.
owner first name VARCHAR(256) Yes First name of the opportunity owner (employee). Sourced from DimEmployee.first_name.
owner last name VARCHAR(256) Yes Last name of the opportunity owner. Sourced from DimEmployee.last_name.
owner middle name VARCHAR(256) Yes Middle name of the opportunity owner. Sourced from DimEmployee.middle_name.
owner mobile phone VARCHAR(64) Yes Mobile phone of the owner. Sourced from DimEmployee.mobile_phone.
owner code VARCHAR(128) Yes Code of the owner. Sourced from DimEmployee.employee_code.
owner employee - code VARCHAR(512) Yes Alternate code of the owner. Sourced from DimEmployee.employee_code.
owner gender VARCHAR(14) Yes Gender of the owner. Sourced from DimEmployee.gender.
owner job name VARCHAR(1020) Yes Job title of the owner. Sourced from DimEmployee.job_name.
owner job code VARCHAR(8000) Yes Job code of the owner. Sourced from DimEmployee.job_code.
is owner inactive BOOLEAN Yes Indicates whether the owner is inactive. Sourced from DimEmployee.is_inactive.
opportunity created by VARCHAR(36) Yes User ID (UUID) of the opportunity creator.
updated by first name VARCHAR(256) Yes First name of the employee who last updated the opportunity. Sourced from DimEmployee.first_name.
updated by last name VARCHAR(256) Yes Last name of the updater. Sourced from DimEmployee.last_name.
updated by middle name VARCHAR(256) Yes Middle name of the updater. Sourced from DimEmployee.middle_name.
updated by mobile phone VARCHAR(64) Yes Mobile phone of the updater. Sourced from DimEmployee.mobile_phone.
updated by code VARCHAR(128) Yes Code of the updater. Sourced from DimEmployee.employee_code.
updated by employee - code VARCHAR(512) Yes Alternate code of the updater. Sourced from DimEmployee.employee_code.
updated by gender VARCHAR(14) Yes Gender of the updater. Sourced from DimEmployee.gender.
updated by job name VARCHAR(1020) Yes Job title of the updater. Sourced from DimEmployee.job_name.
updated by job code VARCHAR(8000) Yes Job code of the updater. Sourced from DimEmployee.job_code.
is updated by inactive BOOLEAN Yes Indicates whether the updater is inactive. Sourced from DimEmployee.is_inactive.
disposition name VARCHAR(65535) Yes Name of the disposition/outcome.
disposition description VARCHAR(65535) Yes Description of the disposition/outcome.
dispostion followup days INTEGER Yes Number of follow-up days set by the disposition.
opportunity source code VARCHAR(256) Yes Code of the opportunity source.
opportunity source name VARCHAR(256) Yes Name of the opportunity source.
opportunity source description VARCHAR(4096) Yes Description of the opportunity source.
opportunity source active BOOLEAN Yes Indicates whether the opportunity source is active.
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.
oppurtunity voided VARCHAR(14) Yes Indicates whether the opportunity is voided.
center company name VARCHAR(200) Yes Company name of the center. Sourced from DimCenter.company_name.
created by first name VARCHAR(256) Yes First name of the employee who created the opportunity. Sourced from DimEmployee.first_name.
created by last name VARCHAR(256) Yes Last name of the creator. Sourced from DimEmployee.last_name.
created by middle name VARCHAR(256) Yes Middle name of the creator. Sourced from DimEmployee.middle_name.
created by mobile phone VARCHAR(64) Yes Mobile phone of the creator. Sourced from DimEmployee.mobile_phone.
created by code VARCHAR(128) Yes Code of the creator. Sourced from DimEmployee.employee_code.
created by employee - code VARCHAR(512) Yes Alternate code of the creator. Sourced from DimEmployee.employee_code.
created by gender VARCHAR(14) Yes Gender of the creator. Sourced from DimEmployee.gender.
created by job name VARCHAR(1020) Yes Job title of the creator. Sourced from DimEmployee.job_name.
created by job code VARCHAR(8000) Yes Job code of the creator. Sourced from DimEmployee.job_code.
is created by inactive BOOLEAN Yes Indicates whether the creator is inactive. Sourced from DimEmployee.is_inactive.
next lead start date TIMESTAMP Yes Start date of the next lead follow-up.
conv_leg INTEGER Yes Conversion leg indicator.
invoicecreateddate DATE Yes Date the related invoice was created.
minutes to add INTEGER Yes Timezone offset in minutes for the center.
notes VARCHAR(65535) Yes Additional notes associated with the opportunity.
issuepk NUMERIC(19,0) Yes Primary key of the related issue record.
Center Code VARCHAR(40) Yes Code of the center. Sourced from DimCenter.center_code.

Primary Key

No explicit primary key is defined. opportunity id serves as the logical key. issuewid is used as the distribution key.

Foreign Keys

  • guest id, guest wid references DimGuest
  • center name, Center Code, center wid references DimCenter
  • owner first name, owner code references DimEmployee (owner)
  • updated by first name, updated by code references DimEmployee (updater)
  • created by first name, created by code references DimEmployee (creator)
  • issuewid, issuepk references issues_fact_consolidated

Enumerator Values

Column Name Known Values
sales stage Lead, Prospect, Negotiation, Closed Won, Closed Lost, etc.
priority name Low, Medium, High, Critical, etc.
oppurtunity voided True, False
optional field 1 Custom-defined values
guest gender Male, Female, etc.
guest email state Integer codes representing email subscription states

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.