Kalsarikannint

お問い合わせ

サイト内検索

改！広告収益分配ツールを改善してみた

develop GASJavaScriptWordPress

こんにちは。Kalsarikannintのフロントエンド担当・daiです。

約1年前、Google AdSenseの審査に通過したタイミングで、このKalsarikannint運営2人がケンカしないために「広告収益分配ツール」を作りました。

祝！AdSense通過！広告収益分配ツールを作ってみた

仕組みとしては、Google Analyticsから月次のページ別PVを取得し、AdSenseの売上金額を取得して、執筆者ごとのPV割合で収益を分配するというものです。すべてGoogle系のサービスで完結するので、Google Apps Scriptでスプレッドシートに集計する形にしていました。

v1の課題点と改善策

ただ、実際に運用する前提で見直してみると、いくつか気になるところがありました。

レポートが見づらい

スプレッドシートで、まっさらなシートにレイアウトも含めてすべてGASで描画させていたので、面倒くさくて凝ったデザインにしていなかったのが原因です。

今回からは、テンプレートのシートをあらかじめ作っておき、それをコピーして月ごとのシートを作成、そこに値だけを入れ込む仕様に変更しました。

▲ リニューアル後のレポート画面。

記事ページ以外も集計される

トップページやカテゴリ内記事一覧ページなど、記事本体ではないページも集計の対象になってしまっていました。

今回は正規表現などを使って、以下のルールで、純粋な記事ページだけを集計の対象とするようにしました。

  • /{カテゴリ}/{記事ID} でパーマリンク設定しているので、 /{文字列}/{数値} の形式のURLのみを対象とする
  • ただし、 /page/2 のようにページネーションで遷移した先も同様の構成になるので、文字列 == page は除外としました

執筆者名を手入力しないといけない

新しい記事を見つけたら、「記事一覧」シートに追加され、執筆者名は僕が毎月手動で入力する仕様にしていました。

WordPressではAPIで /wp-json/wp/v2/posts で記事情報を JSON 形式で取得でき、そこに author の ID が載るので、そこを参照して自動で執筆者まで取得するように改善しました。これでノータッチで集計が完了するようになりました。

スプシを見に行かないとレポートが読めない

そもそも、スプレッドシート内で完結させていたので、スプシにアクセスしないとデータを見ることができませんでした。

今回の改修のついでに Slack 通知を実装したので、大枠は Slack で確認することができるようになりました。毎月通知が来るので、しばらく記事書けてないときのいいケツ叩きになってくれそうですw

▲ Slackに通知されるメッセージ。意外と「読まれた記事」がお互い良い刺激になりそう。

改修後のソースコード全文

ということで、改修後のGASは以下のようになりました。


/***** 設定 ********************************************/
const CONFIG = {
  GA4_PROPERTY_ID: '',                   // GA4 Property ID
  CURRENCY_FORMAT: '¥#,##0',
  PAGE_LIST_SHEET_NAME: 'ページ一覧',
  TEMPLATE_SHEET_NAME: 'template',       // テンプレートシートの名前
  DETAIL_START_ROW: 12,                  // 月間PV明細のデータ開始行
  ADSENSE_ACCOUNT_NAME: 'accounts/pub-', // AdSense アカウント
  SITE_DOMAIN: 'kalsari.net',
  OVERWRITE_PAGE_LIST_TITLES: false,
  SLACK_WEBHOOK_URL: 'https://hooks.slack.com/services/YOUR/WEBHOOK/URL' // Slack Webhook URL
};
/******************************************************/

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('月次集計')
    .addItem('先月分の明細を作成', 'runMonthly')
    .addItem('毎月10日に自動実行を設定', 'createMonthlyTrigger')
    .addToUi();
}

