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
Pick a schedule, a destination, and a format. The Ads feed is delivered daily or hourly, and your Conviva Representative configures the schedule for each account. This walkthrough uses the hourly feed, which needs the deduplication in step 4. The daily feed has the same row grain and key: one row per ad play, keyed on AdSessionID plus StartTimeMs.
Conviva Connect writes the files to one of these destinations:
Amazon S3, including the AWS role for KMS-encrypted uploads
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.
Feeds are configured per account, and each account's folder name under your delivery path is agreed with your Conviva Representative. If you are moving from daily to hourly delivery, you can run both in parallel until you confirm the cutover. See Feeds per account and switching to hourly, including the current limitation on traffic rules.
Step 2: Hourly folder contents
For each hour, Conviva writes the data into a folder named for that hour: {delivery_path}/{YYYY-MM-DD HH:00:00}/. Folder times, like every timestamp in the files, are UTC. Inside a folder you will find:
Multiple part files named
part-*, with the extension of your chosen format, for examplepart-*.avro. The dataset is sharded for parallel writes; commonly around 30 part files, and the exact count is configured per account.One
_SUCCESSfile, which marks the hour as delivered. Conviva writes it even for an hour with no ad plays, so an hour with_SUCCESSand no rows is genuinely empty, while an hour without_SUCCESSis not delivered yet. Load a folder only after its_SUCCESSfile appears.A
.manifestfile 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.
Each hour's folder typically arrives 4 to 6 hours after the end of that hour, and the exact timing varies with traffic volume. Poll for _SUCCESS rather than loading at a fixed offset.
Load every part file in the folder as a single logical dataset for that hour. Within an hour, each ad play 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 an ad play 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 play that spans an hour boundary appears in more than one hourly file, once per active hour, and each row carries that play's totals up to the end of that hour. Skipping this step inflates every metric downstream. Keep only the latest row per ad play 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, an ad play 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. AdSessionID identifies an ad session, a group of ad plays, and every play in the group shares it; StartTimeMs tells the plays apart. Partitioning on AdSessionID alone collapses distinct ad plays into one row. You do not need ContentSessionID in the key; it links the ad play back to its parent content session.
Across the hourly records of one ad play, StartTimeMs stays the same and EndTimeMs moves forward. The ad play is marked ended (EndedStatus not 0) in only one record, and that record carries its final EndTimeMs. Because later hours never restate earlier ones, that ended record is also the one with the greatest dt, so the two rules agree: the deduplicated row is the ended row whenever the play has ended.
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_ad_plays
FROM ads_hourly_raw
WHERE dt >= TIMESTAMP '2025-10-14 00:00:00';Then check that the deduplicated set has no stale open rows. After deduplication, a row with EndedStatus = 0 should belong only to an ad play still running at the end of the window you loaded, so every such row should sit in the last loaded hour. Open rows in an earlier hour usually mean a missing hourly folder:
SELECT
dt,
COUNT(*) AS open_ad_plays
FROM deduped
WHERE EndedStatus = 0
GROUP BY dt
ORDER BY dt;Step 5: Roll up to your reporting grain
Each deduplicated row is one ad play 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 hourly Ads feed never restates a past hour: an ad play appears once per active hour, and its latest row carries its final totals.
Attribute an ad play 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,
a.StartTimeMs,
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, a.StartTimeMs;Group by both key columns. Grouping by AdSessionID alone merges the tags of every ad play in the ad session into one row.
Use a left join here too. A CROSS JOIN UNNEST drops every ad play 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 hourly 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 deduplicate on
AdSessionIDplusStartTimeMs? Deduplicating onAdSessionIDalone undercounts, because it collapses every ad play in an ad session into one row.Did you attribute ad plays to the start day rather than the
dtpartition? Ad plays that cross midnight land in the wrong day otherwise.Did you load every hour? Check that every hour in the window has a
_SUCCESSfile. A missing hourly folder silently removes whole ad plays.Did negative sentinel values leak into an average or a sum?
Next steps
Schema and column dictionary for every field.
Ad Session Summary for the metric definitions and KPI formulas.
Video Session Feed if you also consume content sessions.
Legacy vs Connect if you are migrating an existing legacy Ads SSD pipeline.