models: - name: int_daily_currency_exchange_rates description: >- This model holds a lot of data on currency exchange rates. The time granularity is daily. Each record holds a currency pair for a specific day, source and version. Actual rates are sourced from xe.com data. The `guessed` and `forecast` versions are built by simply 'pushing' the first/last exchange rate on record. Basically, wherever we dont' have data for a date, we pick the closest actual data point that comes from xe.com. Bear in mind this means that `forecast` version records will change on a daily basis as actual data moves forwards, meaning you shouldn't assume your money amounts converted in the future should always stay put. Note that, given the dimensionality, getting a simple time series for a currency pair will require a bit of filtering. Reverse rates are explicit. This means that, for any given day and any given currency pair, you will find two records with opposite from/to positions. So, for 2024-01-01, you will find both a EUR->USD record and a USD->EUR record with the opposite rate (1/rate). columns: - name: id_exchange_rate data_type: text description: A unique ID for the record, derived from concatenating the currencies, date, source and version. Currency order is relevant (EURUSD != USDEUR). data_tests: - not_null - unique - name: from_currency data_type: character description: The source currency, represented as an ISO 4217 code. data_tests: - not_null - name: to_currency data_type: character description: The target currency, represented as an ISO 4217 code. data_tests: - not_null - name: rate data_type: numeric description: >- The exchange rate, represented as the units of the target currency that one unit of source currency gets you. So, from_currency=USD to to_currency=PLN with rate=4.2 should be read as '1 US Dollar buys me 4.2 Polish Zlotys'. For same currency pairs (EUR to EUR, USD to USD, etc). The rate will always be one. The rate can be smaller than one, but can't be negative. data_tests: - not_negative_or_zero - not_null - name: rate_date_utc data_type: date description: The date in which the rate record is relevant. data_tests: - not_null - name: source data_type: text description: Where is the data coming from. Records that are composed from making assumptions on real data will contain `_inferred`. - name: rate_version data_type: text description: The version of the rate. This can be one of `actual` (the rate is a reality fact), `forecast` (the rate sits in the future and is a guess in nature) or `guess` (the rate sits in the past and is a guess in nature). Note that one currency pair can have multiple rate versions on the same date. data_tests: - accepted_values: values: - guess - actual - forecast - not_null - name: updated_at_utc data_type: timestamp with time zone description: For external sources, this will be the point in time when the information was obtained from them. For stuff we make up here in the DWH, this will be the point in time when we made the assumption. data_tests: - not_null - name: int_simple_exchange_rates description: >- A simplified vision of exchange rates, derived from `int_daily_currency_exchange_rates`. Come here if you don't want to understand nuances and complexities and just want to convert rates. The time granularity is daily. Each record holds a currency pair for a specific day. You will only find one conversion rate per currency pair and date. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - from_currency - to_currency - rate_date_utc columns: - name: from_currency data_type: character description: The source currency, represented as an ISO 4217 code. data_tests: - not_null - name: to_currency data_type: character description: The source currency, represented as an ISO 4217 code. data_tests: - not_null - name: rate data_type: numeric description: The target currency, represented as an ISO 4217 code. data_tests: - not_null - name: rate_date_utc data_type: date description: The date in which the rate record is relevant. data_tests: - not_null - name: updated_at_utc data_type: timestamp with time zone description: For external sources, this will be the point in time when the information was obtained from them. For stuff we make up here in the DWH, this will be the point in time when we made the assumption. data_tests: - not_null - name: int_mtd_vs_previous_year_metrics description: | This model is used for global KPIs. It aggregates all the mtd models with the different metrics per source and computes any necessary weighted metric across different sources. Each metric has a date, dimension and dimension value that defines the primary key of this model. Finally, it displays any metric on the current date, the previous year date and it computes the relative increment by using the macro: - calculate_safe_relative_increment data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - dimension - dimension_value - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: revenue_retained_post_resolutions_in_gbp column_B: total_revenue_in_gbp + xero_waiver_paid_back_to_host_in_gbp + xero_host_resolution_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: total_revenue_in_gbp column_B: total_guest_payments_in_gbp + xero_apis_net_fees_in_gbp + xero_operator_net_fees_in_gbp columns: - name: date data_type: date description: The date for the month-to-date metrics. 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_deal - name: dimension_value data_type: string description: The value or segment available for the selected dimension. data_tests: - not_null - name: int_mtd_aggregated_metrics description: | The `int_mtd_aggregated_metrics` model aggregates multiple metrics on a year, month, and day basis. The primary source of data is the `int_mtd_vs_previous_year_metrics` model, which contain the combination of metrics data per source. This model just changes the display format to unpivot the information into a set of metric, value, previous_year_value and relative_increment at a given date. It uses Jinja code to avoid code replication. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - metric - dimension - dimension_value columns: - name: year data_type: int description: year number of the given date. data_tests: - not_null - name: month data_type: int description: month number of the given date. data_tests: - not_null - name: day data_type: int description: day monthly number of the given date. data_tests: - not_null - name: is_end_of_month data_type: boolean description: True if it's end of month. data_tests: - not_null - name: is_end_of_month_or_yesterday data_type: boolean description: True if it's end of month or yesterday. data_tests: - not_null - name: is_current_month data_type: boolean description: | checks if the date is within the current executed month, 1 for yes, 0 for no. 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: date data_type: date description: | main date for the computation, that is used for filters. 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_deal - name: dimension_value data_type: string description: The value or segment available for the selected dimension. data_tests: - not_null - name: previous_year_date data_type: date description: | corresponds to the date of the previous year, with respect to the field date. It's only displayed for information purposes, should not be needed for reporting. - name: metric data_type: text description: name of the business metric. data_tests: - not_null - name: order_by data_type: integer description: | order for displaying purposes. Null values are accepted, but keep in mind that then there's no default controlled display order. - name: include_in_account_reporting data_type: boolean description: | Category to indicate if the metric should be included in the account reporting (by deal). This will limit the display for reporting purposes. data_tests: - not_null - name: display_exclusion data_type: string description: | Category to indicate if the metric requires a certain exclusion due to relying on not timely information. This will limit the display for reporting purposes. data_tests: - not_null - accepted_values: values: - NONE - INVOICING - ONGOING_MONTH - name: number_format data_type: text description: allows for grouping and formatting for displaying purposes. data_tests: - accepted_values: values: [ "integer", "percentage", "currency_gbp", "converted_metric_currency_gbp", ] - name: value data_type: numeric description: | numeric value (integer or decimal) that corresponds to the MTD computation of the metric at a given date. - name: previous_year_value data_type: numeric description: | numeric value (integer or decimal) that corresponds to the MTD computation of the metric on the previous year at a given date. - name: relative_increment data_type: numeric description: | numeric value that corresponds to the relative increment between value and previous year value, following the computation: value / previous_year_value - 1. - name: relative_increment_with_sign_format data_type: numeric description: | relative_increment value multiplied by -1 in case this metric's growth doesn't have a positive impact for Superhog, otherwise is equal to relative_increment. This value is specially created for formatting in PBI - name: int_edeposit_and_athena_verifications description: "This table holds records on verifications for Guesty and Edeposit 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: id_accommodation data_type: text description: "unique Superhog generated id for a listing" - name: version data_type: text description: "value to identify if it is Guesty (V1) or E-deposit (V2)" data_tests: - accepted_values: values: - V1 - V2 - 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: total_fee_local data_type: double precision description: "total fee in local currency 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: company_name 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_monthly_aggregated_metrics_history_by_deal_by_time_window description: | This model aggregates monthly historic metrics for deals over different time windows. It provides insights into bookings, listings, revenue, retained revenue and additional metrics. The data is segmented by deal and time window for detailed analysis. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - id_deal - time_window - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: total_revenue_in_gbp column_B: invoiced_revenue_in_gbp + guest_payments_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: revenue_retained_in_gbp column_B: total_revenue_in_gbp + waiver_paid_back_to_host_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: revenue_retained_post_resolutions_in_gbp column_B: revenue_retained_in_gbp + host_resolution_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: guest_revenue_retained_in_gbp column_B: guest_payments_in_gbp + waiver_paid_back_to_host_in_gbp columns: - name: date data_type: date description: | The last day of the month or yesterday for historic metrics. It's the same date as for KPIs related models. data_tests: - not_null - name: id_deal data_type: character varying description: Id of the deal associated to the host. data_tests: - not_null - name: time_window data_type: character varying description: | Identifier of the time window used for the aggregation of the metrics. data_tests: - not_null - accepted_values: values: - All History - Previous 12 months - Previous 6 months - Previous 3 months - Previous month - name: client_type data_type: string description: | Type of client. It can be either PLATFORM or API. data_tests: - not_null - accepted_values: values: - PLATFORM - API - name: business_scope data_type: string description: | Business scope identifying the metric source. data_tests: - not_null - accepted_values: values: - "Old Dash" - "New Dash" - "API" - "UNSET" - name: metric_from_date data_type: date description: | The first day of the month corresponding to the lower bound range in which the metric is computed. It can be null if there's no previous history for that deal. It can vary from deal to deal depending on the number of months the deal has been active. - name: metric_to_date data_type: date description: | The first day of the month corresponding to the upper bound range in which the metric is computed. It can be null if there's no previous history for that deal. - name: main_deal_name data_type: string description: | Main name for this ID deal. data_tests: - not_null - name: has_active_pms data_type: boolean description: | Does the deal have an active associated PMS. data_tests: - not_null - name: active_pms_list data_type: string description: | Name of the active PMS associated with the deal. It can have more than one PMS associated with it. It can be null if it doesn't have any PMS associated. - name: active_accommodations_per_deal_segmentation data_type: string description: | Segment value based on the number of listings booked in 12 months for a given deal and date. data_tests: - not_null - accepted_values: values: - "0" - "01-05" - "06-20" - "21-60" - "61+" - "UNSET" - name: main_billing_country_iso_3_per_deal data_type: string description: | ISO 3166-1 alpha-3 main country code in which the Deal is billed. In some cases it's null. - name: deal_lifecycle_state data_type: string description: | Lifecycle state of the deal. - name: deal_hubspot_stage data_type: string description: | Hubspot stage of the deal. In some cases it's null. - name: account_manager data_type: string description: | Account manager of the deal. In some cases it's null. - name: live_date_utc data_type: date description: | Date when the deal went live according to Hubspot. In some cases it's null. - name: cancellation_date_utc data_type: date description: | Date when the deal was cancelled according to Hubspot. It can be null if the deal has never churned. - name: months_between_live_and_churn data_type: integer description: | Number of months between the live date and the churn date. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: last_contacted_date_utc data_type: date description: | Date when the deal was last contacted according to Hubspot. - name: days_between_last_contacted_and_churn data_type: integer description: | Number of days between the last contacted date and the churn date. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: amount_times_contacted data_type: integer description: | Number of times the deal was contacted according to Hubspot. - name: cancellation_category data_type: text description: Categorization as to why they cancelled the account - name: cancellation_details data_type: text description: Free text with additional comments on why they cancelled the account - name: is_churning data_type: boolean description: | Flag to identify if the deal is churning or not. - name: churn_reason data_type: string description: | Reason why the deal is churning. data_tests: - accepted_values: values: - "INACTIVITY" - "ACCOUNT CANCELLATION" - name: created_bookings data_type: integer description: | Total amount of bookings created by the deal in the time window. It can be null if no bookings were created. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: listings_booked_in_month data_type: decimal description: | Average amount of listings booked in month by the deal in the time window. It can be null if no listings were booked. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: total_revenue_in_gbp data_type: decimal description: | Total revenue in GBP generated by the deal in the time window. It can be null if no revenue was generated. It can be negative. - name: revenue_retained_in_gbp data_type: decimal description: | Total revenue in GBP retained by the deal in the time window, post host takeaway waivers. It can be null if no revenue was retained. It can be negative. - name: waiver_paid_back_to_host_in_gbp data_type: decimal description: | Total amount of waivers paid back to the host in GBP in the time window. It can be null if no waivers were paid back. It's displayed as a negative value. - name: invoiced_revenue_in_gbp data_type: decimal description: | Total amount of revenue in GBP invoiced to the host in the time window. It considers both Operator revenue as well as APIs revenue. It can be null if no revenue was invoiced to the host. It can be negative. - name: guest_payments_in_gbp data_type: decimal description: | Total amount of payments in GBP made by the guest in the time window. It can be null if no payments were made by the guest. It can be negative. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: guest_revenue_retained_in_gbp data_type: decimal description: | Total amount of revenue in GBP retained by the deal from the guest in the time window, post host takeaway waivers. It can be null if no revenue was retained from the guest. It can be negative. - name: host_resolution_payment_count data_type: integer description: | Total amount of resolution payments made to the host in the time window. It can be null if no resolution payments were made by the host. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: host_resolution_amount_paid_in_gbp data_type: decimal description: | Total amount of resolution payments made to the host in GBP in the time window. It can be null if no resolution payments were made by the host. It can be negative. It's displayed as a negative value. In some extreme cases, it can be higher than 0. - name: revenue_retained_post_resolutions_in_gbp data_type: decimal description: | Total amount of revenue in GBP retained by the deal post waiver payouts and resolution payouts in the time window. It can be null if no revenue was retained post resolution payments. It can be negative, thus indicating that we are losing money. - name: int_deals_consolidation description: | "This table contains all deal ids from different sources used in Superhog. It contains the source (Hubspot, Xero or Core), the id_deal and the name" columns: - name: id_deal data_type: character varying description: "Unique ID for this deal." data_tests: - unique - not_null - name: core_company_name data_type: character varying description: "Company name of the deal as shown in Core." - name: core_company_name_count data_type: integer description: "Count of distinct names the deal has in Core. It might be the case that a deal has ony NULL value for a name, so the count will be 0" data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: hubspot_deal_name data_type: character varying description: "Name of the deal as shown in Hubspot." - name: hubspot_deal_name_count data_type: integer description: "Count of distinct names the deal has in Hubspot. It might be the case that a deal has ony NULL value for a name, so the count will be 0" data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: xero_contact_name data_type: character varying description: "Contact name of the deal as shown in Xero." - name: xero_contact_name_count data_type: integer description: "Count of distinct names the deal has in Xero. It might be the case that a deal has ony NULL value for a name, so the count will be 0" data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: is_deal_in_core data_type: boolean description: "Flag to indicate if the deal is in Core." - name: is_deal_in_hubspot data_type: boolean description: "Flag to indicate if the deal is in Hubspot." - name: is_deal_in_xero data_type: boolean description: "Flag to indicate if the deal is in Xero." - name: int_ytd_mtd_main_metrics_overview description: | This model provides a high-level overview of the main metrics for the month-to-date and financial year-to-date periods. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - dimension - dimension_value - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: current_month_mtd_revenue_retained_post_resolutions_in_gbp column_B: current_month_mtd_total_revenue_in_gbp + current_month_mtd_xero_waiver_paid_back_to_host_in_gbp + current_month_mtd_xero_host_resolution_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: current_ytd_revenue_retained_post_resolutions_in_gbp column_B: current_ytd_total_revenue_in_gbp + current_ytd_xero_waiver_paid_back_to_host_in_gbp + current_ytd_xero_host_resolution_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: current_month_mtd_total_revenue_in_gbp column_B: current_month_mtd_total_guest_payments_in_gbp + current_month_mtd_xero_apis_net_fees_in_gbp + current_month_mtd_xero_operator_net_fees_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: current_ytd_total_revenue_in_gbp column_B: current_ytd_total_guest_payments_in_gbp + current_ytd_xero_apis_net_fees_in_gbp + current_ytd_xero_operator_net_fees_in_gbp columns: - name: date data_type: date description: | The date for the month-to-date and year-to-date metrics. data_tests: - not_null - name: dimension data_type: string description: | The dimension or granularity of the metrics. data_tests: - accepted_values: values: - global - name: dimension_value data_type: string description: | The value or segment available for the selected dimension. data_tests: - not_null - name: calendar_year data_type: integer description: | The calendar year associated with the data. - name: financial_year data_type: integer description: | The financial year associated with the data. - name: previous_year_date data_type: date description: | The equivalent date in the previous year. - name: current_month_mtd_billable_bookings data_type: integer description: | Total billable bookings for the current month MTD. - name: current_month_mtd_churning_deals data_type: integer description: | Number of churning deals for the current month MTD. - name: current_month_mtd_live_deals data_type: integer description: | Number of live deals for the current month MTD. - name: current_month_mtd_new_deals data_type: integer description: | Number of new deals for the current month MTD. - name: current_month_mtd_operator_revenue_per_billable_booking data_type: numeric description: | Operator revenue per billable booking for the current month MTD. - name: current_month_mtd_resolutions_payout_rate data_type: numeric description: | Resolutions payout rate for the current month MTD. - name: current_month_mtd_revenue_retained_post_resolutions_in_gbp data_type: numeric description: | Revenue retained after resolutions for the current month MTD in GBP. - name: current_month_mtd_total_guest_payments_in_gbp data_type: numeric description: | Total guest payments for the current month MTD in GBP. - name: current_month_mtd_total_revenue_in_gbp data_type: numeric description: | Total revenue for the current month MTD in GBP. - name: current_month_mtd_waiver_payments_in_gbp data_type: numeric description: | Total waiver payments made in GBP for the current month MTD. - name: current_month_mtd_waiver_payout_rate data_type: numeric description: | Waiver payout rate for the current month MTD. - name: current_month_mtd_waiver_revenue_per_billable_booking data_type: numeric description: | Waiver revenue per billable booking for the current month MTD. - name: current_month_mtd_xero_apis_net_fees_in_gbp data_type: numeric description: | Net fees for APIs processed through Xero for the current month MTD in GBP. - name: current_month_mtd_xero_host_resolution_amount_paid_in_gbp data_type: numeric description: | Amount paid to hosts for resolutions processed through Xero for the current month MTD in GBP. - name: current_month_mtd_xero_operator_net_fees_in_gbp data_type: numeric description: | Net fees for operators processed through Xero for the current month MTD in GBP. - name: current_month_mtd_xero_waiver_paid_back_to_host_in_gbp data_type: numeric description: | Waiver amounts paid back to hosts via Xero for the current month MTD in GBP. - name: current_month_mtd_total_revenue_churn_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by deals that have churned in the current month MTD. - name: current_month_mtd_total_revenue_global_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by any deal, indistinctly of it being active, churn, etc. for the current month MTD. This is only used to compute the churn rate. - name: current_month_mtd_total_revenue_churn_rate data_type: numeric description: | Total revenue churn rate for the current month MTD. - name: current_month_mtd_booking_net_fees_in_gbp data_type: numeric description: | Total Booking net fees in gbp for the current month MTD. - name: current_month_mtd_booking_net_fees_per_billable_booking data_type: numeric description: | Total Booking net fees per billable booking for the current month MTD. - name: previous_year_mtd_billable_bookings data_type: integer description: | Total billable bookings for the previous year (12 months ago) MTD. - name: previous_year_mtd_churning_deals data_type: integer description: | Number of churning deals for the previous year (12 months ago) MTD. - name: previous_year_mtd_live_deals data_type: integer description: | Number of live deals for the previous year (12 months ago) MTD. - name: previous_year_mtd_new_deals data_type: integer description: | Number of new deals for the previous year (12 months ago) MTD. - name: previous_year_mtd_operator_revenue_per_billable_booking data_type: numeric description: | Operator revenue per billable booking for the previous year (12 months ago) MTD. - name: previous_year_mtd_resolutions_payout_rate data_type: numeric description: | Resolutions payout rate for the previous year (12 months ago) MTD. - name: previous_year_mtd_revenue_retained_post_resolutions_in_gbp data_type: numeric description: | Revenue retained after resolutions for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_total_guest_payments_in_gbp data_type: numeric description: | Total guest payments for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_total_revenue_in_gbp data_type: numeric description: | Total revenue for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_waiver_payments_in_gbp data_type: numeric description: | Total waiver payments made in GBP for the previous year (12 months ago) MTD. - name: previous_year_mtd_waiver_payout_rate data_type: numeric description: | Waiver payout rate for the previous year (12 months ago) MTD. - name: previous_year_mtd_waiver_revenue_per_billable_booking data_type: numeric description: | Waiver revenue per billable booking for the previous year (12 months ago) MTD. - name: previous_year_mtd_xero_apis_net_fees_in_gbp data_type: numeric description: | Net fees for APIs processed through Xero for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_xero_host_resolution_amount_paid_in_gbp data_type: numeric description: | Amount paid to hosts for resolutions processed through Xero for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_xero_operator_net_fees_in_gbp data_type: numeric description: | Net fees for operators processed through Xero for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_xero_waiver_paid_back_to_host_in_gbp data_type: numeric description: | Waiver amounts paid back to hosts via Xero for the previous year (12 months ago) MTD in GBP. - name: previous_year_mtd_total_revenue_churn_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by deals that have churned in the previous year (12 months ago) MTD. - name: previous_year_mtd_total_revenue_global_12m data_type: numeric description: | Total revenue generated globally in the last 12 months for the previous year (12 months ago) MTD. This is only used to compute the churn rate. - name: previous_year_mtd_total_revenue_churn_rate data_type: numeric description: | Total revenue churn rate for the previous year (12 months ago) MTD. - name: previous_year_mtd_booking_net_fees_in_gbp data_type: numeric description: | Total Booking net fees in gbp for the previous year (12 months ago) MTD. - name: previous_year_mtd_booking_net_fees_per_billable_booking data_type: numeric description: | Total Booking net fees per billable booking for the previous year (12 months ago) MTD. - name: current_ytd_billable_bookings data_type: integer description: | Total billable bookings for the current financial year YTD. - name: current_ytd_churning_deals data_type: integer description: | Number of churning deals for the current financial year YTD. - name: current_ytd_live_deals data_type: integer description: | Number of live deals for the current financial year YTD. - name: current_ytd_new_deals data_type: integer description: | Number of new deals for the current financial year YTD. - name: current_ytd_operator_revenue_per_billable_booking data_type: numeric description: | Operator revenue per billable booking for the current financial year YTD. - name: current_ytd_resolutions_payout_rate data_type: numeric description: | Resolutions payout rate for the current financial year YTD. - name: current_ytd_revenue_retained_post_resolutions_in_gbp data_type: numeric description: | Revenue retained after resolutions for the current financial year YTD in GBP. - name: current_ytd_total_guest_payments_in_gbp data_type: numeric description: | Total guest payments for the current financial year YTD in GBP. - name: current_ytd_total_revenue_in_gbp data_type: numeric description: | Total revenue for the current financial year YTD in GBP. - name: current_ytd_waiver_payments_in_gbp data_type: numeric description: | Total waiver payments made in GBP for the current financial year YTD. - name: current_ytd_waiver_payout_rate data_type: numeric description: | Waiver payout rate for the current financial year YTD. - name: current_ytd_waiver_revenue_per_billable_booking data_type: numeric description: | Waiver revenue per billable booking for the current financial year YTD. - name: current_ytd_xero_apis_net_fees_in_gbp data_type: numeric description: | Net fees for APIs processed through Xero for the current financial year YTD in GBP. - name: current_ytd_xero_host_resolution_amount_paid_in_gbp data_type: numeric description: | Amount paid to hosts for resolutions processed through Xero for the current financial year YTD in GBP. - name: current_ytd_xero_operator_net_fees_in_gbp data_type: numeric description: | Net fees for operators processed through Xero for the current financial year YTD in GBP. - name: current_ytd_xero_waiver_paid_back_to_host_in_gbp data_type: numeric description: | Waiver amounts paid back to hosts via Xero for the current financial year YTD in GBP. - name: current_ytd_total_revenue_churn_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by deals that have churned in the period of the current financial year YTD. - name: current_ytd_total_revenue_global_12m data_type: numeric description: | Total revenue generated globally in the last 12 months for the current financial year YTD. This is only used to compute the churn rate. - name: current_ytd_total_revenue_churn_rate data_type: numeric description: | Total revenue churn rate for the current financial year YTD. - name: current_ytd_booking_net_fees_in_gbp data_type: numeric description: | Total booking net fees in gbp for the current financial year YTD. - name: current_ytd_booking_net_fees_per_billable_booking data_type: numeric description: | Total booking net fees per billable booking for the current financial year YTD. - name: previous_ytd_billable_bookings data_type: integer description: | Total billable bookings for the previous financial year YTD. - name: previous_ytd_churning_deals data_type: integer description: | Number of churning deals for the previous financial year YTD. - name: previous_ytd_live_deals data_type: integer description: | Number of live deals for the previous financial year YTD. - name: previous_ytd_new_deals data_type: integer description: | Number of new deals for the previous financial year YTD. - name: previous_ytd_operator_revenue_per_billable_booking data_type: numeric description: | Operator revenue per billable booking for the previous financial year YTD. - name: previous_ytd_resolutions_payout_rate data_type: numeric description: | Resolutions payout rate for the previous financial year YTD. - name: previous_ytd_revenue_retained_post_resolutions_in_gbp data_type: numeric description: | Revenue retained after resolutions for the previous financial year YTD in GBP. - name: previous_ytd_total_guest_payments_in_gbp data_type: numeric description: | Total guest payments for the previous financial year YTD in GBP. - name: previous_ytd_total_revenue_in_gbp data_type: numeric description: | Total revenue for the previous financial year YTD in GBP. - name: previous_ytd_waiver_payments_in_gbp data_type: numeric description: | Total waiver payments made in GBP for the previous financial year YTD. - name: previous_ytd_waiver_payout_rate data_type: numeric description: | Waiver payout rate for the previous financial year YTD. - name: previous_ytd_waiver_revenue_per_billable_booking data_type: numeric description: | Waiver revenue per billable booking for the previous financial year YTD. - name: previous_ytd_xero_apis_net_fees_in_gbp data_type: numeric description: | Net fees for APIs processed through Xero for the previous financial year YTD in GBP. - name: previous_ytd_xero_host_resolution_amount_paid_in_gbp data_type: numeric description: | Amount paid to hosts for resolutions processed through Xero for the previous financial year YTD in GBP. - name: previous_ytd_xero_operator_net_fees_in_gbp data_type: numeric description: | Net fees for operators processed through Xero for the previous financial year YTD in GBP. - name: previous_ytd_xero_waiver_paid_back_to_host_in_gbp data_type: numeric description: | Waiver amounts paid back to hosts via Xero for the previous financial year YTD in GBP. - name: previous_ytd_total_revenue_churn_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by deals that have churned in the period of the previous financial year YTD. - name: previous_ytd_total_revenue_global_12m data_type: numeric description: | Total revenue generated globally in the last 12 months for the previous financial year YTD. This is only used to compute the churn rate. - name: previous_ytd_total_revenue_churn_rate data_type: numeric description: | Total revenue churn rate for the previous financial year YTD. - name: previous_ytd_booking_net_fees_in_gbp data_type: numeric description: | Total booking net fees in gbp for the previous financial year YTD. - name: previous_ytd_booking_net_fees_per_billable_booking data_type: numeric description: | Total booking net fees per billable booking for the previous financial year YTD. - name: previous_month_eom_billable_bookings data_type: integer description: | Total billable bookings for the previous month, at the end of the month. - name: previous_month_eom_churning_deals data_type: integer description: | Number of churning deals for the previous month, at the end of the month. - name: previous_month_eom_live_deals data_type: integer description: | Number of live deals for the previous month, at the end of the month. - name: previous_month_eom_new_deals data_type: integer description: | Number of new deals for the previous month, at the end of the month. - name: previous_month_eom_operator_revenue_per_billable_booking data_type: numeric description: | Operator revenue per billable booking for the previous month, at the end of the month. - name: previous_month_eom_resolutions_payout_rate data_type: numeric description: | Resolutions payout rate for the previous month, at the end of the month. - name: previous_month_eom_revenue_retained_post_resolutions_in_gbp data_type: numeric description: | Revenue retained after resolutions for the previous month, at the end of the month in GBP. - name: previous_month_eom_total_guest_payments_in_gbp data_type: numeric description: | Total guest payments for the previous month, at the end of the month in GBP. - name: previous_month_eom_total_revenue_in_gbp data_type: numeric description: | Total revenue for the previous month, at the end of the month in GBP. - name: previous_month_eom_waiver_payments_in_gbp data_type: numeric description: | Total waiver payments made in GBP for the previous month, at the end of the month. - name: previous_month_eom_waiver_payout_rate data_type: numeric description: | Waiver payout rate for the previous month, at the end of the month. - name: previous_month_eom_waiver_revenue_per_billable_booking data_type: numeric description: | Waiver revenue per billable booking for the previous month, at the end of the month. - name: previous_month_eom_xero_apis_net_fees_in_gbp data_type: numeric description: | Net fees for APIs processed through Xero for the previous month, at the end of the month in GBP. - name: previous_month_eom_xero_host_resolution_amount_paid_in_gbp data_type: numeric description: | Amount paid to hosts for resolutions processed through Xero for the previous month, at the end of the month in GBP. - name: previous_month_eom_xero_operator_net_fees_in_gbp data_type: numeric description: | Net fees for operators processed through Xero for the previous month, at the end of the month in GBP. - name: previous_month_eom_xero_waiver_paid_back_to_host_in_gbp data_type: numeric description: | Waiver amounts paid back to hosts via Xero for the previous month, at the end of the month in GBP. - name: previous_month_eom_total_revenue_churn_12m data_type: numeric description: | Total revenue in the last 12 months that was generated by deals that have churned in the previous month, at the end of the month, in GBP. - name: previous_month_eom_total_revenue_global_12m data_type: numeric description: | Total revenue generated globally in the last 12 months for the previous month, at the end of the month. This is only used to compute the churn rate. - name: previous_month_eom_total_revenue_churn_rate data_type: numeric description: | Total revenue churn rate for the previous month, at the end of the month. - name: previous_month_eom_booking_net_fees_in_gbp data_type: numeric description: | Total booking net fees in gbp for the previous month at the end of the month. - name: previous_month_eom_booking_net_fees_per_billable_booking data_type: numeric description: | Total booking net fees per billable booking for the previous month at the end of the month. - name: int_ytd_mtd_aggregated_main_metrics_overview description: | This model provides a high-level overview of the main metrics for the month-to-date and financial year-to-date periods. Data is aggregated at metric level, and provides evolutions current month MTD vs. previous month EOM, current month MTD vs. previous year MTD and current YTD vs. previous YTD. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - dimension - dimension_value - metric_name - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - dimension - dimension_value - id_metric columns: - name: date data_type: date description: The date for the month-to-date and year-to-date metrics. data_tests: - not_null - name: dimension data_type: string description: The dimension or granularity of the metrics. data_tests: - accepted_values: values: - global - name: dimension_value data_type: string description: The value or segment available for the selected dimension. data_tests: - not_null - name: calendar_year data_type: integer description: The calendar year associated with the data. data_tests: - not_null - name: financial_year data_type: integer description: The financial year associated with the data. data_tests: - not_null - name: previous_year_date data_type: date description: | The equivalent date in the previous year. It can be null if the metric is not available in the previous year - name: id_metric data_type: integer description: | Unique ID for this metric. It is preferable to use this ID when building a report to ensure changes in the metric name do not affect the report. data_tests: - not_null - name: metric_name data_type: string description: | Name of the metric. It is preferable to use the ID of the metric when building a report to ensure changes in the metric name do not affect the report. data_tests: - not_null - name: number_format data_type: string description: | Number format to display the metric in the report. data_tests: - not_null - accepted_values: values: - INTEGER - PERCENTAGE_2_DECIMALS - CURRENCY_GBP_INTEGER - CURRENCY_GBP_1_DECIMAL - name: display_exclusion data_type: string description: | Category to indicate if the metric requires a certain exclusion due to relying on not timely information. This will limit the display for reporting purposes. data_tests: - not_null - accepted_values: values: - NONE - INVOICING - ONGOING_MONTH - name: current_month_mtd data_type: numeric description: | Value of the metric for the current month MTD. - name: previous_month_eom data_type: numeric description: | Value of the metric for the previous month EOM. - name: previous_year_mtd data_type: numeric description: | Value of the metric for the previous year MTD. - name: current_year_ytd data_type: numeric description: | Value of the metric for the current year YTD. - name: previous_year_ytd data_type: numeric description: | Value of the metric for the previous year YTD. - name: diff_current_month_mtd_vs_previous_month_eom data_type: numeric description: | Difference between the current month MTD and the previous month EOM. - name: diff_current_month_mtd_vs_previous_year_mtd data_type: numeric description: | Difference between the current month MTD and the previous year MTD. - name: diff_current_ytd_vs_previous_ytd data_type: numeric description: | Difference between the current year YTD and the previous year YTD. - name: rel_diff_current_month_mtd_vs_previous_month_eom data_type: numeric description: | Relative difference between the current month MTD and the previous month EOM. - name: rel_diff_current_month_mtd_vs_previous_year_mtd data_type: numeric description: | Relative difference between the current month MTD and the previous year MTD. - name: rel_diff_current_ytd_vs_previous_ytd data_type: numeric description: | Relative difference between the current year YTD and the previous year YTD. - name: target_eom_value data_type: numeric description: | The EOM target value for this metric. This is the value that we aim to achieve by the end of the month. It can be null if the target is not available. - name: target_ytd_value data_type: numeric description: | The YTD target value for this metric. This is the cumulative value that we aim to achieve by the end of each month with respect to the beginning of the financial year, that will put us to reach the EOFY target. It can be null if the target is not available. - name: target_eofy_value data_type: numeric description: | The EOFY target value for this metric. This is the value that we aim to achieve by the end of the financial year. It can be null if the target is not available. - name: diff_current_month_mtd_vs_eom_target data_type: numeric description: | Difference between the current month MTD and the EOM target. It can be null if the target is not available. - name: diff_current_ytd_vs_ytd_target data_type: numeric description: | Difference between the current year YTD and the YTD target. It can be null if the target is not available. - name: diff_current_ytd_vs_eofy_target data_type: numeric description: | Difference between the current year YTD and the EOFY target. It can be null if the target is not available. - name: rel_diff_current_month_mtd_vs_eom_target data_type: numeric description: | Relative difference between the current month MTD and the EOM target. It can be null if the target is not available. - name: rel_diff_current_ytd_vs_ytd_target data_type: numeric description: | Relative difference between the current year YTD and the YTD target. It can be null if the target is not available. - name: achievement_rate_current_ytd_vs_eofy_target data_type: numeric description: | Achievement rate between the current year YTD and the EOFY target. It can be null if the target is not available. - name: rel_diff_with_sign_current_month_mtd_vs_previous_month_eom data_type: numeric description: | Relative difference between the current month MTD and the previous month EOM, with a sign to represent if the relative difference is good (positive) or bad (negative) for our business. - name: rel_diff_with_sign_current_month_mtd_vs_previous_year_mtd data_type: numeric description: | Relative difference between the current month MTD and the previous year MTD, with a sign to represent if the relative difference is good (positive) or bad (negative) for our business. - name: rel_diff_with_sign_current_ytd_vs_previous_ytd data_type: numeric description: | Relative difference between the current year YTD and the previous year YTD, with a sign to represent if the relative difference is good (positive) or bad (negative) for our business. - name: rel_diff_with_sign_current_month_mtd_vs_eom_target data_type: numeric description: | Relative difference between the current month MTD and the EOM target, with a sign to represent if the relative difference is good (positive) or bad (negative) for our business. - name: rel_diff_with_sign_current_ytd_vs_ytd_target data_type: numeric description: | Relative difference between the current year YTD and the YTD target, with a sign to represent if the relative difference is good (positive) or bad (negative) for our business. - name: int_mtd_aggregated_metrics_by_deal description: | This model aggregates the historic information of our business by providing different metrics at account level (by id_deal). Additionally it provides Deal attributes. Metrics displayed in the model range for the past 24 months. Also, churned accounts are available for historical values, until 3 months after the offboarding date. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - date - metric - id_deal columns: - name: year data_type: int description: Year number of the given date. - name: month data_type: int description: Month number of the given date. - name: day data_type: int description: Day monthly number of the given date. - name: is_end_of_month data_type: boolean description: Is end of month, 1 for yes, 0 for no. - name: is_current_month data_type: boolean description: | Checks if the date is within the current executed month, 1 for yes, 0 for no. - name: is_end_of_month_or_yesterday data_type: boolean description: | Checks if the date is end of month or yesterday, 1 for yes, 0 for no. - name: first_day_month data_type: date description: | First day of the month corresponding to the date field. It comes from int_dates_mtd logic. - name: date data_type: date description: | Main date for the computation, that is used for filters. It comes from int_dates_mtd logic. data_tests: - not_null - name: id_deal data_type: string description: | Unique ID for a deal, or account. data_tests: - not_null - name: deal data_type: string description: | Combination of the ID and the Name of the deal. - name: active_accommodations_per_deal_segmentation data_type: string description: | Segment value based on the number of listings booked in 12 months for a given deal and date. - name: main_billing_country_iso_3_per_deal data_type: string description: | ISO 3166-1 alpha-3 main country code in which the Deal is billed. In some cases it's null. - name: business_scope data_type: string description: | Business scope identifying the metric source. - name: account_manager data_type: string description: | Account manager of the deal. In some cases it's null. - name: deal_lifecycle_state data_type: string description: | Lifecycle state of the deal. - name: metric data_type: text description: Name of the business metric. data_tests: - not_null - name: order_by data_type: integer description: | Order for displaying purposes. Null values are accepted, but keep in mind that then there's no default controlled display order. - name: number_format data_type: text description: Allows for grouping and formatting for displaying purposes. - name: value data_type: numeric description: | Numeric value (integer or decimal) that corresponds to the MTD computation of the metric at a given date. Note that if the month is not in progress, then this value corresponds to the monthly figure. - name: previous_year_value data_type: numeric description: | Numeric value (integer or decimal) that corresponds to the MTD computation of the metric on the previous year at a given date. - name: relative_increment data_type: numeric description: | Numeric value that corresponds to the relative increment between value and previous year value, following the computation: value / previous_year_value - 1. - name: relative_increment_with_sign_format data_type: numeric description: | Relative_increment value multiplied by -1 in case this metric's growth doesn't have a positive impact for Truvi, otherwise is equal to relative_increment. This value is specially created for formatting in PBI - name: display_exclusion data_type: string description: | Category to indicate if the metric requires a certain exclusion due to relying on not timely information. This will limit the display for reporting purposes. data_tests: - not_null - accepted_values: values: - NONE - INVOICING - ONGOING_MONTH - name: int_flagging_booking_categorisation description: | A model that computes different Booking counts depending whether these had claims or not, if these were categorised at risk or not, and if there was a submitted payout or not. This only applies for Bookings: - that come from New Dash users - that are protected, either by a protection or a deposit management service Additionally, we track Completed Bookings as those Bookings which, as of today, have been checked out for more than natural 14 days. From these Bookings, we check if these had an incident related in Resolution Center: - that is linked to a Booking - that is not in a duplicated status Since Bookings can be duplicated in the incidents data, we effectively consider: - Bookings with "any" claim - Bookings with a finished claim, either with a payout or not - Bookings with a finished claim and a submitted amount for payout data_tests: - dbt_expectations.expect_table_row_count_to_equal: value: 1 - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: total_bookings column_B: completed_bookings + not_completed_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: total_with_claim_bookings column_B: completed_with_claim_bookings + not_completed_with_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_bookings column_B: completed_with_claim_bookings + completed_without_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_bookings column_B: completed_risk_bookings + completed_no_risk_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_risk_bookings column_B: completed_risk_with_claim_bookings + completed_risk_without_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_with_claim_bookings column_B: completed_risk_with_claim_bookings + completed_no_risk_with_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_no_risk_bookings column_B: completed_no_risk_with_claim_bookings + completed_no_risk_without_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_without_claim_bookings column_B: completed_risk_without_claim_bookings + completed_no_risk_without_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_bookings column_B: completed_awaiting_resolution_bookings + completed_not_awaiting_resolution_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_not_awaiting_resolution_bookings column_B: completed_with_submitted_payout_bookings + completed_without_submitted_payout_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_with_submitted_payout_bookings column_B: completed_risk_with_submitted_payout_bookings + completed_no_risk_with_submitted_payout_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_without_submitted_payout_bookings column_B: completed_risk_without_submitted_payout_bookings + completed_no_risk_without_submitted_payout_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_bookings column_B: completed_risk_with_claim_bookings + completed_no_risk_without_claim_bookings + completed_risk_without_claim_bookings + completed_no_risk_with_claim_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_not_awaiting_resolution_bookings column_B: completed_risk_with_submitted_payout_bookings + completed_no_risk_without_submitted_payout_bookings + completed_risk_without_submitted_payout_bookings + completed_no_risk_with_submitted_payout_bookings - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: total_amount_paid_in_gbp column_B: completed_amount_paid_in_gbp + not_completed_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_amount_paid_in_gbp column_B: completed_finished_incidents_amount_paid_in_gbp + completed_awaiting_finish_incidents_amount_paid_in_gbp - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: completed_finished_incidents_amount_paid_in_gbp column_B: completed_risk_with_submitted_payout_amount_paid_in_gbp + completed_no_risk_with_submitted_payout_amount_paid_in_gbp columns: - name: total_bookings data_type: integer description: | Current count of New Dash Protected Bookings, either a Protection Service or a Deposit Management service, for reference. - name: completed_bookings data_type: integer description: | Current count of New Dash Protected Bookings with a Checkout happening more than 14 days ago. - name: not_completed_bookings data_type: integer description: | Current count of New Dash Protected Bookings with a Checkout happening between 14 days ago and today, or in the future. - name: total_with_claim_bookings data_type: integer description: | Current count of New Dash Protected Bookings that have had a claim, indistinctly of these bookings being considered as completed or not. - name: completed_with_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have had a claim. - name: not_completed_with_claim_bookings data_type: integer description: | Current count of New Dash Protected, NOT Completed Bookings that have had a claim. - name: completed_without_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT had a claim. - name: completed_risk_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have been flagged as at Risk. - name: completed_no_risk_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT been flagged as at Risk. - name: completed_awaiting_resolution_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have a claim and are in a resolution status that is not finished. These Bookings are excluded for the submitted payout-based performance analysis, as we don't know if the claim will be paid out or not. - name: completed_not_awaiting_resolution_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that are not awaiting resolution, either because they have a claim in a finished status or because they don't have a claim at all. - name: completed_with_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have had a submitted payout, with the claim being in a finished status. - name: completed_without_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT had a submitted payout, either because there's a claim being in a finished status without a payout or because there's no claim at all. - name: completed_risk_with_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have been flagged as at Risk AND that have had a claim. For the claim-based performance analysis, this would be the true positive. - name: completed_no_risk_without_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT been flagged as at Risk AND that have NOT had a claim. For the claim-based performance analysis, this would be the true negative. - name: completed_risk_without_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have been flagged as at Risk AND that have NOT had a claim. For the claim-based performance analysis, this would be the false positive. - name: completed_no_risk_with_claim_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT been flagged as at Risk AND that have had a claim. For the claim-based performance analysis, this would be the false negative. - name: completed_risk_with_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have been flagged as at Risk AND that have had a submitted payout, with the claim being in a finished status. For the submitted payout-based performance analysis, this would be the true positive. - name: completed_no_risk_without_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT been flagged as at Risk AND that have NOT had a submitted payout, either because there's a claim being in a finished status without a payout or because there's no claim at all. For the submitted payout-based performance analysis, this would be the true negative. - name: completed_risk_without_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have been flagged as at Risk AND that have NOT had a submitted payout, either because there's a claim being in a finished status without a payout or because there's no claim at all. For the submitted payout-based performance analysis, this would be the false positive. - name: completed_no_risk_with_submitted_payout_bookings data_type: integer description: | Current count of New Dash Protected and Completed Bookings that have NOT been flagged as at Risk AND that have had a submitted payout, with the claim being in a finished status. For the submitted payout-based performance analysis, this would be the false negative. - name: total_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for all Bookings. - name: completed_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Completed Bookings. - name: not_completed_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Bookings that are not completed, for reference. - name: completed_finished_incidents_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Completed Bookings that have had a claim in a finished status. - name: completed_awaiting_finish_incidents_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Completed Bookings that have had a claim in a status that is not finished. These Bookings are excluded for the submitted payout-based performance analysis, as technically the incident has not finished so there might be further updates. - name: completed_risk_with_submitted_payout_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Completed Bookings that have been flagged as at Risk AND that have had a submitted payout, with the claim being in a finished status. - name: completed_no_risk_with_submitted_payout_amount_paid_in_gbp data_type: numeric description: | Total amount paid in GBP in terms of Resolutions Payouts for Completed Bookings that have NOT been flagged as at Risk AND that have had a submitted payout, with the claim being in a finished status. - name: int_flagging_performance_analysis description: | Provides a basic statistical analysis with binary classification metrics on the flagging performance for New Dash Protected bookings, in the scope of claims raised or submitted payouts. data_tests: - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: count_total column_B: count_true_positive + count_true_negative + count_false_positive + count_false_negative - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: recall_score column_B: 1.0 * count_true_positive / (count_true_positive + count_false_negative) - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: precision_score column_B: 1.0 * count_true_positive / (count_true_positive + count_false_positive) - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: false_positive_rate_score column_B: 1.0 * count_false_positive / (count_false_positive + count_true_negative) - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: f1_score column_B: 2.0 * count_true_positive / (2 * count_true_positive + count_false_negative + count_false_positive) - dbt_expectations.expect_column_pair_values_to_be_equal: column_A: f2_score column_B: 5.0 * count_true_positive / (5 * count_true_positive + 4 * count_false_negative + count_false_positive) columns: - name: flagging_analysis_type data_type: string description: | Type of the analysis conducted, i.e., what do we consider as a positive - predicted (flagged) vs. actual (claim, payout). data_tests: - not_null - unique - accepted_values: values: - RISK_VS_CLAIM - RISK_VS_SUBMITTED_PAYOUT - name: count_total data_type: integer description: | Total count of bookings considered for the flagging performance analysis. - name: count_true_positive data_type: integer description: | Count of True Positives: predicted positives that are also an actual positive. - name: count_true_negative data_type: integer description: | Count of True Negatives: predicted negatives that are also an actual negative. - name: count_false_positive data_type: integer description: | Count of False Positives: predicted positives that are not an actual positive. - name: count_false_negative data_type: integer description: | Count of False Negatives: predicted negatives that are not an actual negative. - name: true_positive_score data_type: decimal description: | True Positives as a ratio over 1. This is the count of true positives divided by the total count of bookings considered for the flagging performance analysis. - name: true_negative_score data_type: decimal description: | True Negatives, as a ratio over 1. This is the count of true negatives divided by the total count of bookings considered for the flagging performance analysis. - name: false_positive_score data_type: decimal description: | False Positives, as a ratio over 1. This is the count of false positives divided by the total count of bookings considered for the flagging performance analysis. - name: false_negative_score data_type: decimal description: | False Negative, as a ratio over 1. This is the count of false negatives divided by the total count of bookings considered for the flagging performance analysis. - name: recall_score data_type: decimal description: | Recall score, or true positive rate. This corresponds to the proportion of all actual positives that were classified correctly as a positive. It can be seen as a probability of detection: in our case, it answers the question "what fraction of claim/payouts were flagged as at risk?". This is the count of true positives divided by the sum of true positives and false negatives. Recall improves when false negatives decrease. A hypothetical perfect model would have zero false negatives, and thus a recall of 1.0, or 100% detection rate. - name: precision_score data_type: decimal description: | Precision score, or positive predictive value. This corresponds to the proportion of all predicted positives that were classified correctly as a positive. In our case, it answers the question "what fraction of Bookings flagged as at Risk actually generated a Claim/Payout?". This is the count of true positives divided by the sum of true positives and false positives. Precision improves when false positives decrease. A hypothetical perfect model would have zero false positives, and thus a precision of 1.0, or 100% precision rate. - name: false_positive_rate_score data_type: decimal description: | False positive rate, or fall-out. This corresponds to the proportion of all actual negatives that were classified incorrectly as a positive. It can be seen as a probability of false alarm: in our case, it answers the question "what fraction of bookings without a claim/payout were flagged as at risk?". This is the count of false positives divided by the sum of true positives and false positives. A hypothetical perfect model would have zero false positives, and thus a false positive rate of 0.0, or 0% false alarm rate. - name: f1_score data_type: decimal description: | F1 score, which computes the harmonic mean of precision and recall. This metric balances the trade-off between precision and recall, and is useful when we want to find an optimal balance between the two. It is defined as 2 * (precision * recall) / (precision + recall). A hypothetical perfect model would have an F1 score of 1.0, or 100%. When precision and recall are far apart, the F1 score will be closer to the lower of the two. - name: f2_score data_type: decimal description: | F2 score, which computes the harmonic mean of precision and recall, but with a twice higher weight on recall. In our case, it effectively means that we want to reduce the number of false negatives, meaning reducing the number of claims/payouts that are not flagged as at risk, while still keeping a good precision. This metric is useful when we want to prioritize recall over precision, and is defined as 5 * (precision * recall) / (4 * precision + recall). A hypothetical perfect model would have an F2 score of 1.0, or 100%. When precision and recall are far apart, the F2 score will be closer to the lower of the two. - name: int_billable_items_growth_score_by_deal description: | This model computes the growth score of the billable items for each deal. The growth score is computed as the average between: - The billable items of a given month vs. the average of the previous 3 months. - The share a deal has in terms of billable items of a given month if compared to the rest of the deals vs. the average of the previous 3 months. The growth score is capped between -1 and 1. It is important to note that if we check the current month, the count of billable items and the corresponding share will be based on the projection, rather than the actual figure. In this case, the MAE and MAPE of the projected value are indicated in the model. While the growth score is computed at a monthly basis, the value will update every day with the latest available projection. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - end_date - id_deal - dbt_utils.unique_combination_of_columns: combination_of_columns: - start_date - id_deal columns: - name: start_date data_type: date description: | Start date of the period for which the growth score is computed. data_tests: - not_null - name: end_date data_type: date description: | End date of the period for which the growth score is computed. data_tests: - not_null - name: id_deal data_type: string description: | Unique ID for a deal, or account. data_tests: - not_null - name: current_month_billable_items data_type: integer description: | Monthly billable items. If the month is in progress then this value corresponds to the projected figure. This is indicated by "are_billable_items_projected" flag. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: prior_3_months_avg_monthly_billable_items data_type: integer description: | Average of the billable items for the previous 3 months. If the selected range is from 1st April 2025 to 30th April 2025, then this average will be based between 1st January 2025 to 31st March 2025. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: current_month_share_billable_items data_type: decimal description: | Share of the billable items for a given deal in the current month. If the month is in progress then this value corresponds to the projected figure. This is indicated by "are_billable_items_projected" flag. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: prior_3_months_avg_monthly_share_billable_items data_type: decimal description: | Average of the share of the billable items for a given deal in the previous 3 months. If the selected range is from 1st April 2025 to 30th April 2025, then this average will be based between 1st January 2025 to 31st March 2025. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: growth_vs_prior_3_avg_billable_items data_type: decimal description: | Growth score of the billable items based purely on the relative difference between the current month billable items vs. the prior 3 months average. This is a subcomputation of the growth score, for information purposes. It can be null. - name: growth_vs_prior_3_avg_share_billable_items data_type: decimal description: | Growth score of the billable items based purely on the relative difference between the current month share billable items vs. the prior 3 months average. This is a subcomputation of the growth score, for information purposes. It can be null. - name: growth_score data_type: decimal description: | Growth score of the billable items, based on the average between: - The billable items of a given month vs. the average of the previous 3 months. - The share a deal has in terms of billable items of a given month if compared to the rest of the deals vs. the average of the previous 3 months. The growth score is capped between -1 and 1. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: -1 max_value: 1 strictly: false - name: projection_mean_absolute_error data_type: decimal description: | Mean absolute error of the projection of the billable items. It is null if the month is not in progress or value is projected but there's no prior data to compare the projection against. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: projection_mean_absolute_percentage_error data_type: decimal description: | Mean absolute percentage error of the projection of the billable items. It is null if the month is not in progress or value is projected but there's no prior data to compare the projection against. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: are_billable_items_projected data_type: boolean description: | Flag indicating if the billable items are projected or not. If the month is in progress then this value corresponds to the projected figure. This is indicated by "are_billable_items_projected" flag. data_tests: - not_null - name: int_unified_api_verifications description: | This model unifies the API verifications data from different sources (Athena, E-Deposit, Check In Hero, Screen & Protect) into a single table. It also includes information regarding the Booking. Since the data is coming from different sources, the model provides a minimal set of columns that are relevant for this unified view. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - id_verification - api_source columns: - name: api_source data_type: string description: | Source of the API verification (e.g., ATHENA, E-DEPOSIT, CHECK_IN_HERO, SCREEN_AND_PROTECT). This field is mandatory and cannot be null. data_tests: - not_null - accepted_values: values: - ATHENA - E-DEPOSIT - CHECK_IN_HERO - SCREEN_AND_PROTECT - name: id_verification data_type: string description: | Unique ID for a verification. This ID is generated by each API, thus it can potentially be duplicated across different sources. data_tests: - not_null - name: id_booking data_type: string description: | Unique ID for a booking. It can be duplicated, as reflected in the field "is_duplicate_booking". data_tests: - not_null - name: id_deal data_type: string description: | Unique ID for a deal, or account. data_tests: - not_null - name: verification_status data_type: string description: | Status of the verification. It can be null for some sources. Status depends on the source. - name: check_in_date_utc data_type: date description: | Check-in date in UTC. It cannot be null. data_tests: - not_null - name: check_out_date_utc data_type: date description: | Check-out date in UTC. It cannot be null. data_tests: - not_null - name: created_date_utc data_type: date description: | Created date in UTC. It cannot be null. data_tests: - not_null - name: billable_date_utc data_type: date description: | Billable date in UTC. This is the point in time in which the verification can be billed. It cannot be null. data_tests: - not_null - name: is_cancelled data_type: boolean description: | Flag indicating if the booking linked to the verification is cancelled or not. It can be null for some sources. - name: is_duplicate_booking data_type: boolean description: | Flag indicating if the booking linked to the verification is a duplicate or not. It cannot be null. data_tests: - not_null - name: booking_is_duplicated_n_times data_type: integer description: | Number of times the booking linked to the verification is duplicated. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 1 strictly: false - name: int_monthly_account_revenue_impact_from_growth description: | This model computes the monthly revenue impact from the growth of billable items for each deal. The revenue impact is computed as the product of the growth score and the deal contribution to the total revenue in the previous 12 months. There's 2 impact scores computed depending on the revenue metric, namely: - impact_score_total_revenue: based on Total Revenue - impact_score_revenue_retained_post_resolutions: based on Revenue Retained Post Resolutions It is important to note that if we check the ongoing month, the count of billable items and the corresponding share will be based on the projection, rather than the actual figure. In this case, the MAE and MAPE of the projected value are indicated in the model. While the growth and impact scores are computed at a monthly basis, their values will update every day with the latest available projection. data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - end_date - id_deal - dbt_utils.unique_combination_of_columns: combination_of_columns: - start_date - id_deal columns: - name: start_date data_type: date description: | Start date of the period for which the revenue impact is computed. Corresponds to the first day of the month. data_tests: - not_null - name: end_date data_type: date description: | End date of the period for which the revenue impact is computed. Corresponds to the last day of the month. data_tests: - not_null - name: id_deal data_type: string description: | Unique ID for a deal, or account. data_tests: - not_null - name: deal data_type: string description: | Concatenation of the deal ID and the deal name. - name: client_type data_type: string description: | Type of the client, PLATFORM or API. - name: has_active_pms data_type: boolean description: | Flag indicating if the deal has an active PMS or not. - name: active_pms_list data_type: string description: | List of active PMS for the deal. It can be null if the deal has no active PMS. - name: main_billing_country_iso_3_per_deal data_type: string description: | Main billing country for the deal. It can be null. - name: deal_lifecycle_state data_type: string description: | Lifecycle state of the deal. - name: deal_hubspot_stage data_type: string description: | Hubspot stage of the deal. - name: account_manager data_type: string description: | Account manager of the deal. It can be null. - name: live_date_utc data_type: date description: | Live date of the deal according to HubSpot. It can be null. - name: cancellation_date_utc data_type: date description: | Cancellation date of the deal according to HubSpot. It can be null. - name: growth_score data_type: decimal description: | Growth score of the billable items, based on the average between: - The billable items of a given month vs. the average of the previous 3 months. - The share a deal has in terms of billable items of a given month if compared to the rest of the deals vs. the average of the previous 3 months. The growth score is capped between -1 and 1. It can be overridden to -1 in case the deal is cancelled in the same month. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: -1 max_value: 1 strictly: false - name: impact_score_total_revenue data_type: decimal description: | Impact score of the growth score on the total revenue. It is computed as the product of the growth score and the deal contribution to the total revenue in the previous 12 months. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: -1 max_value: 1 strictly: false - name: impact_score_revenue_retained_post_resolutions data_type: decimal description: | Impact score of the growth score on the revenue retained post resolutions. It is computed as the product of the growth score and the deal contribution to the revenue retained post resolutions in the previous 12 months. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: -1 max_value: 1 strictly: false - name: categorisation_impact_score_revenue_retained_post_resolutions data_type: string description: | Categorisation of the impact score on the revenue retained post resolutions. It cannot be null. data_tests: - not_null - accepted_values: values: - MAJOR DECLINE - DECLINE - FLAT - GAIN - MAJOR GAIN - name: rank_impact_score_total_revenue data_type: integer description: | Monthly rank of the deal in terms of impact score on the total revenue. - name: rank_impact_score_revenue_retained_post_resolutions data_type: integer description: | Monthly rank of the deal in terms of impact score on the revenue retained post resolutions. - name: current_month_total_revenue_in_gbp data_type: decimal description: | Total revenue in GBP for the current month. If the month is in progress then this value will be null. - name: rolling_12_months_total_revenue_in_gbp data_type: decimal description: | Total revenue in GBP for the previous 12 months. It can be null. - name: share_total_revenue_rolling_12_months data_type: decimal description: | Share of the deal in terms of total revenue in the previous 12 months. It cannot be null. data_tests: - not_null - name: rank_total_revenue_rolling_12_months data_type: integer description: | Monthly rank of the deal in terms of total revenue in the previous 12 months. - name: current_month_revenue_retained_post_resolutions_in_gbp data_type: decimal description: | Revenue retained post resolutions in GBP for the current month. If the month is in progress then this value will be null. - name: rolling_12_months_revenue_retained_post_resolutions_in_gbp data_type: decimal description: | Revenue retained post resolutions in GBP for the previous 12 months. It can be null. - name: share_revenue_retained_post_resolutions_rolling_12_months data_type: decimal description: | Share of the deal in terms of revenue retained post resolutions in the previous 12 months. It cannot be null. data_tests: - not_null - name: rank_revenue_retained_post_resolutions_rolling_12_months data_type: integer description: | Monthly rank of the deal in terms of revenue retained post resolutions in the previous 12 months. - name: current_month_billable_items data_type: integer description: | Monthly billable items. If the month is in progress then this value might be projected. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: share_billable_items_current_month data_type: decimal description: | Share of the billable items for a given deal in the current month. If the month is in progress then this value might be projected. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: rank_billable_items_current_month data_type: integer description: | Monthly rank of the deal in terms of billable items in the current month. If the month is in progress then this value might be projected. - name: projection_mean_absolute_error data_type: decimal description: | Mean absolute error of the projection of the billable items. It is null if the month is not in progress or value is projected but there's no prior data to compare the projection against. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: projection_mean_absolute_percentage_error data_type: decimal description: | Mean absolute percentage error of the projection of the billable items. It is null if the month is not in progress or value is projected but there's no prior data to compare the projection against. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: are_billable_items_projected data_type: boolean description: | Flag indicating if the billable items are projected or not. If the month is in progress then this value might be projected. It can be null if there's no projection for that deal. - name: is_growth_score_overridden_due_to_cancellation data_type: boolean description: | Flag indicating if the growth score is overridden to -1 due to cancellation in the same month. - name: int_new_dash_deal_onboarding description: | A dedicated model to track the onboarding stages of new accounts (deals) in New Dash. This excludes any deal that has been migrated from Old Dash, so it just contains "new business". columns: - name: id_deal data_type: text description: | Unique identifier of an account. data_tests: - not_null - unique - name: deal_name data_type: text description: | Name of the deal according to HubSpot. - name: onboarding_owner data_type: text description: | Name of the person that is in charge of onboarding this account. - name: account_manager data_type: text description: | Account manager in charge of the account. - name: platform_company_name data_type: text description: | Name of the company in Truvi's backend. - name: count_platform_accounts data_type: integer description: | Amount of Backend accounts (users) linked to this Deal. - name: count_programs_at_deal_level data_type: integer description: | Total amount of programs that this account has. These might not necessarily be applied to a Listing. - name: count_listings data_type: integer description: | Total count of Listings from this account. - name: count_active_listings data_type: integer description: | Count of Listings that are currently active, meaning, that have not been deactivated. - name: count_listings_with_upgraded_programs data_type: integer description: | Count of Listings that have had a program applied that contains at least one service different to Basic Screening. - name: count_active_listings_with_active_upgraded_programs data_type: integer description: | Count of Listings that are currently active and that currently have an active upgraded program, meaning, that contains at least one service different to Basic Screening. - name: count_bookings data_type: integer description: | Total count of Bookings generated from this account. - name: count_bookings_with_paid_service data_type: integer description: | Count of Bookings that have at least one paid service. - name: count_upgraded_programs_at_deal_level data_type: integer description: | Count of programs that this account has that contain, at least, one service different to Basic Screening. These might not necessarily be applied to a Listing. - name: count_upgraded_programs_at_listing_level data_type: integer description: | Count of programs that contain at least one service different to Basic Screening that have been applied to a Listing. - name: count_active_upgraded_programs_at_active_listing_level data_type: integer description: | Count of programs that contain at least one service different to Basic Screening that are currently active and applied to an active Listing. - name: contract_signed_date_utc data_type: date description: | Date in which the contract was signed according to HubSpot. - name: live_date_utc data_type: date description: | Date in which the Deal went live according to HubSpot. - name: cancellation_date_utc data_type: date description: | Date in which the Deal was cancelled according to HubSpot. - name: backend_account_creation_utc data_type: timestamp without timezone description: | Timestamp in which the account was created in the backend. - name: first_listing_created_at_utc data_type: timestamp without timezone description: | Timestamp in which the first listing was created in the backend. - name: first_booking_created_at_utc data_type: timestamp without timezone description: | Timestamp in which the first booking was created in the backend. - name: first_program_created_at_utc data_type: timestamp without timezone description: | Timestamp in which the first program was created at account level in the backend. - name: first_upgraded_program_created_at_utc data_type: timestamp without timezone description: | Timestamp in which the first program was applied to a listing for this account, in the backend. - name: first_upgraded_program_applied_to_listing_at_utc data_type: timestamp without timezone description: | Timestamp in which the first upgraded program was applied to a listing for this account, in the backend. - name: first_booking_with_paid_services_created_at_utc data_type: timestamp without timezone description: | Timestamp in which the first booking that contained paid services was created for this account, in the backend. - name: first_invoice_at_utc data_type: timestamp without timezone description: | Timestamp in which the first invoice happened for this account in Xero. - name: expressed_service_interest data_type: text description: | List of services that during onboarding generated interest to the client. - name: services_in_programs_at_deal_level data_type: text description: | List of all distinct services that appear in programs at deal level, separated by "|" and ordered alphabetically. - name: services_in_programs_applied_to_listings data_type: text description: | List of all distinct services that are applied in all listings for this account, separated by "|" and ordered alphabetically. - name: active_services_in_programs_applied_to_listings data_type: text description: | List of all distinct services that are currently active i.e., that are applied in active listings for this account, separated by "|" and ordered alphabetically. These are the current services that can be applied to new bookings. - name: has_churned data_type: boolean description: | True if the account has a cancellation date in HubSpot, False otherwise. - name: has_listings data_type: boolean description: | True if the account has at least one listing appearing in the backend, False otherwise. - name: has_active_listings data_type: boolean description: | True if the account has at least one listing that is currently active in the backend, False otherwise. - name: has_bookings data_type: boolean description: | True if the account has at least one booking appearing in the backend, False otherwise. - name: has_been_invoiced data_type: boolean description: | True if the account has at least one invoice appearing in Xero, False otherwise. - name: are_all_bookings_free data_type: boolean description: | True if the account has bookings but all of them are free, meaning, there's not a single service being paid. - name: is_account_no_longer_generating_paid_bookings data_type: boolean description: | True if the account has had in the past paid bookings but at the moment there's not a single listing that contains an active upgraded program. Encouraged to be used alongside a filter to determine if the account has churned or not. - name: has_account_changed_services_applied_in_listings data_type: boolean description: | True if the active services in programs applied to listings is different than the services that were historically applied. By nature, this always means that at least one service was being applied before that is no longer being applied. This can indicate a real decrease in services applied that could be linked to a potential revenue loss (decrease in booking fees), although it's also possible that the account has changed from a certain low-level tier to a higher-level one (ex: from Basic Protection to Protection Pro). - name: are_active_services_different_from_expressed_interest data_type: boolean description: | True if the services that are currently applied to listings are different than the ones that were expressed as interest during onboarding. This can indicate a potential need for upselling for business teams to act upon, although it's also possible that the account has added new services that where not expressed as interest during onboarding. - name: int_stay_disrupt_conversion_funnel description: | This model tracks the conversion funnel of the Stay Disrupt product. Data is aggregated in a monthly basis, up to yesterday. There's 2 funnels tracked: - At Account level - At Guest Journey level data_tests: - dbt_utils.unique_combination_of_columns: combination_of_columns: - month_start_date - guest_product_name - dbt_utils.unique_combination_of_columns: combination_of_columns: - month_start_date - guest_product_latest_display_name columns: - name: month_start_date data_type: date description: | Start date of the month for which the funnel is computed. Corresponds to the first day of the month. data_tests: - not_null - name: guest_product_name data_type: string description: | Internal name of the guest product, ex: STAYDISRUPT. Use this for filtering. It cannot be null. data_tests: - not_null - name: guest_product_latest_display_name data_type: string description: | Latest display name of the guest product. This is the name that should be used for display purposes, ex: Confident Stay. data_tests: - not_null - name: count_active_accounts data_type: integer description: | Count of accounts that have been active in the month. It doesn't necessarily mean that these offer the guest product. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: true - name: count_active_accounts_with_guest_product_offered data_type: integer description: | Count of accounts that have been active in the month and that offered the guest product via Guest Journey that month. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: count_active_accounts strictly: false - name: count_active_accounts_with_guest_product_payments data_type: integer description: | Count of accounts that have been active in the month and that had at least one payment for the guest product via Guest Journey that month. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: count_active_accounts strictly: false - name: count_guest_journeys_started data_type: integer description: | Count of Guest Journeys that have been started in the month. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: count_guest_journeys_started_with_guest_product_offered data_type: integer description: | Count of Guest Journeys that have been started in the month and that offered the guest product via Guest Journey that month. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: count_guest_journeys_started strictly: false - name: count_guest_product_payments data_type: integer description: | Count of Guest Journeys that have been started in the month and that had at least one payment for the guest product via Guest Journey that month. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: count_guest_journeys_started strictly: false - name: sum_amount_paid_without_taxes_in_gbp data_type: decimal description: | Sum of the amount paid for the guest product via Guest Journey in GBP, without taxes. It cannot be null. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: false - name: int_booking_summary description: | This model contains enriched information aggregated at Booking level under New Pricing regarding the services that are applied within a Booking. Specifically, contains both Booking and Services attributes (aggregated), as well as the total price in GBP at this specific moment in time. This model also contains information on the potential Resolution Incidents linked to a Booking, as long as these appear in the Resolution Center. In other words, it's the snapshot of the current status of the Booking. Be aware that this model only contains a subset of all bookings. It only applies to 1) Platform (Dashboard) bookings, so APIs are excluded and, 2) that come from hosts that have been migrated into the New Pricing. In essence, that the Booking is linked to a User Product Bundle. It's likely that you are just interested in retrieveing only New Dash Bookings. For this, you will need to apply the following filters: - is_user_in_new_dash = True - is_missing_id_deal = False For Data-Driven Flagging purposes, you're likely going to be interested in applying all or part of the following filters: 1. Bookings from New Dash users with Id Deal - is_user_in_new_dash = True - is_missing_id_deal = False 2. Protected Bookings with a Protection or a Deposit Management service - has_protection_service_business_type or has_deposit_management_service_business_type 3. Bookings with flagging categorisation (this excludes Cancelled/Incomplete/Rejected bookings) - is_booking_flagged_as_risk is not null data_tests: - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B: column_A: booking_completed_date_utc column_B: booking_check_out_at_utc or_equal: False - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B: column_A: booking_check_out_at_utc column_B: booking_check_in_at_utc or_equal: True - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B: column_A: number_of_applied_services column_B: number_of_applied_paid_services or_equal: True - dbt_expectations.expect_column_pair_values_A_to_be_greater_than_B: column_A: number_of_applied_services column_B: number_of_applied_upgraded_services or_equal: True columns: - name: id_booking data_type: bigint description: | The identifier of the booking. Acts as Primary Key to this table. Cannot be null. data_tests: - not_null - unique - name: id_verification_request data_type: bigint description: The identifier of the verification request. It acts as Foreign Key to Verification Request table. It can be null. - name: id_accommodation data_type: bigint description: The identifier of the accommodation or listing. It acts as Foreign Key to the Accommodation table. It cannot be null. data_tests: - not_null - name: id_user_product_bundle data_type: bigint description: The identifier of the Product Bundle, or program, that a User has applied to the Booking. It acts as Foreign Key to the User Product Bundle table. It cannot be null. data_tests: - not_null - name: id_deal data_type: string description: | Unique identifier of the account. It can be null. - name: id_user_host data_type: string description: | Unique identifier of the user that acts as a Host. Cannot be null. data_tests: - not_null - name: id_user_guest data_type: string description: | Unique identifier of the user that acts as a Guest. Can be null if Superhog does not interact with the Guest. - name: booking_status data_type: string description: | The current status of the booking. Cannot be null. data_tests: - not_null - accepted_values: values: - APPROVED - NOFLAGS - NOTAPPROVED - FLAGGED - CANCELLED - INCOMPLETEINFORMATION - REJECTED - name: program_name data_type: string description: | The name of the program, or product bundle, applied to the booking. Cannot be null. data_tests: - not_null - name: booking_created_at_utc data_type: timestamp description: | Timestamp of when the Booking record was created in the Backend. Cannot be null. data_tests: - not_null - name: booking_created_date_utc data_type: date description: | Date of when the Booking record was created in the Backend. Cannot be null. data_tests: - not_null - name: booking_updated_at_utc data_type: timestamp description: | Timestamp of when the Booking record was last updated in the Backend. Cannot be null. data_tests: - not_null - name: booking_updated_date_utc data_type: date description: | Date of when the Booking record was last updated in the Backend. Cannot be null. data_tests: - not_null - name: booking_check_in_at_utc data_type: timestamp description: | Timestamp of the Check-in of the Booking. Cannot be null. data_tests: - not_null - name: booking_check_in_date_utc data_type: timestamp description: | Date of the Check-in of the Booking. Cannot be null. data_tests: - not_null - name: booking_check_out_at_utc data_type: timestamp description: | Timestamp of the Check-out of the Booking. Cannot be null. data_tests: - not_null - name: booking_check_out_date_utc data_type: date description: | Date of the Check-out of the Booking. data_tests: - not_null - name: booking_completed_date_utc data_type: date description: | Date of the Booking completion. This is assumed to be 14 days after Check-out date, and it is used to determine the limit in which the Host can raise a Resolution Incident regarding this booking. data_tests: - not_null - name: booking_number_of_nights data_type: integer description: | Number of nights between Check-in date and Check-out date. - name: host_currency_code data_type: string description: | Iso 4217 currency code for the account of the Host. It can be null. - name: is_user_in_new_dash data_type: boolean description: | Flag to determine if this user host is in New Dash or not. data_tests: - not_null - name: new_dash_version data_type: string description: | For users that are in New Dash, specifies the New Dash Version in which these users were moved or joined. It can be null if the user is not in new dash. - name: user_in_new_dash_since_timestamp_at_utc data_type: timestamp description: | For users that are in New Dash, the effective date since these users can be considered in New Dash. If the user has moved from Old Dash, it will be the new_dash_move_at_utc. If not, it will correspond to the joined_at_utc. It can be null if the user is not in new dash. - name: booking_total_price_in_gbp data_type: decimal description: | Identifies the current total price of the booking by adding up the prices of each service applied to this 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 chargeable date due to the currency rate estimation in the future. - name: service_first_chargeable_date_utc data_type: date description: | Identifies the first moment in time in which the first service applied to this booking is supposed to be charged. - name: service_last_chargeable_date_utc data_type: date description: | Identifies the last moment in time in which the last service applied to this booking is supposed to be charged. - name: service_first_billable_date_utc data_type: date description: | Identifies the first moment in time in which the first service applied to this booking is supposed to be billed. This excludes Guest Payments, and only includes services to be invoiced to the Host. - name: service_last_billable_date_utc data_type: date description: | Identifies the last moment in time in which the last service applied to this booking is supposed to be billed. This excludes Guest Payments, and only includes services to be invoiced to the Host. - name: service_first_created_at_utc data_type: timestamp description: | Timestamp corresponding to the first creation of a Service record applied to this Booking, according to the Backend. data_tests: - not_null - name: service_last_created_at_utc data_type: timestamp description: | Timestamp corresponding to the latest creation of a Service record applied to this Booking, according to the Backend. data_tests: - not_null - name: service_last_updated_at_utc data_type: timestamp description: | Timestamp corresponding to the latest update on any Service record applied to this Booking, according to the Backend. data_tests: - not_null - name: number_of_applied_services data_type: integer description: | Total number of Services applied to this Booking. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: true - name: number_of_applied_paid_services data_type: integer description: | Total number of Services that require a monetary income to Superhog applied to this Booking. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: number_of_applied_upgraded_services strictly: false - name: number_of_applied_upgraded_services data_type: integer description: | Total number of Services different from Basic Screening applied to this Booking. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: number_of_applied_services strictly: false - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: true where: (has_protection_service_business_type = True or has_deposit_management_service_business_type = True) - name: number_of_applied_billable_services data_type: integer description: | Total number of Services that require an invoice to the Host that are applied to this Booking. data_tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: number_of_applied_upgraded_services strictly: false - name: is_booking_chargeable data_type: boolean description: | Flag to identify it the Booking is chargeable or not. In essence, it solves the question: are we supposed to get money out of this booking, or not? To be considered as chargeable, a chargeable date needs to exist as well as the total price converted to GBP needs to be strictly greater than 0. The fact that a booking is not chargeable does not necessarily mean that it won't be in the future. Similarly, if the booking is chargeable it does not necessarily mean that is actually charged. It cannot be null. data_tests: - not_null - name: is_booking_billable data_type: boolean description: | Flag to identify it the Booking is billable or not. In essence, it solves the question: are we supposed to invoice a certain amount to the host to get money out of this booking, or not? To be considered as billable, a billable date needs to exist as well as the total price of billable services, converted to GBP, need to be strictly greater than 0. The fact that a booking is not billable does not necessarily mean that it won't be in the future. The fact that a booking is not billable does not necessarily mean that it's not chargeable, since Guest Payments could still apply. Similarly, if the booking is billable it does not necessarily mean that is actually billed. It cannot be null. data_tests: - not_null - name: is_missing_currency_code_in_service_detail data_type: boolean description: | Flag to identify if the currency code is missing in any Booking Service Detail record for this Booking. - name: is_booking_cancelled data_type: boolean description: | Flag to identify if the booking has been cancelled or not. - name: has_verification_request data_type: boolean description: | Flag to identify if the booking has a verification request or not. Cannot be null. data_tests: - not_null - name: has_paid_services data_type: boolean description: | Flag to identify if the booking has any paid service or not. - name: has_upgraded_services data_type: boolean description: | Flag to identify if the booking has any service different from Basic Screening or not. - name: has_billable_services data_type: boolean description: | Flag to identify if the booking has any billable service or not. - name: has_screening_service_business_type data_type: boolean description: | Flag to identify if the booking contains any Screening service or not. - name: has_upgraded_screening_service_business_type data_type: boolean description: | Flag to identify if the booking contains any Screening services different from Basic Screening. - name: has_deposit_management_service_business_type data_type: boolean description: | Flag to identify if the booking contains any Deposit Management service or not. - name: has_protection_service_business_type data_type: boolean description: | Flag to identify if the booking contains any Protection service or not. - name: is_missing_id_deal data_type: boolean description: | Flag to identify if the Host for this booking is missing the Id Deal or not. - name: is_missing_host_currency_code data_type: boolean description: | Flag to identify if the Host for this booking is missing the currency code or not. - name: is_booking_past_completion_date data_type: boolean description: | Flag to identify if the booking is past the completion date or not. This is used to determine if the booking can be considered as completed or not, and therefore, if it can be used for further risk-assessment analysis. data_tests: - not_null - name: is_booking_flagged_as_risk data_type: boolean description: | Flag to identify if the booking has been flagged as risk or not. This is based on the Booking Status and will return: - True if the status is "NOTAPPROVED" or "FLAGGED" - False if the status is "APPROVED" or "NOFLAGS" - Null otherwise, such as "CANCELLED", "INCOMPLETEINFORMATION" or "REJECTED" data_tests: - accepted_values: values: - true where: (upper(booking_status) in ('NOTAPPROVED', 'FLAGGED')) - accepted_values: values: - false where: (upper(booking_status) in ('APPROVED', 'NOFLAGS')) - dbt_expectations.expect_column_values_to_be_null: where: (upper(booking_status) not in ('NOTAPPROVED', 'FLAGGED', 'APPROVED', 'NOFLAGS')) - name: has_resolution_incident data_type: boolean description: | Flag to identify if the booking has a Resolution Incident or not. This might imply that the resolution is in progress or that has been resolved, so it's not necessarily finished. If True, then the booking has at least one Resolution Incident. If False, then the booking has no Resolution Incidents - but this does not guarantee that the booking might have a resolution incident in the future. data_tests: - not_null - accepted_values: values: - true where: (has_resolution_incident_finished = True or has_resolution_submitted_payout = True) - accepted_values: values: - false where: (is_booking_flagged_as_risk is null) - name: has_resolution_incident_finished data_type: boolean description: | Flag to identify if the booking has a Resolution Incident that has been finished or not. This means that the resolution has been resolved and no further action is required. If True, then the booking has at least one Resolution Incident that has been finished. If False, then the booking has no Resolution Incidents that have been finished - but this does not guarantee that the booking might have a resolution incident ongoing or in the future. data_tests: - not_null - name: has_resolution_submitted_payout data_type: boolean description: | Flag to identify if the booking has a Resolution Incident that has submitted a resolution payout or not. If True, then the booking has at least one Resolution Incident that has submitted a payout. If False, then the booking has no Resolution Incidents that have submitted a payout - but this does not guarantee that the booking might have a resolution incident with a payout in the future. data_tests: - not_null - name: resolution_submitted_payout_amount_in_gbp data_type: decimal description: | Amount in GBP that has been submitted as a payout for the Resolution Incident. This is only populated if has_resolution_submitted_payout is True. It can be null if the booking has no Resolution Incidents or if the Resolution Incident has not submitted a payout. data_tests: - dbt_expectations.expect_column_values_to_be_between: min_value: 0 strictly: true - is_null: where: "has_resolution_submitted_payout = False"