Daily Calls

Query

WITH base AS (
    SELECT
        CALL_DAY AS date,
        CASE
            WHEN '{{ time_grain | sqlsafe }}' = 'Day' THEN DATE_TRUNC('day', CALL_DAY)
            WHEN '{{ time_grain | sqlsafe }}' = 'Week' THEN DATE_TRUNC('week', CALL_DAY)
            WHEN '{{ time_grain | sqlsafe }}' = 'Month' THEN DATE_TRUNC('month', CALL_DAY)
            WHEN '{{ time_grain | sqlsafe }}' = 'Quarter' THEN DATE_TRUNC('quarter', CALL_DAY)
        END AS period_start,
        THIS_PARTICIPANT_DEVICE_ID,
        SUCCESSFUL_CALLS_RECEIVED,
        SUCCESSFUL_CALLS_MADE,
        SUCCESSFUL_CALLS_PARTICIPATED_IN,
        CALLS_MADE,
        CALLS_RECEIVED,
        NUM_EXTERNAL_CALLS,
        NUM_SUCCESSFUL_EXTERNAL_CALLS,
        NUM_VOICEMAILS_LEFT,
        NUM_VOICEMAILS_RECEIVED,
        TALK_TIME_SECONDS
    FROM ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY
),

daily_metrics AS (
    SELECT
        date,
        period_start,

        COUNT(DISTINCT THIS_PARTICIPANT_DEVICE_ID) AS daily_active_devices,
        COUNT(DISTINCT CASE
            WHEN SUCCESSFUL_CALLS_RECEIVED > 0
             AND SUCCESSFUL_CALLS_MADE > 0
            THEN THIS_PARTICIPANT_DEVICE_ID
        END) AS daily_users_who_both_made_and_received_calls,

        SUM(SUCCESSFUL_CALLS_PARTICIPATED_IN) AS answered_calls,
        SUM(CALLS_MADE) AS outbound_calls,
        SUM(SUCCESSFUL_CALLS_MADE) AS answered_outbound,
        SUM(CALLS_RECEIVED) AS inbound_calls,
        SUM(SUCCESSFUL_CALLS_RECEIVED) AS answered_inbound,
        SUM(NUM_EXTERNAL_CALLS) AS external_calls,
        SUM(NUM_SUCCESSFUL_EXTERNAL_CALLS) AS answered_external,
        SUM(NUM_VOICEMAILS_LEFT) AS voicemails_left,
        SUM(NUM_VOICEMAILS_RECEIVED) AS voicemails_received,
        SUM(TALK_TIME_SECONDS) / 3600.0 AS total_talk_hours
    FROM base
    GROUP BY 1, 2
),

device_period_flags AS (
    SELECT
        period_start,
        THIS_PARTICIPANT_DEVICE_ID,

        MAX(CASE
            WHEN COALESCE(calls_made,0) + COALESCE(SUCCESSFUL_CALLS_RECEIVED,0) > 0
            THEN 1 ELSE 0
        END) AS was_active_in_period,

        MAX(CASE
            WHEN SUCCESSFUL_CALLS_RECEIVED > 0
             AND SUCCESSFUL_CALLS_MADE > 0
            THEN 1 ELSE 0
        END) AS both_made_and_received_in_period
    FROM base
    GROUP BY 1, 2
),

period_distinct_metrics AS (
    SELECT
        period_start,
        COUNT(*) AS active_devices,
        SUM(both_made_and_received_in_period) AS users_who_both_made_and_received_calls
    FROM device_period_flags
    GROUP BY 1
)

SELECT
    d.date,
    d.period_start,

    -- daily metrics for forecasting / trailing-day logic
    d.daily_active_devices,
    d.daily_users_who_both_made_and_received_calls,
    d.answered_calls,
    d.outbound_calls,
    d.answered_outbound,
    d.inbound_calls,
    d.answered_inbound,
    d.external_calls,
    d.answered_external,
    d.voicemails_left,
    d.voicemails_received,
    d.total_talk_hours,

    -- correct distinct metrics at selected grain
    p.active_devices,
    p.users_who_both_made_and_received_calls

FROM daily_metrics d
LEFT JOIN period_distinct_metrics p
    ON d.period_start = p.period_start
ORDER BY d.date;

Overview

Produces a daily time-series of call activity and engagement metrics across all Tin Can devices. Each row represents a single calendar date, with both raw daily metrics and period-correct distinct device counts at the selected time grain.

Depends on: ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY

---

Parameters

Parameter	Accepted Values	Description
{{ time_grain }}	'Day', 'Week', 'Month', 'Quarter'	Controls the aggregation period for period_start and period-level distinct counts. Note: uses sqlsafe filter.

---

Output Columns

Daily metrics

These are summed or counted at the individual date level, regardless of time_grain. Intended for day-level charting and trailing-day calculations.

Column	Description
date	Calendar date
period_start	Start of the period containing date, truncated to the selected grain
daily_active_devices	Distinct devices with any activity on date
daily_users_who_both_made_and_received_calls	Distinct devices that both made and received at least one successful call on date
answered_calls	Total successful calls participated in (inbound + outbound)
outbound_calls	Total outbound call attempts
answered_outbound	Total outbound calls that were answered
inbound_calls	Total inbound call attempts
answered_inbound	Total inbound calls that were answered
external_calls	Total calls involving an external (non-Can) number
answered_external	Total external calls that were answered
voicemails_left	Total voicemails left by devices
voicemails_received	Total voicemails received by devices
total_talk_hours	Total talk time across all devices, in hours

Period-level distinct metrics

These are computed across all days in the period, deduplicated correctly at the selected grain. A device only needs to qualify on any single day within the period to be counted.

Column	Description
active_devices	Distinct devices with any call activity (outbound attempts or answered inbound) within the period
users_who_both_made_and_received_calls	Distinct devices that both made and received at least one successful call within the period

---

CTEs

base

Source: ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY

Selects all relevant call metrics at the device-day grain and computes period_start via DATE_TRUNC keyed on {{ time_grain }}.

daily_metrics

Aggregates base to the date level. Distinct device counts here are daily — they will overcount across multi-day periods if used for period-level reporting.

device_period_flags

Aggregates base to the device × period level, producing two binary flags per device per period: - was_active_in_period — device made at least one outbound attempt or received at least one successful inbound call - both_made_and_received_in_period — device had at least one successful call in each direction

period_distinct_metrics

Rolls up device_period_flags to the period level, producing correctly deduplicated device counts at the selected grain.

---

Join Logic

daily_metrics and period_distinct_metrics are left-joined on period_start. This means every row retains full daily detail while also carrying the period-correct distinct counts for the period it belongs to. All rows within the same period will share the same active_devices and users_who_both_made_and_received_calls values.

---

Notes

• The sqlsafe filter on {{ time_grain }} bypasses Hex's default SQL escaping. This is intentional since the parameter value is injected directly into a DATE_TRUNC string argument.
• Do not use daily_active_devices or daily_users_who_both_made_and_received_calls for period-level reporting — these will overcount. Use active_devices and users_who_both_made_and_received_calls instead.
• Activity in was_active_in_period is defined as calls_made + successful_calls_received > 0, meaning a device that only attempted outbound calls (with none answered) is still considered active.