GmailとGoogle Analyticsとスプレッドシートだけでコンバージョンと流入経路を1:1で紐づける方法（GA4での方法もご紹介します）

2020年12月12日 12:02

コーポレートサイトへの問い合わせや資料請求などメール経由でコンバージョンの情報を取得している場合、「めちゃくちゃアツい問い合わせ来た！この問い合わせの流入経路何かな？」などと思うことってよくありませんか？私はあります。

普通にGoogle Analyticsを使うだけではコンバージョンの総件数と内訳が分かるだけで、コンバージョンデータと流入経路は1:1での紐付けができません。

もちろんMAやCRMのツールを使ったり、問い合わせフォームの送信時に流入経路のパラメータを保持しておいて一緒に送るなどの方法もありますが、わざわざ新しいツールをそのためだけに契約したりエンジニアに実装してもらったりというのも面倒です。

そこで今回はマーケターが自分でもできるよう、GmailとGoogle Analyticsとスプレッドシートだけを使ってコンバージョンと流入経路を1:1で紐づける方法を、ユニバーサルアナリティクス（旧GA）とGoogle Analytics 4（GA4）両方でご紹介します。

書いている人

石渡貴大
株式会社マインディアというマーケティングテクノロジーを扱う会社でBtoC・BtoB両方のマーケティングをしています。
非エンジニアなので用語の説明やコードの書き方など雑なところがあると思いますが、何か気になるところがあったら優しくご指摘くださいw

twitterアカウントはこちら
ぜひフォローしてくださいー！

前提

・ある程度予算が取れる場合は絶対MAやCRMツールを入れた方がいいです。予算が取れない場合の暫定措置としてご覧ください。
・紐付けはコンバージョンの発生時間をキーに行うので、コンバージョン件数が多すぎると辛いです。1日30〜40件程度までが限度なので、toCではなくtoBの問い合わせコンバージョンなどで使いやすいと思います。
・GA4の場合はBigQueryも必要になります。

共通 STEP① スプレッドシートに問い合わせ情報を蓄積

まず問い合わせの情報をスプレッドシートに蓄積していくために、Google App Script（GAS）を使います。書き方はすでにいろいろなブログがあるので、参考にしてみてください。例えばこちら。

問い合わせメールの本文から必要な情報を抽出し、スプレッドシートに蓄積する際に列を分けて（企業名、担当者名とメールアドレスなど）記入されるようにしておくのがおすすめです。

例えば弊社の場合ではメール本文がこのようにjson形式になっています。

問い合わせメール

そのため、この本文をパースしてカラムごとに情報を分割しています。こんな感じです。

var plainBody = messagesForThreads[i][j].getPlainBody();   // メール全体の本文を取得
var delSuffix = plainBody.slice(0, -800);   // 毎回決まって不要な文言が入っているため本文を必要な箇所までで切る
var parseJson = JSON.parse(delSuffix);    // jsonをパースする

var date = messagesForThreads[i][j].getDate();    // メール受信日時

var type = parseJson["type"];    // 問い合わせの種類
var subject = parseJson["subject"];    // 件名
var company = parseJson["company"];    // 会社名
var title = parseJson["title"];    // 役職
var name = parseJson["name"];    // 名前
var mail = parseJson["mail"];    // メールアドレス
var phone = parseJson["phone"];    // 電話番号

var body = parseJson["body"];    // 問い合わせの内容
var strBody = new String(body)    // 内容を文字列に変換
var sliceBody = strBody.slice(0,100)    // 長くなるとスプレッドシートで読みづらいので先頭の100文字までで切る

ここまで設定ができたら、あとはGASのタイマーを使い、新規メールが届いているかどうかを定期的にチェックするようにします。弊社の場合は10分おきにしています。

そうして蓄積されているスプレッドシートがこちら。

コーポレートサイト問い合わせ管理_-_Google_スプレッドシート

旧GA STEP② スプレッドシートにGA情報を蓄積

GAの当日データを定期的に取得してスプレッドシートに蓄積することで、コンバージョンが発生した時間と流入経路の紐付けを行えるようにします。

そのためにまたGASを使います。GASでGAのデータを扱う際には事前準備が必要となりますので、参考サイトを見ながら設定を進めてください。

弊社で利用している実際のコードはこちらです。

