13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

エキサイトAdvent Calendar 2019

Day 25

Google AnalyticsのデータをAWSのs3に保存しQuickSightに可視化

Last updated at Posted at 2020-07-21

概要

Google AnalyticsのデータをS3に保存して、athenaに取り込んみQuickSightで可視化してみました

構成

無題の図形描画 (2).png

Lambda

LambdaでBigQueryからデータの取得しS3にアップロードする。
実際にはBigQueryから直接ダウンロードできないので、一旦Google StorageにBigQueryのダンプファイルを落とす感じになります

BigQueryのデータをダンプしてストレージ保存する

client = bigquery.Client(
    credentials=credentials,
    project=credentials.project_id,
)
project = credentials.project_id
dataset_ref = client.dataset(dataset_id, project=project)
table_ref = dataset_ref.table(table_id)

ext_job_config = bigquery.job.ExtractJobConfig()
ext_job_config.destination_format = bigquery.job.DestinationFormat.NEWLINE_DELIMITED_JSON
ext_job_config.compression = bigquery.Compression.GZIP

extract_job = client.extract_table(
    table_ref,
    destination_uri.format(gcp_bucket_name, target_day, file_name),
    location='US',
    job_config=ext_job_config
)
extract_job.result()

Glue

クローラーを設定する

設定内容はほぼデフォルトのですが「クローラの出力を設定する」の項目以下を設定

  • 「設定オプション」で「新規列のみ追加します。」を選択
  • 「全ての新規および既存のパーティション〜」にチェックをつける

この設定をしないとAthenaでSQLを実行した際に下記の様なエラーメッセージができる
Your query has the following error(s):
HIVE_PARTITION_SCHEMA_MISMATCH: There is a mismatch between the table and partition schemas.

スクリーンショット 2019-11-14 22.42.48.png

Athena

Glueでクロールが完了するとAthena側でSQLを実行してみる、以下の様に結果が表示されればOK
スクリーンショット 2019-11-14 22.51.21.png
ちょっと一手間、Athenaに取り込んだデータのカラムが複合型なので、Viewを作りQuickSightで取り出しやすい形にしておきます

Athena sql
CREATE OR REPLACE VIEW ga_pageviews AS 
SELECT
  "date"
, "count"("totals"."pageviews") "pageviews"
FROM
  woman.type_ga_sessions CROSS JOIN UNNEST("hits") t (hit)
WHERE ("hit"."type" = 'PAGE')
GROUP BY "date"

QuickSight

可視化のためQuickSightを使って可視化を行います
スクリーンショット 2019-11-15 19.05.04.png

注意点

analyticsは360が対象です、360出ない場合はAPIで取得してS3にアップします(制限あり)
Athenaに取り込んだデータの型がstringなので、Glueのジョブで前処理をするか、QuickSigthの方でcastする必要があります

まとめ

開発工数は1〜2人日、すごく手軽にできます。
費用面もbigqueryよりathenaの方が安いように感じます
サービス内で持っているコンテンツの情報も取り込んで、analyticsの情報とかけ合わせると面白い分析が出来ると思いました。

13
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
13
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?