BigQueryAdvent Calendar 2024

Google Play購入履歴から特定ゲームアプリの課金状況を可視化してみた（BigQuery × TROCCO）

Last updated at 2024-12-31Posted at 2024-12-31

背景

リリース当初から楽しんでいるゲームアプリ「プロジェクトセカイカラフルステージ! feat. 初音ミク（以下、プロセカ）」があり、今年の9月で4周年になりました。ふと、いままでどんな風に遊んできたのか気になり、個人でも取得可能なアプリ課金の状況を可視化してみることにしました。

なお、筆者の利用スマートフォンはAndroidのため、Google Playの購入履歴から課金状況を取得します。

　参考：プロジェクトセカイ

想定読者

以下に興味のある方
　・Google Playの購入履歴（Purchase History.json）データ構成、取得方法
　・JSONデータのBigQueryへのインポート、加工
本記事のタグに設定されているツール（特にBigQuery、TROCCO）の概要を一定把握している方

0.作成したデータパイプライン・ダッシュボード
1.Google Play購入履歴のデータ取得
2.BOXからBigQueryへのデータインポート(TROCCO使用)
3.BigQuery内でのデータ加工
4.Looker Studioを使った可視化
5.可視化した内容から分かったこと・思い出したこと
6.まとめ

0. 作成したデータパイプライン・ダッシュボード

<データパイプライン>

<ダッシュボード>

1. Google Play購入履歴のデータ取得

Google Playのデータは、Googleデータエクスポートのページよりエクスポート可能です。

【手順】

(1)Google Playに紐づけているGoogleアカウントでGoogleデータエクスポートにログインします。
　　
　・Googleデータエクスポート
　　https://support.google.com/accounts/answer/3024190?hl=ja

(2)遷移先ページで「新しいエクスポートを作成」ボタンを押下すると、エクスポートする対象を選ぶページに遷移します。

(3)「追加するデータの選択」で、Google Playストアを選びます。

以下いくつかのファイルがエクスポートされますが、今回は「購入(Google Playでの購入のリスト)」を使用します。

なお、Google PayのデータでもGoogle Playの購入履歴を取得可能ではあるのですが、Google Payの場合、Google Play以外の決済履歴も含むようである点や、購入日や購入金額の形式が以下の通り、Google Playのデータのほうが扱いやすいと考えたため、Google Playのデータを使用しました。

Google Pay
　購入日時：2024年10月12日 2:04
　金額："JPY 10,000"
Google Play
　購入日時："2024-08-17T04:16:53.500Z"
　　　金額："¥10,000"

Google Playデータは、「Purchase History.json」というファイル名で、以下データ構成になっています。

Purchase History.json

[{
  "purchaseHistory": {
    "invoicePrice": "¥10,000",
    "paymentMethodTitle": "[決済手段(クレジットカードの場合は、ブランド + 番号の末尾4桁)]",
    "userLanguageCode": "ja_JP",
    "userCountry": "JP",
    "doc": {
      "documentType": "In App Item",
      "title": "クリスタル G (プロジェクトセカイ カラフルステージ！ feat. 初音ミク)"
    },
    "purchaseTime": "2024-08-17T04:16:53.500Z"
}]

上記JSONファイルの各要素に何が設定されているか、Googleの公式から探すことがうまくできなかったのですが、実際に設定されている内容から以下を想定して、データを加工、可視化を進めていくことにします。

要素	設定内容(想定)	設定値例
invoicePrice	請求金額	¥10,000
paymentMethodTitle	決済手段	クレジットカードの場合は、ブランド(例:VISA) + 番号の末尾4桁)
userLanguageCode	利用言語	ja_JP
userCountry	利用国	JP
documentType	課金方法の分類（アプリ内課金 or サブスクリプション）	「In App Item」、「Subscription」
title	購入アイテム、プラン等	クリスタル G (プロジェクトセカイカラフルステージ！ feat. 初音ミク)
purchaseTime	購入日時(UTC)	2024-08-17T04:16:53.500Z

(4)「ファイル形式、エクスポート回数、エクスポート先の選択」で、以下指定を行い、zip形式でBOXというストレージサービスにエクスポートします。

　エクスポート先：BOXに追加
　ファイル形式：.zip

しばらくすると、「takeout-[エクスポート日(yyyymmdd)]」から始まるzipファイルが、指定のBOXにエクスポートされます。

2. BOXからBigQueryへのデータインポート(TROCCO使用)

TROCCOのデータ転送機能を使い、BOX上のGoogle Playの購入履歴（Purchase History.json）をインポートします。今回初めて知ったのですが、TROCCOの場合、BOXに格納されているJSONファイルを、ZIPで圧縮された状態でBigQueryにインポートができます。なお、今回は、JSONファイルの使用する要素（項目）を自由に変えやすいように、データ転送段階では、対象要素を絞り込んだり、パースしたりせずに、1カラムとしてデータを格納しました。

(1)接続設定
　以下マニュアルを参考に、BOX、BigQueryの接続情報を設定します。
　　BigQueryの接続情報
　　 Boxの接続情報