/** メイン：先月分のシートを作成 */
function runMonthly() {
  const ss = SpreadsheetApp.getActive();
  const {start, end, ym} = getPrevMonthRange_();

  // 1) 前月 AdSense 売上（合計）
  const earnings = fetchAdsenseEarnings_(start, end, CONFIG.SITE_DOMAIN); 

  // 2) 前月 GA4 取得＆記事URLフィルタ＆集計
  const aggregated = fetchAndAggregateGa4_(start, end);

  if (aggregated.length === 0) {
    SpreadsheetApp.getUi().alert('対象となる記事URLのデータが見つかりませんでした。');
    return;
  }

  // 3) ページ一覧シートを更新
  upsertPageList_(aggregated);

  // 4) 明細シート生成（templateを複製して作成）
  const newSheet = buildMonthlySheet_(ss, ym, earnings, aggregated);

  // 5) Slack へレポート送信
  if (CONFIG.SLACK_WEBHOOK_URL && !CONFIG.SLACK_WEBHOOK_URL.includes('YOUR/WEBHOOK/URL')) {
    sendSlackReport_(ss, newSheet, ym);
  }
}

/** 毎月10日 9:00 に実行するトリガーを作成 */
function createMonthlyTrigger() {
  const found = ScriptApp.getProjectTriggers().some(t => t.getHandlerFunction() === 'runMonthly');
  if (!found) {
    ScriptApp.newTrigger('runMonthly').timeBased().onMonthDay(10).atHour(9).create();
  }
  SpreadsheetApp.getUi().alert('毎月10日9:00に実行するトリガーを設定しました。');
}

/* ========= AdSense（前月売上合計） ================== */
function fetchAdsenseEarnings_(start, end, domain) {
  const account = getAdsenseAccountName_();
  const s = toParts_(start);
  const e = toParts_(end);

  const tryOnce = (dimensionName) => {
    const params = {
      dateRange: 'CUSTOM',
      'startDate.year':  s.y,
      'startDate.month': s.m,
      'startDate.day':   s.d,
      'endDate.year':    e.y,
      'endDate.month':   e.m,
      'endDate.day':     e.d,
      metrics: ['ESTIMATED_EARNINGS'],
      dimensions: [dimensionName],
      filters: [`${dimensionName}==${domain}`]
    };
    const res = AdSense.Accounts.Reports.generate(account, params);
    if (res && res.footer && res.footer.totals && res.footer.totals.length) {
      return parseFloat(res.footer.totals[0].value || 0) || 0;
    }
    if (res && res.rows && res.rows.length && res.rows[0].cells && res.rows[0].cells.length) {
      return parseFloat(res.rows[0].cells[0].value || 0) || 0;
    }
    return 0;
  };

  try {
    let val = tryOnce('OWNED_SITE_DOMAIN_NAME');
    if (val !== 0) return val;
    return tryOnce('DOMAIN_NAME');
  } catch (err) {
    const fallbackParams = {
      dateRange: 'CUSTOM',
      'startDate.year':  s.y,
      'startDate.month': s.m,
      'startDate.day':   s.d,
      'endDate.year':    e.y,
      'endDate.month':   e.m,
      'endDate.day':     e.d,
      metrics: ['ESTIMATED_EARNINGS'],
      dimensions: ['OWNED_SITE_DOMAIN_NAME']
    };
    const res = AdSense.Accounts.Reports.generate(account, fallbackParams);
    const rows = (res && res.rows) ? res.rows : [];
    for (const r of rows) {
      const cells = r.cells || [];
      const dimVal = cells[0] && cells[0].value;
      const metricVal = cells[1] && cells[1].value;
      if (dimVal === domain) return parseFloat(metricVal || 0) || 0;
    }
    return 0; 
  }
}

function toParts_(ymd) {
  const [y, m, d] = ymd.split('-').map(n => parseInt(n, 10));
  return { y, m, d };
}

function getAdsenseAccountName_() {
  if (CONFIG.ADSENSE_ACCOUNT_NAME) return CONFIG.ADSENSE_ACCOUNT_NAME;
  const resp = AdSense.Accounts.list({pageSize: 50});
  if (!resp.accounts || resp.accounts.length === 0) {
    throw new Error('AdSenseアカウントが見つかりません。権限とAPI有効化を確認してください。');
  }
  return resp.accounts[0].name;
}

