noteのダッシュボードをPower BIとAzure Functionでグラフ化してみた

AvaKansai

2022年4月5日 17:02

こんにちは、アバナード関西オフィスの寺岡です！
まだアバナードに入社したばかりで、Power BIを始め、Microsoftのクラウド製品について知識がないまま、上司に以下のように言われました。

「アバナード関西noteアカウントのダッシュボードを、Power BIでグラフ化して欲しい」

分からないことだらけでしたが、自分なりに試行錯誤して、拙いながらもなんとか作ることができましたので、せっかくなので初学者向けに記事にまとめてみました。

この記事をおすすめする人

・Power BIの初学者の方
・Azure Functionの初学者の方

参考にした記事

上記の記事を参考にさせて頂きました。ありがとうございます！

作るもの

求められている要件は以下のとおり。

・アバナード関西noteアカウントのダッシュボードをアカウント開設日より現在までの全期間をグラフ化する。
・グラフは月単位のPV数および作成した記事数。PVは累計表示も行う。
・グラフは日単位での自動更新とする。

ちなみに、noteのダッシュボードは「週」「月」「年」「全期間」の4つのパターンで、PV/コメント数/スキ数などを確認することができる機能です。

ある時点での状態は確認できますが、
・PV/コメント数/スキ数がどのように推移しているか
・どのような記事がよく見られているのか。どのような要因がPV/コメント数/スキ数の増減に関わっているのか
などの解析は、たしかにこのままでは難しそうです。

システム構成

今回は、以下のようなシステム構成にしてみました。
１. Azure Function でnoteよりデータを取得
２. Azure SQL Databaseにデータを書き込み
３. Power BIでデータ参照

また、Power BIのグラフは、
・月毎のPV、記事数を縦棒グラフで表現
・累計のPVを折れ線グラフで表現
という構成を想定して作成していきます。

1-1. Azure Functionの作成

まず、Azure FunctionをAzure Potalより作成していきます。

Azure Potalにログインし、「関数アプリ」をクリック。

「作成」をクリック。

リソースグループは、存在しなければ、「新規作成」より作成します。
公開は「コード」を選択します。後述のプラン種類にて、「消費量（サーバーレス）」を選択できなくなるためです。

プラン種類は「消費量（サーバーレス）」を選択します。「App Service」はサーバーレスではなく、インスタンス（サーバー）上で関数を実行するプランとなるため、インスタンスの起動時間に課金が発生してしまいます。

特に設定する項目がないので、次へ。

Application insightsは有効にしません。
アプリケーションを監視するサービスですが、追加料金が発生する可能性があるためです。

特に設定する項目がないので、次へ。

入力した設定が正しいことを確認して、作成をクリック。

しばらく後に、デプロイが完了し、関数アプリの一覧に、作成したAzure Functionが表示されれば、作成は完了です。

1-2. Azure Functionプロジェクトの作成

以下手順は、Visual Studio 2022 を利用したものです。他のバージョンの場合、手順や画面イメージが異なる場合があります。

続いて、コーディングの準備を行っていきます。
Visual Studioを開いて、Azure Functionのプロジェクトを作成していきます。

日単位での自動更新を行うため、「Timer trigger」を選択します。

プロジェクトが作成されました。
Function1.Run()関数に、実際の処理をコーディングします。

1-3. Azure へのデプロイ

コーディングの前に、デプロイの設定をしておきましょう。
ソリューションエクスプローラーより、プロジェクトを右クリックして「発行」を選択。

「Azure」を選択して、「次へ」をクリック。

「Azure Function App(Windows)」を選択して、「次へ」をクリック。

「関数アプリ」欄より、Azure Potalで作成した、Azure Functionを選択して、「完了」をクリック。

「発行」をクリックすることで、Azure上にデプロイが行えるようになります。
実装完了後、当該ボタンより、ソースコードをAzure上に適用しましょう。

1-4. Timer Trigger の設定

Timer Triggerの設定がデフォルトのままなので、日単位での実行を行うよう、設定を変更します。
下の赤枠の部分が、設定値です。