function cvUsersData() {
   // コンバージョンユーザー数を取得
   const cvUsersFrom = Analytics.Data.Ga.get(
     'ga:xxxxxxxxxx',   // ビューIDは置き換えてください
     'today',   // 開始日
     'today',   // 終了日
     "ga:goal1Completions",   // 取りたい指標によって置き換えてください
     {
       'dimensions': 'ga:sourceMedium',    // 参照元とメディアのディメンション設定
       'samplingLevel': 'HIGHER_PRECISION'    // サンプリングレベルの設定
     }
   ).rows;
  
  // 返ってきた配列を結合して文字列に 
  let text;
  if (cvUsersFrom) {
   for (i = 0; i < cvUsersFrom.length; i++) {
     cvUsersFrom[i] = cvUsersFrom[i].join(', ');
     cvUsersFrom[i] = '[' + cvUsersFrom[i] + ']';
   }
    text = cvUsersFrom.join(', ');
  } else {
    text = 'no cv';
  }
 
   const formattedDate = Utilities.formatDate(new Date(), "JST", "YYYY/MM/dd HH:mm");   // 現在の日時取得(JST)

   const sheet = SpreadsheetApp.getActiveSheet();   // 出力先のシートを指定
   const lastRow = sheet.getLastRow();     // 最終行を取得

   sheet.getRange(lastRow+1, 1).setValue(formattedDate);     // 次の行に時間を出力
   sheet.getRange(lastRow+1, 2).setValue(text);     // 次の行に取得したGAデータを出力

}

これで、今日1日の流入経路ごとのコンバージョンユーザーの数、が取得できます。これをGASのタイマーで10分おきに実行されるようにしています。
※10分以内に2件のコンバージョンがあるとどっちがどっちか分からなくなってしまいます。1日のコンバージョン数が多い場合などはタイマーを5分にするなど短くした方が良いです。

スプレッドシートに出力されるとこんな感じです。

gaデータ_-_Google_スプレッドシート

取得時間と、コンバージョンデータの前の時間との差分を見れば、いつ発生したコンバージョンがどの経路からだったのか紐付けられる、という訳です。あとはこれを①のスプレッドシートに転記するだけです。

また、流入経路以外のデータも欲しい場合も対応可能です。その場合のGA向けのコードの書き方は複雑なので、こちらを参考に作成してください。

GA4 STEP② GA4とBigQueryの連携

さて、ここからはGA4の説明に入ります。

まず、スプレッドシートにメールデータを蓄積する方法はGAのタイプに関係がないので割愛します。

GA4の場合は、GASのAnalyticsのスクリプトを使うことができません（私が知らないだけかもなので、もし方法知ってるよという方は教えてください）。そのため、BigQueryを活用します。

旧GAでは有料版を使っていないとBigQueryと連携できませんでしたが、GA4からは無料版でも使えるようになりました（ストレージやクエリの料金はかかります）。GA4のデータをBigQueryに出力し、BigQueryでタイムスタンプのついたイベントデータを確認することができます。

GA4とBigQueryの連携はこの記事の手順でやれば数分でできます。

GA4 STEP③ BigQueryとRe:dashの連携（省略可）

弊社ではプロダクトのデータを可視化するためにRe:dashを使用しています。いちいちGAデータを見るためだけにBigQueryを開くのが面倒なのでRe:dashと連携させてRe:dashからクエリを叩くことにしました。

もちろんBigQuery上でクエリ実行することもできるので、不要な方はこの手順はスキップしてください。

GA4 STEP④ SQLクエリの作成

最後に、必要なデータを抽出するためのクエリ作成です。BigQueryに格納されたデータは1つのレコードに複数のパラメータがネストされた形になっていて、基本的なSQLが書ける方でも最初は扱い方に戸惑うと思います。私もいまだに慣れていません。最初のうちは、とりあえずUNNEST関数を使えばなんとかなる、ぐらいの理解で良いと思いますが詳しく知りたい方はこちらをご覧ください。

実際に使用しているクエリはこちらです。

SELECT
 DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') as datetime,
 event_name,
 params.key AS event_param_key,
 params.value.string_value AS event_param_value
FROM
 `analytics_*********.events_*`,    // ID部分は置き換えてください
 UNNEST(event_params) AS params    // この部分がUNNEST関数です
WHERE
 _table_suffix = '{{YYYYMMDD}}'    // {{YYYYMMDD}}はRe:dash用です。BigQueryで使う場合は日付をベタ打ちしてください。
 AND event_name = 'inquiry'    // 取得するコンバージョンのイベント名に置き換えてください
 AND (params.key = 'medium' OR params.key = 'source')
ORDER BY
   1,3

実行した結果がこちらです。

BigQueryで実行するとこちら。

当然ですが同じ結果ですね。

見ての通り、BQで実行した場合はマイクロ秒単位でイベントの発生時間が分かるため、旧GA+スプレッドシートで実行するよりこちらの方が正確に紐付けを行うことが可能です。

最後に

この方法を使うと流入経路だけではなく、例えばランディングページや閲覧されたコンテンツなども紐付けておくことができるので、MAツールがなくてもそれっぽいことをすることができます。予算がなくてもライトにできる施策としておすすめです。

また、何かフィードバックなどありましたらぜひtwitterの方まで！
twitterアカウントはこちら
随時記事の情報も発信していきますので、フォローお願いします！

よろしければサポートお願いします！そのうちオリジナルドメインにしたいなと思っているのでその資金にさせていただきます！