Qiitaのorganizationの記事情報をAPIで取得してLookerStudioで見える化してみた

## はじめに

こんにちは、京セラコミュニケーションシステムの五十嵐（@kccs_hironobu-ikarashi）です。

現在、弊社ではQiita記事を定期的に投稿しており、弊社の投稿記事について、半期ごとの投稿数、いいね数やコメント数を分析した内容を記事にしました。

:::note info
本記事は2024年09月ごろに実施した内容を記事にしておりますので、ご了承ください。
記事の内容は筆者の理解をベースに実施したものとなり、必ずしも効果を保証するものではありません。
:::

## 本記事の対象者
- Qiitaを利用しておりorganization単位で分析したい方

### 流れ

1. 個人用アクセストークンの取得
2. NoteBook作成
3. Qiita APIからのデータ取得
4. BigQueryへロード
5. NoteBookで集計+LookerStudioで見える化

### 実施手順

#### 1.個人用アクセストークンの取得

今回はQiita APIでorganizationに紐づく記事を取得することが目的のため、認証なしでもAPIを利用できます。

ただし、認証なしユーザーの場合は1時間のリクエスト数の上限が小さいため、
Qiita APIリクエスト時のユーザー認証用にアクセストークンを取得しています。


||認証なしユーザー|認証ありユーザー|
|:----|:----|:----|
|1時間のリクエスト数の上限|60回|1000回|

