bc3a364891
# Description * Adds the new snapshot for Guesty Claims, up to 1st July 2025. * Creates a model named int_athena__high_risk_client_detector that handles the following logic: 1. The User has been using the agreed services for at least (3) months 2. The aggregated number of claims filed by the User exceeds a total of £2300 3. The User has filed at least (5) claims 4. The User has a claim ration of (7%) or higher throughout their entire use of agreed services, including any claim that has received a guarantee payment It's heavily opinionated due to lack of clear requirements and lack of data quality, both in athena verifications and guesty claims. Please, check the inline comments for more info. With these model and conditions, only 2 users would be tagged as high risk. # 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: #31687
286 lines
9 KiB
YAML
286 lines
9 KiB
YAML
version: 2
|
|
|
|
models:
|
|
- name: int_athena__verifications
|
|
description: "This table holds records on verifications for Athena bookings.
|
|
It contains details on validations checked on the guests, guest information
|
|
and some booking details like checkin-checkout date or the status of the verification.
|
|
The id values found here are completely unrelated to the ones found in Core DWH.
|
|
|
|
Note that id_verifications and booking_id should normally be 1 to 1.
|
|
Though there are exception, the API will accept a duplicate booking and the users
|
|
will be charged for it. A duplicate would return a unique id_verification."
|
|
columns:
|
|
- name: id_verification
|
|
data_type: text
|
|
description: "unique Superhog generated id for this verification"
|
|
data_tests:
|
|
- unique
|
|
- not_null
|
|
|
|
- name: id_booking
|
|
data_type: text
|
|
description: "unique Superhog generated id for a booking.
|
|
note that this could be duplicated and both will be charged,
|
|
it's up to the user to no generate duplicate verifications"
|
|
|
|
- name: id_user_partner
|
|
data_type: text
|
|
description: "unique Superhog generated id for partner"
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: version
|
|
data_type: text
|
|
description: "value to identify if it is Guesty (V1) or E-deposit (V2)"
|
|
data_tests:
|
|
- accepted_values:
|
|
values:
|
|
- V1
|
|
|
|
- name: verification_source
|
|
data_type: text
|
|
description: "source of the verification for the booking"
|
|
data_tests:
|
|
- accepted_values:
|
|
values:
|
|
- Guesty
|
|
- Edeposit
|
|
|
|
- name: verification_status
|
|
data_type: text
|
|
description: "status of the verification"
|
|
|
|
- name: nightly_fee_local
|
|
data_type: double precision
|
|
description: "fee in local currency charged per night"
|
|
|
|
- name: number_nights
|
|
data_type: integer
|
|
description: "number of nights for the booking"
|
|
|
|
- name: email_flag
|
|
data_type: text
|
|
description: "screening result for email"
|
|
|
|
- name: phone_flag
|
|
data_type: text
|
|
description: "screening result for phone"
|
|
|
|
- name: watch_list
|
|
data_type: text
|
|
description: "screening result of the guest"
|
|
|
|
- name: channel
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: checkin_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp of checkin for the booking"
|
|
|
|
- name: checkin_date_utc
|
|
data_type: date
|
|
description: "Date of checkin for the booking"
|
|
|
|
- name: checkout_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp of checkout for the booking"
|
|
|
|
- name: checkout_date_utc
|
|
data_type: date
|
|
description: "Date of checkout for the booking"
|
|
|
|
- name: is_cancelled
|
|
data_type: boolean
|
|
description: ""
|
|
|
|
- name: cancelled_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp of cancellation of the booking"
|
|
|
|
- name: cancelled_date_utc
|
|
data_type: date
|
|
description: "Date of cancellation for the booking"
|
|
|
|
- name: user_email
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: guest_email
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: guest_last_name
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: guest_first_name
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: guest_telephone
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: property_manager_name
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: property_manager_email
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: listing_name
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: listing_address
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: listing_town
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: listing_country
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: listing_postcode
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: pets_allowed
|
|
data_type: boolean
|
|
description: ""
|
|
|
|
- name: level_of_protection_amount
|
|
data_type: integer
|
|
description: ""
|
|
|
|
- name: level_of_protection_currency
|
|
data_type: text
|
|
description: ""
|
|
|
|
- name: status_updated_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp when status was last updated"
|
|
|
|
- name: status_updated_date_utc
|
|
data_type: date
|
|
description: "Date of last status update of the verification"
|
|
|
|
- name: updated_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp of last updated of the verification"
|
|
|
|
- name: updated_date_utc
|
|
data_type: date
|
|
description: "Date of last update of the verification"
|
|
|
|
- name: athena_creation_at_utc
|
|
data_type: timestamp without time zone
|
|
description:
|
|
"Athena timestamp referring to when the booking was created.
|
|
It's provided by Guesty, but is not mandatory.
|
|
In case of doubt use created_at_utc or created_date_utc fields"
|
|
|
|
- name: athena_creation_date_utc
|
|
data_type: date
|
|
description: "Athena date referring to when the booking was created.
|
|
It's provided by Guesty, but is not mandatory.
|
|
In case of doubt use created_at_utc or created_date_utc fields"
|
|
|
|
- name: created_at_utc
|
|
data_type: timestamp without time zone
|
|
description: "Timestamp of creation of the verification in the system"
|
|
|
|
- name: created_date_utc
|
|
data_type: date
|
|
description: "Date of creation of the verification in the system"
|
|
|
|
- name: int_athena__verifications_with_fees
|
|
description: "This table shows all verification for Guesty.
|
|
The charged fee is 2GBP per booked night if booking is approved
|
|
(considered 1 night when the checkin and checkout are on the same day),
|
|
to be charged on checkout."
|
|
columns:
|
|
- name: id_verification
|
|
data_type: text
|
|
description: "unique Superhog generated id for this verification"
|
|
data_tests:
|
|
- unique
|
|
- not_null
|
|
|
|
- name: id_booking
|
|
data_type: text
|
|
description: "unique Superhog generated id for a booking.
|
|
note that there might be duplicate bookings on the original data
|
|
but we remove them keeping only the verification with the most recent update."
|
|
data_tests:
|
|
- not_null
|
|
- unique
|
|
|
|
- name: verification_status
|
|
data_type: text
|
|
description: "status of the verification"
|
|
|
|
- name: is_cancelled
|
|
data_type: boolean
|
|
description: "indicates if the booking has been cancelled or not."
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: ok_status_fee_in_gbp
|
|
data_type: integer
|
|
description: "total fee charged on checkout, this is only charged for approved verifications"
|
|
data_tests:
|
|
- not_null
|
|
- dbt_expectations.expect_column_values_to_be_between:
|
|
min_value: 0
|
|
strictly: true
|
|
where: is_cancelled = false and verification_status = 'Approved'
|
|
|
|
- name: created_date_utc
|
|
data_type: date
|
|
description: "Date of creation of the verification in the system"
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: checkin_date_utc
|
|
data_type: date
|
|
description: "Date of checkin for the booking"
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: checkout_date_utc
|
|
data_type: date
|
|
description: "Date of checkout for the booking"
|
|
data_tests:
|
|
- not_null
|
|
|
|
- name: int_athena__high_risk_client_detector
|
|
description: |
|
|
This model is used to detect high-risk clients based on their booking and claim history for
|
|
Guesty (Athena).
|
|
This is based on some business rules that might change in the future.
|
|
This is also based on a snapshot that might require updates in the future.
|
|
|
|
Current rules, based on the Data Request on July 1st 2025 by Chloe from Resolutions, are:
|
|
A User is considered a high-risk client if they fall into the below criteria:
|
|
1. The User has been using the agreed services for at least (3) months
|
|
2. The aggregated number of claims filed by the User exceeds a total of £2300
|
|
3. The User has filed at least (5) claims
|
|
4. The User has a claim ration of (7%) or higher throughout their entire use of agreed services, including any claim that has received a guarantee payment
|
|
columns:
|
|
- name: property_manager_email
|
|
data_type: character varying
|
|
description: |
|
|
Email of the property manager.
|
|
This is used to identify the property manager for the booking.
|
|
It is used to group bookings and claims by property manager.
|
|
It is unique and not null.
|
|
data_tests:
|
|
- not_null
|
|
- unique
|