Ad Session Data

This document describes how to use Connect Ad Session Summary data with Ads metrics. Ad Session Summary data is a daily offline historical log that provides session-level information for every ad play or attempted ad play in a given day.

Audience

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

• Business analysts

• Operations teams

• Research teams

How to use Ad Session Summary Data Fields?

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

You can use the field data to:

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

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

• Calculate key experience and engagement metrics (see Calculate Metrics).

• Define and shape business KPIs (see Define KPIs).

• Analyze viewer usage / consumption patterns: By tracking how users are engaging with ads, you can research trends and patterns within viewing habits. This information can be used in ad 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 Ad Session Summary Data Reports

The method of delivery is to either a data store, such a Google Cloud Storage bucket or an Amazon S3 bucket.

The SSD file formats are CSV (comma-separated values), Parquet, and Avro. We use the standard described in RFC 4180.

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:

• DailyAdsSessionLog_<CUSTOMER_NAME>_<YYYY-MM-DD>.csv

Tools to Use

Typically users load Ad Session Summary data into a database such as MySQL or into analytic 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)

Getting Started with Ad Session Summary Data

Each row in the Ad Session Summary file describes an ad session. Conviva defines an ad session as an instance of an attempt to play an ad; the attempt can be an explicit viewer action or an implicit automatic player action.

In Ad Session Summary, each ad session starts on or before the pipeline end date (PED). The ad session ends either on the PED (EndedStatus > 0) or after the PED (EndedStatus = 0). All sessions contain data over the lifetime of the session ending on the PED.

Each session has an AdSessionID, which is a string consisting of five segments, for example:

992489610:1404980148:3349546328:378608002:-1542356252

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

A session that is expired and resumed will appear as different rows with the same AdSessionID but with different Ad Start Times. In this case, the primary key to process the sessions is Ad Session ID + Ad Start Time.

The row for each ad session also contains the Ad Break ID field, which identifies the ad break during which the ad session played. Each ad break can contain multiple ad sessions. In this case, the ad sessions will share the same ad break ID.

Metrics List

Metric Definition and Calculations

This section provides definitions for the metrics and shows how these metrics can be calculated using SQL code. In most cases, metrics at the session level are provided in the Ad Session Summary file. In cases where these are not provided, examples of the formulas and SQL code are given below. This section also shows how to aggregate the ad metrics across sessions. The SQL code is based on Conviva Demo data from the Ad Session Summary with a pipeline end date of February 13, 2023.

Ad Delivery Metrics

1. Ad Attempts

Definition: An attempt to fetch ad creatives in the selected timeframe, regardless if the ad played or not. For client-side ads, an attempt is made when the video player attempts to fetch an ad creative using the ad tag URL. For server-side ads, an attempt is the first event that indicates that the ad attempted to play the stitched media. In Ad Session Summary, each row represents an ad attempt.

Across all sessions, the total number of Ad Attempts is:



SELECT
    COUNT(*) as AdAttempts
FROM
    Demo

2. Ad Playback Failures (APF)

   Definition: The sum or a percentage of the cumulative ended ad creatives that terminated with a playback failure error in the selected time period.

   Sessions with Playback Error = 1 represent Ad Playback Failures (APF). The SSD field error list, lists all the error messages received from the client, for ad sessions that ended due to a APF.

   Ad Playback Failure = True if (playback_error = 1)

   Average Ad Playback Failures (APF) % can be aggregated using the calculation below:

   Average APF % = ∑ Count ( APF ) / ∑ Count (EndedPlays)

   SELECT
       SUM (
           CASE WHEN
               size(VideoPlaybackFailureErrorsBusiness) > 0 or size(VideoPlaybackFailureErrorsTech) > 0
           then
               1
           else
               0
           END) / COUNT(*) * 100 as APF_Perc

   
   

3. Ad Startup Time (AST)

Definition: AST is the number of seconds between the start of Conviva monitoring and the first played ad frame. We exclude any time playing non ad related content.

