Content Summary Data

This document describes how to use Conviva Connect Content Summary reports. The Content Summary report is a daily offline historical log that provides session-level information for every video play or attempted play in a given day. Download an anonymized sample daily Connect file to see the delivered CSV format.

Audience

The Content Summary report is beneficial to many critical business departments, but it's mainly used by:

• Business analysts
• Operations teams
• Research teams

How to use Conviva Connect Fields?

For the complete field dictionary, see the Video Session Feed schema and column dictionary.

You can use the Conviva Connect field data to:

• Filter against a particular metadata field in the Conviva Connect file to identify issues or patterns in a set of sessions.

• Filter against a particular Conviva Connect metric across one or many days.

• Calculate key experience and engagement metrics (refer to the Metrics section).

• Define and shape business KPIs (refer to the KPIs section).

• Map a user journey: You can use a common session ID across multiple data sources (from Conviva Connect and other logs or feeds) to track the user journey through various systems such as site or content discovery, page load, player load, CMS request, DRM request, ad request, CDN request, playback experience, next page or content viewed, and app crashes.

• Analyze viewer usage or consumption patterns: By tracking how users are engaging with content, you can research trends and patterns within viewing habits. This information can be used in content licensing or production decisions.

You can also define custom metadata to focus your queries on your specific business needs. This processed information is intended for analysis using a third-party tool.

Get the Conviva Connect Reports

The supported delivery destinations are:

• Google Cloud Storage Bucket

• Amazon S3 Bucket

• SFTP

The file path is:

[c3 account name]/[file type]/[date of data]/part-*

Data is delivered in multi-part files; part-* is used to enumerate the files.

The Conviva Connect file name is in the format:

DailyContentSessionLog_<CUSTOMER_NAME>_<YYYY-MM-DD>.csv

Tools to Use

Typically, users load Conviva Connect into a database such as MySQL or into analytics tools such as Tableau or Micro Strategy to analyze the data and correlate it with other data sets. Refer to the sample SQL queries section for examples.

def decode_pct_encoding(s):
    import urllib
    if s is None:
        return "Unknown"
    else:
         return urllib.parse.unquote_plus(s)
spark.udf.register("decode_pct_encoding", decode_pct_encoding)

Get Started with Content Summary Reports

1. Define Conviva Sessions

Each row in the Conviva Connect file describes a video session. Conviva defines a Session as an instance of an attempt to play a video; the attempt can be an explicit viewer action or an implicit automatic player action. In Video on Demand (VOD), the attempt would typically align with a specific asset; in linear live, the attempt may correspond to multiple played assets within the same session.

Each session in Conviva Connect has a ConvivaSessionID, which is a string consisting of five segments. For example,

8025681:1708681910:125006299:1630859473:4109428019

The first four segments (in italic) represent the Client ID, which is assigned by Conviva to each device. The fifth segment (underlined) represents the Session ID.

A session that is suspended and resumed will appear as different rows with the same ConvivaSessionID. One row will have the session start time; the other row(s) will have the session resume time(s). In this case, the primary key to process the sessions is Conviva Session ID + Session Start Time.


2. Define Lifetime Sessions

A Conviva Connect file contains snapshots of each lifetime session. A lifetime session starts on or before the pipeline end date and terminates on the pipeline end date (PED).

Example

Consider three lifetime sessions (with abbreviated IDs) from a Conviva Connect file with a December 16 pipeline end date:

1. First session (ID: 1001) starts and ends on December 16.

2. Second session (1002) starts on December 16 and crosses the midnight boundary into the next day (indicated by EndedStatus = 0).

3. Third row (ID: 1003) starts on December 15 and ends on December 16.

   Three lifetime metrics are shown: Playing Time, Connection Induced Rebuffering Ratio (CIRR), and Attempt. Note that because these are lifetime sessions, the third session (ID: 1003) includes Playing Time prior to December 16. All sessions are flagged as attempts because this status persists throughout the lifetime of the session.

Conviva Session ID	Start Time	End Time	Playing Time (seconds)	CIRR	Attempt	Ended Status
1001	2022-12-16 18:38:10	2022-12-16 18:59:10	1250	10	true	1
1002	2022-12-16 23:59:37	2022-12-16 23:59:59	20	0	true	0
1003	2022-12-15 23:54:00	2022-12-16 00:10:00	950	5	true	1

