MySQL로 온라인 스토어 트래픽 전환율 확인하기

안녕하세요, 더스퍼드입니다. 

 

MySQL을 실무에서 어떻게 사용할 수 있을지 온라인 스토어를 예시로 예상 문제를 풀어보도록 하겠습니다. 

 

 

 

 

---

특정 소스의 트래픽 전환율 (Conversion Rate) 구하기

아래 온라인 스토어 데이터 스키마가 있습니다. 해당 데이터 스키마를 기준으로 nonbrand 캠페인의 gsearch 소스 전환율을 알아보도록 하겠습니다.

 

 

아래 해당 데이터 스키마를 기준으로 작성한 쿼리가 있습니다. 쿼리를 하나씩 이해해 보도록 하겠습니다. 

 

 

1. FROM

전환율을 구하려면 구매가 발생한 횟수와 방문자 수에 대한 데이터가 필요합니다. 따라서, website_sessions 테이블과 orders 테이블을 연결하여 필요한 데이터를 조회해야 합니다. 두 테이블 모두에 공통된 데이터 항목인 website_session_id를 기준으로 두 테이블을 연결합니다. left join을 사용하여 website_sessions 테이블을 중심으로 orders 테이블을 연결합니다. 편의성을 위해 website_sessions 테이블에는 'W'라는 별칭 그리고 order 테이블에는 'O'라는 별칭을 지정했습니다. 

 

2. WHERE

2012년 4월 12일 이전의 데이터만 보기 위해 created_at < '2012-04-12'라는 조건을 설정합니다. 그리고 필요한 nonbrand 캠페인의 gsearch 소스만 확인하기 위해 W.utm_source = 'gsearch'와 W.utm_campaign = 'nonbrand'라는 조건을 설정합니다. 앞에 붙은 W는 website_sessions 테이블에서 해당 데이터를 가져온다는 뜻입니다 (앞에서 편의성을 위해 별칭을 설정한 이유입니다). 두 테이블에 같은 종류의 데이터가 있는 경우 오류가 나기 때문에 특정 테이블을 지정해야 합니다. 

 

3. SELECT

참조하는 테이블에서 가져올 데이터 항목을 지정합니다. 소스 종류 (utm_source), 캠페인 종류 (utm_campaign), 주문 수 (count로 계산), 방문자 수 (count로 계산), 그리고 주문 수를 방문자 수로 나눈 전환율을 항목으로 설정합니다. 각 계산식 항목은 알아보기 쉽게 AS로 별칭을 지정합니다.  

 

4. GROUP BY

소스 종류와 캠페인 종류를 기준으로 데이터가 정리 및 계산될 수 있도록 합니다. 계산된 데이터 항목은 GROUP BY에 사용할 수 없습니다. 

 

5. 결과 확인

Nonbrand 캠페인의 gsearch 소스 전환율은 2.8%라는 것을 확인할 수 있습니다.