こんにちは、ソリューションアーキテクトの今井です。
SORACOMの利用料や通信量はSORACOMユーザーコンソール (利用料、通信量)で確認いただけますが、表計算ソフトでの集計や分析に使えるCSVファイルもダウンロードいただけます。このブログでは、CSVファイルの内容の解説や、例えばSIM(通信回線)ごとの利用料・通信量を集計する方法をご案内します。
Ask SA! とは？
IoTプラットフォーム「SORACOM」を現場で活用するための技術情報を、ソラコムのソリューションアーキテクトより紹介する記事です。過去の “Ask SA!” はこちらから一覧できます。
利用料や通信料を集計したくなるのは、どんなとき？
日々、ソリューションアーキテクトとしてお客様とお話をしていると「利用料をSIMごとに集計したい」といったような声を聞くことが多々あります。これらの声をまとめてみると以下のようなユースケースとなりそうです。
SIMごとの日次もしくは月次の利用料を把握したい
このユースケースの主な背景は経理処理的なものが多そうです。例えばデバイスを設置している現場や、顧客ごとに通信料を把握したいというようなものです。
SIMごとの日次もしくは月次の通信量を把握したい
上記ととても似ていますが、背景は少し違って「異常な/想定外の通信をしているような端末がないか知りたい」というのがこのユースケースのよくある背景です。これ以外には「想定していた通信量と実際の通信量の答え合わせをしたい」というのもよく聞くお話です。
SORACOMで出力可能なCSVデータにはどんなものがある？
まずは課金関連の情報から見てみましょう。ダウンロード手順は以下の通りです。
SORACOMユーザーコンソールにログインして、右上の自分のアカウント名をクリックして開いたメニューの「ご利用料金」を開きます。
そして「CSV形式でダウンロード」をクリックすればOKです！
表計算ソフト(今回はExcel)で開くことができます。
さて、ダウンロードされたファイルを見ていきましょう。
料金の集計という観点では、imsi(回線ID)、billItemName(費目)、date(日付)、amount(料金)が使えます。なお金額に関係する項目としてはunitPrice(当該billItemNameの単価)、quantity(個数)がありますが、この値の小計がamountであるため、まずはamountを利用すれば問題ありません。
他の項目について知りたい場合は課金詳細情報 CSV リファレンスをご覧ください。
CSVファイル利用時の注意点:
1. カラムの並び順はダウンロード毎に変化する可能性があります。一行目の項目名を確認するようにしてください。
2. SORACOMコンソールの機能的な制約として、利用料金のCSVデータは「当月分（現在の月の1日からその日まで）のデータしかダウンロードできない」ことにご注意ください。つまり、任意の月を指定して1ヶ月分のデータダウンロードはできません。現状、任意の月のデータをダウンロードするためにはSORACOM CLIやSORACOM APIをご利用頂く必要があります。こちらの方法については追って続編の記事を書く予定です！
ではもうひとつのデータである通信量のダウンロードも行ってみます。
先程の利用料のCSVと同じ「ご利用料金」のページの最下部に「データ使用実績データをCSV形式でダウンロード」というメニューがありますので、この中からAir（SIM通信）のデータをダウンロードしましょう。過去90日分、もしくは過去6か月分を選ぶことができます。今回は過去90日分を選択してから「ダウンロード」ボタンをクリックすると、CSVファイルをダウンロードできます。
表計算ソフトで開くと以下の通り、さきほどととても似たデータが得られました。
imsi, dateは、課金情報CSVの時と同じ意味です。typeはSIMに設定された速度クラスになります。新しく登場したカラムとしてはuploadByteSizeToal(IoTデバイスからSORACOM側への “上り” 方向の通信量(byte))、uploadPacketCountTotal(上り方向のパケット数)、downloadByteSizeTotal(SORACOMからIoTデバイス側への “下り” 方向の通信量(byte))、downloadPacketCountTotal(下り方向のパケット数)があります。それ以外のカラムは先程と同様にグループIDやタグ名などが入っています。今回はimsi, date, uploadByteSize, downloadByteSizeを使えば、通信量集計という目的は達成できそうです。
表計算ソフトで集計をしてみる
表計算ソフトに取り込めたら集計をしてみましょう。目的別に紹介します。
ここからは表計算ソフトの「ピボットテーブル」機能を利用します。ピボットテーブル作成の手順はExcelであればこちらのページをご覧ください。
利用料を「SIM(回線)毎、日次」に集計
まずはシンプルに、SIM毎に日次の利用料金を集計しています。列にdate、行にimsi、値にamountの合計で集計できます。
いいかんじです！これでSIMごとの利用料金が集計できました。これは「端末ごとの通信費用」と読み替えることができますので、これだけでも十分に役にたつケースは多いのではないでしょうか。
利用料を「グループ毎、日次」に集計
次に、利用料をSIMグループごとに集計してみます。列にdate、行にgroup、値にamountの合計で集計できます。
たとえば「プロジェクトや顧客ごとにSIMのグループを分けている」というような環境ではこの集計方法によって費用の計算が可能になります。
通信量を 「SIM(回線)毎、日次」に集計
では次に通信量をSIMごとに集計してみましょう。アプローチは課金情報とほとんど一緒です。通信量CSVを対象としたピボットテーブルを作成した後、列にdate、行にimsi、値にuploadByteSizeToalの合計を設定します。
見え方の観点からuploadByteSizeTotalのみを集計対象にしていますが、downlinkByteSizeTotalも一緒に「値」フィールドに入れて双方を同時に集計することも可能です。
特定の月の通信量をSIMごとに集計
最後に特定の月の通信量をSIMごとに集計してみます。ファイルには90日分のデータが含まれていますので、下記のようにピボットテーブルのフィルタ機能をdateに対して設定することで実現できます。
いかがでしたでしょうか？コンソールからダウンロードできるCSVとお手元の表計算ソフトでも、かなりの利用料/通信量把握ができるのをご覧いただけたと思います。
より柔軟な分析や可視化を行うには？
より柔軟な可視化や集計、分析作業を行い場合はどうすればいいでしょうか？
「APIによるデータ抽出」「Pythonによる分析」といったようなキーワードが出てきます。実はSORACOMでは多くの機能をWeb APIで提供しており、この投稿でご紹介したSORACOMユーザーコンソールのCSVダウンロード機能も、このAPI群を使って実現されています。
下記のブログ記事にて、「APIでデータを取り出してPythonで集計、可視化する」という一連の手順と、サンプルプログラムををご用意していますので、ご興味ある方は下記を御覧ください！Python環境をお持ちの方は、Githubからコードをコピーしてすぐに動かすことができ、コードを見ながら実際に何をやっているかをご確認いただけるようになっています！
IoT データ収集・蓄積サービス「SORACOM Harvest Data」に蓄積したデータもAPI経由で取得し、ツールを活用することでCSV化できます。詳しくはAPI (コマンドライン) からのデータ取得方法をご覧ください。
より厳密な集計や、経理処理まで分けたい場合は？
例えば「顧客やプロジェクトごとに複数のSIMを払い出しをしていて、この収支を管理して経理処理もしっかり分けたい。」というようなケースもあると思います。ここで紹介した「SIMグループ別集計」という手もありますが、より厳密な管理や経理処理を明確にを行いたい場合は「SORACOMのオペレーター (アカウント) を分ける」というのがあります。
この方法であれば顧客やプロジェクトごとに収支の数字だけでなく経理的な処理まで厳密に隔離が可能になってきます。ぜひこのアプローチも心のどこかに置いておいていただければと思います！
― ソラコム 今井 (factory)