# Description
Adds new metrics on Revenue for New Dash services:
invoiced_revenue.xero_basic_protection_net_fees_in_gbp,
invoiced_revenue.xero_waiver_pro_net_fees_in_gbp,
invoiced_revenue.xero_id_verification_net_fees_in_gbp,
invoiced_revenue.xero_protection_plus_net_fees_in_gbp,
invoiced_revenue.xero_screening_plus_net_fees_in_gbp,
invoiced_revenue.xero_sex_offenders_check_net_fees_in_gbp,
invoiced_revenue.xero_protection_pro_net_fees_in_gbp
Note that it does not add Basic Screening. This is because so far this is a free service, even though the kpis models already consider such a possibility.
First data appears for December so this will be available starting 1st February
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
New Dash services Invoiced Revenue
Related work items: #26680
# Description
Exposes 2 New KPIs on Revenue Retained. Work was already done, I just removed the temporary filter and adapted the name of the metric.
# Checklist
- [ ] The edited models and dependants run properly with production data.
- [ ] The edited models are sufficiently documented.
- [ ] The edited models contain PK tests, and I've ran and passed them.
- [ ] I have checked for DRY opportunities with other models and docs.
- [ ] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Expose new KPIs
Related work items: #25804
# Description
Quick improvement to be able to report the same setup of metrics by considering all account history up to a certain date.
It adds a new computation flow for this All History window. Note that I needed to update the macro to override this case by using unbounded preceding.
I also took the opportunity to update the exposures of the new report.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #26085
# Description
Mostly 2 changes:
* Fix metrics with coalesces so values are displayed
* Removes ratios, no longer needed. These are computed in PBI directly, to be able to compute the total figure correctly
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #25829
# Description
* Creates new model monthly_aggregated_metrics_history_by_deal_by_time_window
* Modifies existing intermediate entry on schema to properly fill all fields
* Creates same entry in reporting.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #25829
# Description
This model aggregates at monthly by deal level different metrics for AM reporting purposes. It also includes revenue retained ratios for client profitability assessment.
There's part of the existing AM report that could be simplified, likely, by using the new macro. This will be explored in a separated PR, if it applies.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [ ] I have checked for DRY opportunities with other models and docs. ** Checked and there might be possibilities to simplify the code. I'll check ones I finish this line of work**
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #25829
# Description
Context: I'm intending to work on Account Managers reporting, that mostly will include reporting at Deal level the Resolutions Payouts as well as the new Retained metrics. While checking the great increase on Resolutions Payouts for October 2024:
I decided to take a quick look into the main players... and surprise surprise we have Guesty:
So Guesty represents 37k over the 73K of October. 50%. Not bad.
The main issue is that we've been aware for months now (since Churn efforts, mostly) that we're not reporting in KPIs those deals that are NOT created in Core. Most notably, API deals which includes... well, Guesty. So creating this kind of in-depth Account Managers improvement without reporting Guesty I think it would be very misleading. Note that the overall figures (Global dimension) are still accurate, though.
What's new:
* A new model named `int_kpis__dimension_deals` that basically retrieves Deals from both Core (as before) and Hubspot. It combines information from both and mostly assumes Hubspot as a better source of information than Core - although we do not have the Main Billing Country there afaik.
* Propagates changes, mostly in the monthly by deal view of Main KPIs. Here I confirm that now Guesty appears, and it only has metrics that come from Xero (APIs Revenue, Total Revenue, Resolutions, etc)
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #25829
# Description
Adds `is_end_of_month_or_yesterday` for Main KPIs.
Apparently, the fact that we do not show the ongoing value of the month on some tabs of Main KPIs is the main blocker for Ben C to consistently use Main KPIs, which he actually retrieves from the SH legacy reporting. Since I'm sceptical about the data shown there, I want to remove this blocker.
It will require a small PR on PBI as well.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [NA] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #25342
# Description
New model:
* int_kpis__agg_dates_main_kpis - Serves as the skeleton of dates and dimensions for Main KPIs. It's aggregated since it follows a similar aggregation strategy. It's a single model to feed both Main KPIs visualisations. Note boolean fields are real booleans (true/false) while before these were integers (1/0). This also affects downstream models.
Main KPIs flow adaptations to new skeleton model:
* int_monthly_aggregated_metrics_history_by_deal
* int_monthly_churn_metrics - additionally, calls usual KPIs macro instead of old one
* int_mtd_vs_previous_year_metrics
Reporting changes to ensure report is not down:
* mtd_aggregated_metrics - adaptations on booleans (true-1, false-0)
Cleaning:
* get_kpi_dimensions macro is no longer used
* int_dates_by_deal model and schema entry
* int_dates_mtd_by_dimension model and schema entry
* int_dates_mtd model and schema entry
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23763
# Description
Eliminates models that have been switched with new kpis flow.
Also deletes temporary tests and schema entries.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [NA] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [NA] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23762
# Description
Switches Listings and Deal metrics to production.
I also modified the dependencies on the old lifecycles to read from the news, so we can remove these in a following PR.
I'm considering re-computing also the dimension - specific models to operate within KPIs folder, but will do this later on in a dedicated PR.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [ ] I have checked for DRY opportunities with other models and docs.
- [ ] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23761
# Description
Changes:
* Creates lifecycle_daily_deal, metric_daily_deals and agg_daily_deals. These follow a different strategy due to the nature of the metrics
* Modifies the dimension macro to ensure deal dimension is included in all models except these ones
* Fixes production issue on currently deployed deal lifecycle and metrics
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [X] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23566
# Description
Switches Xero metrics to the new KPIs flow. This uses the 2 new entities around Invoiced Revenue and Host Resolutions.
The metrics affected will change computation source in PBI as well (for MTD per category and Monthly by deal views). This directly affects the following metrics:
* Invoiced Operator Revenue
* Invoiced Booking Fees Revenue
* Invoiced Listing Fees Revenue
* Invoiced Verification Fees Revenue
* Invoiced APIs Revenue
* Invoiced Athena Revenue
* Invoiced E-Deposit Revenue
* Damage Host-Waiver Payments
* Host Resolutions Amount Paid
* Host Resolutions Payment Count
Additionally, the following metrics will be indirectly affected since depend partially on Xero:
* Total Revenue
* 4x Total Revenue per (Booking Created, Guest Journey Created, Deals Booked in Month, Listings Booked in Month)
* Waiver Retained
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [NA] I have checked for DRY opportunities with other models and docs. **Same as last time - the way models are called could be improved, but let's switch everything first and then discuss. There's chances these cross models will die anyway**
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23759
# Description
Same old story: includes all metrics coming from Xero.
Adds a daily model, monthly, mtd + monthly agg and mtd agg
A test to compare values new vs. old
AND fixes the issue I mentioned with the timestamp - an issue in the old KPIs.
# Checklist
- [X] The edited models and dependants run properly with production data.
- [X] The edited models are sufficiently documented.
- [X] The edited models contain PK tests, and I've ran and passed them.
- [NA] I have checked for DRY opportunities with other models and docs.
- [X] I've picked the right materialization for the affected models.
# Other
- [ ] Check if a full-refresh is required after this PR is merged.
Related work items: #23565
