BigQueryのEthereumデータを使ってブロックチェーンゲームを分析しよう！

BigQueryはGoogle Cloud Platformが提供しているビッグデータ分析用のサービスです。
普通にエクセルやMySQLなどにデータを入れて、集計をしようとするとパソコンが止まってしまうような数テラバイトのデータでも、十数秒で結果を返してくれる、とても便利なサービスです。

BigQueryでは、Ethereumのブロックチェーンデータが一般公開データセットに保存されており、Ethereumのデータを通常のデータベースをいじる感覚で、短時間で集計できます。

こちらのページから、登録を行います。
https://cloud.google.com/bigquery/

なお、無料トライアルを押さずに、GoogleCloudPlatformへログインし、BigQueryと調べて追加すると、クレジットカードの登録等せずにお試しすることが可能です。
https://console.cloud.google.com/

BigQueryのサービスへ入ったら、「データを追加」から、「一般公開データセット」を押しましょう。

そこで、Ethereumと検索をすると、Ethereumのデータセットが出てきます。

先ほどのデータを追加の下の検索窓から「ethereum」と入力すると、
Crypto_ethereumの下に、blocks、contracts、logs、token_transfers、token、traces、transactionsと、7個のテーブルが表示されます。

売上の分析に必要なのは、transactionsのデータです。

token_transfersのデータも、現在のTokenの所有者や、これまでの来歴をチェックするのにつかわれます。
クリプトスペルズの売り上げ分析では、token_transfersから、カードが送られたウォレットアドレスを調べて、そのウォレットアドレスへの入金額をtransactionsより集計しています。

クエリは、SQLのSelect分を使用できます。
https://ja.wikipedia.org/wiki/SELECT_(SQL)

基本的には以下のような書き方をします。
select 列名
from テーブル名
where 条件
group by 列名
order by 列名　昇降

結果として表示させる列を指定します。
日付と取引量のグラフを表示したければ、「select 日付にあたる列,取引量にあたる列」のようにカンマ区切りで書きます。
トランザクションのリストを単純に全て表示したい場合は、「*」と指定して全て出力する事もできます。

使用するデータベースのテーブル名を指定します。。
テーブル名は、上記にあげたテーブルを使う場合は、
「bigquery-public-data.crypto_ethereum.〇〇〇〇」のように指定します。
Transactionsのログを集計したい場合は、「bigquery-public-data.crypto_ethereum.transactions」と書きます。

条件を記載します。andか、orで条件をつなぐことができ、
何日以降のデータ、どのコントラクトアドレスのデータなどを指定できます。
ここをなるべく細かく指定することで、Bigqueryの実行した際に処理するデータ量が少なくなり、一回当たりの価格が少なくなります。

例えば以下の例は、to_addressが"0xb1690c08e213a35ed9bab7b318de14420fb57d8c"で、block_timestampが8月1日以降の物で、receipt_statusが1のものを表示します。

where to_address = "0xb1690c08e213a35ed9bab7b318de14420fb57d8c" and block_timestamp >= "2019-08-01" and receipt_status = 1

ブロックチェーンゲーム単体の売り上げを調べる際に、重要なのがGroup byです。
ここを指定しないと、ゲーム全体の指定した期間中の全取引リストや、売り上げ合計など、細かすぎるデータやまとめてしまったデータしか取得できません。

日付毎、月ごとや、ウォレット毎などを調べるには、それぞれまとめたうえで、Selectでどのようにまとめるか指定する必要があります。
日付毎でまとめたい場合、「group by 日付にあたる列」と書き、「select 日付にあたる列,sum(取引量にあたる列),count(*)」と、指定する事で、取引量の合計と、取引数を得る事が出来ます。

日付順の場合は、そもそも並んでいるのであまり関係ありませんが、ウォレット毎の取引量を取得し、一番大きい取引量を調べたい時などは、並び替える必要があります。
「order by 取引量合計にあたる列 desc」とすると、取引量の合計が一番大きいウォレットから順に表示されます。

クリプトキティーズのマーケットのコントラクトアドレスは0xb1690c08e213a35ed9bab7b318de14420fb57d8c。

日付毎に表示したいので、「group by block_date」

八月以降の成功したトランザクションを検索したいので、
「and block_timestamp >= "2019-08-01" and receipt_status = 1」

出力は、日付、取引数、取引量としたいので、
「DATE(block_timestamp) as block_date ,count(*) , sum(value)/pow(10,18)」
（Ethereumの取引量は、10の18乗した値で保存されています。）

やや複雑になります。
課金者の最初の課金日をセレクト…①し、出力されたリストから、最初の課金日毎のリストを生成…②します。

これをグラフにすると、以下のようになり、コインチェックコラボより一気に三倍程度新規の課金者が増えているグラフが出てきます。

無料で出来るゲームの新規課金者が1人増えたという事は、無課金の新規ユーザーが課金率次第ですが、何人もいます。
CoinCheckコラボ後、相当、少なくとも一日100人以上、ユーザーが増えているのではないでしょうか。

クリプトスペルズは、入金用のウォレットアドレスがそれぞれのプレイヤー一人に一つずつ設定されており、カードを購入したら、その入金用のウォレットに、ERC721のアセットが送られます。

そのため、まずはカードが送られた先を調べます。
カードは、token_transfersテーブルに、やりとりされたデータが記録されています。
token_addressが「0x67cbbb366a51fff9ad869d027e496ba49f5f6d55」で、
カードが生成されたときの情報、「from_addressが0x0000000000000000000000000000000000000000」の時のデータを取得する事で、
入金用アドレスのリストが取得できます。…①

そこへ入金されている金額を、transactionsテーブルから集計すると、おおよその売り上げとなります。

（おおよその売り上げとしてあるのは、上記の手法だと、カードを持っていないけど入金したというユーザーのウォレットアドレスを捕捉できないためです。）

出力したデータを保存する場合は、「結果を保存する」から保存ができます。
CSVデータなど、出力してさらにそのデータを触ってみましょう。

なお、長いで他の場合は、一度、BigQueryテーブルとして保存して、さらにストレージにエクスポートしてからダウンロードする必要があり、その場合はクレジットカードの登録が必要になります。

BigQueryは有料ですが、無料で利用できる範囲があります。
月間1TB、1000GBまでの処理は無料です。
上記のマイクリのSQL文で、25GB程度です。
およそ、40回程度実行できます。
期間を区切れば、およそ1GB程度になるため、Whereでしっかりと条件は区切りましょう。

SQLを実行する前に、SQL文の入力欄の右下に、どのくらいの処理をするか表示されますので、そちらを目安に実行しましょう。

その後は従量課金制です。
最初に登録すると、300ドル分の無料枠が一年間ついてきますので、ほとんどやりたいことは全てこの枠で実行できます。

詳しい料金設定は下記をご覧ください。

これさえ覚えれば、ブロックチェーンゲームの売り上げ調査がいくらでも行えます。
売上や、取引量、DAUやMAUといったデータや、
カードの所有枚数、他にどんなゲームをやっているのかなど、
様々なデータを取得できます。

ぜひ試してみましょう。