GLASS
相談する

Looker Studioでアメリカ式の週番号＆日曜始まりの週を実装する方法

Looker Studioでアメリカ式の週番号＆日曜始まりの週を実装する方法

こんにちは。GLASSのコンサルタント、芝田です。
今回はLooker Studioでアメリカ式の週番号＆日曜始まりの週を実装する方法の紹介です。

Looker Studioダッシュボードカスタマイズ代行サービス

週番号の数え方は3種類ある

ややこしいことに週番号の数え方は主に以下の3種類あります。
このうち日本でなじみがあるのはアメリカ式とヨーロッパ式の2種類かと思います。

方式名週の始まり第1週の定義
アメリカ式日曜日1月1日を含む週
ヨーロッパ式（ISO 8601式）月曜日年の最初の木曜日を含む週
イスラム式土曜日年の最初の金曜日を含む週

ヨーロッパ式の実装方法

Looker Studioでヨーロッパ式を実装したい場合は、以下のように設定を少し変更するだけで出来ます。

  1. ヨーロッパ式を実装したい図表を選択（ディメンションに日付が設定されている想定）
  2. 日付の左側にあるカレンダーアイコンをクリック
    日付の左側にあるカレンダーアイコン
  3. データの種類　＞　日付と時刻　＞　ISOの年と週　をクリック
    日付のデータの種類　＞　日付と時刻　＞　ISOの年と週

これだけです。簡単ですよね。
確認のため、年末年始の日単位と週単位の対応表を出してみました。

ヨーロッパ式の実装結果

ヨーロッパ式の定義通り、月曜始まりで、第1週は年の最初の木曜日を含む週となっています。

おまけ：日付に曜日を表示する方法

  1. 日付の左側にあるカレンダーアイコンをクリック
    日付の左側にあるカレンダーアイコン
  2. 表示フォーマット　＞　カスタム日付形式　をクリック
    日付の表示フォーマット　＞　カスタム日付形式
  3. 入力欄に yyyy/MM/dd(ccc) と入力して「適用」をクリック

“ccc“が曜日を表す文字列なので覚えておくと便利です。

アメリカ式の実装方法

一方、Looker Studioにはアメリカ式にする設定が用意されていないため、自分で実装する必要があります。
実装方法ですが、データソースがGoogleアナリティクス（GA4）か、それ以外（BigQueryなど）かで少し異なるので別々に説明します。

アメリカ式の実装結果

先に実装結果をお見せします。

アメリカ式の実装結果

アメリカ式の定義通り、日曜始まりで、第1週は1月1日を含む週となっています。

データソースがGA4以外の場合

まず先にデータソースがGA4以外の場合の実装方法を説明します。
私が動作確認したのはBigQueryのデータですが、GA4以外のデータソースならば多分大丈夫だと思います。
もしうまくいかない場合は、後述するデータソースがGA4の場合を試してみてください。

ちなみに、なぜGA4の場合を後回しにして、こちらを先に説明するかというと、こちらのほうが実装が簡単だからです。
GA4の場合は関数に謎の制限（バグ？）があり、それを回避するために複雑になっています。
ただ、処理の流れや考え方は同じで、使用する関数が異なるだけです。

①週の開始日を計算する

データソースに新しい計算フィールドとして以下の「週の開始日」を追加します。

DATE(
	DATETIME_SUB(
		日付 ,
		INTERVAL WEEKDAY(日付) DAY
	)
)
処理の内容
  1. WEEKDAY関数で日付の曜日を表す整数（日曜が0～土曜が6）を求める
  2. DATETIME_SUB関数で上記の整数分の日数を日付から引き算することで、その日付が属する週の日曜日の日付（＝週の開始日）を求める
  3. DATETIME_SUB関数の戻り値は「日付と時刻」型になっているので、DATE関数で「日付」型に変換する

②週の終了日を計算する

データソースに新しい計算フィールドとして以下の「週の終了日」を追加します。

DATE(
	DATETIME_ADD(
		週の開始日, INTERVAL 6 DAY
	)
)
処理の内容
  1. DATETIME_ADD関数で先ほど求めた「週の開始日」に6日を加算することで、その日付が属する週の土曜日の日付（＝週の終了日）を求める
  2. DATETIME_SUB関数の戻り値は「日付と時刻」型になっているので、DATE関数で「日付」型に変換する

