Daringdaddy’s days

登山/トレッキング、ファミリーキャンプ、トレイルランニングをこよなく愛す。ウルトラマラソンやウルトラトレイル(100KM)などに無謀に挑戦(いつも大体ビリ近辺)。Webベンチャー企業で働く。妻&子ども3人とスペインのバルセロナで2年生活。

DBやアナリティクスをGoogle Sheetsと自動連携!Supermetrics for Google Sheets入門

普段、山やトレランや子育ての記事ばっかり書いてますが、日頃はネット企業でマーケティングやら事業開発関連の仕事をしております。(エンジニアではありません)

 

KPIモニタリングや分析のため、数字をデータベースやアナリティクスから日次なり週次なりで取得し、Google Sheetsに取り込むという作業をする事が多いです。

同じような作業をされている方は多いのではないでしょうか。

エンジニアの多い会社であれば、こういうところにも開発工数を割けると思いますが、エンジニア不足の昨今、そういうことはない職場の方が多いと思います。

実現したい:DBやアナリティクスの情報をコピペして取り込むタスクの自動化

自分もしばしばこういったタスクをする事が多いのですが、かったるい事が嫌いでして、従来は人力で

SQLを実行→結果をシートに貼りつけ→さらに加工して貼りつけ

やってたのを、何かしらツールを使って自動化したいと考えました。(自分ができる方法で)

 

具体的にやりたかった事は(表題の通りなのですが)

・データベースに対して毎日指定日次に、特定のSQLを実行した結果をGoogle Sheetsに取り込める

・同じく、Google Analyticsのデータ(Organic Search経由とかセグメントも指定して)を、日次でGoogle Sheetsに取り込む

これらを自動化したいと考えました。

 

ちなみに、Google Sheetsに取り込みたい理由は単純で、Sheetsであればモニタリングだけでなく、気になった部分を切り出して計算とかしたりできるというメリットがあるからです。

 

データ自動取り込みの解決方法として考えたもの

ただ冒頭述べたように自分はエンジニアではないので、簡単なPHPとか既存ソースの修正くらいならできるものの、難しい事は無理っす。

そこで調べていくと以下の様な手立てがある(ありそう)という事はわかりました。

RedashとGoogle App Script(GAS)、無料

知人によるとRedashで取得した値をGASであれこれしてSheetsに転記する方法があるらしい。しかし、自分はGASがわからないし、一から勉強するには学習コストがかかりすぎるので見送り。

Google SheetsのGoogle Analyticsアドオン、無料

GoogleがSheets用にアドオンを提供しています。これで解決したいことのうち、Analyticsのデータ取り込みはこれで解決できます。

Google Analytics - Google Sheets add-on

しかし、データベースから値を取得する方が解決できないので、判断保留。

Tableau、有料

そもそもSheetsを使わないでTableauにする手もあるということですが、おじさんTableauよく分かりません。さらに、お金がかかる事、Google Sheetsに自動的に移すところが全くやり方みえないので見送り。

ZapierやIFTTT、有料

IFTTTに代表されるシステム間同期ツールで、Zapierというものがあります。Zapierを使い、指定したSQLの結果をSheetsに同期してくれるツールがあればいいなと思って探しましたが、みつかりませんでした。

Supermetrics for Google Sheets、有料

最後に見つけたのが、Google Datastudioのデータソースでもよく見かける事があって名前だけは知ってた「Supermetrics for Google Sheets」です。

Google Sheetsのアドオンとして使います。

日本語の情報源がほとんどないのですが、英語で調べてくとまさにそのものずばりな事ができるよう。

選択肢としてこれに絞ることにしました。

 

Supermetrics for Google Sheetsとは

簡単にいうと、Google Spreadsheets のアドオンです。

 

このツールの凄いところは、ごくごく簡単な手順で、データベースとかアナリティクスとかのデータを、Google Sheetsに取り込めるところ。

Google Analytics と連携するアドオンは、グーグル社自らが開発したものがありますが、やはりデータベースなどと組み合わせてデータをみないと話になりません。

しかしマーケターなど、非エンジニアにはここのハードルが高い。

 

