見出し画像

スプレッドシートとBigqueryでミニマムな分析基盤を作った話@shopify

Ito Ryo

はじめまして、カイリューです。

つい先日、知り合いのshopifyで運営されるECサイトを手伝う過程でデータ分析の土台を作ったので、その備忘録としてどんな感じのものかを残していきます。

0.自己紹介

分析とサイトの改善が主な役割の社会人4年目マーケティング担当です。
普段はアクセス解析ツール（GA,Adobe）等を使って分析したり、BIツールと連携して可視化してたりします。ちなみに、SQLは人並み程度。

1.今回の目的

以下のようなスプレッドシートとBigquery（以下BQ）を利用した分析基盤を作りました。実際のコードはQiitaに書いていきますが、作成時に苦労したポイント含めて残すことが目的です。

画像1

2.事の発端

知り合いが最近流行りのshopifyを使ってECサイトを運営するらしく、その分析を手伝うことになりました。ただ、その会社はエンジニアが1名でその他が営業やマーケなどビジネス職という状況です。そのうえお金もあまりないとのこと。

そこで、まずはなるべくお金とエンジニアリソースをかけずに集計する土台を整備するところから始まります。

クラウド上にDBを立てることやBIツールに流しこみも考えましたが、こちらで作れる上に費用感込で最小構成でやるならスプレッドシートとBQが今の所ベターかなと考えました。

3.分析環境について

僕が考えた構成は以下のとおりです。

画像2

shopifyにはAPIが存在するので、注文データや商品データといった事業ログはコードがかければ可能でした。基本的に取得したデータをスプレッドシート経由もしくは、直接BQへ溜め込む想定です。

データの利活用に関しては、定常的にモニタリングしたいKPIはData portalでダッシュボードを作成。販売実績などアドホックに分析が必要になるものについては、ちょっと前に一般提供が始まったスプレッドシートと連携で対応します。必要なクエリはこちらで準備しておき、適宜項目を調整してもらう想定です。

4.実装

実装したのは主に以下の2点です。

① shopifyAPI→スプレッドシート
② スプレッドシート →BQ

shopifyから事業ログをスプレッドシートに取り込むところでは、販売する商品一覧と注文データを取得しています。また、スプレッドシートからBQへデータを取り込む際には、更新データのみを追加するようにしました。続けて、簡単にですが説明していきます。

① shopifyAPI→スプレッドシート

shopifyにはREST APIがあるので、Google App Scriptで定期的にデータを取得しにいくようにしました。基本的にfetchでデータを取得した後にパースし、for文で地道に必要なデータだけセルに打ち込むという流れです。

一つ手間だったことは、GASには6分の実行時間を超えると処理を終了するという制限です。幸いにも？6分を超えるほどのデータ量ではない見込みですが、前もって回避するようにしておく必要があります。

今回は他の方を参考にして、実行開始から4分を経過した時点で進捗をメモして次のトリガーを仕込むというもので対応しました。あとは、shopifyは1回あたりのレスポンスにデフォルト50件、MAX250件というルールがあります。そのため、都度次のページングをメモしておけば大丈夫です。

② スプレッドシート →BQ

次にスプレッドシートに保存した注文データをBQに追加していきます。
毎日全件を取得するわけにもいかないので、前日に更新されたデータだけを毎日取得するようにしました。

注文データにはcreated_atとupdated_atカラムが存在しており、新規追加時にはどちらも同じデータが追加され、キャンセルなどがあった際にはupdated_atだけが更新される仕様です。

とはいえ、データを取得した際にはBQにまだない新規データと一部だけ変更がある更新データが混在しています。とりあえず全部追加というわけにもいかなかったので、以下の記事を参考に差分を取り込むようにしました。

ようするにテーブル2つを比較した上で、新規もしくは更新データのみを抽出しマージするというものです。（すごい勉強になりました。）
そのため、まずはシート上のデータでテーブルAを作成し、既存テーブルに差分をマージと2種類のクエリを実行しています。今回はこれらもGAS上で対応可能です。

ここまででBQ上に事業ログを蓄積する仕組みです。

あとは、BQから直接Data portalを連携した上でダッシュボードを作成。アドホックな分析したい方向けに一部をクエリでスプレッドシートに抽出。
これで小さな構成ですが、データ分析の土台ができました。

5.積み残し

簡易な土台を作成することはできましたが、BQの権限周り切り分けなどは残しています。個人情報はAPIからの取得時に落とすようにしていますが、運用次第ではBQへの取り込みはあり得ます。

ただ、この場合どこまでを見れるようにするか、いつマスキングするかなど新たに考えるべきことがでてくるので注意が必要です。僕もまだ初心者なので、依頼主と相談しつつ進めていきます。

6.終わりに

ここまで書いた時点でそこそこ長くなってきたのと、実際のソースコードについては読者が分かれそうなので、次の記事に任せたいと思います。
あとは、shopifyでのアクセスログ収集などもGTM経由で実装したので、そのあたりも残したいと思うので、興味があれば見てってください。

この記事を読んでいただきありがとうございました。

この記事が気に入ったらサポートをしてみませんか？