ライフタイム値の期待分析

Column name: Months between first order and this order

Column type: Same Table

​
Column equation: CALCULATION

Column input: A = Seconds between customer's first order date and this order

​
Datatype: Integer

定義：case when A is null then null when A <= 0 then '1'::int else (ceil(A)/2629800)::int end

Column name: Months since order

Column type: Same Table

​
Column equation: CALCULATION

Column input: A = created_at

​
Datatype: Integer

定義：case when created_at is null then null else (ceil((extract(epoch from current_timestamp) - extract(epoch from created_at))/2629800))::int end

Column name: Calendar months between first order and this order

Column type: Same Table

​
Column equation: CALCULATION

Column inputs:

• A = created_at
• B = Customer's first order date

​
Datatype: Integer

定義：case when (A::date is null) or (B::date is null) then null else ((date_part('year',A::date) - date_part('year',B::date))*12 + date_part('month',A::date) - date_part('month',B::date))::int end

Column name: Calendar months since order

Column type: Same Table

​
Column equation: CALCULATION

Column input: A = created_at

​
Datatype: Integer

定義：case when A is null then null else ((date_part('year',current_timestamp::date) - date_part('year',A::date))*12 + date_part('month',current_timestamp::date) - date_part('month',A::date))::int end

Column name: Is in current month? (Yes/No)

Column type: Same Table

​
Column equation: CALCULATION

Column input: A = created_at

​
Datatype: String

定義：case when A is null then null when (date_trunc('month', current_timestamp::date))::varchar = (date_trunc('month', A::date))::varchar then 'Yes' else 'No' end

初回注文日別のユニーク顧客

• ゲストによる注文を有効にする場合は、customer_email を使用します

orders のテーブル内

この指標は、「個別値のカウント を実行します

customer_id 列

Customer's first order date タイムスタンプで並べ替え

指標 A: Revenue (hide)

• Calendar months between first order and this order <= X （X に適した数値を選択してください。例：24 か月）
• Is in current month? = No

​
指標: Revenue

Filter:

指標 B: All time customers (hide)

• Is in current month? = No

Metric: New customers by first order date

Filter:

指標 C: All time customers by month since first order (hide)

• Calendar months since order <= X
• Is in current month? = No

Metric: New customers by first order date

Filter:

Formula: Expected revenue

Formula: A / (B - C)

​
Format: Currency

Time period: All time

時間間隔：None

Group by: Calendar months between first order and this order – すべてを表示

group by の横にある鉛筆アイコンを使用して、All time customers 指標の group by を「独立」に変更します

Show top/bottom のフィールドを次のように編集します。

• Revenue: Top 24 sorted by Calendar months between first order and this order
• All time customers: Top 24 sorted by All time customers
• All time customers by month since first order: Top 24 sorted by All time customers by month since first order