(2)BOXからBigQueryへの転送設定
　　①「転送設定」画面から、転送元：BOX、転送先：Google BigQueryの転送設定を作成します。

②転送元 BOXの設定(マニュアル)
以下の通り設定します。

項目	設定値	備考
BOX接続情報	(1)で設定した接続情報を指定
フォルダID	取込対象のZIPファイルの格納場所URLのフォルダID部分　https://app.box.com/folder/[フォルダID]
ファイルプレフィックス	takeout-	取込対象ファイルのプレフィックス
解凍形式	zip
解凍後の相対パス	.*/Purchase History\.json	正規表現の設定可。「Purchase History.json」のみを指定したいため、相対パスを指定。また、.(ドット)のエスケープをするためにバッククォート(\)を指定した。
入力ファイル形式	JSONPath
JSONPath	$.*	全指定（※）。JSONファイルの特定要素のみではなく、全要素を取得できる形式にした。

※参考：TROCCO:入力ファイル形式の設定について

③転送先 BigQueryの設定(マニュアル)
BigQueryの接続情報や、転送先のテーブルの情報（データセット、テーブル、データセットのリージョン、転送モードを設定します。転送モードについて、今回は「全件洗い替え(REPLACE)」を指定します。

④接続確認の上、自動データ設定を行い、カラム定義の自動設定やデータのプレビューを行います。「保存して適用」ボタンを押下すると設定完了です。

⑤データ転送処理の実行
　実行ボタンを押下するとデータ転送処理が実行されます。しばらくして実行結果を見て、ステータス「SUCCESS」になっていれば、転送処理が正常に完了しています。

うまく取り込めると以下の通り反映されます。

3. BigQuery内でのデータ加工

BigQueryにデータを取り込むことはできましたが、後続のBIツール（Looker Studio）での可視化にはいくつかの課題がありました。そのため、右側に記載した対応策を反映し、BigQueryのWEBUI上で、年次、月次、日次ごとに、①購入日、②購入金額、③購入内容、④購入分類が集計できる可視化用ビューを作成しました。

	課題	対応策
1	JSON形式：JSON形式のため、各要素の設定値が取得しづらい。	必要な要素のみ取得し、各カラムとして取得。
2	タイムゾーンと日付粒度：「purchaseTime」がUTCの日時型のため、例えば、日本時間(JST)の日単位で確認したい場合に、都度加工が必要	日本時間(JST)、かつ、集計したい日付単位（日、月、年）の購入日付のカラムを保持したビューを作成する。
3	アプリ単位の集計の手間：「title」には、アプリ単位（例：プロセカ）の設定値がなく、たいてい「アイテム名/プラン名 + アプリ名」が設定されており、アプリ単位での集計に不向き	実際のデータから設定値の規則性を確認し、購入分類を変換するUDFを作成する。

<加工前>

<加工後>

処理の流れ

(1)利用アプリ判定用のユーザ定義関数(UDF)の作成
(2)JSONデータの必要要素の取得、各カラムへの設定
(3)日付、金額項目の変換
(4)利用アプリ判定、分類
(5)可視化用「プロセカ」ビューの作成

(1)利用アプリ判定用のユーザ定義関数(UDF)の作成

「title」要素には購入アイテム名が含まれており、以下例のように、同じアプリの課金でも異なるアイテム名が設定されています。こちらをどのアプリを利用しているか分類するため、共通で設定されている「プロジェクトセカイ」を含むタイトルの場合に「プロセカ」と分類するCASE文を作成しました。今回だけであれば直接ビューに記載可能ですが、今後別のアプリにも対応できるように、他のアプリの分類を追加した形でユーザ定義関数（UDF）として作成しました。

（例）

カラフルパス～BASIC～ (プロジェクトセカイカラフルステージ！ feat. 初音ミク)
クリスタル G (プロジェクトセカイカラフルステージ！ feat. 初音ミク)

google_play_store.invoice_category_convert

CREATE OR REPLACE FUNCTION
    `[プロジェクト名].google_play_store.invoice_category_convert`(invoice_item STRING) AS (
CASE
    WHEN invoice_item LIKE '%LINEマンガ%' THEN 'LINEマンガ'
    WHEN invoice_item LIKE '%ヤンジャン%' THEN 'ヤンジャン'
    WHEN invoice_item LIKE '%プロジェクトセカイ%' THEN 'プロジェクトセカイ'
    /* ～実際は、他のアプリの分類のCASE文も設定しているが、省略～*/
ELSE NULL END
)

(2)JSONデータの必要要素の取得、各カラムへの設定
購入履歴データ（Purchase History.json）より、以下3要素をそれぞれ1カラムの値として取得します。

項目名(物理)	項目名(論理名)	変換内容
invoice_item	請求内容	Purchase History.jsonから、'$.doc.title'の値を取得
invoice_price	請求金額	Purchase History.jsonから、'$.invoicePrice'の値を取得
purchase_date	購入日	Purchase History.jsonから、'$.purchaseTime'の値を取得

