猫企業の現在 ~まる美Slackbot(記事のアクセス数をGA4から集計して発表する編 in GAS)~

本題に入る前に

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

週1回、Slackに当サイトに掲載されている記事のアクセスランキングを送ってくれる機能です。
ここからは経緯になりますので、実装から知りたい人は実装案まで読み飛ばしてください。
GIFが重いので表示されるまで少しお待ちください
GIFが重いので表示されるまで少しお待ちください

課題や背景(前回からの続き)

実装したけど、、、

前回のNotionAPIによるプロパティ変更検知によって記事の執筆完了を検知し、Slackに通知を飛ばせるようになりました。 その後、似たような仕組みで社外向け記事の公開も検知できるようにしたのですが、まる美botに話させる内容はこれだけでは不十分だなと感じていました。
notion image
この通知だけではまだ不十分?
この通知だけではまだ不十分?
💡
【補足】 オーリーズでは社内ナレッジ共有のために、各メンバーがNotionに業務で得た知見や情報に関する記事を執筆しています。 そして、その中の一部を外部公開しており、当サイトに掲載されるようになっています。 以前はKibelaをナレッジ共有に使っていましたが、当サイトの公開に合わせて、連携のしやすいNotionに移行したという経緯があります。

まだ機能が足りていない

「以前使っていたKibelaと比較して新規公開記事を検知しづらいよね」という課題を解消するため(あと、まる美とのふれあいのため)に実装したまる美botですが、実はKibelaでは記事に対するコメントに関しての通知機能もありました。
わろてるのは執行役員です
わろてるのは執行役員です
ただ同様の機能を実装しようとしても、Notionではコメント機能が使いづらそうなうえ、新着コメントをNotionAPIで抽出することも難しそうだということがわかりました。

フィードバックの重要性

ということで運用面でも技術面でも早々に暗礁に乗り上げてしまったコメント通知機能ですが、そもそも「なぜ」記事を書いたことに対するコメントを通知したかったのでしょうか?
結論から言うと、僕は「コメントをもらうということ」は記事投稿に対する報酬のひとつであると考えています。記事に対して「参考になりました」とか「役に立ちました」って言われるのって単純に嬉しいですし、モチベーションになりますよね?
オーリーズでは外部公開のネタ元になるように、各チームにナレッジ記事投稿の目標があります。(1人当たり数か月に1本程度なので少ないですが)
しかし、本来的には目標に関係なく、個人個人が良い記事を書くことで適切に報酬を得られて自発的に書きたくなる状態が理想的ではないかと思います。
そのような状態を作る枠組み作りを目標としたときに、フィードバックの種類は必ずしもコメントの通知にこだわる必要はありません。記事の質に対する適切なフィードバックを与えられれば良いのです。
そこで僕は「アクセスランキング発表システム」をつくることで、コメント通知のフィードバック的側面の代替とすることにしました。
これは、記事が社外のユーザーにどれだけ読まれたかということを可視化して発表するシステムで、自分の記事がどれだけ貢献したがが分かるようになります。また貢献度の高い記事を表彰することで外部に公開されるような記事を書きたいという皆のモチベーション向上にも繋げられるのではないかと考えました。

実装案

実装の流れ

実行したいのはアクセスランキングの発表ですが、それだけではもったいないのでランキング外の記事の分析ができる基盤があると便利です。
アクセス数の把握はGoogle Analytics 4を使用しBigQueryへのエクスポート機能で元となるデータを取得します。そしてBigQueryと相性の良いData PotalをBIとして利用しましょう。
システムの概要が以下になります。
notion image
それぞれのタスクで行っている作業の概要は以下となります。
①:サイトのアクセスイベントデータをBigQueryExport機能によってBigQueryに送信する。
②:イベントデータのままでは使いづらいので整形をする。
③:DataPotalからBigQueryを参照するよう設定する。
④:DataPotalのメール配信機能を用いてGmailにPDFを送信する。
⑤⑥:②で整形したデータをSQLで必要な形にして取得する。
⑦⑧:④で送信したPDFを取得する。
⑨:Slackにアクセスイベントランキングを送信する。
なお、処理についてですが、①~④まではGASではない箇所です。スクリプトの実行などはそれぞれのツールのスケジューリング機能を作って実行しています。
⑤~⑨まではGASでトリガーをスケジュールして実行しています。
これで完全自動化を達成しています。

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

Slackで送信する情報についてはとにかく見る人にストレスがかからないことを意識し、BIの情報もPDFで送ることで遷移しなくても色々な情報が見れる作りにしました。(Slack上でPDFは開けるので)
また、発表のリアルタイム感を出すために随所で「タメ」をつくって発表する作りにしました。
ところどころでまる美botが「教えてやるにゃ」とか「まとめておいてやったからみるにゃ」みたいにやたらと高圧的な話し方なのは、僕のまる美のイメージによるもので特に意味はないです。
💡
ここだけの話
まる美の口調についてはあまり意味がないのですが、「まる美が話者である」ことには非常に意義があると思っています。例えばこれが「社内通知bot」みたいなものだったら味気ないし、「誠愛さん(副社長)」からの通知だと業務色が強すぎると思います。
業務にあまり関係がないけど全従業員から親しみがある「まる美」の存在はこういったときに非常にありがたい存在です。

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

