ZenotiHelp

HyperConnect Voice Call Metrics Fact Table

ZTZenoti Docs Team Updated October 11, 2026 11 min readGuide

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)
Was this article helpful?
Your feedback helps us improve our documentation.