<TIL> 2024-01-16

• 오늘 진행한 일
  • 팀 프로젝트 분석 진행 및 정리
  • 튜터 피드백
  • 피드백 결과 반영

---

https://jinhyunbae.tistory.com/118

<TIL> 2024-01-15

 

어제 프로젝트에 대해서 분석한 것에서 조금 더심화되어 고객의 세그먼트를 나누어 각 세그먼트 별 데이터 특성을 분석했다.  아래는 위에서 말한 분석에 사용된 코드의 일부분이다.

# 프로모션에 참여한 이력이 있는 사람의 개인정보
create or replace view CustomerInfoByCmp(ID, Age, AgeRange, Marital, KidHome, TeenHome, ChildHome, Income, AcceptedCmp) as(
	select ID,
	       Age, 
	       case when Age between 20 and 29 then '20대'
	            when Age between 30 and 39 then '30대'
	            when Age between 40 and 49 then '40대'
	            when Age between 50 and 59 then '50대'
	            when Age between 60 and 69 then '60대'
	            when Age between 70 and 79 then '70대'
	            when Age between 80 and 89 then '80대'
	            when Age between 90 and 99 then '90대'
	            else '100세이상'
	            end as AgeRange,
	       case when (Marital_Status='Married') or (Marital_Status='Together') then 'NotSingle'
		        else 'Single'
		        end Marital,
		   KidHome, TeenHome, 
		   case when ChildHome >= 1 then 'Child'
		        else 'NoChild'
		        end ChildHome,
	       Income,
	       case when AcceptedCmp > 0 then 'Accepted'
	            else 'Unaccepted'
	            end as AccetedCmp
	from
	(
		select ci.ID, 
		       (2024-ci.Year_Birth) as Age, 
		       ci.Marital_Status, ci.KidHome, ci.TeenHome,
		       ci.KidHome + ci.TeenHome as ChildHome,
		       ci.Income,
		       cr.AcceptedCmp1+cr.AcceptedCmp2+cr.AcceptedCmp3+cr.AcceptedCmp4+cr.AcceptedCmp5+cr.Response as AcceptedCmp
		from customer_info ci inner join customer_response cr on ci.ID = cr.ID
	) a
);

# Frequency_rency 테이블과 Purchase_amount 테이블 연결
create or replace view AmountNumPurchase(ID, MntFishProducts, MntMeatProducts, MntFruits, MntSweetProducts, MntWines, SumAmount, 
                                         NumDealsPurchases, NumCatalogPurchases, NumStorePurchases, NumWebPurchases, SumPurchases, 
                                         Recency, NumWebVisitsMonth) as(
	select pa.ID,
	       pa.MntFishProducts,
		   pa.MntMeatProducts,
		   pa.MntFruits,
		   pa.MntSweetProducts, 
		   pa.MntWines,
		   pa.MntFishProducts+pa.MntMeatProducts+pa.MntFruits+pa.MntSweetProducts+pa.MntWines as SumAmount,
		   fr.NumDealsPurchases,
		   fr.NumCatalogPurchases, 
		   fr.NumStorePurchases, 
		   fr.NumWebPurchases, 
		   fr.NumCatalogPurchases+fr.NumStorePurchases+fr.NumWebPurchases as SumPurchases,
		   fr.Recency,
		   fr.NumWebVisitsMonth
	from frequency_recency fr inner join purchase_amount pa on fr.ID = pa.ID
);

 

위 처럼 작성된 VIEW를 이용해 아래의 코드를 작성하였다. 

select cbc.AcceptedCmp, 
	   cbc.Marital, 
	   cbc.ChildHome,
	   count(*),
	   round(avg(anp.SumAmount)) avgSumAmount, #객단가
	   round(avg(anp.MntFishProducts)) avgMntFish,
	   round(avg(anp.MntMeatProducts)) avgMntMeat,
	   round(avg(anp.MntFruits)) avgMntFruits,
	   round(avg(anp.MntSweetProducts)) avgMntSweet,
	   round(avg(anp.MntWines)) avgMntWines
from CustomerInfoByCmp cbc inner join AmountNumPurchase anp on cbc.ID = anp.ID
group by 1, 2, 3
order by 1,2 desc, 3;

 

쿼리 실행 결과는 다음과 같은데 이는 마케팅 수락 여부, 동거인(결혼)여부, 자녀 여부 이렇게 3가지 측면에서 2개씩의 세그먼트로 나누어 총 8개의 세그먼트의 평균 총 구매량 및 평균 품목별 구매량을 구한 것이다. 

 

이처럼 각각의 세그먼트에 대한 데이터를 분석하였고, 분석한 내용을 기반으로 마케팅 대상을 결정하고,  마케팅 대상에 대한 전략도 수립하였다

 

그리고 분석한 내용 및 결과 그리고 결론에 대해 튜터님께 가져가 피드백을 받았다. 

 

피드백을 받은 내용은 아래와 같다.

• 데이터 전처리를 통해서 이상치와 결측치를 삭제조치 했는데 분석에 영향을 미치는게 아니라면 해당 전처리를 시행할 필요가 없다. 결과 보고서에서는 빼는 것이 좋겠다.
• 해당 데이터는 장바구니 데이터가 아닌 집계데이터이고 이러한 집계데이터에서 회귀분석과 상관분석은 변수가 연관이 있다는 당위성을 제공해주기 어렵다.
• 마케팅 대상인 세그먼트가 품목별 구매 금액에 관해서 다른 세그먼트와 차이가 있는지를 명확하게 보여주면 좋겠다.
• 발표 자료를 만들 때 두괄식으로 만들길 바란다.
• 시간이 더 있다면 세그멘테이션 말고 클러스터링(군집분석)으로 데이터를 다뤄보면 좋을 것이라는 생각이 든다.

이제 내일은 분석한 내용을 바탕으로 PPT를 작성하고 발표준비 및 영상촬영이 있는 날이다. 

발표자료까지 마무리 되면 해당 프로젝트 내용은 블로그에 상세히 남길 것이다.