Build Your First Ads Pipeline

An end-to-end walkthrough for loading the Conviva Connect Ads Session Feed: set up delivery, understand what lands, deduplicate the hourly files, roll up metrics, join to the Video Session Feed, and validate against Pulse.

Updated 2026-10-02 ads-ssd, pipeline, getting started, deduplicate, session tags

These eight steps build a working Ads Session Feed pipeline, from configuring delivery to reconciling the output against Conviva Pulse. They assume you have read Ads Session Feed: Start Here. Work through them in order.

Step 1: Set up delivery

The Ads feed is hourly, so there is no schedule to choose. Pick a destination and a format. Conviva Connect writes the files to your storage every hour:

Choose Parquet as the file format where you can. It is columnar, compresses well, and preserves the array and record fields (SessionTags, ErrorList, and the four failure-error lists) as native nested types rather than escaped strings. CSV flattens them into &-separated text that you have to re-parse.

Columns are selectable per account. Confirm your configured column list with your Conviva Representative before you write the table definition, and ask to be notified when it changes, because a new column shifts positional CSV readers.

Step 2: Hourly folder contents

For each hour, Conviva writes the data into a dated folder. Inside a folder you will find:

  • Multiple part files (the dataset is sharded for parallel writes; commonly around 30, and the exact count is configured per account).

  • A .manifest file that lists the part files for that hour. Read the manifest first and load only the files it lists, so you never pick up a partially written folder.

Load every part file in the folder as a single logical dataset for that hour. An ad session lands in exactly one part file, but which one is not meaningful.

The dt column carries the hour the file covers, as a UTC timestamp truncated to the hour, for example 2025-10-14T07:00:00.000Z. Partition your target table on dt.

Step 3: Load the raw hourly files

Land the files unchanged in a raw table first, then deduplicate downstream. Keeping the raw hourly rows lets you rebuild if you find a bug in the deduplication, and it is the only place the mid-flight state of a session survives.

CREATE TABLE ads_hourly_raw (
  AdSessionID      STRING,
  ContentSessionID STRING,
  StartTimeMs      BIGINT,
  EndTimeMs        BIGINT,
  PlayingTime      BIGINT,
  StartupTime      BIGINT,
  ReBufferingTime  BIGINT,
  ReBufferingEvents BIGINT,
  StartupError     BOOLEAN,
  EndedStatus      BIGINT,
  AdPosition       STRING,
  Advertiser       STRING,
  SessionTags      ARRAY<STRUCT<key STRING, value STRING>>,
  ErrorList        ARRAY<STRING>
  -- plus the rest of your configured columns
)
PARTITIONED BY (dt TIMESTAMP);

See the schema and column dictionary for the type and mode of every field.

Step 4: Deduplicate

An ad session that spans an hour boundary appears in more than one hourly file, once per active hour, and each row carries that session's totals up to the end of that hour. Skipping this step inflates every metric downstream. Keep only the latest row per session before you aggregate anything:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY AdSessionID, StartTimeMs
      ORDER BY dt DESC
    ) AS rn
  FROM ads_hourly_raw
)
SELECT * FROM ranked WHERE rn = 1;

Deduplicate over a window wider than the one you report on. If you rebuild a single day from only that day's 24 files, a session that started at 23:50 and ended the next morning is deduplicated against an incomplete set, and the row you keep is not its final row. Read a look-ahead margin of several hours past the end of your reporting window, deduplicate across the whole range, then filter to the window.

Partition on both columns, not on AdSessionID alone. A session that is suspended and resumed reuses the session ID with a new start time, and those are genuinely different plays.

Run this check on your first week of data to see how much the deduplication actually removes. If it removes nothing, either your ads are short enough to never cross an hour boundary, or you are not loading every hour:

SELECT
  COUNT(*) AS raw_rows,
  COUNT(DISTINCT CONCAT(AdSessionID, ':', CAST(StartTimeMs AS STRING))) AS distinct_sessions
FROM ads_hourly_raw
WHERE dt >= TIMESTAMP '2025-10-14 00:00:00';

Step 5: Roll up to your reporting grain

Each deduplicated row is one ad session with its final totals, so a daily roll-up is a straightforward aggregate over the deduplicated set. There is no lifetime-versus-interval subtraction to do, because unlike the daily Video feed the Ads feed never restates a prior day's session.

