HyperConnect Voice Call Metrics Fact Table
Fact table of pre-aggregated HyperConnect voice call counters, one row per center per day: call volume, answered and missed calls, AI analysis, AI Receptionist and SmartBot activity, transfers, voicemails, call durations, and hourly breakdowns. Every metric is a count or a duration total for that center on that day. A day's row can be recalculated and delivered again as the day's calls are updated. Rows appear only for organizations that use HyperConnect. The metrics are already totals: roll them up with SUM, never COUNT. SmartBot counters here are call outcomes, separate from SmartBot usage in bi_facthchyperconnectusages. Minutes columns are counted independently of seconds columns (for example, total_call_duration_in_mins covers billable calls only), so do not convert one into the other. When you load the S3 files into your own warehouse, update rows by hc_row_key (the center and record_date); a day missing from a later delivery has not been deleted.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| facthcvoicecallmetricswid | BIGINT IDENTITY | NOT NULL | Surrogate warehouse key for the daily metrics record. |
| org_name | VARCHAR(256) | NULL | Organization display name when the record was exported. |
| centerwid | BIGINT | NULL | Warehouse key referencing the center dimension. |
| center_name | VARCHAR(256) | NULL | Center display name when the record was exported. |
| record_date | BIGINT | NOT NULL | Day the counters cover, as an integer YYYYMMDD on the center's local calendar. |
| ttl | BIGINT | NULL | Reserved; currently always empty. |
| inbound_calls | BIGINT | NULL | Inbound calls received. |
| direct_inbound_calls | BIGINT | NULL | Inbound calls that came in directly rather than by transfer. |
| transferred_inbound_calls | BIGINT | NULL | Inbound calls that arrived by transfer. |
| outbound_calls | BIGINT | NULL | Outbound calls placed. |
| calls_answered | BIGINT | NULL | Calls answered. |
| calls_answered_back_facing | BIGINT | NULL | Answered calls that staff took over from the AI Receptionist; counted separately from calls_answered. |
| calls_missed | BIGINT | NULL | Missed calls. |
| calls_missed_back_facing | BIGINT | NULL | Missed calls that had been handed over from the AI Receptionist to staff; counted separately from calls_missed. |
| calls_connected | BIGINT | NULL | Calls connected. |
| call_cancelled | BIGINT | NULL | Cancelled calls. |
| calls_analyzed | BIGINT | NULL | Calls with completed AI analysis. |
| calls_analyzed_back_facing | BIGINT | NULL | Calls with completed AI analysis that staff took over from the AI Receptionist; counted separately from calls_analyzed. |
| calls_analyzed_failed | BIGINT | NULL | Calls whose AI analysis failed. |
| calls_analyzed_failed_back_facing | BIGINT | NULL | Calls whose AI analysis failed after staff took them over from the AI Receptionist; counted separately from calls_analyzed_failed. |
| inbound_calls_analyzed | BIGINT | NULL | Inbound calls with completed AI analysis. |
| inbound_calls_analyzed_back_facing | BIGINT | NULL | Inbound calls with completed AI analysis that staff took over from the AI Receptionist; counted separately from inbound_calls_analyzed. |
| outbound_calls_analyzed | BIGINT | NULL | Outbound calls with completed AI analysis. |
| outbound_calls_analyzed_back_facing | BIGINT | NULL | Outbound calls with completed AI analysis that staff took over from the AI Receptionist; counted separately from outbound_calls_analyzed. |
| calls_handled_by_air | BIGINT | NULL | Calls handled by the AI Receptionist. |
| calls_handled_partially | BIGINT | NULL | Calls the AI Receptionist handled in part. |
| calls_handled_human_handover | BIGINT | NULL | AI Receptionist calls handed over to a staff member. |
| calls_abandoned | BIGINT | NULL | AI Receptionist calls the caller abandoned. |
| appointments_managed_air | BIGINT | NULL | Appointments managed by the AI Receptionist. |
| calls_transferred_to_air | BIGINT | NULL | Calls transferred to the AI Receptionist. Reserved: currently always empty. |
| calls_transferred | BIGINT | NULL | Calls transferred. |
| warm_call_transferred | BIGINT | NULL | Calls transferred by a successful warm transfer. |
| calls_transferred_to_center | BIGINT | NULL | Calls transferred to another center. |
| transferred_to_bot | BIGINT | NULL | Calls handed over from staff to the AI voicebot. Empty rather than 0 when no such handovers were recorded. |
| forwarded_to_smartbot | BIGINT | NULL | Calls forwarded to SmartBot. |
| business_hours_smartbot | BIGINT | NULL | Calls forwarded to SmartBot during business hours. |
| smartbot_conversation_started | BIGINT | NULL | SmartBot conversations started. |
| business_hours_smartbot_conv_started | BIGINT | NULL | SmartBot conversations started during business hours. |
| business_hours_smartbot_success | BIGINT | NULL | Successful SmartBot conversations during business hours. |
| business_hours_smartbot_failed | BIGINT | NULL | Unsuccessful SmartBot conversations during business hours. |
| post_business_hours_smartbot_success | BIGINT | NULL | Successful SmartBot conversations outside business hours. |
| post_business_hours_smartbot_failed | BIGINT | NULL | Unsuccessful SmartBot conversations outside business hours. |
| successful_bot_conversion | BIGINT | NULL | Bot conversations recorded as successful conversions. |
| failed_bot_conversion | BIGINT | NULL | Bot conversations recorded as failed conversions. |
| voicemails | BIGINT | NULL | Voicemails received. |
| business_hours_voice_mail | BIGINT | NULL | Voicemails received during business hours. |
| pending_tasks | BIGINT | NULL | Calls that produced AI-generated call notes. |
| total_call_duration | BIGINT | NULL | Total duration of incoming and outgoing calls, in seconds. |
| total_inbound_call_duration | BIGINT | NULL | Total duration of inbound calls, in seconds. |
| total_outbound_call_duration | BIGINT | NULL | Total duration of outbound calls, in seconds. |
| total_call_duration_in_mins | BIGINT | NULL | Total duration of billable calls, in minutes. |
| total_recording_duration | BIGINT | NULL | Total recording duration, in seconds. |
| total_recording_duration_in_mins | BIGINT | NULL | Total recording duration, in minutes. |
| total_non_billable_calls | BIGINT | NULL | Calls marked as non-billable. |
| total_non_billable_calls_duration | BIGINT | NULL | Total duration of non-billable calls, in minutes. |
| call_duration_00 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 00:00, center local time, in seconds. |
| call_duration_01 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 01:00, center local time, in seconds. |
| call_duration_02 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 02:00, center local time, in seconds. |
| call_duration_03 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 03:00, center local time, in seconds. |
| call_duration_04 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 04:00, center local time, in seconds. |
| call_duration_05 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 05:00, center local time, in seconds. |
| call_duration_06 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 06:00, center local time, in seconds. |
| call_duration_07 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 07:00, center local time, in seconds. |
| call_duration_08 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 08:00, center local time, in seconds. |
| call_duration_09 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 09:00, center local time, in seconds. |
| call_duration_10 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 10:00, center local time, in seconds. |
| call_duration_11 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 11:00, center local time, in seconds. |
| call_duration_12 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 12:00, center local time, in seconds. |
| call_duration_13 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 13:00, center local time, in seconds. |
| call_duration_14 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 14:00, center local time, in seconds. |
| call_duration_15 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 15:00, center local time, in seconds. |
| call_duration_16 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 16:00, center local time, in seconds. |
| call_duration_17 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 17:00, center local time, in seconds. |
| call_duration_18 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 18:00, center local time, in seconds. |
| call_duration_19 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 19:00, center local time, in seconds. |
| call_duration_20 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 20:00, center local time, in seconds. |
| call_duration_21 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 21:00, center local time, in seconds. |
| call_duration_22 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 22:00, center local time, in seconds. |
| call_duration_23 | BIGINT | NULL | Total duration of incoming and outgoing calls in the hour starting at 23:00, center local time, in seconds. |
| international_calls_total | BIGINT | NULL | International calls. |
| international_inbound_calls | BIGINT | NULL | International inbound calls. |
| international_outbound_calls | BIGINT | NULL | International outbound calls. |
| international_missed_calls | BIGINT | NULL | International missed calls. |
| call_category_appointment | BIGINT | NULL | Calls AI analysis categorized as appointment. |
| call_category_purchases | BIGINT | NULL | Calls AI analysis categorized as purchases. |
| call_category_others | BIGINT | NULL | Calls AI analysis categorized as others. |
| sentiment_positive | BIGINT | NULL | Calls with positive sentiment. |
| sentiment_negative | BIGINT | NULL | Calls with negative sentiment. |
| sentiment_neutral | BIGINT | NULL | Calls with neutral sentiment. |
| bookingflow_completed_true | BIGINT | NULL | Analyzed calls in which the booking flow was completed. |
| bookingflow_completed_false | BIGINT | NULL | Analyzed calls in which the booking flow was not completed. |
| rescheduleflow_completed_true | BIGINT | NULL | Analyzed calls in which the reschedule flow was completed. |
| rescheduleflow_completed_false | BIGINT | NULL | Analyzed calls in which the reschedule flow was not completed. |
| cancellationflow_completed_true | BIGINT | NULL | Analyzed calls in which the cancellation flow was completed. |
| cancellationflow_completed_false | BIGINT | NULL | Analyzed calls in which the cancellation flow was not completed. |
| call_volume_hourly_breakdown | VARCHAR(65535) | NULL | Hourly call volume for the day as JSON text (keys such as calls_answered_09). The same values are delivered in the calls_answered_*, calls_missed_*, and outbound_calls_* columns. |
| calls_answered_00 | BIGINT | NULL | Calls answered in the hour starting at 00:00, center local time. |
| calls_answered_01 | BIGINT | NULL | Calls answered in the hour starting at 01:00, center local time. |
| calls_answered_02 | BIGINT | NULL | Calls answered in the hour starting at 02:00, center local time. |
| calls_answered_03 | BIGINT | NULL | Calls answered in the hour starting at 03:00, center local time. |
| calls_answered_04 | BIGINT | NULL | Calls answered in the hour starting at 04:00, center local time. |
| calls_answered_05 | BIGINT | NULL | Calls answered in the hour starting at 05:00, center local time. |
| calls_answered_06 | BIGINT | NULL | Calls answered in the hour starting at 06:00, center local time. |
| calls_answered_07 | BIGINT | NULL | Calls answered in the hour starting at 07:00, center local time. |
| calls_answered_08 | BIGINT | NULL | Calls answered in the hour starting at 08:00, center local time. |
| calls_answered_09 | BIGINT | NULL | Calls answered in the hour starting at 09:00, center local time. |
| calls_answered_10 | BIGINT | NULL | Calls answered in the hour starting at 10:00, center local time. |
| calls_answered_11 | BIGINT | NULL | Calls answered in the hour starting at 11:00, center local time. |
| calls_answered_12 | BIGINT | NULL | Calls answered in the hour starting at 12:00, center local time. |
| calls_answered_13 | BIGINT | NULL | Calls answered in the hour starting at 13:00, center local time. |
| calls_answered_14 | BIGINT | NULL | Calls answered in the hour starting at 14:00, center local time. |
| calls_answered_15 | BIGINT | NULL | Calls answered in the hour starting at 15:00, center local time. |
| calls_answered_16 | BIGINT | NULL | Calls answered in the hour starting at 16:00, center local time. |
| calls_answered_17 | BIGINT | NULL | Calls answered in the hour starting at 17:00, center local time. |
| calls_answered_18 | BIGINT | NULL | Calls answered in the hour starting at 18:00, center local time. |
| calls_answered_19 | BIGINT | NULL | Calls answered in the hour starting at 19:00, center local time. |
| calls_answered_20 | BIGINT | NULL | Calls answered in the hour starting at 20:00, center local time. |
| calls_answered_21 | BIGINT | NULL | Calls answered in the hour starting at 21:00, center local time. |
| calls_answered_22 | BIGINT | NULL | Calls answered in the hour starting at 22:00, center local time. |
| calls_answered_23 | BIGINT | NULL | Calls answered in the hour starting at 23:00, center local time. |
| calls_missed_00 | BIGINT | NULL | Calls missed in the hour starting at 00:00, center local time. |
| calls_missed_01 | BIGINT | NULL | Calls missed in the hour starting at 01:00, center local time. |
| calls_missed_02 | BIGINT | NULL | Calls missed in the hour starting at 02:00, center local time. |
| calls_missed_03 | BIGINT | NULL | Calls missed in the hour starting at 03:00, center local time. |
| calls_missed_04 | BIGINT | NULL | Calls missed in the hour starting at 04:00, center local time. |
| calls_missed_05 | BIGINT | NULL | Calls missed in the hour starting at 05:00, center local time. |
| calls_missed_06 | BIGINT | NULL | Calls missed in the hour starting at 06:00, center local time. |
| calls_missed_07 | BIGINT | NULL | Calls missed in the hour starting at 07:00, center local time. |
| calls_missed_08 | BIGINT | NULL | Calls missed in the hour starting at 08:00, center local time. |
| calls_missed_09 | BIGINT | NULL | Calls missed in the hour starting at 09:00, center local time. |
| calls_missed_10 | BIGINT | NULL | Calls missed in the hour starting at 10:00, center local time. |
| calls_missed_11 | BIGINT | NULL | Calls missed in the hour starting at 11:00, center local time. |
| calls_missed_12 | BIGINT | NULL | Calls missed in the hour starting at 12:00, center local time. |
| calls_missed_13 | BIGINT | NULL | Calls missed in the hour starting at 13:00, center local time. |
| calls_missed_14 | BIGINT | NULL | Calls missed in the hour starting at 14:00, center local time. |
| calls_missed_15 | BIGINT | NULL | Calls missed in the hour starting at 15:00, center local time. |
| calls_missed_16 | BIGINT | NULL | Calls missed in the hour starting at 16:00, center local time. |
| calls_missed_17 | BIGINT | NULL | Calls missed in the hour starting at 17:00, center local time. |
| calls_missed_18 | BIGINT | NULL | Calls missed in the hour starting at 18:00, center local time. |
| calls_missed_19 | BIGINT | NULL | Calls missed in the hour starting at 19:00, center local time. |
| calls_missed_20 | BIGINT | NULL | Calls missed in the hour starting at 20:00, center local time. |
| calls_missed_21 | BIGINT | NULL | Calls missed in the hour starting at 21:00, center local time. |
| calls_missed_22 | BIGINT | NULL | Calls missed in the hour starting at 22:00, center local time. |
| calls_missed_23 | BIGINT | NULL | Calls missed in the hour starting at 23:00, center local time. |
| outbound_calls_00 | BIGINT | NULL | Outbound calls placed in the hour starting at 00:00, center local time. |
| outbound_calls_01 | BIGINT | NULL | Outbound calls placed in the hour starting at 01:00, center local time. |
| outbound_calls_02 | BIGINT | NULL | Outbound calls placed in the hour starting at 02:00, center local time. |
| outbound_calls_03 | BIGINT | NULL | Outbound calls placed in the hour starting at 03:00, center local time. |
| outbound_calls_04 | BIGINT | NULL | Outbound calls placed in the hour starting at 04:00, center local time. |
| outbound_calls_05 | BIGINT | NULL | Outbound calls placed in the hour starting at 05:00, center local time. |
| outbound_calls_06 | BIGINT | NULL | Outbound calls placed in the hour starting at 06:00, center local time. |
| outbound_calls_07 | BIGINT | NULL | Outbound calls placed in the hour starting at 07:00, center local time. |
| outbound_calls_08 | BIGINT | NULL | Outbound calls placed in the hour starting at 08:00, center local time. |
| outbound_calls_09 | BIGINT | NULL | Outbound calls placed in the hour starting at 09:00, center local time. |
| outbound_calls_10 | BIGINT | NULL | Outbound calls placed in the hour starting at 10:00, center local time. |
| outbound_calls_11 | BIGINT | NULL | Outbound calls placed in the hour starting at 11:00, center local time. |
| outbound_calls_12 | BIGINT | NULL | Outbound calls placed in the hour starting at 12:00, center local time. |
| outbound_calls_13 | BIGINT | NULL | Outbound calls placed in the hour starting at 13:00, center local time. |
| outbound_calls_14 | BIGINT | NULL | Outbound calls placed in the hour starting at 14:00, center local time. |
| outbound_calls_15 | BIGINT | NULL | Outbound calls placed in the hour starting at 15:00, center local time. |
| outbound_calls_16 | BIGINT | NULL | Outbound calls placed in the hour starting at 16:00, center local time. |
| outbound_calls_17 | BIGINT | NULL | Outbound calls placed in the hour starting at 17:00, center local time. |
| outbound_calls_18 | BIGINT | NULL | Outbound calls placed in the hour starting at 18:00, center local time. |
| outbound_calls_19 | BIGINT | NULL | Outbound calls placed in the hour starting at 19:00, center local time. |
| outbound_calls_20 | BIGINT | NULL | Outbound calls placed in the hour starting at 20:00, center local time. |
| outbound_calls_21 | BIGINT | NULL | Outbound calls placed in the hour starting at 21:00, center local time. |
| outbound_calls_22 | BIGINT | NULL | Outbound calls placed in the hour starting at 22:00, center local time. |
| outbound_calls_23 | BIGINT | NULL | Outbound calls placed in the hour starting at 23:00, center local time. |
| hc_row_key | VARCHAR(128) | NOT NULL | Unique key of the row: the center ID and record_date combined. |
| void | BOOLEAN | NULL | Soft-delete flag. Always FALSE for this table. |
| 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
facthcvoicecallmetricswid
Foreign Keys
| Column | References |
|---|---|
| centerwid | Bi_DimCenter.centerwid |
Enumerator Values
| Column | Values |
|---|---|
| void | Always FALSE (daily metrics are not deleted at the source) |