SQL 활용 쿠폰 프로모션 효과 분석: 전체 주문 대비 쿠폰 사용률 살펴보기

SQL을 사용해서 프로모션 전후 주문 건수 비교를 통한 쿠폰 효과 분석을 살펴보겠습니다.

 

우선 다음과 같이 샘플 데이터를 생성하겠습니다.

실무에서는 아래 테이블 보다 복잡한 구조를 갖고 있는 점 참조 부탁 드리겠습니다.

CREATE TABLE sample_voucher (
    promotion_id INT
);
-- sample_voucher 테이블에 데이터 추가
INSERT INTO sample_voucher (promotion_id)
VALUES (1), (2), (3), (4), (5);

CREATE TABLE sample_order (
    order_id INT,
    voucher_promotion_id INT
);
-- sample_order 테이블에 데이터 추가
INSERT INTO sample_order (order_id, voucher_promotion_id)
VALUES (1, 1),
       (2, 1),
       (3, 2),
       (4, 3),
       (5, NULL),
       (6, NULL),
       (7, 4),
       (8, 5),
       (9, NULL),
       (10, NULL);

sample_voucher 테이블

promotion_id 컬럼을 포함하는 샘플 프로모션 테이블 생성 했습니다. 

INSERT 문을 사용하여 promotion_id 컬럼에 1, 2, 3, 4, 5 값을 추가했습니다.

sample_voucher 데이터 실행

sample_order 테이블

order_id 컬럼과 voucher_promotion_id 컬럼을 포함하는 샘플 주문 테이블 생성 했습니다.

INSERT 문을 사용하여 각 주문(order_id)에 대해 쿠폰 프로모션 ID(voucher_promotion_id)를 할당하며 데이터를 추가했습니다.

일부 주문은 쿠폰 프로모션을 사용하지 않아 voucher_promotion_id 값이 NULL인 경우도 있습니다.

sample_order 데이터 실행

 

아래 쿼리를 통해 프로모션 효과에 대한 분석을 진행할 수 있습니다.

WITH voucher_promotion_id AS (
    SELECT
        promotion_id
    FROM 
        sample_voucher
    GROUP BY
        1
)
SELECT
    COUNT(DISTINCT o.order_id) AS total_order_cnt,
    COUNT(DISTINCT CASE WHEN f.promotion_id IS NOT NULL THEN o.order_id END) AS voucher_order_cnt
FROM
    sample_order AS o
LEFT JOIN
    voucher_promotion_id AS f
ON
    o.voucher_promotion_id = f.promotion_id;

SELECT 문을 사용하여 다음 두 가지 값을 계산합니다.

total_order_cnt: order_table에서 주문 ID의 고유 값 수를 세어 전체 주문 건수를 계산합니다.

voucher_order_cnt: CASE문을 사용하여 쿠폰 프로모션을 사용한 주문만 선택하고, 그 고유 주문 수를 세어 쿠폰 프로모션을 사용한 주문 건수를 계산합니다.

쿼리 실행 결과

실행 결과에서 전체 주문 건은 10개이고 그중에 쿠폰을 사용한 주문 건은 6개입니다.

쿠폰 사용률은 60%로 계산할 수 있습니다.