The Subscription Event Log is part of Slate Analytics, our new partner data product. The Subscription Event Log is unlike other Slate reports in several ways:
- It’s append-only (each file has new data)
- It’s not available for download in the UI (this is accessible via API only)
- It’s built explicitly for partner Data Engineers, and enables them to consume granular data to perform analytics
This topic helps Data Engineers set up their pipelines to retrieve Subscription Event Log data, defines the values in the Subscription Event Log files, and provides sample queries and suggestions for the optimal way partners can use this data.
To get started, check out the guidelines in the API onboarding guide. For more information about the reports you can get from Slate, see Reporting API documentation.
Ingest subscription event log data
To access this data you must have:
- A login with Amazon Security Profile
- An authorization code to request a token
- A token for all curl requests
Prime Video publishes new Subscription Event Log files daily, at 11:59 PM UTC. These logs may contain data from up to 12 hours prior. If we experience a delay, we will communicate through email as soon as possible.
A list of subscription event logs is available at the following curl URL:
https://videocentral.amazon.com/apis/v1/accounts/{account_id}/channelsAnalyticsLogs/{channel/studio_id}/reportTypes/amzn1.vcpa.arcs.insight.29fb1b79-9aa1-4732-9ebb-4befccbeb09a/reports
Use the following request parameters:{
limit: {The number of reports returned in a single request (the page size).},
offset: {The number of pages to skip (the page number).},
modifiedDateGte: {UTC timezone since you last pull}
}
The following code shows an example response to this request.
{
"next":{link to next request},
"total":{num of reports},
"data":
[
{
"downloadUrl":"{presigned url}",
"numRows":{num of rows},
"reportDateBegin":"2024-07-18",
"reportDateEnd":"2024-07-19",
"modifiedDate":"2024-07-23T17:24:29.312Z",
"name":"Channels Persistent CID Subscription Event Log",
"cadence":"Daily"}
]
}
Use the downloadUrl to download the CSV file.
Use these links to find your account_id or your channel/studio_id.
Manage new and changing data
We provide data to consumers in the form of a changelog. Each subscription event is published only once. However, if any column values for a previously provided row need to be updated, we will publish a new version of the record to reflect the changes in your next available file. The changelog is append-only, to ensure that all data modifications are captured. Data engineers can use this changelog to update their subscription data tables directly.
When you process the changelog, it’s essential to always use the latest record for a given subscription_event_id, based on the last_update_time_utc column. This ensures that you always have the most up-to-date version of each record. If a record needs to be deleted, this action is reflected in the is_deleted column. A value of 1 indicates that the record has been deleted, while a value of 0 represents an active record.
This changelog approach allows you to effectively manage new and changing data, and ensures that your subscription data tables remain accurate and up to date with the latest information.
Column definitions
The following table defines the columns and values you’ll see in subscription event logs.
| Column | Definition |
| subscription_event_id | The unique ID for each subscription event vended through this log. |
| subscription_event_type | The type of subscription event that occurred: Start: Customer subscribed to a channel they were not subscribed to previously. |
| subscription_event_time_utc | The time the subscription event occurred, standardized to UTC. |
| subscription_event_time_zone | The time zone of the subscription marketplace. |
| cid | Anonymized customer identifier. This customer identifier (CID) will persist for all events under a single Parent Channel to enable inter-tier movement and customer lifecycle tracking. |
| offer_id | The ID of the specific subscription offer the event occurred in relation to. |
| offer_name | The human-readable name of the offer. |
| offer_type | The type of offer. |
| offer_marketplace | The marketplace where the subscription offer was live. |
| offer_billing_type | The type of payment required for the offer: HO: Hard offer; payment required. |
| offer_payment_amount | The billing amount of the offer_id. |
| benefit_id | The ID of the Prime Video benefit the offer is configured under. |
| channel_label | The name of the channel the offer is under. Note: If this column shows a null value, you might need to onboard to Slate Analytics. To group offers or tiers under a single channel label, please contact your CAM or PsM. |
| channel_tier_label | The name of the channel the offer is under. Note: If this column shows a null value, you might need to onboard to Slate Analytics. To group offers under a single tier, please contact your CAM or PsM. |
| is_promo | Indicates whether an offer is on a promotion at time of event (0= no promo, 1 = yes promo). |
| create_time_utc | The time the subscription event log record was created, standardized to UTC. |
| last_update_time_utc | The time the subscription event log record was last updated, standardized to UTC. |
| is_deleted | Indicates whether a record that was previously created should be deleted (0=should persist, 1= should be deleted). |
Common use cases
With the data provided in the Subscription Event Log, you can easily map individual customer journeys in granular detail, or aggregate the data for core metrics like retention.
The following sections provide some common use cases and sample queries for leveraging this dataset.
Note: All of these sample queries are written to work with AWS Athena. The central subscription event log table is named tahoe_cleansed.subs_event_obt for these examples.
Total subscribers at end of measured period
The following query provides the number of subscriptions at the end of June 2024. All UTC timestamps are converted to the local time zone.WITH main AS (
SELECT
a.cid,
a.offer_id,
SPLIT(a.offer_id, '-')[1] as offer_group_id,
cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp) AS curr_subscription_event_time,
a.subscription_event_type AS curr_event_type,
LEAD(a.subscription_event_type) OVER (
PARTITION BY a.encrypted_customer_id, SPLIT(a.offer_id, '-')[1]
ORDER BY cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)
) AS next_event_type,
LEAD(cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)) OVER (
PARTITION BY a.encrypted_customer_id, SPLIT(a.offer_id, '-')[1]
ORDER BY cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)
) AS next_subscription_event_time
FROM
tahoe_cleansed.subs_event_obt a
WHERE
a.subscription_event_type IN ('Start', 'Renewal', 'Cancel')
)
SELECT
offer_id,
COUNT(DISTINCT cid || offer_group_id) AS month_end_subscriptions
FROM
main
WHERE
(curr_event_type IN ('Renewal', 'Start') AND next_event_type IS NULL AND
curr_subscription_event_time < CAST('2024-07-01' as timestamp)) -- Currently active subscriptions
OR (
curr_event_type IN ('Renewal', 'Start')
AND curr_subscription_event_time < CAST('2024-07-01' AS TIMESTAMP)
AND next_event_type = 'Cancel'
AND next_subscription_event_time >= CAST('2024-07-01' AS TIMESTAMP)
) -- Active before end of month, cancelled after month end
OR (
curr_event_type IN ('Renewal', 'Start')
AND next_event_type IN ('Renewal', 'Start')
AND curr_subscription_event_time < CAST('2024-07-01' AS TIMESTAMP)
AND next_subscription_event_time >= CAST('2024-07-01' AS TIMESTAMP)
) -- Capture all active who stayed active and renewed after the month end
GROUP BY
offer_id;
Total paid subscriptions at end of measured period
The following query provides the number of paid subscriptions at the end of June 2024. All UTC timestamps are converted to the local time zone.WITH main AS (
SELECT
a.cid,
a.offer_id,
SPLIT(a.offer_id, '-')[1] as offer_group_id,
cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp) AS curr_subscription_event_time,
a.subscription_event_type AS curr_event_type,
LEAD(a.subscription_event_type) OVER (
PARTITION BY a.encrypted_customer_id, SPLIT(a.offer_id, '-')[1]
ORDER BY cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)
) AS next_event_type,
LEAD(cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)) OVER (
PARTITION BY a.encrypted_customer_id, SPLIT(a.offer_id, '-')[1]
ORDER BY cast(from_iso8601_timestamp(date_format(at_timezone(a.subscription_event_time_utc, a.subscription_event_time_zone), '%Y-%m-%dT%H:%i:%s')) as timestamp)
) AS next_subscription_event_time
FROM
tahoe_cleansed.subs_event_obt a
WHERE
a.subscription_event_type IN ('Start', 'Renewal', 'Cancel').
and offer_billing_type = 'HO'
)
SELECT
offer_id,
COUNT(DISTINCT cid || offer_group_id) AS month_end_subscriptions
FROM
main
WHERE
(curr_event_type IN ('Renewal', 'Start') AND next_event_type IS NULL AND
curr_subscription_event_time < CAST('2024-07-01' as timestamp)) -- Currently active subscriptions
OR (
curr_event_type IN ('Renewal', 'Start')
AND curr_subscription_event_time < CAST('2024-07-01' AS TIMESTAMP)
AND next_event_type = 'Cancel'
AND next_subscription_event_time >= CAST('2024-07-01' AS TIMESTAMP)
) -- Active before end of month, cancelled after month end
OR (
curr_event_type IN ('Renewal', 'Start')
AND next_event_type IN ('Renewal', 'Start')
AND curr_subscription_event_time < CAST('2024-07-01' AS TIMESTAMP)
AND next_subscription_event_time >= CAST('2024-07-01' AS TIMESTAMP)
) -- Capture all active who stayed active and renewed after the month end
GROUP BY
offer_id;
New subscribers in June 2024
The following query provides the total number of subscribers who enrolled between June 1 and July 1, 2024.-- New Subscribers
SELECT
offer_id, COUNT(DISTINCT cid) AS new_subscribers
FROM
tahoe_cleansed.subs_event_obt
WHERE
subscription_event_type = 'Start'
AND subscription_event_time_utc >= CAST('2024-06-01' AS TIMESTAMP)
AND subscription_event_time_utc < CAST('2024-07-01' AS TIMESTAMP)
GROUP BY offer_id;
Cancelled events
The following query provides the total number of subscriptions that were canceled between June 1 and July 1, 2024.
SELECT
count(distinct cid || offer_id)
FROM
tahoe_cleansed.subs_event_obt
WHERE subscription_event_type = 'Cancel'
AND subscription_event_time_utc >= CAST('2024-06-01' AS TIMESTAMP)
AND subscription_event_time_utc < CAST('2024-07-01' AS TIMESTAMP)
Free-trial conversions to HO in June 2024
The following query provides the total number of free-trial customers who converted to a hard offer in June 2024.
WITH events AS (
SELECT
subscription_event_id,
cid,
offer_id,
offer_billing_type,
subscription_event_type,
subscription_event_time_utc,
CASE
WHEN subscription_event_type = 'Start' or subscription_event_type = 'Renewal' THEN 1
ELSE 0
END AS is_active,
ROW_NUMBER() OVER (PARTITION BY cid, offer_id ORDER BY subscription_event_time_utc DESC) AS rn
FROM
tahoe_cleansed.subs_event_obt
WHERE
subscription_event_time_utc >= CAST('2024-06-01' AS TIMESTAMP)
AND subscription_event_time_utc < CAST('2024-07-01' AS TIMESTAMP)
),
ft_events AS (
SELECT
cid,
offer_id,
min(subscription_event_time_utc) as ft_event_time
from events
group by cid,
offer_id
),
ho_events AS (
SELECT
cid,
offer_id,
subscription_event_time_utc as ho_event_time
FROM
events
WHERE
rn = 1 and offer_billing_type = 'HO' and is_active = 1
),
conversions as (select a.cid,
a.offer_id,
a.ft_event_time,
b.ho_event_time
from ft_events a
inner join ho_events b on a.cid = b.cid and a.offer_id = b.offer_id
where b.ho_event_time > a.ft_event_time)
select count(distinct cid || offer_id) as num_conversions
from conversions;
Customer tier change from Offer_ID1 to Offer_ID2
The following query provides the total number of customers who changed their subscription tier within a Parent Channel.
with first_offer as (
select cid,min(subscription_event_time_utc) as first_offer_time
from tahoe_cleansed.subs_event_obt
where subscription_event_type in ('Start','Renewal')
and offer_id = 'offer_id1'
group by cid
),
second_offer as (
select cid,min(subscription_event_time_utc) as second_offer_time
from tahoe_cleansed.subs_event_obt
where subscription_event_type in ('Start','Renewal')
and offer_id = 'offer_id2'
group by cid
)
select distinct a.cid
from first_offer a
inner join second_offer b on a.cid = b.cid
where b.second_offer_time > a.first_offer_time;
