HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms Tables
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
- HyperConnect, Challenges and column additions
- AI Lead Manager, Smart Targets, Employee KPI and custom forms
- Tips
- Troubleshooting
- Related articles
Before you begin
- Table names in this article have no suffix, as in Analytics β Redshift Data Share. In Analytics β Amazon S3, add
_s3to the table name. - S3 files have no header row. Apply the schema upgrade scripts from your
SchemaUpgradesfolder 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,guestidandemployeeid. Analytics β Redshift Data Share uses thewidcolumns for the same keys. The table under Key columns in S3 and Redshift lists each difference. Where a Redshift table has nowidlink 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 oncall_sid, such asCA123_bot.parent_call_sidis thecall_sidwithout it. transcript_textis empty. The column exists in every call-log file, but it isn't populated. In the samples checked,call_noteswas 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_dateis a number, not a date. It's abigintinyyyymmddform, for example20260907.- Minutes and seconds are counted separately.
call_duration_in_minsand the*_in_minsand*_minutescolumns 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_deletedin the call log and onreview_statusin 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:
ChallengeTypeis onBi_DimChallenges, not onBi_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
targetmetricidandtargetcategoryidinstead of hard-coding IDs, because new metrics and categories are added. The join from the fact table to the category dimension stays ontargetmetricid, because the fact table has nowidlink to the dimension. The seven categories are1Sales,2Collections,3Provider utilization,4Guest feedback,5Booking source,6Rebooking and7Add-ons. targetdateis 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.targetgroupidgroups 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:
void = TRUEmeans 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. Filtervoid = FALSEand take the latestrankdatefor each center.computedscoreisn't comparable across business units. It's onBi_FactEmpKPIRankonly, not onBi_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
computedscoreacross business units.emprankis meaningful only within one organization, center and business unit. Rank order is technicians who aren't new first, thencomputedscoredescending, thenretailtotaldescending.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
prebookcreditsand 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.
UILabelis 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 onfieldname, 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.