左から、「秒」「分」「時」「日」「月」「曜日」の設定値を表現しています。詳しくは、下記Microsoftの公式ドキュメントを参照ください。

今回は、毎日12時にnoteよりデータを取得したいため、「0 0 3 * * *」と設定しました。

Azure Functionsは、UTC（世界標準時。+00:00）のタイムゾーンで実行されるため、それを考慮した設定を行う必要があります。
つまり、JST（日本標準時。+09:00）の12時に実行したい場合、9時間前の「3」を設定します。

1-5. Azure Function でnoteよりデータを取得

今回は、noteのWebAPIを利用しました。が、このAPIは公式がnoteを運営する上でのツールとして使用されているだけであり、予告なく仕様が変わる可能性があります。
また、当然サーバーに負荷をかけるような行為はNGであり、利用の際は規約違反しないように、気を付ける必要があります。

noteのWebAPを利用してデータを取得します。まず以下のWebAPIでログインし、セッションを取得しましょう。

POST https://note.com/api/v1/sessions/sign_in

C#の具体的なコードは以下の通り。

string login = "アカウントのログインID"
string password = "アカウントのパスワード" 

var parameters = new Dictionary<string, string>()
{
   { "login", login },
   { "password", password },
   { "g_recaptcha_response", "" },
};
var content = new FormUrlEncodedContent(parameters);
HttpClient httpClient = new();
await httpClient.PostAsync("https://note.com/api/v1/sessions/sign_in", content);

ログイン後、実際にダッシュボードのデータを取得していきます。しかし、ここで難しい問題に直面しました。
noteのダッシュボードは「週」「月」「年」「全期間」の4つで切り替えができますが、
・日毎に表示する機能は存在しない
・「月」は現在日を中心に、30日前、30日後を表示する機能のため、実は月単位毎のPVは分からない
ため、データの取得方法を工夫しなければ、要件を満たせそうにありません。

どうしようか、色々悩んだ結果、、

「週」の取得を1日間隔で反復取得し、差分比較を行い、日毎のPVを割り出す

という結論に至りました。

具体的なイメージを解説すると、例えば、noteのアカウント開設日が01月15日の場合、以下「週」の取得結果は01月15日のPVだと特定できます。

次に「週」の取得日を1日ずらして取得してみたところ、PVは14になりました。1日前よりPVが9増えていますが、これは01月16日での増加分だと特定できるはずです。

つまり、これを繰り返します。（かなり強引）

とはいえ、毎日のAzure Functionの実行で、アカウント開設日から全てのデータを取得するわけにもいかないので、以下の通り、日毎の実行内容と、初回のデータ作成で処理を分け、かつ初回は手動（※1）で実行したほうがよさそうです。

・初回：ブログの開設日から本日まで、1日間隔で「週」を反復取得、差分比較を繰り返す。
・日毎：現在の「週」（7日前～本日のPV）を取得し、既に取得済みのデータ（Azure SQL Database上のデータ）と差分比較し、現在日のPVを特定する。
※1 今回は、Timer Triggerに「0 * * * * *」に設定し、一時的に毎分0秒に実行されるようにした上で、デバッグ実行でのデータ投入を行いました。

「週」の取得WebAPIは以下の通りです。ダッシュボード画面にて、次の週へ切り替える際に呼び出されているWebAPIです。

GET https://note.com/api/v1/stats/pv?end_date={targetDay}&filter=weekly&page={page}&sort=pv&span=next

C#の具体的なコードは以下の通り。

