Session Source Data

This document describes how to use legacy Session Source Data (SSD) reports. Session Source Data (SSD) is a daily offline historical log (view SSD sample) that provides session-level information for every video play or attempted play in a given day.

For the latest session summary data feeds from Conviva, see Conviva Connect.

Audience

SSD is beneficial to many critical business departments, but it's mainly used by:

• Business analysts
• Operations teams
• Research teams

Get Started with SSD Reports

1. Define Conviva Session

Each row in the SSD 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 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.

Prior to the release of the new Conviva Timelines Backend (TLB), each session in SSD (line in the log file) had a unique session ID, which could be used as the primary key. After the release of TLB, a session that is suspended and resumed will appear in different lines in the SSD log file with the same session ID. Each session line has a respective start time, with the first portion of the session showing the session start time and the second portion of the session showing the session resume time. In this case, the primary key to process the sessions is Session ID + Session Start Time.

Note: Before the release of TLB, there was a known issue where a session that was suspended and later resumed (for example, user pressing home button on phone or closing the laptop), could report only the final portion of the session (after resume). In this case, each session (line in the log file) had a unique session ID, which could be used as primary key.

In the SSD file, the Conviva session data (SSD fields) includes metadata, such as viewerID, and metrics (aggregated from event logs), such as Playing Time. The session time span is from the moment the client starts the session until the client terminates the session. The SSD file contains the snapshot of each session at the end of the day.

2. How to use SSD Fields

We have summarized all the fields in a table, please review the SSD field definitions at the end of this document.

You can use the SSD field data to:

• filter against a particular metadata field in the SSD file, to identify issues or patterns in a set of sessions.
• filter against a particular SSD metric across one or many days.
• calculate key experience and engagement metrics (see Calculate Metrics).
• define and shape business KPIs (see Define KPIs).
• map a user journey: a common session ID can be used across multiple data sources (from SSD and other logs or feeds) to track the user journey through various systems (e.g. site/content discovery, page load, player load, CMS request, DRM request, ad request, CDN request, playback experience, next page/content viewed, app crashes, etc).
• analyze viewer usage/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.

3. Get the SSD Reports

The most common method of delivery is via FTP, SFTP protocol or an S3 bucket. With FTP/SFTP/S3, storage and retention is controlled by your specific policy. We expect the daily Conviva Connect file to be delivered within 24-48 hours of the end of the day, based on the time-zone specified in your Conviva VSI account.

• The SSD file format is CSV (comma-separated values). We use the standard described in RFC 4180: http://tools.ietf.org/html/rfc4180
• The SSD file name is in the format: DailySessionLog_<CUSTOMER_NAME>_<YYYY-MM-DD>.csv
• Each SSD file is paired with a manifest file, with the same name, but with a .manifest extension. The manifest file (view manifest sample, open with any text editor) provides all the metadata relating to the specific SSD report.

4. Tools to Use

Typically users load SSD into a database such as MySQL or into analytics tools such as Tableau or Micro Strategy, to analyze the data and correlate with other data sets. Please see examples at the sample SQL queries provided for some of the metrics.

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)

Conviva Metrics and KPIs

1. Metrics Guidelines

SSD reports are used to calculate Conviva metrics based on historical session data. Please note some important guidelines to assist you with your calculations.

Lifetime and Interval Metrics

In this document we focus on metrics shown in ConvivaVSI. However, ConvivaVSI shows interval metrics, which are based on the last 24 hrs. Each SSD file provides lifetime metrics, which is a snapshot of the entire duration of the session up to that point. If a session spans days (e.g. crosses the midnight boundary in the account’s timezone), that session will appear in many SSD log entries: the first SSD log will be the session metrics for the first day (until midnight); the following day’s SSD log will include the complete session up to that point (e.g. from when it started the previous day); the third days' logs will include the data for three days. This is by design.

With SSD data, frequently you want to aggregate data based on a set of sessions, for a particular time period (i.e. all video playing time across all sessions for a specific date). To achieve that, we use interval metric calculations and we provide some examples how to calculate those from the SSD lifetime metrics.

SSD and SQL fields

In addition to the default fields in SSD, we created three additional fields for the SQL calculations in the How to Calculate Metrics (with SQL examples) section:

