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

きっかけ

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

実現したかったこと

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

また要件としては以下がありました。

1. 1投稿につきラベルは複数つけられる
2. ラベルは削除できる
3. UIはスプシ／Google Apps Script（SNS運用者が使うため）

ラベルテーブルのスキーマを検討する

ネストフィールドにするか単一行にするか

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

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

参考:

今回、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);

参考:

投稿データとラベルデータと掛け合わせるクエリを書く

単純に投稿データとラベルデータを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）アカウント