MySQL로 온라인 스토어 광고 성과 분석

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

 

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

 

 

 

---

특정 소스의 광고 성과 분석

아래 온라인 스토어 데이터 스키마가 있습니다. nonbrand 캠페인 gsearch 소스의 광고 예산을 2012년 4월 15일부터 줄였습니다. 예산을 줄인 후 nonbrand 캠페인 gsearch 소스의 광고 성과를 분석하겠습니다. 

 

 

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

 

 

 

1. FROM

날짜 (created_at)와 방문자 수 (website_session_id) 데이터가 필요하기 때문에 website_sessions 테이블에서 데이터를 참조합니다.

 

2. WHERE

nonbrand 캠페인의 gsearch 소스만 확인하기 위해  utm_source = 'gsearch'와 utm_campaign = 'nonbrand'를 설정합니다. 2012년 4월 15일을 기준으로 광고 예산 변화에 따른 방문자 수 차이를 분석하기 위해 2012년 5월 10일 이전 날짜를  ㅇcreated_at < '2012-05-10'를 설정합니다. 3가지 조건을 모두 만족하는 데이터 행을 가져와야 하기 때문에 AND를 사용합니다.  

 

3. SELECT

2012년 연도만 따로 표시하기 위해 YEAR 함수를 사용합니다 YEAR(created_at) AS yr. 또한, 해당 연도의 몇 번째 주인지 따로 표시하기 위해 WEEK 함수를 사용합니다 WEEK(created_at) AS wk. 그리고 해당 주의 첫 번째 날짜를 표시하기 위해 MIN 함수 사용합니다 MIN(DATE(created_at)) AS start_of_week. 날짜 데이터의 형식이 문자 형식일 수도 있기 때문에 DATE 함수를 사용하여 날짜 형식으로 만들어줍니다. 마지막으로 총 방문자 수를 COUNT 함수를 사용하여 구해야 하는데 중복 방문자를 제외하기 위해 DISTICT 함수를 사용합니다 COUNT(DISTINCT website_session_id) AS sessions. AS를 사용하여 별칭을 설정할 수 있습니다. 

 

4. GROUP BY

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

 

5. 결과 확인

 Nonbrand 캠페인 gsearch 소스의 광고 예산을 2012년 4월 15일부터 줄였습니다. 이전 날짜 대비 15일부터 방문자 수가 눈에 띄게 줄어들었으며 19일까지 점점 줄어드는 결과를 확인할 수 있습니다. 다른 소스와도 비교해 볼 필요가 있겠지만 해당 결과만 봤을 때 nonbrand 캠페인 gsearch 소스는 광고 예산에 민감하다고 볼 수 있을 것 같습니다.