③週番号を計算する

データソースに新しい計算フィールドとして以下の「週番号」を追加します。

DATETIME_DIFF(
	週の終了日,
    DATETIME_ADD(
    	DATE(YEAR(週の終了日),1,1),
        INTERVAL (6 - WEEKDAY(DATE(YEAR(週の終了日),1,1))) DAY
    ),
    DAY
) / 7 +1
処理の内容
  1. YEAR関数で先ほど求めた「週の終了日」が属する年を抽出する
  2. DATE関数で上記の年の1月1日の日付を生成する
  3. DATETIME_ADD関数で上記の1月1日の日付に、1月1日が属する週の土曜日までの日数を加算することで、年の最初の土曜日の日付を求める
  4. DATETIME_DIFF関数で「週の終了日」（＝土曜日）と上記の「年の最初の土曜日の日付」の日数差を求める
  5. 上記の日数差を7で割り1を加算することで、「週の終了日」がその年の何回目の土曜日か（＝週番号）を求める

④新しい週単位としてまとめる

最後に①～③の3つの計算フィールドをCONCAT関数で連結します。
データソースに新しい計算フィールドとして以下の「日付（アメリカ式の年と週）」を追加します。

CONCAT(週の開始日,"～",週の終了日,"（",週番号,"週目）")

これで実装完了です！

データソースがGA4の場合

次にデータソースがGA4の場合の実装方法を説明します。

前述しましたが、データソースがGA4の場合は関数に謎の制限（バグ？）があり、それを回避するために一工夫する必要があります。

謎の制限とは何か

データソースがGA4以外の場合では、計算フィールドの中でDATETIME_SUB関数とDATETIME_ADD関数を使いました。
この関数の引数の1つに”INTERVAL ○○ DAY”があり、○○の部分には定数、変数、式を入れていましたが、データソースがGA4の場合はなぜか変数が使えないようです（定数と”1+1″のような定数のみの式は使えます）。
そのため○○の部分が定数となるような実装方法を考える必要があります。

幸い、今回のケースでは○○の部分が取りえる値は0～6の整数で、全部で7パターンしかありません。
よってCASE関数を使って7パターン分の分岐を作成することで、この制限を回避しています。
CASE関数によって1つの式が長くなってしまったので、計算フィールドを小分けにしてわかりやすくしています。

①週の開始日を計算する

データソースに新しい計算フィールドとして以下の「週の開始日」を追加します。

CASE WEEKDAY(日付)
 WHEN 0 THEN 日付
 WHEN 1 THEN DATE(DATETIME_SUB(日付, INTERVAL 1 DAY))
 WHEN 2 THEN DATE(DATETIME_SUB(日付, INTERVAL 2 DAY))
 WHEN 3 THEN DATE(DATETIME_SUB(日付, INTERVAL 3 DAY))
 WHEN 4 THEN DATE(DATETIME_SUB(日付, INTERVAL 4 DAY))
 WHEN 5 THEN DATE(DATETIME_SUB(日付, INTERVAL 5 DAY))
 WHEN 6 THEN DATE(DATETIME_SUB(日付, INTERVAL 6 DAY))
END

②週の終了日を計算する

データソースに新しい計算フィールドとして以下の「週の終了日」を追加します。

DATE(
	DATETIME_ADD(
		週の開始日 , INTERVAL 6 DAY
	)
)

③「週の終了日」が属する年の1月1日の日付を生成する

データソースに新しい計算フィールドとして以下の「週の終了日の元日」を追加します。

DATE(YEAR(週の終了日),1,1)

④「週の終了日」が属する年の、最初の土曜日の日付を計算する

データソースに新しい計算フィールドとして以下の「年の最初の土曜日の日付」を追加します。

CASE (6-WEEKDAY(週の終了日の元日))
 WHEN 0 THEN 週の終了日の元日
 WHEN 1 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 1 DAY))
 WHEN 2 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 2 DAY))
 WHEN 3 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 3 DAY))
 WHEN 4 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 4 DAY))
 WHEN 5 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 5 DAY))
 WHEN 6 THEN DATE(DATETIME_ADD(週の終了日の元日, INTERVAL 6 DAY))
