Skip to content

Call Quality Telemetry

RAW_DB.THINGSBOARD.CALL_TELEMETRY holds one row per call leg reported by a device: RTP packet counts, jitter-buffer behavior, echo cancellation, Wi-Fi signal strength, and the call's duration. Devices write it to S3 as Parquet every fifteen minutes and Airbyte loads it hourly in append mode.

It lives in the thingsboard schema and arrives on the same kind of pipeline as thingsboard_reports, but the two are unrelated in every way that matters. Different grain — one row per call here, one row per device per hourly snapshot there. Different identifiers, which do not join to each other. Reach for this table for call quality; reach for thingsboard_reports for device state.

Which source answers "how's our call quality?"

Two tables measure call quality, and a complete answer uses both — they are not alternatives to choose between. Answering with only one is the common failure: the perceptual score without the causes, or the causes without a number anyone can act on.

RAW_DB.TINCAN.CDR.audio_in_mos is the headline. Mean Opinion Score, the industry-standard perceptual rating, present on every connected call leg. One interpretable number. Start there for how good were the calls.

This table is the diagnosis. Packet loss, jitter buffer, echo cancellation, Wi-Fi signal — the inputs that explain a bad score, plus the per-household breakdown. Start here for why were they bad.

The two agree. Bucketing calls by MOS and measuring this table's counters against them, packet loss rises monotonically from about 0.2% at the MOS ceiling to about 7% below 3.0, with jitter underruns and Wi-Fi signal degrading in step. They are independent measurements of the same reality — the platform's perceptual scoring on one side, the device's own network counters on the other.

They join on call_id, so a bad score leads directly to its cause. See "Starting from a bad MOS score" below.

For the plain question — "how's our call quality?" — lead with the MOS distribution, then use this table to explain the tail. A stakeholder wants one number and a reason, not a packet-loss rate on its own.

One caveat on MOS: it saturates. The large majority of calls sit at the 4.5 ceiling, so it cannot rank a good call against a very good one. It is an exception detector rather than a variance signal — a ceiling reading means "nothing was wrong," not "no information."

Three ways to get call quality wrong

call_quality is not a measure of call quality. The column reads good / fair / poor and it is the most inviting thing in the table, which is exactly the problem. It tracks call duration, not network performance: nearly every call longer than fifteen seconds is labeled poor, while the median call in every duration band loses zero packets. It appears to count cumulative events over the call instead of rating them, so a long clean call accumulates its way into poor. Reporting the share of calls by this label produces an alarming number that describes nothing. Do not use it. Nothing in this guide does.

Packet loss needs a guard or one row will carry the day. On the order of a hundred calls a day report an rtp_lost in the tens of millions — larger than rtp_pkts_total, which cannot happen. They look like a counter reset or an uninitialized read. By row count they are a rounding error; by weight they are catastrophic, because a single one can move a day's loss rate from a quarter of a percent to almost one hundred. Every query below filters rtp_lost <= rtp_pkts_total. Without it the daily series looks violently unstable; with it, it is flat.

The table is not unique on call_id. Delivery is at-least-once, so a record can appear several times, usually within the same file and byte-identical across every column. Counts, sums and distinct device figures survive this with slight inflation. An average of a per-call rate does not — the duplicates skew toward lossy calls, so an unguarded average of packet_loss_pct runs meaningfully high. Deduplicate first.

Both guards together, which is how every query here starts:

with calls as (
    select *
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
    qualify row_number() over (partition by call_id order by ts desc) = 1
)

ts is epoch milliseconds, so to_timestamp_ltz(ts, 3) is how you get a real timestamp. The session timezone is already Pacific — do not convert it again.

How is call quality overall?

