Redshift Data Share Tables for HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms
This article lists the HyperConnect, Challenges, AI Lead Manager, Smart Targets, Employee KPI and custom forms tables and columns in the Redshift Data Share, and what is different about them. Table, column and code definitions are in the Analytics — Amazon S3 reference, because the same tables are documented once for both delivery modes.
You don't run any DDL. Zenoti applies schema changes to the cluster as part of its releases. See Handle schema changes and upgrades. If your queries use explicit column lists, review them against the tables below.
For what each table and column holds, see HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms Tables. The table pages are in the Analytics — Amazon S3 table reference.
Audience: Analytics engineers and BI developers who maintain queries, reports or pipelines against Zenoti's Redshift Data Share.
On this page
How the tables differ
AI Lead Manager tables are snapshots. Each table has one row per record per snapshot_date. To get the current state, take the latest snapshot_date for each key. They're exported daily for the previous day. Every *_utc and *_center timestamp is text, so cast it before you compare or calculate. There are three delete signals: source_deleted, source_void and void. In the Redshift Data Share, Bi_FactAilmGuestLinkingLevel has guestwid and Bi_FactAilmLeadLevel has centerwid. Bi_DimAilmEnumMapping is a code list, not a snapshot table. Its columns are enum_name, code, label, is_legacy and sort_order, and it has none of the common columns. Use it to decode the coded columns. Join the dimension tables on id. The fact tables carry the dimension id in columns such as source_id, stage_id, lead_type_id and priority_type_id. See AI Lead Manager data tables.
Keys use the wid columns. For example, the center key is centerwid, the guest key is guestwid and the employee key is employeewid. In Analytics — Amazon S3 the same keys are centerid, guestid and employeeid. The full list is under Key columns in S3 and Redshift in HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms Tables. The joins from Bi_FactAilmLeadLevel to the stage, source, lead type and priority dimensions use id plus organizationid. UILabel on Bi_DimFormsMetaData is VARCHAR(2048).
HyperConnect tables are daily and mostly text. Timestamps are epoch milliseconds. Numbers and booleans arrive as text, including call_type (values 0, 1, 2 and null) and review_status. Deleted rows (is_deleted) stay in the table. Count call volume on parent_call_sid.
void on the Employee KPI tables means the row is soft-deleted. Scores are rebuilt weekly, and each rebuild soft-deletes the previous rows. Filter void = FALSE and take the latest rankdate for each center.
Coded columns can gain values. Don't reject a code you don't recognize. Meanings are in Analytics data export column value reference. That reference applies to Redshift Data Share as well as S3, and it's the only place the codes are listed.
Differences from Amazon S3
In Redshift Data Share:
- Table names have no
_s3suffix. For example, the table isBi_FactAilmLeadLevel, notBi_FactAilmLeadLevel_s3. - Each table has a
BIGINT IDENTITYsurrogate key that ends inwid, such asfactailmleadlevelwid. - Center, employee and guest references are
centerwid,employeewidandguestwid. They join the dimension tables. In Redshift Data Share, use thewidkeys and column names. In Amazon S3, use theidfield only. Where a Redshift table has nowidlink to the table it joins, the join stays on the natural key. For example,Bi_DimChallengeLevelMetricsjoins onChallengeID, and the Smart Targets fact table joins to the category dimension ontargetmetricid. - Some text columns are wider than in S3.
For the full comparison, see Read the analytics data export.
Tips
- Create a view that picks the latest snapshot for each AI Lead Manager table, so reports don't repeat the logic.
- Filter
lead_status = 0onBi_FactAilmLeadLevelwhen you count leads in a pipeline. - Check the refresh log first if a recent day looks short. See Data freshness and refresh cycles.
Troubleshooting
A new table or column isn't visible yet. Zenoti applies changes during its release. Check again after the release reaches your cluster. If it's still missing, contact your Zenoti account representative.
A table is empty. Feature tables, such as the HyperConnect and challenge tables, hold rows only for organizations and centers that use the feature.
Employee KPI rows are duplicated for a week.
Filter void = FALSE.