async Task<List<dynamic>> GetArticleWeekTransitionStats(HttpClient httpClient, DateTime targetDay)
{
   int page = 1;
   var result = new List<dynamic>();
   string targetDayString = targetDay.AddDays(-7).ToString("yyyy-MM-dd");

   do
   {
       string url = $"https://note.com/api/v1/stats/pv?end_date={targetDayString}&filter=weekly&page={page}&sort=pv&span=next";
       var json = await httpClient.GetStringAsync(url);
       var jsonRootElement = (JsonElement)JsonSerializer.Deserialize<object>(json);
       var articleSummary = jsonRootElement.GetProperty("data");
       var lastPage = articleSummary.GetProperty("last_page").GetBoolean();
       var noteStats = articleSummary.GetProperty("note_stats");
       int noteStatsLength = noteStats.GetArrayLength();

       for (int i = 0; i < noteStatsLength; i++)
       {
           var item = noteStats[i];

           var totalStats = new
           {
               WeekStartDate = targetDay,
               ArticleId = item.GetProperty("id").GetInt32(),
               Pv = item.GetProperty("read_count").GetInt32(),
               Comment = item.GetProperty("comment_count").GetInt32(),
               Likes = item.GetProperty("like_count").GetInt32()
           };
           result.Add(totalStats);
       }
       if (lastPage || noteStatsLength == 0)
       {
           break;
       }
       page++;
   } while (true);

   return result;
}

詳しい解説は省きますが、以下のようなJsonが取得できますので、パースを行っています。また、記事数が多い場合はページ分割されているので、そちらもループで取得しています。

{
	"data": {
		"start_date_str": "2022/03/18",
		"start_date": "2022-03-18T00:00:00.000+09:00",
		"end_date_str": "2022/03/24",
		"end_date": "2022-03-24",
		"last_page": false,
		"note_stats": [
			{
				"id": 42799314,
				"key": "nd7c41bb69b61",
				"name": "Azureを知らないエンジニアが「AZ-900 Microsoft Azure Fundamentals」を受験してみた",
				"body": null,
				"type": "TextNote",
				"status": "published",
				"read_count": 11,
				"like_count": 0,
				"comment_count": 0,
				"user": {
					"urlname": "avakansai"
				},
				"custom_domain": null
			},
		],.....
		"total_pv": 204,
		"total_like": 1,
		"total_comment": 0,
		"filter": "weekly",
		"last_calculate_at": "2022/3/31 10:58"
	}
}

上記関数を、繰り返したい日数分繰り返すことで、PVを取得できそうです。

次に、月単位の作成した記事数を取得する必要があるので、以下WebAPIで、記事情報を取得します。

https://note.com/api/v2/creators/{loginId}/contents?kind=note&page={page}&disabled_pinned=false&with_notes=false

C#の具体的なコードは以下の通り。

private async Task<List<dynamic>> GetArticles(HttpClient httpClient)
{
   int page = 1;
   var result = new List<dynamic>();
   do
   {
       string url = $"https://note.com/api/v2/creators/avakansai/contents?kind=note&page={page}&disabled_pinned=false&with_notes=false";
       var json = await httpClient.GetStringAsync(url);
       var jsonRootElement = (JsonElement)JsonSerializer.Deserialize<object>(json);
       var jsonDataSummary = jsonRootElement.GetProperty("data");
       bool lastPage = jsonDataSummary.GetProperty("isLastPage").GetBoolean();
       var contents = jsonDataSummary.GetProperty("contents");

       int contentsLength = contents.GetArrayLength();
       for (int i = 0; i < contentsLength; i++)
       {
           var item = contents[i];

           var article = new
           {
               Id = item.GetProperty("id").GetInt32(),
               Title = item.GetProperty("name").GetString(),
               publish_at = item.GetProperty("publishAt").GetDateTime()
           };
           result.Add(article);
       }

       if (lastPage)
       {
           break;
       }
       page++;
   } while (true);

   return result;
}

２. Azure SQL Databaseの作成

Azure Function同様に、Azure Potalより作成していきますが、本記事では手順を省略します。
また、C#のコードより、Azure SQL Databaseにデータを保存する処理を実装する必要がありますが、そちらも本記事の主題から外れるため、省略しています。

Azure Functionで取得したデータの保存先として、Azure SQL Databaseを利用します。

上記の通り、Azure SQL Database上にテーブルとビューを作成しました。
【記事日毎スタッツ】テーブルに日毎のPVのデータを、【記事】テーブルに、記事情報を格納します。
【記事月毎サマリ】ビューは、PowerBI上でグラフを表示を行うために、テーブルの情報を月毎に集計したビューです。具体的には、以下のとおりです。

CREATE VIEW [dbo].[v_article_month_summary]
AS
SELECT
	stats.aggregation_month as month
	,stats.pv
	,article.articles
