F2転換分析：基本編のサンプルコード

F2転換分析：基本編のサンプルコード

code: example.js

view: user_order_facts {

# 顧客マスタテーブルのビューファイルと同じラベルにすることで、

# Explore上では一つのテーブルであるかのように見せることができる

view_label: "顧客マスタ"

derived_table: {

sql:

SELECT

user_id

, COUNT(DISTINCT order_id) AS lifetime_orders --累計受注回数

, CAST(MIN(created_at) AS TIMESTAMP) AS first_order --初回受注日

FROM ***tablename***

GROUP BY user_id

;;

}

# -----

dimension: user_id {

label: "顧客ID"

primary_key: yes

hidden: yes

sql: ${TABLE}.user_id ;;

}

dimension_group: first_order {

label: "初回受注"

type: time

sql: ${TABLE}.first_order ;;

}

dimension: lifetime_orders {

label: "累計受注回数"

type: number

sql: ${TABLE}.lifetime_orders ;;

}

dimension: F2_customer {

label: "F2転換フラグ"

description: "累計受注回数 > 1　か否か"

type: yesno

sql: ${lifetime_orders} > 1 ;;

}

measure: count_F2 {

label: "F2転換顧客数"

type: count

}

measure: count_users {

label: "受注顧客数"

type: count

}

measure: F2_rate {

label: "F2転換率"

sql: 1.0 * ${count_F2} / NULLIF(${count_users},0) ;;

value_format_name: percent_1

}

}