카테고리 없음
[DAY7] 멋쟁이사자처럼부트캠프 그로스마케팅_Today I Learned
hachaenii
2025. 2. 11. 21:49
오늘의 학습 내용
- 테이블 생성 및 데이터 삽입
- 데이터 수정 및 삭제
테이블 생성, 수정, 삭제 관련 이론
1. 테이블 생성 CREATE TABLE
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY, -- 자동 증가하는 기본 키
name VARCHAR(100) NOT NULL, -- 이름 (필수 입력)
position VARCHAR(50) NOT NULL, -- 직급
salary DECIMAL(10,2) NOT NULL, -- 급여 (소수점 2자리)
hire_date DATE DEFAULT (CURRENT_DATE) -- 입사일 (기본값: 현재 날짜)
);2. 데이터 삽입 INSERT INTO
2.1 1개 데이터 삽입
INSERT INTO employees (name, position, salary)
VALUES ('홍길동', '개발자', 5000000);2.2 여러 개 데이터 삽입
INSERT INTO employees (name, position, salary)
VALUES
('김철수', '디자이너', 4500000),
('이영희', '마케팅', 4800000),
('박민수', '개발자', 5200000),
('최수영', 'HR 매니저', 4700000);3. 데이터 조회 SELECT
3.1 모든 데이터 조회
SELECT * FROM employees;3.2 특정 조건으로 데이터 조회
SELECT * FROM employees WHERE position = '개발자';4. 데이터 수정 UPDATE
4.1 특정 직원의 급여 수정
UPDATE employees
SET salary = 5500000
WHERE name = '홍길동';4.2 여러 개 컬럼 동시에 수정
UPDATE employees
SET position = '시니어 개발자', salary = 6000000
WHERE name = '박민수';4.3 특정 조건을 만족하는 모든 행 업데이트
UPDATE employees
SET salary = salary * 1.1 -- 모든 직원의 급여를 10% 인상
WHERE position = '디자이너';5. 데이터 삭제 DELETE
DELETE FROM employees
WHERE name = '김철수';6. 테이블 전체 데이터 삭제 TRUNCATE
TRUNCATE TABLE employees;
# TRUNCATE는 모든 데이터를 삭제하지만, 테이블 구조는 유지7. 테이블 삭제 DROP TABLE
DROP TABLE employees;
# DROP TABLE을 실행하면 테이블 자체가 삭제데이터 베이스 활용 실습
1. 사용자 행동 분석 (user_behavior1)
# 테이블 생성
CREATE TABLE user_behavior1 (
user_id INT AUTO_INCREMENT PRIMARY KEY, -- 사용자 ID (기본 키)
session_id VARCHAR(50) NOT NULL, -- 세션 ID
page_viewed VARCHAR(100) NOT NULL, -- 방문한 페이지
time_spent INT NOT NULL, -- 페이지에서 머문 시간 (초)
device VARCHAR(50) NOT NULL, -- 사용 기기 (모바일, 데스크탑 등)
referrer VARCHAR(100) NOT NULL, -- 유입 경로 (검색, SNS 등)
purchase INT NOT NULL DEFAULT 0, -- 구매 여부 (0: 미구매, 1: 구매)
visit_date DATETIME NOT NULL -- 방문 시간
);
#데이터 삽입
INSERT INTO user_behavior1 (session_id, page_viewed, time_spent, device, referrer, purchase, visit_date) VALUES
('S001', '홈페이지', 120, '모바일', 'Google Search', 0, '2024-01-01 10:30:00'),
('S002', '상품 상세', 300, '데스크탑', 'Facebook', 1, '2024-01-02 14:20:00'),
('S003', '결제 페이지', 60, '모바일', 'Email Campaign', 1, '2024-01-03 09:45:00'),
('S004', '홈페이지', 80, '태블릿', 'Direct', 0, '2024-01-03 18:00:00'),
('S005', '상품 상세', 240, '모바일', 'Google Search', 1, '2024-01-04 11:15:00');1.1 사용자별 평균 페이지 체류 시간 조회
SELECT user_id, AVG(time_spent) AS avg_time_spent
FROM user_behavior1
GROUP BY user_id;1.2 기기별 구매 전환율 분석
# 전환율(%) = (구매 사용자 수 / 총 방문 수) * 100
SELECT device,
(SUM(purchase) / COUNT(*)) * 100 AS conversion_rate
FROM user_behavior1
GROUP BY device
ORDER BY conversion_rate DESC;1.3 특정 페이지에서 가장 오래 머문 사용자 찾기
SELECT user_id, page_viewed, MAX(time_spent) AS max_time_spent
FROM user_behavior1
GROUP BY user_id, page_viewed
ORDER BY max_time_spent DESC
LIMIT 1;1.4 유입 경로별 평균 세션 지속 시간 분석
SELECT referrer, AVG(time_spent) AS avg_session_duration
FROM user_behavior1
GROUP BY referrer
ORDER BY avg_session_duration DESC;2. 이메일 캠페인 성과 분석 (email_campaigns1)
# 테이블 생성
CREATE TABLE email_campaigns1 (
campaign_id INT AUTO_INCREMENT PRIMARY KEY,
subject VARCHAR(150) NOT NULL, -- 이메일 제목
sent INT NOT NULL, -- 발송 수
opened INT NOT NULL, -- 열람 수
clicked INT NOT NULL, -- 클릭 수
conversions INT NOT NULL, -- 전환 수
send_date DATE NOT NULL
);
# 데이터 삽입
INSERT INTO email_campaigns1 (subject, sent, opened, clicked, conversions, send_date) VALUES
('봄맞이 할인 이벤트', 10000, 4500, 1200, 300, '2024-03-01'),
('VIP 전용 한정 할인', 8000, 5000, 1800, 700, '2024-04-10'),
('신제품 런칭 특별 이벤트', 12000, 7000, 2500, 900, '2024-05-15'),
('무료 샘플 신청', 9000, 6200, 1900, 850, '2024-06-01'),
('회원 감사 쿠폰 증정', 7500, 5200, 1600, 600, '2024-07-20');2.1 이메일 캠페인별 오픈율, 클릭율, 전환율 분석
SELECT subject,
(opened / sent) * 100 AS open_rate,
(clicked / sent) * 100 AS click_rate,
(conversions / sent) * 100 AS conversion_rate
FROM email_campaigns1
ORDER BY conversion_rate DESC;2.2 가장 효과적인 이메일 캠페인 조회
SELECT subject, conversions
FROM email_campaigns1
ORDER BY conversions DESC
LIMIT 1;2.3 특정 기간(예: 6개월) 동안 캠페인 성과 조회
SELECT * FROM email_campaigns1
WHERE send_date >= DATE_SUB('2024-08-01', INTERVAL 6 MONTH);3. 광고 ROI 분석 (ad_performance)
# 테이블 생성
CREATE TABLE ad_performance (
ad_id INT AUTO_INCREMENT PRIMARY KEY,
platform VARCHAR(50) NOT NULL, -- 광고 플랫폼 (Google, Facebook 등)
budget DECIMAL(10,2) NOT NULL, -- 광고 예산
impressions INT NOT NULL, -- 노출 수
clicks INT NOT NULL, -- 클릭 수
conversions INT NOT NULL, -- 전환 수
revenue DECIMAL(15,2) NOT NULL, -- 매출액
start_date DATE NOT NULL
);
# 데이터 삽입
INSERT INTO ad_performance (platform, budget, impressions, clicks, conversions, revenue, start_date) VALUES
('Google Ads', 5000000, 200000, 15000, 3000, 150000000, '2024-01-01'),
('Facebook Ads', 6000000, 250000, 18000, 4000, 180000000, '2024-02-01'),
('Instagram Ads', 5500000, 220000, 17000, 3500, 140000000, '2024-03-01'),
('YouTube Ads', 7000000, 300000, 22000, 5000, 200000000, '2024-04-01'),
('TikTok Ads', 4000000, 180000, 14000, 2800, 120000000, '2024-05-01');3.1 광고별 ROI(Return on Investment) 분석
# ROI(%) = ((매출액 - 예산) / 예산) * 100
SELECT platform,
((revenue - budget) / budget) * 100 AS ROI
FROM ad_performance
ORDER BY ROI DESC;3.2 광고 플랫폼별 평균 클릭당 비용 (CPC) 계산
#CPC = 예산 / 클릭 수
SELECT platform, (budget / clicks) AS CPC
FROM ad_performance
ORDER BY CPC ASC;3.3 가장 효과적인 광고 플랫폼 분석 (전환당 비용 기준)
# CPA (Cost Per Acquisition) = 예산 / 전환 수
SELECT platform, (budget / conversions) AS CPA
FROM ad_performance
ORDER BY CPA ASC
LIMIT 1;4. REVERSE ) 테이블을 보고 SQL구현하기
4.1 광고 효율성 비교 (CPC, CPA 분석)
각 광고 캠페인의 CPC(Cost Per Click)와 CPA(Cost Per Acquisition)를 계산하고,
CPC가 가장 낮은 광고 캠페인을 찾는 SQL 쿼리를 작성하세요.
- CPC 계산: budget / clicks
- CPA 계산: budget / conversions
# 테이블 생성
CREATE TABLE ad_cost_analysis3 (
campaign_name VARCHAR(50) NOT NULL,
budget DECIMAL(10,2) NOT NULL,
clicks INT NOT NULL,
conversions INT NOT NULL
);
# 데이터 삽입
INSERT INTO ad_cost_analysis3 (campaign_name, budget, clicks, conversions) VALUES
('Google Ads', 8500000, 19000, 4200),
('Facebook Ads', 8000000, 18500, 3900),
('Instagram Ads', 7800000, 17000, 3800),
('TikTok Ads', 6000000, 14000, 3200),
('LinkedIn Ads', 5500000, 9000, 2000),
('Twitter Ads', 5000000, 11000, 2500),
('YouTube Ads', 12000000, 25000, 5000),
('Bing Ads', 4800000, 8000, 1800),
('Reddit Ads', 4000000, 7500, 1700);# 문제해결
SELECT campaign_name,
(budget / clicks) AS CPC,
(budget / conversions) AS CPA
FROM ad_cost_analysis3
ORDER BY CPC ASC
LIMIT 1;4.2 광고 효율성 비교 (CPC, CPA 분석)
- 전환율 계산: (conversions / clicks) * 100)
# 테이블 생성
CREATE TABLE ad_performance4 (
ad_id INT AUTO_INCREMENT PRIMARY KEY,
channel VARCHAR(50) NOT NULL,
budget DECIMAL(10,2) NOT NULL,
impressions INT NOT NULL,
clicks INT NOT NULL,
conversions INT NOT NULL,
start_date DATE NOT NULL
);
# 데이터 삽입
INSERT INTO ad_performance4 (channel, budget, impressions, clicks, conversions, start_date) VALUES
('Google Ads', 8000000, 300000, 20000, 4000, '2024-06-01'),
('Facebook Ads', 7000000, 250000, 18000, 3500, '2024-07-01'),
('Instagram Ads', 6500000, 230000, 17000, 3400, '2024-08-01');# 문제 해결
ELECT channel, (conversions / clicks) * 100 AS conversion_rate
FROM ad_performance4
GROUP BY channel
ORDER BY conversion_rate DESC
LIMIT 1;
실습 중 강사님께서 주신 마케팅 시나리오 살펴보기
"e커머스 스타트업 A사는 마케팅 데이터를 활용하여 매출을 극대화하고 광고 효율성을 높이기 위해 고도화된 데이터 분석 시스템을 구축하려 한다. 광고 비용 대비 전환율 최적화, 사용자 리텐션 분석, 마케팅 채널별 ROI 비교, 고객 생애 가치(LTV) 예측, 캠페인 자동 최적화, A/B 테스트 결과 비교, 퍼널 분석, 추천 시스템 최적화, 구독 모델 분석 등의 전략을 적용하려 한다."
강사님께서 마케팅 용어에 익숙해 지라고 하시며 교안에 넣어주신 시나리오..!
오늘 나온 마케팅 용어들을 정리해보려고한다.
전환율( Conversion Rate ) 전환율(%) = (전환 수 / 클릭 수) * 100 클릭률(CTR) 클릭률(%) = (클릭 수 / 노출 수) * 100 ...... A/B 테스트 분석 클릭당 비용(CPC) 클릭당 비용 = 예산 / 클릭 수 ROI
(Return on Investment)ROI(%) = ((매출액 - 예산) / 예산) * 100, ROI = ((매출 - 광고비) / 광고비) × 100 전환당 비용(CPA) 전환당 비용 = 예산 / 전환 수, CPA = 광고비 / 전환 수 고객 생애 가치(LTV) LTV = 평균 구매 금액 × 구매 빈도 × 평균 고객 유지 기간 각 단계별 이탈률 각 단계별 이탈률 = (이전 단계 방문자 - 현재 단계 방문자) / 이전 단계 방문자 × 100 사용자 리텐션 분석 Retention Rate = (잔존 고객 수 / 최초 가입 고객 수) × 100
끝으로 기억해야 할 개념이라고 하셔서 아주 작게 필기한
최적화와 분석 내용 넣어봅니닷
분석이 되어야 최적화가 가능하다는 것
" 오늘의 회고 "
오늘은 sql 테이블을 다루는데 익숙해 질 때까지 끊임없이 같은 작업을 반복하는 인내의 시간이었다..!
그런데 지루하게 느껴지지 않고 재미있었던 것 같다
현업 데이터나 더 많은 요구사항이 포함된 문제도 풀 수 있을까 싶지만..
강사님께서 데이터는 반복해서 익숙해지는 것이 가장 중요하다고 말씀하셨는데 오늘 수업을 들으며 무슨 말씀이신지 알것 같았다. 이번주 남은 날들도 다들 화이팅..!!