FROM
	(
		SELECT
		    SUM(pv) as pv
		    ,CONVERT(DATETIME, FORMAT(aggregation_date, 'yyyy/MM/01')) as aggregation_month
		FROM 
		    dbo.article_day_transition_stats
		GROUP BY
			CONVERT(DATETIME, FORMAT(aggregation_date, 'yyyy/MM/01'))
	) stats 
	LEFT JOIN (
		SELECT
		    COUNT(*) as articles
		    ,CONVERT(DATETIME, FORMAT(publish_at, 'yyyy/MM/01')) as publish_at_month
		FROM 
		    dbo.article
		GROUP BY
		    CONVERT(DATETIME, FORMAT(publish_at, 'yyyy/MM/01'))
	) article ON 
		stats.aggregation_month = article.publish_at_month

3-1. Power BIよりデータの参照

データが揃ったので、ようやくPower BIの出番です。
Azure SQL Databaseのデータを使って、グラフを作っていきましょう。

Power BIを開き、「データを取得」＞「SQL Server」をクリック。

「サーバー」「データベース」にAzure SQL Databaseの情報を設定し、「OK」をクリック。

Azure SQL Databaseに作成した、「v_article_month_sammary」ビューを選択して、「読み込み」をクリック。

「視覚化」ペインより、「折れ線グラフおよび集合縦棒グラフ」をクリック。

「フィールド」ペインより、「v_article_month_sammary」ビューの全てのデータを選択。

グラフが表示されました！
が、なぜか年毎にPVと記事数が集約されてしまいます。

「視覚化」ペインの「共有の軸」に表示されている「month」を右クリックし、「Date Hierarchy」から「month」に変更します。

月毎の表示になり、だいぶそれっぽくなりました。
しかし、「月毎のPV累計」の折れ線が表示されていません。また、「フィールド」ペインにそれらしきデータも存在しないので、新しいデータ項目として、作成を行う必要があります。

「フィールド」ペインの「v_article_month_sammary」ビューを右クリックし、「新しいクイックメジャー」をクリック。

計算に「累計」選択し、基準値に「PVの合計」、フィールドに「month」を選択し、「OK」をクリック。

「フィールド」ペインに「monthのpvに対する累積値」という、新しいデータ項目が追加されたことを確認し、「視覚化」ペインの「線の値」にドラッグアンドドロップします。

月毎PVと累計PVが同じY軸で表現され、グラフが見にくくなってしましました。
「視覚化」ペインの「ビジュアルの書式設定」より、「第2Y軸」をONに変更。

ほぼ、要件通りのグラフが完成！　長かった。。。

3-2. Power BIのグラフデザイン修正

前章までで、グラフのおおよその形は完成しました。
ここから、ちくちくデザインの修正を行っていきます。
、、が、記事が長くなってしまったので、省略します。
一応、完成形のイメージだけ張っておきます。

まとめ・振り返って

・アバナード関西の活動として作成したものなので、記事にまとめてみようかな～、と軽い気持ちで作成してみましたが、やたらめったら記事が冗長になってしまいました。
Azure FunctionやPower BIなどを触ったことがない方がみて、なんとなく雰囲気が伝わればよいな、と思います。
　
・お察しの方もいるかと思いますが、本構成は無料ではありません。
Azure Functionは、ほぼ無償範囲での利用に留められると思いますが、Azure SQL Databaseが時間課金となり、最も安価な「Basic」プランで、約月550円の料金が発生します。（2022年03月時点）
　無償でグラフ化を行う場合は、「Google Apps Script」より、noteのWebAPIを呼び出し、「Google Spread Sheets」にPVや記事数などのデータ書き込んで、「Google Data Studio」でグラフ化するのがよいと思います。

さいごに

最後まで見ていただき、ありがとうございました。

記事が皆さんのお役に立てましたらいいねやフォローをお願いします！
内容に関するご指摘や、その他何かございましたら下記までご連絡ください。
アバナード関西オフィス寺岡優 / Masaru Teraoka
masaru.teraoka@avanade.com

この記事が気に入ったらサポートをしてみませんか？