HyperConnect Voice Call Log Fact Table
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 |