3. Lifetime vs Interval Metrics

Lifetime metrics report a session's totals over its full lifespan and are delivered in the daily feed, which is most useful once a session has ended. Interval metrics report a session's totals as of a given interval (the hour) and are delivered in the hourly feed, which is used to track sessions while they are still ongoing. If you compare daily feed numbers with a Pulse dashboard, the difference is that Pulse aggregates over its chosen interval while the daily feed reports lifetime totals per session; align the windows and the two converge. Refer to the Frequently Asked Questions section for further details. To derive interval (day) metrics from the daily lifetime feed, refer to examples given in the Calculating Interval (day) Metrics section.

Metrics List

Metric Definitions and Calculations

In most cases, lifetime metrics at the session level are provided in the Conviva Connect dataset. In cases where lifetime metrics at the session level are not provided, this section includes examples of the formulas and SQL code. The section below also shows how to aggregate the lifetime metrics across sessions. The SQL code is based on Conviva Connect date with December 16 (2022) pipeline end date.

Core Metrics

1. Attempts

Definition: An attempt is initiated when a viewer clicks play or a video auto-plays. An attempt can result in a successful play, or an early termination due to Video Start Failures (VSF) or Exits Before Video Start (EBVS). In Conviva Connect, each unique ConvivaSessionID + StartTimeUnix is an attempt. Across all sessions, the total number of Attempts is:



SELECT
    COUNT(*) as Attempts
FROM
    Demo

2. Plays

Definition: A Play is a session with Playing Time greater than zero, which indicates that at least one video frame was played during the session lifetime.


There are two ways to aggregate the Plays. The first is a count of all sessions with PlayingTime > 0 milliseconds:



SELECT
    COUNT(*) as Plays
FROM
    Demo
WHERE
    PlayingTime > 0

The second way is to count all session attempts that didn’t have a failure like VSF or EBVS:



In some cases, the player may report a joined state, but the user might have exited before the first video frame was played. Joined time is indicated by StartupTime > 0. If the session joined but we don't have the actual join time, then we display -3 for StartupTime. Therefore, to aggregate Plays, you can include sessions where StartupTime = -3 and PlayingTime >= 0. See also What does VST = -3 stand for? in the FAQ section.



-- Alternative method
SELECT
    COUNT(*) as Plays
FROM
    Demo
WHERE
    StartupTime > 0
or  (
        StartupTime = -3
    and PlayingTime >= 0)

QoE Metrics

3. Video Startup Failures (VSF)

Definition: VSF indicates if a video failed to play due to a startup error. This metric is available in Conviva Connect but is called StartupError. You can find the list of associated errors in the ErrorList field.

Across all sessions, the VSF % can be aggregated using the Attempts metric:




SELECT
    (VSF * 100.0 / Attempts) as VSF_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    StartupError = 'true'
                then
                    1
                else
                    0
                END) as VSF,
            COUNT(*) as Attempts
        FROM
            Demo)

Note: Conviva lists all the VSF errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

4. Video Startup Failures Business (VSF-B)

Definition: VSF-B indicates if a video failed to play due to a business error. Find the list of associated errors in the VSFBusinessErrorList field.

Across all sessions, the VSF-B % can be aggregated using the Attempts metric:



SELECT
    (VSFB * 100.0 / Attempts) as VSFB_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    VSFBusiness = 'true'
                then
                    1
                else
                    0
                END) as VSFB,
            COUNT(*) as Attempts
        FROM
            Demo)

Note: Conviva lists all the VSF-B errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

5. Video Startup Failures Technical (VSF-T)

Definition: VSF-T indicates if a video failed to play due to a technical error. Find the list of associated errors in the VSFTechnicalErrorList field.

Across all sessions, the VSF-T % can be aggregated using the Attempts metric:



SELECT
    (VSFT * 100.0 / Attempts) as VSFT_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    VSFTechnical = 'true'
                then
                    1
                else
                    0
                END) as VSFT,
            COUNT(*) as Attempts
        FROM
            Demo)

Note: Conviva lists all the VSF-T errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

6. Video Playback Failures (VPF)

