BigQuery로 분석한 Hongwon's Data 블로그 [1]

0. 들어가며

GA 분석 내용이 아닌 더 높은 레벨의 분석을 위해서 로우 데이터 분석이 필수적입니다. GA4는 Bigquery와 무료로 연동하여 로우 데이터를 받아볼 수 있습니다. 이번 글에선 SQL로 직접 제 블로그 사용자의 로우 데이터를 분석해보겠습니다.

분석 주제

• 날짜별 사용자 수 대비 첫 사용자 비율은?
• 최다 조회 사용자의 특성은?
• 외국에서 접속한 사용자가 본 페이지는?

 

※ 참고
GA4와 Bigquery의 연동방법

강의자료 - GA4 - 빅쿼리(BigQuery) 연결하는 방법

---

1. 데이터 개요

데이터 구조

5일간(10/9~10/13) 블로그 사용자 로그 데이터를 받았고, 위 캡처 화면은 10/9일의 데이터 모습입니다. 특이한 점은 행 안에 여러 행이 존재하고 있다는 점입니다. 구글은 nested 구조라고 명하고, 이를 장점으로 내세우고 있지만, 사실 분석을 위해서는 약간의 고생(?)이 필요해 보입니다. 

주요 컬럼

받아온 데이터에는 여러 칼럼 존재하지만, 제 블로그 분석에서 필요한 데이터의 정보는 아래와 같습니다.

• event_date : 이벤트가 기록된 날짜(앱에 등록된 시간대의 YYYYMMDD 형식)
• event_timestamp : 클라이언트에서 이벤트가 기록된 시간(단위: 마이크로초, UTC)
• event_name : 이벤트 이름
• event_params.key : 이벤트 매개변수의 키
• event_params.value.string_value : 이벤트 매개변수의 문자열 값
• event_params.value.int_value : 이벤트 매개변수의 정수 값
• user_pseudo_id : 사용자의 가명 처리된 ID(예: 앱 인스턴스 ID)
• device.category : 기기 카테고리(모바일, 태블릿, 데스크톱)
• device.mobile_brand_name : 기기 브랜드 이름
• device.mobile_model_name : 기기 모델 이름
• device.operating_system : 기기의 운영체제
• device.operating_system_version : OS 버전
• device.language : OS 언어
• device.web_info.browser : 사용자가 콘텐츠를 본 브라우저
• device.web_info.browser_version : 사용자가 콘텐츠를 본 브라우저의 버전
• geo.continent : 이벤트가 보고된 대륙(IP 주소 기준)
• geo.country : 이벤트가 보고된 국가(IP 주소 기준)
• geo.region : 이벤트가 보고된 지역(IP 주소 기준)
• geo.city : 이벤트가 보고된 도시(IP 주소 기준)

---

2. 분석

날짜별 사용자 수 대비 첫 사용자 비율

select 
	event_date,
    	count(distinct user_pseudo_id) as user_cnt,
    	count(case when event_name = 'first_visit' then user_pseudo_id else null end) as new_user_cnt,
    	concat(round(100.0 * count(case when event_name = 'first_visit' then user_pseudo_id else null end)/count(distinct user_pseudo_id),0),"%") as new_user_percent
from `analytics_310464334.events_*`
where _TABLE_SUFFIX between '20221009'and'20221013'
group by event_date
order by event_date;

우선, 제 블로그의 일별 새 사용자의 비율을 알기 위한 쿼리를 작성하였습니다. 

※ 참고
new_user_cnt : case 문을 사용하여 event_name 이 first_visit 인 user_pseudo_id를 count
where 절 : 일자별 table을 엮기 위해 _TABLE_SUFFIX 사용 ( FROM 절 : event_ 다음 * 인 것도 주목) 

'블로그'라는 사이트 특성상 새 사용자 수가 너무 적어도 문제, 많아도 문제인데요.  

대략 70%의 새 사용자, 30%의 기존 사용자가 있는 것을 알 수 있습니다. 제가 보기에는 준수한 비율처럼 보입니다.

---

최다 조회 사용자의 특성은?

