12 KiB
12 KiB
Services and Revenue modelling
Modelling strategy within DWH
We want to have 3 main tables within DWH: booking_service_line, booking_service_detail and booking_summary
- Booking Service Line should contain the information of “this booking has these services with this prices history”. It’s an append-only table that logs all changes of prices that apply to a booking service depending on when these services are supposed to be charged, are actual charged, if there’s refunds, booking cancellation, etc. Summing all line prices over a Booking Service Detail should provide the current price of the service for the booking.
- Booking Service Detail should contain the information of “this booking with these booking attributes has these services with these service attributes and these are the unit and total prices as well as the moment in time it’s charged that apply for this service in the current state”.
- Booking Summary it’s an aggregate of the previous Booking Service Detail and should contain the information of “this booking with these booking attributes has this total amount supposed to be charged, the current amount charged as of today, and the remainder of what’s left to be charged.”
Note that at this moment we’re not modelling protections but ideally we should be able to fit them here.
Booking Service Line
Primary key: id_booking_service_line
Additional unique test: id_booking_service_detail + service_line_created_at_utc
All string fields are capitalised in DWH.
| Field Name (DWH) | Field Name (backend) | Properties | Type | Possible Values | Description | Feasible? |
|---|---|---|---|---|---|---|
| id_booking_service_line | ? | PK, not null | bigint | 1,2,3… | Unique identifier of the booking service line | ? |
| id_booking_service_detail | BookingViewToService.Id | FK, not null | bigint | 1,2,3… | Unique identifier of the booking service detail | Yes |
| id_booking | Booking.Id | FK, not null | bigint | 1,2,3,… | Unique identifier of the booking | Yes |
| service_line_created_at_utc | ? | not null | timestamp | 2024-11-15 02:22:09.130 | Timestamp of when the booking_service_line record was first created in the backend | Yes |
| service_business_scope | - | not null | string | PLATFORM (for the time being) | Identifies the main business scope (platform, guest products, apis) according to New Pricing documentation | Yes, handled on Data side |
| service_business_type | - | not null | string | SCREENING, DEPOSIT_MANAGEMENT, PROTECTION, UNKNOWN | Identifies the service type according to New Pricing documentation | Partially, can be handled on Data side, ideally tagged in the backend in ProductService for Screening vs. Deposit Management |
| We need backfill of Ids in BookingViewToService to remove UNKNOWN cases | ||||||
| service_source | - | not null | string | PRODUCT, PROTECTION, UNKNOWN (for platform) | Identifies the source of the information used (Product or Protection based on how backend is modelised) | Partially |
| We need backfill of Ids in BookingViewToService to remove UNKNOWN cases | ||||||
| service_name | ? | not null | string | BASIC SCREENING, WAIVER PRO, BASIC PROTECTION, etc | Identifies the service name applied to a booking | Partially |
| We need backfill of Ids in BookingViewToService to remove usage of the ServiceName to fall into a more robust logic | ||||||
| currency_code | ? | not null | string (char(3)) | GBP, USD, EUR, etc. | Identifies the currency in which the price is charged. Can be null. | Yes |
| service_line_price_local_curr | ? | not null | decimal | 35 | Identifies the total line price of that service in a given booking in a given currency. Can vary over time depending on the service status, payments, etc. | No, need single source of truth in the backend |
| service_line_price_in_gbp | ? | not null | decimal | 21.675 | Identifies the total price of that service in a given booking converted in GBP. Can be null. Can vary over time depending on the service status, payments, etc, as well as it can vary over time until the charge date due to the currency rate estimation in the future. | No, need single source of truth in the backend |
| service_line_charge_date_utc | ? | not null | date | 2025-02-01, 2024-12-01, etc | Identifies the moment in time in which the service is charged. | No, need single source of truth in the backend |
| service_line_sign | ? | not null | integer | -1, 1 | Identifies if the price appearing in service_line_price fields need to be added or subtracted. | |
| service_line_comment | ? | string | A comment addressing the line. Can be null. | |||
| is_adding | ? | not null | boolean | Flag to identify if the line adds a charge or not. | ||
| is_reverting | ? | not null | boolean | Flag to identify if the line reverts a previous charge or not. |
Booking Service Detail
Primary key: id_booking_service_detail
Additional unique test: id_booking + service_name
All string fields are capitalised in DWH.
Booking Summary
| Field Name (DWH) | Field Name (backend) | Properties | Type | Possible Values | Description | Feasible? |
|---|---|---|---|---|---|---|
| id_booking_service_detail | BookingViewToService.Id | PK, not null | bigint | 1,2,3… | Unique identifier of the booking service detail | Yes |
| id_booking | Booking.Id | FK, not null | bigint | 1,2,3,… | Unique identifier of the booking | Yes |
| service_detail_created_at_utc | BookingViewToService.CreatedDate | not null | timestamp | 2024-11-15 02:22:09.130 | Timestamp of when the booking_service_detail record was first created in the backend | Yes |
| service_detail_updated_at_utc | BookingViewToService.UpdatedDate | not null | timestamp | 2024-11-15 02:22:09.130 | Timestamp of when the booking_service_detail record was first created in the backend | Partially, at the moment using BookingViewToService information but should be updated with the latest invoicing line update |
| booking_created_at_utc | Booking.CreatedDate | not null | timestamp | 2024-11-15 02:21:18.717 | Timestamp of when the corresponding booking record was first created in the backend | Yes |
| booking_updated_at_utc | Booking.UpdatedDate | not null | timestamp | 2024-11-15 02:22:09.073 | Timestamp of when the corresponding booking record was last updated in the backend | Yes |
| booking_check_in_at_utc | Booking.CheckIn | not null | timestamp | 2024-12-27 00:00:00.000 | Timestamp of the check in of the booking | Yes |
| booking_check_out_at_utc | Booking.CheckOut | not null | timestamp | 2025-01-03 00:00:00.000 | Timestamp of the check out of the booking | Yes |
| service_business_scope | - | not null | string | PLATFORM (for the time being) | Identifies the main business scope (platform, guest products, apis) according to New Pricing documentation | Yes, handled on Data side |
| service_business_type | - | not null | string | SCREENING, DEPOSIT_MANAGEMENT, PROTECTION, UNKNOWN | Identifies the service type according to New Pricing documentation | Partially, can be handled on Data side, ideally tagged in the backend in ProductService for Screening vs. Deposit Management |
| We need backfill of Ids in BookingViewToService to remove UNKNOWN cases | ||||||
| service_source | - | not null | string | PRODUCT, PROTECTION, UNKNOWN (for platform) | Identifies the source of the information used (Product or Protection based on how backend is modelised) | Partially |
| We need backfill of Ids in BookingViewToService to remove UNKNOWN cases | ||||||
| service_status | BookingViewToService.Status | not null | string | NOFLAGS, FLAGGED, -, NOCHECKS, REJECTED, etc | Identifies the status of the service applied to a booking | Yes |
| booking_status | BookingState.Name | not null | string | CANCELLED, APPROVED, NOTAPPROVED, INCOMPLETEINFORMATION, etc | Identifies the status of the booking | Yes |
| service_name | ProductService.FullName | ProtectionPlan.FullName | BookingViewToService.ServiceName | not null | string | BASIC SCREENING, WAIVER PRO, BASIC PROTECTION, etc |
| We need backfill of Ids in BookingViewToService to remove usage of the ServiceName to fall into a more robust logic | ||||||
| payment_type | PaymentType.FullName | string | AMOUNT, PERCENTAGE, UNKNOWN, [NULL] | Identifies if the service price unit is an actual amount or a percentage of another value. It can be null if the host currency is not populated. | Partially | |
| We need backfill of Ids in BookingViewToService to remove usage of the ServiceName to fall into a more robust logic | ||||||
| price_base_unit | BillingMethod.FullName | string | PER BOOKING, PER NIGHT, UNKNOWN, [NULL] | Identifies if the service price unit needs to be applied per booking or per number of nights between check-in and check-out. It can be null if the host currency is not populated. | Partially | |
| We need backfill of Ids in BookingViewToService to remove usage of the ServiceName to fall into a more robust logic | ||||||
| invoicing_trigger | InvoicingMethod.FullName | string | PRE-BOOKING, AT DEPOSIT PAYMENT, AT WAIVER PAYMENT, POST-CHECKOUT, UNKNOWN, [NULL] | Identifies the moment in time in which this service needs to be charged. It can be null if the host currency is not populated. | Partially | |
| We need backfill of Ids in BookingViewToService to remove usage of the ServiceName to fall into a more robust logic | ||||||
| currency_code | Currency.IsoCode | string (char(3)) | GBP, USD, EUR, etc. | Identifies the Host currency. Can be null. | Yes | |
| service_unit_price_local_curr | ProductServiceToPrice.Amount | ProtectionPlanToPrice.Amount | decimal | 5 | Identifies the service unit price in the Host currency. Can be null. | |
| service_unit_price_in_gbp | - | decimal | 3.235 | Identifies the service unit price converted to GBP with the rate of the date of charge. Can be null. Can vary over time until the charge date due to the currency rate estimation in the future. | No, need single source of truth in the backend (specifically for charge date). Will depend on Booking Service Line | |
| service_total_price_local_curr | - | decimal | 35 | Identifies the current total price of that service in a given booking in the Host currency. Can be null. Can vary over time depending on the service status, payments, etc. | No, need single source of truth in the backend. Will depend on Booking Service Line. | |
| service_total_price_in_gbp | - | decimal | 21.675 | Identifies the current total price of that service in a given booking converted in GBP. Can be null. Can vary over time depending on the service status, payments, etc, as well as it can vary over time until the charge date due to the currency rate estimation in the future. | No, need single source of truth in the backend. Will depend on Booking Service Line. | |
| service_charge_date_utc | - | date | 2025-02-01, 2024-12-01, etc | Identifies the moment in time in which the service is charged. | No, need single source of truth in the backend. Will depend on Booking Service Line. | |
| is_missing_currency_code | - | not null | boolean | True/False | Flag to identify if the applied service has no currency informed. | Yes |
| is_booking_cancelled | - | not null | boolean | True/False | Flag to identify it the Booking is cancelled or not. | Yes |
| is_paid_service | - | not null | boolean | True/False | Flag to identify it the service total price is strictly greater than 0 or not. | Yes |
| is_upgraded_service | - | not null | boolean | True/False | Flag to identify if the service is an upgraded version, meaning, it’s not a Basic Screening. | Yes |
To be filled, will retrieve further attributes from Booking (id_user_host, id_user_guest, etc) as well as aggregate information of the Booking Service Detail (prices, etc).