EDA of Google Analytics Data

SQL | EDA of Google Analytics Data

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

https://www.kaggle.com/code/dillonmyrick/sql-eda-of-google-analytics-data

Goal

주요 목표

1. 현재 사이트 트래픽의 구성 이해

2. 사용자의 흐름 및 전환 경로 이해

3. 제품 수요 예측

분석 자료 기간

분석 과정

1. 데이터 추출 및 내용 확인

2. 목표를 구체화하기

3. 데이터 탐색 및 분석

4. 통찰력 시각화 및 결과 해석

SQL | EDA of Google Analytics Data

import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

1. 데이터 확인

• 데이터에 대한 정보는 이 링크에 자세히 설명되어 있다.

• 빅쿼리와 연동해서 데이터를 보기 위해 관련 라이브러리를 설치 라이브러리 깃허브

pip install --upgrade google-cloud-bigquery

• 설치 후 불러오면 인증을 하라고 에러 메시지가 뜬다.

from google.cloud import bigquery

client = bigquery.Client()
dataset_ref = client.dataset('google_analytics_sample', project = 'bigquery-public-data')

dataset = client.get_dataset(dataset_ref)

from google.cloud import bigquery

client = bigquery.Client()
dataset_ref = client.dataset('google_analytics_sample', project = 'bigquery-public-data')

dataset = client.get_dataset(dataset_ref)

when I bigquery.Client(project="My Valid Project") then the call succeeds.

from google.cloud import bigquery

# client 생성
client = bigquery.Client(project = "프로젝트 ID 복붙")

# dataset reference 생성
dataset_ref = client.dataset('google_analytics_sample', project = 'bigquery-public-data')

dataset = client.get_dataset(dataset_ref)

파이썬으로 구글 애널리틱스 데이터 가져오기

블로그를 운영하시는 분들을 대부분 구글 애널리틱스를 사용하실 것입니다. 저도 당연히 구글 애널리틱스를 사용하고 있습니다. 구글 애널리틱스를 사용하면 블로그에 대한 다양한 분석자료를 얻을 수 있기 때문에 블로그를 사용하시는 분들은 꼭 사용해야 한다고 생각됩니다. 아무튼 그래서 이번에는 파이썬으로 구글 애널리틱스 데이터를 가져오는 것을 한번 해보려고 합니다. 참고로 저는 코딩의 코자도 모르는 코린이입니다. 정말 코딩에 대한 지식수준이 낮기 때문에 제가 하는 방법이 정답이 아닐 수도 있습니다. 다만 제가 직접 시도를 해서 성공을 한 후기라고 생각해 주시면 감사하겠습니다. 여기에 작성된 내용은 아래 링크를 기반으로 작성되었습니다. API Quickstart | Google Analytics Data API | ..

https://intstorage.tistory.com/entry/파이썬으로-구글-애널리틱스-데이터-가져오기

UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK without a quota project. You might receive a "quota exceeded" or "API not enabled" error. See the following page for troubleshooting: https://cloud.google.com/docs/authentication/adc-troubleshooting/user-creds.
warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)

✔️ 프로젝트 ID 확인법

# dataset 확인
[x.table_id for x in client.list_tables(dataset)][:5]

# pip install db-dtypes
# pip install --upgrade 'google-cloud-bigquery[all]'
# pip install --upgrade google-cloud-bigquery-storage google-cloud-bigquery-datatransfer

# table_referencce 생성
table_ref_20160801 = dataset_ref.table('ga_sessions_20160801')

table_20160801 = client.get_table(table_ref_20160801)

# 데이터 확인
client.list_rows(table_20160801, max_results = 5).to_dataframe()

• 함수로 만들고

# 데이터별로 정리 

def format_schema_field(schema_field, indent = 0):
    indent_str = "  " * indent
    field_info = f"{indent_str}{schema_field.name} ({schema_field.field_type})"

    if schema_field.mode != "NULLABLE":
        field_info += f" - {schema_field.mode}"

    if schema_field.description:
        field_info += f" - {schema_field.description}"

    nested_indent = indent + 2
    if schema_field.field_type == "RECORD":
        for sub_field in schema_field.fields:
            field_info += "\n" + format_schema_field(sub_field, nested_indent)

    return field_info

• 출력한다.

