ZenotiHelp

HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms Tables

ZTZenoti Docs Team Updated November 11, 2026 8 min readGuide

This article lists the tables and columns in the analytics data export for HyperConnect, Challenges, class registrations and payroll, AI Lead Manager, Smart Targets, Employee KPI and custom forms. Each section links to its table reference. For how to read these tables, see Read the analytics data export.

Audience: Data engineers and BI developers who maintain queries and downstream tables for the analytics data export.


On this page


Before you begin

  • Table names in this article have no suffix, as in Analytics β€” Redshift Data Share. In Analytics β€” Amazon S3, add _s3 to the table name.
  • S3 files have no header row. Apply the schema upgrade scripts from your SchemaUpgrades folder before you consume data for an affected table. See Handle schema changes and upgrades.
  • Tables for a feature appear only for organizations that use that feature.
  • Key naming. Analytics β€” Amazon S3 keeps the source ID columns, such as centerid, guestid and employeeid. Analytics β€” Redshift Data Share uses the wid columns for the same keys. The table under Key columns in S3 and Redshift lists each difference. Where a Redshift table has no wid link to the table it joins, the join stays on the natural key. These cases are listed under Challenges, AI Lead Manager and Smart Targets.

Key columns in S3 and Redshift

Table Analytics β€” Amazon S3 Analytics β€” Redshift Data Share
Bi_FactAilmGuestLinkingLevel guestid, centerid guestwid, centerwid
Bi_FactAilmLeadLevel, Bi_FactAilmTaskLevel, Bi_FactAilmMultiTouchLevel centerid centerwid
Bi_Facthcvoicecalllog centerid, guestid, employeeid centerwid, guestwid, employeewid
Bi_Facthcvoicecallmetrics centerid centerwid
Bi_Facthchyperconnectusages centerid centerwid
Bi_Facthcreputereviewstore centerid centerwid
Bi_FactTargetKPIMonthCenter, Bi_FactTargetKPIMonthCenterHist CenterId centerwid
Bi_FactEmployeeKPI, Bi_FactEmpKPIRank CenterId, EmployeeId centerwid, employeewid
Bi_FactChallenges ChallengeID challengewid (challengeid isn't on the Redshift fact table)

The center key on the metrics table is centerid in S3 and centerwid in Redshift. Neither uses center_id.

HyperConnect, Challenges and column additions

HyperConnect, AI Receptionist and Reviews

Four tables carry voice call, usage and review data.

Table One row per Key
Bi_Facthcvoicecalllog Call leg call_sid
Bi_Facthcvoicecallmetrics Center per day centerid and record_date
Bi_Facthchyperconnectusages Organization, center, product and day org_center_id_product and usage_date
Bi_Facthcreputereviewstore Review review_id

Points to know:

  • Count center call volume on parent_call_sid. Transfer and bot legs have a suffix on call_sid, such as CA123_bot. parent_call_sid is the call_sid without it.
  • transcript_text is empty. The column exists in every call-log file, but it isn't populated. In the samples checked, call_notes was empty on 166,544 of 168,598 calls and the AI analysis column (openai_analysis) was empty on 81,059. Don't build reports that depend on either column being filled.
  • record_date is a number, not a date. It's a bigint in yyyymmdd form, for example 20260907.
  • Minutes and seconds are counted separately. call_duration_in_mins and the *_in_mins and *_minutes columns on the metrics table are calculated independently from their seconds columns. Small differences are expected. Use one or the other and don't derive one from the other.
  • No row means no usage. A center with no usage of a product has no row for that product that day. It doesn't have a zero row.
  • Deleted rows aren't removed. Filter on is_deleted in the call log and on review_status in the reviews table.

See the table references: HyperConnect Voice Call Log, HyperConnect Voice Call Metrics, HyperConnect Usages and HyperConnect Repute Review Store.

Challenges

Bi_DimChallengeLevelMetrics holds one row per challenge, metric and level, and it's the source of truth for challenge targets. Don't read a single target from the challenge row.

Two columns support challenge levels:

Table Column What it holds
Bi_DimChallenges ChallengeType Standard, multi-metric or multi-tier. Existing challenges are set to Standard.
Bi_FactChallenges LevelNum The tier the guest enrolled at. It's null on rows created before multi-tier challenges. Treat null as Level 1.

To join an enrollment to its target in Analytics β€” Redshift Data Share, use the surrogate keys:

SELECT  fc.ChallengeUserID,
        dc.ChallengeName,
        dc.ChallengeType,
        COALESCE(fc.LevelNum, 1)  AS level_num,
        lm.LevelName,
        lm.TargetCount
FROM        Bi_FactChallenges            fc
JOIN        Bi_DimChallenges             dc ON dc.dimchallangeswid = fc.ChallengeWID
LEFT JOIN   Bi_DimChallengeLevelMetrics  lm ON lm.ChallengeID = dc.ChallengeID
                                           AND lm.LevelNum    = COALESCE(fc.LevelNum, 1)
                                           AND lm.Void        = FALSE
WHERE   fc.Void = FALSE;

In Analytics β€” Amazon S3, tables have no wid keys, so join on the challenge GUID instead:

SELECT  fc.ChallengeUserID,
        dc.ChallengeName,
        dc.ChallengeType,
        COALESCE(fc.LevelNum, 1)  AS level_num,
        lm.LevelName,
        lm.TargetCount
FROM        Bi_FactChallenges_s3            fc
JOIN        Bi_DimChallenges_s3             dc ON dc.ChallengeID = fc.ChallengeID
LEFT JOIN   Bi_DimChallengeLevelMetrics_s3  lm ON lm.ChallengeID = fc.ChallengeID
                                              AND lm.LevelNum    = COALESCE(fc.LevelNum, 1)
                                              AND lm.Void        = FALSE
WHERE   fc.Void = FALSE;

Keep lm.Void = FALSE in the LEFT JOIN ... ON clause, not in the WHERE clause. In the WHERE clause it removes enrollments that have no level row. The join from Bi_DimChallengeLevelMetrics stays on ChallengeID, because that table has no wid link to the challenge.

For a multi-metric challenge, either query returns one row per metric, because the challenge has several targets at the same level.

In the samples checked, Bi_DimChallengeLevelMetrics, Bi_DimChallenges and Bi_FactChallenges had zero rows. These tables hold rows only for organizations that use challenges.

See Challenge Level Metrics Dimension Table and Challenges Dimension Table.

Note: ChallengeType is on Bi_DimChallenges, not on Bi_FactChallenges. Some earlier release material showed it on the fact table. The dimension table is correct.

Payroll and class registration columns

Table Column What it holds
Bi_FactEmployeePay InstructorPay The instructor pay component on the employee pay row
Bi_FactRegistration WaitlistPromotionSource How a waitlisted registration was promoted. See the column value reference.

AI Lead Manager, Smart Targets, Employee KPI and custom forms

AI Lead Manager

Thirteen export tables: five facts and eight dimensions. They're snapshots with text timestamps, they export daily, and they have three delete signals. Analytics β€” Redshift Data Share also has Bi_DimAilmEnumMapping, a reference table that decodes the coded columns, so it has fourteen. It isn't a snapshot table and has none of the common columns.

In Analytics β€” Redshift Data Share, Bi_FactAilmGuestLinkingLevel has guestwid and Bi_FactAilmLeadLevel has centerwid. The S3 files keep guestid and centerid. Join the dimension tables on id. In Redshift, 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.

See AI Lead Manager data tables.

Smart Targets

Three tables support the targets that you set per metric at monthly, daily or weekly level, for a center, zone or organization.

Table What it holds
Bi_DimSmartTargetsCategoryMetrics The catalog of target categories and metrics. One row per target metric.
Bi_FactTargetKPIMonthCenter The current target value per center, metric and target period.
Bi_FactTargetKPIMonthCenterHist A historical archive of the center-level monthly targets, with the same columns as Bi_FactTargetKPIMonthCenter.
  • Join to the dimension table for targetmetricid and targetcategoryid instead of hard-coding IDs, because new metrics and categories are added. The join from the fact table to the category dimension stays on targetmetricid, because the fact table has no wid link to the dimension. The seven categories are 1 Sales, 2 Collections, 3 Provider utilization, 4 Guest feedback, 5 Booking source, 6 Rebooking and 7 Add-ons.
  • targetdate is a timestamp, and it holds day-level values. Every value is at midnight. The table name says month, but don't expect one row per month.
  • targetgroupid groups targets that were saved together in one action. One save can span many months and centers.

Employee KPI

Two tables hold weekly technician KPI credits and the resulting ranking.

Table One row per
Bi_FactEmployeeKPI Center, employee, business unit and week
Bi_FactEmpKPIRank Center, employee, business unit and rank date

A technician who works in two business units has two rows per week, one for each unit. The business unit is in businessunitid and businessunitname. In the samples checked, both tables had zero rows.

Column Definition
opportunities Distinct guest, day, technician and business unit visits. The same guest seen twice in a day by the same technician counts once.
treatmentcredits Services in the Treatments subcategory within the Hair category. Hair business unit only.
upsellcredits Opportunities where the final invoice total exceeded the check-in total by more than the business unit's threshold. At most one credit per invoice per technician.
prebookcredits Opportunities where the same guest and technician have a future service booked on or before the visit date. No limit on how far ahead.
retailcredits Opportunities that earned a retail credit.
retailtotal Net retail value for the week. Used as the ranking tie-breaker.
isnewemployee True when the technician's chair start date is within one year of weekstartdate.

Three points give wrong numbers if missed:

  1. void = TRUE means the row is soft-deleted. Scores are rebuilt weekly. When a week is rebuilt for a center, the previous rows are soft-deleted (marked void) and a new set is added. Filter void = FALSE and take the latest rankdate for each center.

  2. computedscore isn't comparable across business units. It's on Bi_FactEmpKPIRank only, not on Bi_FactEmployeeKPI. The score is calculated differently:

    Business unit Score
    Hair (Treatment + Prebook + Retail credits) Γ· Total opportunities
    Nails, Aesthetics/Wax, Massage (Upsell + Prebook + Retail credits) Γ· Total opportunities

    Don't average, compare or rank computedscore across business units. emprank is meaningful only within one organization, center and business unit. Rank order is technicians who aren't new first, then computedscore descending, then retailtotal descending.

  3. Prebook isn't Rebook. In Zenoti, Rebook means a future appointment booked on the same calendar day. Prebook here means created on or before the visit date, with no date limit and no business unit check. A gap between prebookcredits and a rebook report isn't an error.

Custom forms

Bi_DimFormsMetaData has a UILabel column. It's NVARCHAR(2048) in Analytics β€” Amazon S3 and VARCHAR(2048) in Analytics β€” Redshift Data Share. It holds the label a user sees for a custom form field. fieldname still holds the internal name.

  • UILabel is read from the stored form HTML. If the markup is unusual, the value can be empty or imperfect. Most labels are empty today (571,491 of 571,919 rows in one sample), so join on fieldname, which remains the reliable key.
  • Zenoti may reprocess existing data in full to fill UILabel. If it does, you'll see a one-time re-export of form metadata history, and row counts change.

See Forms Meta Data Dimension Table.

Tips

  • Apply schema changes first. Because S3 files have no header row, a column that arrives before your table is ready causes misaligned data without an error.
  • Use the column value reference for every coded column, and join to a dimension table where one exists.
  • Plan for unknown codes. New values can be added to any coded column.

Troubleshooting

Employee KPI rows appear twice for the same week. Filter void = FALSE. The earlier rows were soft-deleted (marked void) by a weekly rebuild.

Smart Targets rows don't match a month total. targetdate is day-level. Aggregate by month if you need monthly totals.

UILabel is empty for many fields. The label is read from form markup, and most labels are empty today. Use fieldname.

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