>>オーリーズが分かる資料セット（サービス資料・事例集）をダウンロードする

前回の記事

• 前回の記事

• 猫社員・まる美について

本題に入る前に

結論としてはこんな機能ができました！

週１回、Slackに当サイトに掲載されている記事のアクセスランキングを送ってくれる機能です。

ここからは経緯になりますので、実装から知りたい人は実装案まで読み飛ばしてください。

課題や背景（前回からの続き）

実装したけど、、、

前回のNotionAPIによるプロパティ変更検知によって記事の執筆完了を検知し、Slackに通知を飛ばせるようになりました。
その後、似たような仕組みで社外向け記事の公開も検知できるようにしたのですが、まる美botに話させる内容はこれだけでは不十分だなと感じていました。

💡 【補足】
オーリーズでは社内ナレッジ共有のために、各メンバーがNotionに業務で得た知見や情報に関する記事を執筆しています。
そして、その中の一部を外部公開しており、当サイトに掲載されるようになっています。
以前はKibelaをナレッジ共有に使っていましたが、当サイトの公開に合わせて、連携のしやすいNotionに移行したという経緯があります。

まだ機能が足りていない

「以前使っていたKibelaと比較して新規公開記事を検知しづらいよね」という課題を解消するため（あと、まる美とのふれあいのため）に実装したまる美botですが、実はKibelaでは記事に対するコメントに関しての通知機能もありました。

ただ同様の機能を実装しようとしても、Notionではコメント機能が使いづらそうなうえ、新着コメントをNotionAPIで抽出することも難しそうだということがわかりました。

フィードバックの重要性

ということで運用面でも技術面でも早々に暗礁に乗り上げてしまったコメント通知機能ですが、そもそも「なぜ」記事を書いたことに対するコメントを通知したかったのでしょうか？

結論から言うと、僕は「コメントをもらうということ」は記事投稿に対する報酬のひとつであると考えています。記事に対して「参考になりました」とか「役に立ちました」って言われるのって単純に嬉しいですし、モチベーションになりますよね？

オーリーズでは外部公開のネタ元になるように、各チームにナレッジ記事投稿の目標があります。（1人当たり数か月に1本程度なので少ないですが）

しかし、本来的には目標に関係なく、個人個人が良い記事を書くことで適切に報酬を得られて自発的に書きたくなる状態が理想的ではないかと思います。

そのような状態を作る枠組み作りを目標としたときに、フィードバックの種類は必ずしもコメントの通知にこだわる必要はありません。記事の質に対する適切なフィードバックを与えられれば良いのです。

そこで僕は「アクセスランキング発表システム」をつくることで、コメント通知のフィードバック的側面の代替とすることにしました。

これは、記事が社外のユーザーにどれだけ読まれたかということを可視化して発表するシステムで、自分の記事がどれだけ貢献したがが分かるようになります。また貢献度の高い記事を表彰することで外部に公開されるような記事を書きたいという皆のモチベーション向上にも繋げられるのではないかと考えました。

実装案

実装の流れ

実行したいのはアクセスランキングの発表ですが、それだけではもったいないのでランキング外の記事の分析ができる基盤があると便利です。

アクセス数の把握はGoogle Analytics 4を使用しBigQueryへのエクスポート機能で元となるデータを取得します。そしてBigQueryと相性の良いData PotalをBIとして利用しましょう。

システムの概要が以下になります。

それぞれのタスクで行っている作業の概要は以下となります。

①：サイトのアクセスイベントデータをBigQueryExport機能によってBigQueryに送信する。

②：イベントデータのままでは使いづらいので整形をする。

③：DataPotalからBigQueryを参照するよう設定する。

④：DataPotalのメール配信機能を用いてGmailにPDFを送信する。

⑤⑥：②で整形したデータをSQLで必要な形にして取得する。

⑦⑧：④で送信したPDFを取得する。

⑨：Slackにアクセスイベントランキングを送信する。

