예상 라이프타임 값 분석

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(24개월 등 X에 적합한 숫자 선택)
• 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