with calls as (
    select *
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
    qualify row_number() over (partition by call_id order by ts desc) = 1
)
select
    count(*)                                                         as calls,
    count(distinct device_id)                                        as devices,
    round(sum(call_duration_s) / 3600.0, 0)                          as talk_hours,
    round(median(call_duration_s), 0)                                as median_call_s,
    round(100.0 * sum(rtp_lost) / nullif(sum(rtp_pkts_total), 0), 3) as packet_loss_pct,
    round(100.0 * sum(jb_underrun) / nullif(sum(jb_rx), 0), 3)       as jitter_underrun_pct,
    round(100.0 * sum(jb_drop) / nullif(sum(jb_rx), 0), 3)           as jitter_drop_pct,
    round(100.0 * sum(rtp_ooo) / nullif(sum(rtp_pkts_total), 0), 3)  as out_of_order_pct,
    round(avg(erle_db_final), 1)                                     as avg_echo_reduction_db,
    round(avg(wifi_rssi), 0)                                         as avg_wifi_rssi
from calls
where to_timestamp_ltz(ts, 3) >= dateadd('day', -7, current_timestamp());

Packet loss is pooled — total packets lost over total packets sent — rather than an average of the per-call percentage. That is deliberate. A flat average weights a two-second call the same as a ten-minute one and is the figure most distorted by the duplicate records. Use the pooled form wherever you aggregate loss.

For context on reading the output: loss well under one percent is healthy for VoIP, and jitter underruns are the metric that most directly corresponds to audio a person would call choppy.

Is call quality getting better or worse?

with calls as (
    select *
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
    qualify row_number() over (partition by call_id order by ts desc) = 1
)
select
    to_date(to_timestamp_ltz(ts, 3))                                 as call_date,
    count(*)                                                         as calls,
    round(100.0 * sum(rtp_lost) / nullif(sum(rtp_pkts_total), 0), 3) as packet_loss_pct,
    round(100.0 * sum(jb_underrun) / nullif(sum(jb_rx), 0), 3)       as jitter_underrun_pct
from calls
where to_timestamp_ltz(ts, 3) >= dateadd('day', -30, current_timestamp())
group by 1
order by 1 desc;

If this series ever shows a dramatic single-day spike, suspect the data before suspecting the network. Check whether one row is carrying the day:

select
    to_date(to_timestamp_ltz(ts, 3)) as call_date,
    count(*)                         as calls,
    sum(iff(rtp_lost > rtp_pkts_total, 1, 0)) as impossible_rows,
    max(rtp_lost)                    as max_rtp_lost,
    sum(rtp_lost)                    as sum_rtp_lost
from raw_db.thingsboard.call_telemetry
where to_timestamp_ltz(ts, 3) >= dateadd('day', -14, current_timestamp())
group by 1
order by 1 desc;

A max_rtp_lost close to sum_rtp_lost means a single corrupt call is the entire story. A genuine network problem moves jitter underruns too — packet loss that rises on its own, with the jitter metrics flat, is almost always an artifact.

Which households are having bad calls?

device_id does not join to anything in the ThingsBoard tables, despite this table's lineage and schema. It is the Tin Can application's device key. The path to a customer runs through raw_db.tincan.device:

with calls as (
    select *
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
    qualify row_number() over (partition by call_id order by ts desc) = 1
)
select
    dd.customer_id,
    dd.routing_id,
    count(*)                                                             as calls,
    round(100.0 * sum(c.rtp_lost) / nullif(sum(c.rtp_pkts_total), 0), 3) as packet_loss_pct,
    round(100.0 * sum(c.jb_underrun) / nullif(sum(c.jb_rx), 0), 3)       as jitter_underrun_pct,
    round(avg(c.wifi_rssi), 0)                                           as avg_wifi_rssi
from calls c
join raw_db.tincan.device d
    on d.key = c.device_id
join analytics_db.analytics.dim_device dd
    on dd.routing_id = d.routing_id
where to_timestamp_ltz(c.ts, 3) >= dateadd('day', -30, current_timestamp())
group by 1, 2
having count(*) >= 50
order by packet_loss_pct desc
limit 25;

The having clause matters. Without a minimum call count the top of this list is households that made three calls, one of which went badly.

Read the Wi-Fi column alongside the loss column. Households at the top of this list usually show signal in the -70s or -80s dBm, which points at a home network problem rather than anything Tin Can controls. A household with strong signal and high loss is the more interesting case and worth looking at individually.

Starting from a bad MOS score

The previous query finds households with poor network conditions. This one starts from the perceptual score instead — the households whose calls actually sounded bad — and attaches the telemetry that explains why.

