[GA] EDA

1. GCP / Bigquery 탐색

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

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

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

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

• 배열 : 데이터 유형이 동일한 값으로 구성된 목록 (파이썬 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

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

• 사이트 : https://shop.googlemerchandisestore.com/
• 스키마 : https://support.google.com/analytics/answer/3437719
• 빅쿼리 데이터셋 : https://console.cloud.google.com/marketplace/product/obfuscated-ga360-data/obfuscated-ga360-data?project=study-1st

• 사용할 컬럼 정보
• 스키마를 참고해서, 데이터 값이 정상적으로 들어와있고 분석에 필요한 값만 선정했다
쿼리문

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;

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

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()