見出し画像

【コード共有】LINEスタンプの売上CSVデータを手軽にがっちゃんこしよう

Chame

こんにちは、「がっちゃんこ」ってビジネス用語じゃないよな？と日々思っている私です。というのも、弊社は所謂意識高い系と揶揄されるようなカタカナビジネス用語がガンガン飛び交う環境なのですが、がっちゃんこだけはみんながっちゃんこって言っててちょっと面白いんですよね。FIXだのサマリだの付いているファイルたちの中に「2506データ_がっちゃんこ」とか入ってるｗ「そこはマージ、って言わないんだ！」って一人で勝手におかしくなっています。（でもクライアント宛てのメールにも「がっちゃんこデータを送付致します」ってしてるのは流石にどうかと思うw）

という前置きはさておき、LINEさんから来るCSVデータを使って分析するにあたり、一カ月ごとに分かれているデータを手軽に一つの表に出来たら＝がっちゃんこしたら捗りますよ、というお話です。

使用するのはGoogleアカウントがあれば無料で使えるスプレッドシートとGoogleドライブです。
事前準備として、①新規のスプレッドシート作成　②Googleドライブの一つのフォルダの中に全てのCSVファイルを入れておく　をしておいてください。

その次に、①で作成したスプレッドシートの上の方にある拡張機能クリック→Apps Scriptをクリックします。

そうすると、このようなページが立ち上がるので、右側に入っているfunction myFunction() { ... }の部分は削除します。

赤枠部分は全部削除

次にfunction myFunction() { ... }　が元々入っていた場所に以下のコードを貼り付けます。格納した全CSVを自動で読み込んでくれるGASのコードです。

function importStampsCSVs() {
  const folderId = 'ここにフォルダIDを入力';
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFilesByType(MimeType.CSV);
  
  let allData = [];
  let isFirstFile = true;

  while (files.hasNext()) {
    const file = files.next();
    const csvText = file.getBlob().getDataAsString('UTF-8');
    const csvData = Utilities.parseCsv(csvText);
    
    if (csvData.length === 0) continue;

    const headers = csvData[0];
    
    // 変換したい2つの列が「左から何番目か」を調べる
    const fromIndex = headers.indexOf("From (GMT+9:00)");
    const toIndex = headers.indexOf("To (GMT+9:00)");

    if (isFirstFile) {
      allData.push(headers);
      isFirstFile = false;
    }
    
    for (let i = 1; i < csvData.length; i++) {
      let row = csvData[i];
      
      // フィルター：From列が空でない場合のみ処理
      if (fromIndex !== -1 && row[fromIndex] !== "") {
        
        // From列のピリオドを削除
        if (fromIndex !== -1) {
          row[fromIndex] = row[fromIndex].replace(/\./g, '');
        }
        
        // To列のピリオドを削除
        if (toIndex !== -1) {
          row[toIndex] = row[toIndex].replace(/\./g, '');
        }

        allData.push(row);
      }
    }
  }
  
  sheet.clearContents();
  if (allData.length > 0) {
    sheet.getRange(1, 1, allData.length, allData[0].length).setValues(allData);
  }
}

// スプレッドシートを開いた時に自動で実行される関数
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  // メニューの名前と、ボタンを押した時に動かす関数を指定
  ui.createMenu('★管理メニュー')
      .addItem('CSVを取り込む', 'importStampsCSVs')
      .addToUi();
}
こんな感じになればOK

次に、対象のCSVファイルをすべて入れたGoogleフォルダのURLの末尾の英数字（フォルダID）をコピーします。

例：https://drive.google.com/drive/folders/1a2b3c4d5e6f7g8h9i0j... の最後の/の後ろ部分の 1a2b3c... の部分です。

そのコピーしたフォルダIDを　const folderId = 'ここにフォルダIDを入力';　となっている部分に貼り付けます。（前後のシングルクォーテーションを削除しないように注意）

赤枠部分のように入力できていればOK

