240116화_TIL

재구매에 관해.. 유의한 결과 썩. 계속해서 요리조리 분석. 

그 외의 지표. 온라인 의류?커머스의 경우 rfm. 고객 클러스터링 등 많이 쓴다고 하십니다.

또한 의류 쇼핑몰의 경우 top 몇 프로의 고객들이 대부분의 매출을 이끌기도 한다고. (백화점 생각하면 그런 것 같기도 합니다.)

 

또 팀원분이 알려주신 태블로. 색깔 좀 예쁘게 나오더랍니다.

 

알록 달록. 좋습니다.

 

주문 상위 10%고객의 구매 상품의 상위 10% / 말이 이상한데 할튼 이러한 방식으로 고객/상품 세분화를 하는 거구나. 배웠습니다.

뷰테이블 아직 익숙케 쓰지는 못하지만, 더 갈고 닦읍시다. 일단 빨리 결과물 내놓으려고 더럽게 작성했는데 프로젝트 후에 복기 및 수정하는 시간을 가지도록. 

 

SELECT age_gender_category, sum(re_order)
from
(SELECT  distinct(o.customer_id), 
case when count(o.order_id)over(PARTITION by o.customer_id)=1 then 0
	else 1
	end as re_order,
case when c.age between 19 and 29 and c.gender='female' then '20female'
when c.age between 19 and 29 and c.gender='male' then '20male'
when c.age  BETWEEN 30 and 39 and c.gender ='female' then '30female'
when c.age  BETWEEN 30 and 39 and c.gender ='male' then '30male'
when c.age between 40 and 49 and c.gender ='female' then '40female'
when c.age between 40 and 49 and c.gender ='male' then '40male'
when c.age BETWEEN 50 and 59 and c.gender='female' then '50female'
when c.age BETWEEN 50 and 59 and c.gender='male' then '50male'
when c.age BETWEEN 60 and 69 and c.gender ='female' then '60female'
when c.age BETWEEN 60 and 69 and c.gender ='male' then '60male'
WHEN c.age>69 and c.gender='female' then '70+female' 
WHEN c.age>69 and c.gender='male' then '70+male' 
END as age_gender_category
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id) a
where age_gender_category is not null
group by 1


SELECT DISTINCT(customer_id) , 
	case when count(order_id)over(PARTITION by customer_id)>1 then '2+ orders'
	else '1 order'
	end as order_over_once_or_not


SELECT age_gender_category, count(customer)
from
(SELECT DISTINCT c.customer_id as customer,
case when c.age between 19 and 29 and c.gender='female' then '20female'
when c.age between 19 and 29 and c.gender='male' then '20male'
when c.age  BETWEEN 30 and 39 and c.gender ='female' then '30female'
when c.age  BETWEEN 30 and 39 and c.gender ='male' then '30male'
when c.age between 40 and 49 and c.gender ='female' then '40female'
when c.age between 40 and 49 and c.gender ='male' then '40male'
when c.age BETWEEN 50 and 59 and c.gender='female' then '50female'
when c.age BETWEEN 50 and 59 and c.gender='male' then '50male'
when c.age BETWEEN 60 and 69 and c.gender ='female' then '60female'
when c.age BETWEEN 60 and 69 and c.gender ='male' then '60male'
WHEN c.age>69 and c.gender='female' then '70+female' 
WHEN c.age>69 and c.gender='male' then '70+male' 
END as age_gender_category
FROM customers c left join orders o on c.customer_id =o.customer_id
where o.order_id is not null)a 
where age_gender_category is not null
GROUP by 1
order by 1


SELECT o.order_id ,c.customer_id ,p.product_ID ,p.product_type, p.colour,p.size
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id  
INNER JOIN sales s  ON o.order_id  = s.order_id
inner join products p on s.product_id =p.product_ID
where c.customer_id in (664,566,282,571,465,206)



SELECT  s.product_id, p.product_name, p.product_type ,p.colour ,p.size
FROM customers c  INNER JOIN orders o  ON c.customer_id =o.customer_id  
INNER JOIN sales s  ON o.order_id  = s.order_id
inner join products p on s.product_id =p.product_ID 
where c.age BETWEEN 30 and 39 and c.gender='male'

댐 지저분 담에 정리정돈 할 것.