SQL을 활용한 구매 전환율 계산

구매 전환율이란?
방문자 수 대비 실제 구매한 고객의 비율을 의미합니다.

주로 구매자가 특정 행동을 하고 어느 정도의 비율이 구매까지 이어졌는지 확인합니다.
전환율이 높을수록 비즈니스 성과가 좋다는 것을 나타냅니다.

필요한 데이터 수집하기
구매 전환율을 계산하기 위해서는 방문자 수와 구매자 수가 필요합니다.
구매자 수는 구매 이력을 수집하고, 방문자 수는 로그 데이터를 활용해 추적할 수 있습니다.

Dbeaver를 사용해서 search_logs, order_logs 샘플 데이터를 생성했습니다. 각각 데이터는 임의로 만들었습니다.
User가 search 버튼을 확인할 수 있는 search_logs 테이블과, User가 주문을 확인했는지 알 수 있는 order_logs 테이블입니다.

--search_logs 테이블 생성
CREATE TABLE search_logs (
  user_id INT,
  search_timestamp TIMESTAMP,
  search_button_clicked BOOLEAN
);
--search_logs 테이블 값 생성
INSERT INTO search_logs (user_id, search_timestamp, search_button_clicked) 
VALUES 
  (1, '2023-03-10 10:00:00', true),
  (2, '2023-03-10 11:00:00', true),
  (3, '2023-03-10 12:00:00', false),
  (4, '2023-03-11 10:00:00', true),
  (5, '2023-03-11 11:00:00', true),
  (6, '2023-03-11 12:00:00', true),
  (7, '2023-03-11 13:00:00', false),
  (8, '2023-03-12 10:00:00', true),
  (9, '2023-03-12 11:00:00', true),
  (10, '2023-03-12 12:00:00', true);


  --order_logs 테이블 생성
  CREATE TABLE order_logs (
  user_id INT,
  order_timestamp TIMESTAMP
);
--order_logs 테이블 값 생성
INSERT INTO order_logs (user_id, order_timestamp) 
VALUES 
  (1, '2023-03-10 10:30:00'),
  (2, '2023-03-10 11:30:00'),
  (4, '2023-03-11 10:30:00'),
  (5, '2023-03-11 11:30:00'),
  (5, '2023-03-11 12:30:00'),
  (6, '2023-03-11 13:30:00'),
  (9, '2023-03-12 11:30:00');

SQL로 구매 전환율 계산하기
SQL을 사용하여 구매 전환율을 계산하기 위해서는 방문자 수와 구매자 수를 세는 쿼리문을 작성해야 합니다.

예시 데이터를 사용해서 일별 user가 search 버튼을 사용한 후의 구매 전환율을 구해 보겠습니다. 쿼리는 아래와 같습니다.

WITH search_users AS (
    SELECT 
        DATE_TRUNC('day', search_timestamp) AS search_date,
        cast(COUNT(DISTINCT user_id) as numeric) AS search_count
    FROM 
        search_logs
    WHERE 
        search_button_clicked = true
    GROUP BY 
        1
), 
order_users AS (
    SELECT 
        DATE_TRUNC('day', o.order_timestamp) AS order_date,
        COUNT(DISTINCT o.user_id) AS order_count
    FROM 
        order_logs as o
    join
        search_logs as s
    on
        o.user_id = s.user_id
        and DATE_TRUNC('day', s.search_timestamp) = DATE_TRUNC('day', o.order_timestamp)
    GROUP BY 
        1
)
SELECT 
    s.search_date, 
    COALESCE(s.search_count, 0) AS search_count, 
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.order_count, 0) / COALESCE(s.search_count, 0) AS conversion_rate
FROM 
    search_users as s
LEFT JOIN 
    order_users as o
ON 
    s.search_date = o.order_date
ORDER BY 
    1;

위의 SQL 쿼리에서는 다음과 같은 임시 테이블을 생성하여 사용합니다.
search_users: search_logs 테이블에서 일별로 검색 버튼을 클릭한 사용자 수를 세어 생성한 임시 테이블입니다.
order_users: order_logs와 search_logs 테이블을 조인하여, 검색 버튼을 클릭한 후 해당 일자에 주문을 한 사용자 수를 세어 생성한 임시 테이블입니다.

조인을 하는 이유는 search 버튼을 클릭한 후 당일 내에 주문을 한 사용자 수를 확인하기 위해서입니다. 이후 search_users 테이블을 order_users 테이블에 left join 합니다. 이는 주문을 하나도 하지 않은 사용자 수가 존재할 수 있는 날짜에 대해서도 계산하기 위함입니다.

구매 전환율 분석 결과 해석하기
Dbeaver를 사용 해서 아래와 같이 결과를 도출했습니다. 예시 데이터일 뿐이니 로직 참조 용도로만 참조해 주세요.

구매 전환율을 분석하여 고객의 구매 행동 패턴을 파악할 수 있습니다. 구매 전환율이 높은 시기나 구매자 유형 등을 파악하여 비즈니스 전략 수립에 활용할 수 있습니다.

구매 전환율 개선을 위한 팁
구매 전환율을 높이기 위해서는 웹사이트 레이아웃, 제품 정보 제공, 결제 프로세스 등을 개선할 수 있습니다.
A/B 테스트를 통해 구매 전환율을 개선할 수 있는 최적의 방법을 찾아보는 것도 좋은 방법입니다.

구매 전환율은 비즈니스 성과를 나타내는 중요한 지표입니다.
SQL을 활용하여 구매 전환율을 계산하고 분석함으로써, 비즈니스 전략 수립에 도움이 되는 인사이트를 얻을 수 있습니다.