概要

mediba Advent Calendar 2022の19日目です。

昨年のAdventカレンダーではPWA（A2HS）の仕様調査について書きましたが、
今回は「ホーム画面追加後の訪問頻度の変化」についてアクセスログを元に分析してみます。

前提

Webサイト側にmanifest.jsonを設置しており、start_urlで一意のURLパラメーターを指定しています。このパラメータを利用することでホーム画面経由のアクセスが判別できるようになります。
また、displayはbrowserを指定しブラウザ起動させています。

{
  "name": "アプリ名",
  "display": "browser",
  "short_name": "アプリ名",
  "start_url": "/?ref=homescreen"
}

アクセスログの抽出

分析に必要なデータをUniversal AnalyticsのBigQueryテーブルからSQLで抽出していきます。
作業工程が長くなるため、手順ごとにSQLとテーブルを切り出しています。

1) 月毎の日数を抽出

この後の集計で「日数の補正」が必要となるため各年月ごとに日数（最終日）を抽出します。テーブル名はmonth_lastdayとします。

DECLARE
  from_date string;
DECLARE
  to_date string;
SET
  from_date = "20220101";
SET
  to_date = "20220731";
CREATE OR REPLACE TABLE
  `project-name.dataset-name.month_lastday` AS (
  WITH
    raw_data AS (
    SELECT
      DATE_TRUNC(PARSE_DATE("%Y%m%d", CAST(date AS STRING)), MONTH) AS month,
      EXTRACT(DAY
      FROM
        PARSE_DATE("%Y%m%d", CAST(date AS STRING))) AS day
    FROM
      `project-name.dataset-name.ga_sessions_*`,
      UNNEST(hits) AS hits
    WHERE
      hits.type = "PAGE"
      AND _table_suffix BETWEEN from_date
      AND to_date ),
    raw_data2 AS (
    SELECT
      month,
      MAX(day) OVER(PARTITION BY month) AS last_day
    FROM
      raw_data )
  SELECT
    month,
    last_day
  FROM
    raw_data2
  GROUP BY
    month,
    last_day
  ORDER BY
    month )

ポイント

WINDOW関数を使い、月毎に「日の最大値（MAX）」を集計しています。

month_lastday テーブル

month	last_day
2022-01-01	31
2022-02-01	28
2022-03-01	31
2022-04-01	30
2022-05-01	31
2022-06-01	30
2022-07-01	31

2) uidごとの利用開始月の抽出

「uidごとのホーム画面追加月」のテーブルを作成します。テーブル名はuid_startmonthとします。

DECLARE
  from_date string;
DECLARE
  to_date string;
SET
  from_date = "20220101";
SET
  to_date = "20220731";
CREATE OR REPLACE TABLE
  `project-name.dataset-name.uid_startmonth` AS (
  WITH
    raw_data AS (
    SELECT
      CAST(date AS INT64) AS date,
      fullVisitorId,
      hits.page.pagePath
    FROM
      `project-name.dataset-name.ga_sessions_*`,
      UNNEST(hits) AS hits
    WHERE
      hits.type = "PAGE"
      AND _table_suffix BETWEEN from_date
      AND to_date ),
    uid_with_start_date AS(
    SELECT
      fullVisitorId,
      MIN(date) OVER (PARTITION BY fullVisitorId) AS start_date
    FROM
      raw_data
    WHERE
      fullVisitorId IS NOT NULL
      AND REGEXP_CONTAINS(pagePath, "ref=homescreen"))
  SELECT
    fullVisitorId,
    DATE_TRUNC(PARSE_DATE("%Y%m%d", CAST(start_date AS STRING)), MONTH) AS start_month
  FROM
    uid_with_start_date
  GROUP BY
    fullVisitorId,
    start_date )

ポイント

uidごとに「?ref=homescreen のURLパラメータが付いているページビュー」の「一番古い日（若い日）」を抽出します。
その日（月）を「初めてホーム画面からアクセスした日（月）」としてstart_monthに設定します。

uid_startmonth テーブル

fullVisitorId	start_month
xxxxxxxxxxxxxxxxxx	2022-01-01
yyyyyyyyyyyyyyyyyy	2022-01-01
zzzzzzzzzzzzzzzzzz	2022-01-01

※行数は省略

3) uidごとの利用日数を抽出

uidごとに各月の利用日数を抽出します。テーブル名はuid_month_countとします。

DECLARE
  from_date string;
DECLARE
  to_date string;
SET
  from_date = "20211201";
SET
  to_date = "20220731";
