F2転換分析：発展編のサンプルコード
F2転換分析：発展編のサンプルコード
https://scrapbox.io/files/675a4a9ac16467b7958b169e.png
code: example.js
view: user_order_fact_f2 {
view_label: "顧客マスタ"
derived_table: {
sql: WITH orders_with_first_order_date AS (SELECT
order_id,
order_items.user_id,
created_at,
first_order_date
FROM
***テーブル名*** as order_items
LEFT JOIN(
SELECT
user_id,
CAST(MIN(created_at) AS TIMESTAMP) AS first_order_date
FROM
yoshitake-looker-core-argolis.thelook_ecomm.orders
GROUP BY user_id) first_orders
ON order_items.user_id = first_orders.user_id)
SELECT
user_id,
first_order_date,
COUNT(user_id) as lifetime_orders,
-- liquidを使ってパラメータを仕込む
SUM(CASE WHEN created_at <= DATE_ADD(first_order_date, INTERVAL {% parameter interval_days %} DAY) THEN 1 ELSE 0 END) as orders_in_first_x_days,
FROM orders_with_first_order_date
GROUP BY user_id, first_order_date ;;
}
####
# このパラメータを変動させることで判定日数を変えられる（画像参照）
parameter: interval_days {
label: "F2転換の判定日数上限"
type: number
default_value: "90"
}
dimension: user_id {
type: number
sql: ${TABLE}.user_id ;;
}
dimension_group: first_order_date {
label: "初回受注"
type: time
sql: ${TABLE}.first_order_date ;;
}
dimension: lifetime_orders {
label: "累計受注回数"
type: number
sql: ${TABLE}.lifetime_orders ;;
}
dimension: orders_in_first_x_days {
label: "初回受注からx日以内の受注回数"
type: number
sql: ${TABLE}.orders_in_first_x_days ;;
}
dimension: f2_customer {
label: "F2転換フラグ"
type: yesno
sql: ${orders_in_first_x_days} > 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
}
set: detail {
fields: [
user_id,
first_order_date_time,
lifetime_orders,
orders_in_first_x_days
]
}
}