【BigQuery公開データ分析】アメリカオースティンの市民の声から見えた！市民窓口のサービス改善のヒント

2024年9月22日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兆行のデータ

概要

市民からの要望や苦情を受け付ける311サービスは、行政が市民の声を聞き、より良いサービスを提供するために重要な役割を果たしています。そして、これらの市民の声を分析することで、サービス改善のヒントを得ることが期待できます。

今回は、テキサス州オースティン市の公開データを使い、市民の声を分析し、311サービスの改善点を検討します。分析には、Google Cloudのデータウェアハウス「BigQuery」を活用します。BigQueryの強力な分析機能を用いることで、大量のデータから効率的に情報を抽出することができます。この記事では、BigQueryを使ってオースティン市の311サービスに関する公開データを分析し、そこから得られたインサイトを紹介します。

311サービスとは？
BigQueryで311サービスデータを分析するメリット
分析に使用するBigQueryのデータセット
BigQueryで311サービスデータを分析する手順
BigQueryを使った311サービスデータ分析例
FAQ：BigQueryに関するよくある質問
まとめ：BigQueryで市民の声を分析し、より良いサービス提供を目指そう！

1. 311サービスとは？

311サービスとは、市民からの要望や苦情、問い合わせなどを一元的に受け付ける行政サービスです。緊急性のない行政サービスに関する問い合わせ窓口として、多くの都市で導入されています。311サービスの導入により、市民は必要な行政サービスに簡単にアクセスできるようになり、行政は市民の声を収集し、サービス改善に役立てることができます。

2. BigQueryで311サービスデータを分析するメリット

BigQueryは、311サービスデータ分析に最適なツールです。その理由は、以下の様なメリットがあるからです。

大規模データの高速処理： 膨大な311サービスデータを高速に分析できるため、サービス全体の傾向や課題を効率的に把握できます。
様々なデータソースとの連携： 公開データ、自社データ、外部データなど、様々なデータソースと連携できるため、311サービスデータと他のデータを組み合わせて、より深い分析を行うことができます。
豊富な分析機能： SQL、機械学習、自然言語処理など、高度な分析機能を利用できるため、複雑なデータ分析にも対応できます。
コストパフォーマンスの高さ： 従量課金制なので、必要な時に必要なだけ利用でき、コストを抑えられます。
高いセキュリティ： Google Cloudの強固なセキュリティ基盤により、安心してデータを管理できます。

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

今回の分析では、BigQueryの公開データセット「Austin 311 Service Requests」を使用します。このデータセットには、テキサス州オースティン市に寄せられた311サービスの要望に関する情報が格納されています。データの内容は、要望の種類、発生日時、発生場所、対応状況などです。

4. BigQueryで311サービスデータを分析する手順

BigQueryで311サービスデータを分析する手順は以下の通りです。

4.1 1. データ確認

まず、データセットの内容を確認します。BigQueryコンソールのUIでデータセットのスキーマを確認したり、サンプルデータをプレビューしたりすることで、データの構造や内容を理解することができます。`austin_311` データセットには、 `311_service_requests` テーブルが含まれており、311サービスの要望に関する詳細情報が記録されています。

4.2 2. データ加工

必要に応じて、データを加工します。例えば、日付データを年、月、日、曜日などに分割したり、特定の条件でデータをフィルタリングしたり、集計を行ったりします。BigQueryは、SQLを使ってデータ加工を行うための様々な関数を提供しています。

4.3 3. データ分析

加工したデータに対して、SQLクエリやBigQuery MLなどを用いて分析を行います。例えば、要望の種類別件数、地域別の要望件数、時間帯別の要望件数、対応状況別の件数などを分析することができます。また、機械学習を用いて、要望の発生を予測するモデルを構築することも可能です。

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

分析結果に基づいて、311サービスの改善点を考察します。例えば、特定の種類の要望が多い場合は、その要望に対応するサービスの強化や、市民への情報提供の改善などを検討することができます。また、特定の地域で要望が多い場合は、その地域のインフラ整備やサービス拠点の設置などを検討することができます。

5. BigQueryを使った311サービスデータ分析例

今回は、BigQuery公開データセットbigquery-public-data.austin_311.311_service_requestsを分析して、オースティン市の311サービスにおける改善点を探ります。

311サービスは、市民からの様々な要望や苦情を受け付ける窓口として、重要な役割を担っています。

リクエストの内容や傾向を分析することで、サービスの質向上や、市民生活の向上に繋がるヒントが見えてきます。

BigQuery分析例：苦情タイプ別リクエスト件数と解決率分析

どの苦情タイプ(complaint_description)への対応に課題があるのかを把握するため、タイプ別のリクエスト件数と解決率を集計します。

WITH request_count_by_type AS (
  SELECT
    complaint_description,
    COUNT(*) AS total_requests,
    SUM(CASE WHEN status = 'Closed' THEN 1 ELSE 0 END) AS closed_requests
  FROM `bigquery-public-data.austin_311.311_service_requests`
  GROUP BY 1
)
SELECT
  complaint_description,
  total_requests,
  closed_requests,
  SAFE_DIVIDE(closed_requests, total_requests) * 100 AS resolution_rate
FROM request_count_by_type
ORDER BY total_requests DESC

【BigQuery SQL解説】

WITH句でrequest_count_by_typeという共通テーブル式を定義し、苦情タイプ別のリクエスト件数と解決済みリクエスト件数を集計します。
SAFE_DIVIDE(closed_requests, total_requests) * 100で、解決率を計算します。SAFE_DIVIDE関数は、ゼロ除算エラーを回避するために使用します。
GROUP BY句で、苦情タイプ(complaint_description)ごとに集計します。
ORDER BY句で、リクエスト件数(total_requests)の降順に並べ替えます。

【BigQuery分析での活用例】

苦情タイプ別の解決率を比較することで、対応が遅れている、あるいは解決が困難な苦情タイプを特定できます。
特定の苦情タイプに焦点を当て、対応プロセスや担当部署の連携体制を見直すことで、サービス改善に繋げることができます。

注意点

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

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

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

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

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

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

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

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

7. まとめ：BigQueryで市民の声を分析し、より良いサービス提供を目指そう！

BigQueryは、311サービスデータ分析に最適なツールです。BigQueryの高速な処理性能と豊富な分析機能を活用することで、市民の声を分析し、サービス改善のヒントを得ることができます。この記事で紹介した分析手順や分析例を参考に、BigQueryを使った311サービスデータ分析に挑戦し、より良いサービス提供を目指しましょう！

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をコピーしました！