데이터 분석 트랙 8일차(25.02.26.) TIL

카테고리 없음

데이터 분석 트랙 8일차(25.02.26.) TIL

jjaio8986 2025. 2. 26. 22:15

4회차 SQL 강의 및 개인 공부 정리
팀 발제 자료 취합 및 인사이트 정리

4회차 SQL 강의 및 개인 공부 정리

1. 강의내용 정리

- "UNION"함수 : 테이블 끼리의 수직 결합으로 반드시 "열(컬럼)"의 '이름', '조합순서', '결합 개수'가 모두 동일해야 한다!

- 기본적인 형태

SELECT
FROM

(

select 컬럼1, 컬럼2, 컬럼3..

from 테이블명1

union (all)

select 컬럼1, 컬럼2, 컬럼3..

from 테이블명2

)

- 종류 : "union" : 수직으로 중복되는 데이터를 제거해 하나의 결과값만을 아웃풋으로 조회!

"union all" :수직으로 모든(중복 무관) 데이터를 하나의 테이블로 모아준다.

- 만일 컬럼 내용은 같은데 이름은 다르다면 이름을 별칭부여로 동일하게 하여 컬럼을 맞춰줘야 한다!

- 2개 이상의 테이블도 결합이 가능하다.

- 컬럼 내 데이터가 숫자형, 텍스트형인지에 따라 cast를 통해 타입을 바꿔주나 일반적으로 동일하다

- union의 위, 아래 구문엔 반드시 형태를 맞추고 가급적 where같은 조건을 쓰지 않기!

단, where의 경우 결합이 끝난 다음에 필터링으로 사용이 가능하다.

- order by의 경우 union결합이 끝나야 사용 가능하다 즉, union 위에 사용하면 오류가 생긴다.

- "JOIN" 함수 : 테이블을 공통 컬럼을 기준으로 수평으로 결합한다.

- "JOIN"의 사용 단계

1단계 : 공통 컬럼 찾기 > 공통컬럼이 1개 이상일 수 있으나 아무것도 없다면 "JOIN"을 사용할 수 없다. 또한 "JOIN" 뒤에 "ON"으로 묶이는 공통컬럼을 2개 이상 줄 수 있다. [공통컬럼으로 조건을 준다고 이해!]

중요한 점은 "JOIN" 으로 묶은 공통컬럼은 SELECT절 뒤에 꼭 적어줘야 한다.

2단계 : PK(테이블의 유일한 기준 컬럼)와 FK(테이블 내 종속된 컬럼)찾기 > 기준 컬럼이란 그 행의 값이 NULL값이 들어가지 않은 테이블의 기둥과 같은 대상이다. 이와 관련한 'ERD 관계도'가 있다. 그러나 '관계도의 제작'은 데이터 사이언티스트와 엔지니어의 주된 롤이기에, 데이터 분석가는 관계도 해석 정도에 집중하면 좋다.

3단계 : 조인의 종류 결정 > MYSQL에는 다양한 종류의 JOIN이 있으나 대게 LEFT JOIN, (INNER) JOIN이 사용된다.

주의! 서브쿼리를 묶어주고자 할 때, 묶고자 하는 서브쿼리의 이름을 반드시 기입해줘야 한다!

1.2. 공부내용 정리

- UNION ALL 과 JOIN은 같이 쓸 수 있다. 단, 유니온은 컬럼의 개수가 같아져야 하기에 작업이 복잡해진다.

- 공통컬럼은 묶어주기 위한 ‘공통 값’이기 때문에 두 테이블의 컬럼명은 달라도 괜찮다.

- join 문을 쓸 때 조회할 컬럼을 지정해야 할 경우 테이블의 이름을 적은뒤 컬럼을 지정해야 조회할 수 있다.

- join을 통해 테이블들을 합친 뒤 컬럼끼리의 연산을 진행할 시 테이블 명을 꼭 쓰고 컬럼명을 써서 연산식을 적기

ex) a.컬럼명*b.컬럼명

- 서브쿼리문에서 join이 끝나고 본 쿼리문으로 옮기면 굳이 최초의 테이블명을 적을 필요 없이 컬럼명을 적으면 된다.

