SQL을 활용한 뉴스레터 구독자 데이터 분석

데이터 분석 목적

• 뉴스레터 플랫폼의 유료화 -> 메일 발송 대상 타겟팅을 통한 비용 최소화
• 코드카타만 풀어보고 직접 데이터를 전처리하고 업로드하는 경험은 많이 해보지 않았기 때문에 SQL로 데이터 분석 시도

---

DBeaver에 csv 파일 업로드하기

• localhost에 새로운 Database 생성
• CSV Basic 사용을 시도해 보았으나 사용할 수 있는 문법이 제한적이기 때문에 다시 MySQL을 사용하는 것으로 결정

    ▶ csv 파일을 import 하면서 다양한 문제 발생

1. 날짜 형식에 '+900' 이 포함되어 DATETIME으로 인식되지 않는 경우
2. 이메일과 닉네임이 같은 열에 작성되어 있는 경우
3. 문자가 너무 긴 경우 등등..

   ▶ 데이터 전처리와 csv 파일을 업로드해보는 경험은 많이 해보지 않았기 때문에 SQL문을 작성하는 것보다 이 과정이 더 오래 걸리고 힘들었지만 다음에는 더 능숙하게 잘 할 수 있을 것 같음!

---

데이터 분석을 통해 알고자 하는 것

• 구독자 오픈율 데이터
• 오픈율이 높은 유저의 특성 파악
• 메일 발송 대상 타겟팅

---

구독자 오픈률

• 구독자별 오픈률

SELECT 이메일, 
	ROUND(`오픈한 뉴스레터 수` / `받은 뉴스레터 수` * 100, 2) 오픈율
FROM subscriber

 

• 오픈률이 평균 이상인 구독자

   ▶ 오픈율이 평균 이상인 구독자는 1083명 중 396명으로 36.56%의 비율

-- 평균 오픈율
SELECT ROUND(AVG(`오픈한 뉴스레터 수` / `받은 뉴스레터 수` * 100), 2) 평균오픈
FROM subscriber

-- 오픈율이 평균 이상인 구독자
WITH openrate AS (
	SELECT 이메일, 
	ROUND(`오픈한 뉴스레터 수` / `받은 뉴스레터 수` * 100, 2) 오픈율
	FROM subscriber
	)			
SELECT *
FROM subscriber s 
JOIN openrate o ON s.이메일 = o.이메일 
WHERE 오픈율 > 35.39

---

오픈율이 높은 구독자의 특성 파악

• 설문조사 응답자의 뉴스레터 오픈율

   ▶ 가정: 설문조사 응답자는 평소 뉴스레터를 잘 챙겨보고 있었던 구독자일 것이다

   ▶ 결론: 설문조사 응답 인원 56명 중 25명 44.64%가 평균보다 높은 오픈율을 보이고 있음

• 다만, 아래와 같은 이유로 메일리 구독자 데이터 자체의 신뢰성에 대한 의문 발생
  • 작년 여름-가을쯤 설문조사를 했는데 생성일이 12월 30일로 나온 경우
  • 생성일과 마지막 오픈시간이 얼마 차이가 나지 않는데 뉴스레터를 굉장히 많이 받은 경우
  • 생성일이 잘못 기록되고 있는 경우가 있거나 생성일 != 구독일 일수도 있음
    • 생성일의 정의에 대한 문의 필요

-- 설문조사 응답자는 뉴스레터를 잘 보고 있는가
SELECT s.이메일, s.나이, s.직업, s.`유입 경로`, s.목적, s.만족도, s.`전하고 싶은 이야기`,
	s2.생성일, s2.`마지막 오픈 시간`, s2.`받은 뉴스레터 수`, s2.`오픈한 뉴스레터 수`, 
	ROUND(s2.`오픈한 뉴스레터 수` / s2.`받은 뉴스레터 수` * 100, 2) 오픈률
