AI Lead Manager data tables
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
- Fact tables
- Decode coded columns with the enum reference table
- Dimension tables
- Handle personal data
- Tips
- Troubleshooting
- Related articles
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 latestsnapshot_datefor each key. - Daily export, previous day. These tables don't follow the 1β2 hour cycle used by most tables.
- Timestamps are text. Every
*_utcand*_centercolumn isVARCHAR(64). The_utccolumn holds the UTC instant. The_centercolumn 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 withvoidset is a soft-deleted row.
- The latest snapshot decides. The latest
snapshot_datefor each key is the current state of that record. In the samples checked,void,source_deletedandsource_voidwere 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.
levelonBi_DimAilmLeadPriorityTypesisn't a code. It's a sort rank, and a lower number means a higher priority.previous_field_idonBi_DimAilmFormFieldsis a linked list. Follow the chain to rebuild the display order. There's no single ordering column.Bi_DimAilmFormFieldOptions.idappears insidefield_valuefor dropdown, radio and checkbox answers.Bi_DimAilmAdCampaignsbelongs to the organization. It has no center column. For center-level or zone-level S3 subscriptions, it's copied into every bucket.statusandeffective_statuscan legitimately differ. For example, a campaign can beACTIVEwhile its ad account is disabled.
Handle personal data
Bi_FactAilmGuestLinkingLevelandBi_FactAilmLeadLevelcontain no names, email addresses, phone numbers, dates of birth or consent fields. Lead names and free-text fields aren't exported.Bi_FactAilmGuestLinkingLevelincludesuser_code, which identifies the linked person. Treat it as an identifier and apply your own handling rules.field_valuecan 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_FactAilmMultiTouchLevelincludes online identifiers:ip,user_agent,fbclid,fbc,fbp,gbraid,wbraidandga_client_id. Apply your own retention and handling rules.compliance_timestampdoesn'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 = 0for pipeline counts. - Join dimensions to facts on
id. In Analytics β Redshift Data Share, the joins fromBi_FactAilmLeadLevelto the stage, source, lead type and priority dimensions useidplusorganizationid. The fact tables carry the dimensionidin columns such assource_id,stage_id,lead_type_idandpriority_type_id. Join form field options throughfield_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.