Attribute an ad session to the day it started, using StartTimeMs, so an ad that began at 23:59 and finished after midnight is not counted twice:

WITH deduped AS (
  -- the step 4 query
)
SELECT
  DATE(FROM_UNIXTIME(StartTimeMs / 1000))              AS ad_day,
  AdPosition,
  COUNT(*)                                             AS ad_attempts,
  SUM(CASE WHEN StartupError THEN 1 ELSE 0 END)        AS ad_start_failures,
  SUM(CASE WHEN PlayingTime >= 0 THEN PlayingTime END) AS ad_playing_ms,
  SUM(ReBufferingTime)                                 AS ad_rebuffering_ms,
  AVG(CASE WHEN StartupTime >= 0 THEN StartupTime END) AS avg_ad_startup_ms
FROM deduped
GROUP BY 1, 2;

The two CASE expressions exclude the negative sentinel values. StartupTime carries -1 when the ad never played and -3 when the client never reported enough to measure it. PlayingTime and ContentLength carry -1 when unavailable. These values are status codes rather than durations, so exclude them from ratios as well as from sums and averages. A ratio with a sentinel in the numerator or the denominator produces a number that means nothing. For the full metric definitions and the KPI formulas, see Ad Session Summary.

Decide what the denominator of each ratio is before you publish it. A rebuffering ratio over all ad sessions and a rebuffering ratio over the sessions that played are different numbers, because a session that never played contributes zero playing time to the second one. Record the denominator next to the metric.

Step 6: Unnest the session tags

SessionTags carries your own player metadata as key-value pairs, typically a few dozen per row. Some of the keys duplicate dedicated columns, so prefer the dedicated column when one exists; reach into the tags for the metadata that only you define.

Pivot the tags you report on into real columns once, at load time, rather than unnesting on every query:

SELECT
  a.AdSessionID,
  MAX(CASE WHEN t.key = 'c3.cm.contentType' THEN t.value END) AS content_type,
  MAX(CASE WHEN t.key = 'c3.cm.channel'     THEN t.value END) AS channel,
  MAX(CASE WHEN t.key = 'c3.app.version'    THEN t.value END) AS app_version
FROM deduped a
LEFT JOIN UNNEST(a.SessionTags) AS t(key, value) ON TRUE
GROUP BY a.AdSessionID;

Use a left join here too. A CROSS JOIN UNNEST drops every ad session whose SessionTags array is empty, which silently removes rows from the result rather than leaving their tag columns null.

In CSV the same data arrives as one &-separated, URL-encoded string, so split on &, then on the first =, then URL-decode the value.

Step 7: Join to the Video Session Feed

ContentSessionID is the ConvivaSessionID of the video session the ad played inside. Join the two feeds to relate ad experience to content outcomes:

SELECT
  v.ConvivaSessionID,
  v.AssetName,
  v.PlayingTime             AS content_playing_time,
  COUNT(a.AdSessionID)     AS ads_attempted,
  SUM(a.ReBufferingTime)   AS ad_rebuffering_time
FROM video_sessions v
LEFT JOIN deduped a
  ON a.ContentSessionID = v.ConvivaSessionID
GROUP BY 1, 2, 3;

Use a left join. A content session with no ads is a real and common case, and an inner join silently drops it.

Collapse the video side to one row per session first. The daily Video Session Feed repeats a long-running session on every day it was active, so joining the raw daily files to the ads rows multiplies each ad by the number of days its content session spanned. Deduplicate the video sessions on ConvivaSessionID and StartTimeUnixMs exactly as you deduplicated the ads, then join.

Align the windows before you join. The Video daily feed is timezone-aligned to your customer day while the Ads feed is partitioned on UTC hours, so load the Ads hours that cover your customer day rather than assuming the two line up.

Step 8: Validate against Pulse

Reconcile your output against Conviva Pulse before you publish it. Pick a day, a metric, and a filter (for example, ad attempts for one ad position), compute it from your loaded Ads data, and compare it to the same view in Pulse.

Expect close but not identical numbers, and align the windows before you investigate a gap. If a metric is far off, work through this list first:

  • Did you deduplicate? An inflated playing time or attempt count is almost always a missing step 4.

  • Did you attribute sessions to the start day rather than the dt partition? Sessions that cross midnight land in the wrong day otherwise.

  • Did you load every hour? A missing hourly folder silently removes whole sessions.

  • Did negative sentinel values leak into an average or a sum?

Next steps