ここの隙間を埋めるのがSupermetrics for Google Sheetsです。

期間やレコードのまとめる単位やらSWLやらをポチポチ設定して、スケジュール設定すれば、決めた日時に毎日情報を取得してくれるというありがたすぎるツールです。

 

そして極めつけは月額たったの70ドルですってよ奥さん!

怪しい会社かどうかももちろん気になるところですが、フィンランドのベンチャー企業です。

なんでもグローバルですでに1万社以上のクライアントを抱え、すでに黒転してるなど、近年のフィンランドのベンチャーの中でもピカイチの存在なんだとか。

 

さっそく試してみる事を決意。15日間は無料で、有料版の全機能が使えます。

以下、簡単に使い方を説明します。

 

Supermetrics for Google Sheetsの使い方

Google Sheetsのアドオンをインストールする

以下のリンクから、「Start Free Trial」ボタンをクリックします。

Pricing | Supermetrics Google Sheets add-on

 ↑
赤いでかいボタンをクリック!

 

すると以下のページに遷移します。「Free」ボタンをクリックします。

 

すると、Google Sheetsが自動的に開き、以下のポップアップが表示されます。

SupermetricsにGoogle各サービスへのアクセス権限を許可します。

 

Google Sheetsに戻ると、「アドオン」メニューに「Supermetrics」が表示されるようになります。ここまででアドオンの追加は完了です。

実際にSupermetrics for Google Sheetsを使う

ではさっそくSupermetrics for Google Sheetsを使ってみましょう。

アドオン>Supermetrics>Launchを選ぶと、Supermetricsが立ち上がり、データを取り込み可能なサービスの一覧がずらっと表示されます。

自分の想定用途である、データベースとアナリティクス以外にもめっちゃいっぱいサービスが出てきます。色々な事ができる可能性を感じます。

Supermetricsからデータベースへの接続(初期設定)

データベースへの接続に関しては「Database」を選びましょう。

すると以下の設定画面が表示されます。

DB type:データベースの種類を選びましょう。MySQL、PostgreSQLなど。

DB address:データベースの接続URLを入力します。

DB port:ポート番号を入力します。デフォルト値は3306。

DB name:接続するデータベース名を入力します

Username:データベースの接続用ユーザー名を入れます

Password:パスワードを入れます

Use SSL:SSLを使う場合はチェックします

Request Origin:GoogleかAmazonを選びます。Google Sheetsからアクセスするので、多分Googleしか選ばないと思うんだけどなぁ。。。

そして「IMPORTANT」以下にごちゃごちゃっと注意書きがありますが、ここで書いているのは、DB接続時にGoogle Sheetsからアクセスが行くので、GoogleのIPを外部から接続することを許可する必要があるということです。(ホワイトリスト指定)

64.18.0.0 - 64.18.15.255
64.233.160.0 - 64.233.191.255
66.102.0.0 - 66.102.15.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
74.125.0.0 - 74.125.255.255
173.194.0.0 - 173.194.255.255
207.126.144.0 - 207.126.159.255
209.85.128.0 - 209.85.255.255
216.239.32.0 - 216.239.63.255

 

上記はシステム部門などに依頼する必要があるので、まず最初に対応しておきましょう。

上記のIPが許可され、設定情報を入力したらば、「Test Connection」ボタンを押してみましょう。正しく設定がされていれば接続が成功した、と表示が出ます。

Supermetricsで実際にSQLを実行してデータを取る

上記までできていれば、実際にSupermetricsから情報を取得するのはとても簡単です。

貼り付けを開始したいセルを選択したら、Query SQLの欄に、任意のSQLを貼りつけて実行するだけです。

その際、二つ設定項目があります。

Append new results to old:従来まで存在しているデータに差分を付け加える形で、シートに更新をかけます。(ちゃんと使ったことがないので、この機能は詳細は解説できません)No header row:テーブル側で定義されているカラム名が不要な場合、ここにチェックをいれます。多くの場合、データベースのテーブル側で定義されているカラム名は、人間にとっては分かりづらいので、スプレッドシートでわかりやすい見出し行を用意してあげるほうがいいかと思います。

