본문 바로가기

SQL/실무 SQL16

[PostgreSQL] 10. 지속률과 정착률 한 번에 산출하는 쿼리(종합) 참고 지속률과 정착률은 모두 등록일 기준으로 n일 후의 행동을 집계하는 것이다. 따라서 등록일로부터 n일 경과하지 않은 상태라면 집계가 불가능하다.(1일 ~ 7일과 같은 단기간 지표를 활용하는 이유) 정착률은 7일 동안의 기간을 집계하므로, 실제로 며칠 사용했는지는 알 수 없다. 종합 쿼리 정착률을 계산하기 위해 만들었던 repeat_interval 테이블의 형식을 수정하면 지속률까지 계산할 수 있다. WITH repeat_interval(index_name, interval_begin_date, interva_end_date) AS ( VALUES ('01 day repeat ', 1, 1) , ('02 day repeat ', 2, 2) , ('03 day repeat ', 3, 3) , ('04 d.. 2024. 2. 4.
[PostgreSQL] 9. 매일의 N일 정착률 추이 확인하기(종합쿼리 포함) N일 지속률과 N일 정착률의 추이 n일 지속률과 n일 정착률을 따로 집계하면 등록 후 며칠간 사용자가 안정적으로 서비스를 사용하는지, 며칠 후에 그만두는 사용자가 많아지는지 등을 알 수 있다. 만약 지속률이나 정착률이 극단적으로 떨어지는 시점이 있다면, 해당 시점을 기준으로 공지사항 등을 전달하거나 N일 이상 사용한 사용자에게 보너스를 주는 등의 대책을 수행할 수 있을 것이다 ✨ 이번 포스팅에서는 지속률에 이어 정착률을 확인할 수 있는 종합 쿼리를 작성해본다 매일의 N일 정착률 추이 👉 지속률과 마찬가지로, 대책이 의도한 대로의 효과가 있는지 확인하려면 정착률을 매일 집계한 리포트가 필요하다 👉 참고로 7일 정착률이 극단적으로 낮은 경우에는 정착률이 아니라 '다음날 지속률' ~ '7일 지속률'을 확인해서.. 2024. 2. 4.
[PostgreSQL] 8. N일 후 지속률 산출하기(종합쿼리 첨부) 🎯 등록 시점을 기준으로 일정 기간 동안 사용자가 지속해서 사용하고 있는지를 조사할 때 지속률과 정착률을 사용하면 경향을 쉽게 파악할 수 있다. 지속률과 정착률이란? 지속률 : 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표 등록일 이후 매일 서비스를 사용하지 않더라도, 판정 날짜 안에 사용했다면 지속자로 취급함 집계 방법 : 등록자와 사용자 수를 집계하고, (사용자 수 / 등록 수) 로 구한다. 정착률 : 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표 7일이라는 기간 동안 한 번이라도 서비스를 사용했다면 정착자로 다룸 7일 정착률은 등록 후 1일부터 7일까지의 정착률을 기준으로 산출함 14일 정착률은 이후 7일(등록일부터 8일 후부.. 2024. 2. 1.
[PostgreSQL] 7. 사용자 등록 수의 추이와 경향 보기 다음과 같이 SNS 서비스에서 수집된 데이터의 예시로 실습한다. 1. 등록 수의 추이와 경향 보기 사용자 등록이 필요한 서비스에서 등록 수는 중요한 지표이다. 등록자가 감소 경향을 보인다면 서비스를 활성화하기 어려워진다는 의미이고, 반대로 등록자가 증가 경향을 보인다면 사용자가 서비스에서 이탈할지 아닐지를 분석해서 서비스 활성화와 연결할 수 있어야 한다. 사용자 이탈 여부를 살피기 전에, 먼저 현재 등록 수를 파악해본다. ▶︎ 날짜별 등록 수의 추이 SELECT register_date , count(DISTINCT user_id) AS register_count FROM mst_users GROUP BY 1 ORDER BY 1 ▶︎ 월별 등록 수의 추이 등록 날짜에서 연-월을 추출하고, 이를 기준으로 .. 2024. 1. 30.
[PostgreSQL] 6. RFM 분석으로 사용자 그룹 나누기 RFM 분석 Recency : 최근 구매일 최근에 무언가를 구매한 사용자를 우량 고객으로 취급 Frequency : 구매 횟수 사용자가 구매한 횟수를 세고, 많을수록 우량 고객으로 취급 Monetary : 구매 금액 합계 사용자의 구매 금액 합계를 집계하고, 금액이 높을수록 우량 고객으로 취급 👉 지난 포스팅에서 살펴본 Decile 분석에서는 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러 번 구매한 사용자가 같은 그룹으로 판정되기도 한다. 하지만 RFM 분석은 이러한 사용자들도 구분할 수 있다. 1. RFM 구하기 고객별로 가장 최근 구매일과 구매 금액이 있다면 RFM을 구할 수 있다. max(dt)로 가장 최근 구매일을 구하고, current_date와의 날짜 차이를 구한다.. 2024. 1. 30.
[PostgreSQL] 5. Decile 분석을 사용해 사용자를 10단계 그룹으로 나누기 Decile 분석 사용자 특징 분석시 성별과 연령 등의 데이터가 있다면 이러한 속성에 따른 특징을 확인할 수 있다. 데모그래픽한 데이터가 존재하지 않는 경우, 사용자 액션으로 속성을 정의해 보는 것도 좋다. 데이터를 10단계로 분할해서 중요도를 파악하는 것을 'Decile 분석'이라고 한다. 여기서는 사용자의 구매 금액에 따라 순위를 구분하고 중요도를 파악하는 리포트를 만들어본다. 📢 단, Decile 분석은 데이터 검색 기간에 따라 문제가 있다. 검색 기간이 너무 장기간이면 과거의 우수 고객이었지만, 현재는 휴면인 고객이 포함될 가능성이 있다. 반대로 검색 대상이 단기간이라면 정기적으로 구매하는 안정 고객이 포함되지 않고, 해당 기간 동안에만 일시적으로 많이 구매한 사용자가 우수 고객으로 취급 될 수 .. 2024. 1. 30.
[PostgreSQL] 4. 사용자의 방문 빈도 집계하기 사용자가 일주일 또는 한 달 동안 서비스를 얼마나 쓰는지 알면 분석에 큰 도움이 된다. 이번엔 '서비스를 한 주 동안 며칠 사용하는 사용자가 몇 명인지' 집계하는 방법을 알아본다. 👆 위 데이터를 바탕으로 한 주 동안 매일 몇 명의 사용자가 방문했는지 파악하고 구성비누계도 계산한다. 한 주 동안 며칠 방문했는지 사용자 수를 집계 먼저 timestamp에서 날짜만 추출한다. 유저 별로, 일주일 동안 며칠 방문했는지 count 한다. (Where 조건과, count(distinct) 사용) 카운트 된 일 수(방문 일 수) 기준으로 유저 아이디 count를 한다. 👉 결과 예로, 7일 중 2일 방문한 사용자가 2명이다. WITH action_log_with_dt AS ( SELECT * , substring(.. 2024. 1. 29.
[PostgreSQL] 3. 연령별 특징 추출하기 2에서 사용한 연령별 구분을 기반으로 각각 구매한 상품의 카테고리를 집계해보자. 연령과 나이를 구분하여 카테고리를 만드는 것까지는 이전 포스팅과 동일하다. WITH mst_users_with_int_birth_date AS ( SELECT * , 20170101 AS int_specific_date ,CAST(replace(substring(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date FROM mst_users ) , mst_users_with_age AS ( SELECT * , floor((int_specific_date - int_birth_date)/10000) AS age FROM mst_users_with_int_birth_date ).. 2024. 1. 29.
[PostgreSQL] 2. 연령별 구분 집계 연령별 구분 집계 시청률 분석에 많이 사용되는 연령별 구분을 집계해본다. 다음 표를 기반으로 사용자의 연령별 구분을 집계한다. 데이터는 다음과 같다. 사용자 나이 구하기 나이는 생일과 특정 날짜를 정수로 표현하고, 이 차이를 10,000으로 나누는 방법으로 간단히 구할 수 있다. 먼저 특정 날짜를 정수로 표현한다(기준 날짜) 사용자 생년월일(문자형)을 다음과 같이 정수 표현으로 반환한다. WITH mst_users_with_int_birth_date AS ( SELECT * , 20170101 AS int_specific_date ,CAST(replace(substring(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date FROM mst_users .. 2024. 1. 29.
728x90

티스토리툴바