【データ集計】ShareView口コミデータをBigQueryでカテゴリ集計

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

対象者

データ分析に関心のあるマーケター、商品開発担当者、データアナリスト

興味関心

• 効果的な口コミデータのカテゴリ別集計方法
• データに基づいたマーケティング戦略の立案
• カテゴリ別の顧客ニーズの把握

解消できるお悩み

• 顧客ニーズに基づいた商品開発のアイデアがない
• 集計ができず口コミデータを有効活用できていない

はじめに

本ブログではShareViewに蓄積された膨大な口コミデータのカテゴリ別集計方法を解説します。具体的な集計方法から集計結果の情報を提供します。この記事を読むことで貴社の商品開発・マーケティング戦略に口コミデータを最大限に活かすためのヒントが得られるでしょう。

本集計から作成した記事は下記。

あわせて読みたい

【データ集計】スクレイピングで取得したShareView口コミデータのカテゴリ集計 記事の対象者と解消できるお悩み 対象者 データ分析に関心のあるマーケティング担当者、商品開発担当者、データアナリスト 興味関心 口コミデータの効果的なカテゴリ別...

データ概要

1. データ内容

今回の集計では、ShareViewサイトから収集した口コミデータを使用。以下の情報が含まれています。

• 提供企業
• 商品名
• レビュー本文
• 評価（5段階）
• 投稿日時
• 顧客属性（年齢、性別、居住地など）
• ラベル情報（リピートしたい、コスパが良い、高級感があるなど）

データの期間：2011年4月12日〜2024年5月18日
総レビュー数：584,058件

2. 実際のデータ

本集計では、BigQueryを用いて集計を行いました。以下が実際のデータです。

データの前処理は下記記事にて解説しております。

あわせて読みたい

【データ集計】ShareView口コミデータをBigQueryで集計 記事の対象者と解消できるお悩み 【対象者】 初中級データアナリストマーケティング担当者 【興味関心】 効果的な口コミデータの集計方法が知りたい 自社商品・競合商品...

カテゴリ集計クエリ

今回、顧客のリアルな声と商品に対する評価を包括的に見える化するため、以下の項目で口コミデータの集計を行いました。

• カテゴリ別の口コミ数：各カテゴリにおける口コミの件数を把握することで、どのカテゴリの商品に対する関心が高いかを確認します。
• 性別の口コミ比率：ユーザーの性別比率を分析し、特定の性別からの意見が偏っていないかを確認します。
• 年代別の口コミ比率：ユーザーの年代別比率を分析し、どの年代層からの意見が多いか、また、特定の年代層の意見が偏っていないかを確認します。
• 都道府県別の口コミ比率：ユーザーの居住地域を分析し、地域ごとの口コミ投稿の活発度を把握します。
• カテゴリ別の評価: 各カテゴリにおける評価を算出し、どのカテゴリの商品が高く評価されているかを確認します。
• 性別の評価: 性別ごとの評価を算出し、性別による評価の違いを分析します。
• 年代別の評価: 年代ごとの評価を算出し、年代による評価の違いを分析します。
• 都道府県別の評価（箱ひげ図）: 都道府県別の評価分布を箱ひげ図で可視化し、地域ごとの評価のばらつきや傾向を把握します。

カテゴリ別口コミ数

実際のクエリから可視化まで

カテゴリ別の口コミ数クエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, count(0) as cnt, sum(count(0)) over() as total_cnt
from tb
group by 1
)
select
  cname, cnt, total_cnt,
  sum(cnt) over(order by cnt desc, cname) as cum_cnt,
  round(cnt/total_cnt * 100, 2) as rate,
  round(sum(cnt) over(order by cnt desc, cname)/total_cnt * 100, 2) as cum_rate,
from cname_cnt

cname	cnt	total_cnt	cum_cnt	rate	cum_rate
お菓子・デザート	237008	584058	237008	40.58	40.58
飲料	143295	584058	380303	24.53	65.11
食品	114433	584058	494736	19.59	84.71
その他	56725	584058	551461	9.71	94.42
日用品	32597	584058	584058	5.58	100

性別での口コミ比率クエリ

以下集計では上位3カテゴリの集計を行います。(口コミ件数494736件_全体の84.71%)
また、性別の「回答なし」は件数が少ないため除外。(口コミ件数488284件_全体の約83.60%)

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, case when gender = "-" then "回答なし" else gender end gender, 
  count(0) as cnt, sum(count(0)) over() as total_cnt,
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and gender in ("男性", "女性")
group by 1,2
)

