広告で売上粗利を最大化するために、GA4とBigQueryを使ってLTVを計算した話
この記事のターゲット
・インターネット広告を運用している方。
・BigQueryが使える環境にあって、サービスのLTVを算出したい方。
・LTVを算出するクエリのサンプルを確認したい方。
目次
・はじめに
・中古車EXとは
・LTVがなぜ広告運用に必要なのか
・LTVの計算式
-平均顧客単価
-平均CV頻度
・GA4とBQの連携
・実際のクエリ
・終わりに
はじめに
こんにちは。株式会社じげんに2022年4月に新卒として入社した逆瀬川（さかせがわ）と申します。大学が教養学部かつ、内定者インターンの経験もなかったので、ITに関する知識はほぼ皆無の状態で入社しました。
現在は、じげんの自動車Div.で中古車情報サービス「中古車EX」のマーケティングと、中古車輸出プラットフォーム「TCV」の加盟店集客を担当しています。
未経験から新卒でマーケティング職に配属され、「エスキューエル？」「でーたべぇす？」の状態から、実践の中でSQLを学び、BigQueryからある程度自分が欲しい情報を抽出できるくらいには成長できました。
マーケティングにおける意思決定に最も必要なものは、言わずもがなデータです。
新規施策を起案する際に、蓋然性があるのか？という上長の問いに対して、YESと返答出来るようになるためには、それを根拠づけるデータが必要です。
じげんでは、BigQueryにデータを集約しており、クエリを叩いて、欲しいデータを加工・抽出し、マーケティングに活かしています。
今回、GA4とBigQueryを使用してLTVを算出したところ、WebのユーザーよりもアプリのユーザーのLTVが高い事がわかりました。その算出の流れについて以下で書いています。
中古車EXとは
中古車EXは、じげんが運営する全国の中古車情報を検索できるポータルサイトです。
2022年12月現在、48万台以上の中古車が掲載されており、実際の販売店舗に在庫確認や見積り、来店希望の問合せをすることができます。
ビジネスモデルは、ひとつの問合せが発生する都度、提携クライアントから手数料をいただくモデルです。
LTVがなぜ広告運用に必要なのか
LTVは、”Life Time Value”の3つの単語の頭文字を取った言葉で、顧客ひとり当たりの価値を意味します。
設定したある期間のうち、ひとりの顧客がどれだけサービスの売上に貢献するかを測る指標として用いられます。
そもそも広告運用の際に、LTVがどのように使われるかというと、主に目標CPAを設定する際に利用されます。
例えば、「目標CPAを短絡的に、顧客単価よりも低ければ良い」という決め方をすると、LTVを加味したCPAよりも安くなるため、広告が表示されるユーザーが縮小されます。
平均顧客単価は顧客1人あたりの1取引で発生する金額の平均であるのに対して、LTVは1顧客あたりの設定期間内の取引で発生する金額の平均であるからです。
よって、瞬間的な粗利率は高いが、通期で見た際に、売上・粗利が小さくなる場合があります。逆にLTVを加味すると、上記で設定したCPAよりも高い目標CPAで広告出稿が出来るため、より多くのユーザーに広告が表示され、粗利の最大化やROASの適正値を見つけやすくなります。
つまり、広告運用にとってサービスのLTVを導き出すことは不可欠なのです。
今回は新しくGoogle Play StoreにてPWAという形式でアプリをリリースしたため、その広告出稿をするためにLTVを算出しました。参照するデータは、既に広告出稿を始めているiOSアプリとPWAに関するものです。
LTVの計算式
今回使うLTVの計算式は以下です。LTVの計算式は数多く存在しますが、今回は
平均顧客単価×設定期間の平均CV頻度
で算出します。
それぞれの因数を更に分解していきます。
平均顧客単価
平均顧客単価を算出するためには、設定期間(今回は1年)の売上金額の合計と設定期間の取引回数の合計が必要です。
例えば売上が100万円で取引回数が100回だとすると平均顧客単価は1万円となります。
中古車EXでは、顧客単価はクライアントとの契約上一定となっているので、今回はBigQueryを使用して抽出はしないですが、必要であれば上記2つのカラムを取り出すクエリを書いて抽出します。
平均CV頻度
1年間の平均CV頻度を算出するためには、1回の取引を特定するものとユニークユーザー数が必要です。（取引数/ユニークユーザー数）つまり、全取引が格納されているテーブルとその取引を行ったユーザーを特定するIDが必要です。
以上を踏まえて、データベースから抽出したいデータのイメージは下記になります。
GA4とBQの連携
次に、Google アナリティクス4をBig Queryにリンクします。
Google アナリティクス4で保管している情報をBig Queryに移動させて、クエリを走らせることで加工しやすいデータを抽出できます。
本記事では、SQL初心者の方でマーケティング担当者をターゲットとして想定しているため、詳細説明は割愛します。
連携方法は以下Google Supportに記載されています。
https://support.google.com/analytics/topic/9359001?hl=ja&ref_topic=9306488
実際のクエリ
今回のカラムの定義は以下に記載されています。
https://support.google.com/firebase/answer/7029846?hl=ja
WITH
sess_start AS(
SELECT
-- イベントが発生した日付
event_date,
-- userを特定するid
user_pseudo_id,
-- 流入経路
traffic_source.name,
-- 流入経路のカテゴリ(cpc,organicなど)
traffic_source.medium,
-- 一番最初の流入経路（google,yahoo,lineなど）
traffic_source.source,
FROM
`[project_id].analytics_[view_id].events_*`
WHERE
event_name = 'session_start' -- サイト訪問
GROUP BY
event_date,
user_pseudo_id,
name,
medium,
`source`),
inquiry_done AS (
SELECT
event_date,
user_pseudo_id,
-- ブラウザ名
CASE
WHEN device.web_info.browser = "Android Webview" THEN "PWA"
WHEN device.web_info.browser = "Safari (in-app)" THEN "iOSApp"
WHEN device.web_info.browser = "(not set)" THEN "not-set"
ELSE
"Others"
END
AS device,
COUNTIF(event_name = '問い合わせ完了') AS inquiries
FROM
`[project_id].analytics_[view_id].events_*`
GROUP BY
event_date,
user_pseudo_id,
device)
SELECT
i.event_date,
s.user_pseudo_id,
device,
name,
medium,
`source`,
inquiries,
FROM
sess_start AS s
LEFT JOIN
inquiry_done AS i
ON
s.user_pseudo_id = i.user_pseudo_id
AND s.event_date = i.event_date
WHERE
s.event_date >= '2021-12-24'
AND inquiries > 0
WITH句説明
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja
CASE文説明
https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ja
上記クエリでデータを抽出し、スプレッドシートで以下のように加工しました。
加工後(例)↓
設定期間(今回は1年)の平均CV頻度が出せたので、
平均顧客単価×設定期間の平均CV頻度
にあてはめてLTVを算出します。
終わりに
今回は広告運用に必要不可欠なLTVをGA4とBigQueryを使ってデータをどのように抽出し、算出したかについて書きました。
マーケティングに必要なデータをSQLを使って抽出できるようになるためには、初めに
SELECT,FROM,WHEREの3つさえ理解して、後は実践のみだと感じました。分からなかったら即検索で、それでも分からなかったら周りの先輩に聞くのが一番早いです。(じげんは優しい先輩が多いので聞きやすいかも？)
また、１回で完璧なSQLを書くよりも一旦書いてエラーを出してから、修正する方が圧倒的に効率が良いです。
自分が欲しいデータを抽出することは、目的ではなく意思決定をするための手段です。
いち早くSQLマスターして新規施策をどんどん出せるよう一緒に頑張りましょう！
