sh-notion/notion_data_team_no_files/20250409-01 - Wrong computation on Revenue Retaine 1d10446ff9c980e0b6d3e52b40879b68.md

# 20250409-01 - Wrong computation on Revenue Retained metrics

Managed by Uri 

## Summary

- From April 2 to 10, 2025, a data model error caused inaccurate Total and Retained Revenue metrics in key Power BI reports, with deviations up to ±3%.
- The issue stemmed from improper handling of metric dimensions during a KPI refactor.
- It was fixed on April 10 with added test coverage to prevent recurrence.

## Impact

From 2nd of April to 10th of April 2025, the computation of Total and Retained Revenue metrics have had Data Quality issues

An estimated impact over all historical data shows that:

- Total Revenue was showing +0.7% more than the actual.
- Revenue Retained was showing -2.5% less than the actual.
- Revenue Retained Post Resolutions was showing -3.0% less than the actual.

This issue was reflected in the following Power BI Apps:

Business Overview

- **Report**: Main KPIs
- **Tabs**: All
- **Impacted metrics**: Total Revenue, Revenue Retained, Revenue Retained Post Resolutions and equivalent rates.

Account Management

- **Report**: Churn Report & Account Margin
- **Tabs**: All
- **Impacted metrics**: Total Revenue, Revenue Retained, Revenue Retained Post Resolutions and equivalent rates. Limited to 34 affected Deals.

The rest of Power BI Reports have been working normally.

Worth mentioning that the extractions for Old Dash to New Dash migration project has *not* been affected.

## Timeline

| Time (UTC) | Event |
| --- | --- |
| 2025-04-01 13:40 | A new daily model for Total and Retained Revenue is created, with the name `int_kpis__metric_daily_total_and_retained_revenue`. This contains an error in the computation. However, while this is actually deployed in production, it is not affecting any existing reporting. Related PR [4874](https://guardhog.visualstudio.com/Data/_git/data-dwh-dbt-project/pullrequest/4874). |
| 2025-04-02 14:00 | The switch of new Revenue models from KPIs happens in production. This introduces the faulty computation in production. Related PR [4887](https://guardhog.visualstudio.com/Data/_git/data-dwh-dbt-project/pullrequest/4887). |
| 2025-04-09 13:43 | Kayla raises to Uri the fact that Revenue Retained and Revenue Retained Post-Resolutions is wrongly computed for a specific account. Indeed, Total Revenue - Host TakeHome is not equal to Revenue Retained in Account Margin report. |
| 2025-04-09 15:39 | Error is found in the model `int_kpis__metric_daily_total_and_retained_revenue`. The fact that guest payments have more dimensions than the rest of the models is generating duplicates in a very few cases, which is hidden by the aggregation of the metric. A total of 34 deals are affected, which are shared with Kayla. |
| 2025-04-10 06:52 | A Pull Request is created to fix the issue in the source, `int_kpis__metric_daily_total_and_retained_revenue`. Additionally it creates new data tests to ensure the issue does not happen in AM Account Margin report. Related PR [4970](https://guardhog.visualstudio.com/Data/_git/data-dwh-dbt-project/pullrequest/4970). |
| 2025-04-10 07:23 | PR is merged and a re-run of DWH is triggered. |
| 2025-04-10 07:40 | Incident is mitigated after the successful run. |
| 2025-04-10 13:06 | Additional test coverage implemented for Total Revenue and Revenue Retained Post-Resolutions in critical, report-facing models. |
|  |  |

## Root Cause(s)

In the scope of KPIs refactoring, a faulty computation was introduced in the new model of `int_kpis__metric_daily_total_and_retained_revenue`. In short, the combination of dimensions was not being handled properly: Guest Revenue had additional dimensions with respect to Invoiced Revenue and Host Resolutions. This was causing very localised duplicated instances of Invoiced Revenue and Host Resolutions. Since the final metrics were aggregated by summing, this duplicates were hidden within an internal CTE of the model and no duplicity alert was ever raised. 

This issue went unnoticed during the development despite using audit tools for refactoring purposes. Likely, the root cause is a human mistake on checking such audits, as posterior checks show that the issue was there at the time this new model was put in production.

## Resolution and recovery

Resolution has focused mostly on conducting a first aggregation of Guest Revenue data for the necessary dimensions before joining with Invoiced Revenue and Host Resolutions metrics.

Additionally, the same aggregation has been handled for Invoiced Revenue and Host Resolutions, despite is not really needed. This is just to ensure that in the event of further dimensions being created for these models, the outcome of the Total and Retained Revenue would still prevail.

Testing coverage has been increased in downstream models, report-facing, to ensure that such an issue is properly flagged.

After 2025-04-10 07:40 UTC, all affected instances on Power BI have been recovered normally.

## **Lessons Learned**

- What went well
    - Kayla effectively raising the issue on checking the Account Margin report
    - Our development flow and version control allowed us to quickly understand when the issue started, what was the root cause, reproduce it and remediate it
- What went badly
    - Issue went unnoticed for a week, without any alerting in place.
    - Development deficiencies on the refactor that was unable to detect this issue.
- Where we were lucky
    - 

## Action Items

- [ ]  Retrospect all together how can we perform refactors in the `dbt` project with more confidence and less mistakes

## Appendix