概要
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倍程度まで伸びていたことが分かりました。1
「Webサイトへのショートカットをホーム画面に追加できること」を知らない方も多いと思うので、サービス提供側からも設定方法を丁寧に伝えていくことで 利便性の向上であったり来訪頻度の増加に繋げることができるかもしれません。
余談
Courseraで今年開講したGoogle データアナリティクス プロフェッショナル認定証プログラムではSQLはもちろんのこと、この記事で触れたようなTidyverseを使った前処理やビジュアライゼーションを学ぶことができます。Rのエコシステムを学びたい方はぜひご活用ください!
-
それぞれのセグメント(ホーム画面追加月)で追加翌月の平均値がリフトしているため、「交絡因子の影響を受けていない(因果関係にあるのでは)」と考えています。 ↩