以下が実際のSlackへの送信になります。
アクセスランキング1~3位の発表
アクセスランキング1~3位の発表
新着記事の中で最も読まれた記事の発表
新着記事の中で最も読まれた記事の発表
興味がある人向けの補足情報をPDFで送付
興味がある人向けの補足情報をPDFで送付

具体的な実装

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

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


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

特筆すべきポイントはないので設定方法については割愛します。頻度は毎日とストリーミングの双方にチェックを入れいています。これでBigQuery側にユーザーアクセスの都度データがたまることになります。
GoogleAnalytics側の設定
GoogleAnalytics側の設定
BigQuery側に送信されるデータ。日ごとに分割されたテーブルに格納されており、intradayとなっているのは当日分(時間帯によっては前日分も)のイベントデータとなっています。
BigQuery側に送信されるデータ。日ごとに分割されたテーブルに格納されており、intradayとなっているのは当日分(時間帯によっては前日分も)のイベントデータとなっています。
テーブルの内容については以下を参照してください。

②:データの整形

データの整形についてはBigQueryのスケジュールされたクエリの機能を使います。
notion image
データの加工については、以下のように必要情報を抽出したうえで著者名などを付加する形にしています。最終的にはユニークなユーザーID数(以下UU数)をカウントしたいのですが、あえてこの段階ではUU数をカウントして日別のデータに集約することはしていません。※後で説明します。
notion image
データの更新に関してはデータを毎回全量delete&insertしているとデータの読み込み量が経過日数に比例してしまうので二週間前のデータから削除するスクリプト、同期間のデータを挿入するスクリプトの2本をワンセットで実行することで更新しています。
  • 削除スクリプト
SQL
  • 挿入スクリプト
SQL
注意点
  • 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の関数の強みですね。
notion image

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

以下から設定
notion image

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


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

よく見られた記事TOP3を取得するSQLを実行します。トップページのビューが多いのは当然なので除外しておきます。また、今回はUU数をSQLで計算していますが、日ごとではなく指定期間内でのUU数を数えているので同期間のDataPotalの数値と一致するような仕組みとなっています。
以下のスクリプトでresultに記事情報が代入されます。(BigQueryのクライアントライブラリの追加も必要です。)
JavaScript
const result = BigQuery.Jobs.query(queryRequest, "プロジェクト名");
の後からは、まる美を話させるために以下のスクリプトを追加します。
(先程のシステムの概要図ではわかりやすさのため⑨でSlackへ送信していましたが、実際はデータを取得しつつSlackへの送信も並行して行っています。)
JavaScript
Utility.sleepはまる美をゆっくりしゃべらせるために利用しています。
  • 利用しているメソッド
JavaScript
新着記事のランキングも似たようなスクリプトで実装しました。

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

こちらの実装はほぼこちらのサイトのままです。
Google Apps ScriptでDataportalのレポートを毎日Slackに配信する - notebook
この記事は Google Apps Script Advent Calendar 2021のカレンダー | Advent Calendar 2021 - Qiita 2日目の記事です Dataportalのレポートをメール配信する機能が結構良かったのでGASと合わせてSlackに投稿する というのをやってみた 流れは次のようにする Dataportalのメール配信設定を行う GASで対象メールを特定、添付のPDFファイルをSlackへ送信 いつの間にかレポートのメール配信が可能になっていた(もしかしたら把握してなかっただけかも) タイトルはGASでのメール特定時に「この文字列が含まれているタイトル」というように絞り込みを行うのでこのDataportalからのメールですよというのが分かるような文字列にする 実際に配信スケジュールをセットして配信されたメールを見てみると 設定で指定したページの画像を表示+PDFファイルを添付してくれる かなり鮮明なので良い 画像すべてを送っても良いかなと思ったが今回はPDFのみをSlackに流す メール配信の設定自体は1Dataportalのレポートに対して1スケジュール(設定)のようなので日次でこの設定、週次でこの設定 といった使い方はできない そういう場合分けが必要な場合はDataportalのレポートを別で作る必要がある GASを使ってSlackへ送信する function setSlackToken() { PropertiesService.getScriptProperties().setProperty('SLACK_WEB_API_TOKEN', ''); } function setScheduleTrigger() { ScriptApp.newTrigger('main').timeBased().atHour(11).nearMinute(0).everyDays(1).inTimezone("Asia/Tokyo").create(); } function setProperties() { PropertiesService.getScriptProperties().setProperty('SLACK_CHANNEL', 'metrics'); PropertiesService.getScriptProperties().setProperty('MAIL_SUBJECT_PREFIX', 'BlogDashboardReport(Dataportal)'); } function getMail()

結果&反応

 
notion image
notion image
notion image
notion image
発表の仕方も好評で、自動で情報が取得できるようになっただけでなく、まる美botの役割が増えたことで、リモート環境でもまる美の存在感を感じることができるようになり、少しだけ猫がいるオフィスの雰囲気を取り戻すことができました。
今回は、前回の記事の続きで、社内事情と合わせながら記事のアクセス数をGA4から集計してSlackに通知を飛ばす方法を紹介しました。
当サイトでは運用型広告に関する記事を中心に、このようなデータ連携に関する記事も掲載していますので、ぜひご参考にいただければと思います。

link image
\ SHARE ON /
👥メンバー情報