MySQL/SQLiteで日別・週別・月別で集計するクエリまとめ

Re:dashでメトリクスを作成する際に日別、週別、月別にデータの推移を見たく、いろいろ触ってみたので残しておく。

データ分析対象のアプリケーションのDBはMySQLを使用している。
また、adwordsで使われている集客コストをspread sheetに吐き出して、Re:dashでそれをインポートして利用しているのでQueryResultを操作するクエリも実行する必要があったので、今回はMySQLとSQLiteの日別・週別・月別のデータを集計するクエリをまとめる。(Re:dashのQueryResultはSQLiteで実行される)

なお、Re:dashに取り込める形式でadwordsのレポートデータを定期的にspread sheetに吐き出す方法は下記の記事にまとめています。
keyama.hatenablog.com

新規ユーザーの推移を見ることを仮定して下記のusersテーブルを集計するクエリをまとめる。

id	created_at
00001	2018-01-20 00:00:00
00002	2018-01-20 13:30:00
00003	2018-01-20 14:00:00
00004	2018-01-21 00:00:00
00005	2018-01-22 00:00:00
00006	2018-01-23 00:00:00
00007	2018-01-24 00:00:00
00008	2018-01-25 00:00:00
00009	2018-01-26 00:00:00
00010	2018-01-27 00:00:00
00011	2018-01-27 10:00:00
00012	2018-01-28 00:00:00
00013	2018-01-29 00:00:00
00014	2018-01-30 00:00:00
00015	2018-01-31 00:00:00
00016	2018-02-01 00:00:00
00017	2018-02-02 00:00:00
00018	2018-02-03 00:00:00
00019	2018-02-04 00:00:00

■日別

MySQL

select count(id) as new_user_count, date_format(created_at ,'%Y-%m-%d') as day from users group by day;

SQLite(QueryResult)

select count(id) as new_user_count, strftime('%Y-%m-%d', created_at) as day from users group by day;

■週別

MySQL

select count(id) as new_user_count, date_format(created_at ,'%Y-%u weeks') as week from users group by week;

SQLite(QueryResult)

select count(id) as new_user_count, strftime('%Y-%W weeks', created_at) as week from users group by week;

対象の日付の年の何番目の週か、で集計する。
なお、最初の月曜日を1週目とするように集計している。
本来であれば日曜スタートで集計したいところだがSQLiteの週単位のフォーマットは月曜スタートしかサポートしておらず、MySQLもそれに合わせるカタチとしている。
なお、MySQLで日曜スタートで集計したい場合は下記のようにおこなう。

MySQL

select count(id) as new_user_count, date_format(created_at ,'%Y-%U weeks') as week from users group by week;

■月別

MySQL

select count(id) as new_user_count, date_format(created_at ,'%Y-%m') as month from users group by month;

SQLite(QueryResult)

select count(id) as new_user_count, strftime('%Y-%m', created_at) as month from users group by month;

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側を月曜始めに合わせることでズレが解消されることがわかりました。

でも、正直日曜始めで集計したい。。。
どなたかやり方ご存知だったり思いついたら教えてほしいところです。

cloud functions × puppeteer × Google Apps Scriptで超低コスト定期実行クローラを作って金曜ロードショーを毎週slackに通知させる

cloud functions × puppeteer × Google Apps Scriptで定期実行されるクローラを作ってみます。
今回は毎週月曜に今週の金曜ロードショーをslackで通知させるようにしてみます。

cloud functionsはGCPで利用できるAWSのlumbdaのようなもので、サーバーやランタイム環境を管理することなく、簡単に単一目的のスタンドアロン関数を作成することができます。
実務でも使っていますが、サーバレスでのAPI提供がcloud functionsならすぐに、且つ、簡単にできるのでオススメです。

そのcloud functionsですが8月にpuppeteerのサポートが行われました。

cloud.google.com

puppeteerはheadless chromeを操作するchrome公式のnodeライブラリです。

github.com

クローリングで必要な様々な処理を提供していてシンプルな記述でheadless chromeの操作ができます。

それでは早速やっていきます。

まずはGCPにログインしてメニューからCloud Functionsを選択し、新規に作成画面に進みます。

下記のように情報を入力しましょう。

名前はなんでもいいですがここでは friday-roadshow としました。
割り当てられるメモリはデフォルトだと256MBになってますが小さ過ぎてクローラが動かないので512MBにしてください。
ランタイムはNode.jsの8系を選択。

ソースは下記。

まずはpackage.jsonにpuppeteerを追加します。

次にindex.jsです。

