[GA] EDA

EDA 반쯤 슛~

Jun 20, 2023

1. GCP / Bigquery 탐색

💡

SK T아카데미에서 진행한 무료 빅쿼리 수업이 있어서 열심히 들어봤다. 최고당..

BigQuery시작하기 3강 - SQL Basics | T아카데미

BigQuery의 다양한 기능을 소개하고 BigQuery를 활용한 데이터 분석 방법에 대해 알아봅니다. 또한, 함께 사용할 수 있는 도구들에 대해 함께 소개합니다.

https://www.youtube.com/watch?v=_tF62kYpjxM&list=RDCMUCtV98yyffjUORQRGTuLHomw&index=3

🎴

BigQuery 시작하기

validator

check process byte (입력된 쿼리에 대한 조회 용량 확인)

콘솔에서 쿼리 입력 후 validator에서 확인

public dataset 접근

https://console.cloud.google.com/bigquery?project=bigquery-public-data&ws=!1m0

어휘 구조와 구문

StandardSQL : 표준 SQL 문법 (MySQL사용자에겐 좀 엄격한 편)

대소문자의 구분 없음 : 키워드, 기본 제공 함수, 열 이름, 쿼리 내 별칭

SELECT문 내 에 후행 쉼표 사용 가능

데이터 유형

numeric, string, bool

array, struct

date, datetime, timestamp, time

geography, json

interval, byte

날짜 및 시간, 구조체, 배열

배열 : 데이터 유형이 동일한 값으로 구성된 목록 (파이썬 list와 유사, 하나의 행에 데이터 타입이 동일한 여러 값이 저장됨)

구조체(struct) : 각각 유형(필수)과 필드 이름(선택사항)이 있는 정렬된 필드의 컨테이너 (빅쿼리 UI 내에서 record로 표시됨)

UNNEST : 배열을 평면화하는 작업


sql
-- ARRAY에 특정 값이 있는지 확인하고 싶은 경우
-- WHERE 절에서 UNNEST로 풀고, IN 사용
-- Scala를 선호하는 사람을 찾고 싶은 경우

WITH example_data AS(
      SELECT 
        'kyle' AS name,
        ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 
        31 AS age
      UNION ALL
      SELECT 
        'max' AS name,
        ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 
        29 AS age
      UNION ALL
      SELECT
        'yun' AS name,
        ['Python', 'SQL'] AS preferred_language, 
        28 AS age
      )
		
  SELECT 
    name, prefer_lang, age
  FROM example_data, UNNEST(example_data.preferred_language) as prefer_lang
