커뮤니티2

[Google Analytics] 구글애널리틱스 커뮤니티입니다. 구글애널리틱스관련 정보를 공유 해 주세요!

안녕하세요 도치쀼 입니다 :) 

이전 글에서 '엑셀 시트와 빅쿼리를 연결하는 방법' 에 대해서 공유 드렸었는데

이번에는 제가 그 연동 이후에 위클리 리포팅을 위해 작업한 쿼리를 공유드리려 합니다.

이 글을 조회하는 분들이 저와 동일한 리포팅 폼을 활용할 가능성은 낮더라도 

비슷한 고충이 있으시다면 참고하시길 바라며 이어서 적어보겠습니다 :) 

(쿼리 초보인데 좀 더 효율성을 높히는 방법이 보이신다면 언제든 피드백 부탁드려요~! )

우선 쿼리를 공유드리기 앞서 

제가 왜 위클리 리포팅에 쿼리를 활용하려 했고, 또 구체적으로 어떤 값들을 나오게 만드려는지 공유드리겠습니다.

아래의 포맷은 제가 리포팅하는 캠페인의 

유입 세션수, 캠페인 내 이벤트 수, 캠페인 외의 구매 관련 행동 등에 대해 트렌드를 파악하는 리포트의 형식 입니다.

원래는 엑셀의 함수를 이것 저것 써가며 계산해오고 있었는데  빅쿼리에서 실행 버튼 클릭 한번으로

10개 국가의 세션수의 누적값 / 2초 이상 체류한 세션의 비율 / 금주 세션수 / 전주 대비 증감률을  구할 수 있다면...?

시도해 볼만 한 과정이죠 :)

기존에 제가 위의 리포팅을 작성할 때는 대시보드로 만든 테이블에서 값을 확인한 후, 

그 값을 엑셀로 옮겨와 가공하여, 궁극적으로 저 형태로 ppt 를 만들어야 했는데, 

값을 옮기는 과정에서 오류가 있을 경우 재작업을 한 적도 잦고, 

함수를 복사/붙여넣기하는 과정에서 값이 누락되는 상황도 종종 있었습니다.

허나 이전 글에서 공유드린 '구글 시트와 빅쿼리를 연동' 하게 되면  

실시간 연동을 통해 데이터를 빠르고, 정확하게 리포팅 할 수 있게 됩니다.

엑셀 함수 필요 없이 클릭 한 번 만으로요...! 

즉 ,이렇게 아래처럼 구글 시트만 있으면

<구글 시트 업데이트 예시 -- 1월 15-21일 (1월 3주차) 데이터 업데이트> 

 뿅 하고 원하는 결과값들을 한번에 얻을 수 있답니다 

<실행 후 예시 결과값 : 1월 3주차까지의 누적 세션수, 유효 세션수의 비중 , 이번주 총 세션수, 전주 대비 세션 증감률> 

[Let's Query!]

그럼 위의 리포팅에서 왼쪽 하단에 있는 Visit 항목들을 계산한 쿼리를 하나씩 파보겠습니다 :) 

Step 1

기존의 피벗 테이블 형식을 Unpivot 함수를 통해 풀어주고, sum () 윈도우 함수로 누적 세션수 값을 구해주는 테이블을 with 절로 만들어줍니다.

SELECT  

            PARSE_DATE('%Y-%m-%d',date)asdate,

            country,

            sessions,

            sum(sessions)over(partitionbycountryorderbydaterowsbetweenunboundedprecedingandcurrentrow)cumulated_sessions,

      FROM`lifesgood-test.lifesgood_weekly_reprot_format.organic_and_paid sessions_table`

      unpivot(sessions

              forcountry

              in(GLOBAL,KR,US,AU,DE,INDIA,SA,SAEN,SA_SAEN,AE,AEAR,AE_AEAR,VN,BR,daily_total))

Unpivot 함수를 통해 아래처럼 country 라는 컬럼에 국가코드값이 넣어지면서 피벗테이블 형식이 풀렸죠?

누적값을 구할때는 국가별로 partition by 를해주고, 날짜 순서대로 누적할 것 이기 때문에 order by date 를 해줍니다

이 테이블을 unpivot_sessions 라고정의하고 with 절로묶어줍니다.

<+>

window 함수 sum 의 특성상 

위의 쿼리식과

sum(sessions)over(partitionbycountryorderbydate) 

아래의 식은 같은 결과를 보여줍니다. 

sum(sessions)over(partitionbycountryorderbydate rows between unbounded preceding and current row )  

Step 2

 2초 이상 체류한 세그먼트의 세션수(valid_sessions) 테이블도 위와 동일하게 unpivot 해 준 다음 누적값을 구하고,

 unpivot_sessions의  date, country 를 키 값으로 조인해줍니다.

Step 3

전주 대비 이주의 증감룰을구하기위해 lag 함수를통해 

일주일전의값과이번주의값을같은행에서계산할수있도록만들어 줍니다.

# LAG (sessions, 7) > sessions 의 7 번째 이전에 있는 값을 가져온다

 ifnull(lag(sessions,7)over(partitionbycountryorderbydate),0)aslag_one_week

#이때 null 값이 있으면 이후에 계산 시 오류가 발생하므로 ifnull 로 null 값들은 0 으로 만들어 줍니다. 