SQL実行中の注意!

SQLを実行すると、進捗がパーセンテージで出ます。ここで注意が必要なのが、この進捗表示が消えるまで、そのタブ(ウィンドウ)は閉じたり、他のシートをいじらないでください。(ちなみにSQL実行時以外でもそのようです)

複雑な処理が走っているらしく、操作すると、データが途中から全然違うところにコピペされてしまったりしますのでマジ注意!

Google Analyticsからのデータ取得

次にGoogle Analyticsからのデータ取得について説明します。

データソースから「Google Analytics」を選ぶと、アクセスを許可する画面に再びなりますので「許可する」(Allow)をクリックしましょう。

 

Google Analyticsのデータ取得時のSupermetrics設定項目

Google Analyticsの場合、データベースよりも柔軟な設定が可能です。

  • Select Views:アナリティクスのビューを選びましょう
  • Select dates:対象期間を選択可能です。Yesterdayとかtodayのような指定の仕方も可能なので、自動更新用の設定をするときは開始期間は日にち指定、終了をYesterdayなどとするのがよいでしょう。
    また「Compare to」を選ぶと、昨年同期比といった比較用数字も合わせて取得してくれます。さらに便利なことに、Same dates a year ago, matching day of weekを選ぶと1年前の同じ週の同じ曜日と比較してくれます。曜日トレンドを考慮に入れた比較をしたい場合はこれを選ぶと良いです。
  • Select metrics:Session、Userなどの指標を選択できます。
  • Split by:対象となる期間、指標を、どのような期間単位で分割するかを決めます。日次で取得するのであればDateを選びます。月次であればYear&Monthを選びましょう。
    そして一番素敵なのが、データの貼り付け方向を選べるところ!
    組織や管理する情報によって、データが縦方向だったり横方向だったりすると思いますが、縦方向ならSplit to rows、横方向ならSplit to columnsを選べばよいです。すてきやぁー。。。
  • Segment:アナリティクスのセグメントを選べます。素晴らしいのは、自分で作ったカスタムセグメントも選べるところ!かゆいところに手が届くなぁ。
  • Filter:アナリティクスのフィルタを選べるようですが僕は使ったことないですね。。
  • Options:色々項目があるのですが、大事と考えているのは「Try to avoid Google data sampling」。これは普通にデータを取得しようとするとGoogle側である程度丸めた数字を返してくるのを、精緻な数字をとりたいときにチェックをいれます。トレードオフとして処理に時間がかかること、GoogleのAPIへのリクエスト回数が多くなってしまうので日次の上限値に達しやすくなってしまいます。
    「No header row」はデータベースの項で説明したのと同じ使い方ができます。

実際のアナリティクスデータの取得

Get data to tableという緑色のボタンをクリックすると処理が開始します。やはりここでも、進捗が100%になるまで根気よく待ちましょう。特に「avoid Google data sampling」にチェックをいれ、日次でデータを取得する場合とても時間がかかりますので留意してください。

Supermetrics for Google Sheetsめっちゃ使えるよ。

というのが自分の結論です。

複雑なSQLもこなしてくれ、自動的に情報取得をしてくれる。

分析しやすいようにGoogle Sheetsに情報をコピペしてくれる。

マジでありがたい存在です。

 

役に立ちそう!って思ったらまずは、15日のトライアルを試してみては。

そんで気に入ったら、あとからクレカで決済できます。

ちなみに年払いだと月69ドル相当、月払いだと86ドルちょいです。人が毎週とか毎日とか、頑張ってコピペする工数、コストを考えたら安いですよね。

ぼくの場合、それまで人力で週次でおこなってたモニタリングを、自動的に日次ですることに成功しました。

 

さぁ、浮いた時間で山のぼろう!
キャンプ行こう!
川に飛び込もう!

現場からは以上です。

 

PS

Supermetrics for Google Sheetsについて何かこの辺知りたいとか質問とかあればお気軽にコメントくださいな。日本語ソースがほとんどないと思いますので。。。

 

お試しはこちらから!

Supermetrics for Google Sheets