ZenotiHelp

AI Lead Manager data tables

ZTZenoti Docs Team Updated November 11, 2026 7 min readGuide

Thirteen AI Lead Manager tables are part of the analytics data export: five fact tables and eight dimension tables. They cover leads, follow-up tasks, guest linking, custom form answers and marketing attribution. In Analytics β€” Redshift Data Share there is also one reference table, Bi_DimAilmEnumMapping, which decodes the coded columns, so the Redshift Data Share has fourteen. This article describes what each table holds and the points that most often cause wrong numbers.

Audience: Data engineers and BI developers who report on AI Lead Manager data.


On this page


How these tables behave

These conventions differ from the rest of the data share. Read them first.

  • Snapshot, not delta. Each table holds one row per record per snapshot_date, for every day the record changed. To get the current state, take the latest snapshot_date for each key.
  • Daily export, previous day. These tables don't follow the 1–2 hour cycle used by most tables.
  • Timestamps are text. Every *_utc and *_center column is VARCHAR(64). The _utc column holds the UTC instant. The _center column holds the same instant in the center's local time. Cast the value before you compare it or do arithmetic on it.
  • Three delete signals, not one:
    • source_deleted β€” the record was deleted upstream.
    • source_void β€” the record was voided upstream.
    • void β€” the warehouse soft-delete flag. A row with void set is a soft-deleted row.
  • The latest snapshot decides. The latest snapshot_date for each key is the current state of that record. In the samples checked, void, source_deleted and source_void were false on every lead row, but still read all three flags rather than assuming a record is live.

All thirteen export tables include these columns: snapshot_date, organizationid, is_active, source_deleted, source_void, void, export_run_id, podname, groupid and etlcreateddate. Bi_DimAilmEnumMapping is a code list, not a snapshot table, so it doesn't have these columns. In Analytics β€” Redshift Data Share, each of the thirteen tables also has a surrogate key that ends in wid.

For the differences between Analytics β€” Amazon S3 and Analytics β€” Redshift Data Share, see Read the analytics data export.

Fact tables

Table One row per What it holds
Bi_FactAilmLeadLevel Lead per snapshot date The state of every lead in the pipeline: stage, source, type, priority, owner, pricing and lifecycle dates
Bi_FactAilmTaskLevel Lead task per snapshot date Follow-up tasks raised against leads
Bi_FactAilmGuestLinkingLevel Lead contact per snapshot date Whether and how a lead contact was matched to a Zenoti guest record
Bi_FactAilmLeadCustomFieldValues Captured field value Answers to custom lead-capture forms
Bi_FactAilmMultiTouchLevel Marketing touch per snapshot date UTM, campaign and device context for multi-touch attribution

Columns that need explaining

Table Column What to know
Bi_FactAilmLeadLevel lead_status Filter to 0 to count pipeline leads. 0 is Active, 1 Duplicate, 2 Merged and 3 Attached. Rows with any other value stay in the table and inflate a pipeline count if you leave them in.
lead_origin See the column value reference.
action_source See the column value reference.
disposition_id The outcome recorded when a call ended. It isn't a Won or Lost reason. No dimension table for it ships in this release.
centerid (centerwid in Analytics β€” Redshift Data Share) Can be null. Leads can belong to an organization or a zone instead of a center.
latest_note_id Identifies the latest note. The note text isn't exported.
Bi_FactAilmTaskLevel task_source Uses the same codes as action_source.
origin_lead_id The lead the task was first raised on, when the task moved with a lead merge.
Bi_FactAilmGuestLinkingLevel status See the column value reference.
mode, mode_before_last_stage A bit field, not a simple code. See below.
guestid (guestwid in Analytics β€” Redshift Data Share) Null until the lead is linked to a guest.
Bi_FactAilmLeadCustomFieldValues field_value JSON. The shape depends on the field type. See below.
field_origin Uses the same codes as lead_origin.
Bi_FactAilmMultiTouchLevel lead_id Can be null. A touch can be recorded before a lead exists.
compliance_timestamp Records when consent was captured. It doesn't say what was consented to.

Read mode as a bit field

mode and mode_before_last_stage combine values by addition. The bits are 0, 1, 2, 4, 8, 16, 32, 64 and 128. For example, 18 is 2 (LinkedExistingGuest) plus 16 (InLastStage). Test each bit instead of matching whole values, because a combined value such as 18 doesn't match any single listed value. 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. The full list is in the column value reference.

To find Won and Lost leads, don't use mode. Use stage_type on Bi_DimAilmLeadStages, and decode it through Bi_DimAilmEnumMapping in Analytics β€” Redshift Data Share or the column value reference.

Read field_value

field_value is JSON. Two shapes appear in the data for fields that hold a typed value. In the samples checked, an object with a value key was on 297 of 297 rows in one sample and 8,314 of 9,543 rows in the other. An object with a Number key and no value key was on 1,229 rows in the second sample.