for i in range(5,11):
    print(format_schema_field(table_20160801.schema[i]))
    print('\n')

totals (RECORD)
  visits (INTEGER)
  hits (INTEGER)
  pageviews (INTEGER)
  timeOnSite (INTEGER)
  bounces (INTEGER)
  transactions (INTEGER)
  transactionRevenue (INTEGER)
  newVisits (INTEGER)
  screenviews (INTEGER)
  uniqueScreenviews (INTEGER)
  timeOnScreen (INTEGER)
  totalTransactionRevenue (INTEGER)
  sessionQualityDim (INTEGER)


trafficSource (RECORD)
  referralPath (STRING)
  campaign (STRING)
  source (STRING)
  medium (STRING)
  keyword (STRING)
  adContent (STRING)
  adwordsClickInfo (RECORD)
    campaignId (INTEGER)
    adGroupId (INTEGER)
    creativeId (INTEGER)
    criteriaId (INTEGER)
    page (INTEGER)
    slot (STRING)
    criteriaParameters (STRING)
    gclId (STRING)
    customerId (INTEGER)
    adNetworkType (STRING)
    targetingCriteria (RECORD)
      boomUserlistId (INTEGER)
    isVideoAd (BOOLEAN)
  isTrueDirect (BOOLEAN)
  campaignCode (STRING)


device (RECORD)
  browser (STRING)
  browserVersion (STRING)
  browserSize (STRING)
  operatingSystem (STRING)
  operatingSystemVersion (STRING)
  isMobile (BOOLEAN)
  mobileDeviceBranding (STRING)
  mobileDeviceModel (STRING)
  mobileInputSelector (STRING)
  mobileDeviceInfo (STRING)
  mobileDeviceMarketingName (STRING)
  flashVersion (STRING)
  javaEnabled (BOOLEAN)
  language (STRING)
  screenColors (STRING)
  screenResolution (STRING)
  deviceCategory (STRING)


geoNetwork (RECORD)
  continent (STRING)
  subContinent (STRING)
  country (STRING)
  region (STRING)
  metro (STRING)
  city (STRING)
  cityId (STRING)
  networkDomain (STRING)
  latitude (STRING)
  longitude (STRING)
  networkLocation (STRING)


customDimensions (RECORD) - REPEATED
  index (INTEGER)
  value (STRING)