CREATE OR REPLACE TABLE
  `project-name.dataset-name.uid_month_count` AS (
  WITH
    raw_data AS (
    SELECT
      PARSE_DATE("%Y%m%d", CAST(date AS STRING)) AS date,
      DATE_TRUNC(PARSE_DATE("%Y%m%d", CAST(date AS STRING)), MONTH) AS month,
      EXTRACT(DAY
      FROM
        PARSE_DATE("%Y%m%d", CAST(date AS STRING))) AS day,
      fullVisitorId
    FROM
      `project-name.dataset-name.ga_sessions_*`,
      UNNEST(hits) AS hits
    WHERE
      hits.type = "PAGE"
      AND _table_suffix BETWEEN from_date
      AND to_date ),
    month_date_uid AS (
    SELECT
      month,
      date,
      fullVisitorId
    FROM
      raw_data
    GROUP BY
      month,
      date,
      fullVisitorId )
  SELECT
    fullVisitorId,
    month,
    COUNT(date) AS count
  FROM
    month_date_uid
  GROUP BY
    fullVisitorId,
    month )

uid_month_count テーブル

fullVisitorId	month	count
xxxxxxxxxxxxxxxxxx	2022-01-01	10
yyyyyyyyyyyyyyyyyy	2022-01-01	3
zzzzzzzzzzzzzzzzzz	2022-01-01	2

※行数は省略

4) 2つのテーブルをJOIN

前述の2つのテーブルをfullVisitorIdをキーにJOINして uid_startmonth_month_countとして新しいテーブルを作成します。

CREATE OR REPLACE TABLE
  `project-name.dataset-name.uid_startmonth_month_count` AS (
  SELECT
    *
  FROM
    `project-name.dataset-name.uid_month_count`
  INNER JOIN
    `project-name.dataset-name.uid_startmonth`
  USING
    (fullVisitorId))

uid_startmonth_month_count テーブル

fullVisitorId	start_month	month	count
xxxxxxxxxxxxxxxxxx	2022-01-01	2022-01-01	12
xxxxxxxxxxxxxxxxxx	2022-01-01	2022-02-01	14
yyyyyyyyyyyyyyyyyy	2022-01-01	2022-01-01	13

※行数は省略

5) ホーム画面追加以前の平均訪問日数を抽出

ホーム画面追加以前の平均訪問日数をuidごとに抽出して users_access_countとしてテーブルを作成します。

CREATE OR REPLACE TABLE
  `project-name.dataset-name.users_access_count` AS (
  WITH
    raw_data AS(
    SELECT
      fullVisitorId,
      AVG(count) AS previous_avg_count
    FROM
      `project-name.dataset-name.uid_startmonth_month_count`
    WHERE
      month < start_month
    GROUP BY
      fullVisitorId )
  SELECT
    *
  FROM
    raw_data
  JOIN
    `project-name.dataset-name.uid_startmonth_month_count`
  USING
    (fullVisitorId) )

6) 日数を補正

月毎の日数の差を補正します。ここでは、その月の日数で割って30を掛けています。

CREATE OR REPLACE TABLE
  `project-name.dataset-name.users_access_count` AS (
  SELECT
    fullVisitorId,
    month,
    IFNULL(count / last_day * 30, 0) AS count,
    previous_avg_count,
    start_month
  FROM
    `project-name.dataset-name.users_access_count`
  LEFT JOIN
    `project-name.dataset-name.month_lastday`
  USING
    (month) )

users_access_count テーブル

fullVisitorId	month	count	previous_avg_count	start_month
xxxxxxxxxxxxxxxxxx	2022-01-01	10	10	2022-01-01
xxxxxxxxxxxxxxxxxx	2022-02-01	14	10	2022-01-01
yyyyyyyyyyyyyyyyyy	2022-01-01	6	2	2022-01-01
yyyyyyyyyyyyyyyyyy	2022-01-01	5	3	2022-01-01

※行数は省略

各カラムの説明

fullVisitorId
- GoogleAnalytics側が発行する一意のuidです。ここでは簡単のためにfullVisitorIdとしていますが、実際の分析ではサーバサイドから払い出した一意のuidをカスタムディメンションとして利用しています。
- A2HSをbrowserモードで利用する場合はfullVisitorIdで問題ありませんが、それ以外のモードではブラウザとCookieが共有できないため、一意のuidが必須となります。
month
- 集計対象月
count
- 集計対象月の利用日数。1ヶ月あたりの日数は月毎に異なるため補正しています。
previous_avg_count
- ホーム画面追加以前の平均利用日数。利用頻度におけるセグメント分けで利用
start_month
- 初めてホーム画面経由でアクセスしたした月

Rで前処理

BigQuery側でのデータの準備ができたので、可視化のための前処理をしていきます。

以下のコードでは先ほど作成したBigQueryテーブルに対してSQLを発行し、返却されたデータを加工しています。

library(tidyverse)
library(bigrquery)
library(ggthemes) 

bq_auth(email = "hoge@example.com")

sql <- "SELECT * FROM `project-name.dataset-name.users_access_count`"

tb <- bq_project_query("project-name", sql)
df_raw <- bq_table_download(tb)

df <- df_raw %>% 
  mutate(segment = case_when(
    previous_avg_count <= 10 ~ "light",
    previous_avg_count <= 20 ~ "middle",
    TRUE ~ "heavy"
  )) %>% 
  mutate(segment = factor(segment,levels = c("light", "middle", "heavy"))) %>% 
  mutate(start_month = format(start_month, "%Y-%m")) %>% 
  mutate(start_month = as.factor(start_month))

