비밀번호

커뮤니티2

  • 맑음속초6.2맑음북춘천0.9맑음철원0.0맑음동두천2.9맑음파주-0.5맑음대관령-1.3맑음춘천2.1맑음백령도9.7맑음북강릉7.0맑음강릉6.9구름조금동해5.7맑음서울5.2맑음인천5.4맑음원주4.9비울릉도8.2맑음수원3.8맑음영월2.1맑음충주2.0맑음서산2.8맑음울진6.1맑음청주6.8맑음대전4.1맑음추풍령2.9맑음안동3.5맑음상주4.9맑음포항8.1맑음군산4.8맑음대구4.8맑음전주7.4맑음울산7.6맑음창원9.1맑음광주7.2맑음부산9.8맑음통영7.9맑음목포8.8맑음여수10.3맑음흑산도12.1맑음완도8.7맑음고창5.3맑음순천2.4맑음홍성2.6맑음서청주3.3맑음제주12.6맑음고산12.5맑음성산9.0맑음서귀포12.0맑음진주3.7맑음강화6.1맑음양평4.6맑음이천2.6맑음인제2.1맑음홍천1.8맑음태백-1.0맑음정선군0.5맑음제천0.1맑음보은1.0맑음천안1.7맑음보령4.3맑음부여2.8맑음금산2.1맑음세종4.0맑음부안4.9맑음임실2.5맑음정읍4.9맑음남원3.3맑음장수0.2맑음고창군4.1맑음영광군5.4맑음김해시5.9맑음순창군3.9맑음북창원7.9맑음양산시6.5맑음보성군7.5맑음강진군8.8맑음장흥5.1맑음해남9.3맑음고흥3.9맑음의령군3.2맑음함양군2.3맑음광양시7.3맑음진도군9.5맑음봉화1.0맑음영주1.9맑음문경7.0맑음청송군-1.0맑음영덕4.4맑음의성0.5맑음구미4.7맑음영천2.0맑음경주시2.9맑음거창1.7맑음합천3.8맑음밀양4.3맑음산청3.6맑음거제6.8맑음남해7.9맑음북부산5.3
  • 2024.11.07(목)

구글애널리틱스[Google Analytics]구글애널리틱스 커뮤니티입니다.

[업무 고충 해결] 빅쿼리와 구글 시트를 연동하여 엑셀 작업 간소화 하기 (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 를 키 값으로 조인해줍니다.