Ad SPI and Ad UpTime

In addition to individual ad metrics, Conviva enables an ad performance index and daily ad uptime percentage to report overall ad playback performance levels and focus on specific areas for ad playback performance improvements.

Ad SPI

The Conviva Ad Streaming Performance Index (Ad SPI) provides a quick highlight of the overall percentage of total ad session performance, based on meeting a combination of KPI threshold settings that define the performance details of good ad session playbacks. Only ad playback sessions that meet all of the criteria are consider to have good performance.

Ad SPI thresholds for good performance include:

• Ad Startup Time less than 2 seconds

• Ad Buffering Time less than 1% of the total ad duration

• Ad Average Bitrate greater than 500 Kbps

• Ad Completion Rate equal to 100% completion

• Ad Errors, no errors during ad playback

• No Exit Before Ad Start occurred

The basic logic for Ad SPI calculation determines the number of good ad playback sessions and divides that number by the number of total sessions.

# Initialize counters
good_sessions = 0
total_sessions = 0
# Iterate through ad sessions
for session in ad_sessions:
    if (
        session.startup_time < 2 and  # Check for acceptable Ad Startup Time
        session.buffering_time / session.duration < 0.01 and  # Check for minimal Ad Buffering Time
        session.average_bitrate > 500 and  # Check for sufficient Ad Average Bitrate
        session.completion and  # Check for Ad Completion
        not session.errors and  # Check for No Errors (APF or ASF)
        not session.ebas  # Check for No Exit Before Ad Start (EBAS)
    ):
        good_sessions += 1  # Increment good sessions counter
        total_sessions += 1  # Increment total sessions counter
# Calculate Ad SPI
ad_spi = (good_sessions / total_sessions) * 100  # Calculate Ad SPI as a percentage

Ad Uptime

Conviva's Ad Uptime metric highlights the percentage of days during which all ad uptime performance thresholds were met, establishing performance benchmarks for maintaining a high-level of successful ad session playbacks.

Suggested Ad Uptime thresholds include:

• Ad Impressions greater than or equal to 99.1

• Ad Completion greater than 82

• Ad Playback Failures less than or equal to 25%

• Ad Start Failures less than or equal to 6%

• Ad Rebuffering Ratio less than or equal to 18%

• Ad Startup Time less than or equal to 8 one-hundredths of a second

Basic logic for Ad Uptime calculation determines the number of uptime days divided by the total days.

# Initialize counters
uptime_days = 0
total_days = 0
# Iterate through daily metrics
for day in daily_metrics:
    if (
        day.impressions >= 99.1 and  # Check for sufficient Ad Impressions
        day.completion >= 82 and  # Check for sufficient Ads Completed
        day.playback_failures <= 0.25 and  # Check for minimal Ad Playback Failures
        day.start_failures <= 0.06 and  # Check for minimal Ad Start Failures
        day.rebuffering_ratio <= 0.18 and  # Check for minimal Ad Rebuffering Ratio
        day.startup_time <= 0.08  # Check for acceptable Ad Startup Time
    ):
        uptime_days += 1  # Increment uptime days counter
    total_days += 1  # Increment total days counter
# Calculate Ad Uptime
ad_uptime = (uptime_days / total_days) * 100  # Calculate Ad Uptime as a percentage

Example SQL to Calculate AD SPI and Ad Uptime

Extract SSD Data

-- Create a temporary view with all necessary columns for metric calculations
create or replace temporary view simple_adssd as
  select
    Conviva_content_session_id,  -- Unique session identifier
    from_unixtime(`start_time(unix_time)`) as start_time,  -- Convert start time to readable format
    from_unixtime(`end_time(unix_time)`) as end_time,      -- Convert end time to readable format
    (`end_time(unix_time)` - `start_time(unix_time)` ) as session_time_sec,  -- Session duration in seconds
    case when ended_status > 0 and `actual_duration(ms)` > 0 then true else false end as ended_play,  -- Did the ad play to end?
    case when `actual_duration(ms)`>0 then true else false end as impression,  -- Was there an impression?
    round(`startup_time(ms)`/1000,2) as startupTimeSec,  -- Startup time in seconds
    `buffering_time(ms)` as bufferingTime,               -- Buffering time in ms
    `average_bitrate(kbps)` as avgBitRate,               -- Average bitrate in kbps
    -- Error flags
    --[APF is not available for legacy Ad SSD,please remove it for legacy Ad SSD;please reference this to get the metric from connect SSD;]
    case when (size(VideoPlaybackFailureErrorsTech)!=0 or size(VideoPlaybackFailureErrorsBusiness)!=0) then true else false end as APF,  -- Playback failure
    case when `startup_time(ms)`=-1 and `actual_duration(ms)`=0 and startup_error=1 then true else false end as ASF,  -- Ad start failure
    case when `startup_time(ms)`=-1 and `actual_duration(ms)`=0 and startup_error=0 then true else false end as EBAS, -- Exit before ad start
    `planned_duration(ms)` as contentLength,             -- Planned ad duration
    `actual_duration(ms)` as playingTime,                -- Actual ad play time
    -- Ad completion logic
    case 
      WHEN impression and (contentLength is null or contentLength=-1) THEN 0
      when impression and PlayingTime>=contentLength then 1
      when impression and contentLength>0 and PlayingTime<contentLength then round(playingTime*1.0/contentLength,0)  
      else null end as AdCompleted,
    round(bufferingTime / (playingTime+bufferingTime),2) as bufferingRatio,  -- Buffering ratio
    `startup_error` as startupError,  -- Startup error flag
    ended_status as endedStatus       -- Ended status
  from
    ad_ssd

