自社のSNS投稿をラベリングするシステムをBigQueryとスプシで作った話

## きっかけ
自社で運用しているSNSの投稿（インスタグラム）をラベリングしたいという相談を受けました。ラベルごとにパフォーマンスを分析したり、管理したいということでした。

## 実現したかったこと
まず、SNS投稿のデータ自体は既にBigQueryの別のテーブルに保存されている状態でした。そのため、最終的には、その投稿テーブルとラベルテーブルを掛け合わせた形でデータの出力ができるようになることが目標でした。

```mermaid
flowchart TD
    direction TB
    subgraph 投稿データ
        direction LR
        posts[投稿テーブル]
        labels[ラベルテーブル]
        posts --- |掛け合わせる| labels
    end
    投稿データ --> |出力| LS[Looker Studio]
```

また要件としては以下がありました。
1. 1投稿につきラベルは複数つけられる
1. ラベルは削除できる
1. UIはスプシ／Google Apps Script（SNS運用者が使うため）

## ラベルテーブルのスキーマを検討する
#### ネストフィールドにするか単一行にするか
1投稿につき複数ラベルが振られうるということで、1対多の関係を1つのテーブル内で表現できるネストフィールドを使うか、単純に投稿とラベルを1行1行で管理するか検討しました。それぞれのメリット、デメリットをまとめます。

- ネストフィールドでやる場合
  - メリット
    - データ構造が直感的でわかりやすい
    - 投稿とラベルの関係を簡潔に表現できる
  - デメリット：
    - クエリが複雑になりやすい
    - データ操作が難しくなる場合あり
- 単一行でやる場合
  - メリット
    - クエリがシンプルで理解しやすい
    - データ操作が容易
  - デメリット：
    - データ量が増える可能性あり
    - データの重複が発生する可能性あり

参考:

https://cloud.google.com/bigquery/docs/best-practices-performance-nested?hl=ja

今回、UIがスプシだったため、スプシのデータをそのままテーブルに持っていきやすい形の単一行を採用することにしました。

| カラム名| データ型 |
--- | ---
アカウントID | STRING
投稿ID | STRING
ラベル | STRING

## MERGEステートメントでラベルテーブルを更新する
MERGEステートメントを使うと、`INSERT`、`UPDATE`、`DELETE` の各オペレーションを1つのテーブルでに対して行えます。
スプシに以下のようなデータがあったとします。

|| アカウントID | 投稿ID | ラベル | 削除フラグ（既にDBに登録されていて削除したい場合にTRUE）
--- | --- | --- | --- | ---
1 | 00000000000000 | 11111111111111 | ラベル1 | FALSE
2 | 00000000000000 | 11111111111111 | ラベル2 | FALSE
3 | 00000000000000 | 22222222222222 | ラベル3 | TRUE

その場合、以下のようなクエリを書くことでデータの更新ができます。
結果としては、1行目と2行目は `UPSERT` され、3行目は `DELETE` されます。
```
MERGE
  `${プロジェクトID}.${データセットID}.labels` AS T
USING
  (
  SELECT
    *
  FROM (
    SELECT
      '00000000000000' AS account_id,
      '11111111111111' AS post_id,
      'ラベル1' AS label,
      FALSE AS to_be_deleted
    UNION ALL
    SELECT
      '00000000000000' AS account_id,
      '11111111111111' AS post_id,
      'ラベル2' AS label,
      FALSE AS to_be_deleted
    UNION ALL
    SELECT
      '00000000000000' AS account_id,
      '22222222222222' AS post_id,
      'ラベル3' AS label,
      TRUE AS to_be_deleted
)) AS S
ON
  T.account_id = S.account_id
  AND T.post_id = S.post_id
  AND T.label = S.label
  WHEN MATCHED AND S.to_be_deleted = TRUE THEN DELETE
  WHEN NOT MATCHED
  THEN
INSERT
  (account_id,
    post_id,
    label)
VALUES
  (S.account_id, S.post_id, S.label);
```

参考:

https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

https://cloud.google.com/bigquery/docs/data-manipulation-language?hl=ja#update_delete_merge_dml_concurrency

## 投稿データとラベルデータと掛け合わせるクエリを書く
単純に投稿データとラベルデータをJOIN（LEFT JOIN）してしまうと1投稿に対してラベルが複数のときに重複が発生してしまい、パフォーマンスの数字がラベルの重複分だけ倍になってしまいます。
そのため、集約関数の `ARRAY_AGG` ラベルを配列で表現し、データの重複を防ぐことにしました。
以下のようなイメージです。

```
SELECT
  * except (labels_post_id)
FROM (
  SELECT
    id,
    content
  FROM `${プロジェクトID}.${データセットID}.posts`
  GROUP BY
    id,
    content
) AS posts -- 投稿も2時間ごとにデータがあったので、ディメンションでユニークになるように調整
LEFT JOIN (
  SELECT
    id AS label_post_id,
    ARRAY_AGG(label) AS label
  FROM
    `${プロジェクトID}.${データセットID}.labels`
  GROUP BY
    id
) labels
ON
  posts.id = labels.label_post_id
```

## まとめ
今回のプロジェクトでは、自社で運用しているSNSの投稿にラベルを付け、そのパフォーマンスを分析・管理するシステムを構築しました。
構築するにあたり、ふだんはあまり使ったことがなかったMERGEステートメントといったBigQueryのDMLにも触れることになり良い勉強の機会となりました。

## 宣伝
GASラボでは、便利な自動化ツール（GAS製・100種類以上）、新規サービス、テンプレートを開発・公開しております。
最新情報については、Twitter(X）でも配信しておりますので、是非チェックしていただけると幸いです。

[【GASラボ】Twitter（X）アカウント](https://x.com/gas93740656?ref_src=twsrc%5Etfw%7Ctwcamp%5Eembeddedtimeline%7Ctwterm%5Escreen-name%3Agas93740656%7Ctwcon%5Es1_c1)