ZenotiHelp

Analytics data export column value reference

ZTZenoti Docs Team Updated November 11, 2026 5 min readGuide

Several columns in the HyperConnect, AI Lead Manager, Challenges, Class Registration and Employee KPI tables store a number whose meaning isn't visible in the data. This article lists what each stored value means. It applies to both Analytics β€” Amazon S3 and Analytics β€” Redshift Data Share.

Audience: Data engineers and BI developers who build reports on the analytics data export.


On this page


Before you begin

  • Codes in HyperConnect tables arrive as text. Compare against '0', '1' and so on, or cast the column first.
  • New values can be added to any set below. Existing values are never reassigned a different meaning. Don't reject a code you don't recognize.
  • Table names in this article have no suffix. In Analytics β€” Amazon S3, add _s3 to the table name.

Coded columns

call_type β€” Bi_Facthcvoicecalllog

Value Meaning
0 Incoming
1 Outgoing
2 Missed
3 Cancelled
4 Transferred (legacy)
5 AI Receptionist (legacy)

Values 4 and 5 are kept for historical rows. On current data, read is_transferred and from_voicebot instead. call_type carries the original direction.

In Analytics β€” Redshift Data Share, call_type is stored as text. The values are 0, 1, 2 and null.

voicemail_activity_status β€” Bi_Facthcvoicecalllog

Value Meaning
-1 Resolved
0 Unread
1 Read
2 Listened
3 Listened and read
4 Called back
5 Assigned
6 Messaged
7 Forwarded
8 Forwarded to center

review_status β€” Bi_Facthcreputereviewstore

Value Meaning
0 Active
1 Deleted at source β€” removed from Google or Facebook

Rows are never physically deleted. Treat 1 as a soft delete. In Analytics β€” Redshift Data Share, review_status is stored as text.

ChallengeType β€” Bi_DimChallenges

Value Meaning
1 Standard β€” one metric, one target
2 Multi-metric β€” several metrics, each with its own target
3 Multi-tier β€” one metric with levels, each with a higher target

All challenges created before this feature was added were set to 1.

Note: ChallengeType is on Bi_DimChallenges, not Bi_FactChallenges. Bi_FactChallenges has LevelNum, which holds the tier a guest enrolled at. A null LevelNum means Level 1.

WaitlistPromotionSource β€” Bi_FactRegistration

Value Meaning
0 None β€” a non-waitlist registration, or a row created before this column was added
1 Auto promotion
2 Manual staff confirm
3 Guest confirm

lead_origin and field_origin β€” AI Lead Manager

Applies to lead_origin on Bi_FactAilmLeadLevel and Bi_FactAilmMultiTouchLevel, and to field_origin on Bi_FactAilmLeadCustomFieldValues.

Code Meaning Code Meaning
0 Manual 12 DataIOZenoti
1 MetaAds 13 Webstore
2 GoogleAds 14 SmartBotSms
3 Zenoti 15 SmartBotVoice
4 API 16 SmartBotWeb
5 CustomForms 17 HyperConnectVoice
6 HyperConnect 18 HyperConnectSms
7 SmartBot 19 HyperConnectWhatsApp
8 Zenoti_MembershipExpiry 20 AiReceptionist
9 Zenoti_PackageExpiry 21 ManualDataImport
10 Zapier 22 AiRetentionManager
11 DataIO

Important: 0 is ambiguous. It means a lead entered manually, and it is also the value stored when the origin name can't be read. Don't treat 0 as proof of manual entry.

action_source and task_source β€” AI Lead Manager

Code Meaning
0 Employee
1 System, including stage rules
2 Workflow
3 HyperConnect
4 ManualDataImport
5 AI Receptionist call

lead_status β€” Bi_FactAilmLeadLevel

Code Meaning
0 Active
1 Duplicate
2 Merged
3 Attached

Filter lead_status = 0 to count pipeline leads.

status β€” Bi_FactAilmGuestLinkingLevel

Code Meaning
0 NotStarted
1 Queued
2 LinkedSuccessful
3 Failed
5 GuestIdUnverifiable

4 isn't used.

mode and mode_before_last_stage β€” Bi_FactAilmGuestLinkingLevel

These columns are a bit field, not a simple code. Values combine by addition, and nothing in the column type shows it.

Bit Meaning
0 NotLinked
1 LinkedNewGuest
2 LinkedExistingGuest
4 LinkedManually
8 Deleted
16 InLastStage
32 DeletedLinkedNew
64 DeletedLinkedExisting
128 DeletedLinkedManual