select
  cname, gender, cnt, total_cnt,
  sum(cnt) over(partition by cname order by cnt desc , cname) as cum_cnt,
  round(cnt/sum(cnt) over (partition by cname) * 100, 2) as rate,
  round(sum(cnt) over(partition by cname order by cnt desc , cname)/sum(cnt) over (partition by cname) * 100, 2) as cum_rate,
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

cname	gender	cnt	total_cnt	cum_cnt	rate	cum_rate
お菓子・デザート	女性	179822	488284	179822	76.94	76.94
お菓子・デザート	男性	53896	488284	233718	23.06	100
飲料	女性	97511	488284	97511	68.82	68.82
飲料	男性	44187	488284	141698	31.18	100
食品	女性	87087	488284	87087	77.16	77.16
食品	男性	25781	488284	112868	22.84	100

年代別での口コミ比率クエリ

年代別の「回答なし」は件数が少ないため除外。(口コミ件数480642件_全体の約83.60%)

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, 
  case 
    when age = "" or age = "-" then "回答なし"
    when age like "%10代%" then "10代" 
    when age like "%60代%" then "60代以上"
    else age end age,
  count(0) as cnt, sum(count(0)) over() as total_cnt,
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and age != "" and age != "-"
group by 1,2
)

select
  cname, age, cnt, total_cnt,
  sum(cnt) over(partition by cname order by cnt desc , cname) as cum_cnt,
  round(cnt/sum(cnt) over (partition by cname) * 100, 2) as rate,
  round(sum(cnt) over(partition by cname order by cnt desc , cname)/sum(cnt) over (partition by cname) * 100, 2) as cum_rate,
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

cname	age	cnt	total_cnt	cum_cnt	rate	cum_rate
お菓子・デザート	30代	87250	480642	87250	38	38
お菓子・デザート	40代	69011	480642	156261	30.06	68.06
お菓子・デザート	50代	33690	480642	189951	14.67	82.73
お菓子・デザート	20代	25224	480642	215175	10.99	93.71
お菓子・デザート	60代以上	12070	480642	227245	5.26	98.97
お菓子・デザート	10代	2361	480642	229606	1.03	100
飲料	30代	51683	480642	51683	37.07	37.07
飲料	40代	42340	480642	94023	30.37	67.44
飲料	50代	22728	480642	116751	16.3	83.74
飲料	20代	14479	480642	131230	10.38	94.12
飲料	60代以上	7520	480642	138750	5.39	99.51
飲料	10代	677	480642	139427	0.49	100
食品	30代	44218	480642	44218	39.62	39.62
食品	40代	31845	480642	76063	28.53	68.15
食品	50代	17765	480642	93828	15.92	84.07
食品	20代	10485	480642	104313	9.39	93.46
食品	60代以上	6723	480642	111036	6.02	99.49
食品	10代	573	480642	111609	0.51	100

都道府県別での口コミ比率クエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, 
  case when pref = "" then "回答なし" else pref end pref,
  count(0) as cnt, sum(count(0)) over() as total_cnt,
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and pref != ""
group by 1,2
)

select
  cname, pref, cnt, total_cnt,
  sum(cnt) over(partition by cname order by cnt desc , cname) as cum_cnt,
  round(cnt/sum(cnt) over (partition by cname) * 100, 2) as rate,
  round(sum(cnt) over(partition by cname order by cnt desc , cname)/sum(cnt) over (partition by cname) * 100, 2) as cum_rate,
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

カテゴリ別評価

実際のクエリから可視化まで

カテゴリ評価の集計クエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_tb as (select *, from tb where cname in ("お菓子・デザート", "飲料", "食品"))
, cname_cnt as (
select 
  cname, round(avg(rating),2) avg, min(rating) mi_r,
  APPROX_QUANTILES(rating, 100)[OFFSET(25)] as per25, round(bqutil.fn.median(array_agg(rating)),2) as med,
  APPROX_QUANTILES(rating, 100)[OFFSET(75)] as per75, max(rating) mx_r, count(0) as cnt, sum(count(0)) over() as total_cnt
from cname_tb
group by 1
)

select
  cname, cnt, total_cnt,
  avg, mi_r, per25, med, per75, mx_r
from cname_cnt
order by cnt desc

cname	cnt	total_cnt	avg	mi_r	per25	med	per75	mx_r
お菓子・デザート	237008	494736	4.23	0.4	4	4.25	4.67	5
飲料	143295	494736	4.05	0	3.67	4	4.5	5
食品	114433	494736	4.14	0	3.83	4.17	4.5	5

