Quality Impacted Assets (QIA) Report from Legacy SSD
The Quality Impacted Assets (QIA) report enables identifying assets that are impacted with severely degraded Quality of Experience (QoE). Compared to the Broken Assets module available in Pulse Automatic Insights, the QIA Report shows more insights into the impacted asstes. The report is capable of uncovering long-tail and hard-to-detect issues, that are not visible through the Pulse. This makes the QIA report a critical tool for surfacing assets that are consistently underperforming.
Enabling Conviva Legacy SSD
Reach out to your Conviva Representative or contact Conviva Support.
Generating the QIA Report from Conviva Legacy SSD Files
-
Select and compute basic columns we need from SSD.
-
Select the columns relevant to the QIA report.
-
Replace the default date week (2025-09-01) with a dynamic week parameter.
Copy%sql
CREATE OR REPLACE TEMPORARY VIEW simple_ssd AS
SELECT
to_date(from_unixtime(start_time_unix_time)) AS day,
'2025-09-01' as week,
initcap(country) as country,
SUBSTRING_INDEX(conviva_session_id, ":", 4) AS client_id,
asset AS asset_name,
session_tags AS tag_map,
case when startup_time_ms>=0 then true else false END AS joined,
CASE WHEN ended_status > 0 THEN true ELSE false END AS ended_session,
CASE WHEN ended_status > 0 AND startup_time_ms>=0 THEN true ELSE false END AS ended_play,
CASE WHEN ended_status > 0 AND startup_time_ms>=0 AND (!VPF) THEN 1 ELSE 0 END AS ended_successful_play,
CASE WHEN !startup_error AND startup_time_ms = -1 THEN true ELSE false END AS is_ebvs,
(VSF_T or VSF_B) AS is_vsf,
VSF_T AS is_vsf_t,
VSF_B AS is_vsf_b,
VPF AS is_VPF,
VPF_T AS is_vpf_t,
VPF_B AS is_vpf_b,
buffering_time_ms,
CASE WHEN startup_time_ms>=0 then startup_time_ms else null END as join_time_ms,
connection_induced_rebuffering_time_ms AS network_buffering_time_ms,
ROUND(connection_induced_rebuffering_time_ms / (playing_time_ms + buffering_time_ms), 4) AS network_rebuffering_ratio,
CASE WHEN connection_induced_rebuffering_time_ms = 0 AND ended_status > 0 AND startup_time_ms>=0 THEN 1 ELSE 0 END AS is_zero_cirr_ended_play,
playing_time_ms AS playing_time_ms,
average_bitrate_kbps AS bitrate,
round(percentage_complete/100,4) AS pct_complete,
(end_time_unix_time - start_time_unix_time) * 1000 AS session_time_ms,
round(content_length_ms/1000, 2) AS content_len,
rejoined_count
FROM
ssd
WHERE
ended_status > 0
and to_date(from_unixtime(start_time_unix_time)) >= '2025-09-01'Exclude Ad Sessions to focus only on the relevant data.
Copy%sql
create or replace temporary view base as
select
day,
week,
country,
client_id,
tag_map['Device Name'] as device,
tag_map["Device Hardware Type"] as screen_type,
case when tag_map['c3.video.isLive']='F' then "VOD" else "Live" end as content_type,
asset_name,
joined,
ended_session,
ended_play,
ended_successful_play,
is_ebvs,
is_vsf,
is_vsf_t,
is_vsf_b,
is_vpf,
is_vpf_t,
is_vpf_b,
join_time_ms,
buffering_time_ms,
network_buffering_time_ms,
network_rebuffering_ratio,
is_zero_cirr_ended_play,
playing_time_ms,
bitrate,
pct_complete,
session_time_ms,
rejoined_count,
case when tag_map['c3.video.isLive']='F' then round(pct_complete * content_len, 2) else null end as completed_time_sec
from
simple_ssd
where
(tag_map["c3.video.isAd"] is null or tag_map["c3.video.isAd"] = 'F')Compute SPI Session Quality to determine whether sessions meet the SPI Good and SPI Best thresholds.
Copy%sql
create or replace temporary view prep as
select
day,
week,
country,
client_id,
device,
screen_type,
content_type,
asset_name,
joined,
rejoined_count,
ended_session,
ended_play,
ended_successful_play,
is_ebvs,
is_vsf,
is_vsf_t,
is_vsf_b,
is_vpf,
is_vpf_t,
is_vpf_b,
(is_vsf_t or is_vpf_t) as session_is_technical_failure_impacted,
join_time_ms,
buffering_time_ms,
network_buffering_time_ms,
network_rebuffering_ratio,
is_zero_cirr_ended_play,
playing_time_ms,
bitrate,
pct_complete,
completed_time_sec,
case
when is_ebvs and session_time_ms > 10000 then true
when is_vsf_t then true
when is_vpf_t then true
when ended_play then true
else false
end as good_spi_stream,
case
when is_ebvs and session_time_ms > 8000 then true
when is_vsf_t then true
when is_vpf_t then true
when ended_play then true
else false
end as best_spi_stream,
case
when is_ebvs and session_time_ms > 10000 then "Bad"
when is_vsf_t then "Bad"
when is_vpf_t then "Bad"
when ended_play
and
(
join_time_ms > 10000
or
(network_rebuffering_ratio > 0.004 and network_buffering_time_ms > 2000) -- I calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.004 instead of 0.4
or
(
(bitrate > 0 and bitrate <= 200)
or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 400)
or
(screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 800)
)
) then "Bad"
end as good_spi_bad,
case
when is_ebvs and session_time_ms > 8000 then "Bad"
when is_vsf_t then "Bad"
when is_vpf_t then "Bad"
when ended_play
and
(
join_time_ms > 10000
or
(network_rebuffering_ratio > 0.0002 and network_buffering_time_ms > 1000) -- I calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.004 instead of 0.4
or
(
(bitrate > 0 and bitrate <= 400)
or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 800)
or
(screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 2000)
)
) then "Bad"
end as best_spi_bad
from
basAggregate the SPI data to compute performance metrics across the sessions.
Copy%sql
create or replace temporary view agg as
select
week,
country,
device,
content_type,
asset_name,
count(distinct client_id) as unique_devices,
count(distinct (case when session_is_technical_failure_impacted then client_id else null end)) as unique_impacted_devices,
count(*) as total_attempts,
sum(case when ended_play then 1 else 0 end) as total_plays, -- ended plays
round(sum(playing_time_ms) / 1000 / 60, 0) as total_playing_time_minutes,
round(sum(playing_time_ms) / 1000 / 60 / 60, 0) as total_playing_time_hours,
round(sum(playing_time_ms) / 1000 / 60 / sum(case when ended_play then 1 else 0 end), 0) as avg_minutes_per_play,
round(avg(pct_complete), 4) as avg_pct_complete,
round((1 - sum(case when good_spi_bad = "Bad" then 1 else 0 end) / sum(case when good_spi_stream then 1 else 0 end)) * 100, 2) as good_spi,
sum(case when good_spi_stream then 1 else 0 end) - sum(case when good_spi_bad = "Bad" then 1 else 0 end) as sessions_good_spi_cnt,
sum(case when good_spi_bad = "Bad" then 1 else 0 end) as impacted_sessions_good_spi_cnt,
round((1 - sum(case when best_spi_bad = "Bad" then 1 else 0 end) / sum(case when best_spi_stream then 1 else 0 end)) * 100, 2) as best_spi,
sum(case when best_spi_stream then 1 else 0 end) - sum(case when best_spi_bad = "Bad" then 1 else 0 end) as sessions_best_spi_cnt,
sum(case when best_spi_bad = "Bad" then 1 else 0 end) as impacted_sessions_best_spi_cnt,
round(sum(case when is_vsf_t then 1 else 0 end) / count(*), 4) as video_start_failures_technical_pct, -- can also do sum(int(is_vsf))
sum(case when is_vsf_t then 1 else 0 end) as video_start_failures_technical_cnt,
round(sum(case when is_ebvs then 1 else 0 end) / count(*), 4) as exit_before_video_starts_pct,
sum(case when is_ebvs then 1 else 0 end) as exit_before_video_starts_cnt,
round(avg(case when joined then join_time_ms else null end) / 1000, 1) as avg_video_start_time_sec,
round(sum(buffering_time_ms) / sum(buffering_time_ms + playing_time_ms), 4) as avg_rebuffering_ratio,
round(sum(network_buffering_time_ms) / sum(buffering_time_ms + playing_time_ms), 4) as avg_connection_induced_rebuffering_ratio,
round(sum(is_zero_cirr_ended_play) / sum(case when ended_play then 1 else 0 end), 4) as zero_cirr_plays_pct,
round(sum(case when bitrate > 0 then bitrate * playing_time_ms else null end) / sum(case when bitrate > 0 then playing_time_ms else null end), 0) as avg_bitrate_kbps,
sum(case when bitrate > 0 then playing_time_ms else null end) as sum_playtime,
sum(case when bitrate > 0 then bitrate * playing_time_ms else null end) as sum_bitMultiplyPlaytime,
round(sum(case when is_vpf_t then 1 else 0 end) / sum(if(joined, 1, 0)), 4) as video_playback_failures_technical_pct,
sum(case when is_vpf_t then 1 else 0 end) as video_playback_failures_technical_cnt,
round(sum(case when network_rebuffering_ratio > 0.004 and ended_play then 1 else 0 end) / sum(if(ended_play,1, 0)), 4) as good_high_rebuffering_pct,
sum(case when network_rebuffering_ratio > 0.004 and ended_play then 1 else 0 end) as good_high_rebuffering_cnt,
round(sum(case when network_rebuffering_ratio > 0.0002 and ended_play then 1 else 0 end) / sum(if(ended_play,1, 0)), 4) as best_high_rebuffering_pct,
sum(case when network_rebuffering_ratio > 0.0002 and ended_play then 1 else 0 end) as best_high_rebuffering_cnt,
round(sum(case when joined and join_time_ms > 10000 then 1 else 0 end) / sum(if(joined,1, 0)), 4) as high_startup_pct,
sum(case when joined and join_time_ms > 10000 then 1 else 0 end) as high_startup_cnt,
round(count(case when (screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 800) or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 400) or
(bitrate > 0 and bitrate <= 200) then 1 else null end) / sum(case when ended_play then 1 else 0 end), 4) as good_low_bitrate_pct,
count(case when (screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 800) or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 400) or
(bitrate > 0 and bitrate <= 200) then 1 else null end) as good_low_bitrate_cnt,
round(count(case when (screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 2000) or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 800) or
(bitrate > 0 and bitrate <= 400) then 1 else null end) / sum(case when ended_play then 1 else 0 end), 4) as best_low_bitrate_pct,
count(case when (screen_type in ("TV","Set Top Box","Games Console") and bitrate > 0 and bitrate <= 2000) or
(screen_type in ("Desktop","Tablet") and bitrate > 0 and bitrate <= 800) or
(bitrate > 0 and bitrate <= 400) then 1 else null end) as best_low_bitrate_cnt,
round(mean(case when is_vpf_t and joined then completed_time_sec end), 2) as avg_completed_time_sec,
round(stddev(case when is_vpf_t and joined then completed_time_sec end), 2) as stddev_completed_time_sec,
round(variance(case when is_vpf_t and joined then completed_time_sec end), 2) as variance_completed_time_sec
from
prep
group by
week,
country,
content_type,
device,
asset_nameDetermine sessions that are impacted by technical failures:
-
Apply a threshold for technical failures impacted. The recommended benchmark is 10.
-
Assign a proper value for the {technical_failures_impacted_threshold} parameter.
Copy%sql
create or replace temporary view agg2 as
select
week,
country,
content_type,
device,
asset_name,
unique_devices,
unique_impacted_devices,
total_attempts,
total_plays,
total_playing_time_minutes,
total_playing_time_hours,
avg_minutes_per_play,
avg_pct_complete,
round(video_start_failures_technical_pct + video_playback_failures_technical_pct, 4) as technical_failure_pct,
case when round(video_start_failures_technical_pct + video_playback_failures_technical_pct, 4) >= (${technical_failures_impacted_threshold} / 100) then 1 else 0 end as is_quality_impacted,
good_spi,
sessions_good_spi_cnt,
impacted_sessions_good_spi_cnt,
best_spi,
sessions_best_spi_cnt,
impacted_sessions_best_spi_cnt,
video_start_failures_technical_pct,
video_start_failures_technical_cnt,
exit_before_video_starts_pct,
exit_before_video_starts_cnt,
avg_video_start_time_sec,
avg_rebuffering_ratio,
avg_connection_induced_rebuffering_ratio,
zero_cirr_plays_pct,
avg_bitrate_kbps,
video_playback_failures_technical_pct,
video_playback_failures_technical_cnt,
good_high_rebuffering_pct,
good_high_rebuffering_cnt,
best_high_rebuffering_pct,
best_high_rebuffering_cnt,
high_startup_pct,
high_startup_cnt,
good_low_bitrate_pct,
good_low_bitrate_cnt,
best_low_bitrate_pct,
best_low_bitrate_cnt,
avg_completed_time_sec,
stddev_completed_time_sec,
variance_completed_time_sec
from
aggAggregate and compute quality-impacted assets.
-
Select sessions that reached the technical failures impacted threshold.
-
Use {minimum_attempts} as a parameter in the WHERE filter. Update the default value 1000 with the expected value needed for reporting.
-
Exclude null assets from the results.
Copy%sql
create or replace temporary view quality_impacted_assets as
select
week,
country,
content_type,
device,
asset_name,
unique_devices,
unique_impacted_devices,
total_attempts,
total_plays,
technical_failure_pct,
good_spi,
sessions_good_spi_cnt,
impacted_sessions_good_spi_cnt,
best_spi,
sessions_best_spi_cnt,
impacted_sessions_best_spi_cnt,
video_start_failures_technical_pct,
video_start_failures_technical_cnt,
total_playing_time_minutes,
total_playing_time_hours,
avg_minutes_per_play,
avg_pct_complete,
video_playback_failures_technical_pct,
video_playback_failures_technical_cnt,
exit_before_video_starts_pct,
exit_before_video_starts_cnt,
avg_video_start_time_sec,
high_startup_pct,
high_startup_cnt,
avg_rebuffering_ratio,
avg_connection_induced_rebuffering_ratio,
good_high_rebuffering_pct,
good_high_rebuffering_cnt,
best_high_rebuffering_pct,
best_high_rebuffering_cnt,
zero_cirr_plays_pct,
avg_bitrate_kbps,
good_low_bitrate_pct,
good_low_bitrate_cnt,
best_low_bitrate_pct,
best_low_bitrate_cnt,
avg_completed_time_sec,
stddev_completed_time_sec,
variance_completed_time_sec
from
agg2
where
is_quality_impacted = 1
and total_attempts >= 1000
and asset_name != 'Unknown' -- also excludes null
order by
country asc,
content_type asc,
device asc,
technical_failure_pct descRun the following SQL query to access the results in the quality_impacted_assets table. Use the report for further ingestion.
Copyselect * from quality_impacted_assetsType to search. Results appear as you type.
Recent -