Definition: VPF indicates if the video play terminates due to a playback error. VPFs are an important measurement of service quality and audience engagement, especially when a large percentage of plays terminate due to VPF. Find the list of associated errors in the VPFErrorList field.

Across all sessions, the VPF % can be aggregated using the EndedPlays metric:



SELECT
    (VPF * 100.0 / EndedPlays) as VPF_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    VPF = 'true'
                then
                    1
                else
                    0
                END) as VPF,
            COUNT(*) as EndedPlays
        FROM
            Demo
        WHERE
            PlayingTime > 0
        and EndedStatus > 0)

Note: Conviva lists all the VPF errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

7. Video Playback Failures Business (VPF-B)

Definition: VPF-B indicates if the video play terminates due to a business playback error. VPF-Bs are an important measurement of service quality and audience engagement, especially when a large percentage of plays terminate due to VPF-B. Find the list of associated errors in the VPFBusinessErrorList field.

Across all sessions, the VPF-B % can be aggregated using the EndedPlays metric:



SELECT
    (VPFB * 100.0 / EndedPlays) as VPFB_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    VPFBusiness = 'true'
                then
                    1
                else
                    0
                END) as VPFB,
            COUNT(*) as EndedPlays
        FROM
            Demo
        WHERE
            PlayingTime > 0
        and EndedStatus > 0)

Note: Conviva lists all the VPF-B errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

8. Video Playback Failures Technical (VPF-T)

Definition: VPF-T indicates if the video play terminated due to a technical playback error. VPF-Ts are an important measurement of service quality and audience engagement, especially when a large percentage of plays terminate due to VPF-T. Find the list of associated errors in the VPFTechnicalErrorList field.

Across all sessions, the VPF-T % can be aggregated using the EndedPlays metric:



SELECT
    (VPFT * 100.0 / EndedPlays) as VPFT_Perc
FROM
    (
        SELECT
            SUM (
                case
                WHEN
                    VPFTechnical = 'true'
                then
                    1
                else
                    0
                END) as VPFT,
            COUNT(*) as EndedPlays
        FROM
            Demo
        WHERE
            PlayingTime > 0
        and EndedStatus > 0)

Note: Conviva lists all the VPF-T errors the player reports within 90 seconds of the failure in the order in which they were reported. Modifying the player error reporting and error message text can help to clarify which error caused the failure.

9. Exits Before Video Start (EBVS)

Definition: EBVS measures the number of viewing attempts that were terminated, typically by the viewer, before the video started. If an error is not generated, we count the failed attempt as an EBVS. An EBVS is similar to a VSF (failure to play video), but an EBVS does not include a descriptive error message.

The EBVS for each session needs to be computed from the Conviva Connect data.



Across all sessions, the EBVS % can be aggregated using the Attempts metric:



SELECT
    (EBVS * 100.0 / Attempts) as EBVS_Perc
FROM
    (
        SELECT
            SUM (
                CASE
                WHEN
                    StartupError    = 'false'
                    and PlayingTime = 0
                    and StartupTime = -1
                then
                    1
                else
                    0
                END) as EBVS,
            COUNT(*) as Attempts
        FROM
            Demo)

An alternative way to calculate EBVS is:



10. Rebuffering Ratio

Definition: Rebuffering Ratio shows the percentage of buffering during Playing Time. From this metric, we exclude the initial player startup buffering time (before the first video frame).

Across all sessions, the Rebuffering Ratio % can be aggregated as:



SELECT
    (TotalBufTime * 100.0 / (TotalBufTime + TotalPlayTime)) as RebufferingRatio_Perc
FROM
    (
        SELECT
            SUM(BufferingTime) as TotalBufTime,
            SUM(PlayingTime)   as TotalPlayTime
        FROM
            Demo
        WHERE
            PlayingTime > 0)

11. Connection Induced Rebuffering Ratio (CIRR)

Definition: Rebuffering occurs when video play must wait for the buffer to fill because there's insufficient buffered video. CIRR is rebuffering unrelated to user-initiated seeks, but potentially caused by network conditions.

Across all sessions, the CIRR % is calculated using the ConnectionInducedRebufferingTime, BufferingTime, and PlayingTime fields in Conviva Connect:



SELECT
    COUNT(*) as Plays
FROM
    Demo
WHERE
    StartupTime > 0
