ユーザ行動分析の答え

こんにちは山本です。

どのデバイスでどこに住む、どこのページから流入したユーザがどこを回遊し、
どこをクリックし、どの順番にどのようにフォームを入力し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しなかったユーザ
などの一連のこうどうの履歴を出していきます。

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”pre”:”LIST”,”l_id”:5,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”7″,”l_id”:5,”device”:”PC”}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”l_id”:5,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”pre”:”LIST”,”l_id”:5,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”7″,”l_id”:5,”device”:”PC”}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”l_id”:5,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”7″,”l_id”:5,”device”:”PC”}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”l_id”:5,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”LIST”,”pre”:”CAR”,”l_id”:5,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”CAR”,”pre”:”DET”,”l_id”:25,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”15″,”l_id”:25,”device”:”PC”}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”CAR”,”l_id”:25,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”98″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”96″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”93″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”93″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”94″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”act”:”1″,”cv”:”0″,”part”:”94″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”pre”:”DET”,”l_id”:20,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”10″,”l_id”:20,”device”:”PC”,”p_id”:”8545″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”pre”:”DET”,”l_id”:20,”cv”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”utm”:””,”LD”:”10″,”l_id”:20,”device”:”PC”,”p_id”:”8571″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:20,”part”:”0″}

{“uid”:0,”gid”:”NslfRtF3″,”pg”:”DET”,”l_id”:”15″,”act”:”1″,”cv”:”0″,”part”:”2006″}

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

/** * Gradation.js – スムーズに切り替わるグラデーション背景ライブラリ * class=”grade” – アニメーションありのグラデーション背景（ランダムな開始グラデーション） * class=”grade1″ ~ “grade6″ – アニメーションなしの静的グラデーション背景 * @version 1.2.0 * @author GitHub Copilot */ (function() { // グラデーションの定義 const gradients = [ ‘linear-gradient(to right, #ff8585, #def493)’, ‘linear-gradient(to right, #9ffedb, #1552f9)’, ‘linear-gradient(to right, #f6ffc7, #f4a7f4)’, ‘linear-gradient(to right, #eae5ff, #cff4fd)’, ‘linear-gradient(to right, #eae5ff, #ffb495)’, ‘linear-gradient(to right, #dcff99, #dedeff)’ ]; // oklchバージョン（対応ブラウザ用） const gradientsOklch = [ ‘linear-gradient(to right in oklch, #ff8585, #def493)’, ‘linear-gradient(to right in oklch, #9ffedb, #1552f9)’, ‘linear-gradient(to right in oklch, #f6ffc7, #f4a7f4)’, ‘linear-gradient(to right in oklch, #eae5ff, #cff4fd)’, ‘linear-gradient(to right in oklch, #eae5ff, #ffb495)’, ‘linear-gradient(to right in oklch, #dcff99, #dedeff)’ ]; // 現在のブラウザがoklchに対応しているか確認 const supportsOklch = CSS.supports(‘color’, ‘oklch(0.5 0.2 0)’); const gradientList = supportsOklch ? gradientsOklch : gradients; // スタイルを動的に追加 const style = document.createElement(‘style’); style.textContent = ` /* 共通スタイル */ .grade, [class^=”grade”] { position: relative; overflow: hidden; z-index: 0; } /* アニメーション付きグラデーション */ .grade { /* CSS変数でグラデを受け取る。初期値はデフォルトで、後でJSで上書き */ –g1: ${gradientList[0]}; –g2: var(–g1); } .grade::before, .grade::after { content: “”; position: absolute; top: 0; left: 0; width: 100%; height: 100%; transition: opacity 2.5s ease-in-out; z-index: -1; } .grade::before { background: var(–g1); opacity: 1; } .grade::after { background: var(–g2); opacity: 0; } .grade.fade-next::after { opacity: 1; } /* 静的グラデーション (grade1-grade6) */ .grade1 { background: ${gradientList[0]}; } .grade2 { background: ${gradientList[1]}; } .grade3 { background: ${gradientList[2]}; } .grade4 { background: ${gradientList[3]}; } .grade5 { background: ${gradientList[4]}; } .grade6 { background: ${gradientList[5]}; } /* コンテンツは前面に */ .grade > *, [class^=”grade”] > * { position: relative; z-index: 1; } `; document.head.appendChild(style); /** * ランダムな整数を生成（0からmax-1の範囲） * @param {number} max – 最大値（この値は含まない） * @returns {number} – ランダムな整数 */ function getRandomInt(max) { return Math.floor(Math.random() * max); } /** * グラデーション背景をクロスフェード切り替え * @param {Object} options – 設定オプション * @param {number} options.intervalMs – 背景切り替え間隔（ミリ秒） * @param {number} options.fadeMs – フェード時間（ミリ秒） * @param {Array} options.gradients – カスタムグラデーション配列（オプション） */ function startGradationCrossfade(options = {}) { const config = { intervalMs: options.intervalMs || 5000, fadeMs: options.fadeMs || 2500, gradients: options.gradients || null }; // 使用するグラデーションリスト const customGradientList = config.gradients || gradientList; // class=”grade”を持つすべての要素にグラデーションを適用 document.querySelectorAll(‘.grade’).forEach(el => { // ランダムな開始インデックスを生成 let idx = getRandomInt(customGradientList.length); // 初期グラデを CSS変数にセット（ランダム） el.style.setProperty(‘–g1’, customGradientList[idx]); el.style.setProperty(‘–g2’, customGradientList[idx]); setInterval(() => { const next = (idx + 1) % customGradientList.length; // 次の背景を –g2 にセット el.style.setProperty(‘–g2’, customGradientList[next]); // クロスフェード開始 el.classList.add(‘fade-next’); // フェード完了後に g1←g2 にしてリセット setTimeout(() => { el.style.setProperty(‘–g1’, customGradientList[next]); el.classList.remove(‘fade-next’); idx = next; }, config.fadeMs); }, config.intervalMs); }); } // DOMContentLoadedイベントで自動実行 document.addEventListener(‘DOMContentLoaded’, function() { // グローバルに設定がある場合はそれを使用 const options = window.gradationOptions || {}; startGradationCrossfade(options); }); // ライブラリをグローバルに公開 window.Gradation = { start: startGradationCrossfade, gradients: gradients, gradientsOklch: gradientsOklch, // ユーティリティ関数も公開 getRandomGradient: function() { return gradientList[getRandomInt(gradientList.length)]; } }; })();

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

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

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