Test bits rather than matching whole values. For example, 18 means the lead is linked to an existing guest (2) and is in the last stage (16).

In Analytics β€” Redshift Data Share, the guest_link_method enum in Bi_DimAilmEnumMapping lists 0, 1, 2, 4, 8, 32, 64 and 128 and has no entry for 16.

mode_before_last_stage uses the same values as mode.

Important: If you treat mode as a simple code, combined values such as 18 won't match any single listed value. To find Won and Lost leads, use stage_type on Bi_DimAilmLeadStages, not mode.

stage_type β€” Bi_DimAilmLeadStages

Code Meaning
0 Default
1 Initial
2 Won
3 Lost

source_type β€” Bi_DimAilmLeadSources

Code Meaning Code Meaning
0 Default β€” custom source 7 AIReceptionist
1 SmartBotVoice 8 Zenoti
2 SmartBotSMS 9 WebstoreAbandonedCart
3 SmartBotWeb 10 AIRetentionManager
4 HyperconnectVoice 11 HyperconnectVoiceInbound
5 HyperconnectSMS 12 HyperconnectVoiceMissed
6 HyperconnectWhatsapp

lead_type_category β€” Bi_DimAilmLeadTypes

Code Meaning
0 Default
1 GeneralInquiry

form_type β€” Bi_DimAilmFormFields

Code Meaning
0 LeadCapture β€” a shareable capture form
1 LeadDetailsCollection β€” the organization's Master Form

Values in Bi_FactAilmLeadCustomFieldValues point at Master Form fields. Filter form_type = 1 when you join for lead detail.

platform β€” Bi_DimAilmAdCampaigns

Code Meaning
1 Meta
2 Google
3 TikTok

void β€” Bi_FactEmployeeKPI and Bi_FactEmpKPIRank

void isn't a code, but it's often read as one and read wrongly on these two tables.

Value Meaning
FALSE Current β€” the active row for that week and center
TRUE Soft-deleted by a weekly rebuild β€” not removed by a user

void is the soft-delete flag here, as it is across the warehouse. For these tables, the weekly rebuild is what sets it.

Fixed-value text columns

These columns already store the label, so no decoding is needed. They're listed so you know the full set to expect.

Column Table Values
sentiment Bi_Facthcvoicecalllog Positive, Negative, Neutral
call_category Bi_Facthcvoicecalllog appointment, purchases, others
transferred_type Bi_Facthcvoicecalllog agent, center, external
transcription_status Bi_Facthcvoicecalllog success, failed, inProgress, notApplicable, deleted
analysis_status Bi_Facthcvoicecalllog success, failure, pending, completed, failed, notApplicable
review_source Bi_Facthcreputereviewstore google, facebook, ZENOTI
product Bi_Facthchyperconnectusages voice, air, connect, connect-whatsapp, smartbot, voice-international, and others
uom Bi_Facthchyperconnectusages Mins, SMS, Message, and others
status, effective_status Bi_DimAilmAdCampaigns Platform values such as ACTIVE, PAUSED

analysis_status, call_subcategory and recording_status are open sets, and new values can appear without notice. To find calls with an analysis available, match on success instead of listing every failure value.

transferred_type_id pairs with transferred_type. It holds an agent ID, a center ID or a phone number, matching the value in transferred_type.

Not yet published

Zenoti hasn't published the meaning of these values yet:

  • The values 1 to 4 in missed_call_category on Bi_Facthcvoicecalllog.
  • The values in MembershipCreditBenefitsType on Bi_DimMembership and Bi_DimMembershipUser.
  • Values of valuetype on Bi_DimSmartTargetsCategoryMetrics other than 1 (Monetary), which is used on all Sales and Collections metrics.

Don't build reporting on guessed labels for these columns. This article will be updated when the meanings are confirmed.

Tips

  • Join to dimensions where one exists. For stages, sources, lead types and form fields, join to the dimension table instead of hard-coding the code, because new rows are added over time.
  • Filter pipeline counts. When you count leads in a pipeline, filter lead_status = 0 on Bi_FactAilmLeadLevel. See AI Lead Manager data tables.
  • Handle both boolean styles. On the call log, voice_mail_received can arrive as "1" and "0" or as "True" and "False". Handle both.
Was this article helpful?
Your feedback helps us improve our documentation.