ZenotiHelp

HyperConnect Voice Call Log Fact Table

ZTZenoti Docs Team Updated October 12, 2026 5 min readGuide

Fact table recording HyperConnect voice calls, one row per call; a transferred or AI voicebot leg of a call has its own row. Includes call direction and duration, caller and staff details, AI analysis results, transcription status, voicemail, and transfer details. A call's row keeps changing after the call ends as AI analysis, transcription, and voicemail actions complete, and the latest version replaces the earlier one. Rows appear only for organizations that use HyperConnect. When you load the S3 files into your own warehouse, update rows by call_sid and keep the newest version; export_run_id identifies the delivery and is not part of the key. Do not delete rows that are missing from a later delivery.

Columns

Column Name Data Type Nullable Description
facthcvoicecalllogwid BIGINT IDENTITY NOT NULL Surrogate warehouse key for the call record.
call_sid VARCHAR(256) NOT NULL Unique identifier of the call. A transferred or AI voicebot leg keeps a suffix on this value (for example, _bot).
parent_call_sid VARCHAR(256) NULL The call identifier without its suffix; equals call_sid when there is no suffix. Count distinct values of this column to get call volume without counting transfer or voicebot legs separately.
centerwid BIGINT NULL Warehouse key referencing the center dimension.
center_name VARCHAR(256) NULL Center display name when the record was exported.
org_name VARCHAR(256) NULL Organization display name when the record was exported.
call_type VARCHAR(64) NULL Call direction or classification code. See Enumerator Values.
call_duration BIGINT NULL Call duration in seconds. Empty when the call was never answered.
call_timestamp BIGINT NULL When the call record was written, in epoch milliseconds (UTC).
center_timestamp BIGINT NULL When the call happened, in epoch milliseconds on the center's local clock. Use this column for date ranges.
guest_name VARCHAR(256) NULL Full name of the guest on the call.
guest_phone_code VARCHAR(32) NULL Country dialing code of the guest's phone number, with a leading +.
guest_phone_number VARCHAR(256) NULL Guest's phone number without the country code.
GuestWid BIGINT NULL Warehouse key referencing the guest in the user dimension. Empty when the caller was not matched to a guest.
agent_name VARCHAR(256) NULL Display name of the staff member on the call.
EmployeeWid BIGINT NULL Warehouse key referencing the staff member in the user dimension.
voice_mail_received VARCHAR(64) NULL Whether the caller left a voicemail (1/0 or True/False).
analysis_status VARCHAR(64) NULL State of the AI call analysis. See Enumerator Values.
call_category VARCHAR(128) NULL Category assigned to the call by AI analysis. See Enumerator Values.
sentiment VARCHAR(64) NULL Caller sentiment assigned by AI analysis. See Enumerator Values.
call_notes VARCHAR(65535) NULL AI-generated notes for calls handled by the AI Receptionist.
transferred_type VARCHAR(64) NULL Type of transfer target. See Enumerator Values.
transferred_type_id VARCHAR(64) NULL Identifier of the transfer target: a staff member ID, a center ID, or an external phone number, matching transferred_type.
is_transferred BOOLEAN NULL TRUE when the call was transferred.
from_voicebot VARCHAR(32) NULL Whether the AI voicebot handled the call. call_type still holds the original call direction. See Enumerator Values.
recording_status VARCHAR(64) NULL State of the call recording; deleted once the recording has been purged.
missed_call_category VARCHAR(128) NULL Reason code (1–4) for a missed call. Set only when call_type is 2 (Missed), and can be empty for very recent missed calls. Labels for these codes are not included in the data.
transcription_status VARCHAR(64) NULL State of the call transcription. See Enumerator Values.
transcript_text VARCHAR(65535) NULL Always NULL. Call transcripts are not delivered, in either the Amazon S3 export or Redshift. To see whether a call was transcribed, use transcription_status.
assigned_to_id VARCHAR(64) NULL Identifier of the staff member the voicemail is assigned to.
voicemail_activity_status VARCHAR(64) NULL Follow-up state of the voicemail. See Enumerator Values.
call_source VARCHAR(64) NULL Source attribution label for the call (for example, Google Ads). Empty until call source attribution is set up.
is_international BOOLEAN NULL TRUE for international calls.
is_business_hours BOOLEAN NULL TRUE when the call happened within the center's business hours.
is_deleted BOOLEAN NULL TRUE when the call record was deleted at the source. Deleted calls stay in the table; void is also TRUE for them.
zma_ringing_devices VARCHAR(2048) NULL Devices on the ZMA channel that rang for the call, as a text list; each entry includes the ID of the staff member whose device rang.
web_ringing_devices VARCHAR(2048) NULL Web devices that rang for the call, as a text list; each entry includes the ID of the staff member whose device rang.
sip_ringing_devices VARCHAR(2048) NULL Desk phones that rang for the call, as a text list of SIP user names.
outgoing_masking_number VARCHAR(64) NULL Masked caller ID number used for an outbound call.
outgoing_masking_name VARCHAR(256) NULL Masked caller ID name used for an outbound call.
forwarded_source VARCHAR(128) NULL Call forwarding attribution: the forwarding source.
forwarded_type VARCHAR(64) NULL Call forwarding attribution: the forwarding type.
forwarded_from VARCHAR(128) NULL Call forwarding attribution: where the call was forwarded from.
call_handled_via VARCHAR(128) NULL Channel or device path through which the call was handled.
conference_name VARCHAR(256) NULL Name of the call conference that carried the call.
via_phone_tree BOOLEAN NULL TRUE when the call went through the phone tree.
call_duration_in_mins FLOAT NULL Call duration in minutes.
call_disconnected VARCHAR(16) NULL Marker set when the call disconnected.
call_disconnected_timestamp BIGINT NULL When the call disconnected, in epoch milliseconds.
call_ringing VARCHAR(16) NULL Marker set when the call started ringing.
call_ringing_timestamp BIGINT NULL When ringing started, in epoch milliseconds.
child_call_sid VARCHAR(256) NULL Identifier of a child or related call leg, when there is one.
sip_call_sid VARCHAR(256) NULL Identifier of the call leg that reached a desk phone, when there is one.
guest_iso_code VARCHAR(16) NULL ISO country code of the guest's phone number.
recording_duration BIGINT NULL Recording length in seconds.
recording_duration_in_mins FLOAT NULL Recording length in minutes.
openai_analysis VARCHAR(65535) NULL AI analysis output for the call, often as JSON text. Can be large.
agent_performance_score FLOAT NULL AI-assigned score for how the staff member handled the call, when analysis has run.
call_subcategory VARCHAR(256) NULL Subcategory assigned by AI analysis; finer grained than call_category.
void BOOLEAN NULL Soft-delete flag; TRUE when the call record was deleted at the source (is_deleted).
export_run_id VARCHAR(64) NULL Identifier of the HyperConnect export run that delivered the row: its UTC date and time as YYYYMMDDHHMMSS.

Note: All fact tables also include standard ETL metadata columns (podname, organizationid, groupid, etlCreatedDate) which are omitted from this reference for brevity.

Primary Key

facthcvoicecalllogwid

Foreign Keys

Column References
centerwid Bi_DimCenter.centerwid
GuestWid Bi_DimUser.userwid
EmployeeWid Bi_DimUser.userwid

Enumerator Values

Column Values
call_type 0 = Incoming
1 = Outgoing
2 = Missed
3 = Cancelled
4 = Transferred (older records; newer records use is_transferred)
5 = AI Receptionist (older records; newer records use from_voicebot)
analysis_status success, failure, pending, completed, failed, notApplicable. More values can appear; treat success as "analysis available".
call_category appointment, purchases, others
sentiment Positive, Negative, Neutral
transferred_type agent, center, external
transcription_status success, failed, inProgress, notApplicable, deleted (recording purged)
voicemail_activity_status -1 = Resolved
0 = Unread
1 = Read
2 = Listened
3 = Listened and read
4 = Called back
5 = Assigned
6 = Messaged
7 = Forwarded
8 = Forwarded to center
from_voicebot True, False
void FALSE = Active, TRUE = Deleted at the source
Was this article helpful?
Your feedback helps us improve our documentation.