with calls as (
    select call_id, device_id, rtp_lost, rtp_pkts_total, jb_underrun, jb_rx, wifi_rssi
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
      and to_timestamp_ltz(ts, 3) >= dateadd('day', -14, current_timestamp())
    qualify row_number() over (partition by call_id order by ts desc) = 1
), scored as (
    select call_id, min(audio_in_mos) as mos
    from raw_db.tincan.cdr
    where call_started_at >= dateadd('day', -15, current_timestamp())
      and billsec > 0
      and audio_in_mos is not null
    group by 1
)
select dd.customer_id,
       count(*)                                                             as calls,
       round(avg(s.mos), 2)                                                 as avg_mos,
       round(100.0 * count_if(s.mos < 3.5) / count(*), 1)                   as pct_calls_below_3_5,
       round(100.0 * sum(c.rtp_lost) / nullif(sum(c.rtp_pkts_total), 0), 3) as packet_loss_pct,
       round(100.0 * sum(c.jb_underrun) / nullif(sum(c.jb_rx), 0), 3)       as jitter_underrun_pct,
       round(avg(c.wifi_rssi), 0)                                           as avg_wifi_rssi
from calls c
join scored s                             on s.call_id     = c.call_id
join raw_db.tincan.device d               on d.key         = c.device_id
join analytics_db.analytics.dim_device dd on dd.routing_id = d.routing_id
group by 1
having count(*) >= 50
order by pct_calls_below_3_5 desc
limit 25;

The CDR window is deliberately a day wider than the telemetry window, so calls near the boundary still find their score.

Read the loss and Wi-Fi columns together, because the interesting split is between two populations. Households with poor scores and high packet loss on weak signal have a home network problem — that is the common case and it is not something Tin Can controls. Households with poor scores and clean telemetry are the ones worth escalating: the device saw no loss, no jitter, and strong signal, so the problem is upstream of the device rather than in the home. Only the join distinguishes them; neither table can on its own.

Audio problems that are not packet loss

Packet loss is the headline metric but not the only way a call sounds bad. These three columns describe distinct failure modes and a call can fail one while passing the others.

with calls as (
    select *
    from raw_db.thingsboard.call_telemetry
    where rtp_lost <= rtp_pkts_total
    qualify row_number() over (partition by call_id order by ts desc) = 1
)
select
    to_date(to_timestamp_ltz(ts, 3))                           as call_date,
    round(100.0 * sum(jb_underrun) / nullif(sum(jb_rx), 0), 3) as jitter_underrun_pct,
    round(100.0 * sum(jb_drop)     / nullif(sum(jb_rx), 0), 3) as jitter_drop_pct,
    round(100.0 * sum(jb_gaps)     / nullif(sum(jb_rx), 0), 3) as jitter_gap_pct,
    round(avg(erle_db_final), 2)                               as avg_echo_reduction_db,
    round(avg(aec_ref_underrun), 2)                            as avg_aec_ref_underrun
from calls
where to_timestamp_ltz(ts, 3) >= dateadd('day', -14, current_timestamp())
group by 1
order by 1 desc;

Jitter buffer (jb_underrun, jb_drop, jb_gaps) is about timing rather than loss. Packets arrive, but too late or too unevenly to play smoothly. Underruns are the closest thing in this table to "the call sounded choppy."

Echo cancellation (erle_db_final, aec_ref_underrun) is about the device, not the network. erle_db_final measures how much echo was removed, in dB, where higher is better. Elevated aec_ref_underrun with healthy packet loss points at an echo or duplex problem rather than connectivity.

What this table cannot tell you

Anything about who was on the call. There is no participant, contact, or phone number here. For call records, who called whom, and call outcomes, use the call logs — this table is about the technical quality of the audio, not the call itself.

Deep history — yet. The feed begins in late July 2026, which is when the export was switched on rather than a retention limit. Files are kept, so the window grows from here. Anything needing a longer baseline has to wait for it, or come from the call logs instead.

Anything about the earliest days of the feed. There are a few missing days shortly after the export was first stood up. The feed has been continuous since early August.