Looker Studio で、生年月日から「CV時点の年代」を算出する Case文

ちょくちょく使うようになってきたので。

Looker Studio（旧データポータル）で、コンバージョンの分析を行うときに、CV情報に年齢は入っていないが、生年月日は入っており、それをもとにCV時点の年代を分類して、年代別の傾向を把握したいということがあります。

また、Googleシグナルの情報を使って年代別のCV傾向を把握しているが、サンプル数が少ないし、基幹データとものすごくずれている気がするので、基幹のデータと比較してズレの程度を確かめたい、というときも同じことをすると思います。直近で必要になったケースはこれでした。

このとき、

生年月日はこれで、CV日はこれ
２つの差は◯日、年に直すと◯年
年代にすると◯◯

という感じの Case文を書く必要がありますが、毎回ほとんど同じ内容で１から調べるのは面倒なので、ここにメモしておきます。

Case文
解説
Looker Studio の設定
余談

Case文

SQL

CASE
  WHEN FLOOR(DATE_DIFF(注文日, 生年月日) / 365.25) BETWEEN 18 AND 24 THEN '18-24'
  WHEN FLOOR(DATE_DIFF(注文日, 生年月日) / 365.25) BETWEEN 25 AND 34 THEN '25-34'
  WHEN FLOOR(DATE_DIFF(注文日, 生年月日) / 365.25) BETWEEN 35 AND 44 THEN '35-44'
  WHEN FLOOR(DATE_DIFF(注文日, 生年月日) / 365.25) BETWEEN 45 AND 54 THEN '45-54'
  WHEN FLOOR(DATE_DIFF(注文日, 生年月日) / 365.25) BETWEEN 55 AND 64 THEN '55-64'
  ELSE '65+'
END

解説

DATE_DIFF(X,Y)

2 つの日付の間の日数を計算する。DATETIME_DIFF() という新しい関数があり、本当はこっちを使ったほうが良いみたい。

戻り値は「X – Y」なので、先に書くほうの数字がより新しい日付になるようにする。

365.25

日を年に変換する。0.25は4年に1度のうるう年分。これ、もうちょっと良いやり方ある気がする。

FLOOR()

指定された値以下の最も近い整数を返す。つまり正の数なら「小数切り捨て」。負の数だと切り上げになっちゃうので注意。

年代の区分

この中途半端は区分は、Googleシグナルが採用しているもので、それにならって年齢をCase文で分類する。

Looker Studio の設定

Looker Studio > 追加済みのデータソースの管理 > 編集 > フィールドを追加

で、さきほどのSQLを下記のように入力します。

あと一応、データの検証用に、下記のように年代を分類する前の「年齢」というフィールドを同じ要領で追加しておくと、なにかと都合が良いです。

SQL

FLOOR(DATE_DIFF(注文日,生年月日)/365.25)

余談

AI Programmer という、日本語の命令文を書くとAIが勝手に相当するプログラムを作成してくれるサービスがありまして、今回のようによくあるケースでは、これを使うとけっこうそれっぽいプログラムを作成してくれます。

たとえば本稿のタイトル「Looker Studio で、生年月日から「CV時点の年代」を算出する Case文」をアホみたいにそのまま入力するとこうなります。

けっこうそれっぽい。。。。。

ただ、このままだと使うことができないので、最終調整はする必要があります。よくあるプログラムであればあるほど、精度が高くなります。また、同じ命令でも都度違う結果が帰ってきたりするので、イマイチな結果が帰ってきた場合は命令文の言い回しをちょっと変えたりすると、うまくハマったりします。

※サムネ画像引用元：下記の動画からキャプりました