dbt Fintech api

Growth Client Info

Growth Client Info model for fintech api.

inferred

Blocks

dbt SQL LKML LookML

growth_client_info.sql · 102 lines

inferred

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

SELECT
  ap.client_id as client_id,
  ap.client_name as client_name,

  -- revenue history
  pp.revenue_first as revenue_first,
  pp.revenue_last as revenue_last,
  pp.revenue_lifetime as revenue_lifetime,

  -- request history
  MIN(ap.current_time_ms) as requests_first,
  MAX(ap.current_time_ms) as requests_last,
  COUNT(*) as requests_lifetime,

  -- pay-per-request, auth
  COUNT(CASE WHEN ap.utilization_metric='auth-submit' THEN 1 END) as auth_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='auth-get' THEN 1 END) as auth_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='auth' THEN ap.occurrence_id END) as auth_distinct_oids,

  -- pay-per-request, info
  COUNT(CASE WHEN ap.utilization_metric='info-submit' THEN 1 END) as info_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='info-get' THEN 1 END) as info_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='info' THEN ap.occurrence_id END) as info_distinct_oids,

  -- pay-per-account, balance
  COUNT(CASE WHEN ap.utilization_metric='auth-delete' THEN 1 END) as auth_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='balance' THEN 1 END) as balance_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category IN ('auth','balance') THEN ap.occurrence_id END) as balance_distinct_oids,

  -- pay-per-account, connect
  COUNT(CASE WHEN ap.utilization_metric='connect-submit' THEN 1 END) as connect_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-delete' THEN 1 END) as connect_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-get' THEN 1 END) as connect_get_requests,
  COUNT(CASE WHEN ap.utilization_metric='upgrade' THEN 1 END) as upgrade_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='connect' THEN ap.occurrence_id END) as connect_distinct_oids

FROM
  api_production ap

LEFT OUTER JOIN (
  SELECT
    p.client_id as client_id,
    MIN(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_first,
    MAX(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_last,
    SUM(p.amount) as revenue_lifetime
  FROM
    production_audit p
  GROUP BY
    p.client_id
  ) as pp
ON
  pp.client_id=ap.client_id

WHERE
  ap.client_name NOT IN ('test','financial_api_provider','carl')
GROUP BY
  ap.client_id,
  ap.client_name,
  pp.revenue_first,
  pp.revenue_last,
  pp.revenue_lifetime

# models/schema.yml
version: 2

models:
  - name: growth_client_info
    columns:
      - name: client_id
        tests:
          - unique
          - not_null
      - name: client_name
      - name: revenue_last
      - name: revenue_lifetime
      - name: revenue_lifetime_tier
      - name: requests_last
      - name: requests_lifetime
      - name: days_as_client
      - name: days_as_client_tier
      - name: days_since_last_request
      - name: is_active
      - name: auth_submit_requests
      - name: auth_get_requests
      - name: auth_revenue_requests
      - name: auth_distinct_occurrence_ids
      - name: info_submit_requests
      - name: info_get_requests
      - name: info_revenue_requests
      - name: balance_requests
      - name: auth_delete_requests
      - name: balance_accounts
      - name: balance_distinct_oids
      - name: connect_submit_requests
      - name: connect_get_requests
      - name: connect_delete_requests
      - name: upgrade_requests
      - name: connect_accounts
      - name: connect_distinct_oids

Assumptions

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.

growth_client_info.sql · 61 lines

source-preserved validation: parses

SELECT
  ap.client_id as client_id,
  ap.client_name as client_name,

  -- revenue history
  pp.revenue_first as revenue_first,
  pp.revenue_last as revenue_last,
  pp.revenue_lifetime as revenue_lifetime,

  -- request history
  MIN(ap.current_time_ms) as requests_first,
  MAX(ap.current_time_ms) as requests_last,
  COUNT(*) as requests_lifetime,

  -- pay-per-request, auth
  COUNT(CASE WHEN ap.utilization_metric='auth-submit' THEN 1 END) as auth_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='auth-get' THEN 1 END) as auth_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='auth' THEN ap.occurrence_id END) as auth_distinct_oids,

  -- pay-per-request, info
  COUNT(CASE WHEN ap.utilization_metric='info-submit' THEN 1 END) as info_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='info-get' THEN 1 END) as info_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='info' THEN ap.occurrence_id END) as info_distinct_oids,

  -- pay-per-account, balance
  COUNT(CASE WHEN ap.utilization_metric='auth-delete' THEN 1 END) as auth_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='balance' THEN 1 END) as balance_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category IN ('auth','balance') THEN ap.occurrence_id END) as balance_distinct_oids,

  -- pay-per-account, connect
  COUNT(CASE WHEN ap.utilization_metric='connect-submit' THEN 1 END) as connect_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-delete' THEN 1 END) as connect_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-get' THEN 1 END) as connect_get_requests,
  COUNT(CASE WHEN ap.utilization_metric='upgrade' THEN 1 END) as upgrade_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='connect' THEN ap.occurrence_id END) as connect_distinct_oids

