ietty TECH blog

Google Data Studioとre:dashでつくるダッシュボード講座

f:id:chanshimo:20161202183733j:plain iettyでマーケティングとサービス企画を担当している下地と申します。

今回は、Google Data Studio、re:dash、googleスプレッドシートを使ったダッシュボードの構築方法について説明します。

ざっくり言うと

  • Google Data Studioでダッシュボードを作った

  • Google Apps Scriptでre:dashのデータをGoogleスプレッドシートへインポート

  • Googleスプレッドシートで異なるデータをJOINする

googleスプレッドシートでの分析はもう限界ｨｨｨ!!

今までは、マーケティング領域のデータの可視化はgoogleスプレッドシートを使っていました。

DB内のユーザー情報をインポートして広告費を集計して、sumifsで紐付けしてCPAや継続率を見る…といったことをしていたのですが、いかんせんスプレッドシートは関数が増えていくと表示/更新がどんどん重くなっていくので、そろそろ限界を感じていました（arrayformulaはsumifsやcountifsなど配列を含む関数には使えないし、各セルにsumifsを書いていくと、メンテが大変）。

そんな中、Google Data Studioの存在を知り「これは！」と思い使ってみることに。

Google Data Studioとは

googleが出している無料BIツール（もともとは『Analytics 360 Suite』シリーズに搭載されているものの無償版らしい）。

詳しくはこの記事をご参考に。 bita.jp

上記の記事を参考にしつつ、実際に作ったダッシュボードがこんな感じです。 f:id:chanshimo:20161202174554p:plain

マーケティング用のダッシュボードとは別に、細かいKPIを追うためのボードや、AdWordsの効果計測をするためのボードなども複数運用しています。

Google Data Studioへのデータの受け渡し方法(ver.ietty)

f:id:chanshimo:20161202175225j:plain

  • re:dashでsqlを書き、元データを作る

  • Google Apps Scriptでre:dash APIを叩き、データをスプレッドシート上に書き込む

  • スプレッドシートをデータソースとしてdata studioで読み込む

Google Data Studioのデータソースにはmysqlもありますが、iettyのmysqlはAWS上にRDSとして置いてあるので、今回は直接のデータ転送はセキュリティ的に断念しました。 Goole Data StudioでSSL通信オプションが実装されれば、直接接続も期待したいところです。

続いて、今回実施した方法で、個人的に面倒だった箇所のやり方を書いておきます。

Google Apps Scriptでre:dash APIを叩いてデータをスプレッドシート上に書き込む

今回、スプレッドシートをデータソースにするにあたり、sqlの結果をスプレッドシートに定期的に自動更新させるように、以下のGoogle Apps Scriptを使います。

Google Apps Script

function run(){
  //列を増やす
  range = addFields();
  //データを読み取って書き込む
 def(range);
}

function addFields(){
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("[シート名]");
 var clear = sheet.clear();
 var range = sheet.getRange("[開始セル]");
 return range
}

function def(range){
  var url; var lines;
  url = "https://redash.co.jp/api/queries/[クエリNo.]/results.json?api_key=[このクエリのapi key]";
  lines = [ここにre:dashで出力したカラム名を記載する];
  range = importRedash(range, url, lines);
}

function importRedash(range, url, lines){
  var response = UrlFetchApp.fetch(url);   
  var ret=JSON.parse(response.getContentText());
  for ( var i in ret['query_result']['data']['rows']){ 
    for(var r in lines) {
        if(i == 0){
        range.setValue(lines[r]);
        } else{
      var val = ret['query_result']['data']['rows'][i][lines[r]];
      range.setValue(val);
      }
      range = range.offset(0, 1);
    }
    range = range.offset(0, -lines.length);
    range = range.offset(1, 0);
  }
  return range
}

このスクリプトを毎日0時に実行するようにタイマーを設定します。

f:id:chanshimo:20161202143041p:plain 時計マークをクリックして

f:id:chanshimo:20161202142407p:plain 実行する関数を選択してタイミングを設定

re:dash→スプレッドシートへの自動更新は今回以外にも色々使えるので重宝します。

Googleスプレッドシート上で複数データをJOINする

Google Data Studioでは、複数データソースをJOINすることができないので、スプレッドシート上で行います。

スプレッドシートには、sqlライクに集計ができるquery関数というものが存在しますが、この関数にもJOIN句は無い…のでarrayformulaとvlookupを組み合わせてそれっぽいことをします。 （query関数はquery関数で便利。個人的にはカラムを指定するだけで使えるpivot句が好き。）

今回は、キャンペーンごとのユーザー獲得数と広告費を紐付けてみます。

データの準備

まずは、紐付けるデータ同士を準備します。

f:id:chanshimo:20161202155100p:plain reg（キャンペーンごとの日別ユーザー獲得数）

f:id:chanshimo:20161202155122p:plain cost（キャンペーンごとの日別コスト）

※広告キャンペーンごとにデイリーで集計したデータを合体させているので2次元配列になってます。ほんとはこっちも縦持ちにしたい…。

regのF2セルに、以下の関数を入力します。

 arrayformula(
  iferror(
   vlookup(A2:A,cost!A:D,
      match(B2:B,cost!1:1,0)
   ,false)
  ,0)
 )
,counta(A2:A),1)

するとこう。 f:id:chanshimo:20161202155603p:plain いい感じですね。

vlookup(match())で値を取得して、arrayformulaで配列数式にします。これでわざわざ全セルに数式をコピペしなくても自動的に展開されます。

arrayformulaはシートの一番下まで展開されてしまうので、データ長を揃えたい場合は、array_constrainで囲んであげるといい感じです。

・

・

・

元データが用意できたら、google data studioで読み込んで、ちょちょっと整形してデザイン整えて……はい完成！ （data studioの使い方は説明するほどでもないくらい簡単なので省略）

f:id:chanshimo:20161202174554p:plain

スタートアップにはgoogle data studioオススメ

実際に運用してみて感じた良い点と、改善してほしい点をまとめてみました。

良い点

  • 無料（今のところ）

  • リアルタイム共有＆更新が簡単

  • デザインのカスタマイズ性が高い

  • 期間指定やフィルタ機能が良い（re:dashでもダッシュボード機能はあるけど、あちらはデザインやレイアウトがほぼ固定なのと、期間指定がそれぞれのquery依存になってしまうのが難点）

改善してほしい点

  • データソース間のJOINは出来ない

  • グラフの表示形式は最低限（コホートとかも欲しい）

・

・

Tableauに比べると全然劣る、という声も聞きますが、あっちは高機能な分そこそこ値段が張るし、iettyにはオーバースペックで必要ない機能も割と多いので、コスパは良くないなあ、と思います。

それに比べると、無料でそれなりのダッシュボードが作れるGoogle Data Studioは、小さいスタートアップからすると魅力的だし、まだベータ版なのでこれから出来ることはどんどん増えていくはずなので、オススメです。

今後の課題としては、広告費の集計はまだ手入力が大半なので、少しずつ自動化していきたいですね…。

こんな感じで、iettyでは分析に必要な数字はエンジニアに極力頼らず、マーケター・ディレクターが自分で出すようにしています。分析チームという名前で毎日言われるままsql書いたり、管理画面をメンテすることに疲弊しているエンジニアは、iettyに来ると幸せになれるかもしれませんよ……。

www.wantedly.com

www.wantedly.com

www.wantedly.com

www.wantedly.com