Ad Session Data

This page is the metric reference for the Ads Session Feed. It defines the ad experience metrics, shows the SQL that produces each one, and answers the questions that come up when your numbers do not match Pulse.

Ad Session Summary is an offline historical log that provides session-level information for every ad play or attempted ad play. It is delivered daily or hourly, and your Conviva Representative configures the schedule for each account. See Daily and hourly delivery for how the two differ.

Audience

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

• Business analysts

• Operations teams

• Research teams

Using the 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 a data store of your choice: a Google Cloud Storage bucket, an Amazon S3 bucket, or SFTP.

Choose the output file format: CSV, Avro, or Parquet. See Get the Conviva Data Feed for the format and file-naming details.

For the hourly feed, the file path is:

{delivery_path}/{YYYY-MM-DD HH:00:00}/part-*

Data is delivered in multi-part files; part-* is used to enumerate the files, and the extension follows the format you chose. Each hourly folder also holds one _SUCCESS file, written even for an hour with no ad plays. Folder times are UTC, and each hour typically arrives 4 to 6 hours after it ends. The folder names for your account under the delivery path are agreed when the feed is configured; your Conviva Representative confirms the file naming for your account. See Build your first pipeline, step 2.

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 one ad play: an instance of an attempt to play an ad, whether an explicit viewer action or an implicit automatic player action. An ad session, identified by AdSessionID, is a group of ad plays, so one AdSessionID can appear on many rows. The metric SQL on this page counts rows, so its counts are counts of ad plays.

In the hourly feed, each ad play in a file starts on or before the end of the hour the file covers. The ad play either ended within that hour (EndedStatus > 0) or was still active at the end of it (EndedStatus = 0, never null). Each row carries the ad play's totals over its lifetime up to the end of that hour.

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.

Every ad play in an ad session carries the same AdSessionID, each with a different Ad Start Time (StartTimeMs). The unique key for one ad play is therefore Ad Session ID + Ad Start Time (AdSessionID + StartTimeMs). ContentSessionID is not needed for uniqueness; it links the ad play to its parent content session.

The row for each ad session also contains the Ad Break ID field, which identifies the ad break during which the ad played. Each ad break can contain multiple ad plays. In this case, the ad plays 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 deduplicated set of Ad Session Summary hourly files.

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.

   The ErrorList column lists all the error messages received from the client for ad sessions that ended with a playback failure.

   Ad Playback Failure = True if either VideoPlaybackFailureErrorsTech or VideoPlaybackFailureErrorsBusiness is non-empty

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

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

   SELECT
       SUM (
           CASE WHEN
               cardinality(VideoPlaybackFailureErrorsBusiness) > 0 or cardinality(VideoPlaybackFailureErrorsTech) > 0
           then
               1
           else
               0
           END) * 100.0 / COUNT(*) as APF_Perc
   FROM
       Demo
   WHERE
       PlayingTime > 0
   and EndedStatus > 0

   
   

3. Ad Startup Time (AST)

Definition: AST is the number of milliseconds between the start of Conviva monitoring and the first played ad frame, excluding any time spent playing non ad related content. Exclude the -1 and -3 sentinel values before averaging. The query reports the result in seconds.

Across all sessions, AST can be aggregated as:



SELECT
    SUM(StartupTime) / 1000.0 / 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. A row counts as an ASF when StartupError is true and PlayingTime is 0. A session that played frames and then failed counts as a playback failure. 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
            and PlayingTime = 0
        then
            1
        else
            0
        END) * 100.0 / COUNT(*) 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) * 100.0 / (SUM(ReBufferingTime) + SUM(PlayingTime)) as AdRebufferingRatio
FROM
    Demo
WHERE
    PlayingTime > 0

7. Average Ad Bitrate

Definition: The average bitrate in kilobits per second of delivered ad content in the session. A value of 0 means the client did not report a bitrate, so the query excludes those sessions.

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



-- Weight each session bitrate by its playing time
SELECT
    SUM(AvgBitRate * PlayingTime) * 1.0 / SUM(PlayingTime) as AverageAdBitRate
FROM
    Demo
WHERE
    PlayingTime > 0
and AvgBitRate > 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) * 100.0 / COUNT(*) 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(*) * 1.0 / 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 total amount of data transferred, in megabytes (MB), across the sessions in scope.

Across all sessions, Ad Bandwidth can be aggregated as:



SELECT
    SUM(PlayingTime * AvgBitRate / 8000000.0) as AdBandwidth
FROM
    Demo
WHERE
    PlayingTime > 0 and AvgBitRate > 0

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:



Exclude the sentinels first. PlayingTime and ContentLength both carry -1 when the value is unavailable. A row where both are -1 gives a ratio of exactly 1.0, which clears the 0.9 threshold and counts an ad that never played as a completed play. The query adds ContentLength > 0 AND PlayingTime > 0 to both the numerator and the denominator.

SELECT
    100.0 * SUM(
        CASE
        WHEN
            PlayingTime > 0
            and ContentLength > 0
            and (PlayingTime * 1.0 / ContentLength) >= 0.9
        then
            1
        END) / SUM(
        CASE
        WHEN
            (PlayingTime > 0 and ContentLength > 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. Playing Time above the ad Content Length is clamped to the Content Length, so one session cannot contribute more than 100%. We don’t count the Playing Time for sessions where the Ad Content Length isn’t available.



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

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.0 / COUNT(*) as AdActualDurationSecAdPlays
FROM
    Demo
WHERE
    PlayingTime > 0

KPIs

Startup

• StartupTime >= 0 AND PlayingTime > 0 AND no error code, corresponds to a successful play.
• StartupError = true AND PlayingTime = 0, corresponds to an ASF.
• StartupError = false AND PlayingTime = 0 AND StartupTime = -1, corresponds to an EBAS.

Quality KPI: Successfully started sessions

• StartupError = false and PlayingTime > 0 => Pass
• StartupError = true or PlayingTime <= 0 => 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

• (ReBufferingTime / (ReBufferingTime + PlayingTime)) * 100 <= Threshold => Pass
• (ReBufferingTime / (ReBufferingTime + 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 the hourly feed an ad play routinely crosses an hour boundary, in which case it appears in more than one hourly file and must be deduplicated before you aggregate. 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 delivered as CSV use URI encoding on the Session Tags column. Parquet and Avro carry the tags as a native array and need no decoding. The Session Tags column 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

You can customize your Ad Session Summary reports to include only the columns you need for your metrics. Your Conviva Representative configures the column list for your account.

Conviva Ad Session Summary schema

Every column in the feed is documented once, in the Ads Session Feed schema and column dictionary: field name, type, mode, and description, grouped by subject, with a sample delivered row. Use that page as the single source of truth for the schema so the two lists cannot drift apart.