マルティスープ
Staff Blog

2018.11.25

GCPの課金データをBigQueryにエクスポートしてDataStudioで可視化してみる（３）

みなさん、こんにちは。

前回まで、GCPの課金データをBigQueryにエクスポート、DataStudioで簡単に可視化するところまで解説しました。

GCPの課金データをBigQueryにエクスポートしてDataStudioで可視化してみる（１）

GCPの課金データをBigQueryにエクスポートしてDataStudioで可視化してみる（２）

前回作成した表では、実はクレジット（値引き金額）を反映していませんでした。Google Maps Platform は、みなさんご存知かと思いますが、毎月$200は無料で利用できます。では、早速、クレジットを反映していきましょう。

クレジットは、「credits.amount」というフィールドが該当します。表を選択し、指標に「credits.amount」を追加してみてください。総計が「−30,167」になりました。しかし、なぜか金額の総計が「30,175」に変わってしまいました。おかしいですね。

気にせず、右上の金額にもクレジットを適用してみます。これを行うには、データソースに新しいフィールド「金額-クレジット」を作成する必要があります。リソースの「追加済みデータソースの管理」→「編集」→「フィールドの追加」をクリックします。

項目名にフィールド名を入力、数式に「金額 + credits.amount」と入力し、保存ボタンをクリックすると新しいフィールドが追加されます。

元の画面に戻り、右上の請求金額を選択し指標を先程作成した「請求金額」に変更します。すると９円になりました。おかしいですね。このプロジェクトはクレジットが適用され0円のはずです。

色々調べた結果、DataStudio上でクレジットを適用した請求額を計算すると上手くいかないことがわかりました。（多分）

ではどうするかと言うと、BigQuery上で予め計算した方が良いです。

BigQueryを開き、以下のようなSQLを書き、「ビューを保存」ボタンをクリックします。ビューの名前を「billing_view」を入力して保存を完了させてください。

SQLの内容は、以下の通りです。

SELECT
  ROUND(cost, 5)+ROUND(amount,5) AS cost_amount,
  ROUND(amount, 5) AS credit_amount,
  DATE(TIMESTAMP(CONCAT(SUBSTR(t0.invoice.month, 0,4), '-',  SUBSTR(t0.invoice.month, 5,2), '-1'))) AS invoice_date ,
  t0.*
FROM (
  SELECT
    *,
    (SELECT SUM(amount) FROM UNNEST(credits)) AS amount
  FROM
    `{データセット名}`
  ) AS t0;

cost_amount

請求金額からクレジットを引いた金額です。

credit_amount

クレジットの金額です。

invoice_date

正しく請求日で計算する場合は、請求年月（YYYYMM）から請求日（YYYY-MM-DD）に変換した方が良いです。

(SELECT SUM(amount) FROM UNNEST(credits)) AS amount

credits は構造体?で登録されています。1つのレコードに複数の credits が登録されている場合があるので、このような書き方をするようです。

これで、クレジットを計算済みのビューが作成されました。

次に、作成したビューをDataStudio にアタッチします。

リソースの「追加済みのデータソース」→「データソースの追加」を開き、BigQueryを選択します。先程作成した「billing_view」に接続し、レポートに追加してください。

表を選択し、データソースを「billgin_view」、期間のディメンションを「invoice_date」、指標に「credit_amount」に変更します。

右上の請求金額も同様に、データソースを「billing_view」、期間のディメンションを「invoice_date」、指標を「cost_amount」に変更します。実際の請求金額の0円になりましたね。

今回は0円だったので行いませんでしたが、実際は、8%の消費税がかかりますので、必要な場合は、BigQuery上で計算するか、データソースから新しいフィールドを作成してみてください。

いかがでしたか？

DataStudio は非常に便利なBIツールだと思いますが、複雑なことを行おうとすると、エラーがよく発生します。私の勉強不足が原因でもあるのですが、困った時は元のデータソースを変更した方が、早く解決すると思います。

みなさん、是非活用してみてください！！

バイ