hits (RECORD) - REPEATED
  hitNumber (INTEGER)
  time (INTEGER)
  hour (INTEGER)
  minute (INTEGER)
  isSecure (BOOLEAN)
  isInteraction (BOOLEAN)
  isEntrance (BOOLEAN)
  isExit (BOOLEAN)
  referer (STRING)
  page (RECORD)
    pagePath (STRING)
    hostname (STRING)
    pageTitle (STRING)
    searchKeyword (STRING)
    searchCategory (STRING)
    pagePathLevel1 (STRING)
    pagePathLevel2 (STRING)
    pagePathLevel3 (STRING)
    pagePathLevel4 (STRING)
  transaction (RECORD)
    transactionId (STRING)
    transactionRevenue (INTEGER)
    transactionTax (INTEGER)
    transactionShipping (INTEGER)
    affiliation (STRING)
    currencyCode (STRING)
    localTransactionRevenue (INTEGER)
    localTransactionTax (INTEGER)
    localTransactionShipping (INTEGER)
    transactionCoupon (STRING)
  item (RECORD)
    transactionId (STRING)
    productName (STRING)
    productCategory (STRING)
    productSku (STRING)
    itemQuantity (INTEGER)
    itemRevenue (INTEGER)
    currencyCode (STRING)
    localItemRevenue (INTEGER)
  contentInfo (RECORD)
    contentDescription (STRING)
  appInfo (RECORD)
    name (STRING)
    version (STRING)
    id (STRING)
    installerId (STRING)
    appInstallerId (STRING)
    appName (STRING)
    appVersion (STRING)
    appId (STRING)
    screenName (STRING)
    landingScreenName (STRING)
    exitScreenName (STRING)
    screenDepth (STRING)
  exceptionInfo (RECORD)
    description (STRING)
    isFatal (BOOLEAN)
    exceptions (INTEGER)
    fatalExceptions (INTEGER)
  eventInfo (RECORD)
    eventCategory (STRING)
    eventAction (STRING)
    eventLabel (STRING)
    eventValue (INTEGER)
  product (RECORD) - REPEATED
    productSKU (STRING)
    v2ProductName (STRING)
    v2ProductCategory (STRING)
    productVariant (STRING)
    productBrand (STRING)
    productRevenue (INTEGER)
    localProductRevenue (INTEGER)
    productPrice (INTEGER)
    localProductPrice (INTEGER)
    productQuantity (INTEGER)
    productRefundAmount (INTEGER)
    localProductRefundAmount (INTEGER)
    isImpression (BOOLEAN)
    isClick (BOOLEAN)
    customDimensions (RECORD) - REPEATED
      index (INTEGER)
      value (STRING)
    customMetrics (RECORD) - REPEATED
      index (INTEGER)
      value (INTEGER)
    productListName (STRING)
    productListPosition (INTEGER)
  promotion (RECORD) - REPEATED
    promoId (STRING)
    promoName (STRING)
    promoCreative (STRING)
    promoPosition (STRING)
  promotionActionInfo (RECORD)
    promoIsView (BOOLEAN)
    promoIsClick (BOOLEAN)
  refund (RECORD)
    refundAmount (INTEGER)
    localRefundAmount (INTEGER)
  eCommerceAction (RECORD)
    action_type (STRING)
    step (INTEGER)
    option (STRING)
  experiment (RECORD) - REPEATED
    experimentId (STRING)
    experimentVariant (STRING)
  publisher (RECORD)
    dfpClicks (INTEGER)
    dfpImpressions (INTEGER)
    dfpMatchedQueries (INTEGER)
    dfpMeasurableImpressions (INTEGER)
    dfpQueries (INTEGER)
    dfpRevenueCpm (INTEGER)
    dfpRevenueCpc (INTEGER)
    dfpViewableImpressions (INTEGER)
    dfpPagesViewed (INTEGER)
    adsenseBackfillDfpClicks (INTEGER)
    adsenseBackfillDfpImpressions (INTEGER)
    adsenseBackfillDfpMatchedQueries (INTEGER)
    adsenseBackfillDfpMeasurableImpressions (INTEGER)
    adsenseBackfillDfpQueries (INTEGER)
    adsenseBackfillDfpRevenueCpm (INTEGER)
    adsenseBackfillDfpRevenueCpc (INTEGER)
    adsenseBackfillDfpViewableImpressions (INTEGER)
    adsenseBackfillDfpPagesViewed (INTEGER)
    adxBackfillDfpClicks (INTEGER)
    adxBackfillDfpImpressions (INTEGER)
    adxBackfillDfpMatchedQueries (INTEGER)
    adxBackfillDfpMeasurableImpressions (INTEGER)
    adxBackfillDfpQueries (INTEGER)
    adxBackfillDfpRevenueCpm (INTEGER)
    adxBackfillDfpRevenueCpc (INTEGER)
    adxBackfillDfpViewableImpressions (INTEGER)
    adxBackfillDfpPagesViewed (INTEGER)
    adxClicks (INTEGER)
    adxImpressions (INTEGER)
    adxMatchedQueries (INTEGER)
    adxMeasurableImpressions (INTEGER)
    adxQueries (INTEGER)
    adxRevenue (INTEGER)
    adxViewableImpressions (INTEGER)
    adxPagesViewed (INTEGER)
    adsViewed (INTEGER)
    adsUnitsViewed (INTEGER)
    adsUnitsMatched (INTEGER)
    viewableAdsViewed (INTEGER)
    measurableAdsViewed (INTEGER)
    adsPagesViewed (INTEGER)
    adsClicked (INTEGER)
    adsRevenue (INTEGER)
    dfpAdGroup (STRING)
    dfpAdUnits (STRING)
    dfpNetworkId (STRING)
  customVariables (RECORD) - REPEATED
    index (INTEGER)
    customVarName (STRING)
    customVarValue (STRING)
  customDimensions (RECORD) - REPEATED
    index (INTEGER)
    value (STRING)
  customMetrics (RECORD) - REPEATED
    index (INTEGER)
    value (INTEGER)
  type (STRING)
  social (RECORD)
    socialInteractionNetwork (STRING)
    socialInteractionAction (STRING)
    socialInteractions (INTEGER)
    socialInteractionTarget (STRING)
    socialNetwork (STRING)
    uniqueSocialInteractions (INTEGER)
    hasSocialSourceReferral (STRING)
    socialInteractionNetworkAction (STRING)
  latencyTracking (RECORD)
    pageLoadSample (INTEGER)
    pageLoadTime (INTEGER)
    pageDownloadTime (INTEGER)
    redirectionTime (INTEGER)
    speedMetricsSample (INTEGER)
    domainLookupTime (INTEGER)
    serverConnectionTime (INTEGER)
    serverResponseTime (INTEGER)
    domLatencyMetricsSample (INTEGER)
    domInteractiveTime (INTEGER)
    domContentLoadedTime (INTEGER)
    userTimingValue (INTEGER)
    userTimingSample (INTEGER)
    userTimingVariable (STRING)
    userTimingCategory (STRING)
    userTimingLabel (STRING)
  sourcePropertyInfo (RECORD)
    sourcePropertyDisplayName (STRING)
    sourcePropertyTrackingId (STRING)
  contentGroup (RECORD)
    contentGroup1 (STRING)
    contentGroup2 (STRING)
    contentGroup3 (STRING)
    contentGroup4 (STRING)
    contentGroup5 (STRING)
    previousContentGroup1 (STRING)
    previousContentGroup2 (STRING)
    previousContentGroup3 (STRING)
    previousContentGroup4 (STRING)
    previousContentGroup5 (STRING)
    contentGroupUniqueViews1 (INTEGER)
    contentGroupUniqueViews2 (INTEGER)
    contentGroupUniqueViews3 (INTEGER)
    contentGroupUniqueViews4 (INTEGER)
    contentGroupUniqueViews5 (INTEGER)
  dataSource (STRING)
  publisher_infos (RECORD) - REPEATED
    dfpClicks (INTEGER)
    dfpImpressions (INTEGER)
    dfpMatchedQueries (INTEGER)
    dfpMeasurableImpressions (INTEGER)
    dfpQueries (INTEGER)
    dfpRevenueCpm (INTEGER)
    dfpRevenueCpc (INTEGER)
    dfpViewableImpressions (INTEGER)
    dfpPagesViewed (INTEGER)
    adsenseBackfillDfpClicks (INTEGER)
    adsenseBackfillDfpImpressions (INTEGER)
    adsenseBackfillDfpMatchedQueries (INTEGER)
    adsenseBackfillDfpMeasurableImpressions (INTEGER)
    adsenseBackfillDfpQueries (INTEGER)
    adsenseBackfillDfpRevenueCpm (INTEGER)
    adsenseBackfillDfpRevenueCpc (INTEGER)
    adsenseBackfillDfpViewableImpressions (INTEGER)
    adsenseBackfillDfpPagesViewed (INTEGER)
    adxBackfillDfpClicks (INTEGER)
    adxBackfillDfpImpressions (INTEGER)
    adxBackfillDfpMatchedQueries (INTEGER)
    adxBackfillDfpMeasurableImpressions (INTEGER)
    adxBackfillDfpQueries (INTEGER)
    adxBackfillDfpRevenueCpm (INTEGER)
    adxBackfillDfpRevenueCpc (INTEGER)
    adxBackfillDfpViewableImpressions (INTEGER)
    adxBackfillDfpPagesViewed (INTEGER)
    adxClicks (INTEGER)
    adxImpressions (INTEGER)
    adxMatchedQueries (INTEGER)
    adxMeasurableImpressions (INTEGER)
    adxQueries (INTEGER)
    adxRevenue (INTEGER)
    adxViewableImpressions (INTEGER)
    adxPagesViewed (INTEGER)
    adsViewed (INTEGER)
    adsUnitsViewed (INTEGER)
    adsUnitsMatched (INTEGER)
    viewableAdsViewed (INTEGER)
    measurableAdsViewed (INTEGER)
    adsPagesViewed (INTEGER)
    adsClicked (INTEGER)
    adsRevenue (INTEGER)
    dfpAdGroup (STRING)
    dfpAdUnits (STRING)
    dfpNetworkId (STRING)

