ユーザ行動分析の答え

こんにちは山本です。

どのデバイスでどこに住む、どこのページから流入したユーザがどこを回遊し、
どこをクリックし、どの順番にどのようにフォームを入力しCVするのか
または離脱したのか

これを定量的に知る術をみなさんあまり知りません。
上場企業でもメガベンチャーでもベンチャーでも
意外とそのような分析体制がないことを知っています。
いつもここから整理するからです。

以下、私がいつもやることを書きますが、
googleのエンジニアやマーケターに聞いてもこれが答えだということだったので
おそらくそれなりに筋は良いかと思います。

どのように行動分析をできるようにするのか

まず、サイトのすべてのページにページID、
パーツにpart番号を振っていきます。

こういう感じで、このページを開いたらページ種別がTOPで、
そのこの部分をクリックしたらpart_idが34番などと
全てにdata属性を振っておき、それをGTMの変数を作りbigqueryに送信します。

コードで見るとこんな感じになります。

そして全ページに
リードステータス
ゲストID
流入時のutmパラメーター
DBのユーザID
ページ種別
デバイス
パートID
などを
定義します。

上記は一部です。
そしてこれをGTMの変数として定義し、bigqueryに飛ばします。

bigqueryでは

このようなsqlで特定の期間の行動履歴を出すことができます。

そして取得した行動履歴を
吐き出したものがこちらです

これができたら、
例えば一例でgasスクリプトを記載しますが

/**
 * "results-20250603-151144" シートの C 列に '"part":"98"' または '"part":"99.1"' を含む行を抽出し、
 * "cta_click_user_list" シートにコピーします。
 */
function extractRowsByPartInColumnC() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheetName = 'results-2xx3-5xx4';
  var dstSheetName = 'cta_click_user_list';

  // ソースシート取得
  var srcSheet = ss.getSheetByName(srcSheetName);
  if (!srcSheet) {
    SpreadsheetApp.getUi().alert('シート "' + srcSheetName + '" が見つかりません。');
    return;
  }

  // 抽出先シートを取得 or 新規作成
  var dstSheet = ss.getSheetByName(dstSheetName);
  if (dstSheet) {
    dstSheet.clearContents();
  } else {
    dstSheet = ss.insertSheet(dstSheetName);
  }

  // 全データを取得
  var values = srcSheet.getDataRange().getValues();

  var output = [];
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    var cCell = row[2]; // C列はインデックス 2
    if (cCell == null) continue;

    var text = cCell.toString();
    if (text.indexOf('"part":"98"') !== -1 || text.indexOf('"part":"99.1"') !== -1) {
      output.push(row);
    }
  }

  if (output.length === 0) {
    SpreadsheetApp.getUi().alert('該当する行が見つかりませんでした。');
    return;
  }

  // 抽出結果を A1 から書き込む
  dstSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
  SpreadsheetApp.getUi().alert('抽出完了：' + output.length + ' 行を "' + dstSheetName + '" シートにコピーしました。');
}



/**
 * "cta_click_user_list" シートの C 列から gid を抽出し、
 * 同じ行の I 列に書き込みます。
 */
function get_gid_from_cta_click_user() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'cta_click_user_list';
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert('シート "' + sheetName + '" が見つかりません。');
    return;
  }

  // シートの最終行を取得
  var lastRow = sheet.getLastRow();
  if (lastRow < 1) return;

  // C列のデータを取得（1 行目から最終行まで）
  var cRange = sheet.getRange(1, 3, lastRow, 1);
  var cValues = cRange.getValues();

  // I列に書き込むための2次元配列を準備
  var output = [];

  for (var i = 0; i < cValues.length; i++) {
    var cellText = cValues[i][0];
    var gid = '';

    if (cellText && typeof cellText === 'string') {
      // '{"uid":0,"gid":"' で分割
      var parts = cellText.split('{"uid":0,"gid":"');
      if (parts.length > 1) {
        // 2番目の要素を先頭の '"' で再分割して gid を抽出
        var remainder = parts[1];
        var secondParts = remainder.split('"');
        if (secondParts.length > 0) {
          gid = secondParts[0];
        }
      }
    }

    output.push([gid]);
  }

  // I列に一括で書き込む
  sheet.getRange(1, 9, output.length, 1).setValues(output);
}



/**
 * "cta_click_user_list" シートの I 列の値を重複を除いて
 * J 列に書き出します。
 */