or  (
        StartupTime = -3
    and PlayingTime >= 0)

Note: The Zero CIRR Ended Plays % metric can be derived by identifying the sessions that played without CIRR and calculating that percentage of sessions from the total sessions that played.

12. Average Peak Bitrate

Definition: The average bitrate in kilobytes per second of delivered content across the lifetime session, as derived from the bandwidth attribute in the player manifest file. Some players may not report the bandwidth attribute.

Across all sessions, the Average Peak Bitrate can be aggregated as:



-- Weight each session bitrate by its playing time
SELECT
    (TotalBits * 1.0 / TotalPlayTimeSec) as AggAveragePeakBitRate
FROM
    (
        SELECT
            SUM(AverageBitRate * PlayingTime) as TotalBits,
            SUM(PlayingTime) TotalPlayTimeSec
        FROM
            Demo
        WHERE
            PlayingTime > 0)

13. Avg Average Bitrate

    Definition: The average bitrate in kilobytes per second of delivered content across the lifetime session, as derived from the average bandwidth attribute in the player manifest file. It represents the time-weighted average bitrates played by the player. Because some manifest files do not report average bandwith, the Avg Average Bitrate field tends to have more zero values than the Average Peak Bitrate field.

    Across all sessions, the Avg Average Bitrate can be aggregated as:

    

    -- Weight each session bitrate by its playing time
    SELECT
        (TotalBits * 1.0 / TotalPlayTimeSec) as AggAvgAverageBitRate
    FROM
        (
            SELECT
                SUM(AvgAverageBitRate * PlayingTime) as TotalBits,
                SUM(PlayingTime) TotalPlayTimeSec
            FROM
                Demo
            WHERE
                PlayingTime > 0)

14. Session Duration

    Definition: Session Duration is the total time in seconds from the session start to the session end. It is calculated as:

    
    

SELECT
    EndTimeUnix - StartTimeUnix as SessionDuration
FROM
    Demo

Bandwidth

Definition: Bandwidth is the volume of data transferred, in megabytes (MB), over the lifetime of the session. The query below averages that volume across sessions.

Across all sessions, the Bandwidth can be aggregated as:



-- PlayingTime is milliseconds and AverageBitRate is kilobits per second
-- Dividing the product by 8,000,000 gives megabytes
SELECT
    SUM(Bandwidth) / COUNT(*) as Bandwidth
FROM
    (
        SELECT
            PlayingTime,
            PlayingTime * AverageBitRate / 8000000.0 as Bandwidth
        FROM
            Demo
        WHERE
            PlayingTime > 0)

Video Startup Time (VST)

Definition: VST is the number of seconds between the start of the Conviva monitoring (i.e. Session Start) and the first played video frame. We exclude any time trying to play or playing ads.

Across all sessions, VST can be aggregated using the StartUpTime value:



SELECT
    ROUND(TotalStartupTimeSec / Plays, 2) as StartupTimeSec
FROM
    (
        SELECT
            SUM(StartupTime) / 1000.0 as TotalStartupTimeSec,
            COUNT(*)                as Plays
        FROM
            Demo
        WHERE
            StartUpTime >= 0)

Video Restart Time (VRT)

Definition: VRT is the amount of time in seconds after seeking is completed by the user until the video begins playing.

Across all sessions, the VRT can be aggregated as:



SELECT
    ROUND(TotalRestartTimeSec / Plays, 2) as RestartTimeSec
FROM
    (
        SELECT
            SUM(VideoRestartTime) / 1000.0 as TotalRestartTimeSec,
            COUNT(*)                     as Plays
        FROM
            Demo
        WHERE
            PlayingTime      > 0
        AND VideoRestartTime != -1 )

Micro Playing Time

Definition: The total time in milliseconds that a session spent in continuous play time that is less than 200 milliseconds. Sometimes, the player reports false play duration; this time is excluded from the Playing Time.

Across all sessions, the Micro Playing Time can be aggregated as:



SELECT
    SUM(MicroplayingTime) as MicroplayingTimeMs
FROM
    Demo
WHERE
    PlayingTime > 0

Micro Playing Interrupts

Definition: The total number of times a session spent in continuous play time that is less than 200 milliseconds. Sometimes, the player reports false play duration; this time is excluded from the Playing Time.

