Analytics data export column value reference
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
_s3to 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:
ChallengeTypeis onBi_DimChallenges, notBi_FactChallenges.Bi_FactChallengeshasLevelNum, which holds the tier a guest enrolled at. A nullLevelNummeans 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:
0is ambiguous. It means a lead entered manually, and it is also the value stored when the origin name can't be read. Don't treat0as 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
modeas a simple code, combined values such as18won't match any single listed value. To find Won and Lost leads, usestage_typeonBi_DimAilmLeadStages, notmode.
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 | |
| 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
1to4inmissed_call_categoryonBi_Facthcvoicecalllog. - The values in
MembershipCreditBenefitsTypeonBi_DimMembershipandBi_DimMembershipUser. - Values of
valuetypeonBi_DimSmartTargetsCategoryMetricsother than1(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 = 0onBi_FactAilmLeadLevel. See AI Lead Manager data tables. - Handle both boolean styles. On the call log,
voice_mail_receivedcan arrive as"1"and"0"or as"True"and"False". Handle both.