GoogleAnalyticsで出る数値をBigQueryでやってみる。

GoogleAnalyticsのデータって？

GoogleAnalyticsの生データはPremium（以下GAP）サービスに申し込むことで、BigQuery（以下BQ）にJSONデータでエクスポートすることができます。去年も書いたのですが、このデータって非常によくできている。やり方によってはローカルのデータと組み合わせて、かなり精度の高い分析が可能です。
でも、非常によく出来ている反面、JSONが使いづらいという声をよく聞くのも事実だったりします。このJSONフォーマットのデータがGAPデータをBQで活用させる壁になっていると言っても過言じゃないかなと思います。
（※GAP使ってるのにBQ使ってるのって１割ぐらいだと思います）
ということで、実際に使っているクエリなども書いて行きたいと思います。

流入元を見たい。

GAPのデータではtrafficSource.mediumというところに流入元の種類が入ってきます。
例えばディスプレイ広告なら『display』、アドワーズやリスティング広告の場合は『cpc』、twitterやfacebookなどは『social』と入っています。そして、そこで絞ってからtrafficSource.sourceを見るとその流入元がわかります。例のSQLの場合、yahooとかgoogleとかがわかります。（他にもbingとかも入ってます。）
このようにGAが自動的に判断して入れる場合もありますが、スマホアプリやlineなどリファラが取れないものについてはリンクのURLにクエリストリングスのutm_medium=*****で自分の好きな値で設定することもできます。（※詳細は こちら）
例えば流入元の種類をディスプレイ広告として、流入元別のPVUUを過去8日間履歴で取るにはこんなSQLとなります。

display.sql

SELECT
  date,
  SUM(CASE WHEN display_source CONTAINS 'yahoo' THEN pv ELSE 0 END) yahoo_disp_pv,
  SUM(CASE WHEN display_source CONTAINS 'yahoo' THEN uu ELSE 0 END) yahoo_disp_uu,
  SUM(CASE WHEN display_source CONTAINS 'google' THEN pv ELSE 0 END) google_disp_pv,
  SUM(CASE WHEN display_source CONTAINS 'google' THEN uu ELSE 0 END) google_disp_uu,
  sum(case when display_source not like '%yahoo%' and display_source not like '%google%' then pv else 0 end) other_disp_pv,
  sum(case when display_source not like '%yahoo%' and display_source not like '%google%' then uu else 0 end) other_disp_uu
FROM (
  SELECT
    date,
    trafficSource.source display_source,
    SUM(totals.pageviews) pv,
    INTEGER(EXACT_COUNT_DISTINCT(fullVisitorId)) uu
  FROM (TABLE_DATE_RANGE( [*******.ga_sessions_], TIMESTAMP(DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour'),-8,'day'))), TIMESTAMP(DATE(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour')))) )
  WHERE
    trafficSource.medium = 'display'
  GROUP BY
    date,
    display_source)
GROUP BY
  date
ORDER BY
  date;

では次に流入元の種類をsocialとして、その流入元別にPVUUも見てみましょう。trafficSource.mediumにsocialと入れ替えるだけです。

sns.sql

SELECT
  date,
  SUM(CASE WHEN sns_source CONTAINS 'facebook' THEN pv ELSE 0 END) facebook_sns_pv,
  SUM(CASE WHEN sns_source CONTAINS 'facebook' THEN uu ELSE 0 END) facebook_sns_uu,
  SUM(CASE WHEN sns_source CONTAINS 'twitter' THEN pv ELSE 0 END) twitter_sns_pv,
  SUM(CASE WHEN sns_source CONTAINS 'twitter' THEN uu ELSE 0 END) twitter_sns_uu,
  sum(case when sns_source not like '%facebook%' and sns_source not like '%twitter%' then pv else 0 end) other_sns_pv,
  sum(case when sns_source not like '%facebook%' and sns_source not like '%twitter%' then uu else 0 end) other_sns_uu
FROM (
  SELECT
    date,
    trafficSource.source sns_source,
    SUM(totals.pageviews) pv,
    INTEGER(EXACT_COUNT_DISTINCT(fullVisitorId)) uu
  FROM (TABLE_DATE_RANGE( [*******.ga_sessions_], TIMESTAMP(DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour'),-8,'day'))), TIMESTAMP(DATE(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour')))) )
  WHERE
    trafficSource.medium contains 'social'
  GROUP BY
    date,
    sns_source)
GROUP BY
  date
ORDER BY
  date desc;