Across all sessions, Micro Playing Interrupts can be aggregated as:



SELECT
    SUM(MicroplayingInterruptions) as MicroplayingInterrupts
FROM
    Demo
WHERE
    PlayingTime > 0

Micro Buffering Time

Definition: The total time in milliseconds that a session spent in continuous buffering that is less than 200 milliseconds. Micro buffering could result in jittering due to the video playback; this is not excluded from the session's buffering time.

Across all sessions, Micro Buffering Time can be aggregated as:



SELECT
    SUM(MicroBufferingTime) as MicroBufferingTimeMs
FROM
    Demo
WHERE
    PlayingTime > 0

Micro Buffering Interrupts

Definition: The total count of number of times a session spent in continuous buffering that is less than 200 milliseconds. Micro buffering could result in jittering due to the video playback; this is not excluded from the session's buffering time.

Across all sessions, Micro Buffering Interrupts can be aggregated as:



SELECT
    SUM(MicroBufferingInterruptions) as MicroBufferingInterrupts
FROM
    Demo
WHERE
    PlayingTime > 0

Long Rebuffering Time

Definition: The total time in milliseconds that a session spent in continuous buffering that is more than 90 seconds. Long buffering occurs when a player is stuck in a buffering state; this is excluded from the session's buffering time.

Across all sessions, Long Rebuffering Time can be aggregated as:



SELECT
    SUM(LongRebufferingTime) as LongRebufferingTimeMs
FROM
    Demo
WHERE
    PlayingTime > 0

Long Rebuffering Interrupts

Definition: The number of times a session spent in continuous buffering that is more than 90 seconds. Long buffering occurs when a player is stuck in a buffering state; this is excluded from the session's buffering time.

Across all sessions, Long Rebuffering Interrupts can be aggregated as:



SELECT
    SUM(LongRebufferingInterruptions) as LongRebufferingInterrupts
FROM
    Demo
WHERE
    PlayingTime > 0

Abandonment

Definition: Abandonment occurs when a viewer exits a video before the video start with a wait time greater than 10 seconds (SPI setting defined as Good) or 8 seconds (SPI setting defined as Best). At the session level, the Abandonment metric can be calculated as:




SELECT
    CASE
    WHEN
        (StartUpTime           = -1
        and StartupError       = 0
        and PlayingTime        = 0
        and SessionDurationSec > 10)
    then
        1
    else
        0
    END as Abandonment10Sec
FROM
    (
        SELECT
            StartUpTime ,
            StartupError,
            PlayingTime ,
            (EndTimeUnix - StartTimeUnix) as SessionDurationSec
        FROM
            Demo)


Across all sessions, Abandonment % can be aggregated as:



SELECT
    (SUM(Abandonment10Sec) * 100.0 / Count(*)) as Abandonment10Sec_Perc
FROM
    (
        SELECT
            CASE
            WHEN
                (StartUpTime           = -1
                and StartupError       = 0
                and PlayingTime        = 0
                and SessionDurationSec > 10)
            then
                1
            else
                0
            END as Abandonment10Sec
        FROM
            (
                SELECT
                    StartUpTime ,
                    StartupError,
                    PlayingTime ,
                    (EndTimeUnix - StartTimeUnix) as SessionDurationSec
                FROM
                    Demo) )

A second approach more closely matches the SPI Streams calculation on SEI. This calculation excludes from the denominator sessions that:

a) exited before the video started and the viewer did not wait a significant time (e.g. 8 seconds).

b) sessions impacted by business logic errors.

SELECT
    (SUM(Abandonment10Sec) * 100.0 / (COUNT(*) - SUM(BusinessError))) as Abandonment_Perc
FROM
    (
        SELECT
            CASE
            WHEN
                (StartUpTime           = -1
                and StartupError       = 0
                and PlayingTime        = 0
                and SessionDurationSec > 10)
            then
                1
            else
                0
            END as Abandonment10Sec,
            CASE
            WHEN
                (SessionDurationSec < 8
                or VSFBusiness      = true
                or VPFBusiness      = true)
            then
                1
            END as BusinessError
        FROM
            (
                SELECT
                    StartUpTime                                        ,
                    StartupError                                       ,
                    PlayingTime                                        ,
                    (EndTimeUnix - StartTimeUnix) as SessionDurationSec,
                    VSFBusiness                                        ,
                    VPFBusiness
                FROM
                    Demo))

