Session Source Data (Parquet)

This document describes how to use legacy Session Source Data (SSD) reports, delivered in parquet format. Session Source Data (SSD) is a daily offline historical feed (This document describes how to use 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. Each video play in Conviva terms is considered a video session.

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

Audience

SSD is beneficial to many critical business departments, but it is 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. The session time span is from the moment the client starts the session until the client terminates the session.

In the SSD file the columns reflect the Conviva session data (SSD fields). The SSD fields include metadata (such as viewerID), and metrics aggregated from event logs (such as Playing Time). The SSD file contains the snapshot of each session for that 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 or SFTP protocol. Alternately, we can configure a folder on the Box.com storage service to store the files. If using Box, we keep each report for a month. With FTP/SFTP, storage and retention is controlled by your specific policy. We deliver the file daily 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 parquet. We use the standard described in Aparche Parquet.
• The SSD in parquet is delivered in the following folder structure:
  XXXX<YYYY-MM-DD>/
  This folder contains the number of part files that belong to the same time frame. The file extension is parquet.gz:
  
  
• Each SSD file is paired with a manifest file named DailySessionLog_<CUSTOMER_NAME>_<YYYY-MM-DD>.manifest extension. The manifest file (view manifest sample, open with any text editor) provides all the metadata relating to the specific SSD report. Please review the details in the Manifest File section.

4. Tools to Use

Typically users load SSD into a Big Data system like SparkSql, Hive or cloud technologies like AWS Athena and Google Big Query. Also you can use it with analytics tools like Tableau to analyze the data and correlate it with other data sets. Please review the examples at the sample SQL queries section, to further understand how to get various metrics out of SSD data.

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

This document focuses on metrics shown in Conviva VSI. Each SSD report provides interval metrics, which is a snapshot of the session within that day, as shown in Conviva VSI dashboards. If a session spans many 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 file will include just the second day interval; the third day SSD will include data just for the third day.

SSD and SQL fields

In addition to the SSD default fields, 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 blue: 8025681:1708681910:125006299:1630859473:4109428019.
• session ID: from the Conviva Session ID, the session ID is the 5th segment, highlighted in red: 8025681:1708681910:125006299:1630859473:4109428019.
• We defined the table name as companyX_YYYY_MM_DD, which helps isolate a specific date. The format is companyX_2018_02_20 in the SQL queries.

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 Playback Failures (VPF)
• 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_2018_02_20

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:



SELECT (VSF/Interval_attempts)*100 AS VSF_pct FROM (
   SELECT
        SUM(case when 'startup_error' = true then 1 else 0 end) AS VSF, count(*) AS Interval_attempts
            FROM companyX_2018_02_20
)

3. 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 (VPF/plays)*100 AS VPF_pct FROM (
   SELECT
      SUM(case when 'VPF' = true then 1 else 0 end) AS VPF,
      SUM(case when 'startup_time_ms' != -1 then 1 else 0 end) AS plays FROM companyX_2018_02_20
)

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

Exit 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 'playing_time' = 0 and 'startup_time_ms' = -1 then 1 else 0 end) AS ebvs
         FROM companyX
         Where ssd_date = 2018-02-20)a 