リクエストはGETのみ受け付けるようにしています。
誰でも叩けないようにinternal keyを設定していてヘッダに同じkeyが含まれていないと403を返却するようにしています。
本当は環境変数から引っ張ってくるなり、datastoreから引っ張ってくるなりして直接定義しないほうがいいです。ご認識を。
実際のクローリング箇所は自分でいじってみてもらったほうがわかると思うのでいじってもらえればと。
基本はブラウザを起動して該当ページにアクセスし、セレクタを指定してそのセレクタの中身を取得してレスポンスとして返却してあげる、という流れです。
ソースコードはたったこれだけでOKです。

残りの設定に進みます。

実行する関数は exec で。

次にその他をクリックして詳細オプションを下記のように変更します。

リージョンをasiaにしてタイムアウトを上限値まで上げておきましょう。
実際は今回のようなシンプルなクローリングであれば数秒で処理は完了します。

これでAPIの準備はOK。
保存しましょう。
保存からAPIを叩けるようになるまで数分かかるのでしばらく待ちます。

APIが叩けるようになったら実際に叩いてみましょう。

トリガーからURLを確認して叩きます。

こんな感じで取得できました。

次にslackのbotの設定をします。
今回はincoming webhookを使います。

下記を参考に設定します。
keyama.hatenablog.com

最後にGoogle Apps Scriptの設定です。

Google Apps Scriptが先ほど作った金曜ロードショーのデータを取得するAPIをコールし、返却された結果を使ってbotのapiを叩きます。

google driveを開いて、右クリックからその他を選択し、Google Apps Scriptを選択。選択肢になければ「アプリを追加」から追加してください。

ソースは下記です。

実行しようとすると外部サービスへの接続を作成したアプリケーションに許可するかどうかのポップアップが表示されるので許可します。

下記のように通知がきたらOKです。

最後に毎週月曜にこのスクリプトが実行されるように設定しましょう。

編集から現在のプロジェクトのトリガーを選択。

新規にトリガーを作成しましょう。

下記のように設定します。

これで毎週月曜に金曜ロードショーが通知されるようになりました。

このようにcloud functions × puppeteer × Google Apps Scriptを組み合わせると非常に簡単に定期実行するクローラを作成できます。
今回のようにslackや、他にはlineのapiと組み合わせたりしてもいろいろ便利なものができそうです。
また、cloud functionsでクローリングするAPIを作成することでクローリングのために常時起動させるサーバを用意しておく必要がなく、コストを大幅に削減できます。

ぜひ使ってみてください。

adwordsのレポートデータをspread sheetに定期的に吐き出してredashが取り込めるようにする

まずはspread sheetにgoogle adwordsの日毎のコストデータを吐き出させます。

spread sheetのGoogle Adsというアドオンを使うのがシンプルで楽そうだなーと思い、試してみました。
実際にシンプルにadwordsのレポートデータを引っ張ってこれるんですが都度手動で取り込まなくちゃいけない、且つ、日次データは取得できる期間が限られているので最終的にredashが取り込むとすると部分的なデータが取れてもなー、というのを思い、アプローチを変えてadwords scriptを使いました。

adwordsにログインし、ヘッダのツールの一括操作 > スクリプトからadwords scriptを作ります。
サンプルは下記です。
吐き出し先のspread sheetのURLを付け替えてもらえればそのまま動作します。

ソースコード内のNOTEにも記載しましたが2点注意が必要です。

adwordsのレポートはAWQLという一般的なSQLとはビミョーに異なる文法で書かれます。
レポートの対象期間の指定にDURINGを使うのですがこれがadwords側で事前に今日の分や過去1週間分などのコード値が定義されており、それを使って期間を指定します。
すべての期間ももちろんあってALL_TIMEというコードをDURINGに指定してあげればいいんですが、、、レポートではALL_TIMEは使えないとのこと。

AdWords クエリ言語（AWQL）  |  AdWords API  |  Google Developers

ALL_TIMEだめだと手運用残すか、ちょっとめんどくさいスクリプト組まなきゃならないなーといろいろ試行錯誤していたら自分で日付の範囲指定ができたのでadwordsを使い始めた日から、今日までの指定を動的にされるように作ってみました。
このやり方であればALL_TIMEと同じことが実現できました。
これを許容するならALL_TIME指定させてくれー、と思わずにはいられなかったですが。。。

もう1点はredashが取り込む際にそのままのCostの形式だと正しくimportされず、数字がずれてしまうので調整してあげる必要があります。
これでspread sheetへの吐き出しはOK。

最後に定期実行されるようにすれば手運用は一切する必要がなくなります。
定期実行の設定ですがスクリプトの作成画面からは設定ができません。わかりづらい。
スクリプトの一覧画面の頻度の列の対象セルをクリックすると設定できます。
自分は日別の0:00を設定しました。

次はredashにデータソースとしてspread sheetのデータを読み込ませるやり方ですが、公式にシンプルにまとめられています。