※[Qiita API v2 リファレンス](https://qiita.com/api/v2/docs)を参照

- 取得手順（Qiitaアカウントを持っている前提となります）
1. [Qiita](https://qiita.com)にログインし、「設定」>「アプリケーション」>「新しくトークンを発行する」を押下する。

![qiita_page1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/05ebe6d3-2927-a2de-fc31-3dc354d0a23c.png)

2. アクセストークンの説明を任意に入力し、今回は記事情報を取得するだけなので`read_qiita`にチェックをいれて「発行する」を押下。

![qiita_page2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/12d4b959-63bb-b65b-53d1-519e98ec744b.png)

3. アクセストークンが取得できるので、トークン内容を控えておく。

![qiita_page3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/cf117d75-262e-e7d6-3f6b-c1e2dbdeff76.png)

#### 2. NoteBook作成

1.Google Cloud Consoleにアクセスし、検索欄から「vertex ai」を検索し、下記画面に遷移後、「NOTEBOOKS」の「Colab Enterprise」を選択する。

![notebooks.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/f3c213c5-0311-eae1-b509-6c2386de7283.png)

2. 「ノートブックを作成する」を選択すると、コードの入力が可能になる。

![notebooks1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/347407eb-dee5-9dbb-6441-8b7ddaa71614.png)

#### 3. Qiita APIからのデータ取得

下記コードを実行し、```org:kccs-digital-solution```の記事作成日が2022-04-01から2024-10-10までの情報を取得。

確認用に取得結果（`df_articles`）が最後に出力されます。

※ HEADERSのAuthorizationの中の`【取得したトークンを記載】`には、「[1.個人用アクセストークンの取得](#1個人用アクセストークンの取得)」で取得したトークンを記載。

```python
import requests
import pandas as pd

# 定数をまとめて読みやすくする
PAR_PAGE = 20
QIITA_API_URL = "https://qiita.com/api/v2/items"
QUERY_PARAMS = {
    "query": "org:kccs-digital-solution created:>=2022-04-01 created:<=2024-10-10",
}
HEADERS = {
    "content-type": "application/json",
    "Authorization": "Bearer 【取得したトークンを記載】",
}

def fetch_qiita_articles(url, params, headers, per_page=PAR_PAGE):
    """Qiita APIから記事を複数ページに渡って取得する。"""
    all_articles = []
    page = 1

    while True:
        response = requests.get(url, params={**params, **({"page": page} if page > 1 else {})}, headers=headers)

        if response.status_code != 200:
            raise requests.exceptions.RequestException(f"検索結果取得失敗: {response.status_code} {response.text}")

        articles = response.json()
        all_articles.extend(articles)

        if not articles or len(articles) < per_page:
            break

        page += 1
    return all_articles


def create_dataframe(articles):
    """取得した記事データをPandas DataFrameに変換する。"""
    # リスト内包表記で簡潔に記述
    data = [{
        "ID": article["id"],
        "タイトル": article["title"],
        "URL": article["url"],
        "いいね数": article["likes_count"],
        "ストック数": article["stocks_count"],
        "リアクション数": article["reactions_count"],
        "コメント数": article["comments_count"],
        "非公開": article["private"],
        "更新日": article["updated_at"],
        "作成日": article["created_at"],
        "Organization": article["organization_url_name"],
        "投稿者ID": article["user"]["id"],
        "投稿者名": article["user"]["name"],
    } for article in articles]

    return pd.DataFrame(data)


if __name__ == "__main__":
    try:
        articles = fetch_qiita_articles(QIITA_API_URL, QUERY_PARAMS, HEADERS)
        df_articles = create_dataframe(articles)
    except requests.exceptions.RequestException as e:
        print(f"エラーが発生しました: {e}")
    except KeyError as e:
        print(f"予期せぬキーエラーが発生しました: {e}. APIレスポンスの構造が変更された可能性があります。")

df_articles
```

下記画像のように、「ノートブック」のコード部分に上記のコードを記載し、左側の実行ボタンを押下すると、APIで取得した結果が出力される。

![notebooks2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/60c1a25e-019f-f753-8a37-adcc001405e6.png)

#### 4. BigQueryへロード

まずは上記の`df_articles`の内容をCSVに出力して、Cloud Storageにコピーする。

```python
df_articles.to_csv("qiita_articles.csv", index=False)

# エクスポート確認
!ls -la qiita_articles.csv
!cat qiita_articles.csv | head -n 5

# Cloud Storageにコピー
!gsutil cp qiita_articles.csv gs://qiita_test/qiita_articles.csv
```
実行してCloud Storageへのアップロードを確認。

![notebooks3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/e97c9a74-25d1-57d7-741a-2df4881045d5.png)

次にCloud StorageからBigqueryへロードする

1. bigquerの定義用ファイル、プロジェクトをセット

※`【自身のproject名を指定】`には自身のproject名をセット

```python
schema_file_qiita_articles = "/schema_file_qiita_articles.json"
bq_def_file_qiita_articles = "/bq_def_file_qiita_articles.json"
project_id="【自身のproject名を指定】"
bq_table_qiita_articles="qiita.QIITA_ARTICLES"
```

```python
# スキーマファイルの生成
with open(schema_file_qiita_articles, 'w') as file:
    file.write(
        '[\
        {"name":"ID","type":"STRING","mode":"NULLABLE","description":"ID"},\
        {"name":"TITLE","type":"STRING","mode":"NULLABLE","description":"タイトル"},\
        {"name":"URL","type":"STRING","mode":"NULLABLE","description":"URL"},\
        {"name":"NUMBER_OF_LIKES","type":"INTEGER","mode":"NULLABLE","description":"いいね数"},\
        {"name":"NUMBER_OF_STOCK","type":"INTEGER","mode":"NULLABLE","description":"ストック数"},\
        {"name":"NUMBER_OF_REACTIONS","type":"INTEGER","mode":"NULLABLE","description":"リアクション数"},\
        {"name":"NUMBER_OF_COMMENTS","type":"INTEGER","mode":"NULLABLE","description":"コメント数"},\
        {"name":"PRIVATE","type":"STRING","mode":"NULLABLE","description":"非公開"},\
        {"name":"UPDATE_DATE","type":"TIMESTAMP","mode":"NULLABLE","description":"更新日"},\
        {"name":"CREATION_DATE","type":"TIMESTAMP","mode":"NULLABLE","description":"作成日"},\
        {"name":"ORGANIZATION","type":"STRING","mode":"NULLABLE","description":"Organization"},\
        {"name":"POSTER_ID","type":"STRING","mode":"NULLABLE","description":"投稿者ID"},\
        {"name":"POSTER_NAME","type":"STRING","mode":"NULLABLE","description":"投稿者名"}\
        ]'
    )

# 外部テーブルを作るための定義ファイル生成
with open(bq_def_file_qiita_articles, 'w') as file:
    file.write(
        '{\
            "autodetect": true,\
            "csvOptions": {\
                "allowJaggedRows": false,\
                "allowQuotedNewlines": false,\
                "encoding": "UTF-8",\
                "fieldDelimiter": ",",\
                "quote": "\\"",\
                "skipLeadingRows": 1\
            },\
            "sourceFormat": "CSV",\
            "sourceUris": [\
                "gs://qiita_test/qiita_articles.csv"\
            ]\
        }\
        '
    )
# テーブルの削除
!bq rm -f -t $project_id:$bq_table_qiita_articles

# 外部テーブル定義
!bq mk --table \
  --external_table_definition=$bq_def_file_qiita_articles \
  $project_id:$bq_table_qiita_articles \
  $schema_file_qiita_articles
```

Bigqueryにテーブルが作成されたことを確認
![notebooks4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/6051a034-0220-fb7a-045c-3ff77cb9c2de.png)

ここで弊社西田（@kccs_hiromi-nishida）の記事を参照して、データキャンバスをつかって解析してみた

[初心者OK！Bigqueryデータキャンバスでデータ分析してみよう](https://qiita.com/kccs_hiromi-nishida/items/db49d88152fda5577ebc)

集計はできましたが、可視化がうまくいかなかった・・
※2022年上期、2022年下期、2023年上期…のように見たかった

![データキャンバス.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/7311281c-254f-94db-2756-ac5b66cd632e.png)

#### 5. NoteBookで集計+LookerStudioで見える化

データキャンバスでうまく可視化できなかったので、NoteBook内で集計する。

まずはCloud Storageにコピー。

```python
%%bigquery df_articles_analysis
WITH
  articles_detail AS (
  SELECT
    ID,
    TITLE,
    URL,
    NUMBER_OF_LIKES,
    NUMBER_OF_STOCK,
    NUMBER_OF_REACTIONS,
    NUMBER_OF_COMMENTS,
    PRIVATE,
    UPDATE_DATE,
    CREATION_DATE,
    ORGANIZATION,
    POSTER_ID,
    POSTER_NAME,
    CAST(
      IF
        (EXTRACT(MONTH
          FROM
            CREATION_DATE) >= 4, EXTRACT(YEAR
          FROM
            CREATION_DATE)+1, EXTRACT(YEAR
          FROM
            CREATION_DATE) ) AS STRING) AS FY,
    CAST(
    IF
      (EXTRACT(MONTH
        FROM
          CREATION_DATE) >= 4
        AND EXTRACT(MONTH
        FROM
          CREATION_DATE) <= 9, "上期", "下期" ) AS STRING ) AS half
  FROM
    `【自身のproject名を指定】.qiita.QIITA_ARTICLES` )
SELECT
  FY,
  half,
--  FORMAT_TIMESTAMP('%Y-%m', QIITA_ARTICLES.CREATION_DATE) AS `month`,  -- 月別を見たいならこれをONに！
  COUNT(QIITA_ARTICLES.ID) AS `articles_count`,
  SUM(QIITA_ARTICLES.NUMBER_OF_LIKES) AS `sum_of_likes`,
  SUM(QIITA_ARTICLES.NUMBER_OF_STOCK) AS `sum_of_stock`
FROM
  articles_detail AS QIITA_ARTICLES
WHERE
  CAST(QIITA_ARTICLES.CREATION_DATE AS STRING) >= '2022-10-01'
GROUP BY
  1,
  2
ORDER BY
  1,
  2;
```
上記で集計した結果をcsvに出力し、Cloud Storageにコピー

```python
df_articles_analysis.to_csv("qiita_articles_analysis_half_year.csv", index=False)

# エクスポート確認
!ls -la qiita_articles_analysis_half_year.csv
!cat qiita_articles_analysis_half_year.csv | head -n 5

# Cloud Storageにコピー
!gsutil cp qiita_articles_analysis_half_year.csv gs://qiita_test/qiita_articles/qiita_articles_analysis_half_year.csv
```

最後にLookerStudioで見える化

1. [LookerStudio](https://lookerstudio.google.com/navigation/reporting)にアクセスし、空のレポートを追加
![lookerstudio.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/c299082b-99ab-ac86-2f82-b0baf351f4d6.png)

2. Cloud Storageを指定
![lookerstudio1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/4041ffeb-b7ef-c945-c024-a0113fbfee14.png)

3. 先ほどアップロードしたcsvのパスを指定

![lookerstudio2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/45ff6b32-2b73-7b1e-d200-821a5d08f101.png)


4. グラフを追加から、今回は折れ線グラフを指定
![lookerstudio3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/fc51d42a-2023-b519-fa6b-eba4f4553a7b.png)

5. 20XX年〇期ごとに表示させたいので、FYとhalfでディメンションを追加
![lookerstudio4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/43fbf3b9-bcad-5f5e-b2dd-f6afdc2f505a.png)

![lookerstudio5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/971422cb-0785-77e3-d3e1-7152eaac2c2d.png)

6. 後は設定、スタイルでソート条件や集計内容を編集
![lookerstudio6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/b2459736-c984-bb38-2474-283977fe0a05.png)

### 結果

年度＋半期ごとに見える化できました！
![lookerstudio7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3794674/3e272444-69e0-0095-4d99-764eacb3f3af.png)

## おわりに
Qiita記事のデータ取得、集計から見える化まで行った際の内容を投稿させていただきました。

記事が長くなっておりますが、最後までご拝読いただきありがとうございました。