FROM
  api_production ap

LEFT OUTER JOIN (
  SELECT
    p.client_id as client_id,
    MIN(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_first,
    MAX(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_last,
    SUM(p.amount) as revenue_lifetime
  FROM
    production_audit p
  GROUP BY
    p.client_id
  ) as pp
ON
  pp.client_id=ap.client_id

WHERE
  ap.client_name NOT IN ('test','financial_api_provider','carl')
GROUP BY
  ap.client_id,
  ap.client_name,
  pp.revenue_first,
  pp.revenue_last,
  pp.revenue_lifetime

growth_client_info.view.lkml · 264 lines

inferred

view: growth_client_info {
  derived_table: {
    sql: SELECT
  ap.client_id as client_id,
  ap.client_name as client_name,

  -- revenue history
  pp.revenue_first as revenue_first,
  pp.revenue_last as revenue_last,
  pp.revenue_lifetime as revenue_lifetime,

  -- request history
  MIN(ap.current_time_ms) as requests_first,
  MAX(ap.current_time_ms) as requests_last,
  COUNT(*) as requests_lifetime,

  -- pay-per-request, auth
  COUNT(CASE WHEN ap.utilization_metric='auth-submit' THEN 1 END) as auth_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='auth-get' THEN 1 END) as auth_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='auth' THEN ap.occurrence_id END) as auth_distinct_oids,

  -- pay-per-request, info
  COUNT(CASE WHEN ap.utilization_metric='info-submit' THEN 1 END) as info_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='info-get' THEN 1 END) as info_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='info' THEN ap.occurrence_id END) as info_distinct_oids,

  -- pay-per-account, balance
  COUNT(CASE WHEN ap.utilization_metric='auth-delete' THEN 1 END) as auth_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='balance' THEN 1 END) as balance_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category IN ('auth','balance') THEN ap.occurrence_id END) as balance_distinct_oids,

  -- pay-per-account, connect
  COUNT(CASE WHEN ap.utilization_metric='connect-submit' THEN 1 END) as connect_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-delete' THEN 1 END) as connect_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-get' THEN 1 END) as connect_get_requests,
  COUNT(CASE WHEN ap.utilization_metric='upgrade' THEN 1 END) as upgrade_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='connect' THEN ap.occurrence_id END) as connect_distinct_oids

FROM
  api_production ap

