ZenotiHelp

Read the analytics data export

ZTZenoti Docs Team Updated November 11, 2026 4 min readGuide

Most tables in the analytics data export follow one set of rules. The HyperConnect and AI Lead Manager tables follow different ones, and assuming the usual behavior gives wrong numbers. This article lists the rules that apply everywhere, the tables that differ, and how Analytics β€” Amazon S3 differs from Analytics β€” Redshift Data Share.

Audience: Data engineers and BI developers who load and query the analytics data export.


On this page


Rules that apply to every table

  1. Upsert, don't append. A row can appear in several runs. Key on the table's primary key and keep the latest version.
  2. A missing day isn't a deletion. Each run carries only what changed in its window. A row that's absent from a later run doesn't mean the row was deleted.
  3. Unknown codes will appear. New values are added to coded columns over time. Existing values are never reassigned a different meaning, so don't reject a code you don't recognize. Meanings are in Analytics data export column value reference.

Where the table families differ

Most tables HyperConnect tables AI Lead Manager tables
Export basis Changes only Changes only, and rows can be re-exported Snapshot β€” one row per record per day
Frequency Every 1–2 hours Daily Daily, for the previous day
Day boundary Center-local Center-local day, exported on a UTC schedule Center-local for facts, UTC for lookups
Timestamps Native TIMESTAMP Epoch milliseconds Text (VARCHAR) β€” cast before use
Numbers and booleans Native types Delivered as text Mixed
Delete signal void is_deleted, not filtered out source_deleted, source_void and void

For details on each family, see HyperConnect, AI Lead Manager, Smart Targets, Employee KPI, Challenges and Custom Forms Tables and AI Lead Manager data tables.

Differences between S3 and Redshift

Analytics β€” Redshift Data Share Analytics β€” Amazon S3
Table names Bi_<Name> Bi_<Name>_s3
Primary key BIGINT IDENTITY surrogate key, such as factailmleadlevelwid None. Natural GUID keys only
Center reference centerwid, which joins Bi_DimCenter centerid (VARCHAR(36))
Employee reference employeewid employeeid (VARCHAR(36))
Guest reference guestwid guestid (VARCHAR(36))
Column widths Wider on several text columns Narrower on categoryname, metricsname, maxvalue, podname and businessunitname

Important: S3 files have no header row. The column order in the schema file identifies each value, so don't reorder columns when you build target tables. The current schema files are in the SchemaUpgrades folder in your bucket. That folder is the authoritative definition, not a release presentation.

Columns that need care

Column What to know
parent_call_sid The call ID without the transfer or bot suffix. Count call volume on this column.
center_timestamp Epoch milliseconds on the center-local clock. Filter and partition on this column.
is_deleted Soft-delete flag on the call log. Deleted rows aren't removed from the export.
snapshot_date The day a snapshot row was taken in the AI Lead Manager tables. Take the latest per key.
source_deleted, source_void, void The three delete signals on the AI Lead Manager tables.
organizationid Identifies the organization. Use it instead of the pod name.

Read the data correctly

  1. Mind the day boundary. The daily run exports the previous UTC day, but call rows and AI Lead Manager facts are bucketed by the center-local clock. For centers behind UTC, the most recent day can look incomplete until the next run catches up. If a day looks short, check it again after the next run before you raise it.
  2. Cast HyperConnect values. Numbers and booleans arrive as text. Nulls are real nulls, not empty strings or "None".
  3. Cast AI Lead Manager timestamps. The _utc and _center columns hold the same instant in two time zones.
  4. Parse nested values as JSON. usage_metadata, component_api_response, call_volume_hourly_breakdown, the review reply, field_value, field_position and field_config_json are JSON-encoded.
  5. Count calls on parent_call_sid. Counting call_sid counts transfer and bot legs separately and overstates volume.
  6. Identify an organization by organizationid, never by pod. If an organization moves to another pod, its historical rows stay under the old pod folder and new rows appear under the new one, so the same organization can appear under two pods. If a pod migration leaves a gap, ask your Zenoti account representative for a historical re-export under the new pod.
  7. Expect a fixed, additive schema. Each table has a fixed column list in a fixed order. A new upstream field doesn't change the schema on its own. It's left out until Zenoti adds it deliberately and announces it.
  8. Handle mixed boolean types on the call log. is_transferred, is_international, is_business_hours, is_deleted and via_phone_tree are native booleans. from_voicebot ("True" or "False"), voice_mail_received ("1" and "0", or "True" and "False") and the call_disconnected and call_ringing event markers arrive as text.

Troubleshooting

A day's row count is lower than expected. Wait for the next daily run, then check again. See the day boundary note above.

Call volume is too high. You're probably counting call_sid. Count parent_call_sid instead.

An organization's history is split across two folders. The organization moved pods. Join on organizationid and ask your Zenoti account representative for a historical re-export if there's a gap.

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