```


WITH example_data AS(
      SELECT 
        'kyle' AS name,
        ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 
        31 AS age
      UNION ALL
      SELECT 
        'max' AS name,
        ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 
        29 AS age
      UNION ALL
      SELECT
        'yun' AS name,
        ['Python', 'SQL'] AS preferred_language, 
        28 AS age
      )
		
  SELECT 
    name, prefer_lang, age
  FROM example_data, UNNEST(example_data.preferred_language) as prefer_lang

SQL 안티패턴

데이터 편향 : 데이터가 균등하지 않은 크기의 파티션으로 분할된 경우

불균형 조인 : 조인 키의 불균형이 발생한 경우, 한 샤드에 많은 데이터가 몰려 오버로드 발생

교차 조인 : 입력보다 더 많은 출력을 생성하는 조인이 생성됨

단일 행을 업데이트 또는 삽입하는 DML문 : 일괄 업데이트 및 삽입 권장

SQL 최적화

Late Aggregating : 집계는 최대한 마지막 단계에서 할 것

Join Order : 큰 테이블부터 사용

Filter before Join : 조인 전에 필요없는 행은 필터링 하기

Where filter order : 먼저 선언된 순으로 데이터 필터링

2. GA dataset

2016년 8월 ~ 17년 7월 → 1년치 Google Merchandise 홈페이지에서 발생한 매출 데이터를 GA 360을 통해 수집함

Google Merchandise에서 자사의 브랜드 제품 판매하고 있음

How to Query the Google Analytics Sample Dataset

Explore and run machine learning code with Kaggle Notebooks | Using data from Google Analytics Sample

https://www.kaggle.com/code/paultimothymooney/how-to-query-the-google-analytics-sample-dataset/notebook?scriptVersionId=5165120

사용할 컬럼 정보

스키마를 참고해서, 데이터 값이 정상적으로 들어와있고 분석에 필요한 값만 선정했다

쿼리문


SELECT
  date AS Date,
  TIMESTAMP_SECONDS(VisitStartTime) AS VisitStartTime,
  CONCAT(FullVisitorId, CAST(VisitId AS STRING)) AS UniqueSessionId,
  FullVisitorId AS User,
  VisitId,
  VisitNumber,
  GeoNetwork.continent AS continent,
  geoNetwork.country AS country,
  geoNetwork.city AS city,
  TrafficSource.referralPath,
  TrafficSource.Source,
  TrafficSource.Medium,
  ChannelGrouping AS Channel,
  Device.deviceCategory AS device,
  Totals.newVisits AS newVisits,
  CAST(SUM(IFNULL(Totals.Transactions, 0)) AS INTEGER) AS Orders,
  CAST(SUM(IFNULL(Totals.PageViews, 0)) AS INTEGER) AS PageViews,
  ROUND(SUM(IFNULL(Totals.TimeOnSite, 0))/60, 2) AS DurationMinutes,
  SUM(IFNULL(Totals.TotalTransactionRevenue, 0))/1000000 AS Sales
FROM `study-1st.google_analytics_sample.ga_sessions_*`
WHERE 
  _TABLE_SUFFIX BETWEEN '20160801' AND '20170801' 
  AND trafficSource.source <> 'mall.googleplex.com'  -- remove internal traffic
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15
ORDER BY 3;

Timestamp

visitStartTime

date

사용자 식별자

fullVisitorId : Google 애널리틱스에서 제공하는 고유한 사용자 식별자이며, 하나의 사용자에게 고유하게 할당됨. 해당 사용자의 모든 세션에 대해 동일한 값을 갖게 됨.

visitId : 세션 ID

fullVisitorId + visitId 조합해서 UniqueSessionId 만듬

사용자 정보

geoNetwork.continent

geoNetwork.country

geoNetwork.city

유입 정보 (TrafficSource)

trafficSource.referralPath : medium이 referral인 경우, referrer path로 정해짐

trafficSource.source

trafficSource.medium

~~trafficSource.keyword : utm_term 혹은 cpc 광고의 키워드 —> 제대로 입력값이 안 들어와있음~~

ChannelGrouping : Organic Search, Paid Search 등

device.deviceCategory : mobile, tablet, desktop

trafficSource.adwordsClickInfo

방문 정보 (Total)

totals.newVisits : 세션당 신규유저 여부 (Yes면 1, 아니면 null)

totals.Transactions : 세션당 거래 수

totals.pageviews : 세션당 pageviews

totals.timeOnSite : 세션당 소요시간

totals.totalTransactionRevenue : 매출 Total transaction revenue, expressed as the value passed to Analytics multiplied by 10^6 (e.g., 2.40 would be given as 2400000).

확인 필요


-- STRUCT TYPE은 OFFSET을 사용해야함
hits[OFFSET(0)].eCommerceAction.action_type

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.eventInfo

hits.experiment

💡

확인 결과, 해당 데이터셋엔 제대로 데이터가 안 들어와있음

3. TIL

Bigquery는 신세계당 +_+ GCP 세계란!!!! MySQL과 조금 문법이 달라서 특히 차이점을 잘 정리해둬야겠다.

STRUCT, ARRAY 타입 데이터를 SQL로 만져본게 처음인데, 약간 생소하지만 크게 어렵진 않았다. 카일님의 BigQuery UNNEST, ARRAY, STRUCT 사용 방법 이 글은 도움이 많이 됬습니다..

코랩 <> 빅쿼리


#1000행만 불러오는 예시
!pip install google-cloud-bigquery
from google.cloud import bigquery
from google.colab import auth
import pandas as pd

# 인증 설정
auth.authenticate_user()

# 클라이언트 생성
client = bigquery.Client(project="study-1st")
dataset_ref = client.dataset("google_analytics_sample", project="bigquery-public-data")

# 쿼리 작성
query = """
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
LIMIT 1000
"""

# 쿼리 실행
df = client.query(query).to_dataframe()

# 결과 출력
df.head()

sql -- ARRAY에 특정 값이 있는지 확인하고 싶은 경우 -- WHERE 절에서 UNNEST로 풀고, IN 사용 -- Scala를 선호하는 사람을 찾고 싶은 경우 WITH example_data AS( SELECT 'kyle' AS name, ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 31 AS age UNION ALL SELECT 'max' AS name, ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 29 AS age UNION ALL SELECT 'yun' AS name, ['Python', 'SQL'] AS preferred_language, 28 AS age ) SELECT name, prefer_lang, age FROM example_data, UNNEST(example_data.preferred_language) as prefer_lang ```

WITH example_data AS( SELECT 'kyle' AS name, ['Python', 'SQL', 'R', 'Julia', 'Go'] AS preferred_language, 31 AS age UNION ALL SELECT 'max' AS name, ['Python', 'SQL', 'Scala', 'Java', 'Kotlin'] AS preferred_language, 29 AS age UNION ALL SELECT 'yun' AS name, ['Python', 'SQL'] AS preferred_language, 28 AS age ) SELECT name, prefer_lang, age FROM example_data, UNNEST(example_data.preferred_language) as prefer_lang

SELECT date AS Date, TIMESTAMP_SECONDS(VisitStartTime) AS VisitStartTime, CONCAT(FullVisitorId, CAST(VisitId AS STRING)) AS UniqueSessionId, FullVisitorId AS User, VisitId, VisitNumber, GeoNetwork.continent AS continent, geoNetwork.country AS country, geoNetwork.city AS city, TrafficSource.referralPath, TrafficSource.Source, TrafficSource.Medium, ChannelGrouping AS Channel, Device.deviceCategory AS device, Totals.newVisits AS newVisits, CAST(SUM(IFNULL(Totals.Transactions, 0)) AS INTEGER) AS Orders, CAST(SUM(IFNULL(Totals.PageViews, 0)) AS INTEGER) AS PageViews, ROUND(SUM(IFNULL(Totals.TimeOnSite, 0))/60, 2) AS DurationMinutes, SUM(IFNULL(Totals.TotalTransactionRevenue, 0))/1000000 AS Sales FROM `study-1st.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170801' AND trafficSource.source <> 'mall.googleplex.com' -- remove internal traffic GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15 ORDER BY 3;

#1000행만 불러오는 예시 !pip install google-cloud-bigquery from google.cloud import bigquery from google.colab import auth import pandas as pd # 인증 설정 auth.authenticate_user() # 클라이언트 생성 client = bigquery.Client(project="study-1st") dataset_ref = client.dataset("google_analytics_sample", project="bigquery-public-data") # 쿼리 작성 query = """ SELECT * FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*` WHERE _TABLE_SUFFIX BETWEEN '20160801' AND '20170801' LIMIT 1000 """ # 쿼리 실행 df = client.query(query).to_dataframe() # 결과 출력 df.head()