![見出し画像](https://assets.st-note.com/production/uploads/images/75789435/rectangle_large_type_2_e295043075ece6f71ccccd0df01e8061.png?width=1200)
noteのダッシュボードをPower BIとAzure Functionでグラフ化してみた
こんにちは、アバナード関西オフィスの寺岡です!
まだアバナードに入社したばかりで、Power BIを始め、Microsoftのクラウド製品について知識がないまま、上司に以下のように言われました。
「アバナード関西noteアカウントのダッシュボードを、Power BIでグラフ化して欲しい」
分からないことだらけでしたが、自分なりに試行錯誤して、拙いながらもなんとか作ることができましたので、せっかくなので初学者向けに記事にまとめてみました。
この記事をおすすめする人
・Power BIの初学者の方
・Azure Functionの初学者の方
参考にした記事
上記の記事を参考にさせて頂きました。ありがとうございます!
作るもの
求められている要件は以下のとおり。
・アバナード関西noteアカウントのダッシュボードをアカウント開設日より現在までの全期間をグラフ化する。
・グラフは月単位のPV数および作成した記事数。PVは累計表示も行う。
・グラフは日単位での自動更新とする。
ちなみに、noteのダッシュボードは「週」「月」「年」「全期間」の4つのパターンで、PV/コメント数/スキ数などを確認することができる機能です。
ある時点での状態は確認できますが、
・PV/コメント数/スキ数がどのように推移しているか
・どのような記事がよく見られているのか。どのような要因がPV/コメント数/スキ数の増減に関わっているのか
などの解析は、たしかにこのままでは難しそうです。
システム構成
今回は、以下のようなシステム構成にしてみました。
1. Azure Function でnoteよりデータを取得
2. Azure SQL Databaseにデータを書き込み
3. Power BIでデータ参照
![概念図](https://assets.st-note.com/production/uploads/images/72574308/picture_pc_87cc7f1f27f768099a95e5ab2fc2d664.png?width=1200)
また、Power BIのグラフは、
・月毎のPV、記事数を縦棒グラフで表現
・累計のPVを折れ線グラフで表現
という構成を想定して作成していきます。
![画像27](https://assets.st-note.com/production/uploads/images/75791216/picture_pc_d04be631dee7686ad87b3d9a0391005c.png?width=1200)
1-1. Azure Functionの作成
まず、Azure FunctionをAzure Potalより作成していきます。
Azure Potalにログインし、「関数アプリ」をクリック。
![画像13](https://assets.st-note.com/production/uploads/images/75494546/picture_pc_0c633b71fea3f160c193a826282f558f.png?width=1200)
「作成」をクリック。
![画像13](https://assets.st-note.com/production/uploads/images/75494575/picture_pc_0b834307b551c0c7591004ee0047c8f5.png?width=1200)
リソースグループは、存在しなければ、「新規作成」より作成します。
公開は「コード」を選択します。後述のプラン種類にて、「消費量(サーバーレス)」を選択できなくなるためです。
![画像10](https://assets.st-note.com/production/uploads/images/75493856/picture_pc_7ec05bfc6a115c557da7332a09c927bc.png?width=1200)
プラン種類は「消費量(サーバーレス)」を選択します。「App Service」はサーバーレスではなく、インスタンス(サーバー)上で関数を実行するプランとなるため、インスタンスの起動時間に課金が発生してしまいます。
![画像13](https://assets.st-note.com/production/uploads/images/75494633/picture_pc_9352f45178535cbb48a8cf39f88aac3e.png?width=1200)
特に設定する項目がないので、次へ。
![画像13](https://assets.st-note.com/production/uploads/images/75494060/picture_pc_962f780dcf7d061ccc06f1f0bfa9af3d.png?width=1200)
Application insightsは有効にしません。
アプリケーションを監視するサービスですが、追加料金が発生する可能性があるためです。
![画像13](https://assets.st-note.com/production/uploads/images/75494697/picture_pc_bcdffd271c12ffe03c268670365cd708.png?width=1200)
特に設定する項目がないので、次へ。
![画像12](https://assets.st-note.com/production/uploads/images/75494260/picture_pc_ea1e7f59412b4357b92a5f0c04c316a3.png?width=1200)
入力した設定が正しいことを確認して、作成をクリック。
![画像13](https://assets.st-note.com/production/uploads/images/75494305/picture_pc_91ba8d8b296dd58bdd7c3da6ca30617d.png)
しばらく後に、デプロイが完了し、関数アプリの一覧に、作成したAzure Functionが表示されれば、作成は完了です。
![画像15](https://assets.st-note.com/production/uploads/images/75494783/picture_pc_7120e7ceceaac461100c3c1a407916b8.png?width=1200)
1-2. Azure Functionプロジェクトの作成
以下手順は、Visual Studio 2022 を利用したものです。他のバージョンの場合、手順や画面イメージが異なる場合があります。
続いて、コーディングの準備を行っていきます。
Visual Studioを開いて、Azure Functionのプロジェクトを作成していきます。
![画像10](https://assets.st-note.com/production/uploads/images/75433809/picture_pc_518fef8a92da2dd3415d551e5684dc9a.png?width=1200)
日単位での自動更新を行うため、「Timer trigger」を選択します。
![画像10](https://assets.st-note.com/production/uploads/images/75433891/picture_pc_ac466ed493db388e93678df257ad4c98.png?width=1200)
プロジェクトが作成されました。
Function1.Run()関数に、実際の処理をコーディングします。
![画像11](https://assets.st-note.com/production/uploads/images/75433949/picture_pc_c9628443c6e5b5e10d6309d6b94b5e6d.png?width=1200)
1-3. Azure へのデプロイ
コーディングの前に、デプロイの設定をしておきましょう。
ソリューションエクスプローラーより、プロジェクトを右クリックして「発行」を選択。
![画像18](https://assets.st-note.com/production/uploads/images/75785213/picture_pc_f8adff1acae4094f6f8823ade8f38863.png?width=1200)
「Azure」を選択して、「次へ」をクリック。
![画像19](https://assets.st-note.com/production/uploads/images/75785267/picture_pc_213ae31aec81422a4156858b407b40d2.png?width=1200)
「Azure Function App(Windows)」を選択して、「次へ」をクリック。
![画像20](https://assets.st-note.com/production/uploads/images/75785287/picture_pc_f90adc4dd46038ba7f7fbb47e8f5a2f2.png?width=1200)
「関数アプリ」欄より、Azure Potalで作成した、Azure Functionを選択して、「完了」をクリック。
![画像21](https://assets.st-note.com/production/uploads/images/75785478/picture_pc_c47655e6c24437bde1ec34520bde4551.png?width=1200)
「発行」をクリックすることで、Azure上にデプロイが行えるようになります。
実装完了後、当該ボタンより、ソースコードをAzure上に適用しましょう。
![画像22](https://assets.st-note.com/production/uploads/images/75785523/picture_pc_0b1fdb13837a90779cdec2f9e579c49f.png?width=1200)
1-4. Timer Trigger の設定
Timer Triggerの設定がデフォルトのままなので、日単位での実行を行うよう、設定を変更します。
下の赤枠の部分が、設定値です。
![画像23](https://assets.st-note.com/production/uploads/images/75786996/picture_pc_c0e3eb4ad1b0749405b5591d4ce9ce6b.png?width=1200)
左から、「秒」「分」「時」「日」「月」 「曜日」の設定値を表現しています。詳しくは、下記Microsoftの公式ドキュメントを参照ください。
今回は、毎日12時にnoteよりデータを取得したいため、「0 0 3 * * *」と設定しました。
![画像23](https://assets.st-note.com/production/uploads/images/75787915/picture_pc_ee909738641aaff428851e1d7ce22350.png)
Azure Functionsは、UTC(世界標準時。+00:00)のタイムゾーンで実行されるため、それを考慮した設定を行う必要があります。
つまり、JST(日本標準時。+09:00)の12時に実行したい場合、9時間前の「3」を設定します。
1-5. Azure Function でnoteよりデータを取得
今回は、noteのWebAPIを利用しました。が、このAPIは公式がnoteを運営する上でのツールとして使用されているだけであり、予告なく仕様が変わる可能性があります。
また、当然サーバーに負荷をかけるような行為はNGであり、利用の際は規約違反しないように、気を付ける必要があります。
noteのWebAPを利用してデータを取得します。まず以下のWebAPIでログインし、セッションを取得しましょう。
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だと特定できます。
![画像15](https://assets.st-note.com/production/uploads/images/75495419/picture_pc_587e68e2d65d1b9135526eb4e92ff433.png)
次に「週」の取得日を1日ずらして取得してみたところ、PVは14になりました。1日前よりPVが9増えていますが、これは01月16日での増加分だと特定できるはずです。
![画像15](https://assets.st-note.com/production/uploads/images/75495404/picture_pc_58196d227e57edc90fced537366b4d94.png)
つまり、これを繰り返します。(かなり強引)
とはいえ、毎日の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で、記事情報を取得します。
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;
}
2. Azure SQL Databaseの作成
Azure Function同様に、Azure Potalより作成していきますが、本記事では手順を省略します。
また、C#のコードより、Azure SQL Databaseにデータを保存する処理を実装する必要がありますが、そちらも本記事の主題から外れるため、省略しています。
Azure Functionで取得したデータの保存先として、Azure SQL Databaseを利用します。
![画像16](https://assets.st-note.com/production/uploads/images/75773506/picture_pc_af7653c43812f549aa3c46269a31bfdd.png?width=1200)
上記の通り、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」をクリック。
![画像24](https://assets.st-note.com/production/uploads/images/75789886/picture_pc_73158d5a64c0afae7d9fc58793cbf1d0.png?width=1200)
「サーバー」「データベース」にAzure SQL Databaseの情報を設定し、「OK」をクリック。
![画像25](https://assets.st-note.com/production/uploads/images/75790005/picture_pc_6937facbf55cb40af958d9b4ccc66b48.png?width=1200)
Azure SQL Databaseに作成した、「v_article_month_sammary」ビューを選択して、「読み込み」をクリック。
![画像26](https://assets.st-note.com/production/uploads/images/75790580/picture_pc_c0c488c0a5367af3fb9f879d4f77027a.png?width=1200)
「視覚化」ペインより、「折れ線グラフおよび集合縦棒グラフ」をクリック。
![画像28](https://assets.st-note.com/production/uploads/images/75791782/picture_pc_a61e8428f91c43e238dd72bb6a664ca0.png?width=1200)
「フィールド」ペインより、「v_article_month_sammary」ビューの全てのデータを選択。
![画像32](https://assets.st-note.com/production/uploads/images/75793496/picture_pc_f7750790e9668905435688bde5fa483e.png?width=1200)
グラフが表示されました!
が、なぜか年毎にPVと記事数が集約されてしまいます。
![画像33](https://assets.st-note.com/production/uploads/images/75793539/picture_pc_ba8842444d3f54a59d0c108e392d762f.png?width=1200)
「視覚化」ペインの「共有の軸」に表示されている「month」を右クリックし、「Date Hierarchy」から「month」に変更します。
![画像32](https://assets.st-note.com/production/uploads/images/75793835/picture_pc_3b89972b7632419bdaef4c511d20c376.png?width=1200)
月毎の表示になり、だいぶそれっぽくなりました。
しかし、「月毎のPV累計」の折れ線が表示されていません。また、「フィールド」ペインにそれらしきデータも存在しないので、新しいデータ項目として、作成を行う必要があります。
![画像32](https://assets.st-note.com/production/uploads/images/75793885/picture_pc_0783edd9c325584b80f54992ac66c93e.png?width=1200)
「フィールド」ペインの「v_article_month_sammary」ビューを右クリックし、「新しいクイックメジャー」をクリック。
![画像33](https://assets.st-note.com/production/uploads/images/75794152/picture_pc_4bf834830b71099fe02e5727c8bc9867.png?width=1200)
計算に「累計」選択し、基準値に「PVの合計」、フィールドに「month」を選択し、「OK」をクリック。
![画像34](https://assets.st-note.com/production/uploads/images/75794321/picture_pc_abc2df808488158f9bf0b915de0221b2.png?width=1200)
「フィールド」ペインに「monthのpvに対する累積値」という、新しいデータ項目が追加されたことを確認し、「視覚化」ペインの「線の値」にドラッグアンドドロップします。
![画像36](https://assets.st-note.com/production/uploads/images/75794917/picture_pc_f1dee63a763a556af869e216b204d109.png?width=1200)
月毎PVと累計PVが同じY軸で表現され、グラフが見にくくなってしましました。
「視覚化」ペインの「ビジュアルの書式設定」より、「第2Y軸」をONに変更。
![画像37](https://assets.st-note.com/production/uploads/images/75794984/picture_pc_52431faaeda4b617ad39bd21a3bb2f0a.png?width=1200)
ほぼ、要件通りのグラフが完成! 長かった。。。
![画像37](https://assets.st-note.com/production/uploads/images/75795082/picture_pc_c69c72a37510cf5afdf2957f83ce0f28.png?width=1200)
3-2. Power BIのグラフデザイン修正
前章までで、グラフのおおよその形は完成しました。
ここから、ちくちくデザインの修正を行っていきます。
、、が、記事が長くなってしまったので、省略します。
一応、完成形のイメージだけ張っておきます。
![画像38](https://assets.st-note.com/production/uploads/images/75795343/picture_pc_3d975b0561f9c8d63f0e4b100fdbffd8.png?width=1200)
まとめ・振り返って
・アバナード関西の活動として作成したものなので、記事にまとめてみようかな~、と軽い気持ちで作成してみましたが、やたらめったら記事が冗長になってしまいました。
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
この記事が気に入ったらサポートをしてみませんか?