見出し画像

分析基盤をGA3からGA4&BigQueryに移行した話

木庭有基

この記事はなにか

justInCaseTechnologiesのマーケティング事業部でうごうごしてるマーケターが、SQLなにそれ状態から各相対企業での施策結果シートをSQLで構築するところまでのプロセスや、やったこと・やってよかったことを紹介します。

ちなみに↓の記事は、まさに自分が担当させて頂いている企業の紹介です。

用語の定義

  • Google Analytics 3 - GA3

  • Google Analytics 4 - GA4

  • GCP - Google Cloud Platform

  • スプレッドシート - スプシ

  • シート - 1スプレッドシート内のシート

分析基盤の大事な前提

justInCaseTechnologiesの本事業においては個人情報は保持していません。

GA3からGA4に移行した背景

GA3の終了を見据えて

Googleが2023年7月1日にGA3やめるからよろしく宣言を出したので、移行しはじめないとあかんなということで、2022年4月頃から移行開始。

アドオン・関数を用いスプシ上で構築していた集計をBigQueryで行う運用にしたかった

  • 担当者によってはシートに手動で数字書いたり行列増やしたり、関数を書かず直打ちして参照元不明など魔境化。担当者以外では操作できない、読み解けないデータ構造になっていた

  • 各シートを連結するためのKEY列を都度作る必要があるためシートがノイジーだった

  • スプレッドシート内のシートが多すぎてよくわからない問題

  • 関数が多く、計算が走るためスプレッドシートの挙動が徐々に鈍重に

といった問題や事象が多々あるままスプシを複数企業で横展開していたので、集計は基本BigQueryに寄せ、スプシはアウトプット+ちょっと記述のためのツールという立ち位置にしたかった。

移行前後の集計分析基盤フロー全体像

前提

1申込者が本人以外の配偶者・親族 x がん・医療・介護など複数商品申込むという商品設計。GAの管理画面上では申込者数・申込件数・収入保険料という概念を表現できないため、スプシにアウトプットしている。

Before - 全力スプシ関数生活

スプレッドシートの見えないところで大量の関数がうごめいてるよ

After - 極力GCPで処理生活

色がいっぱいあってきれいダ

やってること：運用面

GA4で取得するデータはすべてGCPに投げる運用にした

集計分析に不要な項目があるとしても、データは全部GCPに投げる運用にし、あとから必要になったときに悲しいことにならないようにしました。

GA4管理画面上でコンバージョン等は設定せず、GA4はただのハブとした

  • コンバージョンとしてカウントしたいページURL、eventをBigQueryで集計すればいいだけじゃね？

  • 管理画面上でコンバージョン等設定すると引き継ぎ漏れ、それなんだっけになりがち

という観点で計測したいページにGA4を置くだけの運用にしました。

一部手入力やコピペ欄もある、自動化と手入力のほどよい共存化

相対企業からもらうデータがエクセルだったり、施策結果等は手入力する必要があるので自動化のうえに、一部手入力をするようにしました。

ほとんどマスクしてて申し訳なく

やってること：技術面

1_お申込みデータを相対企業ごとに集計しつつ+α

  • お申込みのない日があっても日付が歯抜けにならないよう日付を追加処理

  • 全体とutmパラメータ単位でお申込み件数等を集計

2_1とGA4データ共通のutmパラメータをkeyとして合体

  • GA4から訪問ユーザー数を集計

  • ↓のようなSQLを書いてGA4データとS3のデータをJOIN

SELECT
  CONCAT (LEFT(a.Delivery_Date,4),'-',SUBSTR(a.Delivery_Date,5,2)) AS Delivery_Month,
  a.utm_campaign,
  a.utm_source,
  COALESCE(a.Visit_user,0) AS visit_user,
  COALESCE(b.ある商品の申込件数,0) AS xxxx_count,
  COALESCE(b.ある商品の収入保険料,0) AS xxxx_premium
FROM
  `xxxx.warehouse.Results_by_Detail_ga4` AS a
LEFT OUTER JOIN
  `xxxx.warehouse.Results_by_Detail_s3` AS b
ON
  a.utm_campaign = b.utm_campaign
WHERE
  a.Delivery_Date IS NOT NULLなど
GROUP BY
  a.utm_campaign,
  a.utm_source,
  a.Delivery_Date,
  a.Visit_user,
  b.ある商品の申込件数,
  b.ある商品の収入保険料
ORDER BY
  a.utm_campaign ASC

その他やってること

  • 言いたいことも言えないこんな世の中じゃポイズン

やってよかったこと・わかったこと

  • あとで困らないように、とりあえずGA4をつかってデータを全部BigQuery（有料版）に送っておくのは大事

  • 複数企業と相対する場合、結果フォーマットは極力統一し、初見者でも理解・把握できるようにするのは大事

  • 売上を作る施策・ユーザビリティ向上方法等の仮説検証施策をじっくり考えるために、分析結果はほぼ自動で出力できるようにすべき

  • とりあえずやってみる、でほぼ１人でSQLなにそれ？から始めた。時間がかかった一方、SQL記述についての知見や、記述方法の検索勘所がわかるようになった

    • ググってもわからないことは社内のSQLマスター https://twitter.com/KoizumiJp に聞いてた（今も聞いてる）。感謝しかない

終わりに｜SQLでの分析が普及したらいいな

SQLで分析を運用できるようになると、ロジックの組み立て力もつくし、なによりどのようなロジックで分析が成り立ってるかがSQLとして残るから、他者とのすり合わせが楽になります。（VLOOKUP等関数重ね合わせとか結構読み解けないし、スプシ内で書きづらいからね…）

この記事が参加している募集

#最近の学び

181,685件

#マーケティングの仕事

7,043件

この記事が気に入ったらサポートをしてみませんか？