Calling Subscriptions Summary

Examples of how CallingSubscriptionsProductSummary handles edge cases. Sources are StgAutomaticProductPurchaseHistory (subscription events) and FactPaymentAnalytic (payments).

1. Created and deactivated without payment

UserId = 48692326, ProductId = 8188: created 2024-07-23, deactivated 2024-09-06, no payment found for the product (payments at the same dates are missing a ProductId). The model skips the Created event and shows Created and Deactivated on the deactivation date.

2. Starts with a Deactivation event

UserId = 50195540, ProductId = 8094: deactivated 2025-07-21 and activated later that day. Payments exist before the deactivation, with ApplicationId = 20001 (automated). The model assumes the initial Created event is missing, takes all payments until the deactivation, and puts Created on the first payment date.

3. Trial payments inside the subscription cycle

UserId = 49974284, ProductId = 8092: the subscription was created 2025-02-06 without the Trial ProductId, with back-to-back Activation events. The initial payment used the Trial product 8592. The model captures it, starts the BillingCycle increment there and marks SubscriptionSubStatus = 'Trial'. An Activation on 2025-05-10 while already active is ignored.

4. Reactivation through unsuccessful payments

UserId = 51821356, ProductId = 6161: deactivated 2025-12-18, reactivated 2026-01-12 with no payment that day. The first payment (2026-01-17) failed, the first successful one was 2026-01-19. The model deactivates on 2025-12-18 and reactivates on the date of the first successful payment after the reactivation state. Unsuccessful payments outside activated dates are not shown.

Inspect a case:

select *
from dw4_prod.stage."StgAutomaticProductPurchaseHistory"
where "UserId" = 51821356
order by "CreatedAt";

select *
from dw4_prod.dbt."CallingSubscriptionsProductSummary"
where "UserId" = 51821356 and "ProductId" = 6161
order by "Datetime";