入力が終わったら保存ボタンクリックもしくはCtrl+Sで一回保存してください。保存が終われば実行ができるようになるので、実行ボタンを押せば作成したスプシに全てのCSVデータが書き出されます。

最初にこの設定をしておけば、来月以降は①フォルダに新しいCSVを入れる②スプシで拡張機能クリック→Apps Scriptをクリック→実行クリック、で新しいデータをどんどん蓄積してくれます。
②に関しては、管理メニューというものを新しく作る処理を入れているので、上部の管理メニュークリック→CSVを取り込むクリック、でちょっとだけ簡単になります。
※コード動かしたのに管理メニュー出てこないよって人は数分待ってスプシを再読み込みしてください。

ひょっこり入れておいた

このCSV取り込みに関しては、フォルダに新しいデータが入ったら自動で更新したりスプシを開いたら自動で更新、とかも出来るのでお好みで調整していただければ。

補足
初めて動かす時は以下のようなやりとりが発生します。初見だとちょっとギョッとする文言ですが以下のように進めて大丈夫です。

権限を確認をクリック
デベロッパー部分が自分のメールアドレスだったら大丈夫です。
「詳細」をクリック
「無題のプロジェクト（安全ではないページ）に移動」をクリック
「すべて選択」にチェックを入れたあと下にスクロールしていくと「続行」とあるのでそれをクリック

Google「ねえねえ！変な奴（GAS）がフォルダの中勝手に見ようとしてる！こわすぎ！！危険危険！！！」
私「それ私が頼んだ奴だから大丈夫」
Google「ならば通ってヨシ！」

という感じです。初回のみこの確認が入ります。

このスプレッドシートを作っておけば、拡張機能からそのままLooker Studioが使えます。Looker Studioを使用しての分析はurajoさんの記事に詳しく載っています。
私はスタンプ作りを始める前に、情報収集をしている中でurajoさんの記事に辿り着き、データを載せてくださっている記事を片っ端から読みました。urajoさんのおかげでLINEスタンプの売上分析において注視するべきデータを知れたので、後から分析しやすいかを念頭に入れてシリーズ展開等を進めることが出来ました。データ分析のすすめ必読です。

※こちらのurajoさんの記事にご記載の日付のピリオド問題も、先述のGASコードでは対応しているので、Looker Studioで日付として認識してくれます。

と、ここまで書きましたが、私自身はスプシのモッサリした挙動が苦手&自作して販売している分析用ダッシュボードをいくつか持っているので、Excelで管理しています。そのためLooker Studioはほぼわかりませんｗでもちょっと触ってみた感じは直感的な操作で使いやすそうでした。
じゃあなぜスプシでご紹介したかというと、正規のOffice製品（多分互換ソフトだとクエリは使えないと思う）を自宅PCに入れているのは少数派だと思ったので、無料で使えるスプシでこんなことできるよ～の記事でした。

ここからは完全に蛇足ですが、私の管理方法はExcelのパワークエリで読み込み→独自の分類を加えた表を数式で自動反映、という感じです。
パワークエリで使用しているM言語はこんな感じです。（誰か使う人いるかもしれないから一応載せておきます）

