data.ml
SQL Ecommerce marketplace

User Retention Facts

User Retention Facts model for ecommerce marketplace.

source-preserved

Blocks

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

SELECT b.profile_id as profile_id
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '30 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_30
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_60
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '90 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_90
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '180 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_180
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb + INTERVAL '30 DAYS' AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_30_to_60
FROM bookings_booking AS b
  LEFT JOIN (
    SELECT b.profile_id as profile_id
      , MIN(b.create_date) as ftb
    FROM bookings_booking AS b
    WHERE b.status = 10
    GROUP BY 1) AS ftb
  ON ftb.profile_id = b.profile_id
WHERE b.status = 10
GROUP BY 1

# models/schema.yml
version: 2

models:
  - name: user_retention_facts
    columns:
      - name: profile_id
        tests:
          - unique
          - not_null
      - name: booking_day_0_to_30
      - name: has_booking_day_0_to_30
      - name: booking_day_0_to_60
      - name: has_booking_day_0_to_60
      - name: booking_day_0_to_90
      - name: has_booking_day_0_to_90
      - name: booking_day_0_to_180
      - name: has_booking_day_0_to_180
      - name: booking_day_30_to_60
      - name: has_booking_day_30_to_60

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.
user_retention_facts.sql · 16 lines
source-preserved validation: parses 
SELECT b.profile_id as profile_id
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '30 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_30
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_60
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '90 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_90
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '180 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_180
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb + INTERVAL '30 DAYS' AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_30_to_60
FROM bookings_booking AS b
  LEFT JOIN (
    SELECT b.profile_id as profile_id
      , MIN(b.create_date) as ftb
    FROM bookings_booking AS b
    WHERE b.status = 10
    GROUP BY 1) AS ftb
  ON ftb.profile_id = b.profile_id
WHERE b.status = 10
GROUP BY 1
user_retention_facts.view.lkml · 124 lines
inferred 
view: user_retention_facts {
  derived_table: {
    sql: SELECT b.profile_id as profile_id
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '30 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_30
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_60
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '90 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_90
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '180 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_180
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb + INTERVAL '30 DAYS' AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_30_to_60
FROM bookings_booking AS b
  LEFT JOIN (
    SELECT b.profile_id as profile_id
      , MIN(b.create_date) as ftb
    FROM bookings_booking AS b
    WHERE b.status = 10
    GROUP BY 1) AS ftb
  ON ftb.profile_id = b.profile_id
WHERE b.status = 10
GROUP BY 1 ;;
  }

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

  dimension: booking_day_0_to_30 {
    type: int
    sql: ${TABLE}.booking_day_0_to_30 ;;
  }

  dimension: has_booking_day_0_to_30 {
    type: yesno
    sql: ${booking_day_0_to_30} > 0 ;;
  }

  dimension: booking_day_0_to_60 {
    type: int
    sql: ${TABLE}.booking_day_0_to_60 ;;
  }

  dimension: has_booking_day_0_to_60 {
    type: yesno
    sql: ${booking_day_0_to_60} > 0 ;;
  }

  dimension: booking_day_0_to_90 {
    type: int
    sql: ${TABLE}.booking_day_0_to_90 ;;
  }

  dimension: has_booking_day_0_to_90 {
    type: yesno
    sql: ${booking_day_0_to_90} > 0 ;;
  }

  dimension: booking_day_0_to_180 {
    type: int
    sql: ${TABLE}.booking_day_0_to_180 ;;
  }

  dimension: has_booking_day_0_to_180 {
    type: yesno
    sql: ${booking_day_0_to_180} > 0 ;;
  }

  dimension: booking_day_30_to_60 {
    type: int
    sql: ${TABLE}.booking_day_30_to_60 ;;
  }

  dimension: has_booking_day_30_to_60 {
    type: yesno
    sql: ${booking_day_30_to_60} > 0 ;;
  }

  measure: count {
    type: count
  }

  measure: count_booking_0_to_30 {
    type: count
    filters: [has_booking_day_0_to_30: "yes"]
  }

  measure: percent_booking_0_to_30 {
    label: "Repeat Booking 1-30 Days After First Booking"
    type: number
    sql: 100.0 * ${count_booking_0_to_30} / NULLIF(${count},0) ;;
  }

  measure: count_booking_0_to_60 {
    type: count
    filters: [has_booking_day_0_to_60: "yes"]
  }

  measure: percent_booking_0_to_60 {
    label: "Repeat Booking 1-60 Days After First Booking"
    type: number
    sql: 100.0 * ${count_booking_0_to_60} / NULLIF(${count},0) ;;
  }

  measure: count_booking_0_to_90 {
    type: count
    filters: [has_booking_day_0_to_90: "yes"]
  }

  measure: percent_booking_0_to_90 {
    label: "Repeat Booking 1-90 Days After First Booking"
    type: number
    sql: 100.0 * ${count_booking_0_to_90} / NULLIF(${count},0) ;;
  }

  measure: count_booking_0_to_180 {
    type: count
    filters: [has_booking_day_0_to_180: "yes"]
  }

  measure: percent_booking_0_to_180 {
    label: "Repeat Booking 1-180 Days After First Booking"
    type: number
    sql: 100.0 * ${count_booking_0_to_180} / NULLIF(${count},0) ;;
  }
}
user_retention_facts.view.lookml · 87 lines
source-preserved 
- view: user_retention_facts
  derived_table:
    sql: SELECT b.profile_id as profile_id
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '30 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_30
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_60
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '90 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_90
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb AND b.create_date <= ftb.ftb + INTERVAL '180 DAYS' THEN b.id ELSE null END) AS booking_day_0_to_180
  , COUNT(DISTINCT CASE WHEN b.create_date > ftb.ftb + INTERVAL '30 DAYS' AND b.create_date <= ftb.ftb + INTERVAL '60 DAYS' THEN b.id ELSE null END) AS booking_day_30_to_60
