728x90
목차
실습에 필요한 테이블
날짜별 접근 데이터를 집계하는 쿼리
- 로그인을 한 뒤 사용하는 이용자와 로그인을 하지 않고 사용하는 이용자를 나누는 것이 집계할 떄 의미있는 결과를 도출한다.
- 사이트에서 로그인 기능을 제공하는 경우, 로그인 UU와 비로그인 UU도 동시에 집계되어야 한다.
SELECT
substring(stamp, 1, 10) AS dt
-- 쿠키 계산하기
, COUNT(DISTINCT long_session) AS access_users
-- 방문 횟수 계산하기
, COUNT(DISTINCT short_session) AS access_count
-- 페이지 뷰 계산하기
, COUNT(*) AS page_view
-- 1인당 페이지 뷰 수
, 1.0 * COUNT(*) / NULLIF(COUNT(DISTINCT long_session), 0) AS pv_per_user
FROM
access_log
GROUP BY
dt
ORDER BY
dt
경로 별 집계하기
- 상세 페이지라고 집계할 수 있게, 요청 매개변수를 생략하고 경로만 집계한다.
WITH
access_log_with_path AS (
SELECT
substring(url from '//[^/]+([^?#]+)') AS url_path
FROM
access_log
)
SELECT
url_path
, COUNT(DISTINCT short_session) AS access_count
, COUNT(DISTINCT long_session) AS access_users
, COUNT(*) AS page_view
FROM
access_log_with_path
GROUP BY
url_path;
유입원별로 방문 횟수 또는 CVR 집계하기
- 웹사이트 접근시 다음과 같은 다른 사이트의 링크 클릭 방법도 있다.
- 검색 엔진
- 검색 연동형 광고
- SNS
- 제휴 사이트
- 유입원 판정하는 방법
- 직전 페이지의 URL을 레퍼러(referrer) 라고 부른다.
- URL 매개변수 기반 판정
- 레퍼러 도메인과 랜딩 페이지를 사용한 판정
- URL 매개변수 기반 판정
- 구글 애널리틱스로 URL 생성 도구 기반으로 매개변수를 추가하는 방법을 사용하는 경우가 많다.
- Campain URL Builder라는 것을 통해 필요한 매개변수를 입력하면 URL을 생성해준다.
- 생성된 URL은 다음과 같은 규칙을 따른다
- (www.~~~.com?utm_source=google&utm_medium=cpc&utm_campaign=spring_sale)
- URL 생성 도구의 매개변수
| URL 생성 도구 항목 | URL 매개변수 | 용도 |
| Campaign Source | utm_source | 레퍼러, 사이트 이름, 명칭 |
| Campaign Medium | utm_medium | 유입 분류 |
| Campaign Name | utm_campaign | 캠페인 페이지의 이름 |
| Campaign Term | utm_term | 유입 키워드 |
| Campaign Content | utm_content | 광고 분류 식별자 |
- 레퍼러 도메인과 랜딩 페이지를 사용한 판정
- 검색 엔진, 개인 블로그 , 트위터 등 광고 담당자가 조작할 수 없는 영역은 URL 매개변수를 활용할 수 없다.
- 이럴때 레퍼러를 사용해야 어떤 곳에서 유입되는지 파악이 가능하다.
- 유입원별 방문 횟수 집계한 쿼리
- 유입원 판단 로직
| 유입 경로 | 판정 방법 | 집계 방법 |
| 검색 연동 광고 | URL 생성 도구로 만들어진 매개변수 | URL에 utm_source, utm_medium이 있을 경우, 두 개의 문자열을 결합해서 집계 |
| 제휴 마케팅 사이트 | ||
| AD 네트워크 | ||
| 검색 엔진 | 도메인 | 레퍼러의 도메인이 다음과 같은 검색 엔진일 때 - search.naver.com - www.google.co.kr |
| 소셜미디어 | 레퍼러의 도메인이 다음과 같은 소셜 미디어일 때 - twitter.com - www.facebook.com |
|
| 기타 사이트 | 위에 언급한 도메인이 아닐 경우 other |
WITH
access_log_with_parse_info AS (
-- 유입원 정보 추출
SELECT
substring(url from 'https?://([^/]*)') AS url_domain
, substring(url from 'utm_source=([^&]*)') AS url_utm_source
, substring(url from 'utm_medium=([^&]*)') AS url_utm_medium
, substring(referrer from 'https?://([^/]*)') AS referrer_domain
FROM
access_log
)
, access_log_with_via_info AS (
SELECT *
, ROW_NUMBER() OVER(ORDER BY stamp) AS log_id
, CASE
WHEN url_utm_source <> '' AND url_utm_medium <> ''
THEN concat(url_utm_source, '-', url_utm_medium)
WHEN referrer_domain IN ('search_yahoo.co.jp', 'www.google.co.jp') THEN 'search'
WHEN referrer_domain IN ('twitter.com', 'www.facebook.com') THEN 'social'
ELSE 'other'
-- ELSE reffere_domain으로 변경시 도메인별 집계
END AS via
FROM
access_log_with_parse_info
WHERE
COALESCE(referrer_domain, '') NOT IN ('', url_domain)
)
SELECT
via, COUNT(1) AS access_count
FROM
access_log_with_via_info
GROUP BY
via
ORDER BY
access_count DESC;
- 유입원별로 CVR 집계하기
- 각 방문에서 구매한 비율을 집계하는 쿼리
WITH
access_log_with_parse_info AS (
-- 유입원 정보 추출
SELECT
substring(url from 'https?://([^/]*)') AS url_domain
, substring(url from 'utm_source=([^&]*)') AS url_utm_source
, substring(url from 'utm_medium=([^&]*)') AS url_utm_medium
, substring(referrer from 'https?://([^/]*)') AS referrer_domain
FROM
access_log
)
, access_log_with_via_info AS (
SELECT *
, ROW_NUMBER() OVER(ORDER BY stamp) AS log_id
, CASE
WHEN url_utm_source <> '' AND url_utm_medium <> ''
THEN concat(url_utm_source, '-', url_utm_medium)
WHEN referrer_domain IN ('search_yahoo.co.jp', 'www.google.co.jp') THEN 'search'
WHEN referrer_domain IN ('twitter.com', 'www.facebook.com') THEN 'social'
ELSE 'other'
-- ELSE reffere_domain으로 변경시 도메인별 집계
END AS via
FROM
access_log_with_parse_info
WHERE
COALESCE(referrer_domain, '') NOT IN ('', url_domain)
)
SELECT
a.log_id
, a.via
, SUM(
-- Postgresql 경우 interval 자료형의 데이터와 날짜와 시간 사칙연산 가능
CASE WHEN p.stamp::date BETWEEN a.stamp::date AND a.stamp::date + '1 day'::interval
THEN amount
END) AS amount
FROM
access_log_with_via_info AS a
LEFT OUTER JOIN
purchase_log AS p
ON a.long_session = p.long_session
GROUP BY a.log_id, a.via
)
SELECT
via
, COUNT(1) AS via_count
, COUNT(amount) AS conversions
, AVG(100.0 * SIGN(COALESCE(amount, 0))) AS cvr
, SUM(COALESCE(amount, 0)) AS amount
, AVG(1.0 * COALESCE(amount, 0)) AS avg_amount
FROM
access_log_with_purchase_amount
GROUP BY
via
ORDER BY
cvr DESC;사이트 내의 사용자 행동 파악하기
- 입구 페이지와 출구 페이지 집계하기
- 접근 로그의 각 세션에서 URL 추출 시 FIRST_VALUE / LAST_VALUE 사용한다
- ORDER BY는 디폴트가 첫~ 현재 행이고 ROWS BETWEEN으로 모든 행 대상 지정한다
- 각 세션의 입구 페이지와 출구 페이지 경로를 추출하는 쿼리
- 다음 과 같은 표의 결과를 구하는 쿼리
| type | 경로 | 입구 수 / 출구 수 |
| landing | /detail | 3539 |
| landing | /search_list | 2500 |
| exit | /detail | 3663 |
| exit | /search_list | 2555 |
WITH
activity_log_with_landing_exit AS (
SELECT
session
, path
, stamp
, FIRST_VALUE(path)
OVER(PARTITION BY session ORDER BY stamp ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS landing
, LAST_VALUE(path)
OVER(PARTITION BY session ORDER BY stamp ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS exit
FROM
activity_log
)
SELECT *
FROM
activity_log_with_landing_exit;
이탈률과 직귀율 계산하기
- 직귀율 : 직귀 수 / 입구 수, 특정 페이지만 조회하고 곧바로 이탈한 비율 ⇒ 사이트 배치 등을 재검토
- 이탈율 : 출구 수 / 페이지 뷰, 이탈률이 높은 페이지는 나쁘지만은 않다
- 경로별 이탈률을 집계하는 쿼리
WITH
activity_log_with_exit_flag AS (
SELECT
*
-- 출구 페이지 판정
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) = 1 THEN 1 ELSE 0
END AS is_exit
FROM
activity_log
)
SELECT
path
, SUM(is_exit) AS exit_count
, COUNT(1) AS page_view
, AVG(is_exit * 100.0) AS exit_ratio
FROM
activity_log_with_exit_flag
GROUP BY
path;- 경로별 직귀율 집계하는 쿼리
WITH
activity_log_with_exit_flag AS (
SELECT
*
-- 입구 페이지 판정
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) = 1 THEN 1 ELSE 0 END AS is_landing
-- 직귀 판정
, CASE
WHEN COUNT(1) OVER(PARTITION BY session) = 1 THEN 1 ELSE 0 END AS is_bounce
FROM
activity_log
)
SELECT
path
, SUM(is_bounce) AS bounce_count
, SUM(is_landing) AS landing_count
, AVG(100.0 * CASE WHEN is_landing = 1 THEN is_bounce END) AS bounce_ratio
FROM
activity_log_with_exit_flag
GROUP BY
path;
검색 조건들의 사용자 행동 가시화하기
- Ex.) EC 사이트에서는 카테고라, 제조사, 가격대 필터 기능 제공
- 검색 조건을 더 자세하게 지정해서 사용하는 사용자는 동기가 명확하므로, 성과로 이어지는 비율이 높다
- 검색 타입별 CTR, CVR과 방문 횟수를 확인한다
- 다음은 클릭 플래그와 컨버전 플래그를 계산하는 쿼리
WITH
activity_log_with_session_click_conversion_flag AS (
SELECT
session
, stamp
, path
, search_type
-- 상세 페이지 이전 접근에 플래그 추가하기
, SIGN(SUM(CASE WHEN path = '/detail' THEN 1 ELSE 0 END)
OVER(PARTITION BY session ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS has_session_click
-- 성과를 발생시키는 페이지의 이전 접근에 플래그 추가하기
, SIGN(SUM(CASE WHEN path = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY session ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS has_session_conversion
FROM
activity_log
)
SELECT
session
, stamp
, path
, search_type
, has_session_click AS click
, has_session_conversion AS cnv
FROM
activity_log_with_session_click_conversion_flag
ORDER BY
session, stamp;
폴아웃 리포트를 사용해 사용자 회유를 가시화하기
- 어떤 지점에서 어떤 지점으로 옮겨가는 것을 폴스루, 이탈을 폴아웃이라 한다
- 폴아웃 단계 순서를 접근 로그와 결합하는 쿼리
WITH
mst_fallout_step AS (
-- 폴아웃 단계와 경로의 마스터 테이블
SELECT 1 AS step, '/' AS path
UNION ALL SELECT 2 AS step, '/search_list' AS path
UNION ALL SELECT 3 AS step, '/detail' AS path
UNION ALL SELECT 4 AS step, '/input' AS path
UNION ALL SELECT 5 AS step, '/complete' AS path
)
, activity_log_with_fallout_step AS (
SELECT
l.session
, m.step
, m.path
-- 첫 접근과 마지막 접근 시간 구하기
, MAX(l.stamp) AS max_stamp
, MIN(l.stamp) AS min_stamp
FROM
mst_fallout_step AS m
JOIN
activity_log AS l
ON m.path = l.path
GROUP BY
-- 세션별로 단계 순서와 경로를 사용해 집약
l.session, m.step, m.path
)
, activity_log_with_mod_fallout_step AS (
SELECT
session
, step
, path
, max_stamp
-- 직전 단계에서의 첫 접근 시간 구하기
, LAG(min_stamp)
OVER(PARTITION BY session ORDER BY step)
AS lag_min_stamp
-- 세션에서의 단계 순서 최소값 구하기
, MIN(step) OVER(PARTITION BY session) AS min_step
-- 해당 단계에 도달할 떄까지 걸린 단계 수 누계
, COUNT(1)
OVER(PARTITION BY session ORDER BY step
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_count
FROM
activity_log_with_fallout_step
)
SELECT
*
FROM
activity_log_with_mod_fallout_step
ORDER BY
session, step;
오류율 집계하기
- URL만으로는 확인 화면으로 이동율을 집계되지 않는다.
- 로그를 기록하려면 페이지 열람 로그에 error라는 상태를 출력해야 한다.
- /confirm 페이지에서 오류가 발생해서 재입력 화면을 출력한 경우 집계한다.
- 다음은 확인 화면에서 오류율을 집계하는 쿼리
SELECT
COUNT(*) AS confirm_count
, SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS error_count
, AVG(CASE WHEN status = 'error' THEN 1.0 ELSE 0.0 END) AS error_rate
, SUM(CASE WHEN status = 'error' THEN 1.0 ELSE 0.0 END) / COUNT(DISTINCT session) AS error_per_user
FROM
form_log
WHERE
-- 확인 화면 페이지 판정하기
path = '/regist/confirm';- 입력 양식 직귀율 집계하기
- 직귀 수가 높으면, 사용자가 입력을 중간에 포기할 만큼 입력 항목이 많거나, 레이아웃이 난잡하다는 의미이다.
WITH
form_with_progress_flag AS (
SELECT
substring(stamp, 1, 10) AS dt
, session
-- 입력 화면으로의 방문 플래그계산하기
, SIGN(
SUM(CASE WHEN path IN ('/regist/confirm', '/regist/complete') THEN 1 ELSE 0 END)) AS has_progress
FROM
form_log
GROUP BY
dt, session
)
SELECT
dt
, COUNT(1) AS input_count
, SUM(CASE WHEN has_progress = 0 THEN 1 ELSE 0 END) AS bounce_count
, 100.0 * AVG(CASE WHEN has_progress =0 THEN 1 ELSE 0 END) AS bounce_rate
FROM
form_with_progress_flag
WHERE
-- 입력 화면에 방문했던 세션만 추출
has_input = 1
GROUP BY
dt;- 오류가 발생하는 항목과 내용 집계하기
- 오류가 발생할 떄 로그를 출력해야만 원인을 파악할 수 있다.
- 각 입력 양식의 오류 발생 장소와 원인을 집계하는 쿼리
SELECT
form
, field
, error_type
, COUNT(1) AS count
, 100.0 * COUNT(1) / SUM(COUNT(1)) OVER(PARTITION BY form) AS share
FROM
form_error_log
GROUP BY
form, field, error_type
ORDER BY
form, count DESC;'데이터분석 > PostgreSQL' 카테고리의 다른 글
| 데이터를 무기로 삼기 위한 분석 기술 (0) | 2023.07.30 |
|---|---|
| 데이터 활용의 정밀도를 높이는 분석 기술 (0) | 2023.07.30 |
| 사용자를 파악하기 위한 데이터 추출 - 2 (2) | 2023.07.05 |
| 사용자를 파악하기 위한 데이터 추출 - 1 (0) | 2023.07.05 |
| 시계열 기반으로 데이터 집계하기 (0) | 2023.07.04 |