본문 바로가기

분류 전체보기339

[PostgreSQL] 히스토그램용 데이터 가공(width_bucket) 히스토그램 차트를 위한 데이터 전처리 상품 가격대별로 히스토그램을 그려하기 때문에, 가격의 상한/하한 값을 구하고 그 차이를 계산한다. WITH stats AS ( SELECT max(price) AS max_price , min(price) AS min_price , max(price) - min(price) AS price_range , 10 AS bucket_num FROM purchase_detail_log ) SELECT * FROM stats ; 2. 상한값에 + 1 을 하여 최댓값도 구간에 포함되도록 한다. 3. 각 가격에서 min_price를 뺀 값을 Diff로 구한다. 4. 계층 범위는 가격 구간을 구간 개수로 나누어 구한다. 5. (diff / 계층 범위) 결과를 내림하면 값의 상대적인.. 2024. 1. 27.
[PostgreSQL] 팬 차트로 매출 증가율 확인(FIRST VALUE 함수) 팬차트란? 팬차트 : 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 하는 그래프이다. 계절 변동이 적은 평균적인 달을 기준으로 하는 것이 좋다. 기준 달에 따라 결과가 달라지므로 근거를 잘 생각하고 작성한다. 팬차트 작성을 위해 필요한 데이터를 가공해보자. 데이터가 다음과 같이 정리되어야 팬차트를 만들 수 있다. 먼저 날짜에서 연/월/일을 추출하고 일자별 매출을 합산한다. 일자별 매출을 토대로, 연/월을 기준으로 카테고리별 매출을 다시 합산한다. 윈도우 함수로, FIRST VALUE를 사용하여 기준 연/월, 카테고리별 매출 값을 구한다. 기준점 대비 비율을 구하기 위해 100 * 연/월 매출 / 기준점 매출을 계산한다. 마지막으로, 연/월과 카테고리로 order by 한다. WI.. 2024. 1. 27.
[Pandas] RFM Segmentation 분석(feat. K-Means Clustering) 코호트 분석과 동일한 데이터로 이어서 RFM 세그먼트를 분석해본다. (RFM에 대한 개념과 예시는 별도 포스팅으로 게시할 예정입니다) RFM을 구하기 위한 전처리 먼저 총 구매금액 컬럼부터 생성해준다. 상품 개당 가격과 판매량을 곱해준다. # 총 구매금액 컬럼 생성 df['TotalSum'] = df['UnitPrice'] * df['Quantity'] 송장번호 최솟값과 최댓값(가장 오래전 주문 건과 최근 주문 건의 날짜)을 출력해본다. 대략적인 시간 범위를 파악한다. print('Min Invoice Date: ', df.InvoiceDate.dt.date.min(), 'max Invoice Date: ', df.InvoiceDate.dt.date.max(), '\n') df.head(3) 보통 당일 .. 2024. 1. 27.
[Pandas] Cohort Analysis(2) : 고객 유지율 구하기 #1에서 도출한 코호트로 고객 유지율(Retention Rate)부터 살펴보자. Retention Rate Table(고객 유지율) cohort_counts 테이블에서 0번째 인덱스는 해당 월에 유입된 고객 수를 뜻한다. # 각 코호트의 초기 고객 수 cohort_size = cohort_counts.iloc[:, 0] cohort_size cohort_counts 테이블을 위에서 구한 cohort_size로 divide()하면 리텐션 비율이 구해진다. 이때 axis = 0 으로 하여 모든 행에 대해서 월별 리텐션 비율을 구한다. # 각 월별 고객의 유지율 retention = cohort_counts.divide(cohort_size, axis=0) #axis 열방향 retention.round(3) .. 2024. 1. 26.
[PostgreSQL] 카테고리별 매출 총계와 소계 / ABC 분석 매출에 시계열뿐만 아니라 상품의 카테고리, 가격 등을 조합해서 데이터의 특징을 추출해 리포팅한다. 1. 카테고리별 매출(총계)과 소계 계산하기 🎯 대분류(카테고리) > 소분류(하위 카테고리) > 매출 순으로 계산되게 한다. 🎯 작은 단위로 집계한 후 >> UNION ALL 구문을 사용 WITH sub_category_amount AS ( -- 소 카테고리 매출 집계 SELECT category AS category , sub_category AS sub_category , SUM(price) AS amount FROM purchase_detail_log GROUP BY category , sub_category ), -- 카테고리 매출 집계 category_amount AS ( SELECT categor.. 2024. 1. 26.
[Pandas] Cohort Analysis(1) 코호트 분석 시작하기 라이브러리와 데이터 임포트 import numpy as np import pandas as pd import datetime as dt import matplotlib.pyplot as plt import seaborn as sns from sklearn.preprocessing import StandardScaler from sklearn.cluster import KMeans import os df = pd.read_excel('Online Retail.xlsx') 데이터 확인 df.head() df.info() df.isnull().sum() 결측치 제거 Description 과, CustomerID에 결측지 있음. 확인 필요! df = df.dropna(subset=['CustomerID']) d.. 2024. 1. 26.
코호트 분석이란? 코호트 분석이란? 동일한 기간동안 동일한 특성을 가진 사람들을 모아 분석하는 것으로, 일종의 '행동 분석'이다. 쉽게, 날짜에 기반한 사용자 세그먼트라고도 이해할 수 있다.(세그먼트와의 비교는 하단에 설명) 코호트를 통해 분석할 수 있는 데이터 전환률(converstion rates) 리텐션률(retention rates) 이용자의 목표 달성률(Goal completions per user) 이용자의 수익률(Revenue per user) 이용자 당 세션(Sessions per user) 이용자 당 페이지 뷰 수(Page views per user) 이용자 당 세션 기간(session duration per user) ; 얼마나 오래 머물러 있는지 이용자 당 전환률(transactions per user.. 2024. 1. 26.
[PostgreSQL] 매출을 파악하기 위한 데이터 추출2 (종합 쿼리) ▶︎ 연/월별로 다음 데이터를 뽑는 종합 쿼리 판매 횟수 평균 구매액 매출액 누계 매출액 작년 (동월) 매출액 작년 (동월) 비율 with daily_purchase as ( select dt , substring(dt, 1, 4) as year , substring(dt, 6, 2) as month , substring(dt, 9, 2) as date , sum(purchase_amount) as purchase_amount , count(order_id) as orders from purchase_log group by 1 ), monthly_purchase as( select year , month , sum(orders) as orders , avg(purchase_amount) as avg_am.. 2024. 1. 26.
[PostgreSQL] 매출을 파악하기 위한 데이터 추출1 데이터 확인 1. 날짜별 매출 집계 시간의 흐름에 따라 일자별로 총 매출, 구매 횟수, 평균 매출을 구한다 SELECT dt , COUNT(*) AS purchase_cnt , SUM(purchase_amount) AS total_amount , AVG(purchase_amount) as avg_amount FROM purchase_log group by dt order by dt 2. 이동평균을 사용한 날짜별 추이 날짜별 매출을 위에서 구했는데, 이를 시각화한 결과에 주기성이 나타난다면 이동평균을 확인해본다. 날짜별 매출과 7일 이동평균을 집계해본다. 이때 날짜별로 변동을 파악하는 것도 중요하므로 날짜별 추이와 이동평균을 함께 표현하는 것이 좋다. seven_day_avg : 단순히 현재 날짜로부터 7.. 2024. 1. 26.
728x90

티스토리툴바