END

⑤週番号を計算する

データソースに新しい計算フィールドとして以下の「週番号」を追加します。

DATETIME_DIFF(週の終了日, 年の最初の土曜日の日付, DAY) / 7 +1

⑥新しい週単位としてまとめる

最後に①、②、⑤の3つの計算フィールドをCONCAT関数で連結します。
データソースに新しい計算フィールドとして以下の「日付（アメリカ式の年と週）」を追加します。

CONCAT(週の開始日,"～",週の終了日,"（",週番号,"週目）")

これで実装完了です！

【注意】週番号を求めるのにEXTRACT関数を使うのは不適切

日曜始まりの週番号を求める際にEXTRACT関数を使って

EXTRACT(WEEK FROM 日付)+1

とするブログ記事などを見かけますが、アメリカ式で週番号を数えたい場合、これは不適切です。
関数のヘルプページを見てもらうとわかりますが、”WEEK”パラメータの定義は以下となっています。

WEEK : 範囲 [0, 53] 内の日付の週番号を返します。週は日曜日から始まり、年の最初の日曜日より前の日付は 0 週目です。

つまり第1週の定義は「年の最初の日曜日を含む週」となります。
この定義で一見問題なさそうに見えますが、実際に上記のEXTRACT関数を使って週番号を求めると以下のような結果となります。

週番号にEXTRACT関数のWEEKを使用した場合の実装結果その1

赤枠の部分を見てもらうとわかりますが、同じ週なのに週番号が”53週目”と”1週目”に分裂しており、誤りです。
正しくは7日間とも”1週目”とならなければなりません。

頭の柔らかい方は「引数として”日付”を使うからダメなんだ。以下のように“週の開始日”または”週の終了日”を使えばいいのでは？」と考えるかもしれません。

EXTRACT(WEEK FROM 週の開始日)+1
EXTRACT(WEEK FROM 週の終了日)+1

実際に試した結果が以下となります。
右側の3列（青枠の部分）が週番号にEXTRACT関数のWEEKを使った列で、引数だけ変えています。

週番号にEXTRACT関数のWEEKを使用した場合の実装結果その2

先ほど問題だった赤枠の部分を見ると、引数に「週の開始日」を使った場合は”1週目”が消えて”53週目”になっており、誤りです。
一方、引数に「週の終了日」を使った場合は”1週目”になっており、良さそうに見えますね。

しかし、ちょっと待ってください！

この2024～2025年の例では年の最初の日曜日より前の日付が存在するので週番号は0から始まり、+1することで正しい週番号に補正しています。
では、年の最初の日曜日より前の日付が存在しない場合、すなわち1月1月が日曜日の場合はどうなるのでしょうか？

答えは以下のようになります。

週番号にEXTRACT関数のWEEKを使用した場合の実装結果その3

直近で1月1日が日曜日だったのは2023年なので2022～2023年で試してみました。
先ほどは問題なかった引数に「週の終了日」を使った場合ですが、赤枠の部分を見ると“2週目”から始まっており、誤りです。

以上から、アメリカ式で週番号を数えたい場合、EXTRACT関数のWEEKを使用するのは不適切であることがお分かりいただけたかと思います。

ちなみに週番号を計算する関数は他にもEXTRACT関数のISOWEEKとWEEK関数がありますが、これらはどちらもヨーロッパ式の週番号を返す関数なので使えません。

まとめ

今回はLooker Studioでアメリカ式の週番号＆日曜始まりの週を実装する方法を紹介しました。

日本だと週番号を使う機会はあまりないので、第1週の定義がどうだろうと気にならないかもしれません。
しかし、安直にEXTRACT関数のWEEKを使用すると上記で紹介したような不具合の原因となるので、本記事で紹介した方法で週番号を計算するか、そもそも週番号を表示しない（週の開始日と終了日のみ表示）ほうが良いかと思います。

弊社では本記事のようなダッシュボードに関する悩みや、マーケティングに関するご相談を承っています。
お困りの方は、ぜひ一度お問い合わせください！

Looker Studioダッシュボードカスタマイズ代行サービス
マーケティングのお悩み相談ならGLASSへ！

最近書いた記事

もっと見る
カテゴリー： ダッシュボード
GLASSで一緒に働いてみませんか？」