TIL

[멋쟁이사자처럼 부트캠프 그로스 마케팅] 데이터 분석 개론 : SQL (2)

•ɞ• 2025. 2. 12. 18:26

 
 
 
 
1도 안 올리고 2를요?
어쩔 수 없어 오늘은 저녁 약속이 있으니 오늘 배운 걸 냅다 올리겠어

 
 
 
학습목표

  • JOIN 이해
  • UNION 이해
  • 마케팅 예제 실습

 
 

1. JOIN

1) INNER JOIN

고객과 주문 정보를 함께 조회
--> customers 테이블과 orders 테이블을 customer_id 를 기준으로 연결하여 customers 테이블의 name, email과 orders 테이블의 product, price, order_date 데이터를 함께 조회.
 
 

2) LEFT JOIN

고객 정보와 주문 내역을 모두 가져오기
--> customer_id 를 기준으로 customers 테이블(FROM절 - left)에 orders 테이블(JOIN절 - right)을 조인하여 customers 테이블의 name, email과 orders 테이블의 product, price, order_date 데이터를 조회. 이때 기준이 되는 customer 테이블의 모든 행이 출력되고 order 테이블에 일치하는 데이터는 함께 출력, 없으면 NULL로 표시.
 
 

3) RIGHT JOIN

주문 내역이 있는 고객만 조회
--> customer_id 를 기준으로 orders 테이블(JOIN절 - right)에 customers 테이블(FROM절 - left)을 조인하여 customers 테이블의 name, email과 orders 테이블의 product, price, order_date 데이터를 조회. 이때 기준이 되는 order 테이블의 모든 행이 출력되고 customer 테이블에 일치하는 데이터는 함께 출력, 없으면 NULL로 표시.
 
 

4) UNION (FULL OUTER JOIN)

모든 고객과 주문 정보 조회
--> LEFT 함수와 RIGHT 함수를 UNION으로 연결해 customers 테이블의 name, email과 orders 테이블의 product, price, order_date 데이터의 모든 행을 조회. 이때 기준이 되는 테이블에 일치하는 데이터는 함께 출력, 없으면 NULL로 표시.
 
예시>>

 

 

  • UNION ALL : 중복 데이터 포함

 
 
마케팅 예시>> A/B 테스트 그룹 데이터 통합

 
 
 

연습문제

CREATE TABLE student (
	student_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(50),
	grade VARCHAR(50),
	class_id INT,
	FOREIGN KEY (class_id) REFERENCES class(class_id) ON DELETE CASCADE
);

INSERT INTO student (name, grade, class_id) VALUES
('김민수', '1학년', 1),
('이지은', '2학년', 2),
('박철수', '3학년', 1),
('최영희', '1학년', 3),
('정우성', '2학년', NULL);

CREATE TABLE class (
	class_id INT AUTO_INCREMENT PRIMARY KEY,
	subject VARCHAR(50),
	teacher VARCHAR(50)
);

INSERT INTO class (subject, teacher) VALUES
('수학', '이승기'),
('과학', '한지민'),
('영어', '김태희');

 
1. 학생과 수업 정보를 결합하여 각 학생이 듣는 수업의 과목명과 담당 교사를 조회하세요.

 
2. 모든 학생을 포함하여 수업 정보를 조회하고, 수업을 듣지 않는 학생도 포함되도록 하세요.

 
3. 모든 수업을 포함하여 학생과 연결하고, 해당 수업을 듣는 학생이 없으면 NULL 값을 표시하세요.

class 테이블에 유재석 티처의 음악 클래스 추가함

 
 

2. 그로스 마케팅 예제

1. 고객 정보와 구매 데이터를 JOIN하여 VIP 고객 분석

 
* 별칭(Alias) : FROM 절에서 테이블 이름 or 서브 쿼리 뒤에 정의
ex) FROM customers c / JOIN ( .. ) p
 
--> 테이블 customers와 purchases의 데이터를 customer_id를 기준으로 통합 조회. 이때 purchases 테이블의 구매 금액 데이터의 총합(total_spent)을 customer_id별로 묶어 컬럼 추가. 총 구매 금액이 10,000원이 넘는 고객을 sort하여 VIP 고객으로 선별.
 
 