※1ヶ月あたりの訪問日数が10日以下だったらlight、11日以上20日以下だったらmiddle、それ以外だったらheavyとしてセグメントを定義しています。

最終的にuid単位のレコードをグルーピングして集計・集約します。

df_summarise <- df %>% 
  group_by(start_month, month, segment) %>% 
  summarise(mean = mean(count)) %>% 
  filter(mean != 0)

以下のようなデータフレームとなりました。

df_summarise

## # A tibble: 147 × 4
## # Groups:   start_month, month [49]
##    start_month month      segment  mean
##    <fct>       <date>     <fct>   <dbl>
##  1 2022-01     2022-01-01 light    10.2
##  2 2022-01     2022-01-01 middle   19.1
##  3 2022-01     2022-01-01 heavy    28.1
##  4 2022-01     2022-02-01 light    16.8
##  5 2022-01     2022-02-01 middle   20.4
##  6 2022-01     2022-02-01 heavy    27.9
##  7 2022-01     2022-03-01 light    14.7
##  8 2022-01     2022-03-01 middle   18.6
##  9 2022-01     2022-03-01 heavy    27.5
## 10 2022-01     2022-04-01 light    14.4
## # … with 137 more rows

Rで可視化

Rのggplot2を使って可視化します。
ここではforループでセグメントごとに折れ線グラフ（geom_line）をプロットし、画像の書き出しを行なっています。

segment_type <- as.vector(levels(df_summarise$segment))

for (i in segment_type) {
  df_summarise %>%
    filter(segment == i) %>% 
    ggplot(aes(x = month, y = mean)) +
    labs(title = "平均訪問日数の推移") +
    xlab("年月") + 
    ylab("平均訪問日数/月") + 
    labs(color = "ホーム画面追加月") +
    scale_x_date(date_labels = "%Y-%m", date_breaks = "1 month") +
    scale_y_continuous(limits = c(0, 30)) +
    scale_color_tableau("Tableau 20") +
    theme_gray(base_family = "HiraKakuPro-W3") +
    geom_line(aes(color = start_month))
    ggsave(file = paste0("segment_", i, ".png"), width = 8, height = 7)
}

1) ライトユーザー

平均的に4日/月程度だった訪問日数がホーム画面追加後から14日/月程度に伸びており、実に3.5倍の伸びとなったようです。これは驚きの結果です。

散らばり具合の変化

「平均値の変化」だけではなく「データの散らばり具合の変化」も確認してみます。

df %>%
  mutate(month = format(month, "%Y-%m")) %>% 
  filter(segment == "light") %>% 
  filter(start_month == "2022-04") %>% 
  filter(month != "2021-12") %>% 
  ggplot(aes(x = month, y = count)) +
  labs(title = "平均訪問日数の推移（2022年4月 ホーム画面追加）") +
  xlab("年月") + 
  ylab("平均訪問日数/月") +
  theme_gray(base_family = "HiraKakuPro-W3") +
  geom_boxplot(color = "#1170aa", fill = "#c8d0d9")+
  geom_jitter(alpha = 0.2, height = 0.1, color = "#1170aa", size = 0.5)
  ggsave(file = "box.png",width= 8,height = 7)

「2022年4月にホーム画面追加したユーザー」で箱ひげ図（geom_boxplot）をプロットすると以下のようになりました。

ホーム画面に追加した月（2022-04）から四分位範囲が広がり、25パーセンタイル、50パーセンタイル、75パーセンタイルそれぞれが上昇していることが分かります。
また最大値（30日）付近のユーザーも増えているため、一定数がヘビーユーザー化したもの思われます。

2) ミドルユーザー

ライトユーザーほどは伸びていませんが、ホーム画面追加の直前の月と比較すると3~4日/月ほどリフトしていることがわかります。

3) ヘビーユーザー

大きな変化は確認できませんでした。
もともとのロイヤリティが高いため、ホーム画面追加の影響が少ないのかもしれません。

まとめ

ホーム画面への追加はライトユーザーでの行動変容が大きく、訪問日数は3.5倍程度まで伸びていたことが分かりました。¹

「Webサイトへのショートカットをホーム画面に追加できること」を知らない方も多いと思うので、サービス提供側からも設定方法を丁寧に伝えていくことで利便性の向上であったり来訪頻度の増加に繋げることができるかもしれません。

余談

Courseraで今年開講したGoogle データアナリティクスプロフェッショナル認定証プログラムではSQLはもちろんのこと、この記事で触れたようなTidyverseを使った前処理やビジュアライゼーションを学ぶことができます。Rのエコシステムを学びたい方はぜひご活用ください！

それぞれのセグメント（ホーム画面追加月）で追加翌月の平均値がリフトしているため、「交絡因子の影響を受けていない（因果関係にあるのでは）」と考えています。 ↩