Skip to content

Customer Call History and Usage

Use this guide whenever the question is about a customer or user rather than a single device.

Canonical source selection

  • Use ANALYTICS_DB.ANALYTICS.DEVICES_JOINED_FULL_HISTORY to map DEVICE_ID to CUSTOMER_ID and roll device activity up to one customer.
  • Use ANALYTICS_DB.ANALYTICS.PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY for full-history customer usage counts and milestone dates. It is the canonical source for metrics built from daily participant summaries such as CALLS_MADE, SUCCESSFUL_CALLS_MADE, SUCCESSFUL_CALLS_RECEIVED, and SUCCESSFUL_CALLS_PARTICIPATED_IN.
  • Use ANALYTICS_DB.ANALYTICS.CALL_LOGS for current-era call-level detail from 2025-05-05 onward.
  • Use ANALYTICS_DB.ANALYTICS.CALL_LOGS_FULL_HISTORY when the user needs historical call-level detail across the full available call-log period.
  • Use ANALYTICS_DB.ANALYTICS.PARTICIPANT_LOGS_FULL_HISTORY only when you need participant-side direction or counterparty detail relative to a specific device or customer. A single can-to-can conversation can contribute one row for each Tin Can participant, so this is not the best starting point for deduplicated call counts.

Customer-level aggregation

  • Default the analysis grain to CUSTOMER_ID for customer and user questions.
  • Aggregate across all devices that belong to the customer.
  • Use device-level rows only when the user explicitly asks about device behavior, activation, or hardware.

Counting rules

Calls made

Use this definition for requests like "calls made", "total calls made", "calls last 7 days", or "calls last 30 days": - Base metric: outbound calls from the customer's devices. - Daily-summary source: sum CALLS_MADE from PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY. - Answered-only variant: sum SUCCESSFUL_CALLS_MADE.

This definition matches customer usage screening and leaderboard-style analyses better than a mixed inbound and outbound participation count.

Calls participated in

Use this definition when the user asks for call history, call participation, or total answered conversations: - Include calls where any of the customer's devices appears on either side of the call. - At the daily-summary grain, use SUCCESSFUL_CALLS_PARTICIPATED_IN. - At the call-level grain, match the customer's devices against both CALL_FROM and CALL_TO.

Minutes on phone

Use answered calls only: - Filter call-level tables to IS_CALL_ANSWERED = TRUE. - Sum TALK_TIME_SECONDS and divide by 60 for minutes. - In customer-level participation metrics, credit the call duration to each participating customer.

If you need unique cohort-level call totals rather than per-user usage, aggregate from a one-row-per-call table and deduplicate before summing.

Direction and detail views

  • Determine direction relative to the customer:
  • outbound: one of the customer's devices is CALL_FROM
  • inbound: one of the customer's devices is CALL_TO
  • For a detail table that should reconcile to a "calls made" metric, show outbound records only.
  • For a detail table that should show the customer's full call history, include both inbound and outbound records and label the result as participation or history rather than calls made.

Trailing windows and denominators

  • Use CALL_DAY for trailing windows built from PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY.
  • Use CALL_DATE for trailing windows built from call-level tables.
  • For averages phrased as "per user", divide by all users in the cohort and treat users with no qualifying calls as zero.
  • If the requested denominator is only active users, label it explicitly as "per active user".

Historical coverage and first successful calls

  • PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY is the safest full-history source for milestone detection such as "first successful call".
  • Use SUCCESSFUL_CALLS_PARTICIPATED_IN > 0 to identify the first day a device had a successful call, then roll that up to the customer across all devices.
  • Add call-level attributes afterward from CALL_LOGS_FULL_HISTORY or PARTICIPANT_LOGS_FULL_HISTORY.

Practical defaults

  • "one customer per row" -> aggregate to CUSTOMER_ID
  • "calls made last 7 or 30 days" -> sum CALLS_MADE across all customer devices
  • "first successful call" -> derive the date from PARTICIPANT_DAILY_ACTIVITY_FULL_HISTORY, then enrich from call-level logs
  • "show me that customer's calls" -> choose between outbound-only detail vs full participation history, and label the output to match the definition