data.ml
SQL Insurance vehicle

Customer Booking Facts

Customer Booking Facts model for insurance vehicle.

source-preserved

Blocks

customer_booking_facts.sql · 57 lines
inferred 
-- models/customer_booking_facts.sql
{{ config(materialized='view') }}

SELECT bookings.customerid AS customer_id
  , COUNT(*) AS lifetime_bookings
  , SUM((bookings.totalamount * bookings.fx_rate_aud)) AS lifetime_combined_spend
  , SUM(CASE
          WHEN product='Insurance'
          THEN 1
          ELSE 0
        END) AS lifetime_insurance_bookings
  , SUM(CASE
          WHEN product='Car'
          THEN 1
          ELSE 0
        END) AS lifetime_rental_bookings
  , SUM(CASE
          WHEN product='Insurance'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END
    ) AS lifetime_insurance_spend_aud
  , SUM(CASE
          WHEN product='Car'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END) AS lifetime_rental_spend_aud
  , MIN(bookings.createdon::timestamp) AS first_booking
  , MAX(bookings.createdon::timestamp) AS latest_booking
  , MAX(CASE
          WHEN product='Insurance'
          THEN bookings.createdon::timestamp
        END) AS lastest_insurance_booking
FROM {{ this }}.bookings_table AS bookings
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: customer_booking_facts
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: lifetime_bookings
      - name: lifetime_bookings_tier
      - name: lifetime_combined_spend
      - name: lifetime_insurance_bookings
      - name: lifetime_insurance_bookings_tier
      - name: lifetime_rental_bookings
      - name: lifetime_rental_bookings_tier
      - name: customer_insurance_to_rental_ratio
      - name: customer_insurance_to_rental_ratio_tier
      - name: lifetime_insurance_spend_aud
      - name: lifetime_rental_spend_aud

Assumptions

  • materialized='view' is the default; no persist hint found.
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
customer_booking_facts.sql · 32 lines
source-preserved validation: not checked 
SELECT bookings.customerid AS customer_id
  , COUNT(*) AS lifetime_bookings
  , SUM((bookings.totalamount * bookings.fx_rate_aud)) AS lifetime_combined_spend
  , SUM(CASE
          WHEN product='Insurance'
          THEN 1
          ELSE 0
        END) AS lifetime_insurance_bookings
  , SUM(CASE
          WHEN product='Car'
          THEN 1
          ELSE 0
        END) AS lifetime_rental_bookings
  , SUM(CASE
          WHEN product='Insurance'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END
    ) AS lifetime_insurance_spend_aud
  , SUM(CASE
          WHEN product='Car'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END) AS lifetime_rental_spend_aud
  , MIN(bookings.createdon::timestamp) AS first_booking
  , MAX(bookings.createdon::timestamp) AS latest_booking
  , MAX(CASE
          WHEN product='Insurance'
          THEN bookings.createdon::timestamp
        END) AS lastest_insurance_booking