なお、処理についてですが、①～④まではGASではない箇所です。スクリプトの実行などはそれぞれのツールのスケジューリング機能を作って実行しています。

⑤～⑨まではGASでトリガーをスケジュールして実行しています。

これで完全自動化を達成しています。

ストレスなく見れるようにするための工夫

Slackで送信する情報についてはとにかく見る人にストレスがかからないことを意識し、BIの情報もPDFで送ることで遷移しなくても色々な情報が見れる作りにしました。（Slack上でPDFは開けるので）

また、発表のリアルタイム感を出すために随所で「タメ」をつくって発表する作りにしました。

ところどころでまる美botが「教えてやるにゃ」とか「まとめておいてやったからみるにゃ」みたいにやたらと高圧的な話し方なのは、僕のまる美のイメージによるもので特に意味はないです。

💡 ここだけの話
まる美の口調についてはあまり意味がないのですが、「まる美が話者である」ことには非常に意義があると思っています。例えばこれが「社内通知bot」みたいなものだったら味気ないし、「誠愛さん（副社長）」からの通知だと業務色が強すぎると思います。
業務にあまり関係がないけど全従業員から親しみがある「まる美」の存在はこういったときに非常にありがたい存在です。

実際にSlackに送信された内容

以下が実際のSlackへの送信になります。

具体的な実装

それぞれの具体的な実装についてみていきます。

早朝に実行するスクリプト群

---

①：サイトのアクセスイベントデータをBigQueryExport機能によってBigQueryに送信する。

特筆すべきポイントはないので設定方法については割愛します。頻度は毎日とストリーミングの双方にチェックを入れいています。これでBigQuery側にユーザーアクセスの都度データがたまることになります。

テーブルの内容については以下を参照してください。

[GA4] BigQuery Export スキーマ

---

②：データの整形

データの整形についてはBigQueryのスケジュールされたクエリの機能を使います。

データの加工については、以下のように必要情報を抽出したうえで著者名などを付加する形にしています。最終的にはユニークなユーザーID数(以下UU数)をカウントしたいのですが、あえてこの段階ではUU数をカウントして日別のデータに集約することはしていません。※後で説明します。

データの更新に関してはデータを毎回全量delete＆insertしているとデータの読み込み量が経過日数に比例してしまうので二週間前のデータから削除するスクリプト、同期間のデータを挿入するスクリプトの2本をワンセットで実行することで更新しています。

• 削除スクリプト

DELETE
  FROM `作表用のテーブル`
  where
  date >= DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 14 DAY)

• 挿入スクリプト

SELECT
  parse_date('%Y%m%d',A.event_date) AS DATE
  ,A.event_name
  ,A.user_pseudo_id
  ,B.value.string_value AS page_location
  ,traffic_source.name,traffic_source.medium,traffic_source.source
  ,CASE WHEN B.value.string_value = "https://learning.allis-co.com/" THEN "トップ" ELSE Master.title END as title
  ,Master.author,Master.image,Master.share_date
FROM (SELECT * 
        FROM `GA4Export先.events_*`
        WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 14 DAY)) 
            AND FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 1 DAY))
      UNION ALL SELECT * 
        FROM `GA4Export先.events_intraday_*`
        WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 14 DAY)) 
            AND FORMAT_DATE("%Y%m%d",DATE_SUB(CURRENT_DATE('Asia/Tokyo'),INTERVAL 1 DAY))
      )A
LEFT JOIN
  UNNEST(A.event_Params) B
ON
  B.key = 'page_location'
LEFT JOIN `NotionAPIで取得した記事一覧のShreadSheet` Master
  ON Master.id = replace(B.value.string_value,"https://learning.allis-co.com/","")
GROUP BY DATE,A.event_name,A.user_pseudo_id,page_location
  ,traffic_source.name,traffic_source.medium,traffic_source.source,Master.title,Master.author,Master.image,Master.share_date
order by date,A.user_pseudo_id

注意点