만약 제 블로그의 vip 고객을 위해 맞춤 선물을 하는 이벤트를 열기로 했다면 , 그 사용자의 특성을 알아야 합니다.

with user_visit_count as (
select 
	user_pseudo_id,
  	count(case when event_name = 'session_start' then 1 else null end) as visit_cnt
from `analytics_310464334.events_*`
where _TABLE_SUFFIX between '20221009'and'20221013'
group by user_pseudo_id
order by visit_cnt desc
)
select *
from user_visit_count

우선, 5일간 최다 조회자를 찾는 쿼리를 작성하였습니다. 방문 기준은 session_start를 기준으로 하였습니다. 이번 쿼리문에선 굳이 with 절을 쓰지 않아도 되지만, 나중에 더해질 쿼리 문의 가독성을 위해 with 절로 작성했습니다.

2053913881 유저님이 감사하게도 7번 session_start를 해주셨습니다. 

어떤 페이지로 접속하셨는지 궁금해지는데요? 한번 알아보겠습니다.

with user_visit_count as (
select user_pseudo_id,
  count(case when event_name = 'session_start' then 1 else null end) as visit_cnt
from `analytics_310464334.events_*`
where _TABLE_SUFFIX between '20221009'and'20221013'
group by user_pseudo_id
order by visit_cnt desc
)
select 
  a.user_pseudo_id,a.visit_cnt,
  b.event_date,
  timestamp_seconds(cast(cast(b.event_timestamp as INT64)/1000000 as INT64)) + interval '9' hour as event_time_stamp,
  b.event_name,
  b.event_params
from user_visit_count as a left join `analytics_310464334.events_*` as b
on a.user_pseudo_id = b.user_pseudo_id 
where a.user_pseudo_id = '2053913881.1650861985' and event_name = 'session_start'

제가 만든 user_visit_count 테이블에 기존 데이터 테이블을 join 하였습니다. 

※ 참고
timestamp_seconds 함수를 사용하여 기존 event_timestamp 열을 보기 쉽게 변환했습니다. 그리고 + interval '9'  hour로 한국 시간으로 시간을 맞추었습니다

그럼 결과로, 저희 블로그의 vip 고객님인 2053913881 유저님은 어떤 페이지로 접속하셨는지 알아보겠습니다. 

음... 알아보고 싶은데 결과의 빨간 박스 부분이 너무 지저분하네요.. 구글이 자랑하는 nested 구조이지만, 저는 event_params.key의 page title만이 알고 싶습니다. 약간의 코드를 추가하여 깔끔하게 만들어 보겠습니다.

with user_visit_count as (
  select user_pseudo_id,
    count(case when event_name = 'session_start' then 1 else null end) as visit_cnt
  from `analytics_310464334.events_*`
  where _TABLE_SUFFIX between '20221009'and'20221013'
  group by user_pseudo_id
  order by visit_cnt desc
),
unnest_data as (
  select user_pseudo_id,event_date,event_timestamp,event_name,event_params.key,event_params.value
  from `analytics_310464334.events_*`,UNNEST(event_params) AS event_params
  where user_pseudo_id = '2053913881.1650861985' and event_name = 'session_start' and key = 'page_title' 
)
select 
  a.user_pseudo_id,a.visit_cnt,
  b.event_date,
  timestamp_seconds(cast(cast(b.event_timestamp as INT64)/1000000 as INT64)) + interval '9' hour as event_time_stamp,
  b.event_name,
  b.key,
  b.value.string_value
from user_visit_count as a left join unnest_data as b
on a.user_pseudo_id = b.user_pseudo_id
where a.user_pseudo_id = '2053913881.1650861985' and event_name = 'session_start'
order by event_date

unnest_data라는 임시 테이블을 추가하고, user_visit_count와 unnest_data를 join 했습니다.

• unnest의 개요와 사용법은 zzsza 님의 글에서 확인 해 주세요!

드디어 결과를 보겠습니다!

2053913881님.. aws [시작] 글에서 출발해서 뭘 그렇게 보셨나요.. 궁금합니다...!! ㅋㅋㅋㅋ

