SSO 로그 테이블로 MAU와 재방문율 구하는 SQL

시리즈 11/16
데이터
MySQL
SQL

시스템 사용 현황을 보고하려고 월 사용자 수(MAU)와 재방문율을 구했다. Mendix의 사용자, 세션 테이블은 이력이 남지 않아 쓸 수 없었고, SSO 모듈이 남기는 로그인 로그 테이블을 기준으로 했다.

문제

월별 실사용자 수와 다시 들어오는 비율을 알아야 하는데 사용자 테이블에는 접속 이력이 없음

원인

로그 테이블의 사용자 컬럼은 비어 있고, 사용자 ID는 메시지 문자열 안에 들어 있음. 시간은 UTC

해결

메시지 앞부분을 잘라 사용자 ID 추출, 성공 값으로 필터, 9시간 더해 한국 시간 기준으로 집계

로그 테이블 구조

컬럼	예시	주의
message	`user01, login success!`	사용자 ID는 쉼표 앞
logonresult	성공, 실패 값	실제 값을 먼저 조회해서 철자 확인
createddate	UTC 시각	한국 시간 집계 시 +9시간
owner	비어 있음	사용자 식별에 쓸 수 없음

성공 값부터 확인한다로그 값이 예상한 철자와 달랐다. 문자열을 추측해서 조건에 넣지 말고 SELECT DISTINCT로 실제 값을 먼저 확인한다.

SQL: 값 확인

SELECT logonresult, COUNT(*) AS 건수
FROM sso$ssolog
GROUP BY logonresult;

누적 사용자 수

SQL

SELECT COUNT(DISTINCT LOWER(TRIM(SUBSTRING_INDEX(message, ',', 1)))) AS 누적사용자
FROM sso$ssolog
WHERE logonresult = 'Success';      -- 실제 값으로 교체

월별 사용자 수 (MAU)

SQL

SELECT DATE_FORMAT(DATE_ADD(createddate, INTERVAL 9 HOUR), '%Y-%m') AS 월,
       COUNT(DISTINCT LOWER(TRIM(SUBSTRING_INDEX(message, ',', 1)))) AS 사용자수,
       COUNT(*) AS 로그인횟수
FROM sso$ssolog
WHERE logonresult = 'Success'
GROUP BY 월
ORDER BY 월;

일별 순방문자

SQL

SELECT DATE(DATE_ADD(createddate, INTERVAL 9 HOUR)) AS 일자,
       COUNT(DISTINCT LOWER(TRIM(SUBSTRING_INDEX(message, ',', 1)))) AS 방문자수
FROM sso$ssolog
WHERE logonresult = 'Success'
GROUP BY 일자
ORDER BY 일자;

전월 대비 재방문율

지난달 사용자 중 이번 달에도 들어온 사람의 비율이다. MySQL 8 이상의 CTE를 쓴다.

SQL

WITH m AS (
  SELECT DISTINCT
         DATE_FORMAT(DATE_ADD(createddate, INTERVAL 9 HOUR), '%Y-%m-01') AS ym,
         LOWER(TRIM(SUBSTRING_INDEX(message, ',', 1)))                  AS uid
  FROM sso$ssolog
  WHERE logonresult = 'Success'
)
SELECT cur.ym                                   AS 월,
       COUNT(DISTINCT cur.uid)                  AS 사용자수,
       COUNT(DISTINCT prev.uid)                 AS 전월사용자중_재방문,
       ROUND(COUNT(DISTINCT prev.uid) * 100.0 /
             NULLIF((SELECT COUNT(*) FROM m p2
                     WHERE p2.ym = DATE_SUB(cur.ym, INTERVAL 1 MONTH)), 0), 1) AS 재방문율
FROM m cur
LEFT JOIN m prev
       ON prev.uid = cur.uid
      AND prev.ym  = DATE_SUB(cur.ym, INTERVAL 1 MONTH)
GROUP BY cur.ym
ORDER BY cur.ym;

2개월 이상 접속한 사용자 비율

SQL

WITH u AS (
  SELECT LOWER(TRIM(SUBSTRING_INDEX(message, ',', 1))) AS uid,
         COUNT(DISTINCT DATE_FORMAT(DATE_ADD(createddate, INTERVAL 9 HOUR), '%Y-%m')) AS 접속개월
  FROM sso$ssolog
  WHERE logonresult = 'Success'
  GROUP BY uid
)
SELECT 접속개월, COUNT(*) AS 사용자수,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS 비율
FROM u
GROUP BY 접속개월
ORDER BY 접속개월;

테이블 이름Mendix는 DB 테이블 이름을 모듈명$엔티티명 형식으로 만든다. 실제 이름은 모듈 구성에 따라 다르니 DB에서 확인한다. $가 들어간 이름은 클라이언트에 따라 백틱으로 감싸야 할 수 있다.

보고에 쓸 때

MAU는 개발 기간 평균과 운영 기간 평균을 나눠 비교하면 변화가 잘 보임
관리자, 테스트 계정은 제외 조건을 따로 둠
재방문율은 사용자 수가 적은 달에 크게 출렁이므로 사용자 수와 함께 표시

시리즈 목차 16편

PI 담당자가 로우코드로 사내 시스템을 직접 만들고 운영한 기록
Mendix Datagrid2 대신 HTMLSnippet과 JS로 목록 화면 만들기
Mendix SPA에서 HTMLSnippet JS가 꼬이는 이유: 이벤트 중복과 DOM 공존
Mendix mx.data.commit이 조용히 실패할 때
Mendix Java heap OOM 추적기: base64 이미지 컬럼
Mendix 커밋 롤백, 용량 문제가 아니었다: FileDocument 전환기
Mendix 첫 접속 지연 줄이기: 워밍업과 캐시
Mendix SAML SSO 자동 로그인과 딥링크 유지
Mendix Studio Pro에서 안 바뀌는 스타일 main.scss로 바꾸기
Mendix MPK 파일 열어서 마이크로플로우 구조 보기
SSO 로그 테이블로 MAU와 재방문율 구하는 SQL (현재 글)
Mendix DateTime이 BI 도구에서 하루 빠르게 보이는 이유
대시보드 숫자가 틀렸을 때 확인하는 순서
보기 좋은 대시보드에서 할 일을 알려주는 대시보드로
여러 점검 시스템을 하나로 합치는 데이터 모델
데이터 비전공자에게 분석 결과 보고하기

#MAU
#SQL
#MySQL
#SSO
#재방문율
#Mendix