<jupyter_start><jupyter_text>Table of Contentsページ速度の分析BigQuery からデータを取得するデータの内容の確認可視化さらなる定量評価と改善すべきページの洗い出し  ページ速度の分析<jupyter_code>%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns<jupyter_output><empty_output><jupyter_text>BigQuery からデータを取得するSQL の定義（  `__PROJECT__, __DATASET__, __START_DATE__, __END_DATE__` は自分の環境にあわせて適宜変更する ）<jupyter_code>sql = '''
WITH base AS (
  SELECT
    fullVisitorId
    , CONCAT(fullVisitorId, CAST(visitStartTime AS STRING)) AS sessionId
    , h.hitNumber AS hitNumber
    , REGEXP_REPLACE(h.page.pagePath, r'\?[a-zA-Z0-9]+=.*', '') AS page
    , h.type AS hitType
    , h.isExit AS isExit
    , h.latencyTracking.pageLoadSample AS pageLoadSample
    , h.latencyTracking.pageLoadTime AS pageLoadTime
  FROM
    `__PROJECT__.__DATASET__.ga_sessions_20*`
    , UNNEST(hits) AS h
  WHERE
    _TABLE_SUFFIX BETWEEN '__START_DATE__' AND '__END_DATE__'
)
, page_speed AS (
  SELECT
    fullVisitorId
    , sessionId
    , hitNumber
    , hitType
    , page
    , pageLoadTime
  FROM
    base
  WHERE
    pageLoadSample = 1
)
, pages AS (
  SELECT
    fullVisitorId
    , sessionId
    , hitNumber
    , page
    , isExit
    , hitType
  FROM
    base
  WHERE
    hitType = 'PAGE'
)

SELECT
  t1.fullVisitorId
  , t1.sessionId
  , t2.hitNumber AS hitNumber
  , t1.page
  , t1.pageLoadTime
  , CAST(IF(t2.isExit IS TRUE, 1, 0) AS INT64) AS isExit
FROM
  page_speed AS t1
JOIN (
  SELECT 
    sessionId
    , hitNumber
    , page
    , isExit
    , hitType
  FROM
    pages
) AS t2
ON 
  t1.sessionId = t2.sessionId 
  AND t1.page = t2.page
  AND t1.hitNumber = t2.hitNumber+1
ORDER BY
  fullVisitorId
  , sessionId ASC
  , hitNumber ASC
'''<jupyter_output><empty_output><jupyter_text>Google Cloud の Project ID を設定する（自分の環境に合わせて適宜変更する）<jupyter_code>project_id = '__PROJECT__'<jupyter_output><empty_output><jupyter_text>pandas の read_gbq メソッドを使ってデータを取得する<jupyter_code>df = pd.read_gbq(sql, project_id=project_id, dialect='standard')<jupyter_output>Requesting query... ok.
Query running...
Query done.
Processed: 9.2 MB

Retrieving results...
  Got page: 1; 100% done. Elapsed 11.78 s.
Got 871 rows.

Total time taken 11.8 s.
Finished at 2018-01-15 06:03:25.<jupyter_text>データの内容の確認とりあえず df.head() と df.describe() で概要をつかむ<jupyter_code>df.head()
df.describe()<jupyter_output><empty_output><jupyter_text>離脱している群（ isExit が 1 のもの ）と離脱していない群を比較する<jupyter_code>df[df.isExit==1].describe()
df[df.isExit==0].describe()<jupyter_output><empty_output><jupyter_text>離脱してる方が- hit number は 1.2 ほど小さい- pageLoadTime は 平均で 1.43 秒長い（が、分散が倍近い）  これらの違いを可視化して確認してみる。  可視化とりあえず離脱群と非離脱群の比較を Boxplot で行う<jupyter_code>fig = plt.figure(figsize=(8, 6))
ax = fig.gca()
ax = sns.boxplot(x='isExit', y='pageLoadTime', data=df, dodge=True)
ax.set(yscale='log')
ax.set_title('Page Load time distribution for if exists or not', fontsize=18)
ax.set_xlabel('Exit or not (1 = exit)', fontsize=14)
ax.set_ylabel('Page load time (log)', fontsize=14)
plt.xticks(fontsize=14)
plt.yticks(fontsize=14)
plt.show()<jupyter_output><empty_output><jupyter_text>離脱群と非離脱群の違いが小さくこれだけだとわかりにくいので、もう少しブレイクダウンする。まず hit number の傾向を把握する。<jupyter_code>fig = plt.figure(figsize=(8, 6))
ax = fig.gca()
ax.set_title('Normalized hit number histogram', fontsize=18)
ax.hist(df.hitNumber, bins=38, normed=True)
ax.set_xlabel('hit number', fontsize=14)
plt.xticks(fontsize=14)
plt.yticks(fontsize=14)
plt.show()<jupyter_output><empty_output><jupyter_text>おおよそ 11 ~ 12 あたりまでで 90 % 占めてそう。  hitNumber < 15 で Botplot 描いてみる<jupyter_code>data = df[df.hitNumber < 15]
fig = plt.figure(figsize=(20, 14))
ax = sns.boxplot(x='hitNumber', y='pageLoadTime', hue='isExit',
               data=data,
               dodge=True)