function get_nayose_merge_cta_click_usa() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'cta_click_user_list';
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    SpreadsheetApp.getUi().alert('シート "' + sheetName + '" が見つかりません。');
    return;
  }

  // I列の最終行を取得
  var lastRow = sheet.getLastRow();
  if (lastRow < 1) return;

  // I列(9列目)のデータを1行目から最終行まで取得
  var iValues = sheet.getRange(1, 9, lastRow, 1).getValues();

  // 重複を排除するセットを作成
  var uniqueSet = new Set();
  for (var i = 0; i < iValues.length; i++) {
    var val = iValues[i][0].toString().trim();
    if (val !== '') {
      uniqueSet.add(val);
    }
  }

  // セットを配列に変換し、2次元配列に整形
  var uniqueArray = Array.from(uniqueSet).map(function(item) {
    return [item];
  });

  // J列(10列目)に書き込み
  if (uniqueArray.length > 0) {
    sheet.getRange(1, 10, uniqueArray.length, 1).setValues(uniqueArray);
  }
}

/**
 * "cta_click_user_list" シートの J 列の gid を一つずつ取得し、
 * "results-20250603-151144" シートの C 列を検索して
 * 当該 gid に対して '"cv":"1"' が一度も出現しない場合、
 * cta_click_user_list シートの同じ行の K 列に gid を書き込みます。
 */
function get_not_cv_user_from_cta_list() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ctaSheetName = 'cta_click_user_list';
  var resultsSheetName = 'results-20250603-151144';

  var ctaSheet = ss.getSheetByName(ctaSheetName);
  if (!ctaSheet) {
    SpreadsheetApp.getUi().alert('シート "' + ctaSheetName + '" が見つかりません。');
    return;
  }
  var resultsSheet = ss.getSheetByName(resultsSheetName);
  if (!resultsSheet) {
    SpreadsheetApp.getUi().alert('シート "' + resultsSheetName + '" が見つかりません。');
    return;
  }

  // cta シートの最終行を取得
  var ctaLastRow = ctaSheet.getLastRow();
  if (ctaLastRow < 1) return;

  // J列（10列目）のデータを取得（1行目～最終行まで）
  var jValues = ctaSheet.getRange(1, 10, ctaLastRow, 1).getValues();

  // results シートの最終行を取得
  var resultsLastRow = resultsSheet.getLastRow();
  var resultsCValues = [];
  if (resultsLastRow >= 1) {
    // C列（3列目）のデータを取得（1行目～最終行まで）
    resultsCValues = resultsSheet.getRange(1, 3, resultsLastRow, 1).getValues();
  }

  // K列（11列目）に出力するための配列を用意
  var output = [];
  for (var i = 0; i < ctaLastRow; i++) {
    var gid = (jValues[i][0] || '').toString().trim();
    if (gid === '') {
      // J列が空なら K列も空
      output.push(['']);
      continue;
    }

    // results の C列を検索して、同一 gid かつ '"cv":"1"' を含む行があるかチェック
    var hasCvOne = false;
    var searchPattern = '"gid":"' + gid + '"';
    for (var r = 0; r < resultsCValues.length; r++) {
      var text = (resultsCValues[r][0] || '').toString();
      if (text.indexOf(searchPattern) !== -1 && text.indexOf('"cv":"1"') !== -1) {
        hasCvOne = true;
        break;
      }
    }

    if (hasCvOne) {
      // '"cv":"1"' が見つかった → K列は空
      output.push(['']);
    } else {
      // '"cv":"1"' が一度も見つからなかった → K列に gid を書き込む
      output.push([gid]);
    }
  }

  // K列（11列目）に一括で書き込む
  ctaSheet.getRange(1, 11, output.length, 1).setValues(output);
}

つまり行動履歴からさまざまなパターンのユーザを抽出します。

CVしたユーザだけでなく、
見積もりボタン押したのに、CVしなかったユーザ
などの一連のこうどうの履歴を出していきます。

そして、定義の際にpartに説明をつけた内容とGPTにプロンプトをなげると

このような行動が丸わかりになる記述を取得できます。

これまではbigqueryの値をredshiftに送って分析などをやっていましたが
結局「費用はなんでも抑えたい/無料の範囲でやりたい」という人が多いのではないかと思います。
そのため、日にちを限定して上記のような方法でbigqueryから定義されたjsonを取得し
定義されたサイトのパートとストーリーをGPTのプロンプトにすれば
だいぶ細かくストーリー性をもった分析ができるようになります。

ちなみにサムネイルはバンコク法人の梶村です。