• CURRENT_DATE()はタイムゾーンを指定しないと自動実行ではUTCになってしまうようです。早朝に更新するのでタイムゾーンの指定はしておいた方が良いです。
• 早朝に実行するためintradayのテーブルに前日のデータが残ってしまっていることが多いです。最初にUnionして一つのデータとして扱っておきます。

---

③：DataPotalからBigQueryを参照するよう設定する。

DataPotalの具体的な作表に関しては今回は説明しません。

PDFで送ることを考慮して以下のようなシンプルな作表にしています。

UU数を集計するために「APPROX_COUNT_DISTINCT(user_pseudo_id)」を指標としています。

💡 なぜBigQueryで集計しないのか
UU数の集計をBigQueryで日別で行った場合、Aというユーザーが4/1,4/2の二日間アクセスした場合UU数は2となります。
一方で、DataPotalでAPPROX_COUNT_DISTINCTを利用した場合は期間内でのUU数がカウントされるので、指定期間に4/1,4/2の両方が含まれていればUU数は1となります。
指定期間に合わせた集計ができるところはDataPotalの関数の強みですね。

---

④：DataPotalのメール配信機能を用いてGmailにPDFを送信する。

以下から設定

11時ごろに実行するスクリプト

---

⑤⑥：②で整形したデータをSQLで必要な形にして取得する。

よく見られた記事TOP3を取得するSQLを実行します。トップページのビューが多いのは当然なので除外しておきます。また、今回はUU数をSQLで計算していますが、日ごとではなく指定期間内でのUU数を数えているので同期間のDataPotalの数値と一致するような仕組みとなっています。

以下のスクリプトでresultに記事情報が代入されます。（BigQueryのクライアントライブラリの追加も必要です。）

function mva() {
  var queryMVA = `#standardSQL
  SELECT replace(page_location,"https://learning.allis-co.com/",""),title,author,image,COUNT(DISTINCT user_pseudo_id)
  FROM \`集計テーブル\`
  WHERE DATE >= DATE_SUB(CURRENT_DATE(),INTERVAL 7 DAY) AND event_name = "page_view"
  AND title <> "トップ"
  GROUP BY page_location,title,author,image
  ORDER BY COUNT(DISTINCT user_pseudo_id) DESC
  LIMIT 3
	`
	let queryRequest = BigQuery.newQueryRequest();
  queryRequest.query = queryMVA;
  const result = BigQuery.Jobs.query(queryRequest, "プロジェクト名");
}

const result = BigQuery.Jobs.query(queryRequest, “プロジェクト名”);
の後からは、まる美を話させるために以下のスクリプトを追加します。

（先程のシステムの概要図ではわかりやすさのため⑨でSlackへ送信していましたが、実際はデータを取得しつつSlackへの送信も並行して行っています。）

