ラベル bigquery の投稿を表示しています。 すべての投稿を表示
ラベル bigquery の投稿を表示しています。 すべての投稿を表示

Google Analytics(無料版)データをAPI v4を利用してBigqueryへ入れる

marketechlabさんの無料版データを有料版と同じようにBigqueryへ格納する方法は凄くいいなぁと思いつつ、一長一短あるなぁと思いながら見ていました。
  • bot排除が必要(逆にメリットとしてbotの動きを知れる)
  • bigqueryへの格納方法にもよりますが分析コストが高いflat化したり
  • 既存GUIを見る事で足りるデータはあると思う(例えばブラウザバージョン分布等)
  • Bigqueryで舐めるデータ量が多そうなので工夫が必要かも？
bot以外の項目に関してはGAの有料版と同じだし、メリットも多い...ということでそちらへ切り替えようかどうしようか…と考えつつ、現状は力技で解決してしまっているため、今やっている方法も全くダメなんだよなぁといろいろ悩んでしまいました。

現状のデメリットとしては...
  • 分析したいデータという観点でデータの組み合わせ、抽出スパン等を事前に考える必要がある
  • 場合によってはlogだと1レコードで取得できる部分がテーブル違い等で重複して取得している場合がある
あたりでしょう。

■現状の構成

基本GCPで解決しているのは同じで、基本以下のような流れで進みます。現状だとStorageを挟む必要性もない構成なのですが、なんとなく噛ませてしまっていますね。あと鍵管理でKMSを利用していない部分が駄目。
  1. Cloud Scheduler
  2. Cloud Pub/Sub
  3. Cloud Functions
  4. Cloud Storage
  5. Bigquery
■取得データの取捨選択
キーはclientIDです。
  • clientIDベースの閲覧ページとページごとの滞在時間
  • clientIDベースのイベントデータ
  • clientIDベースの流入経路とランディングページ
他社と同様にcustom TaskでタイムスタンプとclientIDをセットでカスタムディメンションへ格納しています。

function() {
  return function(model) {
    var cid = model.get('clientId');
    var timestamp = new Date().getTime();
    model.set('dimension3',  timestamp + '_' + cid);
  }
}

最終的にBigqueryへ格納する際にこのくっつけている部分は分離しています。
分離はpythonのpandasを利用していてカスタムディメンション1に格納していた場合、そのデータを分割して元データをdropして整形しています。

analytics = initialize_analyticsreporting()
response = get_report(analytics, query)
df = get_dataframe(response)
ga_df = pd.concat([df['dimension1'].str.split('_', expand=True),df], axis = 1).drop('dimension1', axis = 1)

あとはデータの組み合わせの通りにGoogle Analytics V4経由でデータを抽出していきます。


#パターン1
'metrics': [{'expression': 'ga:sessions'},{'expression': 'ga:uniquePageviews'},{'expression': 'ga:pageviews'},{'expression':'ga:pageLoadTime'}],
'dimensions' : [{'name' : 'ga:dimension1'},{'name' : 'ga:pagePath'}]

#パターン2
'metrics': [{'expression': 'ga:uniqueEvents'}],
'dimensions' : [{'name' : 'ga:dimension1'},{'name' : 'ga:eventCategory'},{'name' : 'ga:eventAction'},{'name' : 'ga:eventLabel'}]

#パターン3
'metrics': [{'expression': 'ga:sessions'}],
'dimensions' : [{'name' : 'ga:dimension1'},{'name' : 'ga:channelGrouping'},{'name' : 'ga:source'},{'name' : 'ga:medium'},{'name' : 'ga:campaign'},{'name': 'ga:landingPagePath'}]

こんな組み合わせでデータを取得していっています。
dimensionは最大7、metricsは最大10まで指定できるので増やしたらBigqueryのカラムを追加するという感じでの拡張と新しいデータ取得を行ってテーブルを追加したりと拡張していく事を行っていけば良いと思っています。
最初は1テーブルでも良いですし後から分析したい項目内容にしたがって柔軟な対応が可能です。

デメリットもありますが、気軽に自分にあった内容で分析環境を整えていければ良いかなと思います。

[BigQuery]Custom Dimensionsに対するQuery

GoogleのhelpでもBigQueryのCookbookとしてCustom Dimensionsに対するQueryの書き方があります。

■ヒットレベル
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

■セッション・Userレベル
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

カスタムディメンションはBigQueryだとヒットレベル・セッション/ユーザレベル・プロダクトレベルの3つあります。

hit : hits.customDimensions
session/user : customDimensions
product : hits.product.customDimensions

上の例だとcustomDimension2には数値が入りMAXを取得する感じですが入ってる文字列を抜き出してjoinに使いたいとなった場合どう取得すれば良いかと少し悩みました。

パット見て最初わからなかったのは `customDimensions.value` の書き方の部分なのですが。

国内・海外含めコミュニティやブログを参照しつつ色々ためしてみて、一部抜粋&改変でちょっとイマイチな部分がありますが、最終的にこんな感じで書きました。

  (
  SELECT
    t2.id AS id,
    t2.query AS query
  FROM (
    SELECT
      cd.value AS id,
      hits.page.searchKeyword AS query
    FROM
      `xxxxxxxxxxxxxxxxxxxxxx.xxxxxxxxxxxga_sessions_2017092*`,
      unNEST(customDimensions) cd,
      unNEST(hits) hits
    WHERE
      cd.index = 1
      AND cd.value IS NOT NULL
      AND hits.page.searchKeyword IS NOT NULL
    GROUP BY
      cd.value,
      hits.page.searchKeyword) t2 ) t1

WITHIN RECORDを使うやり方やFLATTENを使うやり方、UNNESTを使うやり方などもあり、FLATTEN/UNNESTはRECORDタイプをフラット化して取り扱うやり方ですね。
結構customDimensionsをBQから抜いてる人は多そうですがネット上にはあまり情報が無いなという印象。普段からQueryを書き慣れている人からするとRECORDタイプとかもそんなに苦もなく処理できるんですかね。