FROM ${TABLE}.bookings_table AS bookings
GROUP BY 1
customer_booking_facts.view.lkml · 124 lines
inferred 
view: customer_booking_facts {
  derived_table: {
    sql: SELECT bookings.customerid AS customer_id
  , COUNT(*) AS lifetime_bookings
  , SUM((bookings.totalamount * bookings.fx_rate_aud)) AS lifetime_combined_spend
  , SUM(CASE
          WHEN product='Insurance'
          THEN 1
          ELSE 0
        END) AS lifetime_insurance_bookings
  , SUM(CASE
          WHEN product='Car'
          THEN 1
          ELSE 0
        END) AS lifetime_rental_bookings
  , SUM(CASE
          WHEN product='Insurance'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END
    ) AS lifetime_insurance_spend_aud
  , SUM(CASE
          WHEN product='Car'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END) AS lifetime_rental_spend_aud
  , MIN(bookings.createdon::timestamp) AS first_booking
  , MAX(bookings.createdon::timestamp) AS latest_booking
  , MAX(CASE
          WHEN product='Insurance'
          THEN bookings.createdon::timestamp
        END) AS lastest_insurance_booking
FROM ${TABLE}.bookings_table AS bookings
GROUP BY 1 ;;
  }

  dimension: customer_id {
    type: int
    primary_key: yes
    sql: ${TABLE}.customer_id ;;
  }

  dimension: lifetime_bookings {
    type: number
    sql: ${TABLE}.lifetime_bookings ;;
  }

  dimension: lifetime_bookings_tier {
    type: tier
    sql: ${${TABLE}.lifetime_bookings} ;;
  }

  dimension: lifetime_combined_spend {
    type: number
    sql: ${TABLE}.lifetime_combined_spend ;;
  }

  dimension: lifetime_insurance_bookings {
    type: number
    sql: ${TABLE}.lifetime_insurance_bookings ;;
  }

  dimension: lifetime_insurance_bookings_tier {
    type: tier
    sql: ${${TABLE}.lifetime_insurance_bookings} ;;
  }

  dimension: lifetime_rental_bookings {
    type: number
    sql: ${TABLE}.lifetime_rental_bookings ;;
  }

  dimension: lifetime_rental_bookings_tier {
    type: tier
    sql: ${${TABLE}.lifetime_rental_bookings} ;;
  }

  dimension: customer_insurance_to_rental_ratio {
    type: number
    sql: ${lifetime_insurance_bookings} / NULLIF(${lifetime_rental_bookings},0) ;;
  }

  dimension: customer_insurance_to_rental_ratio_tier {
    type: tier
    sql: ${customer_insurance_to_rental_ratio} ;;
  }

  dimension: lifetime_insurance_spend_aud {
    type: number
    sql: ${TABLE}.lifetime_insurance_spend_aud ;;
  }

  dimension: lifetime_rental_spend_aud {
    type: number
    sql: ${TABLE}.lifetime_rental_spend_aud ;;
  }

  dimension_group: first_booking {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.first_booking ;;
  }

  dimension_group: latest_booking {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.latest_booking ;;
  }

  dimension_group: lastest_insurance_booking {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.lastest_insurance_booking ;;
  }

  measure: average_lifetime_bookings {
    type: average
    sql: ${lifetime_bookings} ;;
  }

  measure: count {
    type: count
  }
}
customer_booking_facts.view.lookml · 101 lines
source-preserved 
- view: customer_booking_facts
  derived_table:
    sql: SELECT bookings.customerid AS customer_id
  , COUNT(*) AS lifetime_bookings
  , SUM((bookings.totalamount * bookings.fx_rate_aud)) AS lifetime_combined_spend
  , SUM(CASE
          WHEN product='Insurance'
          THEN 1
          ELSE 0
        END) AS lifetime_insurance_bookings
  , SUM(CASE
          WHEN product='Car'
          THEN 1
          ELSE 0
        END) AS lifetime_rental_bookings
  , SUM(CASE
          WHEN product='Insurance'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END
    ) AS lifetime_insurance_spend_aud
  , SUM(CASE
          WHEN product='Car'
          THEN (bookings.totalamount * bookings.fx_rate_aud)
          ELSE 0
        END) AS lifetime_rental_spend_aud
  , MIN(bookings.createdon::timestamp) AS first_booking
  , MAX(bookings.createdon::timestamp) AS latest_booking
  , MAX(CASE
          WHEN product='Insurance'
          THEN bookings.createdon::timestamp
        END) AS lastest_insurance_booking
FROM ${TABLE}.bookings_table AS bookings
GROUP BY 1
  fields:
  - dimension: customer_id
    type: int
    primary_key: true
    sql: ${TABLE}.customer_id
  - dimension: lifetime_bookings
    type: number
    sql: ${TABLE}.lifetime_bookings
  - dimension: lifetime_bookings_tier
    type: tier
    sql: ${${TABLE}.lifetime_bookings}
  - dimension: lifetime_combined_spend
    type: number
    sql: ${TABLE}.lifetime_combined_spend
  - dimension: lifetime_insurance_bookings
    type: number
    sql: ${TABLE}.lifetime_insurance_bookings
  - dimension: lifetime_insurance_bookings_tier
    type: tier
    sql: ${${TABLE}.lifetime_insurance_bookings}
  - dimension: lifetime_rental_bookings
    type: number
    sql: ${TABLE}.lifetime_rental_bookings
  - dimension: lifetime_rental_bookings_tier
    type: tier
    sql: ${${TABLE}.lifetime_rental_bookings}
  - dimension: customer_insurance_to_rental_ratio
    type: number
    sql: ${lifetime_insurance_bookings} / NULLIF(${lifetime_rental_bookings},0)
  - dimension: customer_insurance_to_rental_ratio_tier
    type: tier
    sql: ${customer_insurance_to_rental_ratio}
  - dimension: lifetime_insurance_spend_aud
    type: number
    sql: ${TABLE}.lifetime_insurance_spend_aud
  - dimension: lifetime_rental_spend_aud
    type: number
    sql: ${TABLE}.lifetime_rental_spend_aud
  - dimension_group: first_booking
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.first_booking
  - dimension_group: latest_booking
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.latest_booking
  - dimension_group: lastest_insurance_booking
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.lastest_insurance_booking
  - measure: average_lifetime_bookings
    type: average
    sql: ${lifetime_bookings}
  - measure: count
    type: count

Contents

  • customer_booking_facts.sql
  • customer_booking_facts.sql
  • customer_booking_facts.view.lkml
  • customer_booking_facts.view.lookml