time_stamp의 시간도 충분히 떨어져 있는 걸로 봐서는 여러 번 접속한 게 확실히 맞는 것 같은데요..!

 

 where 절의 session_start를 in (session_start, page_view)로 바꿔서.. 결과만 보여드리겠습니다.
(부끄러우실까봐 길게 설명은 안하겠습니다..ㅎ)

 

다시 돌아와서, 제가 알고 싶은 정보는 2053913881 유저님의 특성입니다. 특성을 뽑아보겠습니다.

select 	user_pseudo_id,device.category,	device.mobile_brand_name,device.mobile_model_name, device.operating_system,device.operating_system_version,device.language,device.web_info.browser,geo.country
from `analytics_310464334.events_*` 
where user_pseudo_id = '2053913881.1650861985'
limit 1

편의를 위해 쿼리 후 엑셀에서 피봇팅 하였습니다.

데스크톱 - 윈도 10 - 크롬으로 접속하시고, 서울에 사시는 2053913881님! 혹시 이 글을 보신다면 댓글 남겨주세요!

커피라도 보내드리고 싶네요ㅎㅎ

---

외국에서 접속한 사용자가 본 페이지는?

가끔 외국에서 제 블로그에 접속했다는 로그가 수집되면, 이분들은 누구인지?, 어떤 경로로 어떤 페이지를 보았는지 궁금했습니다.

이번 쿼리로 알아보겠습니다.

select 
  user_pseudo_id,
  geo.country,
  geo.region,
  device.language,
  event_name,
  event_params.value.string_value
from `analytics_310464334.events_*`,UNNEST(event_params) AS event_params
where event_name ='page_view' and key in ('page_title','page_referrer') and geo.country not in ('South Korea')

geo.country는 한국이 아니고, key값으로 page_title과 page_referrer을 조건으로 하여, 제 블로그에서 본 페이지와 그 전 페이지를 알아보겠습니다.

분석 기간 중엔 5분이 일본 지역에서 접속하셨습니다. 1026842844님은 일본의 치바현에서 접속해주셨네요! 하지만 언어가 한국어인걸 보니, 한국분인 것 같습니다.

이외의 4분은 나라는 일본이나, 지역명이 없고, 언어는 영어인 점이 이상합니다. 그리고 page_referrer 가 daum 인 것을 보아 이분들 역시 한국인임을 알 수 있습니다.

 

4분의 공통점은 referre가 daum 인 것인데요, daum에서 접속한 사람들의 laguage를 따로 보겠습니다.

select 
  user_pseudo_id,
  geo.country,
  device.language,
  event_params.key,
  event_params.value.string_value
from `analytics_310464334.events_*`,UNNEST(event_params) AS event_params
where event_name ='page_view' and key ='page_referrer' and value.string_value like '%daum%'
order by event_timestamp

where 절에 value.string_value like '% daum%'으로  page_regerrer에 daum이 포함된 사람을 추출하였습니다.

daum에서 접속한 사람들의 country와 language 가 일치하지 않는 것을 볼 수 있습니다. 물론 표본이 작지만, daum에서 제 블로그를 접속하면 로그 수집에 오류가 발생한다고 의심할 수 있습니다. 앞으로 사용자 분석 시에 이 점을 유의해야겠습니다.

---

4. 마치며

제 블로그를 방문해주신 모든 분들 감사합니다! 덕분에 이렇게 재미있는 분석을 할 수 있었습니다 :)

GA가 보여주는 분석 외에도,  직접 분석 주제를 정하고, 필요한 데이터를 정하고, 쿼리문을 짜며 고민하니까 데이터에 더 정이 가는 것 같습니다. 

GA 정복하기 시리즈는 이번 글로 마무리하겠습니다. 읽어주신 모든 분들 감사합니다.

 

---

분석 코드는 깃허브에서도 보실 수 있습니다.

GitHub - hongwon2/BigQuery-Practice: 데이터 분석을 위한 빅쿼리SQL 연습 레파지토리입니다.

 

---

중요 : 아래 링크를 클릭해서 2편으로 이동해주세요!

2022.11.16 - [Specialist/Project] - BigQuery로 분석한 Hongwon's Data 블로그 [2]

BigQuery로 분석한 Hongwon's Data 블로그 [2]