Paused Time

Definition: Paused Time shows the total time in milliseconds the viewer paused during the session. A pause occurs when the viewer hits the pause button.

Across all sessions, the Paused Time can be aggregated as:



SELECT
    AVG(PausedTime) as AggPausedTimeMs
FROM
    Demo

Paused Ratio

Definition: Paused Ratio gives the paused time as a ratio of the total playing time, including rebuffering and pauses. This metric helps you understand the impact of total pause time during your video sessions.

Across all sessions, the Paused Ratio can be aggregated using the EndedPlays metric:



SELECT
    SUM(PausedTime) * 1.0 / SUM(PausedTime + PlayingTime + BufferingTime) as AggPausedRatio
FROM
    Demo
WHERE
    EndedStatus > 0
and PlayingTime > 0

Last Playhead Time

Definition: The time in milliseconds of the last playhead position. This metric is available only at the session level.

Bitrate Switches

Definition: A bitrate switch occurs whenever a change in bitrate is detected. The Bitrate Switches metric displays the number of the bitrate switches over a lifetime session.

Across all sessions, the average number of Bitrate Switches can be aggregated using the EndedPlays metric:



SELECT
    AVG(NumBitrateSwitches) as AggNumBitRateSwitches
FROM
    Demo
WHERE
    EndedStatus > 0
and PlayingTime > 0

CIR Related Exit

Definition: An exit that occurs during connection induced rebuffering (and not due to seek) within 5 seconds before the session end.

Across all sessions, CIRRelatedExits % can be aggregated using the EndedPlays metric:



SELECT
    (CIRRelatedExits * 100.0 / EndedPlays) as CIRRelatedExits_Perc
FROM
    ( SUM(
        CASE
        WHEN
            CIRRelatedExit = 'true'
        then
            1
        END) as CIRRelatedExits, COUNT(*) as EndedPlays FROM Demo WHERE EndedStatus > 0
    and PlayingTime > 0)

Engagement Metrics

Ended Plays

Definition: An Ended Play is a session with a Play (PlayingTimeMs > 0) that ended (EndedStatus > 0) during the selected time frame, in this case the day (for example, December 16) of the Conviva sessions data.

Across all sessions, the number of Ended Plays can be aggregated as:






SELECT
    Count(*) as EndedPlays
FROM
    Demo
WHERE
    PlayingTime > 0
and EndedStatus > 0

Unique Devices

Definition: Unique Devices counts the total number of devices that had any Ended Plays during the during the selected timeframe, in this case the pipeline end date (for example, December 16) of the Conviva sessions data. A unique device is not equivalent to a unique person. If a person uses multiple devices, each device is counted as Unique Devices. If there are more than one applications offered to the same device, the Client ID will be identified for each application separately. This Client ID is represented by the first four components of the Conviva Session ID, as explained here.

Across all sessions, Unique Devices can be aggregated using the EndedPlays metric:



SELECT
    COUNT(DISTINCT(CONCAT(ID[0], ":", ID[1], ":", ID[2], ":", ID[3]))) as UniqueDevices
FROM
    (
        -- first split sessionID into components, then construct clientID from first four
        SELECT
            SPLIT(ConvivaSessionID, ":") as ID
        FROM
            Demo
        WHERE
            PlayingTime > 0
        and EndedStatus > 0)

Minutes Ended Play

Definition: The Minutes Ended Plays metric is calculated by dividing the total amount of Playing Time minutes by the number of Ended Plays during the selected timeframe, in this case the pipeline end date (for example, December 16).

Across all sessions, Minutes Ended Plays can be aggregated using the EndedPlays metric:



SELECT
    TotalPlayTimeMin / TotalEndedPlays as MinuteEndedPlay
FROM
    (
        SELECT
            -- convert millisec to minutes
            SUM(PlayingTime) / 60000.0 as TotalPlayTimeMin,
            COUNT(*)                       as TotalEndedPlays
        FROM
            Demo
        WHERE
            PlayingTime > 0
        and EndedStatus > 0)

Average % Complete

Definition: Average % Complete shows the amount of viewed play duration compared with the total length of the content. A high % indicates a high level of viewer engagement with the asset, channel, and service.