LEFT OUTER JOIN (
  SELECT
    p.client_id as client_id,
    MIN(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_first,
    MAX(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_last,
    SUM(p.amount) as revenue_lifetime
  FROM
    production_audit p
  GROUP BY
    p.client_id
  ) as pp
ON
  pp.client_id=ap.client_id

WHERE
  ap.client_name NOT IN ('test','financial_api_provider','carl')
GROUP BY
  ap.client_id,
  ap.client_name,
  pp.revenue_first,
  pp.revenue_last,
  pp.revenue_lifetime ;;
    persist_for: "12 hours"
  }

  dimension: client_id {
    primary_key: yes
  }

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

  dimension: revenue_last {
    type: time
    sql: ${TABLE}.revenue_last ;;
  }

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

  dimension: revenue_lifetime_tier {
    type: tier
    sql: ${revenue_lifetime} ;;
  }

  dimension: requests_last {
    type: time
    sql: ${TABLE}.requests_last ;;
  }

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

  dimension: days_as_client {
    type: int
    sql: DATEDIFF(days,${TABLE}.requests_first,${TABLE}.requests_last) + 1 ;;
  }

  dimension: days_as_client_tier {
    type: tier
    sql: ${days_as_client} ;;
  }

  dimension: days_since_last_request {
    type: int
    sql: DATEDIFF(days,${TABLE}.requests_last,CURRENT_DATE) ;;
  }

  dimension: is_active {
    type: yesno
    sql: ${days_since_last_request} < 7 ;;
  }

  dimension: auth_submit_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.auth_submit_requests ;;
  }

  dimension: auth_get_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.auth_get_requests ;;
  }

  dimension: auth_revenue_requests {
    type: number
    sql: ${TABLE}.auth_submit_requests + ${TABLE}.auth_get_requests ;;
  }

  dimension: auth_distinct_occurrence_ids {
    type: number
    sql: ${TABLE}.auth_distinct_oids ;;
  }

  dimension: info_submit_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.info_submit_requests ;;
  }

  dimension: info_get_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.info_get_requests ;;
  }

  dimension: info_revenue_requests {
    type: number
    sql: ${TABLE}.info_submit_requests + ${TABLE}.info_get_requests ;;
  }

  dimension: balance_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.balance_requests ;;
  }

  dimension: auth_delete_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.auth_delete_requests ;;
  }

  dimension: balance_accounts {
    type: number
    sql: ${TABLE}.auth_submit_requests - ${TABLE}.auth_delete_requests ;;
  }

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

  dimension: connect_submit_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.connect_submit_requests ;;
  }

  dimension: connect_get_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.connect_get_requests ;;
  }

  dimension: connect_delete_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.connect_delete_requests ;;
  }

  dimension: upgrade_requests {
    type: number
    hidden: yes
    sql: ${TABLE}.upgrade_requests ;;
  }

  dimension: connect_accounts {
    type: number
    sql: ${TABLE}.connect_submit_requests + ${TABLE}.upgrade_requests - ${TABLE}.connect_delete_requests ;;
  }

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

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

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

  measure: clients_total {
    type: count_distinct
    drill_fields: [detail*]
    sql: ${client_id} ;;
  }

  measure: clients_active {
    type: count_distinct
    filters: [growth_client_info.is_active: "True"]
    sql: ${client_id} ;;
  }

  measure: retention_rate {
    type: number
    value_format: "#\%"
    sql: 100.00 * ${clients_active} / NULLIF(${clients_total},0) ;;
  }

  measure: clients_auth {
    type: count
    filters: [auth_revenue_requests: ">0"]
  }

  measure: clients_info {
    type: count
    filters: [info_revenue_requests: ">0"]
  }

  measure: clients_balance {
    type: count
    filters: [balance_accounts: ">0"]
  }

  measure: clients_connect {
    type: count
    filters: [connect_accounts: ">0"]
  }
}

growth_client_info.view.lookml · 201 lines

source-preserved

- view: growth_client_info
  derived_table:
    sql: SELECT
  ap.client_id as client_id,
  ap.client_name as client_name,

  -- revenue history
  pp.revenue_first as revenue_first,
  pp.revenue_last as revenue_last,
  pp.revenue_lifetime as revenue_lifetime,

  -- request history
  MIN(ap.current_time_ms) as requests_first,
  MAX(ap.current_time_ms) as requests_last,
  COUNT(*) as requests_lifetime,

  -- pay-per-request, auth
  COUNT(CASE WHEN ap.utilization_metric='auth-submit' THEN 1 END) as auth_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='auth-get' THEN 1 END) as auth_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='auth' THEN ap.occurrence_id END) as auth_distinct_oids,

  -- pay-per-request, info
  COUNT(CASE WHEN ap.utilization_metric='info-submit' THEN 1 END) as info_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='info-get' THEN 1 END) as info_get_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='info' THEN ap.occurrence_id END) as info_distinct_oids,

  -- pay-per-account, balance
  COUNT(CASE WHEN ap.utilization_metric='auth-delete' THEN 1 END) as auth_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='balance' THEN 1 END) as balance_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category IN ('auth','balance') THEN ap.occurrence_id END) as balance_distinct_oids,

  -- pay-per-account, connect
  COUNT(CASE WHEN ap.utilization_metric='connect-submit' THEN 1 END) as connect_submit_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-delete' THEN 1 END) as connect_delete_requests,
  COUNT(CASE WHEN ap.utilization_metric='connect-get' THEN 1 END) as connect_get_requests,
  COUNT(CASE WHEN ap.utilization_metric='upgrade' THEN 1 END) as upgrade_requests,
  COUNT(DISTINCT CASE WHEN ap.product_category='connect' THEN ap.occurrence_id END) as connect_distinct_oids

FROM
  api_production ap