callSlackApiMessage("<!here>");
callSlackApiMessage("今週もまるみがオーリーズの公開記事の中から先週最も読まれた記事 *「MVA(Most Viewed Article)」* を勝手に発表するにゃ:tada:");
Utilities.sleep(10000);
callSlackApiMessage("あと、新着記事の中で今週最も読まれた記事 *「MVNA(Most Viewed New Article)」* も教えてやるにゃ:marumi-kao:");
Utilities.sleep(5000);
callSlackApiMessage("「新着記事」は公開されて30日以内の記事ってことにゃ:bulb:");
Utilities.sleep(10000);
callSlackApiMessage("それではさっそく発表するにゃ\nまずは")
Utilities.sleep(1000);
for (let i = 0; i < result.rows.length; i++) {
  var resultRow = result.rows[result.rows.length-1-i];
  var id = resultRow.f[0].v;
  var title = resultRow.f[1].v;
  var author = resultRow.f[2].v;
  var image = resultRow.f[3].v;
  var user = resultRow.f[4].v;

  if(result.rows.length-i == 1){
    callSlackApiMessage("------------------------------------------------------------------")
    callSlackApiMessage("栄えある第"+ (result.rows.length-i) + "位の発表にゃ")
    Utilities.sleep(2000);
    callSlackApiMessage("今週のMVAは・・・・・・")
    Utilities.sleep(2000);
    callSlackApiMessage("今週のMVAは・・・・・・")
    Utilities.sleep(2000);
    callSlackApiMessage("今週のMVAは・・・・・・")
    Utilities.sleep(2000);
    var imageFile = UrlFetchApp.fetch(getNotionBlockData(id).results[0].image.file.url).getBlob();
    callSlackApiFileSend("画像", imageFile,"auto");
    Utilities.sleep(3000)
    callSlackApiMessage(" *「"+ author + "」* さんの<https://www.notion.so/"+ id + "/|"+ title + "> にゃ")
    callSlackApiMessage("ユニークユーザー数は「"+ user + "」にゃ")
    Utilities.sleep(1000);
    callSlackApiMessage("盛大な拍手をおくるにゃ:clap:")
  }
  else{
    callSlackApiMessage("------------------------------------------------------------------")
    callSlackApiMessage("第"+ (result.rows.length-i) + "位の発表にゃ")
    Utilities.sleep(2000);
    callSlackApiMessage("第"+ (result.rows.length-i) + "位は *「"+ author + "」* さんの\n<https://www.notion.so/"+ id + "/|"+ title + "> にゃ")
    Utilities.sleep(2000);
    callSlackApiMessage("ユニークユーザー数は「"+ user + "」にゃ")
    Utilities.sleep(2000);
    callSlackApiMessage("続いて")
  }
}

Utility.sleepはまる美をゆっくりしゃべらせるために利用しています。

• 利用しているメソッド

//Slack通知用のメソッド
function callSlackApiMessage(message){
  const response = UrlFetchApp.fetch(
    `https://www.slack.com/api/chat.postMessage`,
    {
      method: "post",
      contentType: "application/x-www-form-urlencoded",
      headers: { "Authorization": "Bearer xoxb-xxxxxxxxxxxxxxxxxxxxxxxxx" },
      payload: {
        channel: SLACK_CHANNEL,
        text: message
      }
    }
  );
/*Slackへファイルをアップロードするメソッド
  画像 ⇒ fileType = 'auto'
  PDF ⇒ fileType = 'pdf'
*/
function callSlackApiFileSend(title, sendfile,fileType) {
  UrlFetchApp.fetch("https://slack.com/api/files.upload", {
    method: "post",
    payload: {
      channels:SLACK_CHANNEL,
      file: sendfile,
      title: title,
      filename: title,
      filetype: fileType,
      token: "xoxb-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
    }
  });
}
//notionのブロックデータを取ってくるメソッド
function getNotionBlockData(page_id){
  var access_token = "secret_xxxxxxxxxxxxxxxxxxxxxxxxxxxx"
  var headers = {
    "Content-Type": "application/json",
    "Authorization": "Bearer " + access_token,
    'Notion-Version': '2022-02-22',
  };  
  var options = {
     "method" : "get",
     "headers" : headers, 
  }
  var url = 'https://api.notion.com/v1/blocks/' + page_id +'/children';
  var response = UrlFetchApp.fetch(url,options);
  notion_data = JSON.parse(response);
  return notion_data;
}

新着記事のランキングも似たようなスクリプトで実装しました。

---

⑦⑧：④で送信したPDFを取得する。

こちらの実装はほぼこちらのサイトのままです。

結果＆反応

発表の仕方も好評で、自動で情報が取得できるようになっただけでなく、まる美botの役割が増えたことで、リモート環境でもまる美の存在感を感じることができるようになり、少しだけ猫がいるオフィスの雰囲気を取り戻すことができました。

今回は、前回の記事の続きで、社内事情と合わせながら記事のアクセス数をGA4から集計してSlackに通知を飛ばす方法を紹介しました。

当サイトでは運用型広告に関する記事を中心に、このようなデータ連携に関する記事も掲載していますので、ぜひご参考にいただければと思います。