GAデータをAthenaで操作する
会社のDBにメディアのGA(Goole Analytics)ログがあって操作するときに
色々と今までのデータとは毛色が違ったので忘れないように。
※一般的なGAログがどのような形で格納されているか分からないので、あくまで知っている内容で書きます。
struct型でデータが入力されている
まずは、どんな形で入っているか
→Pythonだとdictの様な形で入っている
# TableのDDL(totalsカラム) struct<visits:int,hits:int,pageviews:int,timeonsite:int,bounces:int,transactions:int,transactionrevenue:int,newvisits:int,screenviews:int,uniquescreenviews:int,timeonscreen:int,totaltransactionrevenue:int,sessionqualitydim:int> # SELECTでの表示例 {visits=1, hits=5, pageviews=5, timeonsite=76, bounces=null, transactions=null, transactionrevenue=null, newvisits=null, screenviews=null, uniquescreenviews=null, timeonscreen=null, totaltransactionrevenue=null, sessionqualitydim=0}
上記でtotalsの中のhitsやtimeonsiteを取ってきたい場合、
SELECT totals.hits, totals.timeonsite FROM {table_name}
の感じで、「.」で繋げれば抽出することができる。
array型でデータが入力されている
# TableのDDL(customdimensionsカラム) array<struct<index:int,value:string>> # SELECTでの表示例 [{index=20, value=hpsk16064}, {index=29, value=undefined}, {index=14, value=,}, {index=1, value=Mozilla/5.0 (Linux; Android 8.1.0)}, {index=26, value=fukuoka}]
# 抽出パターン1: arrayの位置で取得(1番目の要素を抽出) # →Pythonと違って一番最初の要素は1から始まる SELECT customdimensions[1] # 抽出パターン2: arrayを要素ごとに行に変換 # →要素分の行数に分解される SELECT cus_unnest FROM {table_name} CROSS JOIN UNNEST (customdimensions) as t(cus_unnest)
抽出パターン2で欲しい要素(例だとindex)が決まっていればWHERE句で条件つけてもよい。
もしもっといい方法あればコメントで教えてください!