Across all sessions, AST can be aggregated as:



SELECT
    SUM(StartupTime / 1000) / COUNT(*) as AST
FROM
    (
        SELECT
            *
        FROM
            Demo
        WHERE
            PlayingTime >0
        and StartupTime >= 0)

4. Ad Start Failures (ASF)

Definition: Ad Start Failures can occur when an ad fails to play and generates an error code. In Ad Session Summary, this metric is called StartupError. You can find the list of associated errors in the ErrorList field.

Across all sessions, Ad Start Failures (ASF) % can be aggregated as:



SELECT
    SUM (
        CASE
        WHEN
            StartupError = 'true'
        then
            1
        else
            0
        END) / COUNT(*) * 100 as ASF_Perc
FROM
    Demo

Ads Metrics

5. Ad Impressions

   Definition: An ad impression is a session with greater than zero seconds of Ad Playing Time (i.e., at least one ad frame was played).

   Across all sessions, Ad Impressions are aggregated as:

   

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

   Another way to aggregate Ad Impressions is to count all attempted ad sessions that didn’t have a failure like ASF or EBAS:

   

   In some cases, the player may report that an ad session started, but the user might have exited before the first ad frame was played. If the ad session started but we don’t have the actual AST, we display AST = -3 . Therefore, for ad impression calculations, you can include sessions where AST = –3 and Ad Playing Time > 0. See also FAQ.

   

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

6. Ad Rebuffering Ratio

Definition: The Ad Rebuffering Ratio shows the percentage of buffering during an ad session. From this metric, we exclude the initial player Ad Startup Buffering time (before the first ad frame).

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



SELECT
    SUM(ReBufferingTime) / (SUM(ReBufferingTime) + SUM(PlayingTime)) * 100 as AdRebufferingRatio
FROM
    Demo
WHERE
    PlayingTime > 0

7. Average Ad Bitrate

Definition: The average bitrate in kilobytes per second of delivered ad content in the session.

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



-- Because bitrate is per second, convert AdPlayingTime to seconds
SELECT
    SUM(AvgBitRate * PlayingTime / 1000) / SUM(PlayingTime / 1000) as AverageAdBitRate
FROM
    Demo
WHERE
    PlayingTime > 0

8. Exits Before Ad Start (EBAS)

Definition: EBAS is when an ad attempt is terminated, typically by the viewer, before the ad started. If an error is not generated, the failed attempt is counted as an EBAS. An EBAS is similar to an ASF (it is a failure to play an ad), but an EBAS does not include a descriptive error message. The EBAS for each session needs to be computed from the Ad Session Summary fields.



Across all sessions, the EBAS % can be aggregated using the AdAttempts metric:



SELECT
    SUM(
        CASE
        WHEN
            StartupError    = false
            and PlayingTime = 0
            and StartupTime = -1
        then
            1
        END) / COUNT(*) * 100 as EBAS_Perc
FROM
    Demo

9. Ad Frequency/Unique Devices

Definition: This metric is defined as the total number of Ad Ended Plays divided by the number of Unique Devices with at least one played ad during the session lifetime. A unique device is not equivalent to a unique person. If a person uses multiple devices, each device will be counted as a unique device with an ad play that ended. If more than one application is offered to the same device, the Client ID is identified for each application separately.



SELECT
    COUNT(*) / COUNT(DISTINCT(DeviceId)) as AdFreqUniqueDev
FROM
    Demo
WHERE
    PlayingTime > 0
and EndedStatus > 0

10. Session Duration

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



SELECT
    EndTime - StartTime as SessionDurationSec
FROM
    Demo

11. Ad Bandwidth

Definition: Ad Bandwidth is the amount of data transferred in megabytes (MB) per second in the session.

Across all sessions, Ad Bandwidth can be aggregated as:



SELECT
    SUM((PlayingTime / 1000) * (AvgBitRate / 8000)) as AdBandwidth
FROM
    Demo

Ad Completion Metrics