Shape Example
An object with a value key. This is the most common shape. {"value": "..."}
An object with a Number key and no value key. This is rarer. {"Number": "..."}

For dropdown, radio and checkbox fields, field_value holds option IDs. Resolve them against Bi_DimAilmFormFieldOptions.id. Read the key that is present rather than assuming value exists on every row.

Decode coded columns with the enum reference table

In Analytics β€” Redshift Data Share, Bi_DimAilmEnumMapping is a separate reference table. It is a code list, not a snapshot. Its columns are enum_name, code, label, is_legacy and sort_order. It has no snapshot_date, void or other common columns. Filter it by enum_name, then join on code.

enum_name Decodes
lead_status lead_status on Bi_FactAilmLeadLevel
lead_origin lead_origin and field_origin
action_source action_source and task_source
guest_link_status status on Bi_FactAilmGuestLinkingLevel
guest_link_method mode and mode_before_last_stage on Bi_FactAilmGuestLinkingLevel
stage_type stage_type on Bi_DimAilmLeadStages
source_type source_type on Bi_DimAilmLeadSources
lead_type_category lead_type_category on Bi_DimAilmLeadTypes
ad_platform platform on Bi_DimAilmAdCampaigns
form_type form_type on Bi_DimAilmFormFields

Dimension tables

Each dimension table has an id column (VARCHAR(36)) that the fact tables join to. Each also has created_on_utc and updated_on_utc, plus the common columns listed above.

Table What it holds Coded column
Bi_DimAilmLeadStages Pipeline stages: name, code, display order, color stage_type
Bi_DimAilmLeadSources Lead sources: name, code, color source_type
Bi_DimAilmLeadTypes Lead types: name, code lead_type_category
Bi_DimAilmLeadPriorityTypes Priorities: Highest, High, Medium, Low, Lowest level (see below)
Bi_DimAilmLeadTaskTypes Task types: name, code, description None
Bi_DimAilmFormFields Custom form field definitions form_type
Bi_DimAilmFormFieldOptions Dropdown and choice options None
Bi_DimAilmAdCampaigns Advertising campaigns platform

The coded columns are decoded in the column value reference.

  • level on Bi_DimAilmLeadPriorityTypes isn't a code. It's a sort rank, and a lower number means a higher priority.
  • previous_field_id on Bi_DimAilmFormFields is a linked list. Follow the chain to rebuild the display order. There's no single ordering column.
  • Bi_DimAilmFormFieldOptions.id appears inside field_value for dropdown, radio and checkbox answers.
  • Bi_DimAilmAdCampaigns belongs to the organization. It has no center column. For center-level or zone-level S3 subscriptions, it's copied into every bucket. status and effective_status can legitimately differ. For example, a campaign can be ACTIVE while its ad account is disabled.

Handle personal data

  • Bi_FactAilmGuestLinkingLevel and Bi_FactAilmLeadLevel contain no names, email addresses, phone numbers, dates of birth or consent fields. Lead names and free-text fields aren't exported.
  • Bi_FactAilmGuestLinkingLevel includes user_code, which identifies the linked person. Treat it as an identifier and apply your own handling rules.
  • field_value can contain whatever a guest typed into a form, including names, phone numbers and email addresses. Treat it as sensitive in your environment and apply your own handling rules.
  • Bi_FactAilmMultiTouchLevel includes online identifiers: ip, user_agent, fbclid, fbc, fbp, gbraid, wbraid and ga_client_id. Apply your own retention and handling rules. compliance_timestamp doesn't carry a consent status, so it can't show on its own whether a touch was consented to.
  • Touches that have no center recorded aren't exported, so multi-touch data can be incomplete.

Tips

  • Take the latest snapshot per key before you join facts to dimensions.
  • Filter on lead_status = 0 for pipeline counts.
  • Join dimensions to facts on id. In Analytics β€” Redshift Data Share, the joins from Bi_FactAilmLeadLevel to the stage, source, lead type and priority dimensions use id plus organizationid. The fact tables carry the dimension id in columns such as source_id, stage_id, lead_type_id and priority_type_id. Join form field options through field_value.
  • Cast text timestamps once, in a view or a staging step, so every report uses the same conversion.

Troubleshooting

Pipeline counts are higher than expected. Check that you filter lead_status = 0 and use the latest snapshot_date for each lead.

Guest-linking counts are wrong. You're probably matching mode as a single value. Test bits instead.

You can't tell which leads are Won or Lost. Don't use mode. Use stage_type on Bi_DimAilmLeadStages, decoded through Bi_DimAilmEnumMapping.

A guest ID is empty. guestid stays null until the lead contact is linked to a guest.

The latest day looks short. These tables are bucketed by center-local day but exported on a UTC schedule. Check again after the next daily run.

Was this article helpful?
Your feedback helps us improve our documentation.