【Looker Studio】カスタムクエリを使用｜昨年比（同曜日）の出し方

はじめに

今回はLooker StudioとBigQueryを併用して、昨年比（同曜日）を算出する方法をご紹介します。

Looker StudioはBIとして優れているので、ボタン操作だけで昨年比を出すことができます。しかしそれはあくまで同日による比較。これは小売業者を含む、曜日ごとに比較をしたい方にとっては致命的とも言えます。
この解説の中では、BigQueryのチカラを借りて曜日比較による昨年比を算出するまでの流れを紹介いたします。

「BigQueryを使わない方法はないの？」

と思った方。ご安心ください。
Looker Studioの統合機能と計算フィールドをフル活用して算出する方法がありますので参考にしてください。

Looker Studio

【Looker Studio】どこよりもやさしい｜昨年比（同曜日）の出し方

それでは解説に入ります。

昨年比（同曜日）の出し方

今回使用するデータは、Tableauという別のBIツールに付属しているサンプルデータです。こちらのデータをBigQueryに保存して使用します。赤枠で囲んだ部分が今回の解説で登場するカラムです。

order_date：注文日
sales：売上

昨年比(同曜日)を算出するためのロジック概要

まず大前提として、販売実績の日付が365日分、揃っていないことを考慮する必要があります。当年と昨年の実績を比較する際に、どちらかの日付が欠落していると比較ができないからです。

Looker Studioだけを使用する解説の中では、同じテーブルを完全外部結合した後、計算フィールドの中でnullを埋め合わせることで昨年比の算出を実現しました。しかし今回はSQLを使用できることもあって、もう少し違ったアプローチで昨年比を算出していきます。

仮に以下のような日付が飛び飛びのテーブルを想定します。

まずはこのテーブルから、日付が飛び飛びになっていない状態の日付カラムを作成します。

オーダー日からマイナス52週した日付＝昨年（同曜日）のカラムを作成後、オーダー日と昨年（同曜日）のそれぞれに売上を左外部結合でリレーションします。

こうすることで、飛び飛びになっていた日付をすべて埋めながら当年と昨年の実績を持ったレコードを作成することができます。

クエリ実装

今回はLooker Studio内でカスタムクエリを使用します。当然ながらBigQueryにデータマートを作成する方法もあるため、運用コストやデータ分析の柔軟性に合わせて選択をしてください。

『データのレポートへの追加』から、『BigQuery』を選択します。

『カスタムクエリ』を選択して、課金プロジェクトのプロジェクトIDを入力します。

クエリ入力欄にクエリを入力します。

--日付が完全に揃ったカラムを作成
WITH generate_date AS
(
 SELECT
  DATE_ADD(min_date, INTERVAL n DAY) AS order_date
 FROM
  (
   SELECT
    MIN(order_date) AS min_date --order_dateの最小値
    ,GENERATE_ARRAY(0,DATE_DIFF(MAX(order_date),MIN(order_date),DAY)) AS n --日付の最大値と最小値の日数差で配列生成
   FROM `your_pj.your_dataset.sample_data_for_looker_studio` 
  )
  ,UNNEST(n) AS n --配列をunnest処理
)

--日付単位で売上を集計
 ,agg_table AS
(
 SELECT
  order_date
  ,SUM(sales) AS sales
 FROM `your_pj.your_dataset.sample_data_for_looker_studio`
 GROUP BY order_date
)

--完全に揃っている日付カラムに対して売上をleft join
SELECT
 a.order_date
 ,IFNULL(b.sales,0) AS sales_current_year
 ,IFNULL(c.sales,0) AS sales_last_year
FROM generate_date AS a
 LEFT JOIN  agg_table AS b --当年売上としてleft join
  USING(order_date)
 LEFT JOIN agg_table AS c
  ON DATE_SUB(a.order_date,INTERVAL 52 WEEK) =c.order_date --昨年売上としてleft join

グラフ作成

今回はデータ確認のためにシンプルな表を作成します。

sales_current_year：カラムをそのまま使用
sales_current_last_year：カラムをそのまま使用
YoY：SUM(sales_current)/SUM(sales_last_year)

グラフが完成しました。

左のグラフがカスタムクエリで作成した加工後のテーブル。右２つが加工前のテーブルです。検算しやすいように期間を当年と昨年で並べました。加工前には欠落していた日付レコードが、加工後には「0」を使用して補完されていることがわかります（例：加工前のテーブルには2022/1/6のレコードが欠落しているが、加工後には「0」を使用して補完されている）。

これで操作は以上です。

まとめ

Looker StudioとBigQueryを使用して昨年比（同曜日）を算出する方法を解説しました。SQLを使用することで、Looker Studioだけで無理くりやっていたような分析も簡単に実現することができます。この記事が少しでもお役に立てていれば幸いです。

ここまでお読みいただきありがとうございました！

この記事を書いた人

松原 健太
（まつばら けんた）

株式会社UNCOVER TRUTH
ビジネスデベロップメントグループ DX-Acceleratorチーム　チームリーダー
2022年、UNCOVER TRUTHに中途入社。
大手GMS・外資系SPA・大手百貨店といった小売業界を経験後、一念発起してIT業界に転身。
広告代理店・マーケティング支援会社などのデータ業務を支援。よく使うツールはBigQueryとLooker Studio、ときどきTableau。