• client ID: from the Conviva Session ID, we isolate the client ID by using the first four segments. For example, the Client ID is highlighted in bold: 8025681:1708681910:125006299:1630859473:4109428019.
• session ID: from the Conviva Session ID, the session ID is the 5th segment, highlighted in bold: 8025681:1708681910:125006299:1630859473:4109428019.

  Note: Legacy SSD session IDs are unsigned, random integers. Session IDs in Conviva Connect and Pulse applications are signed, random integers.

• ssd_date: when you load multiple SSD logs at a time, this field helps isolate a specific date as it is the date on which the SSD was generated. The SSD file name is in the format: DailySessionLog_<CUSTOMER_NAME>_<YYYY-MM-DD>.csv, so the format for ssd_date is YYYY-MM-DD. Use ssd_date in your SQL queries to translate it into a column in your database to identify which session reported on what date.

Please note that for the SQL calculations, we defined an alias for every SSD field. That is shown in the third column in the SSD field table.

2. Metrics

Core Metrics:

• Attempts
• Plays

Quality of Experience Metrics:

• Rebuffering Ratio
• Connection Induced Rebuffering Ratio
• Average Bitrate
• Session Duration
• Video Startup Time
• Video Restart Time
• Video Startup Failures (VSF)
• Video Startup Failures Business (VSF-B)
• Video Startup Failures Technical (VSF-T)
• Video Playback Failures (VPF)
• Video Playback Failures Business (VPF-B)
• Video Playback Failures Technical (VPF-T)
• Exits Before Video Start (EBVS)
• Abandonment

Engagement Metrics:

• Ended Plays
• Unique Devices
• Total Minutes
• Average % Complete

3. KPIs

Startup

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

Quality KPI: Successfully started sessions

• StartupError = 0 and bufferingtime > 0 => Pass
• StartupError <> 0 or playing time < 1 => Fail

Quality KPI: Sessions with Startup Time below acceptable threshold

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

Quality KPI: Sessions Played with an acceptable bitrate

• AvgBR >= Threshold => Pass
• AvgBR < Threshold => Fail

Quality KPI: Sessions Played with acceptable buffering ratio

• (bufferingtime / bufferingtime + playingtime) * 100 <= Threshold => Pass
• (bufferingtime / bufferingtime + playingtime) * 100 > Threshold => Fail

How to Calculate Metrics (with SQL examples)

1. Attempts

Definition: All attempts made to play video.



Calculating interval based metric: no additional steps are needed to calculate interval based metrics for a given day.

SELECT count(*) AS attempts,
     FROM CompanyX WHERE ssd_date = 2018-02-20

Note: When computing attempts for a given date, use ssd_date to count the number of records in the SSD on that date of SSD file.

2. Video Startup Failures (VSF)

Definition: Video Startup Failures occur when a video fails to play video and generates an error code. The error codes indicate the nature of the failure.

Sessions with Startup Error = 1 represent Video Startup Failures (VSF). The SSD field Video Startup Failure Error List, lists all the error messages received from the client, for sessions that ended due to a VSF.



Average Video Start Failures (VSF) % can be aggregated using the calculation below:



Calculating interval based metric: when calculating interval based metrics (for the day) VSF needs to be adjusted by removing any start failures associated with sessions that start on the previous day:

Startup_error = True if (startup_error = 1)

SELECT Interval_attempts AS attempts,
      (VSF/Interval_attempts)*100 as VSF_pct,