性別での評価集計クエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, case when gender = "-" then "回答なし" else gender end gender, 
  round(avg(rating),2) avg, min(rating) mi_r,
  APPROX_QUANTILES(rating, 100)[OFFSET(25)] as per25, round(bqutil.fn.median(array_agg(rating)),2) as med,
  APPROX_QUANTILES(rating, 100)[OFFSET(75)] as per75, max(rating) mx_r, count(0) as cnt, sum(count(0)) over() as total_cnt
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and gender in ("男性", "女性")
group by 1,2
)

select
  cname, gender, cnt, total_cnt,avg, mi_r, per25, med, per75, mx_r
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

cname	gender	cnt	total_cnt	avg	mi_r	per25	med	per75	mx_r
お菓子・デザート	女性	179822	488284	4.24	0.4	4	4.25	4.7	5
お菓子・デザート	男性	53896	488284	4.22	0.5	4	4.25	4.5	5
飲料	女性	97511	488284	4.07	0	3.67	4	4.5	5
飲料	男性	44187	488284	4.02	0	3.67	4	4.4	5
食品	女性	87087	488284	4.15	0	3.83	4.2	4.5	5
食品	男性	25781	488284	4.11	0	3.8	4	4.5	5

性別での評価集計クエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, 
  case 
    when age = "" or age = "-" then "回答なし"
    when age like "%10代%" then "10代" 
    when age like "%60代%" then "60代以上"
    else age end age,
  round(avg(rating),2) avg, min(rating) mi_r,
  APPROX_QUANTILES(rating, 100)[OFFSET(25)] as per25, round(bqutil.fn.median(array_agg(rating)),2) as med,
  APPROX_QUANTILES(rating, 100)[OFFSET(75)] as per75, max(rating) mx_r, count(0) as cnt, sum(count(0)) over() as total_cnt
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and age != "" and age != "-"
group by 1,2
)

select
  cname, age, cnt, total_cnt,avg, mi_r, per25, med, per75, mx_r
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

cname	age	cnt	total_cnt	avg	mi_r	per25	med	per75	mx_r
お菓子・デザート	30代	87250	480642	4.23	0.4	4	4.25	4.67	5
お菓子・デザート	40代	69011	480642	4.21	0.5	4	4.25	4.5	5
お菓子・デザート	50代	33690	480642	4.23	0.5	4	4.25	4.67	5
お菓子・デザート	20代	25224	480642	4.25	0.8	4	4.25	4.75	5
お菓子・デザート	60代以上	12070	480642	4.29	1	4	4.25	4.75	5
お菓子・デザート	10代	2361	480642	4.17	1	3.75	4.25	4.75	5
飲料	30代	51683	480642	4.05	0	3.67	4	4.4	5
飲料	40代	42340	480642	4.03	0.1	3.67	4	4.4	5
飲料	50代	22728	480642	4.05	0	3.67	4	4.5	5
飲料	20代	14479	480642	4.09	1	3.67	4.17	4.67	5
飲料	60代以上	7520	480642	4.14	1	4	4	4.5	5
飲料	10代	677	480642	4.19	1	4	4.33	4.67	5
食品	30代	44218	480642	4.14	0.5	3.83	4.2	4.5	5
食品	40代	31845	480642	4.11	0	3.8	4	4.5	5
食品	50代	17765	480642	4.14	0	3.8	4.1	4.5	5
食品	20代	10485	480642	4.2	1	3.83	4.25	4.67	5
食品	60代以上	6723	480642	4.2	1.17	4	4.2	4.6	5
食品	10代	573	480642	4.11	1	3.75	4.25	4.6	5

都道府県別の箱ひげクエリ