ax.set_title('pageLoadTime distribution by hit numbers', fontsize=18)
ax.set(yscale='log')
ax.set_ylabel('Page Load Time (log)', fontsize=14)
ax.set_xlabel('Hit numbers within a session', fontsize=14)
ax.legend(fontsize=14).set_title('Exit or not', prop={'size': 14})
plt.xticks(fontsize=14)
plt.yticks(fontsize=14)
plt.show()<jupyter_output><empty_output><jupyter_text>Hit number = 6 までは、概ね離脱の有無と pageLoadTime に正の相関ありそう（ 離脱群の方が pageLoadTime 長そう ）。  それ以降はまちまち。  さらなる定量評価と改善すべきページの洗い出しpage ✕ 離脱の有無 ごとに pageLoadTime の中央値と標準偏差を計算する。<jupyter_code>data = df[['page', 'isExit', 'pageLoadTime']].groupby(
    ['page', 'isExit'], as_index=False).agg({'pageLoadTime': ['median', 'std']})
data.columns = ['page', 'isExit', 'median_pageLoadTime', 'std_pageLoadTime']
data.head()<jupyter_output><empty_output><jupyter_text>欠損値を埋めるために、  - 元データ（ df ）から、ページの URL のユニーク値を取得- カラム isExit の欠損値は 1、0 で埋める- pageLoadTime の中央値・標準偏差の欠損値は、それぞれの平均で埋める<jupyter_code>pages = pd.DataFrame(df['page'].unique(), columns=['page'])
data_exit = data[data.isExit == 1]
data_non_exit = data[data.isExit==0]

data_exit = pd.merge(pages, data_exit, on='page', how='left')
data_exit['isExit'] = data_exit.isExit.fillna(1)
data_exit['median_pageLoadTime'] = data_exit.median_pageLoadTime.fillna(data_exit.median_pageLoadTime.mean())
data_exit['std_pageLoadTime'] = data_exit.std_pageLoadTime.fillna(data_exit.std_pageLoadTime.mean())

data_non_exit = pd.merge(pages, data_non_exit, on='page', how='left')
data_non_exit['isExit'] = data_non_exit.isExit.fillna(0)
data_non_exit['median_pageLoadTime'] = data_non_exit.median_pageLoadTime.fillna(data_non_exit.median_pageLoadTime.mean())
data_non_exit['std_pageLoadTime'] = data_non_exit.std_pageLoadTime.fillna(data_non_exit.std_pageLoadTime.mean())
page_data = pd.concat([data_exit, data_non_exit])<jupyter_output><empty_output><jupyter_text>ページ・離脱の有無・pageLoadTime の平均でソート<jupyter_code>page_data.sort_values(by=['page', 'isExit', 'median_pageLoadTime'], ascending=['True', 'True', 'False']).head(10)<jupyter_output><empty_output><jupyter_text>離脱群と非離脱群で、中央値 pageLoadTime の差が大きいものとそうでないものがある。差が大きい方が離脱に影響している可能性高そうなので、離脱群と非離脱群で pageLoadTime の中央値と標準偏差の差分を取り、page でユニークなテーブルを作成する。<jupyter_code>pivoted = page_data.pivot(columns='isExit', index='page')
pivoted.columns = [
    'nonexit_median_pageLoadTime', 'exit_median_pageLoadTime',
    'nonexit_std_pageLoadTime', 'exit_std_pageLoadTime']
pivoted = pivoted.reset_index()
pivoted['diff_median_pageLoadTime'] = pivoted.exit_median_pageLoadTime - pivoted.nonexit_median_pageLoadTime
pivoted['diff_std_pageLoadTime'] = pivoted.exit_std_pageLoadTime - pivoted.nonexit_std_pageLoadTime<jupyter_output><empty_output><jupyter_text>離脱群と非離脱群で pageLoadTime の中央値の差が大きく、標準偏差の差が小さいものでソート  - 中央値の差が大きくばらつきの違いが小さいものから順にページ速度の改善を検討する。<jupyter_code>rankings = pivoted.sort_values(by=['diff_median_pageLoadTime', 'diff_std_pageLoadTime'], ascending=[False, True])
rankings[['page', 'diff_median_pageLoadTime', 'diff_std_pageLoadTime']].head(10)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12640949554896141, 'fraction_numerical': 0.018694362017804153, 'mean_word_length': 6.49, 'pattern_counts': {'":': 0, '<': 58, '<?xml version=': 0, '>': 56, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '626159', 'license': 'Apache-2.0', 'n_tokens_mistral': 3241, 'n_tokens_neox': 3144, 'n_words': 428, 'path': 'page speed analysis example.ipynb', 'repo_name': 'yukoga/bigquery-sample', 'size': '6740'}