2. 팀 발제 자료 취합 및 인사이트 정리

- 팀 단위 발제 목표 : 제공된 주제를 선택하여 SQL을 활용해 EDA를 진행한다.

- 발표 주제(분석 대상) : [이커머스] 이커머스 이벤트 히스토리

- 발표 흐름 : 팀의 주제의 선정이유를 설명하고, 도메인에 대한 간략한 설명을 언급, 주제에서 제시된 데이터의 특성을 확인, 특성을 통해 활용을 위한 분석 보다 분석 자체에 집중해야함을 인지했고, 분석을 시도하고 그 결과를 보여주면서 인사이트를 언급한다.

발표 목차

1. 이커머스와 핵심 분석 지표

2. 발제 주제 데이터 특성

2.1. 테이블, 컬럼의 특성

2.2. 특성을 통한 분석 방향

3. SQL 분석과 인사이트들

4. 제시 가능한 해석과 발표 마무리

5. 참고자료

1. 이커머스와 핵심 분석 지표

1.1. 이커머스(e-commerce)

a. 정의 : “전자상거래(電子商去來)”, “전자적 매체(시스템)를 이용해 가상공간에서 이루어지는 거래행위”

b. 특성 : 오픈마켓이나 소셜커머스, 드물게 전자우편을 사용한 거래이다.

- 거래의 각 과정에서 소비자들이 보여주는 행동 패턴이 있다. (방문, 상품조회, 장바구니 넣기, 장바구니 빼기, 구매, 반품 및 교환, 등등)

1.2. 핵심 분석 지표

a. 이커머스의 분석 목표 : 거래에서 소비자의 행동 패턴을 분석하는 이유는 이를 통해 제품을 많이 팔기 위함이며, 최종적으로 매출 증대, 이윤추구가 목표다! 그를 확인하기 위한 여러 가지 주요지표가 있다.

b. 이커머스 핵심 지표

- 종류 : ‘세션수’, ‘구매 전환율’, ‘주문 단가(물건 단가, 가격)’, ‘구매 포기율’, ‘연간 구입 건수’, ‘고객 확보 비용’, ‘고객 당 매출’, 등등

- 다양한 구성요소가 있지만 다양한 활용은 현 단계에선 무리이기에 매출액을 간단히 분석 시도를 하고자 한다. 이때, 핵심 분석 지표는 다음과 같다.

- 매출액 = 방문자수(세션수)*실제 구매로 넘아간 세션 비중(구매전환율)*구매 전환된 건들의 평균 금액(주문단가)

ex. 세션수(10만), 구매전환율(10%), 물건 단가(50,000원)

"매출액" = 10만*10%*5만원 = 25억원

※ 즉, 우리는 이커머스 데이터를 분석하기 위한 핵심 지표를 방문자수, 구매전환율, 주문단가로 설정해 이를 위한 분석을 시도하고자 했다.

2. 발제 주제의 데이터 특성

2.1. 데이터 설명

- "[이커머스] 이커머스 이벤트 히스토리"에 등장하는 데이터는 중형 화장품 온라인 스토어의 5개월(2019.10.~2020.02.)의 행동데이터이다. 이커머스의 특성상 네트워크상에서 이뤄지는 행위이기에 소비자들의 행동 패턴을 쉽게 수치화 할 수 있다. 가령 플랫폼에서 해당 상품의 확인 후, 장바구니에 상품을 추가하거나, 삭제하거나 그것을 반복하다 최종적으로 구매를 하거나 안한다. 이를 수치화 및 행동 패턴의 분석을 시도해 볼 순 있다.

2.2. 데이터 특성

- 데이터에서 테이블은 “2019-10”, “2019-11”, “2019-12”, “2020-01”, “2020-02”로 총 5개의 테이블로, 각 컬럼은 총 9개로 구분되고 동일한 컬럼명, 유사한 내용(컬럼별 카테고리 코드 및 카테고리 아이디의 사용이 동일하지 않음.)으로 구성되어있다.

