Scenario
A user was working with the Analytics Subscription Line dataset and wanted to build a monthly recurring revenue report that included Customer, Item, Quantity, and Value.
The user was able to retrieve the quantity, but the value field returned the total subscription period amount instead of the monthly value. The user wanted to limit the result to one month, but could not find a criteria option that would restrict the value that way.
The user also found that the price was not available directly in the dataset and asked whether joining to a Price Plan dataset was the correct approach.
Solution
In the Analytics Subscription Line dataset, the “amount/total” style measures are typically term-level (the full subscription/line total for the entire subscription period), so what you’re seeing is expected. To get a monthly MMR/MRR value, you usually need either:
- a dataset that already has periodized amounts by date/range, or
- to derive a monthly amount from term attributes (only works if the pricing is perfectly flat and non-prorated).
Subscription Line: compute a “monthly value” (only for flat pricing)
If pricing is flat and the line total is for the whole term, you can approximate MMR by allocating the total across months. Conceptually:
Monthly Value ≈ (Term Value) / (Number of months in term)
(and optionally × Quantity depending on whether your “Term Value” is per unit or extended)
In Analytics, this means creating a calculated measure like:
- TermAmount / TermInMonths
About “price not natively available” + joining to Price Plan
In many setups the Subscription Line dataset does not directly expose the effective monthly price, and pricing is defined in Price Plans / Price Plan Lines.
How joining typically works (high level)
In a Workbook you would:
- Add Analytics Subscription Line dataset
- Add Price Plan (or Subscription Price Plan / Price Plan Line) dataset
- Create a join using the linking IDs, typically along the lines of:
- Subscription Line’s Price Plan reference (internal ID)
- to Price Plan’s Internal ID
- and sometimes also match the Item or Price Plan Line key (depending on how the dataset is modeled)
Even after joining to Price Plan, you may still not get a clean “monthly value” unless the price plan dataset provides:
- a rate, and
- a billing frequency / recurrence, and/or
- effective dates that let you isolate a month.
So the join helps you find the rate, but it doesn’t automatically solve monthly allocation unless the price plan lines are period-specific.
-----
If you find this information useful, let us know by reacting or commenting on this post!