FROM bookings_booking AS b
  LEFT JOIN (
    SELECT b.profile_id as profile_id
      , MIN(b.create_date) as ftb
    FROM bookings_booking AS b
    WHERE b.status = 10
    GROUP BY 1) AS ftb
  ON ftb.profile_id = b.profile_id
WHERE b.status = 10
GROUP BY 1
  fields:
  - dimension: profile_id
    type: int
    primary_key: true
    sql: ${TABLE}.profile_id
  - dimension: booking_day_0_to_30
    type: int
    sql: ${TABLE}.booking_day_0_to_30
  - dimension: has_booking_day_0_to_30
    type: yesno
    sql: ${booking_day_0_to_30} > 0
  - dimension: booking_day_0_to_60
    type: int
    sql: ${TABLE}.booking_day_0_to_60
  - dimension: has_booking_day_0_to_60
    type: yesno
    sql: ${booking_day_0_to_60} > 0
  - dimension: booking_day_0_to_90
    type: int
    sql: ${TABLE}.booking_day_0_to_90
  - dimension: has_booking_day_0_to_90
    type: yesno
    sql: ${booking_day_0_to_90} > 0
  - dimension: booking_day_0_to_180
    type: int
    sql: ${TABLE}.booking_day_0_to_180
  - dimension: has_booking_day_0_to_180
    type: yesno
    sql: ${booking_day_0_to_180} > 0
  - dimension: booking_day_30_to_60
    type: int
    sql: ${TABLE}.booking_day_30_to_60
  - dimension: has_booking_day_30_to_60
    type: yesno
    sql: ${booking_day_30_to_60} > 0
  - measure: count
    type: count
  - measure: count_booking_0_to_30
    type: count
    filters:
    - has_booking_day_0_to_30: yes
  - measure: percent_booking_0_to_30
    type: number
    label: Repeat Booking 1-30 Days After First Booking
    sql: 100.0 * ${count_booking_0_to_30} / NULLIF(${count},0)
  - measure: count_booking_0_to_60
    type: count
    filters:
    - has_booking_day_0_to_60: yes
  - measure: percent_booking_0_to_60
    type: number
    label: Repeat Booking 1-60 Days After First Booking
    sql: 100.0 * ${count_booking_0_to_60} / NULLIF(${count},0)
  - measure: count_booking_0_to_90
    type: count
    filters:
    - has_booking_day_0_to_90: yes
  - measure: percent_booking_0_to_90
    type: number
    label: Repeat Booking 1-90 Days After First Booking
    sql: 100.0 * ${count_booking_0_to_90} / NULLIF(${count},0)
  - measure: count_booking_0_to_180
    type: count
    filters:
    - has_booking_day_0_to_180: yes
  - measure: percent_booking_0_to_180
    type: number
    label: Repeat Booking 1-180 Days After First Booking
    sql: 100.0 * ${count_booking_0_to_180} / NULLIF(${count},0)

Contents

  • user_retention_facts.sql
  • user_retention_facts.sql
  • user_retention_facts.view.lkml
  • user_retention_facts.view.lookml