- 컬럼은 다음의 특징을 가진다.

컬럼명	특이사항
event_time (이벤트 시간)	이벤트가 발생한 시간(UTC)으로 "YYYY-mm-dd ??:??:??"형식으로 구성되어 있다.
event_type (이벤트 유형)	총 4가지의 이벤트 유형이 있다. {view, cart, remove_from_cart, purchase}
product_id (제품ID)	제품별 ID이다.
category_code (카테고리_아이디)	카테고리_아이디 이다.
category_code (카테고리_코드)	null 값이 대부분을 차지하고 있으며 상품의 카테고리를 의미한다. ∵ 종류가 다양하여 아주 큰 대분류를 제외 대부분 공란이다. 이름이 있는 카테고리는 유의미한 의미를 갖고 있는 경우를 의미하며 대분류로 나뉠수 있는 경우 적힌다.
brand (상표)	흔히 null, ‘’값을 포함하고 있다. 소문자로 된 브랜드 이름이 있는 경우 기재되어 있다.
price (가격)	작성 시점을 기준의 가격차이가 적혀있다. 분포값은 (-79.4 ~ 328)
user_id (사용자_아이디)	영구 사용자 ID를 의미한다.
user_session (사용자 세션)	임시 사용자의 세션 ID로 각 사용자 세션에 대해 동일하다! 사용자가 긴 일시 정지에서 온라인 스토어로 돌아올 때마다 변경된다.

a. '가격' : 분석 적용 시점의 가격을 모두 정확히 알 수 없기에 가격과 관련된 인사이트는 배제한다.

b. '브랜드' : 2019_Dec 테이블 기준 253개의 종류를 갖고 있고(null값 포함), null값을 제외 시 전체 개수 중 1/3이 줄어든다는 점에서 '브랜드'를 통한 어떠한 인사이트 도출이 가능해 보인다.

c. '사용자_아이디', '사용자 세션' : ‘사용자 세션’의 변경 기간이 명확하지 않아, 사용자 구분(기존/신규)은 불가해 보이고, 유입 대비 ‘영구 사용자 전환율’ 확인만 가능해 보인다. 또한 주간, 월간 데이터 구분이 가능할 시 전환율을 통해 인사이트 도출이 가능해 보인다.

d. '이벤트 시간' : 시, 일, 주, 월 단위로 "사용자가 많이 몰리는 시간대 확인", 기간 범위를 통한 타 컬럼과의 연계가 가능해 보인다.

e. '이벤트 유형' : 사용자의 행동패턴을 파악할 수 있는 분석 핵심 컬럼으로 유의미한 분석이 가능하다.

f. '카테고리_아이디', ' 카테고리_코드' : 데이터에서 null값[여기선 ‘’으로 SQL조회 가능]이 많고, 유형 구분이 명확하지 않기에 분석이 불필요해 보인다.

g. '제품ID' : 제품별 행동패턴 파악에 사용될 수 있어 분석하고자 한다.

2.3. 데이터 특성을 통한 분석 목표

