日本語(JP)
English

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

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

ブロックチェーンゲームの売り上げは、基本的にはEthereumネットワーク上に記録されており、ある程度調べる事が出来ます。

中には、EOSやTronなど他のチェーンにまたがっているため、集計が面倒であったり、
クレジット決済を導入していたりする所などは、完全に調べる事はできません。
また、決済方法がクリプトスペルズのように、スマートコントラクトではない場合も、調べる事が難しいです。

そんな中、どうやって調べれば良いかを今回、GoogleCloudのBigQueryを使って説明したいと思います。

※2021-03-21 画像を追加

BigQueryとは

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

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

BigQueryを利用開始しよう

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

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

ETHEREUMのデータセットを追加

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 列名

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

from テーブル名

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

where 条件 (省略可)

条件を記載します。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 列名 (省略可)

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

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

order by 列名 昇降 (省略可)

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

例文

例文1:クリプトキティーズの今月のゲーム内マーケット取引数と取引量を日ごとに調べる。

クリプトキティーズのマーケットのコントラクトアドレスは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乗した値で保存されています。)

Select DATE(block_timestamp) as block_date ,count(*) , sum(value)/pow(10,18)

from `bigquery-public-data.crypto_ethereum.transactions`

where
to_address = "0xb1690c08e213a35ed9bab7b318de14420fb57d8c"
and block_timestamp >= "2019-08-01"
and receipt_status = 1
group by block_date
order by block_date

出典 : SQL

例文:マイクリの今月の新規課金者数を日別で表示する。

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

select first_buy,count(*) as daily_new_users, sum(val) as new_users_total from
(
select from_address ,DATE(min(block_timestamp)) as first_buy , sum(value)/pow(10,18) as val from `bigquery-public-data.crypto_ethereum.transactions`
where
to_address in (
"0x273f7f8e6489682df756151f5525576e322d51a3",
"0x946048a75af11c300a274344887ec39452218b3d",
・・マイクリのコントラクトアドレスを全て小文字で。リストはブロックチェーンゲームインフォより。
"0x211b3c1f7893685e395553da61cf32c10764c23d",
"0xeb0860422043a0b8541356a7b6f671cba9e8f85a"
)
and block_timestamp >= "2018-08-01"
and receipt_status = 1
and value>0
group by from_address
order by first_buy
) #…①にあたる部分。
group by first_buy
order by first_buy desc #…②にあたる部分

出典 : SQL

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

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

例文:クリプトスペルズの売り上げを予測する

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

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

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

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

select sum(value)/pow(10,18) from `bigquery-public-data.crypto_ethereum.transactions`
where

to_address in (
SELECT to_address FROM `bigquery-public-data.crypto_ethereum.token_transfers`
where token_address="0x67cbbb366a51fff9ad869d027e496ba49f5f6d55"
and from_address="0x0000000000000000000000000000000000000000"
and DATE(block_timestamp) >= "2019-05-01"
group by to_address
) #…①

and from_address not in ("キャンペーンの入金等のアドレス")
and DATE(block_timestamp)>="2019-07-10"
and receipt_status=1 #…②

出典 : SQL

データを保存する

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

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

BigQueryの利用料金

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

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

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

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

気になるゲームを分析してみよう

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

ぜひ試してみましょう。

関連記事

雪駄×スニーカー「unda-雲駄-」のバーチャルモデル「unda 4.0」を販売

雪駄×スニーカー「unda-雲駄-」のバーチャルモデル「unda 4.0」を販売