チャンネル登録イベントログには、Prime Videoチャンネルのイベントレベルのチャンネル登録データが表示されます。匿名化された顧客IDを使用して、Start、Renewal、Cancel、自動更新のステータス変更などの未加工のチャンネル登録イベントにアクセスし、ビジネスニーズに合わせたカスタム分析を構築できます。このガイドでは、チャンネル登録分析を構築するためのデータモデル、ディメンション、メトリクス、ベストプラクティスについて説明します。
チャンネル登録イベントログを使用する理由
チャンネル登録イベントログには、既製のダッシュボードで利用できるもの以外にも、チャンネル登録分析の変更ログが表示されます:
- カスタム分析-イベントレベルの未加工データを使用して、特定のビジネス定義とレポートニーズに合わせたメトリクスを構築します
- データウェアハウスの統合- Prime Videoのチャンネル登録データを社内システムやその他のデータソースと統合して、包括的な分析を行います
- 柔軟なビジネスロジック-ビジネス要件に合わせて、独自の決済ルール、重複排除ロジック、メトリクス計算を適用できます
- 顧客ライフサイクルの追跡-安定した匿名化された識別子を使用して、チャンネル登録イベント全体にわたり個々の顧客ジャーニーを追跡します
仕組み
チャンネル登録イベントログは、チャンネル登録データをAPI経由で追加専用のイベントストリームとして配信します。各レコードは、顧客のチャンネル登録ライフサイクルにおける個別のイベントを表しています。データにはビジネスロジックがあらかじめ適用されていない未加工のイベントが含まれており、ニーズに応じてメトリクスを柔軟に計算できます。
適切なデータアクセス方法の選択
Slateインサイトでは、チャンネル登録データにアクセスする方法が2つあります:
機能 |
Slate分析ダッシュボード |
チャンネル登録イベントログ(API) |
最適な用途 |
迅速なインサイト、エグゼクティブレポート |
カスタム分析、データエンジニアリング |
データ形式 |
事前に集計されたメトリクス |
イベントレベルの未加工データ |
決済ロジック |
自動的に適用 |
あなたが独自のロジックを適用 |
重複排除 |
あなたに代わって処理 |
あなたがlast_update_time_utcを使用して実行 |
柔軟性 |
固定されたメトリクス定義 |
フルカスタマイズ |
更新頻度 |
約8時間ごと |
約4時間ごと |
履歴データ |
最長2年間 |
最長2年間 |
アクセス方法 |
ウェブUI |
REST API |
ダッシュボードは、迅速な回答や既製のビジュアライゼーションが必要な場合に使用します。イベントログAPIは、カスタムメトリクスの構築、データウェアハウスとの統合、またはビジネス固有のロジックの適用が必要な場合に使用します。
主要概念
これらの基本的な概念を理解しておくと、チャンネル登録イベントログを効果的に使用するのに役立ちます。
概念 |
説明 |
イベント |
個別のチャンネル登録アクション(Start、Renewal、Cancel、Auto Renewal On/Off)。各イベントは、一意のsubscription_event_idによって識別されます。 |
ディメンション |
イベントの発生日、関係する顧客、オファータイプなど、イベントのコンテキストを説明する属性。ディメンションはデータのフィルタリングとグループ化に使用されます。 |
メトリック |
アクティブな登録者数、解約率、コンバージョン率などのイベントから算出された測定値。メトリクスは直接保存されるのではなく、あなたがイベントデータから計算します。 |
変更ログモデル |
データは追加専用です。イベントの属性が変わると、subscription_event_idは同じでlast_update_time_utcが新しくなった新規レコードが公開されます。 |
決済 |
未加工のイベントからノイズをフィルタリングするビジネスロジック。たとえば、有意義なチャンネル登録ではない同日のStart/Cancelのペアは除外されます。 |
ライフサイクル |
チャンネル登録の連続期間を表すチャンネル登録「サイクル」。Cancelイベントが確定するたびに、新しいライフサイクルが開始されます。 |
注意: イベントとディメンションから始めてデータをクエリし、決済ロジックとライフサイクルロジックを適用してデータをクリーンアップしてから、ビジネス定義に一致するメトリクスを計算します。
ディメンション
ディメンションは、各チャンネル登録イベントのコンテキストを説明する属性です。データのフィルタリング、結果のグループ化、およびレポートの作成に使用されます。
イベントディメンション
Subscription_event_typeは、発生したチャンネル登録イベントのタイプです。これは、顧客ライフサイクルのアクションを理解するための主要なディメンションです。
値 |
説明 |
ユースケース |
Start |
顧客があなたのチャンネルに初めて登録しました。販売時点では、この顧客に関する以前のStartレコードは存在しません。 |
新規登録者獲得数のカウント |
Renewal |
顧客のチャンネル登録が過去の任意の期間から継続されました。Renewalイベントは、顧客に過去のチャンネル登録レコードがあれば、チャンネル登録期間の間にギャップがあっても発生します。 |
継続登録者数のカウント |
Cancel |
顧客のチャンネル登録が終了しました。顧客主導の場合もあれば、支払いの失敗が原因である場合もあります。 |
解約率の計算 |
Active - AR ON |
顧客が自動更新を有効にしました。チャンネル登録は期間終了時に更新されます。 |
自動更新率の追跡 |
Active - AR OFF |
顧客が自動更新を無効にしました。チャンネル登録は、再度有効にしない限り、期間終了時に無効となります。 |
リスクがある登録者の特定 |
Suspended |
支払いの問題(資金不足または無効な請求情報)により、顧客のチャンネル登録が一時的に停止されています。顧客が請求情報を更新して支払いが正常に処理されると、チャンネル登録は自動的に再開され、Renewalイベントが生成されます。 |
一時的に障害が発生しているチャンネル登録で、回復(Renewalsになる)またはCancellationsに切り替わる可能性があるものを追跡するのに役立ちます |
注意: Startイベントは、販売時点で、その顧客の以前のStartレコードがチャンネルにない場合にのみ発生します。アクティブな登録者を確実にカウントするには、StartイベントとRenewalイベントの両方をアクティブなチャンネル登録のメトリクスとして扱います。このアプローチはすべてのエッジケースを一貫して処理します。
オファーディメンション
これらのディメンションは、各イベントに関連するチャンネル登録オファーを表します。
ディメンション |
タイプ |
形式 |
説明 |
is_promo |
integer |
0または1 |
チャンネル登録イベントがプロモーションオファー期間中に発生したかどうかを示します。0 =標準オファー(プロモーションなし)、1 =プロモーションオファー(割引または特別価格が有効)。 |
offer_type |
string |
テキスト |
Amazonへのオファー分類を表します。チャンネルでは常に「3P_SUBS」になります。 |
offer_marketplace |
string |
ロケーションコード |
チャンネル登録オファーが提供されているAmazonのロケーション。値: US (米国)、UK (英国)、DE (ドイツ)、JP (日本)、ROE (その他欧州)、ROW_NA (その他世界—北米)、ROW_EU (その他世界—欧州)、ROW_FE (その他世界—極東)。offer_marketplaceにはオファーが購入されたロケーションが反映されますが、顧客の現在のロケーションとは異なる場合があります。 |
offer_name |
string |
テキスト |
チャンネル設定で設定された、人間が読める形式のチャンネル登録オファーの名前。 |
offer_payment_amount |
数値 |
数値(例:「4.99」、「9.99」) |
現地通貨でのチャンネル登録オファーの表示価格。このフィールドはオファーのメタデータのみで、都度課金による収益を表すものではありません。このフィールドには、イベントに関連するオファーの価格が表示されます。実際に獲得した収益や受け取った金額を表すものではありません。支払いが行われたかどうかに関係なく、すべてのイベントタイプ(Start、Renewal、Cancel、AR変更)に同じ値が表示されます。このフィールドを合計して収益を計算することはできません。実際の収益データについては、別途ご利用の財務報告システムを参照してください。 |
offer_billing_type |
string |
FTまたはHO |
チャンネル登録オファーの支払いモデル。FT =無料トライアル(無料で提供されるお試し期間)、HO =ハードオファー(有料チャンネル登録)。 |
offer_id |
string |
Identifier |
チャンネル登録オファーの固有の識別子。これを使用すると、オファーのメタデータを結合したり、オファーごとにパフォーマンスを追跡したりできます。 |
注意: offer_marketplaceは、チャンネル登録が購入されたロケーションを示し、価格、通貨、およびコンテンツの利用可能状況を決定します。1人の顧客が複数のロケーションでチャンネル登録を持っている場合もあります。offer_billing_typeを使用すると、コンバージョンメトリクスを計算する際に無料トライアルと有料チャンネル登録のイベントを区別できます。
顧客ディメンション
これらのディメンションは登録者を識別し、記述します。
ディメンション |
タイプ |
形式 |
説明 |
cid |
string |
匿名化された識別子 |
匿名化された安定した顧客識別子。このIDは、チャンネル内の特定の顧客のすべてのイベントにわたり一貫しているため、顧客ライフサイクル全体を追跡できます。cidは親チャンネル内で一貫しています。同じ顧客には、ロケーション内の同じ親チャンネルにロールアップされるすべてのオファーとバンドルで同じcidが割り当てられます(たとえば、「Prime広告付き(米国)」に登録し、後で「Prime広告なし(米国)」に切り替えた顧客は、両方のオファーが親チャンネル「Prime (米国)」を共有しているため、同じcidが保持されます)。顧客はパートナー様のチャンネルごとに異なるcid値を持つため、チャンネルごとの分析を可能にしつつプライバシーを確保できます。 |
benefit_id |
string |
Identifier |
チャンネル登録に関連付けられているPrime Videoの特典ID。これにより、チャンネル登録は特定のコンテンツアクセス権にリンクされます。 |
channel_label |
string |
テキスト |
チャンネル登録が属するチャンネルの名前。 |
channel_tier_label |
string |
テキスト |
チャンネル内の特定のティアの名前(該当する場合)。チャンネルがティアを使用していない場合、この値はnullになるか、channel_labelと一致する可能性があります。 |
注意: cidを使用すると、プライバシーを維持しながら顧客ライフサイクルを追跡できます。cidを使用して、個々の顧客の行動パターンを分析し、リテンションメトリクスを計算し、チャンネル再登録イベントを特定します。
時間ディメンション
これらのディメンションは、チャンネル登録イベントの時間的コンテキストを提供します。
ディメンション |
タイプ |
形式 |
説明 |
subscription_event_time_utc |
timestamp |
ISO 8601: YYYY-MM-DDTHH:MM:SS.sssZ |
チャンネル登録イベントが発生したときのタイムスタンプ。協定世界時(UTC)で保存されます。すべてのイベントのタイムスタンプは、一貫性を保つためにUTCで保存されます。レポート用にローカル時間に変換するには、subscription_event_time_zoneを使用します。 |
subscription_event_time_zone |
string |
タイムゾーン識別子(例: America/New_York、Europe/London) |
チャンネル登録イベントが発生したロケーションのタイムゾーン。Slate分析ダッシュボードには、ローカルタイムゾーンでデータが表示されます。 |
create_time_utc |
timestamp |
ISO 8601フォーマット |
チャンネル登録イベントレコードが変更ログに最初に作成されたときのタイムスタンプ。UTCで保存されます。これは、イベントが発生したときではなく、レコードが変更ログに追加された日時を表します。実際のイベント時間には、subscription_event_time_utcを使用します。 |
last_update_time_utc |
timestamp |
ISO 8601フォーマット |
チャンネル登録イベントレコードが変更ログに最後に更新されたときのタイムスタンプ。UTCで保存されます。レコードの重複排除には、常にこのフィールドを使用してください。同じsubscription_event_idのレコードが複数存在する場合は、最新のlast_update_time_utcのレコードのみを保持します。 |
変換例(Athena SQL):
-- Parse ISO 8601 string and convert to local timezone
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d %H:%i:%s.%f'
) AS TIMESTAMP) AS event_time_local
注意: すべての計算とフィルタリングにsubscription_event_time_utcを使用してください。レポートと表示を目的とする場合にのみ、subscription_event_time_zoneを使用してローカルタイムゾーンに変換します。
レコード管理ディメンション
これらのディメンションは、変更ログデータを正しく管理するのに役立ちます。
ディメンション |
タイプ |
形式 |
説明 |
subscription_event_id |
string |
UUIDフォーマット |
各チャンネル登録イベントの一意の識別子。これは重複排除の主キーです。イベントの属性が更新された場合、同じsubscription_event_idに対して複数のレコードが存在する可能性があります。常に最新のlast_update_time_utcを使用してレコードを保持することにより、重複排除を行ってください。 |
is_deleted |
integer |
0または1 |
以前に公開されたレコードをデータセットから削除するかどうかを示します。0 =アクティブなレコード(分析に含める)、1 =削除されたレコード(分析から除外-以前に取り込まれた場合はテーブルから削除)。レコードの削除はまれですが、データの修正が必要な場合に発生することがあります。クエリでは常にis_deleted = 0をフィルタリングしてください。 |
注意: 分析を実行する前に、必ずlast_update_time_utcを使用して重複排除を行ってください。ETLパイプラインの開始時に削除されたレコード(is_deleted = 1)を除外して、データの正確性を確保します。
データクレンジングと決済ロジック
このセクションでは、未加工のイベントデータをクリーンでビジネスに役立つメトリクスに変換するために使用される2段階の重複排除プロセスについて説明します。一部のパートナー様は異なる決済ロジックを使用しているため、柔軟に対応できるように未加工のイベントデータを提供しています。このセクションでは、パートナー様がメトリクスをSlate分析ダッシュボードと照合し、データのモデル化方法を確認できるように、Prime Videoで使用されているロジックについて説明します。
決済が重要な理由
未加工のチャンネル登録イベントデータには、メトリクスを膨らませたり歪めたりする可能性のあるノイズが含まれています:
問題 |
例 |
決済なしの影響 |
同日解約 |
顧客が数時間以内に開始してキャンセル |
StartとCancelの両方として二重にカウントされる |
支払い再試行 |
請求エラーによる複数のCancelイベント |
解約カウントが膨らむ |
開始せずにキャンセル |
対応するStartがライフサイクルにないCancelイベント |
顧客獲得数と一致しない解約の膨らみ |
決済ロジックにより、メトリクスにはシステムによって生成されたノイズではなく、有意義なチャンネル登録アクティビティが反映されます。
ステージ1: 一日の終わりの決済
一日の終り(EOD)状態のみを保持して、同日のStart + Canceのペアを処理します。顧客が同じ暦日にStart/RenewalとCancelの両方のイベントをもっている場合、その日の時系列で最後のイベントのみを保持します。同日キャンセルは、多くの場合、支払い承認の失敗または登録フローの問題を示しています。これらは登録者の有意義なエンゲージメントを示すものではなく、両方をカウントするとStart数とCancel数が膨らみます
実装
-- Step 1: Identify same-day start + cancel pairs
same_day_start_and_cancel AS (
SELECT cid, offer_marketplace, event_date_local
FROM deduped_events
GROUP BY cid, offer_marketplace, event_date_local
HAVING SUM(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN subscription_event_type = 'Cancel' THEN 1 ELSE 0 END) > 0
),
-- Step 2: Rank events by time within each day
events_with_eod_rank AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY e.cid, e.offer_marketplace, e.event_date_local
ORDER BY e.event_time_local DESC
) AS eod_rank
FROM deduped_events e
),
-- Step 3: For same-day pairs, keep only EOD event (rank = 1)
settled_events AS (
SELECT e.*
FROM events_with_eod_rank e
LEFT JOIN same_day_start_and_cancel s
ON e.cid = s.cid
AND e.offer_marketplace = s.offer_marketplace
AND e.event_date_local = s.event_date_local
WHERE s.cid IS NULL -- Not a same-day pair, keep all events
OR e.eod_rank = 1 -- Same-day pair, keep only last event
)
例
この表は、EOD決済ロジックが2025/10/15の同日のStartイベントとCancelイベントのペアをその日の終わりに最終状態のみを保って処理する方法を示しています。
時間 |
イベント |
EOD決済なし |
EOD決済あり |
2025/08/01 00:00 |
Start |
Startとしてカウント |
Startとしてカウント |
2025/09/01 00:00 |
Renewal |
- |
- |
2025/10/01 00:00 |
Renewal |
- |
- |
2025/10/15 07:00 |
Cancel |
Cancelとしてカウント |
除外 |
2025/10/15 09:00 |
Start |
Startとしてカウント |
除外 |
2025/10/15 09:15 |
Cancel |
Cancelとしてカウント |
Cancelとしてカウント |
注意: 顧客はCancelとしてのみカウントされ(または、ライフサイクルの状況によってはまったくカウントされない)、StartとCancelの両方としてはカウントされません。EOD決済は、決済済みのメトリクス(Start、Cancel)にのみ適用されます。スナップショットメトリクスは、変更ログで重複排除されたイベントからEOD決済なしで構築されたチャンネル登録期間を使用して、特定の時点の計算と変換に必要なイベントタイムライン全体を維持します。
ステージ2: ライフサイクル重複排除
イベントをチャンネル登録の「サイクル」にグループ化し、ライフサイクルごとに最初のStartと最初のCancelだけを保持します。有効なCancelには、同じライフサイクル内に対応するStartが必要です。
実装-- Step 1: Identify lifecycle boundaries
-- A new lifecycle starts when there's no previous event OR previous event was Cancel
events_with_lifecycle_boundaries AS (
SELECT e.*,
LAG(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS prev_event_type
FROM settled_events e
),
-- Step 2: Flag lifecycle starts
events_with_lifecycle_start_flag AS (
SELECT e.*,
CASE
WHEN prev_event_type IS NULL THEN 1 -- First event ever
WHEN prev_event_type = 'Cancel' THEN 1 -- After a cancel
ELSE 0
END AS is_lifecycle_start
FROM events_with_lifecycle_boundaries e
),
-- Step 3: Assign lifecycle numbers using cumulative sum
events_with_lifecycle_id AS (
SELECT e.*,
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS lifecycle_num
FROM events_with_lifecycle_start_flag e
),
-- Step 4: Keep only first Start and first Cancel per lifecycle
events_with_dedup_rank AS (
SELECT e.*,
ROW_NUMBER() OVER (
PARTITION BY cid, offer_marketplace, lifecycle_num,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 'Start' END
ORDER BY event_time_local
) AS start_rank,
ROW_NUMBER() OVER (
PARTITION BY cid, offer_marketplace, lifecycle_num,
CASE WHEN subscription_event_type = 'Cancel' THEN 'Cancel' END
ORDER BY event_time_local
) AS cancel_rank
FROM events_with_lifecycle_id e
),
deduped_lifecycle_events AS (
SELECT *
FROM events_with_dedup_rank
WHERE (subscription_event_type IN ('Start', 'Renewal') AND start_rank = 1)
OR (subscription_event_type = 'Cancel' AND cancel_rank = 1 AND lifecycle_has_start = 1)
)
lifecycle_has_start = 1条件を指定すると、ライフサイクルに対応するStartイベントがある場合にのみキャンセルがカウントされます。
例
顧客 |
イベント |
時間 |
ライフサイクル |
保持? |
ABC |
Start |
1/1/2025 |
1 |
✓ ライフサイクル1の最初のStart |
ABC |
Renewal |
2/1/2025 |
1 |
✗ ライフサイクル1の最初のStartではない |
ABC |
Cancel |
3/1/2025 |
1 |
✓ ライフサイクル1の最初のCancel |
ABC |
Start |
4/15/2025 |
2 |
✓ ライフサイクル2の最初のStart |
ABC |
Cancel |
5/15/2025 |
2 |
✓ ライフサイクル2の最初のCancel |
重要: EOD決済と同様に、ライフサイクル重複排除は決済済みのメトリクスにのみ適用されます。スナップショットメトリクスは、未加工のチャンネル登録スパンを使用します。Start/Renewalごとに次のイベントで終了するスパンが作成され、特定の時点の計算に必要な完全なタイムラインが維持されます。
メトリクス
メトリクスは、チャンネル登録イベントから計算された測定値です。ディメンションとは異なり、メトリクスはイベントログに直接保存されず、イベントデータを集計して変換することで計算されます。提供されている計算式は推奨値であり、ビジネス定義に合わせて調整できることに注意してください。
コアメトリクス
以下のメトリクスはチャンネル登録分析の基本です:
メトリック |
説明 |
End of Period Subscribers |
期間終了時に現在のチャンネル登録がある顧客 |
Active Subscribers |
期間中いつでも有効なチャンネル登録がある顧客 |
Subscriber Acquisition (Starts) |
獲得した新規登録者 |
Cancellations |
キャンセルした登録者 |
Free Trial Conversion Rate |
無料トライアルから有料へのコンバージョン |
Net Paid Starts |
コンバージョンを考慮した新規有料登録者 |
登録者メトリクス
これらのメトリクスは、決済とライフサイクル重複排除を適用した後の新規加入者の獲得とキャンセルを追跡します。
メトリック |
定義 |
計算 |
num_starts |
レポート期間内の新しいチャンネル登録ライフサイクルの開始を示すStartイベントとRenewalイベントの数。 |
決済とライフサイクル重複排除を適用した後、subscription_event_type IN (‘Start’, ‘Renewal’)かつ期間内に該当するイベントをカウントします。 SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts |
num_starts_ft |
オファー請求タイプが無料トライアル(FT)である場合のStartの数。 |
SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND offer_billing_type = ‘FT’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts_ft |
num_starts_ho |
オファー請求タイプが「ハードオファー(HO)」(有料チャンネル登録)である場合のStartの数。 |
SUM(CASE WHEN subscription_event_type IN (‘Start', ‘Renewal') AND offer_billing_type = ‘HO’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_starts_ho |
num_net_starts_ho |
有料サービスの正味新規登録者数。直接的なHOの開始と無料トライアルのコンバージョン数を足して計算します。有料プランに直接登録したのか、無料トライアルから切り替えたのかを問わず、新しい有料登録者の全体像を把握できます。 |
num_net_starts_ho = num_starts_ho + num_ft_conversions |
num_cancels |
決済とライフサイクル重複排除を適用した後のレポート期間内のCancelイベントの数。 |
SUM(CASE WHEN subscription_event_type = ‘Cancel’ AND event_time_local >= period_start AND event_time_local < period_end THEN 1 ELSE 0 END) AS num_cancels |
num_cancels_ho |
登録者がハードオファー(有料)プランを利用していた場合のCancellations。 |
- |
num_cancels_ft |
登録者が無料トライアルプランを利用していた場合のCancellations(トライアルがコンバージョンなしで終了)。 |
- |
期間メトリクス
これらのメトリクスは、EOD決済なしで構築されたチャンネル登録期間を使用して、特定の時点のスナップショットと期間中のアクティビティを提供します。
メトリック |
定義 |
計算 |
バリアント |
num_end_period_subs |
レポート期間の終了時にアクティブだった一意の登録者の数。これは特定の時点のスナップショットです。 |
subscription_spansから(EOD決済なしで構築) COUNT(DISTINCT CASE WHEN span_start_date < period_end AND (span_end_date IS NULL OR span_end_date >= period_end) THEN cid END) AS num_end_period_subs |
num_end_period_subs_ho —ハードオファープランの期間終了時の登録者、num_end_period_subs_ft —無料トライアルプランの期間終了時の登録者 |
num_active_period_subs |
レポート期間中の任意の時点でアクティブだった一意の登録者の数。開始日またはキャンセル日に関係なく、有効なチャンネル登録期間がその期間と重複していたすべての顧客をキャプチャします。 |
subscription_spansから(EOD決済なしで構築) COUNT(DISTINCT CASE WHEN span_start_date < period_end AND (span_end_date IS NULL OR span_end_date >= period_start) THEN cid END) AS num_active_period_subs |
num_active_period_subs_ho —ハードオファー(有料)プラン期間中の任意の時点でアクティブな登録者- num_active_period_subs_ft —無料トライアルプラン期間中の任意の時点でアクティブな登録者 |
注意: 期間メトリクスは、EOD決済なしで構築されたsubscription_spansを使用して、イベントタイムライン全体を維持します。
無料トライアルのメトリクス
これらのメトリクスは、EOD決済なしで構築されたチャンネル登録期間を使用して、無料トライアルのコンバージョンと決済を追跡します。
メトリック |
定義 |
計算 |
num_ft_conversions |
レポート期間内に有料(ハードオファー)プランに切り替えた無料トライアルの登録者の数。無料トライアル(offer_billing_type = ‘FT’)を利用しているユーザーの次のイベントがoffer_billing_type = ‘HO’のStart/Renewalである場合に、コンバージョンが発生します。 |
subscription_spansから(EOD決済なしで構築) COUNT(CASE WHEN offer_billing_type = ‘FT’ AND span_end_billing_type = ‘HO’ AND span_end_event_type IN (‘Start', ‘Renewal') AND span_end_date >= period_start AND span_end_date < period_end THEN 1 END) AS num_ft_conversions |
num_ft_settled |
レポート期間内に決済された(完了した)無料トライアルのチャンネル登録の数。トライアルは、登録者が有料に切り替えるか、キャンセルした場合(次の請求タイプがFTではない)場合に決済されます。 |
COUNT(CASE WHEN offer_billing_type = ‘FT’ AND span_end_date IS NOT NULL AND span_end_date >= period_start AND span_end_date < period_end THEN 1 END) AS num_ft_settled |
ft_conversion_rate_pct |
決済済みの無料トライアルのうち、有料チャンネル登録に切り替えられたもののパーセンテージ。 |
CASE WHEN num_ft_settled > 0 THEN ROUND(100.0 * num_ft_conversions / num_ft_settled, 2) ELSE 0 END AS ft_conversion_rate_pct |
注意: 無料トライアルのメトリクスは、EOD決済なしで作成されたsubscription_spansを使用して、顧客が1つのoffer_idから別のoffer_idに切り替えたコンバージョンを正確にトラッキングします。
イベントパターンを理解する
このセクションでは、チャンネル登録イベントログで発生する一般的なイベントパターンと、それらを正しく解釈する方法について説明します。
チャンネル登録ライフサイクル
一般的なチャンネル登録はこのライフサイクルをたどり、最初の登録から更新サイクルを経て、潜在的なキャンセルまで、4つの主要なステージを辿ります。各ステージでは特定のイベントタイプが生成され、顧客の行動を追跡したり、チャンネル登録メトリクスを計算したりできます。
主要なライフサイクルポイント
これらの主要なライフサイクルポイントは、顧客のチャンネル登録ジャーニーにおける4つの重要なステージを表します。それぞれ異なるイベントタイプを生成して、獲得、リテンション、エンゲージメント、解約のパターン追跡を可能にします。
ステージ |
イベントタイプ |
説明 |
獲得 |
Start |
新規顧客があなたのチャンネルに参加します |
リテンション |
Renewal |
既存の顧客がチャンネル登録を継続します |
エンゲージメント |
Active - AR ON/OFF |
顧客が自動更新の設定を調整します |
解約 |
Cancel |
顧客のチャンネル登録が終了します |
一般的なイベントパターン
パターン1: StartなしでRenewal
ある顧客のイベント履歴にRenewalイベントが表示されていますが、Startイベントは表示されていません。顧客の元のStartイベントは2年以上前に発生しました。データ保持ポリシーにより、2年以上前のイベントは利用できません。ウィンドウ内のRenewalイベントには引き続きアクセスできます。
パターン2: 複数のCancelイベント
ある顧客について、数分または数時間以内に複数回のCancelイベントが発生しています。このパターンは通常、支払いの再試行が失敗したことを示しています: 請求システムは顧客の支払い方法への請求を試みます。試行が失敗するたびに、Cancelイベントが生成される場合があります。顧客は、拒否されたカードでチャンネル再登録を試みている可能性があります。ライフサイクル重複排除により、ライフサイクルごとに最初のCancelのみが保持されます。
パターン3: 同日のStartとCancel
ある顧客について、同じ暦日にStartとCancelの両方が発生しています。一般的な原因には以下が含まれます:
- 顧客が登録してすぐにキャンセルした
- 初回登録後に支払い承認に失敗した
- 不正登録の試みがブロックされた
EOD決済はこれを自動的に処理し、最終状態のみが保持されます。
パターン4: 無料トライアルから有料へのコンバージョン
ある顧客について、offer_billing_type = ‘FT’のStartに続き、offer_billing_type = ‘HO’の別のStartまたはRenewalが発生しています。顧客は無料トライアルを完了し、有料チャンネル登録に切り替えました。LEAD ()ウィンドウ関数を使用して、offer_billing_typeがFTからHOにいつ変化するかを検出します。移行イベントがレポート期間内にある場合は、コンバージョンとしてカウントされます。
自動更新イベント
自動更新(AR)イベントは、顧客がチャンネル登録の自動更新を有効または無効にしたときに追跡します。これらのイベントは、将来の解約率を予測し、リスクのある登録者を特定するための早期のシグナルとなります。
イベントタイプ |
説明 |
ユースケース |
AR_ON |
顧客が自動更新を有効にしました。チャンネル登録は、現在の期間の終了時に自動的に更新されます。 |
チャンネル登録の継続にコミットした顧客を追跡します。 |
AR_OFF |
顧客が自動更新を無効にしました。自動更新を再度有効にしない限り、チャンネル登録は現在の期間の終了時に無効となります。 |
早期警告シグナル: 自動更新を無効にする顧客は、チャンネル登録が実際に終了する前に登録解除の意思を示しています。解約予測: AR OFF登録者は、自動更新を再度有効にしない限り、期間終了時に必ず解約されます。そのため、AR OFFイベントは将来の解約率を予測する最も信頼できるメトリクスのひとつとなっています。 |
注意: 自動更新イベントは、顧客がチャンネル登録管理インターフェイスを使用して自動更新設定を積極的に切り替えるたびに記録されます。
ARイベントの分析
自動更新動作を分析するには、メインのライフサイクメトリクスパイプラインとは別にAR ON/OFFイベントをクエリします。ARイベントは同じ変更ログの重複排除ルールに従いますが、Start/Renewal/Cancelのライフサイクルの計算には含まれません。
-- Example:Filter to AR Off Events
SELECT *
FROM deduped_events
WHERE subscription_event_type = 'AR_OFF'
GROUP BY 1, 2
ORDER BY 1, 2
ETLパイプライン
上記のメトリック定義とデータ変換に基づき、このETL (Extract, Transform, Load)パイプラインを使用して、未加工のチャンネル登録イベントログデータをクリーンでビジネスに役立つメトリクスに変換します。このクエリはAmazon Athenaで直接実行できます。
パイプラインは2パスのアーキテクチャを実装して、さまざまなメトリクスタイプを正しく計算します:
- パスA: 決済済みイベント— EOD決済「および」ライフサイクル重複排除を適用します。決済済みメトリクス(Start、Cancel)に使用されます。これにより、実際の獲得を意味しない同日のStart+Cancelのペアなどのノイズが除外されます。
- パスB: チャンネル登録期間 — 変更ログの重複排除「のみ」を適用します。スナップショットメトリクス(期間終了時のカウント、FTコンバージョン)に使用されます。
-- ============================================================================
-- SUBSCRIPTION METRICS ETL PIPELINE
-- ============================================================================
-- Transforms raw subscription events into clean metrics using TWO PATHS:
-- PATH A: Settled Events - for settled metrics (starts, cancels)
-- Applies: Changelog dedup → EOD settlement → Lifecycle dedup
-- Filters out noise like same-day start+cancel pairs
-- PATH B: Subscription Spans - for snapshot metrics (end-of-period, conversions)
-- Applies: Changelog dedup ONLY (preserves full timeline)
-- Required for conversions because customer switches offer_id (A → B)
-- ============================================================================
WITH
-- =============================================================================
-- STEP 1: Parse timestamps and convert to local timezone
-- =============================================================================
parsed_events AS (
SELECT
subscription_event_id,
subscription_event_type,
subscription_event_time_utc,
subscription_event_time_zone,
cid,
offer_id,
offer_name,
offer_marketplace,
offer_billing_type,
offer_payment_amount,
is_promo,
channel_label,
channel_tier_label,
benefit_id,
is_deleted,
last_update_time_utc,
-- Parse and convert timestamps
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''') AS event_time_utc,
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d %H:%i:%s.%f'
) AS TIMESTAMP) AS event_time_local,
CAST(DATE_FORMAT(
AT_TIMEZONE(
parse_datetime(subscription_event_time_utc, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'''),
subscription_event_time_zone
),
'%Y-%m-%d'
) AS DATE) AS event_date_local
FROM your_subscription_events_table
WHERE is_deleted = 0
AND subscription_event_type IN ('Start', 'Renewal', 'Cancel')
),
-- =============================================================================
-- STEP 2: Deduplicate changelog records (used by BOTH paths)
-- =============================================================================
deduped_events AS (
SELECT * FROM (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY subscription_event_id
ORDER BY last_update_time_utc DESC
) AS dedup_rank
FROM parsed_events
)
WHERE dedup_rank = 1
),
-- =============================================================================
-- PATH A: SETTLED EVENTS (for starts, cancels)
-- Applies: Changelog dedup → EOD settlement → Lifecycle dedup
-- =============================================================================
-- STEP A1: EOD Settlement - Identify same-day Start+Cancel pairs
same_day_start_and_cancel AS (
SELECT cid, offer_marketplace, event_date_local
FROM deduped_events
GROUP BY cid, offer_marketplace, event_date_local
HAVING
SUM(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN subscription_event_type = 'Cancel' THEN 1 ELSE 0 END) > 0
),
-- Rank events by time within each day
events_with_eod_rank AS (
SELECT
e.*,
ROW_NUMBER() OVER (
PARTITION BY e.cid, e.offer_marketplace, e.event_date_local
ORDER BY e.event_time_local DESC
) AS eod_rank
FROM deduped_events e
),
-- Apply EOD settlement: for same-day pairs, keep only the last event
eod_settled_events AS (
SELECT e.*
FROM events_with_eod_rank e
LEFT JOIN same_day_start_and_cancel sd
ON e.cid = sd.cid
AND e.offer_marketplace = sd.offer_marketplace
AND e.event_date_local = sd.event_date_local
WHERE sd.cid IS NULL OR e.eod_rank = 1
),
-- STEP A2: Build customer lifecycles (on EOD settled data)
settled_events_with_prev AS (
SELECT
*,
LAG(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS prev_event_type
FROM eod_settled_events
),
settled_events_with_lifecycle_flag AS (
SELECT
*,
CASE
WHEN prev_event_type IS NULL THEN 1
WHEN prev_event_type = 'Cancel' THEN 1
ELSE 0
END AS is_lifecycle_start
FROM settled_events_with_prev
),
settled_events_with_lifecycle AS (
SELECT
*,
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS lifecycle_num,
cid || '|' || offer_marketplace || '|' || CAST(
SUM(is_lifecycle_start) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
ROWS UNBOUNDED PRECEDING
) AS VARCHAR
) AS lifecycle_id
FROM settled_events_with_lifecycle_flag
),
-- STEP A3: Lifecycle deduplication
events_with_type_rank AS (
SELECT
*,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN
ROW_NUMBER() OVER (
PARTITION BY lifecycle_id,
CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 'START' END
ORDER BY event_time_local
)
END AS start_rank,
CASE WHEN subscription_event_type = 'Cancel' THEN
ROW_NUMBER() OVER (
PARTITION BY lifecycle_id,
CASE WHEN subscription_event_type = 'Cancel' THEN 'CANCEL' END
ORDER BY event_time_local
)
END AS cancel_rank,
MAX(CASE WHEN subscription_event_type IN ('Start', 'Renewal') THEN 1 ELSE 0 END) OVER (
PARTITION BY lifecycle_id
) AS lifecycle_has_start
FROM settled_events_with_lifecycle
),
settled_events AS (
SELECT *
FROM events_with_type_rank
WHERE
(subscription_event_type IN ('Start', 'Renewal') AND start_rank = 1)
OR (subscription_event_type = 'Cancel' AND cancel_rank = 1 AND lifecycle_has_start = 1)
),
-- =============================================================================
-- PATH B: SUBSCRIPTION SPANS (for conversions, end-of-period)
-- Applies: Changelog dedup ONLY (NO EOD settlement, NO lifecycle dedup)
-- This preserves the full timeline needed for accurate span calculations
-- =============================================================================
-- STEP B1: Calculate LEAD() on ALL deduped events (before any filtering)
events_with_span_boundaries AS (
SELECT
cid,
offer_marketplace,
offer_id,
offer_billing_type,
subscription_event_type,
event_time_local,
event_date_local,
LEAD(event_time_local) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_event_time,
LEAD(subscription_event_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_event_type,
LEAD(offer_billing_type) OVER (
PARTITION BY cid, offer_marketplace
ORDER BY event_time_local
) AS next_billing_type
FROM deduped_events -- Uses deduped_events directly, NOT eod_settled_events
),
-- STEP B2: Create spans from Start/Renewal events
subscription_spans AS (
SELECT
cid,
offer_marketplace,
offer_id,
offer_billing_type,
subscription_event_type,
event_time_local AS span_start,
event_date_local AS span_start_date,
next_event_time AS span_end,
CAST(next_event_time AS DATE) AS span_end_date,
next_event_type AS span_end_event_type,
next_billing_type AS span_end_billing_type
FROM events_with_span_boundaries
WHERE subscription_event_type IN ('Start', 'Renewal')
),
-- =============================================================================
-- STEP 3: Generate reporting periods
-- =============================================================================
reporting_periods AS (
SELECT
period_month,
CAST(period_month AS DATE) AS period_start,
CAST(DATE_ADD('month', 1, period_month) AS DATE) AS period_end
FROM UNNEST(SEQUENCE(DATE '2025-01-01', DATE '2025-12-01', INTERVAL '1' MONTH)) AS t(period_month)
),
-- =============================================================================
-- STEP 4A: SETTLED METRICS (from settled events - with EOD + lifecycle dedup)
-- Use for: starts, cancels
-- =============================================================================
settled_metrics AS (
SELECT
DATE_FORMAT(p.period_start, '%Y-%m') AS report_month,
e.offer_marketplace,
-- Starts (all)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts,
-- Starts (Free Trial)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.offer_billing_type = 'FT'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts_ft,
-- Starts (Hard Offer / Paid)
SUM(CASE WHEN e.subscription_event_type IN ('Start', 'Renewal')
AND e.offer_billing_type = 'HO'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_starts_ho,
-- Cancels (all)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels,
-- Cancels (Hard Offer)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.offer_billing_type = 'HO'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels_ho,
-- Cancels (Free Trial)
SUM(CASE WHEN e.subscription_event_type = 'Cancel'
AND e.offer_billing_type = 'FT'
AND e.event_date_local >= p.period_start
AND e.event_date_local < p.period_end
THEN 1 ELSE 0 END) AS num_cancels_ft
FROM settled_events e
CROSS JOIN reporting_periods p
GROUP BY DATE_FORMAT(p.period_start, '%Y-%m'), e.offer_marketplace
),
-- =============================================================================
-- STEP 4B: SNAPSHOT METRICS (from subscription spans - NO EOD settlement)
-- Use for: end-of-period counts, conversions, settled trials
-- =============================================================================
snapshot_metrics AS (
SELECT
DATE_FORMAT(p.period_start, '%Y-%m') AS report_month,
s.offer_marketplace,
-- End of period counts (who was active at period end?)
COUNT(DISTINCT CASE
WHEN s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'HO'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs_ho,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_end)
THEN s.cid
END) AS num_end_period_subs_ft,
-- Active at any point during the period
COUNT(DISTINCT CASE
WHEN s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'HO'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs_ho,
COUNT(DISTINCT CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_start_date < p.period_end
AND (s.span_end_date IS NULL OR s.span_end_date >= p.period_start)
THEN s.cid
END) AS num_active_period_subs_ft,
-- Conversions (FT span ending with HO start in this period)
COUNT(CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_end_billing_type = 'HO'
AND s.span_end_event_type IN ('Start', 'Renewal')
AND s.span_end_date >= p.period_start
AND s.span_end_date < p.period_end
THEN 1
END) AS num_ft_conversions,
-- FT Settled (FT span that ended in this period)
COUNT(CASE
WHEN s.offer_billing_type = 'FT'
AND s.span_end_date IS NOT NULL
AND s.span_end_date >= p.period_start
AND s.span_end_date < p.period_end
THEN 1
END) AS num_ft_settled
FROM subscription_spans s
CROSS JOIN reporting_periods p
GROUP BY DATE_FORMAT(p.period_start, '%Y-%m'), s.offer_marketplace
)
-- =============================================================================
-- FINAL OUTPUT: Combine flow and snapshot metrics
-- =============================================================================
SELECT
sm.report_month,
sm.offer_marketplace,
-- Starts (from settled events)
sm.num_starts,
sm.num_starts_ft,
sm.num_starts_ho,
-- Net Starts (paid starts + conversions)
sm.num_starts_ho + sn.num_ft_conversions AS num_net_starts_ho,
-- Cancels (from settled events)
sm.num_cancels,
sm.num_cancels_ho,
sm.num_cancels_ft,
-- Free Trial Metrics (from spans)
sn.num_ft_conversions,
sn.num_ft_settled,
CASE WHEN sn.num_ft_settled > 0
THEN ROUND(100.0 * sn.num_ft_conversions / sn.num_ft_settled, 2)
ELSE 0
END AS ft_conversion_rate_pct,
-- Active Subscribers (from spans)
sn.num_active_period_subs,
sn.num_active_period_subs_ho,
sn.num_active_period_subs_ft,
-- End of Period Subscribers (from spans)
sn.num_end_period_subs,
sn.num_end_period_subs_ho,
sn.num_end_period_subs_ft
FROM settled_metrics sm
JOIN snapshot_metrics sn
ON sm.report_month = sn.report_month
AND sm.offer_marketplace = sn.offer_marketplace
ORDER BY sm.report_month, sm.offer_marketplace
注意: your_subscription_events_tableを実際のテーブル名に置き換えてください。分析のニーズに合わせてreporting_periodsの日付範囲を調整してください。
