Quality Impacted Assets (QIA) Report

 

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 Connect

Reach out to your Conviva Representative or contact Conviva Support.

Generating the QIA Report

1. Configure the report columns and filters.

   • Select the columns relevant to the QIA report.

   • For accounts with traffic rules, apply a filter to get the target sessions. For example, in the sample code, partner=conviva is applied to get the expected sessions.

   • Replace the default date week (2025-07-20) with a dynamic week parameter.

   • Adjust the Country filter as per the reporting needs.

   Copy

   %sql
   CREATE OR REPLACE TEMPORARY VIEW simple_ssd AS
     SELECT
         from_unixtime(StartTimeUnix) AS startTime,
         '2025-07-20' as week,
         initcap(Country) as country,
         SUBSTRING_INDEX(ConvivaSessionID, ":", 4) AS client_id,
         DeviceName as device,
         element_at(filter(sessionTags, tag -> tag.key = 'plt'), 1).value AS platform,
         DeviceHardwareType as screen_type,
         case when !isLive then 'VOD' when isLive then 'Live' else 'Unknown' end as content_type,
         case when (element_at(filter(sessionTags, tag -> tag.key = 'hasAds'), 1).value) = "true" 
              and (element_at(filter(sessionTags, tag -> tag.key = 'cusHasAds'), 1).value) = "true" 
              then TRUE else FALSE end as has_ads,
         AssetName AS asset_name,
         SessionTags AS tag_map,
         case when StartupTime>=0 then true else false END AS joined,
         CASE WHEN EndedStatus > 0 THEN true ELSE false END AS ended_session,
         CASE WHEN EndedStatus > 0 AND StartupTime>=0 THEN true ELSE false END AS ended_play,
         CASE WHEN EndedStatus > 0 AND StartupTime>=0 AND (!VPF) THEN 1 ELSE 0 END AS ended_successful_play,
         CASE WHEN !StartupError AND StartupTime = -1 THEN true ELSE false END AS is_ebvs,
         (VSFTechnical or VSFBusiness) AS is_vsf,
         VSFTechnical AS is_vsf_t,
         VSFBusiness AS is_vsf_b,
         VPF AS is_VPF,
         VPFTechnical AS is_vpf_t,
         VPFBusiness AS is_vpf_b,
         ReBufferingTime as buffering_time_ms, 
         CASE WHEN StartupTime>=0 then StartupTime else null END as join_time_ms,
         ConnectionInducedRebufferingTime AS network_buffering_time_ms,
         ROUND(ConnectionInducedRebufferingTime / (PlayingTime + ReBufferingTime), 4) AS network_rebuffering_ratio,
         CASE WHEN ConnectionInducedRebufferingTime = 0 AND EndedStatus > 0 AND StartupTime>=0 THEN 1 ELSE 0 END AS is_zero_cirr_ended_play,
         PlayingTime AS playing_time_ms,
         AverageBitRate AS bitrate,
         round(PercentageComplete/100,4) AS pct_complete,
         (EndTimeUnixMs - StartTimeUnixMs) AS session_time_ms,
         round(ContentLength/1000, 2) AS content_len
     FROM
         ssd
     WHERE
           lower(Country) not in ('anguilla', 'antigua and barbuda', 'aruba', 'bahamas', 'barbados', 'belize', 'bermuda', 'bolivia', 'caribbean netherlands', 'virgin islands (british)', 'cayman islands', 'costa rica', 'curacao', 'dominica', 'dominican republic', 'ecuador', 'el salvador', 'grenada', 'guatemala', 'guyana', 'haiti', 'honduras', 'jamaica', 'falkland islands (malvinas)', 'montserrat', 'nicaragua', 'panama', 'paraguay', 'south georgia and the south sandwich islands', 'saint kitts and nevis', 'saint lucia', 'saint vincent and the grenadines', 'suriname', 'virgin islands (u.s.)', 'trinidad and tobago', 'turks and caicos islands', 'uruguay', 'venezuela')
          AND EndedStatus > 0 
          AND date(from_unixtime(StartTimeUnix, "yyyy-MM-dd")) >= date('2025-07-20')
          AND size(filter(SessionTags, tag -> tag.key = 'partner' AND tag.value = 'conviva')) > 0

2. Exclude Ad Sessions to focus only on the relevant playback data.

   Copy

   create or replace temporary view base as
     select
       startTime,
       week,
       country,
       client_id,
       device,
       platform,
       screen_type,
       content_type,
       tag_map,
       has_ads,
       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,
       case when content_type = 'VOD' then round(pct_complete * content_len, 2) else null end as completed_time_sec
     from
       simple_ssd
     where
       element_at(filter(tag_map, tag -> tag.key = 'c3.video.isAd'), 1).value is null or (element_at(filter(tag_map, tag -> tag.key = 'c3.video.isAd'), 1).value = 'F')

3. Evaluate SPI Session Quality to determine whether sessions meet the SPI Good and SPI Best thresholds.

   Copy

   create or replace temporary view prep as
     select
       startTime,
       week,
       country,
       client_id,
       screen_type,
       content_type,
       platform,
       asset_name,
       joined,
       ended_session,
       ended_play,
       ended_successful_play,
       has_ads,
       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
       base

4. Compute SPI Metrics by aggregating the SPI data and calculate performance metrics across sessions.

   Copy

   create or replace temporary view agg as
     select,
       week,
       country,
       platform,
       content_type,
       asset_name,
       has_ads,
       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, 
       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,
       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,
       platform,
       content_type,
       asset_name,
       has_ads

5. Determine sessions that are impacted by technical failures:

   • Apply a threshold for technical failures impacted. The recommended benchmark is 10.

   • Use the parameter {technical_failures_impacted_threshold} and assign a proper value when executing.

   Copy

   create or replace temporary view agg2 as
     select
       week,
       country,
       platform,
       content_type,
       asset_name,
       has_ads,
       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
       agg

6. Compute quality-impacted assets.

   • Select sessions that reached the threshold value of the {technical_failures_impacted_threshold} parameter.

   • Use {minimum_attempts} as a parameter in the WHERE filter. Update the default value 1000 with the value needed for reporting.

   • Exclude null assets from the results.

   Copy

   create or replace temporary view quality_impacted_assets as
     select
       week,
       country,
       platform,
       content_type,
       asset_name,
       has_ads,
       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'
     order by
       country asc,
       platform asc,
       content_type asc,
       technical_failure_pct desc

7. Run the following SQL query to access the results in the quality_impacted_assets table. Use the QIA report for further ingestion.

   Copy

   SELECT * FROM quality_impacted_assets

 

QIA QIA QIA QIA Quality Impacted Assets Quality Impacted Assets Quality Impacted Assets Quality Impacted Assets Connect Report Connect Report Connect Report