We are trying to generate a report that will show us a summary of products on cancelled subscriptions distributed along the dates the subscriptions' cancellation amendments were created—not the date the subscription actually expires. So far, that is what we have set up right now _(see screenshot)_. The challenge we're facing is filtering it so that only subscription with at least one successful payment are counted. We offer a free trial that is set as a future dated subscription that will stay unbilled if they cancel during the trial. However, we don't want these trial cancellations to get recorded as cancellation in this report. This was easy to do with our acquisitions report, as building off the InvoiceItem data-source we were able to filter Invoice.PaymentAmount>0. However, most cancellation amendments never generate an invoice, as there is no revenue to record—and we cannot look at older invoices as they will record data for a subscription version with a different Subscription.UpdatedDate from the cancellation amendment we want to record. We attempted to build it with the RatePlanCharge dataset instead, but we have not found a field to track a product's payment history. The closest I have achieved is filtering out subscriptions without renewals, as all renewed subscriptions have theoretically been paid for once; unfortunately this filters out accounts that paid for their initial term, but canceled before renewing. Are there any solutions or workarounds I could look into?
... View more
