Read the analytics data export
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
- Where the table families differ
- Differences between S3 and Redshift
- Columns that need care
- Read the data correctly
- Troubleshooting
- Related articles
Rules that apply to every table
- Upsert, don't append. A row can appear in several runs. Key on the table's primary key and keep the latest version.
- 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.
- 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
SchemaUpgradesfolder 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
- 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.
- Cast HyperConnect values. Numbers and booleans arrive as text. Nulls are real nulls, not empty strings or
"None". - Cast AI Lead Manager timestamps. The
_utcand_centercolumns hold the same instant in two time zones. - Parse nested values as JSON.
usage_metadata,component_api_response,call_volume_hourly_breakdown, the review reply,field_value,field_positionandfield_config_jsonare JSON-encoded. - Count calls on
parent_call_sid. Countingcall_sidcounts transfer and bot legs separately and overstates volume. - 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. - 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.
- Handle mixed boolean types on the call log.
is_transferred,is_international,is_business_hours,is_deletedandvia_phone_treeare native booleans.from_voicebot("True"or"False"),voice_mail_received("1"and"0", or"True"and"False") and thecall_disconnectedandcall_ringingevent 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.