Question

Analyzing Recurly data in Looker

  • 22 June 2018
  • 2 replies
  • 99 views

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!


2 replies

Userlevel 3

Hey @akshay524,



This use case sounds most similar to our State or Status analytics block, which you can find here: https://looker.com/platform/blocks/analytic/state-or-status-data-and-slowly-changing-dimensions



Let me know if you see any differences between what you’d like to build and the documentation provided there, and we can discuss how to adapt that block to your particular use case. Also, there are some links in the related discourse article on learnbeta, one of our learning instances. If you’d like us to set you up with an account for this instance, please send an email to help.looker.com to request access to learnbeta.looker.com, and provide the email you’d like to use for login.

Hey Chris,



Thanks for sharing that! Yeah, i think that helps - will check out the code in the block.



I think is there definitely a lack of SaaS granular analysis knowledge share out there. Always on the lookout for good resources there.



Cheers,


Akshay

Reply