12. Ad Ended Plays

Definition: An Ad Ended Play is a session with an ad impression (PlayingTime > 0) that ended (EndedStatus > 0) during the pipeline end date (for example, February 13). Thus, the session must have at least one viewed ad frame to count toward the Ad Ended Plays metric.

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



SELECT
    COUNT(*) as AdEndedPlays
FROM
    Demo
WHERE
    PlayingTime > 0
and EndedStatus > 0

13. Completed Ad Creative Plays

Definition: The total number of ad creatives that successfully played at least 90% of the ad content.

The Completed Ad Creative Plays % can be aggregated as:



SELECT
    100 * SUM(
        CASE
        WHEN
            (PlayingTime / ContentLength) >= 0.9
        then
            1
        END) / SUM(
        CASE
        WHEN
            (PlayingTime > 0)
        then
            1
        END) as CompAdCreativePlays
FROM
    Demo

14. Ad % Complete

Definition: We calculate Ad % 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 Ad Content Length isn’t available.



SELECT
    SUM(PlayingTimeRC)/SUM(ContentLength) * 100 as AdPercComplete
FROM
    (
        SELECT
            ContentLength,
            CASE
            WHEN
                PlayingTime > ContentLength
            then
                ContentLength
            else
                PlayingTime
            END as PlayingTimeRC
        FROM
            Demo)

15. Ad Actual Duration

Definition: The average ad Playing Time in seconds, which is the total ad playing time divided by the number of ad creatives that played.



SELECT
    (SUM(PlayingTime) / 1000) / COUNT(*) as AdActualDurationSecAdPlays
FROM
    Demo
WHERE
    PlayingTime > 0

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 ASF.
• Startuptime= -1 AND PlayingTime= 0 AND no error code, corresponds to an EBAS.

Quality KPI: Successfully started sessions

• StartupError= 0 and BufferingTime> 0 => Pass
• StartupError0 or PlayingTime Fail

Quality KPI: Sessions with StartupTime below acceptable threshold

• StartupTime Pass
• StartupTime > Threshold => Fail

Quality KPI: Sessions Played with an acceptable bitrate

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

Quality KPI: Sessions Played with acceptable buffering ratio

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

Frequently Asked Questions

1. Difference between Ads Dashboard and SSD

   There are differences between Conviva Ad Session Summary data and Ads data, and that's because:

   1. Ads uses real-time 1-minute or 1-hour interval metrics whereas SSD provides lifetime metrics. For more details on the difference between lifetime and interval metrics, refer here. Note that in some rare cases the ad session can cross the midnight boundary of the pipeline end date. The averages between lifetime and interval metrics will get closer the longer the window you use, that is, a 24-hour average will result in a better match than a 1-hour average.

   2. Although Ads and Ad Session Summary share the same session level sanitization rules, Ads adds additional aggregation logic based on metrics calculations. The idea here is for the consumer of Ad Session Summary to apply aggregation logic based on the desired use case.

   Pursuing an exact match between Ads and offline Ad Session Summary calculations is difficult and often inadvisable.

   A Note on Heartbeats:

   Conviva uses Heartbeats to monitor the ad sessions. Heartbeats continue to be sent while the ad 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 ad 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 an ad session to timeout. However, if the player ends up in what we call a 'zombie state' (incessant/perpetual buffering) then that contributes to Ads and Ad Session Summary metrics. The only difference is that Ads metrics cap buffering at 30 minutes, whereas Ad Session Summary has no such cap.

2. What does AST = -3 stand for?

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


• it is a resumed session, where there is no start up time; for example, when a user closes the laptop during playback, and resumes later.
• we have flags that indicate the startup time is not reliable.
3. Why do I see unexpected ASCII characters in certain fields?

Ad Session Summary 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).

Ad Session Summary Field Definitions

This guide provides a comprehensive list of all available Ad Session Summary columns. You can customize your Ad Session Summary reports to include only the columns you need for your metrics.

Conviva Ad Session Summary Schema