I need to create a table or a set of tables that needs to house transactional data of a subscription-based service, so that certain metrics can be queried efficiently paired with necessary post-processing to put together the data for presentation (something like this).

Currently, transactional data is stored in various tables with different schemes and schemas that make it impossible to query and get some needed information. e.g. For one payment method, a subscription and all subsequent events are updated on the same row in a table, losing most historical data.

For any given period (say per month), some of the desired metrics are...

  1. Number of active subscribers
  2. Number of new subscribers
  3. Number of cancellations
  4. Recurring revenue (payments can be monthly or annual)
  5. Derived metrics using more basic (1-4) metrics: churn rate, cost of acquisition, etc. (may require data from completely other sources - e.g. marketing)

I was thinking of creating a generic table that records one row per any transaction, like below, in additional to existing tables. I think it will be sufficient for getting accurate numbers, but it still requires the user to make multiple queries (especially for derived metrics) and massaging the data. I would like what I create to be as analyst-friendly as possible while being optimal.

Does anyone have experience with this business model and saving well-formed transactional data for it?

Generic table:

| id | user_id | platform | plan_type | action | amount |
  • Are you planning on going back to the original tables for some data? If so, is there a standard way of handling the PKs for those tables (all one single ID for example)? – Egret Jun 25 '18 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.