The field PercentageComplete exists at the session level, which can be used to calculate the Average % Complete across all sessions.

SELECT
    AVG(PercentageComplete) as AvePercComplete
FROM
    Demo
WHERE
    PercentageComplete >= 0

Calculating Interval (day) Metrics

It is not always possible to directly calculate interval metrics from the Conviva Connect lifetime sessions. In this section, we show how to calculate interval based metrics from lifetime sessions using the example from the Get Started with Content Summary Reports section. In this dataset, the third session (ID: 1003) spans two days, which means that we cannot infer the exact Playing Time and CIRR for the December 16 day-interval (00:00:00 to 23:59:59) using the December 16 pipeline end date. One solution is to obtain a dataset using the December 15 pipeline end date. For example, the following dataset has only one session (ID: 1003) because the other sessions start on the next day.

To calculate Playing Time and CIRR for the December 16 day-interval, subtract the respective lifetime metric ending on December 15 from the lifetime metric ending on December 16. So for the December 16 day-interval, intvPlayingTime = 950 - 355 = 595 and intvCIRR = 5 - 2 = 3. Similarly, for ConvivaSessionIDs 1001 and 1002, intvAttempt = true since both sessions started on this day whereas the third session (ID: 1003) did not, hence, intvAttempt = false.

Sample metrics for the December 16 day-interval:

The SQL code shows one way to do this interval calculation, where Demo16Dec is the December 16 pipeline end date and Demo15Dec is the December 15 pipeline end date.

SELECT
    L.ConvivaSessionID                                              ,
    L.PlayingTime                                                   ,
    L.CIRR                                                          ,
    L.PlayingTime - IFNULL(P.PlayingTimePrior, 0) as IntvPlayingTime,
    L.CIRR        - IFNULL(P.CIRRPrior, 0)        as IntvCIRR
FROM
    Demo16Dec L
LEFT OUTER JOIN
    Demo15Dec P
ON
    (
        L.ConvivaSessionId = P.ConvivaSessionID)

To determine an attempt for the 16 December day-interval, we use the Demo16Dec data and the StartTimeUnix field:

SELECT
    CASE
        (
    WHEN
        FROM_UNIXTIME(StartTimeUnix, 'y-MM-dd') = "2022-12-16"
    then
        1
    else
        0
    END) as intvAttempt
FROM
    Demo

To calculate the interval-based metrics such as Plays, Video Startup Failure, Video Restart Time, and others, follow the same logic described above. Please contact Conviva support if needed.

KPIs

Startup

• Startuptime>= 0 AND PlayingTime = (number) AND no error code, corresponds to a successful play.
• Startuptime= -1 AND PlayingTime = 0 AND an error code, corresponds to a VSF.
• Startuptime = -1 AND PlayingTime = 0 AND no error code, corresponds to an EBVS.

Quality KPI: Successfully started sessions

• StartupError = 0 and BufferingTime > 0 => Pass
• StartupError <> 0 or PlayingTime < 1 => Fail

Quality KPI: Sessions with StartupTime below acceptable threshold

• StartupTime <= Threshold => Pass
• StartupTime > Threshold => Fail

Quality KPI: Sessions Played with an acceptable bitrate

• AvgBitrate >= Threshold => Pass
• AvgBitrate < Threshold => Fail

Quality KPI: Sessions Played with acceptable buffering ratio

• (BufferingTime / BufferingTime + PlayingTime) * 100 <= Threshold => Pass
• (BufferingTime / BufferingTime + PlayingTime) * 100 > Threshold => Fail

New and renamed fields versus legacy SSD

The Content Summary schema adds fields that were unavailable in the legacy SSD file, and renames several legacy fields for consistency. For the full field-by-field mapping, see SSD Schema Comparison: Legacy vs Connect.

Content Session Summary field descriptions

The complete field dictionary, with every delivered field, its type, mode, and description grouped by category, now lives on the Video Session Feed schema and column dictionary page.

Conviva SPI Calculation

The Conviva SPI provides a visual indicator of your KPIs performance so you can quickly determine the number and percent of impacted streams and performance level. A stream is impacted when 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.

1. 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

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

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
            (
                  StartupTime >= 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
            (
                  StartupTime >= 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

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