FROM survey s 
LEFT JOIN subscriber s2 
ON s.이메일 = s2.이메일
WHERE ROUND(s2.`오픈한 뉴스레터 수` / s2.`받은 뉴스레터 수` * 100, 2) > 35.39
ORDER BY s2.`마지막 오픈 시간` DESC;

• 유료콘텐츠 결제자의 뉴스레터 오픈율

• 유료 콘텐츠 결제자 중 6명은 구독자 X
• 9명 중 4명 44.44%가 평균보다 높은 오픈율을 보이고 있음
  • 마지막 오픈 시간과 유료 콘텐츠 발행 일자가 일치하지 않아 마지막 오픈 시간도 잘못 기록되고 있는 경우가 있을 것으로 추정

-- 유료 콘텐츠 결제자는 뉴스레터를 잘 보고 있는가 
SELECT b.구매자, s2.생성일, s2.`마지막 오픈 시간`, s2.`받은 뉴스레터 수`, s2.`오픈한 뉴스레터 수`, 
	ROUND(s2.`오픈한 뉴스레터 수` / s2.`받은 뉴스레터 수` * 100, 2) 오픈률
FROM buyer b 
LEFT JOIN subscriber s2
ON s2.이메일 = b.구매자 
ORDER BY s2.`마지막 오픈 시간` DESC;

• 오픈율이 높은 구독자의 특성

• 전체 구독자 대비 설문조사 참여자와 유료 콘텐츠 결제자가 평균 이상의 오픈율을 보이는 경우가 많음
• 이는 곧, 평소 마케팅레시피의 전략에 잘 반응하고 있는 구독자들의 오픈율이 높음을 의미
• 설문조사 데이터 기반 오픈율이 높은 구독자의 특성
  • 나이: 20대 구독자가 72%로 가장 높은 비율 차지, 그 다음은 3, 4, 50대 순서
  • 직업: 마케팅 종사자가 60%로 가장 높은 비율 차지, 그 다음은 마케팅 직종 희망자
  • 유입경로: 웹사이트 검색과 SNS를 통한 유입이 주를 이루고 있음
  • 구독 목적: 마케팅 트렌드 파악, 인사이트 획득이 구독의 주된 이유

---

메일 발송 대상 타겟팅

• 1월 14일까지의 구독자를 기준으로 데이터 분석
• 오픈율은 높지만 최근 오픈을 하지 않은 구독자보다는 최근에도 오픈을 하고 있으며 새로 유입된 구독자에 주목
• 12월부터 1월 사이에 뉴스레터를 마지막으로 오픈했거나 구독을 시작한 사람
• 현재까지 237명 -> 237*4 = 948명

-- 메일 발송 대상 타겟팅
SELECT 이메일, 
       DATE_FORMAT(생성일, '%Y-%m') AS first_open, 
       DATE_FORMAT(`마지막 오픈 시간`, '%Y-%m') AS last_open, 
       `구독 선택하기`
FROM subscriber
WHERE (DATE_FORMAT(생성일, '%Y-%m') >= '2024-12' 
       OR DATE_FORMAT(`마지막 오픈 시간`, '%Y-%m') >= '2024-12')
      AND `구독 선택하기` = 'free'
ORDER BY 2 DESC, 3 DESC;

---

INSIGHT

1. column에 띄어쓰기가 되어 있는 경우 ``을 사용해야함
2. 콘텐츠별 데이터를 활용하면 더 정확한 정보를 얻을 수 있을 것으로 추정되며 이를 기반으로 다시 타겟팅을 해봐도 좋을 듯
3. 월간 뉴스레터 발송 수와 최대 발송 건수를 명확히 한 후 다시 타겟팅을 시도해봐도 좋을 듯
4. 코호트 차트 제작을 통한 구독자 리텐션 파악 필요 (한 번 이탈하면 다시 돌아오지 않는가)
5. 설문조사의 경우 표본이 많지는 않지만 항목별 비율이 크게 차이나기 때문에 설문조사 결과가 유의미하다고 볼 수 있음