dbt Insurance vehicle

Customer Lifetime Value

Customer Lifetime Value model for insurance vehicle.

inferred

Blocks

dbt SQL LKML LookML

customer_lifetime_value.sql · 29 lines

inferred

-- models/customer_lifetime_value.sql
{{ config(materialized='table') }}

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM {{ ref('lifetime_value_bookings') }} AS lifetime_value_bookings

UNION

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM {{ ref('lifetime_value_analytics_connection') }} AS lifetime_value_analytics_connection

# models/schema.yml
version: 2

models:
  - name: customer_lifetime_value
    columns:
      - name: user_id
        tests:
          - unique
          - not_null
      - name: transaction_type
      - name: spend

Assumptions

Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
materialized='table' inferred from a LookML persist hint (persist_for / datagroup / sql_trigger_value).
schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

customer_lifetime_value.sql · 13 lines

source-preserved validation: not checked

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_bookings.SQL_TABLE_NAME} AS lifetime_value_bookings

UNION

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_analytics_connection.SQL_TABLE_NAME} AS lifetime_value_analytics_connection

customer_lifetime_value.view.lkml · 84 lines

inferred

view: customer_lifetime_value {
  derived_table: {
    sql: SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_bookings.SQL_TABLE_NAME} AS lifetime_value_bookings

UNION

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_analytics_connection.SQL_TABLE_NAME} AS lifetime_value_analytics_connection ;;
    persist_for: "1 hour"
  }

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

  dimension: transaction_type {
    sql: ${TABLE}.transaction_type ;;
  }

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

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

  dimension_group: first_transaction {
    type: time
    timeframes: [time, date]
    sql: ${first_and_last_transaction.first_transaction_time}::timestamp ;;
  }

  dimension_group: last_transaction {
    type: time
    timeframes: [time, date]
    sql: ${first_and_last_transaction.last_transaction_time}::timestamp ;;
  }

  measure: lifetime_transactions {
    type: count
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
  }

  measure: insurance_transactions {
    type: count
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
  }

  measure: rental_transactions {
    type: count
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
  }

  measure: lifetime_spend {
    type: sum
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
    sql: ${TABLE}.spend ;;
  }

  measure: insurance_spend {
    type: sum
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
    sql: ${TABLE}.spend ;;
  }

  measure: rental_spend {
    type: sum
    drill_fields: [user_id, time_stamp_time, transaction_type, spend]
    sql: ${TABLE}.spend ;;
  }
}

customer_lifetime_value.view.lookml · 89 lines

source-preserved

- view: customer_lifetime_value
  derived_table:
    sql: SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_bookings.SQL_TABLE_NAME} AS lifetime_value_bookings

UNION

SELECT user_id::integer AS user_id
      ,time_stamp
      ,transaction_type
      ,spend
FROM ${lifetime_value_analytics_connection.SQL_TABLE_NAME} AS lifetime_value_analytics_connection
    persist_for: 1 hour
  fields:
  - dimension: user_id
    type: int
    primary_key: true
    sql: ${TABLE}.user_id
  - dimension: transaction_type
    sql: ${TABLE}.transaction_type
  - dimension: spend
    type: number
    sql: ${TABLE}.spend
  - dimension_group: time_stamp
    type: time
    timeframes:
    - time
    - date
    sql: ${TABLE}.time_stamp
  - dimension_group: first_transaction
    type: time
    timeframes:
    - time
    - date
    sql: ${first_and_last_transaction.first_transaction_time}::timestamp
  - dimension_group: last_transaction
    type: time
    timeframes:
    - time
    - date
    sql: ${first_and_last_transaction.last_transaction_time}::timestamp
  - measure: lifetime_transactions
    type: count
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
  - measure: insurance_transactions
    type: count
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
  - measure: rental_transactions
    type: count
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
  - measure: lifetime_spend
    type: sum
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
    sql: ${TABLE}.spend
  - measure: insurance_spend
    type: sum
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
    sql: ${TABLE}.spend
  - measure: rental_spend
    type: sum
    drill_fields:
    - user_id
    - time_stamp_time
    - transaction_type
    - spend
    sql: ${TABLE}.spend

customer_lifetime_value.sql
customer_lifetime_value.sql
customer_lifetime_value.view.lkml
customer_lifetime_value.view.lookml

Customer Lifetime Value

Blocks

Assumptions

Contents