let
    ソース = Folder.Files("ここに格納フォルダを入力"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    
      CSV読み込み = Table.AddColumn(#"フィルター選択された非表示の File1", "CSVデータ", each Table.PromoteHeaders(Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]))),
    
    展開されたテーブル列1 = Table.ExpandTableColumn(CSV読み込み, "CSVデータ", {"From (GMT+9:00)", "To (GMT+9:00)", "Type", "Item ID", "Item Title", "Country of Sale", "Sales", "Sales Counts", "Share Amount", "Revenue Share Rate", "Revenue Share (Pre-Tax)", "VAT", "Revenue Share", "Compensation"}),
    削除された列 = Table.RemoveColumns(展開されたテーブル列1,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    
    変更された型 = Table.TransformColumnTypes(削除された列,{{"From (GMT+9:00)", type date}, {"To (GMT+9:00)", type date}, {"Type", type text}, {"Item ID", Int64.Type}, {"Item Title", type text}, {"Country of Sale", type text}, {"Sales", Int64.Type}, {"Sales Counts", Int64.Type}, {"Share Amount", Int64.Type}, {"Revenue Share Rate", Int64.Type}, {"Revenue Share (Pre-Tax)", Int64.Type}, {"VAT", Int64.Type}, {"Revenue Share", Int64.Type}, {"Compensation", type text}}),
    フィルターされた行 = Table.SelectRows(変更された型, each ([#"From (GMT+9:00)"] <> null))
in
    フィルターされた行

これで読み込んだ表を元に各月の金額・個数を自動反映する表を作成しています。こういう表を作っておくと、後から「犬と猫でなにか傾向に違いがあるのか知りたいな」となれば、分類を追加すればすぐにダッシュボードを作れるように仕組化しているのでとても楽です。（urajoさんも独自の分類を作成していましたね！）

A~F列は手動入力、G列以降は数式で自動反映
数式は上部に出ている感じ

そして上記の表を元にAVERAGEIFSやMEDIANなどの関数で自動反映する表を作り、それが毎月の売上振り返り記事の中に出てくる表たちになっています。

そのうち期間が増えれば見やすいようにグラフにするかも。グラフ作りたいという場合もこういうデータベースを作っておけばすぐに対応できるので、自分が見たいデータを簡単に可視化できます。→それを直感的な操作で出来るのがLooker Studioですね！なので初めてはやはりスプシの方がおすすめ。

分析とかよくわからんもん！の人へ

これを機に覚えてみると、私のように分析ツールを売ったりデータマネジメント等を副業と出来るかもしれません。
AIの台頭で上記のような依頼は減るかと思いましたが、逆にAIにくわせる整形データが作れなくて困ってる人からの需要は増えている状況です。今までデータはほったらかしていた層が、AIで分析してみたくなった（もしくはする必要が出てしまった）のが理由のようです。

そうはいっても慣れていないと何からやればいいかわからないですよねｗそんな時はAIに頼りましょう。
最初に作成した全期間分が一つのデータとなったスプシをCSV形式で保存します。（スプレッドシートのメニューで 「ファイル」＞「ダウンロード」＞「カンマ区切り形式（.csv）」 を選ぶ）
※AIはテキスト形式の方が正確に読んでくれるので、スプシそのままよりもCSVにした方が確実です。

そのCSVをAIのチャット欄に貼り付けて「添付のファイルはLINEスタンプの全期間売上データです。傾向の分析とこれからの対策を考えて」とお願いするだけで客観的な意見を返してくれます。普段からスタンプ制作の相談をしているAIならその内容も踏まえて色々教えてくれます。
AIが出した分析結果を、urajoさんの記事を参考にして、視覚的に見やすいグラフを作るところから始めると良いと思います。それを繰り返すうちにLooker Studioの使い方を覚えるし、「もっと◯◯なデータが欲しいな。だから次は××なスタンプを作ってみよう」となったり「この分類を自動でスプシに反映するにはどうすればいいんだろう。数式を調べてみよう」と色々と派生していき、汎用性のあるスキルが身につきます。
AIがExcelを全部作ってくれる！グラフもスライドも自動作成！！という情報がSNSには溢れていますが、実務で使用する場合、細かい部分での修正はちょこちょこ発生します。修正のたびにいちいちAIに投げて待つよりも、ササっとその場で直せる方が早くて確実です。AIが作ったデータを修正できるスキルは持っていて損はないと思います。
私が独学でVBA等を覚え副業としてそれなりに収益をあげられている現状に辿り着いたのは、本業で同じような道のりを歩んだからです。…副業界隈ではロードマップっていうのかな！？

それでは、最後までお読みいただきありがとうございました。