Querying a Google Spreadsheet | Redash

GCPを使ってService Account(マシンユーザー的な概念です)を作成し、そのService Accountのクレデンシャル情報のjsonデータをredash側でぽちぽち設定し、最後にredashから読み込ませたいspread sheetの共有設定に作成したService Accountのメールアドレスを登録すればOK。

こちらの記事が手順を丁寧にまとめてくれています。
Re:dashでGoogle SpreadSheetを読み込む - Qiita

最後にspread sheetに吐き出されるCost列の表示形式を設定しておきます。
これを行わないとredash側でインポート時に数字がズレます。
Cost列を選択して表示形式から数字 -> 書式なしテキストを選択します。

これでadwordsのコストを使ったメトリクス分析がredashで行えるようになりました。
redashで設定したspread sheetのqueryを更新すると正しくデータが読み込まれることが確認できるかと思います。

redashのQueryResultはSQLiteで実行される

QueryResultを使った週単位のデータ集計をしようとしたときの話。
分析対象のアプリのDBはmysqlを使っているのでその流れでdate_formatを使って書いたらエラーになった。

mysqlの関数使えないのかー。どうやるんだー。となって公式から漁る。
が、見つからず。。。
ネットにも記事が引っかからずどうしたもんかなーと思って公式を見返したら書いてました。

Querying Existing Query Results | Redash

The processing of the data is being done by SQLite in memory - in case of large result sets it might fail due to memory running out.

redashで使ってるメモリ上にあるSQLiteが実行すると。
ついでにデータが大きすぎるとメモリが足りずに落ちるかもとのこと。

ということでstrftimeを使って解決。

IE11で画面を行き来するアプリケーションでAPIが呼ばれず画面の情報が古いまま表示される

タイトルのとおりの事象が発生。

IE11でreact, redux-sagaの構成のフロントアプリケーションで外部アプリケーションからのwindow.location.hrefでの遷移時になぜか最新の情報が画面にレンダリングされない、、、というところから調査スタート。
safari, chrome, firefox, edgeなど他のブラウザでは正常に動作する。

該当アプリの該当ページのcomponentWillMountで呼び出しているaction creatorがうまく動作してないのか、などだいぶ遠回りしてしまった。
結果、actionは正常に発行されてるにもかかわらずAPIは呼ばれてないというところからhttpクライアントまわりだなーとなる。
なお、httpクライアントはaxiosを使ってます。

調べていくとInternet Explorerは積極的にajax呼び出しをキャッシュするそうで。

axiosにはインターセプタ機能があるのでそれを使って呼び出しエンドポイントが一意になるように現在日時のパラメータを毎回付与させます。
実装例は下記。

下記の記事が大変参考になりました。
感謝！
s8a.jp

GCPのCloud SQLにlocalのSQLクライアントから接続

GCPのアカウントで認証してproxy経由で接続させます。

gcloudコマンドを使うのでインストールしてない方はこちらを参考に。
GCPのgcloudコマンドを公式に沿ってインストール - FFFT

Cloud SQL Admin APIを利用するプロジェクトで有効にします。
Google Cloud Platform

次にCloud SQL Proxyをダウンロードします。

下記はMac OS 64ビットの場合の例。
該当しない人はこちらから自分に合ったものを確認してください。
Cloud SQL Proxy を使用して MySQL クライアントを接続する  |  Cloud SQL for MySQL  |  Google Cloud

curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.darwin.amd64

プロキシに実行権限を追加します。

chmod +x cloud_sql_proxy

GCPのアカウントでdefault credentialを設定します。

gcloud auth application-default login

認証用のURLが生成されるのでブラウザからアクセスしましょう。
認証するとキーが生成されるのでターミナルに戻ってキーを入力すれば完了です。
余談ですがdefault credentialの設定にGCPのアカウント認証をするのはなんか違和感。通常のgcloudの認証で十分じゃないかなと思ったり。

プロキシを起動させます。

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:[PORT_NUMBER]

INSTANCE_CONNECTION_NAMEはGCPの対象のインスタンスのページの「インスタンス接続名」から確認できます。
PORT_NUMBERはlocalですでにmysqlを動かしていたりdocker経由で3306を利用しているとバッティングしちゃうので当たらないように適当なポートを指定しましょう。

これでおっけー。

接続確認。

mysql -u [USERNAME] -p --host 127.0.0.1 -P [PORT_NOMBER]

パスワードを入力して接続できたら終わり。

ちなみにさっとCloud SQLの中身を見たいときはCloud Shellを使うのが楽です。
GCPの対象のインスタンスのページから「Cloud Shellを使用して接続」をクリックするとコマンドが組まれた状態でシェルが起動します。

自分はSequel Proを使いたかったので紹介した手順で接続させました。