2. Make Our Goals Concrete

3. Explore and Analyze Data

1. Most Popular Pages

• 자주 방문하는 페이지와 해당 페이지의 이탈률 확인

• 이탈률 = 이탈 횟수 / 총 세션 수
• 여기서 이탈 횟수((사용자가 사이트에 들어왔다가 바로 떠난 세션))

• 이탈률이 높을 때의 추측
• 사용자가 사이트에서 기대한 내용을 못 봤을 때 - 검색 엔진 최적화(SEO) 수행 필요
• 페이지에 기능 또는 디스플레이 문제가 있을 때

# hit = 1

query = """
        SELECT
            hits.page.pagePath AS landing_page,
            COUNT(*) AS views,
            SUM(totals.bounces)/COUNT(*) AS bounce_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            hits.type = 'PAGE'
        AND
            hits.hitNumber = 1
        GROUP BY
            landing_page
        ORDER BY
            views DESC
        LIMIT 10
        """
result = client.query(query).result().to_dataframe()
result.head(10)

# Google Cloud SDK Shell
gcloud auth login

• FROM
• 샘플 데이터셋에서 ga_sessions_*이 파티션화된 테이블
• UNNEST(hits) AS hits 중첩된 hits필드 펼치기 (반복 레코드 필드임)

• WHERE
• _TABLE_SUFFIX 를 기반으로 20160801 ~ 20170801 데이터 필터링,
• hits.type Page형식의 히트만 고려
• hits.hitNumber hit 1만 표시 즉, 사용자가 처음 페이지를 방문한 순간