with tb as (
  select
    *except(cname),
    case 
      when cname in (
        'チョコ・焼き菓子', 'アイス', 'その他スナック菓子', '米菓', 'ポテトチップス その他',
        'ポテトチップス 塩', 'ポテトチップス のり塩', 'ポテトチップス コンソメ', 'あめ・キャンディ',
        'ソフトキャンディ', 'タブレット菓子', 'キャラメル', 'グミ', 'コンビニ スイーツ', 'カップデザート',
        '惣菜パン・菓子パン', '食パン', '食事パン', 'ベーカリー', 'コンビニ ドーナツ') then 'お菓子・デザート'
      when cname in (
        'チューハイ・カクテル', 'コーヒー飲料', '炭酸飲料', '果実・野菜飲料', 'その他 飲料',
        'お茶', '紅茶飲料', '乳酸菌飲料・はっ酵乳', 'ビール', '発泡酒・新ジャンル',
        '豆乳飲料', 'インスタントコーヒー', 'スポーツ・健康飲料', 'フレーバーウォーター', '機能系茶飲料',
        'ノンアルチューハイ・カクテル', 'ノンアルコールビール', 'ベビー用飲み物', 'ハイボール','ドリップコーヒー', 'コンビニ コーヒー') then '飲料'
      when cname in (
        'カップラーメン', 'カップそば・うどん', '袋麺', '冷凍パスタ', 'カップスープ', 'カップスープ（春雨など）',
        'チルド麺ラーメン・冷し中華', '具材入り冷凍ラーメン', 'カップソース焼きそば', 'カップ焼きそば その他',
        'チルド麺焼きそば', 'チルド麺うどん 麺のみ', '冷凍うどん 麺のみ', 'チルド麺うどん 汁あり',
        'チルド麺そば 麺のみ', 'チルド麺そば 汁あり', 'サラダ麺',
        'カレー レトルト', 'カレー ルウ', 'パスタソース', 'みそ汁', '料理の素 中華',
        '料理の素 和食', '料理の素 洋食 他','鍋つゆ', 'スープ レトルト',
        'ウインナー・ソーセージ', 'ハム', 'ベーコン','魚肉ソーセージ','冷凍餃子・焼売','冷凍唐揚げ', '冷凍ピザ', '冷凍米飯', '冷凍総菜 その他',
        'ヨーグルト', 'チーズ', '牛乳・加工乳', 'バター', 'マーガリン', 'ジャム',
        'ふりかけ', '納豆', 'ベビー用ご飯','ベビー用おやつ', 'ベビー用ソープ','ベビー用ローション','ベビー用オイル','ベビー用クリーム') then '食品'
      when cname in (
        '衣料用洗剤', 'ハミガキ', '柔軟剤', '食器用洗剤', 'シャンプー', 'ボディソープ', 'ハンドソープ', '洗顔料',
        'ボディミルク', 'ボディクリーム', 'トイレ用洗剤', 'コンディショナー・トリートメント', '衣料用漂白剤', 'マルチクリーナー',
        'デンタルリンス・液体ハミガキ', '歯ブラシ', 'デンタルフロス・歯間ブラシ','除菌スプレー', '除菌シート','フローリング掃除シート', '食洗機用洗剤',
        'おむつ', 'おしりふき', '色無しリップクリーム','色付きリップクリーム','ハンドクリーム','のどあめ','ガム', '入浴剤','消臭スプレー','デオドラント その他',
        'デオドラント シート','デオドラント スプレー','デオドラント ミスト', '虫よけ・殺虫剤','トイレ用消臭・芳香剤','箱ティッシュ', 'トイレットペーパー') then '日用品'
    else 'その他'
    end as cname,
  from `ShareView_tb`
)
, cname_cnt as (
select 
  cname, 
  case when pref = "" then "回答なし" else pref end pref,
  round(avg(rating),2) avg, min(rating) mi_r,
  APPROX_QUANTILES(rating, 100)[OFFSET(25)] as per25, round(bqutil.fn.median(array_agg(rating)),2) as med,
  APPROX_QUANTILES(rating, 100)[OFFSET(75)] as per75, max(rating) mx_r, count(0) as cnt, sum(count(0)) over() as total_cnt
from tb
where cname in ("お菓子・デザート", "飲料", "食品")
and age != "" and age != "-"
group by 1,2
)

select
  cname, pref, cnt, total_cnt,avg, mi_r, per25, med, per75, mx_r
from cname_cnt
order by sum(cnt) over(partition by cname) desc, cnt desc

当社の強み、サービス紹介

当社は、以下のサービスを通じて、お客様の業務の効率化をサポートします。

• スクレイピングの受託：お客様のニーズに合わせて、様々なサイトからデータを収集します。
• データの収集・分析：豊富な経験と高度な技術力で、データから隠れたインサイトを抽出します。
• 分析レポートの作成と可視化：専門知識がない方でも理解しやすい、視覚的なレポートを作成します。

当社はデータのプロフェッショナルとして、お客様のビジネスの成長をサポートいたします。ぜひお気軽にご相談ください。

まとめ

口コミデータは顧客インサイトを知れる貴重なデータです。
正しいデータ集計のアプローチを行うことで、以下のメリットが得られます。

• ターゲット顧客のニーズの深い理解
• 効果的なマーケティング戦略を立案
• 顧客体験を最大化

お問い合わせ

本記事で提示したデータや集計結果はあくまで一例です。
お客様のビジネスの状況や課題に合わせて、より詳細な分析やカスタマイズされたソリューションをご提案できます。データ分析に関するご質問やご相談がありましたらお気軽にお問い合わせください。