2. 이메일 캠페인 반응과 구매 데이터 연결

 

 
--> 테이블 email_campaign과 customers 의 데이터를 customer_id를 기준으로 통합한 테이블(LEFT)에 테이블 purchases를 마찬가지로 customer_id를 기준으로 조인. 이때 email_campaign의 clicks 데이터가 5를 초과하는 행만  sort하여 캠페인 성과 분석.
 
 

3. 유입 채널별 전환율 분석

 
--> 테이블 user_trcking(LEFT)에 테이블 purchases를 user_id(user_tracking)과 customer_id(purchases)를 기준으로 조인. 이때 user_tracking 테이블의 user_id의 수를 중복 없이 센 값을 visitors로,  purchases 테이블의 customers의 수를 중복 없이 센 값을 buyers로 명명하여 조회할 컬럼 추가. 같은 channel의 데이터는 묶어 출력하도록 하여 각 유입 채널의 전환율 분석.
 
 

4. 장바구니 이탈 고객 찾기

 

 
--> 먼저 테이블 customers(별칭 c) 전체 데이터와 테이블 cart_items의 customer_id와 cart_total(price와 quantity를 곱한 값의 총합)을 같은 customer_id 별로 묶은 테이블(별칭 b)을 각 테이블의 customer_id를 기준으로 조인. 이 데이터(LEFT)에 테이블 purchses(별칭 p, RIGHT)를 마찬가지로 각 테이블의 customer_id를 기준으로 조인하고, 이때  p의 customer_id 값이 NULL인 것만 sort하여 장바구니에 상품을 담았지만 구매하지 않은 고객을 식별하여 리마케팅 대상 선정.
 
 

5. 마케팅 비용 대비 매출 분석

 

 
--> 테이블 marketing_spend (별칭 m, left)에 테이블 purchases (별칭 p, right)를 각 테이블의 campaign_id를 기준으로 조인하여 marketing_spend의 campaign_name, ad_spend 데이터(각각 같은 데이터끼리 그룹화)와  purchases의 amount의 총합을 total_revenue로 컬럼 추가하여 조회.
 
 

6. 이메일과 SNS 캠페인 반응 데이터 통합

 
--> 테이블 email_campaign의 customer_id, campaign_name 데이터와 'Email' 문자열을 channel이라는 새로 추가한 컬럼으로 구성된 테이블과 테이블 sns_campaign의 customer_id, campaign_name 데이터와 'SNS' 문자열을 channel이라는 새로 추가한 컬럼으로 구성된 테이블을 UNION으로 통합하여 이메일과 SNS 캠페인의 성과 데이터를 하나의 테이블로 통합하여 비교 분석.
 
 

7. 여러 국가에서의 마케팅 성과 통합

 
--> 테이블 marketing_korea, marketing_usa, marketing_europe 각각의 region, campaign_name, revenue 데이터를 UNION으로 통합 조회하여 국가별 마케팅 성과 및 전략을 평가. *이때 UNION으로 통합되려면 컬럼명과 컬럼수가 같아야 함.
 
 

8. 웹사이트, 모바일 앱, 오프라인 구매 데이터를 통합

 
--> 테이블 web_purchases, app_purchases, offline_purchases 각각의 purchase_amount와 각각 'Web', "App', 'Offline'이라는 문자열로 구성된 source라는 새로운 컬럼을 추가하여 UNION을 활용한 데이터 통합 조회로 고객별 구매 경로 분석.
 
 

9. A/B 테스트 그룹별 성과 비교

 
--> 테이블 ab_test_group_a, ab_test_group_b의 user_id, conversion 데이터와 각각 'Group A', 'Group B'라는 문자열을 test_group이라는 새로운 컬럼으로 추가하여 UNION을 활용해 A/B 테스트 그룹 데이터를 하나의 테이블로 결합하여 비교 분석.
 
 

10. 여러 마케팅 채널별 ROI 통합

 
--> 테이블 google_ads, facebook_ads, tiktok_ads 각각의 channel, ad_spend, revenue 데이터를 UNION으로 통합 조회하여 마케팅 채널별 ROI 종합 평가 분석.
 
 

 

안 밀리고 그날그날 아카이빙하시는 분들 대단하다
난 영원히 밀리는 포스팅을 할 듯
정말 영원히