【BigQuery公開データ分析】異常検知でリスク管理！BigQueryでリコール状況を把握

2024年9月29日2024年11月9日

記事の対象者と解消できるお悩み

【対象者】

初中級データアナリスト

【興味関心】

リスク管理
異常検知
リコール分析
品質管理
危機管理
顧客満足度向上

【解消できるお悩み】

製品の欠陥や不具合によるリコールリスクを減らしたい
リコール発生の兆候をいち早く察知し、迅速に対応したい
過去の事例から、リコール発生の傾向や原因を分析したい
品質管理体制を強化したい
顧客満足度を向上させ、企業の評判を守りたい

記事作成者

【人物】株式会社志庵　代表取締役　佐藤　光　起業6年目

【経歴】株式会社光通信➜光通信同期と起業➜データアナリスト転職➜SaaSで2度目起業

【会社実績】

SaaS 資金調達無しARR2億円

SaaS GMV200億円

【内容】自社AaaSであるStruccle、webスクレイピング、BigQueryに関する記事を作成

各種用語

ARR(Annual Recurring Revenue)
- サブスクリプションサービスの年間売上※当社はSaaS売上
Struccle(ストラクル)
- 株式会社志庵が独自開発しているAaaS
AaaS(アース)
- Data Analytics as a Serviceの意
SaaS(サース)
- Software as a Serviceの意
GoogleCloudPlatform
- Googleが提供するクラウドプラットフォーム。この中に様々なサービスが存在する。
GCP(ジーシーピー)
- GoogleCloudPlatformの略
BigQuery(ビッグクエリ)
- 上記GCPの中に含まれるデータ分析基盤サービス
SQL(エスキューエル)
- データベースを操作するプログラミング言語
クエリ
- SQLで記述した命令文
- クエリ例）
  - SQL構文のクエリ⬇
    - SELECT item_name, price FROM sales_table where price >= 1000;
    - 上記クエリ(命令文)の意味としては「売上テーブル(sales_table)から価格(price)が1000円以上の商品名(item_name)と価格(price)を抽出する」
ペタバイト
- データの単位　ペタバイト＝1000テラバイト＝約100兆行のデータ

概要

企業活動において、リスク管理は非常に重要です。特に、製品のリコールは、企業の評判や業績に大きな影響を与える可能性があります。リコール発生の兆候をいち早く察知し、迅速に対応することで、被害を最小限に抑えることができます。データ分析は、これらのリスクを早期に発見し、適切な対策を講じるために有効な手段です。

今回は、Google Cloudのデータウェアハウス「BigQuery」と、FDA（アメリカ食品医薬品局）が公開している医薬品のリコールデータを使って、異常検知に基づいたリスク管理の手法を学びます。BigQueryの強力な分析機能を活用すれば、膨大なリコールデータから、リコール発生の傾向や原因を分析し、将来のリコールリスクを予測することができます。この記事では、BigQueryを使ってリコールデータを分析し、リスク管理に役立てる方法を、具体的なSQLクエリ例とともに解説します。データ分析初心者の方でも、BigQueryを使えば、データ分析でリスク管理を行う方法を理解し、実践的な分析スキルを習得できるでしょう。

BigQueryでリスク管理を行うメリット
分析に使用するBigQueryのデータセット
BigQueryでリコール状況を把握する手順
BigQueryを使ったリコールデータ分析例
FAQ：BigQueryに関するよくある質問
まとめ：BigQueryでリスクを管理し、ビジネスの安定化を目指そう！

1. BigQueryでリスク管理を行うメリット

BigQueryは、リスク管理に最適なツールです。その理由は以下のメリットがあるからです。

大規模データの高速処理： 膨大なデータを高速に分析できるため、リスクの早期発見に役立ちます。
様々なデータソースとの連携： 公開データ、社内データ、外部データなど、様々なデータソースと連携できるため、リスク分析に必要なデータを統合的に分析できます。
豊富な分析機能： SQL、機械学習、統計分析など、高度な分析機能を利用できるため、複雑なリスク分析にも対応できます。
コストパフォーマンスの高さ： 従量課金制なので、必要な時に必要なだけ利用でき、コストを抑えられます。
高いセキュリティ： Google Cloudの強固なセキュリティ基盤により、安心してデータを管理できます。

2. 分析に使用するBigQueryのデータセット

今回の分析ではBigQueryの公開データセット「FDA Drug Enforcement」の `drug_enforcement` テーブルを使用します。このデータセットには、アメリカ食品医薬品局（FDA）による医薬品のリコール情報が格納されています。リコールの分類、製品名、製造業者、リコール理由、リコール開始日、リコール終了日などの情報が含まれています。

3. BigQueryでリコール状況を把握する手順

BigQueryでリコールデータを分析し、リスク管理に役立てる手順は以下の通りです。

3.1 1. データ確認

まず、データセットの内容を確認します。BigQueryコンソールのUIでデータセットのスキーマを確認したり、サンプルデータをプレビューしたりすることで、データの構造や内容を理解することができます。

3.2 2. データ加工

必要に応じて、データを加工します。例えば、日付データを年、月、日、曜日などに分割したり、特定の製品や製造業者に絞り込んだり、集計を行ったりします。BigQueryは、SQLを使ってデータ加工を行うための様々な関数を提供しています。

3.3 3. データ分析

加工したデータに対して、SQLクエリやBigQuery MLなどを用いて分析を行います。例えば、リコールの発生件数の推移、製品タイプ別のリコール発生状況、製造業者別のリコール発生状況、リコール理由の分析などを行うことができます。また、機械学習を用いて、将来のリコールリスクを予測するモデルを構築することも可能です。

