93 lines
No EOL
7.9 KiB
Markdown
93 lines
No EOL
7.9 KiB
Markdown
# KPIs Refactor -2025-04-01
|
||
|
||
# Current State
|
||
|
||
Cross models in KPIs is a bit… well, messy.
|
||
|
||
The goal of the refactor is on: `int_monthly_aggregated_metrics_history_by_deal`, which only depends on KPIs models. This was intended to aggregate ALL metrics that are needed for Main KPIs - Detail by Deal / Deal Comparison tabs.
|
||
|
||
|
||
|
||
Figure 1. Models used to compute int_monthly_aggregated_metrics_history_by_deal. Note how all the dependencies are within KPIs scope.
|
||
|
||
This model is mostly used for 2 use-cases: Main KPIs and Account Managers reporting. However, there’s actually 5 direct downstream models: 2 in the scope of Account Managers and 3 in the scope of Main KPIs.
|
||
|
||
|
||
|
||
Figure 2. Models that depend on int_monthly_aggregated_metrics_history_by_deal. In Red we have Account Managers related dependants, Growth Score and Margin. In Purple we have Churn Rates models, that are a by deal compute that gets attributed to the dimension-based Main KPIs. Similarly, in Yellow, we have a similar setup for Onboarding MRR. Lastly, in Green, we have the Deal Metrics, almost a “copy” of the model to reporting, to feed the Detail by Deal and Deal Comparison tabs in Main KPIs.
|
||
|
||
Let’s deep dive:
|
||
|
||
- Account Managers reporting
|
||
- Growth Score - `int_monthly_growth_score_by_deal`: populates the Account Managers Overview, mostly to compute the growth score.
|
||
- Margin - `int_monthly_aggregated_metrics_history_by_deal_by_time_window`: populates both Account Margin and Churn Report.
|
||
- Main KPIs
|
||
- Churn Rates - `int_monthly_12m_window_contribution_by_deal`: used as a first step to compute Churn Rate metrics.
|
||
- Onboarding MRR - `int_monthly_onboarding_mrr_per_deal`: used as a first step to compute Onboarding MRR metrics
|
||
- Deal Metrics - `monthly_aggregated_metrics_history_by_deal`: populates Detail by Deal and Deal Comparison tabs. *This is the only model that requires ALL metrics displayed in Figure 1*.
|
||
|
||
Now, it’s important to note that:
|
||
|
||
- Churn Rates + Growth Score only need Created Bookings, Listings Booked in X and Total Revenue metrics
|
||
- Onboarding MRR only needs Total Revenue metrics.
|
||
- Margin only needs Created Bookings, Listings Booked in X, Total Revenue, Total Revenue Contributions (Invoiced Operator Revenue, Guest Revenue, APIs Revenue), Revenue Retained and Contributions (RRPR, Waiver Paid Back to Host, Host Resolutions)
|
||
|
||
Note that the above is in terms of metrics. There’s different logic in place and these usually rely on Deal attributes, mostly coming from Hubspot. In any case these should be within `int_kpis__dimension_deals` or similar.
|
||
|
||
## Additional notes
|
||
|
||
- It’s arbitrary to cut Deal Metrics only at Monthly level (not having performance in month, namely, MTD). Account management could benefit from having more timely data, as raised recently by stakeholders.
|
||
- Detail by Deal and Deal Comparison tabs are… well… a mess. I doubt someone would need this amount of information. If this was actually needed, this could be - potentially - a new dimension, being the dimension-value the `id_deal`. And then we’d just benefit from the existing Main KPIs tabs that allow the deep-dive. We’d just need to test performance!
|
||
|
||
# Refactor plan
|
||
|
||
The main issue is that we only computed Revenue (Total, Retained, etc) on two models in cross. Why don’t we actually compute these on KPIs? and this is the exact point!
|
||
|
||
The following plan allows to refactor step by step by ensuring that if we need to switch priorities we will always keep a working setup (as long as each stage is finished…!)
|
||
|
||
Technically once Step 1 is done, Step 2 vs. Step 3+4 are interchangeable. I just decided to focus on Stage 2 as more priority because of the potential immediate benefit we would have if priorities shift. In other words, it’s a faster enabler.
|
||
|
||
## Stage 1: Total Revenue and Revenue Retained in KPIs
|
||
|
||
In Stage 1 we want to handle the logic of Total Revenue and Revenue Retained in KPIs, and remove the logic of computing these metrics in cross. The rest would just keep existing as is, without any impact on downstream dependencies or reports. This consists in:
|
||
|
||
- Create a Total Revenue KPI set of models (in intermediate/kpis folder)
|
||
- Create a Revenue Retained KPI set of models (in intermediate/kpis folder)
|
||
- Make `int_monthly_aggregated_metrics_history_by_deal` depend on these 2 newly created sets of models
|
||
- Make `int_mtd_vs_previous_year_metrics` depend on these 2 newly created sets of models
|
||
|
||
## Stage 2: Decouple Account Managers from By Deal KPIs
|
||
|
||
In Stage 2 we just want to decouple the source models in cross that depend on `int_monthly_aggregated_metrics_history_by_deal` for Account Management purposes. This consists in:
|
||
|
||
- Refactor `int_monthly_growth_score_by_deal` so it reads directly from the needed models in KPIs, instead of the current ALL metrics version.
|
||
- Refactor `int_monthly_aggregated_metrics_history_by_deal_by_time_window` so it reads directly from the needed models in KPIs, instead of the current ALL metrics version.
|
||
|
||
Once Step 2 is finished, we’d effectively removed the dependency of Main KPIs - By Dimension being dependant on By Deal models. In other words, it would no longer depend on `int_monthly_aggregated_metrics_history_by_deal`.
|
||
|
||
## Stage 3: Churn Rates in KPIs
|
||
|
||
In Stage 3 we want to compute Churn Rates in KPIs. These are logic intensive models but the only purpose is to compute KPIs for Main KPIs in the by dimension approach (not by deal). This consists in:
|
||
|
||
- Flag existing Churn Rates models as to be deprecated: `int_monthly_12m_window_contribution_by_deal` + `int_monthly_churn_metrics`
|
||
- Create a Churn Rates KPI set of models (in intermediate/kpis folder)
|
||
- Make `int_mtd_vs_previous_year_metrics` depend on this new set of models
|
||
- Remove deprecated models
|
||
|
||
## Stage 4: Onboarding MRR in KPIs
|
||
|
||
In Stage 4 we want to compute Onboarding MRR in KPIs. These are logic intensive models but the only purpose is to compute KPIs for Main KPIs in the by dimension approach (not by deal). This consists in:
|
||
|
||
- Flag existing Onboarding MRR models as to be deprecated: `int_monthly_onboarding_mrr_per_deal` + `int_mtd_agg_onboarding_mrr_revenue`
|
||
- Create a Onboarding MRR KPI set of models (in intermediate/kpis folder)
|
||
- Make `int_mtd_vs_previous_year_metrics` depend on this new set of models
|
||
- Remove deprecated models
|
||
|
||
Once Step 4 is finished, we’d effectively removed the dependency of Main KPIs - By Dimension being dependant on By Deal models. In other words, it would no longer depend on `int_monthly_aggregated_metrics_history_by_deal`.
|
||
|
||
# Potential Next Steps
|
||
|
||
There’s 2 main stages that need to be accomplished, allowing for potential improvements. Other possibilities might emerge in the meantime so take them as a guideline:
|
||
|
||
- After Stage 2, we could have the possibility to freely adapt Account Managers dedicated reporting. This could include, for instance, allowing for in-current-month data to speed up freshness. Alternatively, or rather, in addition; we could start thinking on including the projections at account level to develop alerting systems. Should we decide to keep things as they are, the solution would still work.
|
||
- After Stage 4, we could have the possibility to freely adapt Main KPIs - By Deal. Here we could decide to remove these models all together, and see if adding Deal as a dimension could work for in-depth understanding. Should we decide to keep them, the solution would still work. |