Re:dashでmysqlとQueryResultのデータソースからそれぞれ週毎にデータを集計しようとすると週がズレる。。。その対応
結論ですがmysqlとSQLiteの週単位に変換するための関数のズレが原因でした。
週毎のCACをRe:dashで見れるようにしようとした際にハマりました。
CACはCost Acquisition Customerの略で「1顧客の獲得にいくらかかったか」を表す数字です。
この数字を週毎にチャートで見たいなと。
週毎に獲得した新規顧客数はアプリケーションのデータベースから、
週毎に使った広告費はadwordsのレポートデータからそれぞれ取得します。
アプリケーションのデータベースはmysqlを使っています。
adwordsのレポートデータは一度spread sheetに吐き出させてからRe:dashに取り込むようにしています。
adwordsの日毎のコストレポートデータをRe:dashに自動で取り込む方法はこちらの記事にまとめているのでご興味があればぜひ見てみてください。
keyama.hatenablog.com
spread sheetから取り込んだデータを操作しようとするとデータソースはQueryResultになるので週毎のCACのチャートを作成するにはmysqlとQueryResultでそれぞれ週毎にデータを集計した結果をまとめれば良さそうです。
QueryResultはSQLiteで実行されるので、（redashのQueryResultはSQLiteで実行される - FFFTを参照）
週毎のCACのチャートを実現するにはmysqlとSQLiteでそれぞれ週毎に集計した結果が必要そうです。
というわけでやってみました。
mysql、SQLiteはそれぞれ下記のように日付の対象カラムを週単位に変換してgroup byでまとめて集計しました。
date_format('__TARGET_DATE__' ,'%Y年の%U週目');
SQLiteは下記のように。
strftime ('%Y年の%W週目','__TARGET_DATE__');
これで後はこの集計データをまとめてCACのチャートを作ればオッケー。と思ったんですがなにやら直近の週だけコストが馬鹿高く出てる。。。
確実にズレてる。。。
というわけで調査。
select date_format('2018-01-01' ,'%Y年の%U週目');
select strftime('%Y年の%W週目','2018-01-01');
うん、ズレてる笑
SQLiteの公式には週単位のフォーマットに関して、すごいライトな記載しか載ってない。。。
SQLite Query Language: Date And Time Functions
%W week of year: 00-53
これじゃわからん笑
いろいろネットを漁って仕様を調査したところ、
「対象の日付の年の週番号を00から53の10進数で表し、最初の月曜日を01週とする」仕様だそう。
対してmysqlの公式の週単位のフォーマットに関する記載はこちら。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数
%U 週 (00..53)、日曜日が週の初日、WEEK() モード 0
%u 週 (00..53)、月曜日が週の初日、WEEK() モード 1
※%V, %v もありますが今回は割愛。
どうやらSQLiteの月曜始めに合わせなくてはズレるようです。
mysqlでの%uでのフォーマットを入れて、2018年の年始から数日先までの結果を表にしてみました。
| 日付 | mysql(%Y年の%U週目) | mysql(%Y年の%u週目) | SQLite(%Y年の%W週目) |
|---|---|---|---|
| 2018/01/01(月) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/02(火) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/03(水) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/04(木) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/05(金) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/06(月) | 2018年の00週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/07(火) | 2018年の01週目 | 2018年の01週目 | 2018年の01週目 |
| 2018/01/08(水) | 2018年の01週目 | 2018年の02週目 | 2018年の02週目 |
| 2018/01/09(木) | 2018年の01週目 | 2018年の02週目 | 2018年の02週目 |
mysql側を月曜始めに合わせることでズレが解消されることがわかりました。
でも、正直日曜始めで集計したい。。。
どなたかやり方ご存知だったり思いついたら教えてほしいところです。