3.4 4. 考察と分析結果の活用

分析結果に基づいて、リコールリスクを低減するための対策を検討します。例えば、特定の製品タイプでリコールが多い場合は、その製品の製造プロセスや品質管理体制を見直す必要があるかもしれません。また、特定の製造業者でリコールが多い場合は、その製造業者との取引を見直す必要があるかもしれません。分析結果を、リスク管理計画の策定や改善に役立てましょう。

4. BigQueryを使ったリコールデータ分析例

ここでは、BigQueryの公開データセット「FDA Drug Enforcement」を使って、リコールデータ分析の例をいくつか紹介します。

BigQuery分析例：

1つ目のクエリはリコールの製品タイプ別件数と割合を集計します。

# 各州におけるリコール実施企業の数と、市町村レベルでのリコール件数を集計
SELECT
  state,
  COUNT(DISTINCT recalling_firm) AS num_recalling_firms,
  COUNT(*) AS num_recalls,
  COUNT(CASE WHEN city IS NOT NULL THEN 1 END) AS num_city_recalls
  -- 市町村レベルのリコール件数
FROM `bigquery-public-data.fda_drug.drug_enforcement`
GROUP BY state
ORDER BY num_recalling_firms DESC;

【BigQuery SQL解説】

product_type_counts CTE で、product_type ごとの recall_count を計算します。
total_recalls CTE で、リコールの総数を計算します。
最終的に、product_type, recall_count, recall_percentage を出力します。recall_percentage は、recall_count を total_recall_count で割ってパーセント表示にしたものです。

【BigQuery分析での活用例】

製品のタイプやリコール理由ごとの傾向を把握することで、リスクの高い製品やリコールの主要な原因を特定できます。
リコール発生件数の時系列変化を分析することで、製品の安全性の改善状況や新たなリスクの出現を検知できます。

実行結果　州別のリコール製品に責任を有する会社数

BigQuery分析例：

2つ目のクエリはリコール理由別件数と、各理由に該当する製品の平均数量を集計します。

# リコール分類とステータス別に件数を集計し、リコール開始日と報告日の間の日数を算出
SELECT
  classification,
  status,
  COUNT(*) AS recall_count,
  AVG(DATE_DIFF(SAFE_CAST(recall_initiation_date as DATE), SAFE_CAST(report_date AS DATE), DAY)) AS avg_days_to_report
FROM `bigquery-public-data.fda_drug.drug_enforcement`
GROUP BY classification, status
ORDER BY recall_count DESC;

【BigQuery SQL解説】

reason_for_recall ごとの recall_count を計算します。
各リコール理由について、product_quantity の平均値を計算します。product_quantity は文字列型なので、SAFE_CAST を使って数値型にキャストします。
最終的に、reason_for_recall, recall_count, avg_product_quantity を出力します。

【BigQuery分析での活用例】

特定の製品タイプやリコール理由に絞り込んで分析することで、より詳細なリスク要因を分析できます。たとえば、特定の製品タイプでリコールが多い場合、その製品の製造工程や品質管理に問題がある可能性があります。

【注意点】

これらのクエリは、BigQuery公開データに基づいています。データの正確性や最新性については、実際のBigQuery公開データをご自身でご確認ください。
当該分析は、様々な要因を総合的に判断する必要があります。これらのクエリは分析の一例であり、意思決定の根拠として単独で使用すべきではありません。

5. FAQ：BigQueryに関するよくある質問

Q1. BigQueryの無料枠を超えて利用すると、どのくらい費用がかかりますか？

A1. 無料枠を超えた場合、従量課金制で料金が発生します。料金は、処理したデータ量やストレージ容量などによって異なります。詳しくは、BigQueryの料金ページをご確認ください。

Q2. BigQueryで使えるSQLは、他のデータベースと同じですか？

A2. BigQueryは標準SQLをサポートしており、他のデータベースで学んだSQLの知識を活かすことができます。ただし、BigQuery独自の関数や構文も存在するため、BigQueryのドキュメントで詳細を確認することをおすすめします。

Q3. SQLの学習には、どのような教材がありますか？

A3. SQLの学習には、オンライン学習サイト、書籍、動画教材など、様々な教材があります。初心者向けの教材から、上級者向けの教材まで、レベルに合わせて選ぶことができます。Googleが提供するBigQueryのチュートリアルもSQLの学習に役立ちます。

6. まとめ：BigQueryでリスクを管理し、ビジネスの安定化を目指そう！

BigQueryは、リスク管理に役立つ強力なツールです。BigQueryの高速な処理性能と豊富な分析機能、そして無料の公開データセットを活用することで、リコール状況を把握し、リスクを最小限に抑えることができます。ぜひ、BigQueryを導入し、データ分析の力を使って、ビジネスの安定化を目指しましょう。

BigQuery導入サポート、転職検討中の方はお問い合わせフォームからご相談くださいませ。

誠心誠意精一杯対応いたします。

【参考URL】

GoogleCloudPlatform：https://console.cloud.google.com/welcome/new

BigQuery：https://cloud.google.com/bigquery?hl=ja

BigQueryリリース情報：https://cloud.google.com/bigquery/docs/release-notes

BigQuery料金：https://cloud.google.com/bigquery/pricing

BigQuery料金無料枠：https://cloud.google.com/bigquery/pricing?hl=ja#free-tier

Google Cloud活用事例：https://cloud.google.com/customers/index.html?hl=ja#

Looker Studio：https://cloud.google.com/looker-studio?hl=ja

Looker Studioサンプル：https://cloud.google.com/bigquery/docs/visualize-looker-studio?hl=ja

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！