(3)日付、金額項目の変換
　BIツール上で集計がしやすいように、以下の通り変換します。

項目名(物理)	項目名(論理名)	変換内容
invoice_price	請求金額	通貨単位（￥）の削除。文字型から数値型への変換
purchase_date	購入日	タイムゾーンの変換(UTC→JST)。日時から日(DATE)への変換

(4)利用アプリ判定、分類
(1)で作成した利用アプリ判定用のユーザ定義関数(UDF)(google_play_store.invoice_category_convert)に、購入内容(title)を指定し、アプリごとの分類に変換します。この項目は請求カテゴリ(invoice_category)カラムの設定値として利用します。

(1)～(4)の対応を行うビュー定義と抽出結果の例は以下の通りです。

<ビュー定義>

google_play_store.purchase_history_summaryビュー

SELECT
    JSON_VALUE(purchaseHistory,'$.doc.title') AS invoice_item,
    CAST(REGEXP_REPLACE(JSON_VALUE(purchaseHistory,'$.invoicePrice'),'[^0-9]','') AS INT64) AS invoice_price,
    DATE(TIMESTAMP(JSON_VALUE(purchaseHistory,'$.purchaseTime')),'Asia/Tokyo') AS purchase_date,
    `google_play_store.invoice_category_convert`(JSON_VALUE(purchaseHistory,'$.doc.title')) AS invoice_category 
FROM
    `[プロジェクト名].google_play_store.purchase_history`

<抽出結果例>

【参考】Purchase History.json(再掲)

[{
  "purchaseHistory": {
    "invoicePrice": "¥10,000",
    "paymentMethodTitle": "[決済手段(クレジットカードの場合は、ブランド + 番号の末尾4桁)]",
    "userLanguageCode": "ja_JP",
    "userCountry": "JP",
    "doc": {
      "documentType": "In App Item",
      "title": "クリスタル G (プロジェクトセカイ カラフルステージ！ feat. 初音ミク)"
    },
    "purchaseTime": "2024-08-17T04:16:53.500Z"
}]

(5)可視化用「プロセカ」ビューの作成
　請求カテゴリ(invoice_category)を「プロジェクトセカイ」のみに絞り、様々な集計単位で集計できるように、購入日付を「年」、「月」、「日」それぞれの項目に分割するようにしました。

<ビュー定義>

SELECT
  invoice_category,
  DATE_TRUNC(purchase_date ,YEAR) AS purchase_year, -- 購入年
  DATE_TRUNC(purchase_date ,MONTH) AS purchase_year_month, -- 購入月
  purchase_date, -- 購入日
  invoice_price, -- 請求金額
  document_type
FROM
  `[プロジェクト名].google_play_store.purchase_history_summary`
WHERE
  invoice_category ='プロジェクトセカイ'
ORDER BY
  purchase_date DESC

＜抽出結果例＞

4. Looker Studioを使った可視化

前述のBigQueryで処理した可視化用「プロセカ」ビューを、Looker Studioに接続し、可視化を行いました。

こちらの可視化では、年次・月次・日次の課金回数、課金額を確認可能です（＜全体像＞の画像参照）。また、特定の年、月を指定すると、その年、月に一致、または、含んでいる年、月、日のデータに絞りこむことが可能です。（＜特定の期間で絞込＞の画像参照）

＜集計定義＞

項目	定義
課金回数	レコード件数
課金金額	請求金額(invoice_price)の合計

以下都合により、2020年、2024年の集計対象期間は、1年(12か月)ではありません。今回の考察の対象にはしていない期間ですが、より精度高く比較する際は、対象期間を揃える等の工夫していこうと思います。

2020年：リリース日が2020年9月末のため、訳3か月分(10～12月)分のデータ
2024年：データ取得日の都合で、約10か月(1～10月)分のデータ

＜全体像＞

＜特定の期間で絞込＞
　例：2021年6月の場合以下が集計対象になる。

集計単位	集計範囲
年次	2021年
月次	2021年6月
日次	2021年6月1日～2021年6月30日の課金が発生している日

5. 可視化した内容から分かったこと・思い出したこと

(1)2021年、2022年、双方で、課金額は高めだが、2022年の方が課金回数が少ない。
　高額セット（例：1万円の「クリスタル G」セット）を少ない回数購入した方が効率が良いと考えたのではないかと予測している。なお、この予測が正しいかの確認には、実際に2021年よりも2022年の方が高額セットを購入している頻度が高いことの確認が必要そうである。

(2)好きなキャラクターの誕生日、イベントの開始日に課金していることが多い。
　誕生日やイベント開始日に課金していることが多く、特定のアイテムやカードを手に入れるための課金が行われていたと思われる（記憶の限りはおそらく合っていそう）。

6. まとめ

プロセカ4周年の記念として、自分自身のGoogle Playの課金履歴データをインポート、可視化をすることができました。今回。可視化したデータを見て、どんな風に遊んだか振り返ることはあまりできませんでしたが、一度データをインポートしておけば色々深堀できそうです。時間を見つけて、もう少し色々な視点でデータを見ていきたいです！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up