최소 구매 금액 미 충족 후, 구매 전환한 사용자 수 구하기

유저 로그 테이블에서 특정 행동 이후 구매 전환한 사용자 수를 구하는 방법을 살펴보겠습니다.

 

샘플 데이터 생성에 필요한 쿼리와 샘플 데이터는 아래와 같습니다.

CREATE TABLE user_logs (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    order_amount NUMERIC(10, 2),
    created_at TIMESTAMP NOT NULL
);

INSERT INTO user_logs (user_id, product_id, order_amount, action, created_at)
    VALUES
        (1, 100, 8000, 'failed_minimum_order', '2023-03-01 12:00:00'),
        (1, 100, 15000, 'success', '2023-03-01 12:30:00'),
        (2, 100, 7000, 'failed_minimum_order', '2023-03-02 15:00:00'),
        (2, 100, 18000, 'success', '2023-03-02 16:00:00'),
        (3, 100, 5000, 'failed_minimum_order', '2023-03-03 18:00:00'),
        (3, 100, 12000, 'success', '2023-03-03 20:00:00'),
        (4, 100, 6000, 'failed_minimum_order', '2023-03-04 09:00:00'),
        (4, 100, 10000, 'success', '2023-03-04 09:30:00'), -- 30분 후 주문
        (5, 100, 4000, 'failed_minimum_order', '2023-03-05 20:00:00'),
        (5, 100, 25000, 'success', '2023-03-05 21:00:00'), -- 1시간 후 주문
        (6, 100, 8000, 'failed_minimum_order', '2023-03-06 11:00:00'),
        (6, 100, 16000, 'success', '2023-03-06 12:00:00'), -- 1시간 후 주문
        (7, 100, 9000, 'failed_minimum_order', '2023-03-06 12:00:00'),
        (7, 100, 13000, 'success', '2023-03-06 12:30:00'); -- 30분 후 주문

샘플 데이터

테이블에는 사용자 ID, 제품 ID, 수행된 작업(성공 또는 실패), 행동이 생성된 시간을 포함하여 사용자 주문에 대한 정보가 있습니다.

데이터는 임의로 만들었고 최소화시켰습니다. 

 

아래 쿼리를 사용하여 최소 구매 금액 미 충족 후에 동일한 물건을 구매한 사용자 수를 확인합니다.

WITH failed_orders AS (
    SELECT
        user_id,
        product_id,
        order_amount,
        created_at::date AS date
    FROM
        user_logs
    WHERE
        action = 'failed_minimum_order'
),
additional_orders AS (
    SELECT
        ul.user_id,
        ul.product_id,
        fo.date
    FROM
        user_logs as ul
    JOIN
        failed_orders as fo 
    ON 
        ul.user_id = fo.user_id
    WHERE
        ul.action = 'success'
        AND ul.product_id = fo.product_id
        -- 실패한 주문 이후 하루 이내에 발생한 성공한 주문건만 카운트
        AND ul.created_at > fo.date
        AND ul.created_at <= fo.date + INTERVAL '1 day'
)
select
    date,
    count(distinct user_id) as users_with_additional_orders
from
    additional_orders
group by
    1
order by    
    1
;

쿼리 실행 결과

실패한 주문 이후 하루 이내에 발생한 성공한 주문 건만 카운트했는데, 일정 기간은 회사마다 다를 수 있습니다. 일반적으로 다음과 같습니다.

1. 하루 (24시간) : 이 기간은 고객이 처음 실패한 주문 직후에 다시 주문을 시도할 가능성이 가장 높기 때문에 많이 사용되는 기간입니다.
2. 3일 : 고객이 처음 주문에 실패한 이후에 몇 번 더 시도하거나, 다른 상품을 찾아볼 시간이 필요한 경우에 사용됩니다.
3. 일주일 (7일) : 일주일은 고객이 처음 주문에 실패한 이후에 다시 시도할 충분한 시간을 제공하며, 일부 회사에서는 주말이나 특정 요일에 다시 주문을 유도하려고 할 수 있습니다.
4. 한 달 (30일) : 이 기간은 고객이 처음 주문에 실패한 후에 다른 상품을 검토하고, 회사에서 새로운 마케팅 캠페인이나 할인을 제공할 시간을 갖는 경우에 사용됩니다.

이 외에도 한 세션 안에 해당 행동이 발생한 로직으로도 사용 합니다. 기간은 비즈니스 모델, 고객 행동 패턴, 마케팅 전략 등 다양한 요인에 따라 달라질 수 있습니다. 

이와 같이 user_log 테이블에서 위의 쿼리 로직을 이용하여, 특정 행동 후에 특정 행동을 하는 경우의 이용자수와 전환율을 확인할 수 있습니다.