Not sure if this is the right category to post in…
I’d love to connect with someone who has worked with SaaS data in Looker (preferably SaaS data from Recurly). I’m trying to figure out some best practices for working with SaaS data. There are bunch of posts online talking about what to look at but few talk about how.
The key challenge is that most SaaS data is stored as the current subscriber state. From an analytics perspective, you’d want to know subscriber states at various points of their billing cycle + the events that happened in each cycle (state changes, expansion, contraction etc.).
Curious to know if anyone has gone through the exercise of converting subscriber state data into something more meaningful that’s useful for analysis? How did you architect it? What are some of the challenges in that process?
Here’s an example of one of the challenges I’m trying to solve:
I have the data about when the subscriber activated + all adjustments that were made to the subscriber account (with their dates and amounts). I want to create a table that allows users to explore a subscriber by billing cycle. Each row represents a billing cycle for a subscriber, plus changes from the previous billing cycle (expansion, contraction etc.) - all the way until they churn or the present month. At the same time, I also want to amortize annual contracts into monthly contracts so I have the actual revenue contribution given by a subscriber.
The combination of amortization + wrapping billing adjustments into billing cycles seems really tough to get at and I like to approach it in a way that avoids all edge cases (users moving between annual and monthly billing patters, refunds etc.)
Thanks for taking the time to read through all of that!