Google Apps Script× BigQuery × Googleスプレッドシート × データポータルで簡易CRMを作ってみた

こんにちは!! BASE BANK 株式会社 Dev Division にてSoftware Developerをしている永野(@glassmonkey)です。 普段はGo/Python/PHPを主に生業に開発・運用から何でもござれの精神でフルサイクルエンジニアをしています。

現在、自分たちのプロダクトであるYELL BANKの分析基盤を構築しています。 その際に、BigQueryで扱っているデータをGoogle App Script(以下GAS)、Googleスプレッドシートとデータポータルで簡易CRMをビジネスサイドのメンバーである猪瀬 (@Masahiro_Inose)と協力して作ったのでそのご紹介です。

いざ実施してみるとハマってる点もそこそこ多く、意外とGASやBigQueryの連携している情報が少なかったので、誰かの助けになれば幸いです。

thebase.in

簡易CRMツールをスプレッドシートベースで作成した背景

弊社で開発している「YELL BANK」では、プロダクト改善のためにユーザーインタビューなど定性情報を顧客に直接聞く形で得ていくスタイルを積極的に実践しています。 その記録を一元管理する場所がなかったこともあり、CRMツールが必要な状況でした。

CRMツールとしてはSalesforceなどが有名ではありますが、様々なデータがBigQueryに集約をする状況でありました。

今回は以下の2点で慣れているスプレッドシートベースのシステムの採用をしました。

• プロダクトとして仮説検証フェーズなので見たい内容は定まってないこと
• データ分析を主として実施しているビジネスサイドのメンバーが自身でカスタマイズ容易なこと

謝辞

一部改変をしておりますが、コードなどは【GAS/BigQuery】日付に応じて異なるクエリを実行から大部分を引用させていただきました。 この場を借りてお礼を申し上げます。

構成

GAS周りの基盤を私が作成して、集計のスプレッドシート構成からダッシュボードの構成まではビジネスサイドのメンバーで分担して作成しました。

簡易CRMの構成図

準備

GASのメンテは基本的には普段コードを書かないビジネスサイドのメンバーが扱うので、都度追加するコードはシンプルである必要がありました。

そこで、OAuth認証に関連するところやAPI経由でBigQueryを実行する処理は共通処理として切り出すことにしました。

GCPプロジェクトの準備

GASからBigQueryをOAuth経由で実行するので、その設定が必要です。

1. 今回は内部的に利用するので、UserTypeは内部を選びます。

0auth同意画面

1. アプリ名とサポートメールとデベロッパーの連絡先情報を入力します。内部用なので適当で良いです。

   

   Oauth設定画面(メールアドレスなど）

2. BigQueryのAPIが有効になっているか確認します。 このあとAPIを利用するので、もし無効になっていたら有効にしておきましょう。

BigQuery API確認画面

GASの共通プロジェクトの設定

まず最初に共通GASプロジェクト設定します。

GASのメンテは基本的には普段コードを書かないビジネスサイドのメンバーが扱うので、都度追加するコードはシンプルである必要がありました。

API経由でBigQueryを実行する処理は共通処理として切り出すことにしました。 認証用のOAuth設定を予め共通プロジェクトとの連携で行っておくことで、ビジネスサイドのメンバーが権限周りを考慮せずにすむようにもしました。

各種画面のスクリーンショットは2021年6月時点のものになります。

1. 新しいプロジェクトから共通プロジェクトを作成する

新しいプロジェクトを作成します。

新しいプロジェクトその

すると下記のように無題のGASのエディターが開きます。

GAS エディター

2. ライブラリを追加する

GASからスプレッドシートのアクセスを簡易的にしてくれる、gas-underscoreを入れます。 詳細はsimula-innovation/gas-underscoreを参照ください。 READMEから"プロジェクトキー"であるM3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4jを使うと良いとありますが、新エディターの場合使えませんので注意ください。

github.com

旧エディターを利用する場合　(非推奨）

1. Add a libraryから"プロジェクトキー"であるM3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4jを入力します。
2. バージョンは最新の2を選びます。 なお、この方法はいずれ廃止になる可能性があるのでおすすめはしません。

新エディターを使う場合

1. 左カラムのライブラリの+ をクリック
2. gas-underscoreの"スクリプトID"は1PcEHcGVC1njZd8SfXtmgQk19djwVd2GrrW1gd7U5hNk033tzi6IUvIAVなのでそれを入力する。調べ方は後述します。

]

調べ方としては、旧エディターで得た情報をベースにはなりますが、インストールするとGASのマニフェストファイルからスクリプトIDがわかるのでそれを利用します。 旧エディタの場合は表示 > マニフェストファイルを表示を選択すると見ることができます。

他にもプロジェクトキーしかわからないライブラリがある場合はこれを使うとわかるので便利です。

GASマニフェストファイルの一部

余談ですが、公式のリポのREADMEには一応Pull Requestを出しました。古いリポジトリなのでメンテされてない可能性が高いので、forkした私のリポジトリも記載しておきます。

github.com

3. サービスにBigQueryを追加する

今回はBigQueryのAPIを利用するので追加します。 1. 左カラムのサービスの+をクリック 2. BigQueryを選びます。バージョンは2021年6月時点で最新のv2を選択しています。

4. GCPプロジェクトと連携させる

1. 歯車マークのアイコンからスクリプトの設定に遷移します。

1. プロジェクトを変更からGCPプロジェクトの連携設定を追加します。 ここでうまくいかない場合は、連携先のGCPプロジェクトのOAuth認証が正しく設定できていない可能性があります。

ここのプロジェクト番号とはGCPのダッシュボードのプロジェクト情報で確認できます。

プロジェクト情報

5. GASの共通コードを用意する。

集約したいスプレッドシートのID、シートの名前、実行したいクエリは都度変わるとのでそこを外部から渡せるように共通処理を書いておきます。

共通処理内部でBigQueryへのAPIリクエストを秘匿するようにしていので、ここはビジネスサイドのメンバーは意識しなくて済むようしておきます。

プロジェクト番号のところは、前述したOAuth設定したBigQueryを実行するgcpプロジェクト番号に読み替えください。

function run(spreadsheetId, sheetName, query) {

/**
* コンフィグ設定
**/
const projectId = 'プロジェクト番号';
// 出力先シートSpreadsheet
const sheet = SpreadsheetApp.openById(spreadsheetId);

  //出力するシート名
  const workSpace = sheet.getSheetByName(sheetName);

  const request = {
    query: query,
    useLegacySql: false
  };

  execBigQuery(projectId, workSpace, request);
};

function execBigQuery(projectId, ws, request) {
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;
  //BigQuery実行
  queryResults = checkOnQueryJobStatus(projectId, queryResults, jobId);
  //結果を取り出す
  const rows = getAllResults(projectId, queryResults, jobId);
  //結果を書き出す
  outputToSpreadsheet(rows, ws, queryResults);
}


function checkOnQueryJobStatus(projectId, queryResults, jobId) {
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  return queryResults;
}

// Get all the rows of results.
function getAllResults(projectId, queryResults, jobId) {
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }
  return rows;
}