5. 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/Interval_attempts)*100 AS abandonment_good_spi_pct,
      (abandonment_best_spi/Interval_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

6. 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 plays from companyX_2018_02_20 WHERE 'startup_time_ms'  != -1



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 plays from companyX_2018_02_20 WHERE 'startup_time_ms' >= 0
       OR ('startup_time_ms' = -3 AND 'playing_time_ms' >= 0 )




QoE Metrics

7. 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 bufferingTime/(bufferingTime + playingTime)*100 AS buffering_pct FROM (
    SELECT SUM(CASE WHEN 'buffering_time_ms' > 30*60*1000 THEN 30*60*1000 ELSE 'buffering_time_ms' END)/60000 AS bufferingTime,
         SUM('playing_time_ms')/60000  AS playingTime FROM companyX_2018_02_20 WHERE 'startup_time_ms' !=-1 )

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



SELECT cibufferingTime/(bufferingTime + playingTime)*100 AS ci_buffering_pct FROM (
SELECT SUM(CASE WHEN 'connection_induced_rebuffering_time_ms'> 30*60*1000 THEN 30*60*1000 ELSE
            'connection_induced_rebuffering_time_ms' END)/60000  AS cibufferingTime,
             SUM(CASE WHEN 'buffering_time_ms' > 30*60*1000 THEN 30*60*1000 ELSE 'buffering_time_ms' END)/60000 AS
                  bufferingTime,
                  SUM('playing_time_ms')/60000 AS playingTime FROM companyX_2018_02_20 WHERE 'startup_time_ms' !=-1
 )

9. 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:



SELECT bits/playingTimeMs AS bitrate_Mbps FROM (
SELECT SUM('average_bitrate_kbps'*('playing_time_ms'/1000)) AS bits,
              SUM('playing_time_ms') AS playingTimeMs FROM companyX_2018_02_20
              WHERE 'startup_time_ms' != -1 and 'playing_time_ms' > 0 AND 'average_bitrate_kbps' > 0)

10. 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:



11. Bandwidth

Definition:

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



12. 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:



SELECT SUM('startup_time_ms')/COUNT(1)/1000 AS vst_time_seconds
       FROM companyX_2018_02_20 WHERE 'startup_time_ms' >= 0

13. 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).



SELECT vrt/rejoinedCnt AS VideoRestartTimeSeconds FROM (SELECT SUM('video_restart_time_ms')/1000 AS vrt,
        SUM('rejoined_count') AS rejoinedCnt FROM companyX_2018_02_20 where 'startup_time_ms' != -1)

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



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



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



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



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



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

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

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



SELECT COUNT(DISTINCT SUBSTRING_INDEX('conviva_session_id', ":", 4))
     AS devices
         FROM companyX_2018_02_20
         WHERE 'startup_time_ms' != -1

22. Total Minutes

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



SELECT SUM('playing_time_ms')/60000
     AS playingTimeMinutes
         FROM companyX_2018_02_20
         WHERE 'startup_time_ms' !=-1

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

Calculating interval based metric:

SELECT PlayingTime/ContentLength
    AS percentage_complete FROM
           (SELECT SUM('playing_time_ms') as PlayingTime, SUM('content_length_ms') as ContentLength
             FROM compnayX_2018_02_20 WHERE 'content_length_ms' > 0)

Calculating lifetime based metric:

SELECT PlayingTime/ContentLength AS percentage_complete
    FROM (SELECT SUM('playing_time_ms') as PlayingTime, SUM('content_length_ms') as ContentLength
              FROM compnayX_2018_02_20 WHERE 'content_length_ms' < 0)

Frequently Asked Questions

1. Difference between Conviva VSI Dashboard and SSD

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

1. Conviva VSI uses real-time, 1-minute or 5-minute 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 Conviva VSI and SSD share the same session level sanitization rules, Conviva VSI 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 Conviva VSI 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, that is 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 Conviva VSI'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. During buffering or pause, please note:
Buffering: 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.
Rebuffering: 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 Conviva VSI and SSD metrics. The only difference is that Conviva VSI 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 Conviva VSI and SSD calculations.
Pause: 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.

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. What if I want to get the lifetime metrics for sessions at the specific day?

   When a session data spans across many days, to get the lifetime details join the table and adjust Playing Time by adding any Playing Time data from the previous day. For example:

   SELECT ROUND(SUM(c.'playing_time_ms' + NVL(p.prior_playingTime, 0))/60000) as PlayingTimeMinutes
    
   FROM (
       (SELECT 'playing_time_ms', 'conviva_session_Id', 'startup_time_ms', 'start_time_unix_time' FROM companyX_2018_02_20 WHERE 'startup_time_ms' !=-1) c
    
   LEFT OUTER JOIN
    
   (SELECT 'playing_time_ms' AS prior_playingTime, 'conviva_session_Id' AS prior_sessionId,
   'startup_time_ms' AS prior_startUpTime, 'start_time_unix_time' AS prior_startTime FROM
   companyX_2018_02_19 WHERE  'startup_time_ms'  != -1) p
       ON (c.'conviva_session_Id' = p.prior_sessionId AND c.'start_time_unix_time' = p.prior_startTime))

4. 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 (Parquet)

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.

Note: The maximum value size for any of the fields is 128 bytes.

Manifest File Definitions

The manifest file is a JSON format file with additional metadata describing the generated SSD file. The fields are described in the following table: