다음의 kaggle 데이터를 활용해서 Google Merchandise Store 분석을 진행해본다.
[ 목차 ]
1. 빅쿼리에서 데이터셋 샘플링
해당 데이터셋은 2016년 8월 1일 ~ 2017년 8월 1일까지 총 1년간의 하루 ga 세션 데이터셋을 담고 있기 때문에 데이터양이 방대하다.
GCP에서 빅쿼리를 활용해 데이터셋 샘플링을 먼저 진행하는 것을 고려했다.
테이블 샘플링은 어떻게?
먼저, 빅쿼리의 일반적인 샘플링 방식은 크게 2가지로 나뉘는데, 아래와 같다.
•
RAND() 방식과
•
TABLESAMPLE() 방식
RAND() 방식의 경우, 전체 테이블을 스캔하기 때문에 방대한 테이블이 많고, 데이터양이 많다면 오랜 시간과 비용이 발생한다. 따라서,
테이블 샘플링을 선택해서 진행해보도록 했다.
테이블 샘플링의 동작 방식
간단하게 말하자면, 빅쿼리 테이블은 적재될 때 데이터 블록들로 구성되어 저장된다. TABLESAMPLE 은 이 데이터 블록을 임의추출하여 해당 블록의 데이터를 모두 읽는 방식이다.
이때, 하나의 데이터 블록에 저장되는 행의 개수에 따라 샘플링의 기본 단위가 결정된다. 따라서, 사용자가 샘플링의 비율을 1%, 10%,,, 등 명령을 해도, 원하는 만큼의 데이터가 샘플링되지 않을 수 있다는 것이다.
즉,
•
블럭의 기존 단위 수보다 샘플링할 개수가 많으면 블럭의 최소 단위 수까지만 다운 샘플링
•
블럭의 기존 단위 수보다 샘플링할 개수가 적으면 블럭의 최소 단위 수까지 업샘플링
하게 된다.
또, 주의해야 될 점은 블럭에 저장된 행의 개수는 테이블마다 상이하고 같은 테이블의 블럭이라고 하더라도 블럭 내 행의 개수는 조금씩 차이가 날 수 있다는 것이다. rand() 나 tablesample 은 모두 재현성이 없다. 샘플링을 반복할 때마다 매번 데이터가 달라진다.하지만 정확한 비율로 샘플링을 하고 싶다면, 대안이 있다. → limit 와 tablesample 을 혼용해주면 해결된다.
예를 들어, 전체 데이터 셋의 1% 를 추출하고 싶지만 기존 블럭의 최소 단위수보다 크다면 원하는 샘플개수보다 적게 샘플링 된다. 따라서, 2% 로 tablesample을 업샘플링한 후, limit으로 원했던 1%의 크기수만큼 절단해주면 해결된다.
이번 프로젝트에서, 이 점을 참고해서 다음을 비교해봤다.
•
1% 의 테이블 샘플수
SELECT count(1)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` TABLESAMPLE SYSTEM (1 PERCENT)
SQL
복사
결과 : 11192 개
•
10% 의 테이블 샘플수
SELECT count(1)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` TABLESAMPLE SYSTEM (10 PERCENT)
SQL
복사
결과 : 91853 개
•
rand와 tablesample 결합
SELECT COUNT(1) FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` TABLESAMPLE SYSTEM (10 PERCENT)
WHERE RAND() < 0.1
SQL
복사
결과 : 8564
여기서, 문제는 샘플링을 매번 반복할 때마다 매번 다르게 추출되기 때문인데,
이에 대안으로 나온 것이 FARM_FINGERPRINT 함수였다.!
•
FARM_FINGERPRINT
해시 함수. 문자열을 입력받으면 숫자로 반환한다. 보통 해시 결과로 나온 값을 10으로 나눠서 나머지에 기반해 샘플링을 한다고 한다.
이를 적용해 1% 샘플링을 진행해봤다.
SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE MOD(ABS(FARM_FINGERPRINT(fullVisitorId)),100) < 1
SQL
복사
9146 개의 데이터셋이 샘플링됐다.
→ 해당 데이터셋을 GA_for_eda 데이터베이스 내 ga_sample10 이라는 이름으로 테이블에 저장해줬다.
2. Colab과 연동후 작업
쿼리 결과 데이터셋을 → 데이터 탐색 → 구글 코랩으로 연동
을 하게 되면 코랩이 열린다.
화면에 다음과 같이 나오게 되는데,
•
설정값
•
연동된 SQL 쿼리문 보기
•
연동된 결과 테이블을 dataframe으로 변경
•
describe() 함수로 통계 값 보기
를 알려주고 있다.
1.
setup
# @title Setup
from google.colab import auth
from google.cloud import bigquery
from google.colab import data_table
project = 'just-skyline-348007' # Project ID inserted based on the query results selected to explore
location = 'US' # Location inserted based on the query results selected to explore
client = bigquery.Client(project=project, location=location)
data_table.enable_dataframe_formatter()
auth.authenticate_user()
Python
복사
2.
연동된 SQL 쿼리문 보기
# Running this code will display the query used to generate your previous job
job = client.get_job('bquxjob_3573e484_188d2e71052') # Job ID inserted based on the query results selected to explore
print(job.query)
Python
복사
get_job 함수에 Job_id 입력한다. 그리고 job.query를 통해 해당 쿼리문을 출력할 수 있다.
→ 빅쿼리는 쿼리문마다 ID를 생성한다. 해당 Job_id는 bigquery의 쿼리 작업 정보에서 확인할 수 있습니다.
3.
연동된 결과 테이블을 DataFrame으로 변경
# Running this code will read results from your previous job
job = client.get_job('bquxjob_3573e484_188d2e71052') # Job ID inserted based on the query results selected to explore
results = job.to_dataframe()
results
Python
복사
4.
isable_dataframe_formatter()를 추가로 입력하여 dataframe_format 끄기
# 결괏값의 데이터프레임 포맷 끄기
data_table.disable_dataframe_formatter()
Python
복사
GA - 히트(Hit) / 세션(Session)
히트 : GA 에서 가장 작은 데이터 단위, 사용자가 웹사이트에서 행동하는 모든 개별적 상호작용 → Hit라고 한다.
세션 : 사용자가 홈페이지에서 발생시킨 행동의 집합. 1회 방문 동안 발생시킨 히트의 집합
•
세션이 종료되는 기준
세션 시간은 default 로 30분으로 설정되어 있다.
기본적으로 30분 동안 아무런 활동이 없다면 세션은 종료된다.
→ 상호작용(행동)을 할 때마다 해당 시점으로부터 30분을 더한 시간이 세션 유효시간이라고 생각하면 된다.
단, 예외가 있다.
◦
캠페인이 변경될 때
웹사이트 유입 경로가 변경 되면 세션은 종료된다.
즉, 네이버를 통해 방문했다가 조금 뒤에 인스타그램을 통해 웹사이트를 방문하면 세션은 2개가 된다.
◦
날짜가 변경될 때
날짜가 변경되면 30분이 되지 않아도 새로운 세션으로 계산한다.
예를 들어 11시 58분 pm에 방문했다가 12:03 am 에 웹사이트를 떠나면, 첫번째 세션은 11시 59분 59초에 완료되고, 12:00 am 에 두번째 세션이 시작되는 것이다.
◦
브라우저가 변경될 때
브라우저가 달라지면 세션도 달라진다. 즉, 크롬으로 웹사이트에 방문하다가, 익스플로러로 또 방문하면 세션은 2개가 된다.
→ 세션이 종료되면, 다시 방문시 새로운 방문자로 취급할 것이다.
3. 데이터 전처리
Nested and Repeated Data
Explore and run machine learning code with Kaggle Notebooks | Using data from Google Analytics Sample
https://www.kaggle.com/code/alexisbcook/nested-and-repeated-data
위의 노트북 튜토리얼을 보고 작성했다.
Bigquery Nested 필드 다루기
먼저, 불러드린 데이터셋을 보면, 특정 필드안에 다수의 필드가 들어가 있는 형태로 이루어진 것을 확인할 수 있다.
이를 Nested 필드라고 부르는데 타입 STRUCT 또는 RECORD 로 이뤄져 있다.
예를 들어서 위의 테이블의 경우, Toy 필드가 네스티드 필드인데, 구조를 살펴보면 오른쪽 그림과 같이 mode 가 RECORD로 되어 있는 것을 확인할 수 있다.
따라서, Toy 필드의 Name이나 Type에 접근하고 싶다면,
•
Toy.Name 또는 Toy.Type 처럼 사용해주면 된다.
다음으로, REPEATED가 문제다.
Toys 필드를 repeated data 라고 하는데, 하나 이상의 값을 가지고 있기 때문이다.
테이블 스키마를 보면, Toys 필드의 mode가 ‘REPEATED’ 로 나타나는 것을 확인할 수 있다.
해당 필드를 쿼리에서 문제 없이 사용하려면 UNNEST() 함수 안에 해당 필드를 넣어서 불러와주면 된다.
→ 이런 Repeated 데이터의 경우, UNNEST 를 하게 되면 행이 그만큼 늘어나게 된다는 단점이 있다.
그렇다면, Nested 와 Repeated 가 혼용되어 있는 경우 는?
요렇게, 만들어버린다.
위의 UNNEST() 함수를 통해서 데이터를 펼쳐낼 수 있다. 또한, Toys 필드에 alias 를 붙여줬기 때문에, nested 되어 있는 필드를 사용할 때 해당 alias 를 붙여서 사용하면 된다.
필드 설명
값이 존재하지 않는 clientId, visitorId, userId 는 삭제했다. 해당 필드에 데이터가 왜 없는지 확인해봤다.
•
visitorId: 사용하지 않는 필드라고 한다. 대신 fullVisitorId 를 사용하면 된다.
•
clientId : Unhashed version of the Client ID for a given user associated with any given visit/session. → 값이 없는 이유가 Unhashed 라서 그런 것으로 파악된다.
•
userId : Overridden User ID sent to Analytics.
•
고유한 유저를 나타낼 수 있는 변수 : visitId + fullVisitorId
•
totals.visits : 해당 세션이 유의미한 세션인지(즉, 활동하는지)의 여부 1이면 활동, 0이면 이탈
•
totals.transactions : 거래가 된 세션이라면 ≥ 1
•
나머지 변수들의 설명부터 살펴보자.
변수 | 설명 |
fullVisitorId | 고유한 방문 id |
visitNumber | 유저의 세션 넘버. 첫 세션이면 1 로 표시된다. |
visitId | 해당 세션의 id. fullVisitorId 와 같이 쓰면 모든 행을 구분할 수 있다. |
visitStartTime | 방문한 시간(posix time으로 표현, timestamp) |
date | 세션이 찍힌 날짜 |
totals.bounces | 전체 이탈 횟수. 이탈한 세션은 1, 아니면 null 값이 찍힌다. |
totals.hits | 세션 안에 hits 수 |
totals.newVisits | 세션 안에 전체 신규유저수. 첫방문이면 1, 아니면 Null값이 찍힌다. |
totals.pageviews | 세션 안에 전체 페이지뷰수 |
totals.screenviews | 세션 안에 전체 스크린뷰수 |
totals.sessionQualityDim | 해당 세션의 전환율을 1 ~ 100 사이의 값으로 추정한 것. 0일 경우 계산되지 않은 것을 의미 |
totals.timeOnScreen | The total time on screen in seconds. |
totals.timeOnSite | Total time of the session expressed in seconds. |
totals.totalTransactionRevenue | 총전환 수익. |
totals.transactionRevenue | deprecated. → 보일 시 삭제할 것 |
totals.transactions | 세션 안에 총 거래된 수 |
totals.UniqueScreenViews | 세션 안에 고유한 스크린뷰 수 |
totals.visits | 총 세션의 수. 활동을 하면 1, 활동을 하지 않게되면 null값이 찍힌다. |
trafficSource.adContent | 광고 컨텐츠 트래픽 |
trafficSource.adwordsClickInfo | 구글광고클릭과 관련된 정보가 담겨있음. |
trafficSource.
adwordsClickInfo.adGroupId | The Google ad-group ID. |
trafficSource.
adwordsClickInfo.adNetworkType | 네트워크 타입. {“Google Search", "Content", "Search partners", "Ad Exchange", "Yahoo Japan Search", "Yahoo Japan AFS", “unknown”} |
trafficSource.isTrueDirect | 직접적인 유입인지 아닌지(boolean)
- 웹사이트 직접 치고 들어오거나
- 북마크를 통해서 들어오거나
- 2 successive but distinct sessions have exactly the same campaign details.
위의 경우가 아니면 null |
trafficSource.campaign | 캠페인 value |
trafficSource.campaignCode | Value of the utm_id campaign tracking parameter |
trafficSource.keyword | The keyword of the traffic source.
usually set when the trafficSource.medium is "organic" or "cpc". |
trafficSource.medium | The medium of the traffic source. Could be "organic", "cpc", "referral” |
trafficSource.referralPath | If trafficSource.medium is "referral", then this is set to the path of the referrer. |
trafficSource.source | The source of the traffic source. Could be the name of the search engine, the referring hostname, or a value of the utm_source URL parameter. |
socialEngagementType | Engagement type("Socially Engaged" / "Not Socially Engaged") |
channelGrouping | The Default Channel Group associated with an end user's session for this View. |
device.browser | 브라우저 유형 ("Chrome" or "Firefox") |
device.deviceCategory | 디바이스 종류(모바일/ 태블릿/ 데스크탑) |
device.isMobile | deprecated → 삭제 |
hits.eCommerceAction | contains all of the ecommerce hits that occurred during the session, repeated field |
hits.eCommerceAction.action_type | The action type. Click through of product lists = 1, Product detail views = 2, Add product(s) to cart = 3, Remove product(s) from cart = 4, Check out = 5, Completed purchase = 6, Refund of purchase = 7, Checkout options = 8, Unknown = 0. |
hits.hour | hit이 발생한 시각 (0 to 23). |
hits.isEntrance | 첫 페이지뷰이거나 첫 스크린뷰이면 True |
hits.isExit | 마지막 페이지뷰이거나 마지막 스크린뷰이면 True |
hits.minute | hits.minute |
hits.product.isImpression | 적어도 한 유저가 제품 리스트에서 상품을 보면 True |
hits.product.isClick | 제품 리스트에서 유저가 해당 제품을 클릭하는지 |
hits.product.productListName | 해당 프로덕트가 어디서 보여지는지, 클릭은 어디서 이뤄지는지 위치를 알려줌("Home Page Promotion", "Also Viewed", "Recommended For You", "Search Results List", etc) |
hits.appInfo.screenDepth | The number of screenviews per session reported as a string. Can be useful for historgrams. |
hits.item.localItemRevenue | Total revenue from this item in local currency, expressed as the value passed to Analytics multiplied by 10^6 (e.g., 2.40 would be given as 2400000). |
hits.item.currencyCode | The local currency code for the transaction. |
hits.item.itemRevenue | Total revenue from the item, expressed as the value passed to Analytics multiplied by 10^6 (e.g., 2.40 would be given as 2400000). |
hits.item.itemQuantity | The quantity of the product sold. |
hits.item.productCategory | The category of the product. |
hits.item.transactionId | The transaction ID of the ecommerce transaction. |
hits.transaction.transactionRevenue | Total transaction revenue, expressed as the value passed to Analytics multiplied by 10^6. (e.g., 2.40 would be given as 2400000). |
hits.transaction.transactionId | The transaction ID of the ecommerce transaction. |
hits.product.v2ProductCategory | Product Category. |
hits.product.productRevenue | 상품의 수익, ga에 전달될 때는 * 10^6 를 해서 전달 |
hits.product.productRefundAmount | 상품의 반품 금액. * 10^6 를 해서 전달 |
hits.product.productQuantity | The quantity of the product purchased. |
hits.product.productPrice | 상품의 가격. * 10^6 |
hits.product.productListName | 상품이 어디서 보여지는지, 또는 어디서 클릭이 발생하는지("Home Page Promotion", "Also Viewed", "Recommended For You", "Search Results List", etc) |
hits.product.productBrand | 상품의 브랜드 |
hits.type | hits의 타입 (’page’ ,’transaction’, ’item’, ‘event’, ‘social’, ‘appview’, ‘exception’)
When you query time-related fields (e.g., hits.latencyTracking.pageLoadTime), choose hits.type as Event if you want to use hit.type in your queries. |
hits.publisher.dfpRevenueCpc | 광고주의 클릭 결과율에 따른 CPC 수익 |
hits.referrer | 해당 세션이 성공했거나 거래성사되었다면 referring page 가 찍힘. 같은 도메인이라면 blank |
hits.refund.refundAmount | 반품된 금액, * 10 ^ 6 |
필드에 대한 궁금증 •
customdimension ?
•
custommetrics ?
•
hits.publisher
→ 광고를 게시할 수 있는 웹/앱 플랫폼에서 수집되는 로그 데이터로 보인다.
변수 타입, 형태 변환 전처리
•
unix time 형태인 visitStartTime → timeStamp 으로 변환하기
•
visitId와 fullVisitorId 합쳐서 unique_sessionId 만들기
•
세션이 자정을 넘어갈 경우, visitStartTime 은 두번 찍힌다.→ 예를 들어, 2/9 → 2/10
즉, visitStartTime 은 정확한 시간대 값이 찍히지만, visitId는 동일한 세션 값으로 찍히게 된다.
따라서, unique한 session을 구하고 싶다면 visitId 와 fullVisitorId 를 합쳐주면 된다.
다음은 unique_sessionId 를 만든 결과다
→ 그렇다면 visitStartTime 과 fullVisitorId 를 합치면 뭐가 되냐?
사용자가 들어와서 자정을 넘겨 사용하게 되면 사실상 세션 1개인데, 이를 2개로 인식한다고 보면 된다. 그냥 찍힌 Session 전체 라고 보면 된다. (하지만 고유하지는 않음)
Practice - 국가별 수입 분석
%%bigquery --project majestic-gizmo-390314
select geoNetwork.country AS country,
SUM(totals.totalTransactionRevenue) / 1e6 AS revenue,
ROUND(100 * (SUM(totals.totalTransactionRevenue) / (SELECT SUM(totals.totalTransactionRevenue) FROM GA_for_eda.ga_sample10 where totals.totalTransactionRevenue IS NOT NULL)),2) AS revenue_pct
from GA_for_eda.ga_sample10
where totals.totalTransactionRevenue IS NOT NULL
GROUP BY 1
ORDER BY 3 DESC
Python
복사
수입 비율을 확인해보니, 미국이 96%를 차지하고 나머지 국가들의 비중은 합쳐도 10%가 안되는 것을 확인했습니다.
4. 가능한 분석 주제
4-1. 유입분석
•
유입기기별 유입 분석
모바일, 데스크탑, 태블릿별 유입 비율을 확인해볼 수 있습니다.
%%bigquery --project majestic-gizmo-390314
select device.deviceCategory AS device,
COUNT(1) / (SELECT COUNT(*) FROM GA_for_eda.ga_sample10) AS session_per_device
from GA_for_eda.ga_sample10
GROUP BY device
Python
복사
유입기기 비율을 확인해보니 데스크탑 비중이 약 75% 로 압도적인 것을 확인했습니다. 그외 모바일 22%, 태블릿은 약 3% 정도인 것을 확인했습니다.
모바일의 유입 비율이 적은 이유 GA 에서의 모바일 로그가 정확하지 않다는 문제 
앱에서는 웹에서 앱으로 넘어오는 순간 쿠키를 통한 사용자에 관한 정보가 단절되기 때문에 해당 사용자에 대해 유입경로 분석을 하기 어렵다. → 트래킹의 부정확성4-2. 방문수 & 페이지뷰 수 분석
웹페이지의 방문수와 페이지뷰 수 현황을 파악해보기로 했습니다.
방문자 수(UA)와 페이지뷰 수를 왜 봐야하나 사이트의 전반적인 성장 추세를 확인해볼 수 있고, 세부적인 분석을 진행하는데 있어서 기본이 되는 지표입니다.
사이트의 매력도를 나타내기 때문에 지속적으로 모니터링할 필요가 있습니다.
사이트의 매력도를 나타내기 때문에 지속적으로 모니터링할 필요가 있습니다. 추가적으로 가능한 분석 이때 재방문자수의 비율과 신규방문자의 비율 추이를 추가적으로 확인해서, 해당 기간에 충성 고객을 늘려가고 있는 마케팅 시점이었던건지, 신규 고객을 끌어들이기 위한 비용이 더 많이 지출되고 있던 시점이었는지 추가적으로 확인해볼 수도 있습니다.
방문 수와 페이지뷰 수에 따른 해석 CASE 1 : 방문수 ↑ , 페이지뷰 수 ↑
CASE 2 : 방문수 ↑, 페이지뷰 수 ↓
CASE 3 : 방문수 ↓, 페이지뷰 수 ↑
CASE 4: 방문수 ↓, 페이지뷰 수 ↓
4-2-1. Google Merchandise Store 데이터에 적용하기
1.
방문수 & 페이지뷰 수
세션과 방문을 구분하는 것에서부터 시작했습니다!
GA 에서 [세션 / 페이지뷰 / 방문] 구별하기
적용 A.
→ visitId 와 fullVisitorId 를 결합한 unique_sessionId 를 생성
적용 B.
•
hits.isEntrance : 이 조회가 세션의 첫 번째 페이지뷰 또는 화면 조회인 경우 true로 설정됩니다.
•
unique_sessionId 의 개수를 counting 했습니다.
%%bigquery --project majestic-gizmo-390314
with unique_df as (
select *,
concat(cast(visitId as string),'_', fullVisitorId) as unique_sessionId
from GA_for_eda.ga_sample10
), Entrance_df as (
select unique_sessionId
from unique_df,
unnest(hits) as hits
where hits.isEntrance = True
select count(distinct unique_sessionId) as tot_entrance
from Entrance_df;
SQL
복사
적용 C.
•
총페이지뷰수
%%bigquery --project majestic-gizmo-390314
with unique_df as (
select *,
concat(cast(visitId as string),'_', fullVisitorId) as unique_sessionId
from GA_for_eda.ga_sample10
), session_pageView as(
select max(totals.pageviews) as session_pageviews -- 세션별 총페이지뷰수
from unique_df
group by unique_sessionId
)
select sum(session_pageviews)
from session_pageView;
SQL
복사
적용 D.
•
월별 방문수, 페이지뷰수 추이 확인
2.
MAU
월간 기준으로 웹사이트를 방문한 사용자 중 적어도 한 번 이상 상호작용한 사용자수를 나타낸다.
→ 상호작용이란?
Hit와 관련된 값들인데, 페이지뷰, 이벤트, 전자상거래 등을 모두 포함한다.
이때, 중요한 점은 Active User 에서 ‘Active’(상호작용) 를 어떤 기준으로 세울 것인가 하는 부분이다.
GA 의 경우, hits 관련한 필드에는 대표적으로 아래 정보가 있는데,
- hits.eCommerceAction : 이커머스 활동과 관련된 모든 hit 정보
- hits.isInteraction : 해당 hit이 interaction일 경우, True.
hits.isInteraction 을 계산 편의상 사용하기로 했다.
순서
•
브라우저별 transaction 비율
%%bigquery --project majestic-gizmo-390314
SELECT
device.browser,
SUM ( totals.transactions ) / (SELECT SUM(totals.transactions) FROM GA_for_eda.ga_sample10) AS total_trans_rate
FROM GA_for_eda.ga_sample10
GROUP BY
device.browser
ORDER BY
total_trans_rate DESC
Python
복사
•
사용자 유형
신규유저, 재방문자의 비율을 확인해볼 수 있습니다.
◦
월별 새 사용자 비율
%%bigquery --project majestic-gizmo-390314
SELECT
FROM GA_for_eda.ga_sample10
GROUP BY
Python
복사
4-3. 페이지뷰 수
총페이지뷰수
웹사이트의 매력도를 파악할 수 있고, 추가적인 세부 분석을 진행하기에 앞서 흐름을 파악할 수 있음.
페이지별 유입량에 따른 매력도를 확인할 수 있으며, 어떤 시간대에 페이지로 자주 유입되는지도 파악해볼 수 있다.
페이지뷰수
페이지뷰수를 계산하는 일반적인 방법 및 BenchMark
세션당 고객의 평균 구매수
%%bigquery --project majestic-gizmo-390314
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM GA_for_eda.ga_sample10
WHERE totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId)
Python
복사
•
이탈률
◦
유입 소스별 이탈률
%%bigquery --project majestic-gizmo-390314
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM GA_for_eda.ga_sample10
GROUP BY
source )
ORDER BY
total_visits DESC
Python
복사
•
전환율 / 재방문자 전환율
•
유입 키워드
유입에 기여한 키워드를 확인해볼 수 있습니다.
•
가장 애용되는 페이지, 각 페이지별 이탈률
SELECT
hits.page.pagePath AS landing_page, -- 페이지의 url path
COUNT(*) AS views,
SUM(totals.bounces)/COUNT(*) AS bounce_rate -- 페이지별 이탈률
FROM GA_for_eda.ga_sample10,
UNNEST(hits) AS hits
WHERE hits.type='PAGE' -- 이벤트 타입 : page
AND
hits.hitNumber=1 -- 세션의 첫 hit
GROUP BY landing_page
ORDER BY views DESC
LIMIT 10
Python
복사
→ ‘이탈률’이 특정 사이트에 방문한 사용자가 다른 페이지로 이동하지 않고 떠난 비율을 가리킨다면, ‘종료율’은 특정 사이트에서 고객이 여러 개의 페이지를 이동한 뒤 사이트를 떠난 비율을 말한다.