• GROUP BY
• landing_page 필드를 기반으로 데이터 그룹화

• SELECT
• 해당되는 데이터의 landing_page, views, bounce_rate만 볼 것임(AS로 컬럼명 수정)
• landing_page 페이지 경로(path)
• views 카운트 COUNT(*)
• bounce_rate 이탈률 = 이탈한 방문 수(totals.bounces의 합) / 총 방문 수(총 count)

• ORDER BY
• views를 기준으로 내림차순 정렬

• LIMIT 10
• 파이썬에서 df.head(10)과 비슷한 기능. 10개만 제한해서 보여준다.

• exit rate는 랜딩 페이지뿐만 아니라 모든 페이지에 대해 계산

• 이탈률(exit_rate) = 이탈 수 / 총 세션 수
• (이탈 수 = 사용자가 페이지로 이동한 후 사이트를 떠난 세션)

• exit_rate이 높으면 사용자가 페이지로 이동할 때 예상한 내용이 표시되지 않거나 페이지 기능 또는 디스플레이에 문제가 있는 것

query = """
        SELECT
            hits.page.pagePath AS page,
            COUNT(*) AS views,
            SUM(totals.bounces)/COUNT(*) AS exit_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            hits.type = 'PAGE'
        GROUP BY
            page
        ORDER BY
            views DESC
        LIMIT 10
        """
result = client.query(query).result().to_dataframe()
result.head(10)

query = """
        SELECT
            hits.page.pagePath AS landing_page,
            COUNT(*) AS views,
            SUM(totals.bounces)/COUNT(*) AS bounce_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            hits.type = 'PAGE'
        AND
            hits.hitNumber = 1
        GROUP BY
            landing_page
        ORDER BY
            views DESC
        LIMIT 10
        """
result = client.query(query).result().to_dataframe()
result.head(10)

query = """
        SELECT
            hits.page.pagePath AS page,
            COUNT(*) AS views,
            SUM(totals.bounces)/COUNT(*) AS exit_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            hits.type = 'PAGE'
        GROUP BY
            page
        ORDER BY
            views DESC
        LIMIT 10
        """
result = client.query(query).result().to_dataframe()
result.head(10)

2. Browser and Device

query = """
        SELECT
            device.Browser AS browser,
            COUNT(*) AS sessions,
            SUM(totals.bounces)/COUNT(*) AS exit_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        GROUP BY
            browser
        ORDER BY
            sessions DESC
        LIMIT 10
        """

result = client.query(query).result().to_dataframe()
result.head(10)

상위 5개 브라우저 중 Chrome의 이탈률은 9%로 비교적 낮은 반면,

이탈률이 19%인 Internet Explorer를 제외한 나머지 브라우저는 Chrome에 비해 약 7~8% 정도 높음

 

3. Traffic Type and Quality

query = """
        SELECT
            trafficSource.medium AS medium,
            COUNT(*) AS sessions,
            SUM(totals.bounces)/COUNT(*) AS exit_rate,
            SUM(totals.transactions) AS transactions,
            SUM(totals.totalTransactionRevenue)/1000000 AS total_revenue,
            SUM(totals.transactions)/COUNT(*) AS conversion_rate
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        GROUP BY
            medium
        ORDER BY
            sessions DESC
        """