LEFT OUTER JOIN (
  SELECT
    p.client_id as client_id,
    MIN(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_first,
    MAX(CASE WHEN p.amount > 0 THEN p.current_time_ms END) as revenue_last,
    SUM(p.amount) as revenue_lifetime
  FROM
    production_audit p
  GROUP BY
    p.client_id
  ) as pp
ON
  pp.client_id=ap.client_id

WHERE
  ap.client_name NOT IN ('test','financial_api_provider','carl')
GROUP BY
  ap.client_id,
  ap.client_name,
  pp.revenue_first,
  pp.revenue_last,
  pp.revenue_lifetime
    persist_for: 12 hours
  fields:
  - dimension: client_id
    primary_key: true
  - dimension: client_name
    sql: ${TABLE}.client_name
  - dimension: revenue_last
    type: time
    sql: ${TABLE}.revenue_last
  - dimension: revenue_lifetime
    type: number
    sql: ${TABLE}.revenue_lifetime
  - dimension: revenue_lifetime_tier
    type: tier
    sql: ${revenue_lifetime}
  - dimension: requests_last
    type: time
    sql: ${TABLE}.requests_last
  - dimension: requests_lifetime
    type: number
    sql: ${TABLE}.requests_lifetime
  - dimension: days_as_client
    type: int
    sql: DATEDIFF(days,${TABLE}.requests_first,${TABLE}.requests_last) + 1
  - dimension: days_as_client_tier
    type: tier
    sql: ${days_as_client}
  - dimension: days_since_last_request
    type: int
    sql: DATEDIFF(days,${TABLE}.requests_last,CURRENT_DATE)
  - dimension: is_active
    type: yesno
    sql: ${days_since_last_request} < 7
  - dimension: auth_submit_requests
    type: number
    hidden: true
    sql: ${TABLE}.auth_submit_requests
  - dimension: auth_get_requests
    type: number
    hidden: true
    sql: ${TABLE}.auth_get_requests
  - dimension: auth_revenue_requests
    type: number
    sql: ${TABLE}.auth_submit_requests + ${TABLE}.auth_get_requests
  - dimension: auth_distinct_occurrence_ids
    type: number
    sql: ${TABLE}.auth_distinct_oids
  - dimension: info_submit_requests
    type: number
    hidden: true
    sql: ${TABLE}.info_submit_requests
  - dimension: info_get_requests
    type: number
    hidden: true
    sql: ${TABLE}.info_get_requests
  - dimension: info_revenue_requests
    type: number
    sql: ${TABLE}.info_submit_requests + ${TABLE}.info_get_requests
  - dimension: balance_requests
    type: number
    hidden: true
    sql: ${TABLE}.balance_requests
  - dimension: auth_delete_requests
    type: number
    hidden: true
    sql: ${TABLE}.auth_delete_requests
  - dimension: balance_accounts
    type: number
    sql: ${TABLE}.auth_submit_requests - ${TABLE}.auth_delete_requests
  - dimension: balance_distinct_oids
    type: number
    sql: ${TABLE}.balance_distinct_oids
  - dimension: connect_submit_requests
    type: number
    hidden: true
    sql: ${TABLE}.connect_submit_requests
  - dimension: connect_get_requests
    type: number
    hidden: true
    sql: ${TABLE}.connect_get_requests
  - dimension: connect_delete_requests
    type: number
    hidden: true
    sql: ${TABLE}.connect_delete_requests
  - dimension: upgrade_requests
    type: number
    hidden: true
    sql: ${TABLE}.upgrade_requests
  - dimension: connect_accounts
    type: number
    sql: ${TABLE}.connect_submit_requests + ${TABLE}.upgrade_requests - ${TABLE}.connect_delete_requests
  - dimension: connect_distinct_oids
    type: number
    sql: ${TABLE}.connect_distinct_oids
  - dimension_group: revenue_first
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.revenue_first
  - dimension_group: requests_first
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.requests_first
  - measure: clients_total
    type: count_distinct
    drill_fields:
    - detail*
    sql: ${client_id}
  - measure: clients_active
    type: count_distinct
    filters:
    - growth_client_info.is_active: True
    sql: ${client_id}
  - measure: retention_rate
    type: number
    value_format: '#\%'
    sql: 100.00 * ${clients_active} / NULLIF(${clients_total},0)
  - measure: clients_auth
    type: count
    filters:
    - auth_revenue_requests: '>0'
  - measure: clients_info
    type: count
    filters:
    - info_revenue_requests: '>0'
  - measure: clients_balance
    type: count
    filters:
    - balance_accounts: '>0'
  - measure: clients_connect
    type: count
    filters:
    - connect_accounts: '>0'

growth_client_info.sql
growth_client_info.sql
growth_client_info.view.lkml
growth_client_info.view.lookml

Growth Client Info

Blocks

Assumptions

Contents