さて、これで個々のSQLができました。trafficSource.mediumの値を変えることで流入元を色んな角度で見ることが出来ます。
この2つのSQLの数値を帳票などで一回で見たいとなれば、1つのSQLで書くことも出来ます。
でも、これは好みの問題かもしれませんが、私の場合は1つのSQLでは書きません。SQLが長くなるとメンテナンスが面倒なので。
じゃ、どうするか？このSQLを個々のviewにしちゃって、この場合だとdateでjoinします。
例えばdisplay.sql→display_view、sns.sql→sns_viewとします。

join.sql

select
 a.date,
 a.facebook_sns_pv,
 a.facebook_sns_uu,
 a.twitter_sns_pv,
 a.twitter_sns_uu,
 a.other_sns_pv,
 a.other_sns_uu,
 b.yahoo_disp_pv,
 b.yahoo_disp_uu,
 b.google_disp_pv,
 b.google_disp_uu,
 b.other_disp_pv,
 b.other_disp_uu
from [*******.sns_view] a
join [*******.display_view] b
on a.date = b.date

ここで一つ注目して欲しいのが、今回の場合に限ってだけかもしれませんが見てるカラムが両方とも同じということです。
これはどういうことを意味しているかというと、個々でSQLを実行するとそれぞれに課金されますが、今回のこの場合だと1回分の課金になるということです。もちろん1つのSQLで書けば結果は同じなのですが、viewで分けたからと言ってそれをjoinしてしまえば同じカラムを見ている場合は費用は同じとなります。
ですから、trafficSource.mediumを色んな角度で取るために10個ぐらいのviewにしたところで、最後にjoinしちゃえば課金は1回のSQLと同じになります。これって何気にすごいなぁと思うんです。

dropページを見てみる。

サイトではユーザーがどこで離脱したのかが非常に気になるわけです。どこのページが一番離脱されてるの？それはなぜ？使い勝手が悪いのか？それとも、目的が達成できなかったのか？
それを調査する優先順位をつけるために、どのページで一番多く人が離脱しているのかを調査してみます。
ここではmax(hits.hitnumber)でユーザー毎に最後のページ番号を取ってきて、そのページ番号のhits.page.pagePathでURI部分を取ってきて、ユーザーの数を取ってきています。
（※でもこれはGAの画面で見ることが出来ますが、このSQLを少し改造することで調査がしやすくなると思いますよ。）

drop.sql

SELECT
  a.date,
  a.hits.page.pagePath,
  INTEGER(EXACT_COUNT_DISTINCT(a.fullVisitorId)) cnt
FROM
  FLATTEN((
    SELECT
      fullVisitorId,
      hits.page.pagePath,
      hits.hitnumber,
      date
    FROM (TABLE_DATE_RANGE( [*******.ga_sessions_], TIMESTAMP(DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour'),-8,'day'))), TIMESTAMP(DATE(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour')))) )),hits.page.pagePath) a
JOIN EACH (
  SELECT
    date,
    fullVisitorId,
    MAX(hits.hitNumber) last_page_num
  FROM (TABLE_DATE_RANGE( [*******.ga_sessions_], TIMESTAMP(DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour'),-8,'day'))), TIMESTAMP(DATE(DATE_ADD(CURRENT_TIMESTAMP(),+9,'hour')))) )
  GROUP BY
    date,
    fullVisitorId) b
ON
  a.date = b.date
  AND a.fullVisitorId = b.fullVisitorId
  AND a.hits.hitnumber = b.last_page_num
GROUP BY
  a.date,
  a.hits.page.pagePath
ORDER BY
  a.date DESC,
  cnt DESC

これでdropしたページがわかります。
もちろん、これだけではただページがわかるだけで『なんでdropしたの？』まではわかりません。
でも、例えばdropした前のページは何だったのか？はlast_page_num - 1などで見ることが出来ます。直帰しているページもlast_page_num = 1だけで絞ればいいとなります。

ということで。

簡単なSQLだけを掲載してみました。が、実際に使っているのはこんなものです。
コツは一つのSQLで最初からなんとかしようとするのではなく、こういう簡単なSQLを作って肉付けしていくのが手っ取り早いです。
table_date_rangeも二日か三日分ぐらいしか指定しなければそんな膨大なデータ量でもないですしね。
で、SQLは極力長くしない方が良いと思っています。出来たらさっさとviewに保存する。で、最後にjoinするって感じです。
ローカルデータとjoinするときも同じかと。最初にGAPのデータをBQであらかたviewで作ってから、ローカルのデータとぶつけるという感じです。すいません、このあたりは大人の事情で詳しく書けないのですがｗ

こんなのGAの画面で見れるじゃん！と思われる方もいらっしゃるかと。でも、この中の値やカラムを変えてトライ＆エラーでやってみてください。
きっと痒いところにだけ手が届くようなデータが出てくると思いますよ。

急いで書いたので間違えているところとか、もっとこうやれば良いという意見を頂ければうれしいです。