이어서별도로생성해뒀던 regular_time 테이블과 between join 을하여 7일마다다른주차별기준점값을설정해줍니다.

#Start_week 과 end_of_Week 를 7일간격으로설정한 regular_time table ‘between join’

<1월 1-7일은 1월 1주차 (24.Jan_first_weeK) 1월 8일-14일은 1월 2주차 (24.Jan_second_weeK) 으로 구분되어 regular_time 열이 생긴다>

SELECT

            a.date,

            a.  country, 

            b.regular_time,

            a.sessionssessions,

            a.  cumulated_sessionscumulated_sessions,

                 ifnull(lag(sessions,7)over(partitionbycountryorderbydate),0)aslag_one_week

            a.cumulated_valid_sessionscumulated_valid_sessions,

FROMbasica

join  `lifesgood-test.test.test_weekly_regular_week`bona.datebetweenb.start_week

                                                             andb.end_of_week

Step 4

위의 테이블에서 일반 세션과 지난주 세션에 대해

country, regulartime 으로 partition by 되고 날짜 순으로 누적값을 구합니다.

select*,

         sum(sessions)over(partitionbycountry,regular_timeorderbydaterowsbetweenunboundedprecedingandunboundedfollowing)  sum_this_week_sessions,

         sum(lag_one_week)over(partitionbycountry, regular_timeorderbydaterowsbetweenunboundedprecedingandunboundedfollowing)sum_last_week_sessions,

frombase

Step 5

이제 위클리에서 실제로 사용하는 포맷과 좀 더 유사한 형식으로 만들어 줍니다.

최종적으로 구하려는 값은

누적세션수 (유효 세션의 비율) | 1월 3주차 7일간의 총 세션수 | 1원 2주차 대비 1월 3주차 세션수의 증감률 입니다.

*참고

STEP 4 에 있는 <예상 결과값> 테이블에서 Jan_third_week 에서 1월 21일 마지막 날짜 행의 값으로 확인 가능

cumulated_sessions 가            2,310 ------(누적 세션수, )

cumulated_valid_sessions 가  1,155 ------(유효 세션수)

sum_this_week 이                   1,260 ------(1월 3주차의 총 세션수)

sum_last_week 이                    770-------- (1월 2주차의 총 세션수)  

하단 좌측의 계산식으로 우측의 결과 값을 구할 수 있게 됩니다.

이 계산식을 쿼리로 작성해주면 되겠죠! 

select      

            country,

            regular_time,

            concat(cumulated_sessions,' (',round(cumulated_valid_sessions/cumulated_sessions*100,0),'%',')')ascumulated_sessions_and_valid_percentage,

            sum_this_week_sessions,

            concat(round(safe_divide((sum_this_week_sessions-sum_last_week_sessions),sum_last_week_sessions)*100,0),'%')aschange_percentage,

            row_number()over(partitionbycountryorderbydatedesc)asnumbering_cumulated_sessions

frombase_table

#safe_divide 를 쓰게 되면 분모가 0 이라 나눌 수가 없는 식이 나오더라도 무방

Step 6

일반적으로 리포팅은 가장 최근 7일을 기준으로 작성하지만

때때로 특정 기간에 대한 데이터를 고객사에 전달드려야 할 때도 있었는데요,

이를 위해서 각 주차별로 구분할 수 있는 값을 row_number 함수로 생성해주려 합니다.

* row_number () 를 통해서 각 나라별로 누적 세션값에 date 를 내림차순으로 하여 순위를 만들어 줍니다.

 해당 row_number () 값은 고정적인 값이 아니라, 새로운 데이터가 추가될 때마다 

 가장 최근 날짜의 값이 ‘1’ 로 설정되는 특징이 있습니다.

위와 같은 상태에서

     select*

     fromdata_table

     where(numbering_cumulated_sessions-1)/7+1  = 1        

     orderbycountry

>>   where(numbering_cumulated_sessions-1)/7+1 

      값이 ‘1’ 이면가장최근의주차별값,

      2라면 > 지난주 , 3이라면 지지난주

위와같이필터링할수도있고,

만약특정주차에상관없이특정날짜까지의 Vist 값들을원한다면

where date = '2024-01-21'   이렇게 where 절로 date 날짜를설정할수있습니다.

FIN

----------------------------------------------------------

이렇게 엑셀 작업으로 구해오던 값들을 쿼리로 표현해보았습니다 :) 

리포팅이 워낙 케이스 바이 케이스지만 

시간 절약과 정확성이 높아진 것들 경험하니

이후에도 비슷한 업무를 만나면 저는 쿼리를 쓰는 방법도 고민해볼 것 같아요 :)

(이런 방법도 있다는것 자체를 인지를 못했어서 엑셀 노가다만 계속 했었다는 ㅜㅜ) 

또 쿼리 초보에게는 

증감률 계산을 위한 lag () 함수, 

누적값 계산을 위한 윈도우 sum () 함수 를 직접 사용해 볼 수 있는 좋은 기회이기도 했답니다.

이 글 조회하시는 분도 저와 비슷한 고충이 있으셨다면 이런 선택지가 있다는게 도움이 되셨길 바라며

이번 글 마치도록 하겠습니다 :) 

그럼 좋은 하루 보내세요~!

-------------------------------------

*메모장이나 PDF 확장자 지원이 안되어서 ㅎㅎㅎ,,, JPG 로라도 총 쿼리 업로드 합니다