FROM (
     SELECT
        count(*) AS Interval_attempts,
     SUM ( case WHEN (startup_error>0 and playingtime=0) then 1 else 0 end)  as VSF,
     FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

3. Video Startup Failures Business (VSF-B)

Definition: Video Startup Failures Business occur when a video fails to play due to a business error. This generates an error code which indicates the nature of the failure.

Sessions with Startup Error = 1 represent Video Startup Failures Business (VSF-B). The SSD field Video Startup Failure Business Error List, lists all the error messages received from the client for sessions that ended due to a VSF-B.



Average Video Start Failures Business (VSF-B) % can be aggregated using the calculation below:



Calculating interval based metric: When calculating interval based metrics (for the day) VSF-B must be adjusted by removing any start failures associated with sessions that start on the previous day:

Startup_error_B = True if (B-VSF = 1)
 
SELECT Interval_attempts AS attempts,
    (VSF/Interval_attempts)*100 as VSF_pct,
FROM (
   SELECT
      count(*) AS Interval_attempts,
   SUM ( case WHEN (B_vsf>0 and playingtime=0) then 1 else 0 end)  as VSF,
   FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

4. Video Startup Failures Technical (VSF-T)

Definition: Video Startup Failures Technical occur when a video fails to play due to a technical error. This generates an error code which indicates the nature of the failure.

Sessions with Startup Error = 1 represent Video Startup Failures Technical (VSF-T). The SSD field Video Startup Failure Technical Error List, lists all the error messages received from the client for sessions that ended due to a VSF-T.



Average Video Start Failures Technical (VSF-T) % can be aggregated using the calculation below:



Calculating interval based metric: When calculating interval based metrics (for the day) VSF-T must be adjusted by removing any start failures associated with sessions that start on the previous day:

Startup_error_T = True if (T-VSF = 1)
 
SELECT Interval_attempts AS attempts,
    (VSF/Interval_attempts)*100 as VSF_pct,
FROM (
   SELECT
      count(*) AS Interval_attempts,
   SUM ( case WHEN (T_vsf>0 and playingtime=0) then 1 else 0 end)  as VSF,
   FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

5. Video Playback Failures (VPF)

Definition: Video Playback Failures (VPF) occurs when 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.



Average Video Playback Failures (VPF) % can be aggregated using the calculation below:



Calculating interval based metric: VPF needs to be adjusted by removing any sessions that started the previous day:

SELECT Plays, (VPF/Plays)*100 as VPF_pct,
    FROM (
        SELECT
           SUM ( case WHEN (startuptime != -1) then 1 else 0 end ) as Plays,
           SUM ( case WHEN (vpf>0) then 1 else 0 end)  as VPF,
        FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

6. Video Playback Failures Business (VPF-B)

Definition: Video Playback Failures Business (VPF-B) occurs when 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.



Average Video Playback Failures Business (VPF-B) % can be aggregated using the calculation below:



Calculating interval based metric: VPF-B must be adjusted by removing any sessions that started on the previous day:

SELECT Plays, (VPF/Plays)*100 as VPF_pct,
    FROM (
        SELECT
           SUM ( case WHEN (startuptime != -1) then 1 else 0 end ) as Plays,
           SUM ( case WHEN (B_vpf>0) then 1 else 0 end)  as VPF,
        FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

7. Video Playback Failures Technical (VPF-T)

Definition: Video Playback Failures Technical (VPF-T) occurs when the video play terminates 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.



Average Video Playback Failures Technical (VPF-T) % can be aggregated using the calculation below:



Calculating interval based metric: VPF-T must be adjusted by removing any sessions that started on the previous day:

SELECT Plays, (VPF/Plays)*100 as VPF_pct,
    FROM (
        SELECT
           SUM ( case WHEN (startuptime != -1) then 1 else 0 end ) as Plays,
           SUM ( case WHEN (T_vpf>0) then 1 else 0 end)  as VPF,
        FROM CompanyX  WHERE ssd_date = 2018-02-20   )a

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.

8. Exits Before Video Start (EBVS)

Definition: Exits Before Video Start 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.

Sessions with Startup Time = -1, and Startup Error = 0 represent Exits Before Video Start (EBVS). An EBVS is similar to a VSF (it is a failure to play video), but an EBVS does not include a descriptive error message.

Exits Before Video Start (EBVS) can be aggregated to any grouping by using the number of attempts as the weighting factor.



Calculating interval based metric: The SQL shows the EBVS adjustement for interval calculations, by removing any start associated with sessions that started the previous day:

SELECT Interval_attempts AS attempts,
       (EBVS/Interval_attempts)*100 as EBVS_pct
       FROM (
           SELECT count(*) AS Interval_attempts,
           SUM( case when (startup_error = 0 and playingtime = 0 and startuptime = -1)
           then 1 else 0 end) as ebvs
           FROM CompanyX  WHERE ssd_date = 2018-02-20 )a

9. Abandonment

   Definition: Abandonment occurs when a viewer exits a video before the video starts with a wait time greater than the SPI threshold setting of 10 seconds for SPI Good and 8 seconds for SPI Best.

   

   SELECT Interval_attempts AS attempts,
      (abandonment_good_spi/attempts)*100 AS abandonment_good_spi_pct,
      (abandonment_best_spi/attempts)*100 AS abandonment_best_spi_pct 
      FROM (
            SELECT COUNT(*) AS Interval_attempts
               SUM(case when 'startup_error' = 0 and 'playing_time' = 0 and 'startup_time_ms' = -1 and ('end_time_unix_time' - 'start_time_unix_time') > 10 then 1 else 0 end) AS abandonment_good_spi,
               SUM(case when 'startup_error' = 0 and 'playing_time' = 0 and 'startup_time_ms' = -1 and ('end_time_unix_time' - 'start_time_unix_time') > 8 then 1 else 0 end) AS abandonment_best_spi
            FROM companyX
            Where ssd_date = 2018-02-20)a

10. Plays

Definition: Plays shows the number of sessions where at least one video frame was displayed.

Plays are calculated as all attempted sessions that didn’t have a failure like VSF or EBVS ie., plays = attempts - sessions with VSF - sessions with EBVS. Based on your preference, you can use two methods to get Plays:

1. Strictly include sessions where at least one video frame was played:
   
   
   

   select  count(*)  as play from CompanyX where playingtime > 0 and ssd_date = 2018-02-20;



2. In some cases, the player may report joined state, but the user might have exited before the first video frame was played. Joined time is indicated by Video Startup Time > 0. If the session joined but we don’t have the actual join time, we display -3 for Video Startup Time (instead of null). Therefore, for plays calculations, you can include sessions where Video Startup Time is –3 and Playing Time is > 0. See also What does VST = -3 stand for? in the FAQ section.

   

   select  count(*)  as play from CompanyX where startuptime >0 or (startuptime = -3 and playingtime >= 0) and  ssd_date = 2018-02-2;




Calculating interval based metric: Plays requires no adjustments.

QoE Metrics

11. Rebuffering Ratio

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



Calculating interval based metric: To calculate the Rebuffering Ratio for a day, adjust the Rebuffering Time and Playing Time by subtracting any Rebuffering Time or Playing Time accumulated in the previous day.

SELECT  Interval_buffering_time*100/(Interval_buffering_time + Interval_playing_time) as buffering_ratio
FROM (
  SELECT
    ROUND(SUM(case when a.bufferingtime >30*60*1000  then 30*60*1000 else a.bufferingtime -NVL(b.prior_buffering_time_ms,0) end  )/60000) AS Interval_buffering_time,
    ROUND(SUM(a.bufferingtime)/60000) AS life_buff_time ,
    ROUND(SUM(a.playingtime -NVL(b.prior_playing_time_ms,0) )/60000) AS Interval_playing_time
    FROM (
          SELECT playingtime,
                bufferingtime,
                convivasessionid,
                startuptime,
                starttime,
                ssd_date
                FROM CompanyX WHERE ssd_date = 2018-02-20 AND startuptime!=-1) a
                LEFT outer JOIN (
                  SELECT r.convivasessionid AS prior_session_id,
                         r.bufferingtime AS prior_buffering_time_ms,
                         r.playingtime AS prior_playing_time_ms,
                         r.startuptime as p_startuptime,
                         r.starttime as p_starttime
                  FROM CompanyX  r
                    WHERE r.ssd_date = 2018-02-19 ) b
                    ON (a.convivasessionid = b.prior_session_id and   a.starttime =b.p_starttime)
     where a.playingtime>0
   ) Tmp

12. Connection Induced Rebuffering Ratio (CIRR)

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

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.

The CIRR ratio shows the non-seek rebuffering time divided by total play time (expressed as a percentage). This ratio is calculated after video play ends.



Calculating interval based metric: Connection Induced Rebuffering Time and Playing Time need to be adjusted by subtracting any Connection Induced Rebuffering Time or Playing Time accumulated in the previous ssd_date.

SELECT Interval_cibuffering_time*100/(Interval_buffering_time + Interval_playing_time) as buffering_ratio
FROM(
  SELECT
    ROUND(SUM(case when a.bufferingtime >30*60*1000  then30*60*1000 else a.bufferingtime -NVL(b.prior_buffering_time_ms,0) end)/60000) AS Interval_buffering_time,
    ROUND(SUM(case when a.cibufferingtime >30*60*1000  then30*60*1000 else a.cibufferingtime -NVL(b.prior_cibuffering_time_ms,0) end)/60000) AS Interval_cibuffering_time,
    ROUND(SUM(a.bufferingtime)/60000) AS life_buff_time ,
    ROUND(SUM(a.playingtime -NVL(b.prior_playing_time_ms,0) )/60000) AS Interval_playing_time
    FROM(
          SELECT playingtime,
                cibufferingtime,
                bufferingtime,
                convivasessionid,
                startuptime,
                starttime,
                ssd_date
                FROM CompanyX WHERE ssd_date= 2018-02-20 AND startuptime!=-1) a
                LEFT outer JOIN(
                  SELECT r.convivasessionid ASprior_session_id,
                         r.cibufferingtime ASprior_cibuffering_time_ms,
                         r.bufferingtime ASprior_buffering_time_ms,
                         r.playingtime ASprior_playing_time_ms,
                         r.startuptime asp_startuptime,
                         r.starttime asp_starttime
                  FROM CompanyX  r
                   WHERE r.ssd_date= 2018-02-19 ) b
                   ON(a.convivasessionid = b.prior_session_id anda.starttime = b.p_starttime)
                   where a.playingtime > 0
   ) Tmp

CIR Interrupt Count shows the number of CIR interrupts during a session.

13. Average Bitrate

Definition: This metric shows the average bitrate of delivered content, across the entire audience, in a given time frame.

The calculation below shows the aggregate for the Average Bitrate for a set of sessions:



Calculating interval based metric: to calculate the Average Bitrate for a day interval, adjust the Playing Time by subtracting any Playing Time accumulated in the previous day.

SELECT Interval_bytesloaded/(Interval_playing_time) AS bitrate,
    life_bytesloaded/life_playing_time as lifebitrate
FROM
(
 SELECT
    ROUND(SUM(a.bytesloaded -NVL(b.prior_bytesloaded,0) )) AS Interval_bytesloaded,
    ROUND(SUM(a.bytesloaded )) AS life_bytesloaded,
    ROUND(SUM(a.playingtime )) AS life_playing_time,
    ROUND(SUM(a.playingtime -NVL(b.prior_playing_time_ms,0) )) AS Interval_playing_time
 
    FROM (
          SELECT playingtime,
                averagebitrate*playingtime as bytesloaded,
                convivasessionid,
                startuptime,
                starttime,
                ssd_date
                FROM CompanyX WHERE ssd_date = 2018-02-20 AND startuptime!=-1 and playingtime>0 and averagebitrate>0) a
 
            LEFT outer JOIN (
                  SELECT r.convivasessionid AS prior_session_id,
                         r.playingtime*r.averagebitrate AS prior_bytesloaded,
                         r.playingtime AS prior_playing_time_ms,
                         r.startuptime as p_startuptime,
                         r.starttime as p_starttime
                  FROM CompanyX  r
                   WHERE r.ssd_date = 2018-02-19  and r.playingtime >0 and r.averagebitrate>0) b
            ON (a.convivasessionid = b.prior_session_id and   a.starttime =b.p_starttime)
   ) Tmp

14. Session Duration

Definition: Session duration is total time we have been monitoring the session.

The total duration of the session and can be approximated as:



15. Bandwidth

Definition:

To calculate the session's total MB played, you can use the below calculation:



16. Video Startup Time

Definition: Video Startup Time is the number of seconds between the start of the Conviva monitoring starts and the first played video frame. We exclude any time trying to play or playing ads.

Average Video Startup Time (VST) can be aggregated using the Plays metric, as follows:



Calculating interval based metric: to calculate Video Startup Time for a specific day, remove any start times associated with sessions that started on the previous day. Please see SQL below for details:

SELECT
ROUND(SUM(a.jointime -NVL(b.p_jointime,0) )/ count(1)
) AS VST_ms
FROM (
      SELECT playingtime,
            bufferingtime,
            convivasessionid,
            startuptime as jointime,
            starttime,
            ssd_date
            FROM CompanyX WHERE ssd_date = 2018-02-20 AND startuptime >= 0) a
            LEFT outer JOIN (
              SELECT r.convivasessionid AS prior_session_id,
                     r.bufferingtime AS prior_buffering_time_ms,
                     r.playingtime AS prior_playing_time_ms,
                     r.startuptime as p_jointime,
                     r.starttime as p_starttime
              FROM CompanyX  r
              WHERE r.ssd_date = 2018-02-19 ) b
      ON (a.convivasessionid = b.prior_session_id and a.starttime = b.p_starttime)
      where a.playingtime >0 and a.jointime >0

17. Video Restart Time

Definition: This is the amount of time after seeking is completed by the user until video begins playing for the user (expressed in seconds).

Calculating interval based metric: Please see SQL below for details:

SELECT Interval_restarttime /(Interval_rejoinCount) AS VRT,
   FROM
      (
       SELECT
                 ROUND(SUM(a.rejoinCount -NVL(b.prior_rejoinCount,0) )) AS Interval_rejoinCount,
                 ROUND(SUM(a.vrestarttime -NVL(b.prior_vrestarttime ,0) )) AS Interval_restarttime
                 FROM (
                       SELECT vrestarttime,
                             rejoincount,
                             convivasessionid,
                             startuptime,
                             starttime,
                             ssd_date
                             FROM CompanyX WHERE ssd_date = 2018-02-20 AND startuptime!=-1 and playingtime>0)a
                         LEFT outer JOIN (
                               SELECT r.convivasessionid AS prior_session_id,
                                      r.vrestarttime AS prior_vrestarttime,
                                      r.rejoincount AS prior_resjoincount,
                                      r.startuptime as p_startuptime,
                                      r.starttime as p_starttime
                               FROM CompanyX  r
                                WHERE r.ssd_date = 2018-02-19  and r.playingtime ) b
                         ON (a.convivasessionid = b.prior_session_id and   a.starttime =b.p_starttime)
                ) Tmp

18. Micro Playing Time

Definition: The total time in milliseconds that a session spent in continuous play time that are less than 200 milliseconds. The player reports false play duration sometimes and this time is excluded from playing time.



19. Micro Playing Count

Definition: The total count of number of times a session spent in continuous play time that are less than 200 milliseconds. The player reports false play duration sometimes and this time is excluded from playing time.



20. Micro Rebuffering Time

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



21. Micro Rebuffering Interrupts

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



22. Long Rebuffering Time

Definition: The total time in milliseconds that a session spent in continuous buffering that are more than 90 seconds. A long buffering could be because of a player being stuck in buffering state and this is excluded from rebuffering time.



23. Long Rebuffering Interrupts

Definition: The total count of number of times a session spent in continuous buffering that are more than 90 seconds. A long buffering could be because of a player being stuck in buffering state and this is excluded from rebuffering time.



Engagement Metrics

24. Ended Plays

Definition: An Ended Play is a viewing session that ended during the selected interval. The session must have at least one viewed video frame to count toward the Ended Play metric. This metric counts only viewing sessions that played and ended.



SELECT count(*)
                        from CompanyX a
                    WHERE a.ended_status > 0 and a.playingtime > 0

25. Unique Devices

Definition: We assign a unique client ID to each device. Unique Devices represents the count of those distinct client IDs which initiated at least one video session in the given time frame.

To count the number of unique devices, you will need to use the data in the 'conviva session id’. The Client ID is the first 4 segments (not the first 4 digits) and the specific session ID is the 5th segment. Using the sample below, the data highlighted in blue is the Client ID 8025681:1708681910:125006299:1630859473:4109428019.

Calculating interval based metric:





select  count(distinct(clientId)) as uniq from CompanyX  where startuptime !=-1 and ssd_date =2018-02-20;

26. Total Minutes

Definition: Playing time (mins) - The total number of minutes that a particular session/any aggregation played.

Calculating interval based metric: when calculating interval based metrics (for the day), Playtime needs to be adjusted by removing any playtime associated with sessions that start on the previous day. Example: aggregating Plays to a viewerID is done as follows:

SELECT
    a.gameId,
    ROUND(SUM(a.playingtime -NVL(b.prior_playing_time_ms,0) )/60000) AS Interval_play_time,
    ROUND(SUM(a.playingtime)/60000) AS life_play_time
    FROM (
      SELECT  playingtime,
             convivasessionid,
            startuptime,
             gameId,
             starttime,
             ssd_date
                FROM CompanyX WHERE ssd_date = 2018-02-20 AND startuptime!=-1) a
            LEFT outer JOIN (
                  SELECT r.convivasessionid AS prior_session_id,
                         r.playingtime AS prior_playing_time_ms,
                         r.startuptime as p_startuptime,
                         r.starttime as p_starttime
                  FROM CompanyX  r
                   WHERE r.ssd_date = 2018-02-19 ) b
                        ON (a.convivasessionid = b.prior_session_id and a.starttime =b.p_starttime)
                        group By a.gameId order by Interval_play_time desc

27. Average % Complete

Definition: We calculate Average % Complete by dividing the total playing time for all sessions by the total content length. We don’t count the playing time for sessions where the content length isn’t available.

Frequently Asked Questions

1. Difference between ConvivaVSI Dashboard and SSD

There are differences between the data that you see within ConvivaVSI vs what you see in the SSD logs. This is by design, and there are a couple of reasons why SSD and ConvivaVSI have differences:

1. ConvivaVSI uses real-time, 1-minute or hourly interval metrics whereas SSD provides lifetime metrics and can provide a historical 1-day interval. The averages between the two will get closer the longer the window you use - i.e. a 24-hour average will result in a better match than a 1-hour average.
2. Although ConvivaVSI and SSD share the same session level sanitization rules, ConvivaVSI adds additional aggregation logic based on metrics calculations. The idea here is for the consumer of Session Source Data to apply aggregation logic based on the desired use case.



Pursuing an exact match between ConvivaVSI and offline SSD calculations is difficult and often inadvisable, but following the rules below will help make the calculations much closer.

Exclude the following SSD sessions from your calculations for playback metrics (buffering/bit-rate):

• When calculation playtime metrics only use sessions that joined ie sessions that didn’t have VSF or EBVS.
• Playing time = 0 and Buffering time is NOT 0.
• You can also specify that any bit-rates over 10mbps be ignored (quite rare but that's one of ConvivaVSI's current sanitization mechanisms).

Include the following sessions:

• Extremely long Buffering Time (but cap this value at 30 minutes).
• Calculate overall Buffering Ratio as:
  

  

A Note on Heartbeats:

Conviva uses Heartbeats to monitor the video sessions.
Heartbeats continue to be sent while the video is buffering, unless the internet connection itself has been interrupted and the Conviva library and backend cannot communicate. In this case, the library will continue monitoring and will send a "catch-up" heartbeat when the connection is re-established.
If the video is paused or the connection is lost for more than 2 minutes, then no heartbeats will be sent and the Conviva back-end will automatically clean-up the session. We do not count paused time, so if the viewer was paused for more than 2 minutes then the session will time out.
We still collect heartbeats during rebuffering, so this would not cause a session to timeout. However, if the player ends up in what we call a 'zombie state' (incessant/perpetual buffering) then that contributes to ConvivaVSI and SSD metrics. The only difference is that ConvivaVSI metrics cap buffering at 30 minutes, whereas SSD has no such cap. Logic in an SSD-analysis tool to account for this difference would be necessary to unify ConvivaVSI and SSD calculations.

2. What does VST = -3 stand for?

VST=-3 means we don't have a specific Video Startup Time. Possible reasons include:

• The current session is the revived portion of an expired session. Sessions expire when there is 120 seconds of inactivity. The expired and revived sessions have the same session ID, so you can group the sessions by session id (SID5). The session End flag of the expired session is set to 2 (Session End Status = 2), while the revived portion has VST of -3.

• Conviva flags indicate that the startup time is not reliable.

• Startup time exceeded 10 minutes and the VST is flagged as unknown.

3. Why do I see unexpected ASCII characters in certain fields?

SSD reports are delivered as CSV files. The Session Tags column in SSD is customer defined and can contain arbitrary strings with special characters which could prevent parsing of CSV; therefore we use URI encoding to remove any offending characters. We recommend that you run URI decoding on the "Session Tags" field, by using various libraries that provide this decode functionality (available in all mainstream languages).

4. How do I stitch SSD across multiple days?

The data in SSD is already "stitched" for sessions that span across multiple days. SSD provides lifetime metrics, which means that when a session data spans across many days, the last session record contains the complete details of the metrics for that session. This is a quick mechanism to view SSD session data across multiple sets. Therefore, when combining SSD sessions, overriding earlier sessions with the most recent session provides the most accurate and quick mechanism to stitch session logs.

5. What is the difference between -1 and 0 in Percent Complete?

The value in Percentage Complete is rounded to the nearest integer value.
A value of 0 is a rounded value when percentage complete is less than 1%. This covers the occasions when the startup time>0 but the video only played for a very small period of time.
A value of -1 (usually for live content) indicates that we do not receive content length.

SSD Field Definitions

This guide provides a comprehensive list of all available SSD columns. You can customize your SSD reports to include only the columns you need for your metrics. Please contact Conviva Customer Support to add or remove columns from your reports.