result = client.query(query).result().to_dataframe()
result.head(10)

4. Identify Conversion Path and Possible Bottlenecks

# action type별 총 조회수(hits) 집계

query = """
        SELECT
            CASE WHEN hits.eCommerceAction.action_type = '1' THEN 'Click through of product lists'
                 WHEN hits.eCommerceAction.action_type = '2' THEN 'Product detail views'
                 WHEN hits.eCommerceAction.action_type = '5' THEN 'Check out'
                 WHEN hits.eCommerceAction.action_type = '6' THEN 'Completed purchase'
            END AS action,
            COUNT(fullvisitorID) AS users,
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits,
            UNNEST(hits.product) AS product
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            (
            hits.eCommerceAction.action_type != '0'
            AND
            hits.eCommerceAction.action_type != '3'
            AND
            hits.eCommerceAction.action_type != '4'
            )
        GROUP BY
            action
        ORDER BY
            users DESC
            """

result = client.query(query).result().to_dataframe()
result.head(10)

• FROM
• ga_sessions_* 지정된 날짜 범위 내의 모든 테이블을 쿼리
• UNNEST 함수를 사용하여 hits 및 hits.product 필드를 펼치기 → 중첩된 데이터 사용

• WHERE
• _TABLE_SUFFIX를 사용하여 날짜 범위 지정(20160801 ~ 20170801)
• hits.eCommerceAction.action_type 필드가 '0', '3', '4'가 아닌 행만 선택

• GROUP BY
• action 열을 기반으로 데이터 그룹화

• SELECT
• action type에 따른 행동 유형 명칭 지정
• action_type = 1 제품 목록 클릭
• action_type = 2 제품 세부 정보 보기
• action_type = 5 결제 단계
• action_type = 6 구매 완료

• ORDER BY
• users 사용자 수를 기준으로 내림차순

# funnel graph
import plotly.graph_objs as go
funnel_graph = go.Figure(go.Funnel(y = result['action'],
                                x = result['users'],
                                textposition = 'inside',  # Funnel 그래프 안에 텍스트를 표시할 위치를 'inside'로 설정
                                textinfo = 'value + percent initial'), # 각 단계의 값과 초기값의 백분율을 나타내도록 설정
                            layout = go.Layout(height = 400, width = 800)
                        )
funnel_graph.update_layout(title_text = 'Google Merchandise Store Conversion Path')

funnel_graph.show()

1. 'value': 현재 단계의 값, 즉 해당 단계에서의 사용자 수가 표시됩니다.

2. 'percent initial': 해당 단계의 값이 초기 단계 값에 대한 백분율로 표시됩니다.

5. Identify Best-Selling Product Categories and Forecast Demand

query = """
        SELECT
            product.v2ProductCategory AS category,
            SUM(totals.transactions) AS transactions,
            SUM(totals.totalTransactionRevenue)/1000000 AS total_revenue
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits,
            UNNEST(hits.product) AS product
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        GROUP BY
            category
        ORDER BY
            total_revenue DESC
        LIMIT 10
        """

cat_result = client.query(query).result().to_dataframe()
cat_result.head(10)

query = """
        WITH daily_mens_tshirt_transactions AS
        (
        SELECT 
            date,
            SUM(totals.transactions) AS transactions
        FROM
            `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
            UNNEST(hits) AS hits,
            UNNEST(hits.product) AS product
        WHERE
            _TABLE_SUFFIX BETWEEN '20160801' AND '20170801'
        AND
            product.v2ProductCategory = "Home/Apparel/Men's/Men's-T-Shirts/"
        GROUP BY
            date
        ORDER BY
            date
        )

        SELECT
            date,
            AVG(transactions)
            OVER(
                ORDER BY date
                ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
                ) AS avg_transactions
        FROM
            daily_mens_tshirt_transactions
        """

result = client.query(query).result().to_dataframe()
result['date'] = pd.to_datetime(result['date'])
result.plot(y = 'avg_transactions', x = 'date', kind = 'line',
            title = 'Men\'s T-Shirts Conversions 7-Day Moving Average', figsize = (12, 6))

plt.show()