// Clear sheet data and output BigQuery results to sheet.
function outputToSpreadsheet(rows, ws, queryResults) {
  if (rows) {
    ws.clear();

    // Append the headers and return header cols.
    const headers = appendHeader(ws, queryResults);

    // Append the results and return all spreadsheet data.
    const data = appendResults(rows, headers);

    ws.getRange(2, 1, rows.length, headers.length).setValues(data);

    return data;
  } else {
    Logger.log('No rows returned.');
  }
}

function appendHeader(ws, queryResults) {
  const headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  ws.appendRow(headers);
  return headers;
}

function appendResults(rows, headers) {
  const data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }
  return data;
}

詳細は後ほど記載しますが、各種呼び出しは以下のような形になります。 実際にシートに集計クエリを書くビジネスサイドのメンバーとしては、集計クエリをどこのシートに出力するかのみ考慮すれば良いようにしました。

function example(){
  //シートID, シート名, クエリを指定する
  共通プロジェクトの名前.run(SpreadsheetApp.getActiveSpreadsheet().getId(), "集計結果シート", `何か集計用SQL`)
}

シートごとの設定編

共通プロジェクトのスクリプトIDをメモ

集計シートからBigQueryを実行できるように共通プロジェクト呼び出しをします。 共通プロジェクトのスクリプトIDをメモします。

スプレッドシートに紐づくGASを開く

1. 集約用のデータを扱うスプレッドシートを用意します。
2. スプレッドシートの ツール > スクリプトエディタからシートと紐づくGASを用意します。

スプレッドシートと紐付いているGASはコンテナにシート名の記載があるので、念の為確認するといいでしょう。

1. ライブラリの+から共通プロジェクトを呼び出しをします。

ここで呼び出しをすることで共通プロジェクトの名前.runで共通処理を呼び出しできるようになります。

1. BigQueryを共通処理と同様にサービス設定に記載します。

呼び出し関数を用意する

何かの集計結果のSQLを集計結果シートに出力する場合は下記のように書くことで実行できます。

基本的にはここの実装はビジネスサイドのメンバーが書くことになるので、 集計したいクエリを書くことに専念できるような呼び出し方としました。

また、関数として切り出すとボタン配置などやデバッグがしやすいのでおすすめです。

function example(){
  //シートID, シート名, クエリを指定する
  共通プロジェクトの名前.run(SpreadsheetApp.getActiveSpreadsheet().getId(), "集計結果シート", `何か集計用SQL`)
}

呼び出し用のボタンを用意する

gasの実行には色々方法がありますが、今回は不要な集計処理を走らせたくなかったのと必要なときに実行できるようにボタンを用意する方式にしました。

1. 適当にボタンとなる図形を挿入 > 図形描画から配置します。

1. 図形右上の︙からスクリプトの割当を選びます。

1. 用意した関数名を入力します。 これが完了すると用意した図形を押下すると集計クエリが出力されるようになります。

結果と使用感

Google Data Studioの連携部分に関してはGoogle Data Studioで何を出すかで変わってくるので割愛します。 結果としてビジネスサイドのメンバーが作ってくれたダッシュボードの一部ですがご紹介します。 架空の数字ですが、イメージは伝わると思います。

簡易CRMのイメージ図

当初の目的通り、ユーザーインタビューなどの定性情報を得にいく時にも、顧客とのコミュニケーション記録を有効に活用できるようになったとのことでした。 過去にコミュニケーションしたショップがファネルの上位のフェーズになったといった変化を発見できるようになったとのことです。

感想

データ分析もより専門性のあるビジネスサイドのメンバーに委譲する流れができた点も良かったと考えています。このおかげで全体としては2週間ほどでCRMのシステムを構築できました。

BigQueryをGAS経由で呼び出せるといろいろ楽なので、誰かの助けになれば幸いです。

そのようなスピード感を持って一緒に開発するアプリケーションエンジニアやカスタマーサクセスのメンバーを募集中です。

herp.careers

herp.careers