0ffcfca6a8
# Description I opted to name the combination of (Platform) Billable Bookings and (API) Billable Verifications as Billable Items. This is to ensure consistent naming. Changes: * Renamed `int_kpis_projected__agg_daily_billable_bookings` to `int_kpis_projected__agg_daily_billable_items`. This now has a new CTE named combination_of_billable_items that combines both API and Platform billable items. Renamed any "bookings" field to "items". * Renamed `int_kpis_projected__agg_monthly_billable_bookings` to `int_kpis_projected__agg_monthly_billable_items`. Renamed any "bookings" field to "items". * Renamed `int_billable_bookings_growth_score_by_deal` to `int_billable_items_growth_score_by_deal`. This now has a new CTE named `aggregated_monthly_billable_items` that combines the historical information both API and Platform on billable items. Renamed any "bookings" field to "items". * Changes Schema accordingly. Small note here that the assert dimension completeness stops working since there's dimensions specific to API or Platform, thus I removed it. # 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: #29374
436 lines
16 KiB
YAML
436 lines
16 KiB
YAML
version: 2
|
|
|
|
models:
|
|
- name: int_kpis_projected__dimension_dates
|
|
description: |
|
|
This model provides the daily time dimensionality needed
|
|
for the projection of KPIs.
|
|
It considers:
|
|
- Dates from the current month, up to the end of it,
|
|
indistinctly if it's in the future or not.
|
|
- Dates from the 3 past complete months.
|
|
This model is intended to be used as a dimension table for
|
|
the projection of KPIs, thus it's expected to be joined with
|
|
the fact table containing the KPIs to be projected.
|
|
|
|
columns:
|
|
- name: date
|
|
data_type: date
|
|
description: Specific date. It's the primary key of this model.
|
|
data_tests:
|
|
- unique
|
|
- not_null
|
|
|
|
- name: first_day_month
|
|
data_type: date
|
|
description: |
|
|
First day of the month corresponding to the date field.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: last_day_month
|
|
data_type: date
|
|
description: |
|
|
Last day of the month corresponding to the date field.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: previous_6_days
|
|
data_type: date
|
|
description: Date of the previous 6 days with respect to the date field.
|
|
data_tests:
|
|
- unique
|
|
- not_null
|
|
|
|
- name: is_end_of_month
|
|
data_type: boolean
|
|
description: True if it's end of month, false otherwise.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_current_month
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is within the current month, false otherwise.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_in_the_future
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is in the future, false otherwise. Note that is in the
|
|
future will also include the current day, as we can only consider full-closed
|
|
data up to yesterday.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_latest_historical_date
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is the latest historical date, false otherwise.
|
|
The latest historical date is the latest date from which we have full information.
|
|
This should be yesterday.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_available_for_same_month_projection
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is available for the projection of KPIs for the same month,
|
|
false otherwise. This will be true for all days contained within the first day
|
|
of the month up to yesterday. This will apply for both the current month and
|
|
the 3 months.
|
|
If today is 10th of May, and data is available up to 9th of May, then all days
|
|
from 1st to 9th of May, April, March and February will be available for projection.
|
|
If today is 1st of May, then no dates will be available for projection.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_available_for_last_7_days_projection
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is available for the projection of KPIs for the past 7 days,
|
|
false otherwise. This will be true only for yesterday and the equivalent day for
|
|
the previous 3 months.
|
|
If today is 10th of May, and data is available up to 9th of May, then all days
|
|
only 9th of May, 9th of April, 9th of March and 9th of February will be available
|
|
for projection.
|
|
If today is 1st of May, then 30th of April, 31st of March, 28th of February and
|
|
31st of January will be available for projection.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: int_kpis_projected__agg_daily_billable_items
|
|
description: |
|
|
This model provides the projected daily billable items, in
|
|
other words, platform billable bookings and API billable
|
|
verifications.
|
|
It considers 2 computations:
|
|
- The daily billable items for the current month,
|
|
- The daily billable items in the past 7 days,
|
|
and the final value is an arithmetic mean of both.
|
|
|
|
This model also retrieves the actual billable items to be able
|
|
to compare the projected values with the actual ones.
|
|
|
|
data_tests:
|
|
- dbt_utils.unique_combination_of_columns:
|
|
combination_of_columns:
|
|
- date
|
|
- dimension
|
|
- dimension_value
|
|
|
|
columns:
|
|
- name: date
|
|
data_type: date
|
|
description: |
|
|
The start and end date of the time range considered for the
|
|
metrics in this record.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: dimension
|
|
data_type: string
|
|
description: The dimension or granularity of the metrics.
|
|
data_tests:
|
|
- accepted_values:
|
|
values:
|
|
- global
|
|
- by_number_of_listings
|
|
- by_billing_country
|
|
- by_business_scope
|
|
- by_service
|
|
- by_deal
|
|
|
|
- name: dimension_value
|
|
data_type: string
|
|
description: The value or segment available for the selected dimension.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: first_day_month
|
|
data_type: date
|
|
description: |
|
|
First day of the month corresponding to the date field.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: last_day_month
|
|
data_type: date
|
|
description: |
|
|
Last day of the month corresponding to the date field.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_end_of_month
|
|
data_type: boolean
|
|
description: True if it's end of month, false otherwise.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_current_month
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is within the current month, false otherwise.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_in_the_future
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is in the future, false otherwise. Note that is in the
|
|
future will also include the current day, as we can only consider full-closed
|
|
data up to yesterday.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: is_latest_historical_date
|
|
data_type: boolean
|
|
description: |
|
|
True if the date is the latest historical date, false otherwise.
|
|
The latest historical date is the latest date from which we have full information.
|
|
This should be yesterday.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: daily_billable_items_for_reporting_source
|
|
data_type: string
|
|
description: |
|
|
The source of the daily billable items for reporting.
|
|
This field is used to identify the source of the data displayed
|
|
in daily_billable_items_for_reporting to differentiate between
|
|
the actual and projected values.
|
|
It's aimed for reforting purposes as any historical month will
|
|
contain the actual figures.
|
|
data_tests:
|
|
- not_null
|
|
- accepted_values:
|
|
values:
|
|
- ACTUAL
|
|
- PROJECTED
|
|
|
|
- name: daily_billable_items_for_reporting
|
|
data_type: integer
|
|
description: |
|
|
The daily billable items for reporting purposes.
|
|
This field contains both the actual and projected values.
|
|
Any date in the future will contain projected values, while
|
|
any date in the past will contain actual values.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: daily_billable_items_for_evaluation_source
|
|
data_type: string
|
|
description: |
|
|
Important: This field is used to evaluate the performance
|
|
of the projections!
|
|
|
|
The source of the daily billable items for evaluation.
|
|
This field is used to identify the source of the data displayed
|
|
in daily_billable_items_for_evaluation to differentiate between
|
|
the actual and projected values.
|
|
It's aimed for evaluation purposes as any historical month can
|
|
contain projected figures.
|
|
|
|
data_tests:
|
|
- not_null
|
|
- accepted_values:
|
|
values:
|
|
- ACTUAL
|
|
- PROJECTED
|
|
|
|
- name: daily_billable_items_for_evaluation
|
|
data_type: integer
|
|
description: |
|
|
Important: This field is used to evaluate the performance
|
|
of the projections!
|
|
|
|
The daily billable items for evaluation purposes.
|
|
This field contains both the actual and projected values.
|
|
Any date in the future will contain projected values. Any date
|
|
in the past which day is after the yesterday day will also contain
|
|
projected values. Any date in the past which day is before the
|
|
yesterday day will contain actual values.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: projected_daily_billable_items
|
|
data_type: integer
|
|
description: |
|
|
The projected daily billable items. This field is the result
|
|
of the projection of the daily billable items for the current month
|
|
and the past 7 days.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: actual_daily_billable_items
|
|
description: |
|
|
The actual billable items for the same period as the projected ones.
|
|
This comes from the standard KPIs.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: same_month_trend_daily_billable_items
|
|
data_type: float
|
|
description: |
|
|
The average daily billable items for the current month.
|
|
This field is the result of the division of the actual daily billable
|
|
items to date by the number of days available within the current month
|
|
to date, and contains decimals.
|
|
This is just for information purposes.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: last_7_days_trend_daily_billable_items
|
|
data_type: float
|
|
description: |
|
|
The average daily billable items for the past 7 days.
|
|
This field is the result of the division of the actual daily billable
|
|
items for the past 7 days by 7 days, and contains decimals.
|
|
This is just for information purposes.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: same_month_trend_total_billable_items
|
|
data_type: integer
|
|
description: |
|
|
The total billable items for the current month.
|
|
This field is the result of the sum of the actual daily billable
|
|
items to date.
|
|
This is just for information purposes.
|
|
|
|
- name: last_7_days_trend_total_billable_items
|
|
data_type: integer
|
|
description: |
|
|
The total billable items for the past 7 days.
|
|
This field is the result of the sum of the actual daily billable
|
|
items for the past 7 days.
|
|
This is just for information purposes.
|
|
|
|
- name: same_month_trend_total_available_days
|
|
data_type: integer
|
|
description: |
|
|
The total available days for the current month.
|
|
This field is the result of the count of the days available within
|
|
the current month to date.
|
|
This is just for information purposes.
|
|
|
|
- name: last_7_days_trend_total_available_days
|
|
data_type: integer
|
|
description: |
|
|
The total available days for the past 7 days.
|
|
This field is the result of the count of the days available for the
|
|
past 7 days.
|
|
This is just for information purposes.
|
|
|
|
- name: int_kpis_projected__agg_monthly_billable_items
|
|
description: |
|
|
This model provides the projected monthly billable items per dimension
|
|
and dimension value.
|
|
Billable items are defined as platform billable bookings and API
|
|
billable verifications. It only considers the current month.
|
|
Historical data is considered only to assess the performance of the
|
|
projections.
|
|
The projection logic is handled on the equivalent daily model,
|
|
please refer to it for more information.
|
|
|
|
data_tests:
|
|
- dbt_utils.unique_combination_of_columns:
|
|
combination_of_columns:
|
|
- dimension
|
|
- dimension_value
|
|
|
|
columns:
|
|
- name: start_date
|
|
data_type: date
|
|
description: |
|
|
The start date of the time range considered for the metrics in this record.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: end_date
|
|
data_type: date
|
|
description: |
|
|
The end date of the time range considered for the metrics in this record.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: dimension
|
|
data_type: string
|
|
description: The dimension or granularity of the metrics.
|
|
data_tests:
|
|
- accepted_values:
|
|
values:
|
|
- global
|
|
- by_number_of_listings
|
|
- by_billing_country
|
|
- by_business_scope
|
|
- by_service
|
|
- by_deal
|
|
|
|
- name: dimension_value
|
|
data_type: string
|
|
description: The value or segment available for the selected dimension.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: current_month_projected_billable_items
|
|
data_type: integer
|
|
description: |
|
|
The projected monthly billable items for the current month.
|
|
This field is the result of the sum of the actual daily billable items
|
|
for the current month to date and the projected daily billable items
|
|
for the rest of the days in the month that are in the future.
|
|
|
|
The closest we are to the end of the month, the more accurate this value will be.
|
|
|
|
In order to check how good or bad the projection is, please refer to the
|
|
historical_monthly_mean_absolute_error and
|
|
historical_monthly_mean_absolute_percentage_error fields.
|
|
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: actual_billable_items
|
|
data_type: integer
|
|
description: |
|
|
The sum of the actual daily billable items for the current month to date.
|
|
This comes from the standard KPIs.
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: historical_monthly_mean_absolute_error
|
|
data_type: float
|
|
description: |
|
|
The mean absolute error for this dimension and dimension value.
|
|
|
|
This field is used to assess the performance of the projections.
|
|
|
|
This is based on the absolute differences between the projected
|
|
monthly billable items for each previous month vs the actual value.
|
|
In order to be consistent, it uses the same number of days available
|
|
for the current month to date as the actual value, and the rest of the days
|
|
are projected.
|
|
|
|
This computation is applied for the past 3 months, and the value displayed
|
|
here is an average of the absolute differences.
|
|
|
|
The closest to 0, the better the projection.
|
|
|
|
- name: historical_monthly_mean_absolute_percentage_error
|
|
data_type: float
|
|
description: |
|
|
The mean absolute percentage error for this dimension and dimension value.
|
|
|
|
This field is used to assess the performance of the projections.
|
|
|
|
This is based on the absolute percentage differences between the projected
|
|
monthly billable items for each previous month vs the actual value.
|
|
In order to be consistent, it uses the same number of days available
|
|
for the current month to date as the actual value, and the rest of the days
|
|
are projected.
|
|
|
|
This computation is applied for the past 3 months, and the value displayed
|
|
here is an average of the absolute percentage differences.
|
|
|
|
The closest to 0, the better the projection.
|