[SQL + Python] DAU,CTR

 

오늘은 DAU와 CTR 쿼리를 작성해서 Python으로 쿼리실행과 결과저장까지 해보는 연습코드를 작성해 보았다

 

1) 데이터 준비

 

SQL 문에서 

- user_log : 사용자 행동 이벤트(login/click)

- ad_log : 광고 이벤트(노출/클릭,켐페인)

-order_log : 주문 데이터(주문시간 ,금액)

 

분석할 지표가 나오도록” 테이블 스키마를 먼저 만들고 INSERT로 샘플 데이터를 넣는 흐름

 

쿼리를 다 보여줄순 없지만

 

이런식으로 쿼리를 작성해나가면서 어떤게 더 효율적인쿼리인지 

꼬꼬무 방식으로 쿼리를 작성해 나갔던거 같다

 

2) SQL로 지표 정의 & 집계

 

- DAU ( Daily Active Users )

  하루에 특정 이벤트를 1번이상 한 유니크 사용자수

  포인트: 지표정의는 WHERE 조건으로 바뀐다

  - 전체 이벤트 기준 DAU

  - login만 DAU

  - login + click 기준 DAU

  추이는 group by event_date로 일자별 DAU를 만든다

 

-CTR ( Click Through Rate )

  CTR = 클릭 수 / 노출 수

  POINT : SUM(CASE WHEN ...등등등)로 클릭/노출을 각각 세고 비율 계산

  NULLIF(...,0)로 0으로 나누기 방지(실무 안전장치 까지 달아줌)

  날짜별/캠페인별로 GROUP BY해서 비교 분석

 

 

3) Python으로 쿼리 실행 -> 결과저장 = 자동화

 SQLAlchemy 엔진으로 MySQL 연결 준비를 하고

 쿼리.py에 SQL문 작성했던 문자열을 모아서 "재사용 가능한 쿼리를 모듈화" 해줬다

 

 main.py에서는 pd.read_sql()로 쿼리를 실행하고

 결과는 to_csv()로 저장할수있게 해두었다 main.py에서는 test용으로 실행하면 저장 될수있게 세팅해두었다

여기서 핵심 = " SQL 결과를 파일로 뽑아내는 ETL의 가장 작은 형태"를 만드는것!

 

 

 

4) 메트릭 통합(가공) - DAU + CTR을 합쳐서 한 장표처럼 보기

metrics.py에서 

 

-DAU 결과 DF

-CTR 결과 DF

-merge(on='event_date')로 같은 날짜 기준으로 합침

 

실무에서 대시보드/리포트는 보통 여러 지표를 날짜키로 합쳐서 만든다

 

5) 스케쥴링 - 매일 정해진 시간에 갱신

 

schedule 라이브러리를 활용해

매일 17:00 같은 고정시간에 job 실행 구조 학습

"한번 실행이 아니라 반복실행의 형태를 만들기"

 

프로젝트를 구성하면서 4개 정도 학습해본거같다.

 

1. 로그 테이블 설계

2. 지표정의 (DAU/CTR)

3. SQLAlchemy + pandas로 쿼리 실행

4. CSV저장 + 스케쥴링(파이프라인 운영 기초)