Calculate Ad SPI

-- Create a temporary view to calculate Ad SPI
-- Ad SPI Stream Calculation
CREATE OR REPLACE TEMPORARY VIEW ad_spi AS
SELECT
    Conviva_content_session_id,
    start_time,
    startupTimeSec,
    bufferingRatio,
    avgBitRate,
    endedStatus,
    --[APF is not available for legacy Ad SSD,please remove it for legacy Ad SSD;please reference this to get the metric from connect SSD;]
    APF,
    ASF,
    EBAS,
    (CASE WHEN 
        startupTimeSec < 2 AND  
        bufferingRatio < 0.01 AND  
        avgBitRate > 500 AND  
        endedStatus > 0 AND  
        !APF AND --[APF is not available for legacy Ad SSD,please remove it for legacy Ad SSD;please reference this to get the metric from connect SSD;]
        !ASF AND !EBAS   
        -- ExitDuringPreRoll is not available in Ad SSD
    THEN true else false END) AS good_spi
FROM
    simple_adssd
    
    
-- Overall Ad SPI
select count(*) as AdAttempts, 
sum(case when good_spi then 1 else 0 end) as good_spi_cnt, 
round(good_spi_cnt*100/AdAttempts,2) as good_spi_rate
from ad_spi

Calculate Ad UpTime

-- Aggregate daily metrics needed for uptime calculation
SELECT 
  COUNT(*) AS AdAttempts,  -- Total ad sessions
  ROUND(SUM(CASE WHEN impression THEN 1 ELSE 0 END) * 1.0 / AdAttempts, 4) AS impressionsRate,  -- Impressions rate
  round(avg(AdCompleted),4)*100 AS AdCompleted,  -- Completion rate
  SUM(CASE WHEN ended_play THEN 1 ELSE 0 END) AS ended_play_cnt,  -- Ended play count
  --[APF is not available for legacy Ad SSD,please remove it for legacy Ad SSD;please reference this to get the metric from connect SSD;]
  ROUND(SUM(CASE WHEN APF THEN 1 ELSE 0 END) * 1.0 / NULLIF(SUM(CASE WHEN ended_play THEN 1 ELSE 0 END), 0), 4) AS APF_ratio,  -- Playback failure ratio
  SUM(CASE WHEN ASF THEN 1 ELSE 0 END) AS ASF_cnt,  -- Start failure count
  ROUND(SUM(CASE WHEN ASF THEN 1 ELSE 0 END) * 100 / COUNT(*), 2) AS ASF_ratio,  -- Start failure ratio
  round(avg(bufferingRatio),2) AS avgBufferingRatio,  -- Average buffering ratio
  ROUND(AVG(CASE WHEN startupTimeSec > 0 THEN startupTimeSec ELSE 0 END), 2) AS StartupTime_sec  -- Average startup time
FROM 
  simple_adssd;
  
  --Ad Uptime Calculation
  -- Create a temporary view to calculate Ad Uptime
CREATE OR REPLACE TEMPORARY VIEW ad_uptime_calculation AS
SELECT
    COUNT(CASE WHEN 
        impressionsRate*100 >= 99.1 AND  -- Check for sufficient Ad Impressions
        AdCompleted >= 82 AND  -- Check for sufficient Ads Completed
        --[APF is not available for legacy Ad SSD,please remove it for legacy Ad SSD;please reference this to get the metric from connect SSD;]
        APF_ratio <= 0.25 AND  -- Check for minimal Ad Playback Failures 
        ASF_ratio <= 0.06 AND  -- Check for minimal Ad Start Failures
        avgBufferingRatio <= 0.18 AND  -- Check for minimal Ad Rebuffering Ratio
        StartupTime_sec <= 0.08  -- Check for acceptable Ad Startup Time
    THEN 1 END) AS uptime_days,
    COUNT(*) AS total_days
FROM
    ad_ssd_daily_metrics;
-- Calculate Ad Uptime
SELECT 
    (uptime_days / total_days) * 100 AS ad_uptime_percentage
FROM 
    ad_uptime_calculation;