[파이썬 압수] (1) SQL로만 DAU, MAU, stickness 구하기

파이썬 압수!

무슨 청천벽력 같은 소리냐고요? 지금부터 저는 이번 미니 플젝을 끝날 때까지 해당 미니 플젝에서는 파이썬은 최소한만 사용 가능합니다. 오로지 SQL로만 지표를 뽑습니다. 물론 실제 데이터를 저장하고 시각화하거나 연동에는 파이썬을 써야 하지만 모든 데이터 추출과 가능한 모든 전처리는 SQL로만 진행합니다.

그리고 가장 최악을(?) 가정하여 딱 메인 테이블 하나만 create 되었다는 상황하에 진행합니다. 함부로 subtable 못만드는 최악의 상황이라고 해둡시다. 하하...

압수 프로젝트에서는 Kaggle의 다음 데이터를 활용합니다.

https://www.kaggle.com/datasets/yaybeedee/multi-category-online-store-funnel

Multi Category Online Store Funnel

이 데이터 셋이 적당한 크기에 상품 뷰, 카트 담기, 상품 구매 3단계 정도의 퍼널도 구성되어 있어 연습에 적합한 대상이라 생각됩니다.

근데, DB를 만들고 뭐하고 해도 되는데, 어차피 그 결과물도 다시 저장하고 가져오고 그러기 번거로우니까.. 조금 다른 방법을 써보죠

Python과 SQL의 연동, sqlite3 써먹기

기본적으로 제공하는 라이브러리를 씁니다. 이거 쓰고 데이터프레임 불러오는거는 파이썬으로 합시다.

import sqlite3
import pandas as pd
df = pd.read_csv('C:/Users/USER/Desktop/TIL_new/data_analyst_sql/events.csv')
# 없을때는 테이블이 생성되고, 있을때는 불러온다
con = sqlite3.connect("events.db")
#최초로 테이블을 create안하고 이미 있는 파일을 load 시킬려면 이렇게 한다.
#다만 이미 생성된 이후로는 error걸리므로 최초 실행 이후로는 주석 필수!
#df.to_sql('events', con)

네, 이렇게 하면 연동이 됩니다. 이제 sql을 실행하는 구문 외에는 사실상 파이썬 압수입니다.

물론 데이터 프레임으로 변형하고 뭐하고 작업도 해야 하지만, 이번 미니 플젝의 목표는 SQL로 비즈니스 데이터 분석에 사용되는 대표 지표들을 모두 한번씩 어떻게던 추출하기 입니다.

간단하게, Sqlite3의 사용법만 확인해봅시다.

# 다음과 같이 하나의 열을 직접 볼 수 있음
cur = con.cursor()
cur.execute('SELECT * FROM events')
cur.fetchone()

이렇게 한 줄을 빼올 수 있습니다. cur.execute에 쿼리문을 넣고요. 하지만 쿼리가 길어지면 답도 없습니다.
하지만 다른 방법이 있습니다.

script="""
SELECT *
FROM events
WHERE event_type='view'
ORDER BY price
LIMIT 1
"""

cur.execute(script)
# 모든 결과 추출하는 방법
result = cur.fetchall()
print(result)

이렇게 script 안에 sql쿼리문을 작성뒤에 cur.execute 시킵니다. 그러면 해결!

근데, 아쉽게도 결과를 출력하면 데이터 컬럼명은 나오지 않습니다. 방법이 없어서 이건 따로 테이블을 만들어야 할 겁니다. 게다가 인덱스 번호도 강제로 할당되어 있어 전처리가 좀 필요하겠어요. 물론 실무에서는 sqlite3을 쓸 리는 없으니 이번 미니플젝에서는 그 과정은 안 할 겁니다. 오로지 정상적으로 뽑는데에 집중합시다.

 

DAU, MAU 뽑기

가장 쉬운 것부터 해보죠. 아, 근데 데이터를 심어줄 때 하필 날짜가 문자열로 심겼네요.
원래대로라면 MySQL기준 Date_format을 써야겠으나 지금은 substr를 써야 합니다.

DAU는 심플합니다. 다만 시간은 필요없으므로, substr을 잘 시켜야겠죠.
우선은 이번 DAU는 view던 cart던 purchase던 모든 행동을 기준으로 합시다. 사람이 꼭 상품을 보고 구매하는 것도 아니고, 가끔은 카트도 안담고 구매할 수 있고, 혹은 원래 담아둔 것을 마무리 할 수도 있죠. 따라서 그냥 유효한 행동이 있었다를 Active의 기준으로 하겠습니다.

script = """
SELECT
    substr(event_time, 1, 10) as day,
    count(distinct user_id) as dau
FROM events
GROUP BY day
ORDER BY day
"""
cur.execute(script)
result = cur.fetchall()
print(result)

MAU도 심플합니다. substr만 달라집니다.

script = """
SELECT
    substr(event_time, 1, 7) as year_month,
    count(distinct user_id) as mau
FROM events
GROUP BY year_month
ORDER BY year_month
"""
cur.execute(script)
result = cur.fetchall()
print(result)

 

Stickness

사용자 고착도 지표인 Stickness는 DAU/MAU로 계산합니다. 근데, DAU 결과를 돌려보면 9/24부터네요.정확한 측정을 위해 Stickness는 10/1부터 계산합니다.

Stickness를 봐야 하는 이유는 무엇일까요? 활성 사용자들이 얼마나 습관적으로 서비스를 이용하는지를 볼 수 있는 지표입니다. stickness가 높다는 것은 사용자들이 반복해서 서비스를 많이 사용한다는 의미가 되겠죠. 사용자가 더 많이, 더 자주 접속해야 좋은 서비스에서는 stickness는 중요한 지표로 작용할 것입니다. 주로 광고 관련 서비스나, 대중적인 온라인 쇼핑몰에서 stickness가 높아야겠죠.

실제 구현에서는 join의 조건이 중요합니다. 이번 상황에서는 DAU에서 월까지 substr한 것이  MAU의 year-month와 같으면 됩니다. LEFT JOIN으로 표기하면 직관적으로 표를 합치는 방향이 이해하기 쉬울 것입니다.

그리고, 추가,! DAU/MAU를 그냥 하면 0으로 나옵니다. 그냥 정수 계산이 되어버린거죠. +0.00 처리를 하면 실수 계산이 됩니다. 수가 작을 것이기에  * 100을 하여 % 단위로 데이터를 추출해 주었습니다.

script="""
SELECT
    DAU.day,
    ROUND((dau+0.00)/(mau+0.00) * 100, 2) as stickness
FROM(
    SELECT
        substr(event_time, 1, 10) as day,
        count(distinct user_id) as dau
    FROM events
    GROUP BY day
    ORDER BY day) AS DAU
    LEFT JOIN 
    (
    SELECT
        substr(event_time, 1, 7) as year_month,
        count(distinct user_id) as mau
    FROM events
    GROUP BY year_month
    ORDER BY year_month) AS MAU
    ON substr(DAU.day,1,7) = MAU.year_month
WHERE year_month != '2020-09'
ORDER BY day
"""

cur.execute(script)
result = cur.fetchall()
print(result)

그래도, 여기까진 간단합니다.

 

하지만, 압수는 이제부터가 진짜 시작입니다.  퍼널과 리텐션이 남아있습니다.!  안 돼