SPI Calculation based on Content Summary
This document describes how to derive the Streaming Performance Index (SPI) data based on Content Summary reports of Conviva Connect.
SPI provides a visual indicator of your KPIs performance, allowing you to quickly assess the number and percentage of affected streams and their performance levels. A stream is impacted if it fails to meet one or more of the defined KPI settings.
Conviva formulates a unified streaming performance KPI based on the percentage of streaming sessions with good or best viewing experience. This KPI represents the Conviva Streaming Performance Index, and is based on the percentage of streams with:
-
No errors (VSF-T or VPF-T)
-
No or very low Rebuffering (using CIRR)
-
Acceptable picture quality based on average bitrate for different screen sizes
-
Acceptable Video Start Time
-
No EBVS if the viewer was waiting a long time before exiting.
Conviva provides KPI settings based on Good and Best performance. You can also set custom KPI settings to match your specific performance goals.
Good Performance KPI Settings
-
No Errors (VSF-T or VPF-T)
-
VST-T < 10sec
-
EBVS wait time < 10sec
-
Avg. Peak Bitrate > 800Kbps for TV screens
-
Avg. Peak Bitrate > 400Kbps for desktop or tablets
-
Avg. Peak Bitrate > 200Kbps for mobile devices screens
-
CIRR < 0.4%
-
CIRT < 2sec
Best Performance KPI Settings
-
No Errors (VSF-T or VPF-T)
-
VST < 10sec
-
EBVS wait time < 8sec
-
Avg. Peak Bitrate > 2Mbps for TV screens
Note: Encoding compression are not considered in the bit rates. -
Avg. Peak Bitrate > 800Kbps for desktop or tablets
-
Avg. Peak Bitrate > 400Kbps for mobile devices screens
-
CIRR < 0.02%
-
CIRT < 1sec
This example shows one approach for deriving SPI from your connect data.
-
Select and compute fields necessary for SPI calculation, such as startup time, average bitrate, and re-buffering.
create or replace temporary view simple_ssd as
select
case when StartupError = 'false' and StartupTime = -1 then true else false end as ebvs,
EndTimeUnix - StartTimeUnix as session_time_sec,
VSFTechnical as VSF,
VPFTechnical as VPF,
case when EndedStatus > 0 and PlayingTime > 0 then true else false end as ended_play,
StartupTime,
PlayingTime,
ReBufferingTime,
ConnectionInducedRebufferingTime,
AverageBitRate,
DeviceHardwareType
from
ssd-
Determine if each session is a valid SPI stream. SPI streams do not include non-ended plays, failures due to business errors, and non-abandonment EBVS.
create or replace temporary view valid_spi as
select
*,
(case when ended_play then ConnectionInducedRebufferingTime else 0 end) / (case when StartupTime > 0 or (StartupTime = -3 and PlayingTime >= 0) then ReBufferingTime + PlayingTime end) as cirr,
case
when ebvs and session_time_sec >= 10 then true
when VSF = 'true' then true
when VPF = 'true' then true
when ended_play then true
else false
end as good_spi_stream
case
when ebvs and session_time_sec >= 8 then true
when VSF = 'true' then true
when VPF = 'true' then true
when ended_play then true
else false
end as best_spi_stream
FROM
simple_ssd-
Compute whether the SPI sessions qualify against the SPI Good and Best threshold settings.
create or replace temporary view prep as
select
good_spi_stream,
best_spi_stream,
case
when ebvs and session_time_sec >= 10 then "Bad"
when VSF = 'true' then "Bad"
when VPF = 'true' then "Bad"
when ended_play
and
(
startup_time_ms >= 10000
or
(cirr > 0.004 and ConnectionInducedRebufferingTime >= 2000) -- calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.004 instead of 0.4
or
(
(AverageBitRate > 0 and AverageBitRate <= 200)
or
(DeviceHardwareType in ("Desktop","Tablet") and AverageBitRate > 0 and AverageBitRate <= 400)
or
(DeviceHardwareType in ("TV","Set Top Box","Games Console") and AverageBitRate > 0 and AverageBitRate <= 800)
)
) then "Bad"
end as good_spi_bad,
case
when ebvs and session_time_sec >= 8 then "Bad"
when VSF = 'true' then "Bad"
when VPF = 'true' then "Bad"
when ended_play
and
(
startup_time_ms >= 10000
or
(cirr > 0.0002 and ConnectionInducedRebufferingTime >= 1000) -- calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.0002 instead of 0.02
or
(
(AverageBitRate > 0 and AverageBitRate <= 400)
or
(DeviceHardwareType in ("Desktop","Tablet") and AverageBitRate > 0 and AverageBitRate <= 800)
or
(DeviceHardwareType in ("TV","Set Top Box","Games Console") and AverageBitRate > 0 and AverageBitRate <= 2000)
)
) then "Bad"
end as best_spi_bad
from
valid_spi-
Aggregate and total the session performance and compute the SPI scores.
select
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)), 4) * 100 as good_spi,
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)), 4) * 100 as best_spi
from
prep-- Select and compute fields necessary for SPI calculation from SSD
create or replace temporary view simple_ssd as
select
case when StartupError = 'false' and StartupTime = -1 then true else false end as ebvs,
EndTimeUnix - StartTimeUnix as session_time_sec,
VSFTechnical as VSF,
VPFTechnical as VPF,
case when EndedStatus > 0 and PlayingTime > 0 then true else false end as ended_play,
StartupTime,
PlayingTime,
ReBufferingTime,
ConnectionInducedRebufferingTime,
AverageBitRate,
DeviceHardwareType
from
ssd
-- Determine whether each session is an SPI stream.
-- SPI streams do not include non-ended plays, VPF-B, VSF-B, and non-abandonment EBVS.
-- This is because we cannot definitively say whether such sessions are good or bad.
create or replace temporary view valid_spi as
select
*,
(case when ended_play then ConnectionInducedRebufferingTime else 0 end) / (case when StartupTime > 0 or (StartupTime = -3 and PlayingTime >= 0) then ReBufferingTime + PlayingTime end) as cirr,
case
when ebvs and session_time_sec >= 10 then true
when VSF = 'true' then true
when VPF = 'true' then true
when ended_play then true
else false
end as good_spi_stream
case
when ebvs and session_time_sec >= 8 then true
when VSF = 'true' then true
when VPF = 'true' then true
when ended_play then true
else false
end as best_spi_stream
FROM
simple_ssd
-- Compute whether a session is good or bad based on Good and Best SPI thresholds.
create or replace temporary view prep as
select
good_spi_stream,
best_spi_stream,
case
when ebvs and session_time_sec >= 10 then "Bad"
when VSF = 'true' then "Bad"
when VPF = 'true' then "Bad"
when ended_play
and
(
startup_time_ms >= 10000
or
(cirr > 0.004 and ConnectionInducedRebufferingTime >= 2000) -- calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.004 instead of 0.4
or
(
(AverageBitRate > 0 and AverageBitRate <= 200)
or
(DeviceHardwareType in ("Desktop","Tablet") and AverageBitRate > 0 and AverageBitRate <= 400)
or
(DeviceHardwareType in ("TV","Set Top Box","Games Console") and AverageBitRate > 0 and AverageBitRate <= 800)
)
) then "Bad"
end as good_spi_bad,
case
when ebvs and session_time_sec >= 8 then "Bad"
when VSF = 'true' then "Bad"
when VPF = 'true' then "Bad"
when ended_play
and
(
startup_time_ms >= 10000
or
(cirr > 0.0002 and ConnectionInducedRebufferingTime >= 1000) -- calculate percentages as values between 0 and 1 instead of 0 and 100, so threshold is 0.0002 instead of 0.02
or
(
(AverageBitRate > 0 and AverageBitRate <= 400)
or
(DeviceHardwareType in ("Desktop","Tablet") and AverageBitRate > 0 and AverageBitRate <= 800)
or
(DeviceHardwareType in ("TV","Set Top Box","Games Console") and AverageBitRate > 0 and AverageBitRate <= 2000)
)
) then "Bad"
end as best_spi_bad
from
valid_spi
-- Compute aggregate SPI scores.
select
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)), 4) * 100 as good_spi,
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)), 4) * 100 as best_spi
from
prepType to search. Results appear as you type.