/* ========= GA4取得 ＆ URL判定 ＆ 集計 ==================== */
function fetchAndAggregateGa4_(start, end) {
  const propertyName = `properties/${CONFIG.GA4_PROPERTY_ID}`;
  const req = {
    dateRanges: [{ startDate: start, endDate: end }],
    dimensions: [{ name: 'pageLocation' }, { name: 'pageTitle' }],
    metrics: [{ name: 'screenPageViews' }],
    limit: 100000
  };

  let rows = [];
  try {
    const res = AnalyticsData.Properties.runReport(req, propertyName);
    rows = res.rows || [];
  } catch (e) {
    throw new Error('GA4のデータ取得に失敗しました。\n' + e);
  }

  const map = new Map();

  for (let i = 0; i < rows.length; i++) {
    const rawUrl = rows[i].dimensionValues[0].value || '';
    const title = rows[i].dimensionValues[1].value || '';
    const views = parseInt(rows[i].metricValues[0].value || '0', 10);

    if (!rawUrl) continue;

    // 1. 正規表現による記事URL判定
    const match = rawUrl.match(/\/([^\/]+)\/(\d+)(?:\/)?(?:[?#].*)?$/);
    if (!match) continue;

    const category = match[1];
    if (category.toLowerCase() === 'page') continue;

    // 2. URL正規化
    const canonical = rawUrl
      .replace(/^https?:\/\//i, 'https://')
      .replace(/(https:\/\/)(?:www\.)?/i, '$1')
      .replace(/[?#].*$/, '')
      .replace(/\/+$/, '');

    // 3. マップに集計
    const prev = map.get(canonical);
    if (prev) {
      prev.views += views;
      if (views > prev.maxViews) {
        prev.maxViews = views;
        if (title) prev.title = title;
      }
    } else {
      map.set(canonical, { title: title, views: views, maxViews: views });
    }
  }

  const out = Array.from(map.entries()).map(([u, o]) => [u, o.title, o.views]);
  out.sort((a, b) => b[2] - a[2]);
  return out;
}

/* ========= ページ一覧シートの更新 ==================== */
function upsertPageList_(aggregated) {
  const ss = SpreadsheetApp.getActive();
  const name = CONFIG.PAGE_LIST_SHEET_NAME;
  let sh = ss.getSheetByName(name);
  if (!sh) {
    sh = ss.insertSheet(name);
    sh.getRange(1,1).setValue('★ページ一覧');
    sh.getRange(3,1,1,3).setValues([['ページ','タイトル','執筆者']]).setFontWeight('bold');
  }

  const last = sh.getLastRow();
  let existingVals = [];
  const existingMap = new Map();

  if (last >= 4) {
    existingVals = sh.getRange(4, 1, last - 3, 3).getValues();
    for (let i = 0; i < existingVals.length; i++) {
      let url = existingVals[i][0];
      if (!url) continue;

      let cu = url.replace(/^https?:\/\//i, 'https://')
                  .replace(/(https:\/\/)(?:www\.)?/i, '$1')
                  .replace(/[?#].*$/, '')
                  .replace(/\/+$/, '');
      
      existingVals[i][0] = cu;
      existingMap.set(cu, { index: i, title: existingVals[i][1] || '', author: existingVals[i][2] || '' });
    }
  }

  const adds = [];
  let hasMissingAuthor = false;

  for (let i = 0; i < aggregated.length; i++) {
    const [cu, title] = aggregated[i];
    const e = existingMap.get(cu);
    
    if (!e) {
      adds.push([cu, title || '', '']);
      existingMap.set(cu, { index: -1, title: title || '', author: '' });
      hasMissingAuthor = true;
    } else {
      const shouldOverwrite = CONFIG.OVERWRITE_PAGE_LIST_TITLES 
        ? (title && title !== e.title) 
        : (!e.title && title);
        
      if (shouldOverwrite) {
        existingVals[e.index][1] = title;
        e.title = title;
      }

      if (!e.author) {
        hasMissingAuthor = true;
      }
    }
  }

  if (hasMissingAuthor) {
    const authorMap = fetchWpAuthorsSingleCall_();

    existingMap.forEach((item, url) => {
      if (!item.author) {
        const fetchedAuthor = authorMap[url];
        if (fetchedAuthor) {
          item.author = fetchedAuthor;
          if (item.index >= 0) {
            existingVals[item.index][2] = fetchedAuthor;
          } else {
            const addRow = adds.find(r => r[0] === url);
            if (addRow) addRow[2] = fetchedAuthor;
          }
        }
      }
    });
  }

  if (last >= 4) {
    sh.getRange(4, 1, existingVals.length, 3).setValues(existingVals);
  }
  
  if (adds.length > 0) {
    const nextRow = last >= 4 ? last + 1 : 4;
    sh.getRange(nextRow, 1, adds.length, 3).setValues(adds);
  }

  sh.setFrozenRows(3);
}

/** WordPress REST API から執筆者名を取得 */
function fetchWpAuthorsSingleCall_() {
  const resultMap = {};
  const apiUrl = `https://${CONFIG.SITE_DOMAIN}/wp-json/wp/v2/posts?per_page=100&_embed=author`;

  try {
    const response = UrlFetchApp.fetch(apiUrl, {
      muteHttpExceptions: true,
      headers: { 'User-Agent': 'Mozilla/5.0' }
    });

    if (response.getResponseCode() === 200) {
      const posts = JSON.parse(response.getContentText());
      for (let i = 0; i < posts.length; i++) {
        const post = posts[i];
        const rawUrl = post.link || '';
        
        // 著者名の取得
        let authorName = '';
        if (post._embedded && post._embedded.author && post._embedded.author.length > 0) {
          authorName = post._embedded.author[0].name || post._embedded.author[0].slug || '';
        }

        if (rawUrl && authorName) {
          // URL正規化
          const cu = rawUrl
            .replace(/^https?:\/\//i, 'https://')
            .replace(/(https:\/\/)(?:www\.)?/i, '$1')
            .replace(/[?#].*$/, '')
            .replace(/\/+$/, '');

          resultMap[cu] = authorName;
        }
      }
    }
  } catch (e) {
    console.error('WordPress REST API 取得エラー: ' + e);
  }

  return resultMap;
}

/* ========= 明細シートの作成（templateコピー ＆ 2シート目に移動） ========================= */
function buildMonthlySheet_(ss, ym, earnings, details) {
  const ex = ss.getSheetByName(ym);
  if (ex) ss.deleteSheet(ex);

  const templateSh = ss.getSheetByName(CONFIG.TEMPLATE_SHEET_NAME);
  if (!templateSh) {
    throw new Error(`テンプレートシート「${CONFIG.TEMPLATE_SHEET_NAME}」が見つかりません。事前に作成してください。`);
  }

  const sh = templateSh.copyTo(ss);
  sh.setName(ym);

  ss.setActiveSheet(sh);
  ss.moveActiveSheet(2);

  const startRow = CONFIG.DETAIL_START_ROW || 12;

  sh.getRange('B4').setValue(earnings);

  if (details.length) {
    sh.getRange(startRow, 1, details.length, 3).setValues(details);

    const formulas = [];
    for (let i = 0; i < details.length; i++) {
      const currentRow = startRow + i;
      formulas.push([
        `=IF(A${currentRow}="","",IFERROR(VLOOKUP(A${currentRow},'${CONFIG.PAGE_LIST_SHEET_NAME}'!A:C,3,FALSE),""))`
      ]);
    }
    sh.getRange(startRow, 4, details.length, 1).setFormulas(formulas);
  }
  
  SpreadsheetApp.flush();

  return sh;
}

/* ========= Slack レポート送信 ========================= */
function sendSlackReport_(ss, sh, ym) {
  const [year, month] = ym.split('-');

  SpreadsheetApp.flush();

  const displayVals = sh.getRange('A1:G14').getDisplayValues();

  const totalEarnings = displayVals[3][1] || '0';
  const tatsuEarnings = displayVals[3][6] || '0';
  const daiEarnings   = displayVals[4][6] || '0';

  const rankingIcons = [':crown:', ':two:', ':three:'];
  let articleLines = [];

  for (let i = 0; i < 3; i++) {
    const rowIdx = 11 + i;
    const url = displayVals[rowIdx][0];
    const title = displayVals[rowIdx][1];
    const pv = displayVals[rowIdx][2];
    const author = displayVals[rowIdx][3];

    if (url) {
      const icon = rankingIcons[i];
      const authorText = author ? author : '未設定';
      articleLines.push(`${icon} <${url}|${title}> by *${authorText}* (PV *${pv}*)`);
    }
  }

  const spreadsheetUrl = ss.getUrl();

  const message = `kalsarikannint の ${year} 年 ${month} 月 のPV・収益レポートをお届けします。
━━━━━━━━━━
 *読まれた記事* :rocket: 
━━━━━━━━━━
${articleLines.join('\n')}

━━━━━━
 *収益* :tada:
━━━━━━
見込み収益: *${totalEarnings}* 円

このレポートの詳細は <${spreadsheetUrl}|スプレッドシート> で確認できます。`;

  const payload = JSON.stringify({ text: message });
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: payload
  };

  UrlFetchApp.fetch(CONFIG.SLACK_WEBHOOK_URL, options);
}

/* ========= 日付ユーティリティ ======================= */
function getPrevMonthRange_() {
  const today = new Date();
  const firstThis = new Date(today.getFullYear(), today.getMonth(), 1);
  const lastPrev = new Date(firstThis - 1);
  const firstPrev = new Date(lastPrev.getFullYear(), lastPrev.getMonth(), 1);
  const pad = n => ('0' + n).slice(-2);
  const start = `${firstPrev.getFullYear()}-${pad(firstPrev.getMonth()+1)}-01`;
  const end = `${lastPrev.getFullYear()}-${pad(lastPrev.getMonth()+1)}-${pad(lastPrev.getDate())}`;
  const ym = `${firstPrev.getFullYear()}-${pad(firstPrev.getMonth()+1)}`;
  return { start, end, ym };
}

仕組みはできた。あとは書くだけだ….

Author dai

地方に住みながら、フルリモート情シスおじさんしてます。
フロントエンド開発とkintoneアプリのカスタマイズやプラグイン開発が得意分野です。
好きなビールは一番搾り。

ミニカーをかっこよく飾りたい

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 僕は車が好きで、過去には2代連続でスカイラインに乗っていたりしました。 ただ、いまは駐車場の都合や経済面を考えると、自分の車を所有するのはなかなか現実的じゃないなーと諦めてます。 ということで、乗れないなら、せめて乗りたい車のミニカーを集めて、かっこよく飾って、眺めてニヤニヤしたい。今回はそのために、デスク横に趣味棚を作りたい、という話です。 いまのミニカー置き場が雑すぎる 現状、ミニカーはいくつか持っています。 ただ、飾っているというより、机にポンって置いているだけです。これはこれで見える場所にあるので悪くはないのですが、どうしても「コレクション感」はありません。 せっかくなので、ただ置く場所ではなく、ちゃんと飾る場所を作りたいです。 アクリルケースに入れることを考える スカイラインクーペの方は元々アクリルのカバーに覆われていますが、トミカプレミアムのスープラは車体がむき出しです。このままではホコリも気になるし、掃除も少々手間なので、まず考えてい
desk ガジェット車

ブログ下書きのしくみ(改)を作りました

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 以前書いた記事 で紹介したやり方を発展させて、ブログの下書きを作るしくみを作りました。 中身としては、Notionにある情報を元にして、Pythonでフローを組み、その中からAI部分としてCodexをCLIで実行させる形です。これまではまだ自分でやらなきゃいけない作業が多かったですが、それをより自動化したしくみ、という位置づけです。 Notionを起点にした下書きフロー 今回のしくみでも、Notionをフローの中心に据えています。 以前と変わらず、ブログ記事の材料になるメモはNotion側にあり、それを元に下書きを作る流れです。メモをそのまま記事にするのではなく、記事として読める本文に変換する部分をAIに任せる形にしています。 全体としては、ざっくり次のような役割分担です。 Notion: 記事の元になるタスクやメモを置く Python: 下書き生成の処理フローを書く Codex CLI: AIでMarkdown本文を生成する ルール: 記事の書き
AI AIブログ

【レビュー】UGREEN エルゴノミクスマウス

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 僕はPCを使う時は断然トラックパッド派です。特にメインPCはMacbook Proですが、トラックパッドの使い心地でMacを選んでるまであるくらい（もちろん他にも理由あるけど）。 そんな僕ですが、この度マウスを購入しました。 出典：Amazon 今回は、トラックパッド好きの僕がマウスを導入した経緯と、実際に使ってみた感想を正直に書いていきます。 僕の普段の作業環境 まず前提として、僕のトラックパッド愛 作業環境を説明しておきます。 家で作業する際は、MacBookの内蔵キーボードの上に Huntsman Mini という60%キーボードを乗せる、いわゆる「尊師スタイル」で使っていて、ポインタ操作は基本的にMacBookのトラックパッドです。 「キーボードの上にキーボード？」と思われるかもしれませんが、これが意外と快適でして。Huntsman Miniの軽快な打鍵感を楽しみつつ、トラックパッドだけはMacのものをそのまま使える、という構成です。 Macのトラ
review MacWindowsガジェット作業環境

関連記事

Windowsバッチファイルの作成Tips｜タイトル設定と管理者権限の自動昇格

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 最近、社内の定型業務をWindowsバッチファイル（.bat）で自動化する機会があったんですが、使ってみると「なんか微妙に使いにくいな」と感じる場面がちょこちょこ出てきました。 少し工夫するだけで運用時のストレスがぐっと減ったので、今回はそのTipsを2つご紹介しようと思います。 そもそも、どんな「使いにくさ」が起きていたのか まず前提として、僕が直面していた状況はこんな感じでした。 複数のバッチファイルを同時に動かしていたので、タスクバーが「コマンドプロンプト」だらけになっていた 管理者権限が必要な処理を含むバッチを、うっかりダブルクリックで起動してエラーになってしまっていた どちらも「仕組みを理解していれば防げるミス」なんですが、毎回気をつけるのも面倒ですよね。なので、バッチファイルの書き方自体で対処できないか考えてみました。 1. 実行中のバッチを識別しやすくする「title」コマンド 困っていたこと 複数のバッチファイルを並列で実行してい
develop Windows

実務で身についたAIとコーディングするコツ

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 仕事を楽にするために、プログラムによる自動化・効率化を日々実践しているのですが、最近ではもっぱらAIを使ったコーディングが中心になっています。想像以上に実用的で、正直なところ「もう戻れないかも」と感じているくらいです。 以前は、ブログ記事を書くときのAIの使い方をまとめましたが、今回は僕が本業で培った経験をお伝えできればと思います。 https://kalsari.net/tool/128 本記事では、AIツールを使った実際のコーディング経験から得た知見や、効果的な活用方法について共有します。「AIでプログラミングができるらしいけど、実際どうなの？」と気になっている方の参考になれば幸いです。 使っているAIツール 現在、メインで使用しているのはChatGPTです。特に理由があるわけではなく、最初に触れたのがこれだったからという、わりと適当な理由だったりします。 GitHub CopilotやCursorなどの有料ツールも試してみたんですが、現時点では自分の用途にお
develop AIコーディング

祝！AdSense通過！広告収益分配ツールを作ってみた

こんにちは。Kalsarikannintのフロントエンド担当・daiです。 先日、Google AdSenseの審査に通過しました。やっと広告を貼り始めたので、今回はその裏側と、2人で運営しているブログだからこその「利益分配ツール」についてお話しします。 広告の掲載位置 PCでブログを見ていただいている場合、記事右側の余白部分に広告を表示しています。 掲載位置についてはAfu.とも話し合って、収益よりも、なるべく邪魔にならず、コンテンツに集中できるような場所を選んで設定したつもりです。 また、スマートフォンでご覧の場合は、記事の末尾に1つだけ掲載しています。 もちろん、掲載数が多かったり、記事内に差し込むように配置するほうが広告収益が高くなりやすいですが、そういうサイトは自分たちも好きではないので、このような場所に落ち着きました。 収益の分配のために... 共同運営だからこそ、「どれだけのPVがあるか」に応じて利益を公平に配る仕組みが必要です。そこで今回は以下の手順で自動化してみることにしました。 Google Anal
develop GASJavaScript