- 테이블의 유사성을 통한 UNION함수 적용이 가능하기에 분석 데이터 기준을 전체, 테이블, 컬럼으로 설정이 가능하다. 또한 분석 가능한 컬럼이 (‘브랜드’, 사용자_아이디', '사용자 세션', '이벤트 시간', '이벤트 유형', '제품ID') 총 7개로 컬럼별 혹은 컬럼을 결합한 인사이트 도출이 가능해 보인다.

- 모든 데이터에 대한 분석 및 해석은 현 교육 시점의 교육생의 역량과 물리적인 시간이 부족하기에, 해볼 수 있는 분석들을 시도하고 그에 대한 인사이트를 도출하고자 한다.

3. 팀 분석 결과 종합

3.1. 브랜드 및 제품 관련 분석

- “전체 데이터 중 브렌드별, 이벤트 유형별, 전환율 3개 제한”

- "모든 테이블 최다 판매 상품 10개의 product_id, 브랜드, 총판매량“

- "전체 데이터 중 브랜드로 구분, 이후 이벤트별 행동횟수 및 이벤트끼리 전환율“

- “테이블(월단위) 중 가장 많이 팔린 제품 top5”

- “전체 데이터에서 최다 판매 상품 3개와 같이 팔린 연관 상품 상위 3개 및 공동구매횟수”

- "앞선 최다 판매 제품 3개의 연관 상품들(7개)의 브랜드"

3.2. 이벤트 유형을 통한 세션(방문) 분석

- "주단위 이벤트 유형별 행동 횟수"

- "테이블(월) 당 이벤트 유형별 행동 횟수"

- “주차별 이벤트 행동 전환율”

- “테이블(월) 별 이벤트 행동 전환율”

- “전체(5개월간) 이벤트 유형별 행동 횟수와 행동 전환율”

4. 제시 가능한 해석과 발표 마무리

- 지금부터 편의상 전체 테이블은 5개월간, 각 테이블은 해당 월, 컬럼은 한글로 바꿔서 정리한다.

<제품 분석 결과 해석 인사이트>

- “5개월, 1개월간 가장 많이 팔린 상품아이디를 비교할 때, 전체적으로 가장 많이 팔리는 상품이 가장 좋은 상품(이윤을 많이 벌어주는)은 아닌 것으로 판단된다.” 주 단위 베스트 상품은 분석하지 못했지만 월단위의 상품 id의 순위가 2~4등은 왔다 갔다 한다. 이는, 마케팅 이벤트(블랙 프라이데이) 혹은 다른 사유로 인해 하나의 상품이 좋은 상품이 될 수도 안 될 수도 있다는 의미이다. [마케팅에 따른 제품 구매량 효과차이가 발생하게 될 수 있다.]

가령 ‘가장 많이 팔린 상품’과 ‘같이 팔린 연관 상품’을 각 이벤트가 행해지는 페이지마다 광고하여 추가적인 구매욕을 자극시키거나, 판촉 및 할인행사를 통한 판매량이 낮은 제품군의 노출 등등을 통해 판매량이 저조한 상품을 판매량이 높은 상품(좋은 상품)으로 판매량이 높은 상품은 더 많이 팔릴 수 있게 만들 수 있을 것이다.

※ 한 줄 요약 : 이윤 추구 관점에서 좋은 상품은 단순히 가장 많이 팔리는 상품이 아니라 그것이 될 가능성이 높은 상품이다!(히트 상품과 연관상품!)

<전환율 해석 인사이트> [ㄱ, ㄴ은 표를 수평적으로 결과를 해석해 봤을 때! a는 수직적으로 결과를 해석해 보았을 때!]

ㄱ) 전체 ‘방문>장바구니’의 전환율은 59.73%이다. 다만 월단위 방문을 보면 19년도에 계속 낮아지다가 20년도에 다시 상승하기 시작한다. 즉 방문에서 장바구니에 담는 횟수가 늘어났다는 뜻이며 이는 어떠한 제품에 대한 마케팅이 캠페인이 19년 10, 11월에 행해지다가 12월을 기점으로 끊겼다는 뜻이며, 이를 뒷받침하듯 ‘장바구니>제거’의 전환율이 계속 높아지다 어느 선을 기점으로 그 비율이 유지되고 있다

ㄴ) 월 당 전환율 중 가장 성과가 좋은 것은 19년 11월, 가장 성과가 저조한 것은 19년 12월 이다. 이를 통해 알 수 있는 것은 마케팅 플렌 혹은 웹사이트의 서비스 개선이 필요한 시점 및 보고서를 통한 개선의 지표가 된다는 점이다. 이 시점에 보고서를 작성하면 좋을 듯 하다.

ㄷ) 주 단위 전환율을 보면 19년도 10월 3~4주차 11월 2주, 4주차 12월 2주차, 3주차, 4주차에 20%선을 유지하고 있어 마케팅 행사가 그 즈음에 행해진 것으로 보이나 실제 수치적으로 볼 때 11월 4주차에 장바구니 담기 행동이 유의미하게 뛴다. 그를 통해 마케팅 혹은 판촉행사가 그 때 이뤄졌다 짐작해보고 있다.