【データ集計】ShareView口コミデータをBigQueryで集計

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

【対象者】

初中級データアナリスト
マーケティング担当者

【興味関心】

1. 効果的な口コミデータの集計方法が知りたい
2. 自社商品・競合商品の評価を知りたい
3. 実践的なBigQueryのコードが知りたい

【解消できるお悩み】

1. 自社商品の開発・改善
2. 効果的なマーケティングプランの立案
3. 効果的な集計クエリの作成

はじめに

今回は国内最大級のレビュープラットフォーム「ShareView」に蓄積された58万件もの口コミデータを集計しました。

本ブログでは弊社だからこそ提供できるデータ集計・スクレイピングの技術を駆使し、口コミデータの具体的かつ実践的なBigQueryのクエリを紹介します。

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

あわせて読みたい

【データ集計】スクレイピングで取得したShareView口コミデータの基礎集計 記事の対象者と解消できるお悩み 【対象者】 初中級データアナリストマーケティング担当者 【興味関心】 顧客の声が知りたい 効果的な口コミデータの集計方法が知りたい...

データ概要

1. データ内容

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

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

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

2. 実際のデータ

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

データの前処理

1. データの前処理の必要性

ShareViewの口コミデータはスクレイピングで収集した生データであるため、BigQueryでの集計にそのまま使用するには、データ品質の面でいくつかの課題があります。

• JSON形式データの構造化：spec、user_info、review_infoカラムはJSON形式でデータが格納されており、そのままでは個別の情報を抽出して集計することが困難。
• データ型の変換：集計はデータを数値として扱うことが多いため、データ型が適切でないとクエリの実行時にエラーが発生します。
• カラムの分割：カラムに複数の情報がまとめて格納されている場合、個別の情報を抽出して分析することが困難です。
• 日付の変換：日付に関する集計や分析を行うには、カラムが適切なDATE型またはTIMESTAMP型である必要があります。

2. 処理手順

実際のデータに対して実行したクエリが以下クエリです。

select
  "https://shareview.jp"||id as url,
  title,
  description as de,
  dt,
  "https://shareview.jp"||string(user_info.id) as uurl,
  string(user_info.name) as uname,
  split(string(user_info.attribute), "／")[offset(0)] as age,
  split(string(user_info.attribute), "／")[offset(1)] as gender,
  split(string(user_info.attribute), "／")[offset(2)] as pref,
  int64(user_info.cup) as cup,
  bool(user_info.is_master_reviewer) as is_master_reviewer,
  bool(user_info.is_deleted) as is_deleted,
  string(review_info.title) as rtitle,
  string(review_info.body) as body,
  timestamp(replace(string(review_info.created_at), ".", "-")) as review_public_dt,
  string(review_info.id) as rid,
  string(review_info.location) as loc,
  ifnull(safe.float64(review_info.rating), 0) as rating,
  string(spec.category_id) as ctg,
  string(spec.category_name) as cname,
  regexp_replace(replace(string(spec.created_at), ".", "-"), "登録日：-|発売日：-", "1970-01-01 00:00:00") as sales_public_dt,
  int64(spec.num_reviews) as num_reviews,
  float64(spec.overall_rating) as overall_rating,
  string(spec.overall_rating_description) as overall_rating_description
from `ShareView_tb`

1. idとURLの結合

"https://shareview.jp"||id as url,

• idカラムに”https://shareview.jp”を結合して完全なURLを作成し、`url`という名前で新しいカラムに格納します。

id	url
/item/detail/13439	https://shareview.jp/item/detail/13439
/item/detail/13456	https://shareview.jp/item/detail/13456
/item/detail/13456	https://shareview.jp/item/detail/13456

2. JSON形式からのデータ抽出

string(user_info.name) as uname,

• user_infoカラム内の name を文字列として抽出し、unameという名前で新しいカラムに格納します。
• user_info.id、spec.category_id、spec.category_name など、JSON内の他のキーについても同様の処理を行います。

user_info	uname
{“attribute”:”40代／女性／大阪府”,”cup”:39,”id”:”/reviewer/detail/21313″,”is_deleted”:false,”is_master_reviewer”:true,”name”:”ゆきんこたんてい”}	ゆきんこたんてい
{“attribute”:”20代／男性／青森県”,”cup”:0,”id”:”/reviewer/detail/36962″,”is_deleted”:false,”is_master_reviewer”:false,”name”:”よさく”}	よさく

3. 文字列分割によるユーザー属性抽出

split(string(user_info.attribute), "／")[offset(0)] as age,
split(string(user_info.attribute), "／")[offset(1)] as gender,
split(string(user_info.attribute), "／")[offset(2)] as pref,

• user_info.attributeカラムの文字列を”／”で分割し、最初の要素（年齢）をageカラムに格納します。
• 同様に、性別、都道府県も抽出します。

user_info	age	gender	pref
{“attribute”:”40代／女性／大阪府”,”cup”:39,”id”:”/reviewer/detail/21313″,”is_deleted”:false,”is_master_reviewer”:true,”name”:”ゆきんこたんてい”}	40代	女性	大阪府
{“attribute”:”20代／男性／青森県”,”cup”:0,”id”:”/reviewer/detail/36962″,”is_deleted”:false,”is_master_reviewer”:false,”name”:”よさく”}	20代	男性	青森県

データ型の変換

min(ifnull(safe.float64(review_info.rating), 0)) as mi_rating,
max(ifnull(safe.float64(review_info.rating), 0)) as max_rating,
round(avg(ifnull(safe.float64(review_info.rating), 0)),1) as avg_rating,

• spec.num.reviewsカラムの値をINT64型に変換します。
• safe.float64(review_info.rating)でnull許容のFloat64型へ変換し、ifnullでnull値は0に変換
• STRINGからINT型、FLOAT型に変換することで平均値や最大値など集計が可能になります。

mi_rating	mx_rating	avg_rating
0	5	4.1

日付形式の変換

timestamp(replace(string(review_info.created_at), ".", "-")) as review_public_dt,

• review_info.created_atカラムの形式を “YYYY-MM-DD HH:MM:SS” に変換し、TIMESTAMP型に変換します。
• レビューの投稿日時を時系列分析に使用する場合、文字列型ではなくTIMESTAMP型である必要があります。

created_at	review_public_dt
2023.07.23 10:53:40	2023-07-23 10:53:40.000000 UTC
2024.05.08 06:30:31	2024-05-08 06:30:31.000000 UTC
2024.02.05 21:41:42	2024-02-05 21:41:42.000000 UTC

regexp_replace(replace(string(spec.created_at), ".", "-"), "登録日：-|発売日：-", "1970-01-01 00:00:00") as sales_public_dt,

• spec.created_at カラムには、日付情報が欠損している場合や、日付以外の文字列（”登録日：”, “発売日：”など）が含まれている場合があります。
• これらの値をそのままTIMESTAMP型に変換しようとするとエラーが発生するため、特定の日付に置き換える必要があります。

created_at	sales_public_dt
発売日：-	1970-01-01 0:00:00
登録日：2024.04.30	登録日：2024-04-30

3. 前処理前後のデータの変化

実際にクエリを実行する前後で、以下のようなデータに差が現れます。　

以下が前処理前のデータです。

id	title	status	spec	description	user_info	review_info	dt	category_id
/item/detail/13439	カンロ 岩塩レモン&ライム さわやかミントの香り	0	{“category_id”:”/category/list/341″,”category_name”:”あめ・キャンディ”,”created_at”:”発売日：-“,”num_reviews”:1,”overall_rating”:4,”overall_rating_description”:”リフレッシュ感|0%\tコスパがいい|0%\tリピートしたい|0%”}	メーカー：カンロ 内容量：62g（個装紙込み)	{“attribute”:”30代／女性／岡山県”,”cup”:1,”id”:”/reviewer/detail/9271″,”is_deleted”:false,”is_master_reviewer”:false,”name”:”ゆりちゃんママ”}	{“body”:”小さいキャンディーなので、かさばらずよいです。ライムとレモンの味ですっきりと食べれます。レモン味の方は塩の味が少し強く感じましたが、他にはない味だなと感じました。”,”created_at”:”2016.09.21 12:20:55″,”id”:”45471″,”label”:””,”location”:””,”picture”:”/post/pics/thumb/45471s.jpg”,”rating”:4,”title”:””}	2024-04-26	/category/list/341

以下が前処理後のデータです。　

url	title	de	dt	uurl	uname	age	gender	pref	cup	is_master_reviewer	is_deleted	rtitle	body	review_public_dt	rid	loc	rating	ctg	cname	sales_public_dt	num_reviews	overall_rating	overall_rating_description
https://shareview.jp/item/detail/13439	カンロ 岩塩レモン&ライム さわやかミントの香り	メーカー：カンロ 内容量：62g（個装紙込み)	2024-04-25	https://shareview.jp/reviewer/detail/9271	ゆりちゃんママ	30代	女性	岡山県	1	FALSE	FALSE		小さいキャンディーなので、かさばらずよいです。ライムとレモンの味ですっきりと食べれます。レモン味の方は塩の味が少し強く感じましたが、他にはない味だなと感じました。	2016-09-21 12:20:55.000000 UTC	45471		4	/category/list/341	あめ・キャンディ	1970-01-01 0:00:00	1	4	リフレッシュ感|0% コスパがいい|0% リピートしたい|0%

基礎集計クエリ

口コミデータは様々な角度から集計することで、顧客のリアルな声と商品に対する評価を包括的に見える化できます。
今回は以下の基礎集計を行いました。

• 時系列：口コミ数の推移、季節ごとの評価の変動などを把握できます。
• ユーザー属性：年齢層、性別ごとの評価を分析できます。
• 地域： 地域ごとの人気商品やサービスの傾向を把握できます。
• 評価：評価の分布、平均評価などを確認できます。
• 商品カテゴリ： 各カテゴリの口コミ数、評価などを比較できます。
• メーカー：各メーカーの口コミ数、評価などを比較できます。

時系列

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

年次口コミ数の集計クエリ

select 
  year, cnt, total_cnt,
  sum(cnt) over(order by year) as cum_cnt,
  round(cnt/total_cnt * 100, 1) as rate,
  round(sum(cnt) over(order by year)/total_cnt * 100, 1) as cum_rate,
  avg, med
from (
  select
    format_date("%Y",review_public_dt) as year,
    round(avg(rating),2) as avg,
    round(bqutil.fn.median(array_agg(rating)),2) as med,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
) 

月次口コミ数の集計クエリ

select 
  month, cnt, total_cnt,
  sum(cnt) over(order by month) as cum_cnt,
  round(cnt/total_cnt * 100, 1) as rate,
  round(sum(cnt) over(order by month)/total_cnt * 100, 1) as cum_rate,
  avg, med
from (
  select
    format_date("%Y/%m",review_public_dt) as month,
    round(avg(rating),2) as avg,
    round(bqutil.fn.median(array_agg(rating)),2) as med,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
) 

日次口コミ数の集計クエリ

select
  format_date("%Y/%m/%d",review_public_dt) as dt,
  count(0) as cnt,
from `ShareView_tb`
group by 1
order by 1

マーケティングへの応用

仮説から事実を検証し、マーケティングに応用する。

• 仮説①：2017, 2018年に口コミ数が増加しているが何か施策を行っているのか？
• 仮説②：2018年5月と2023年5月の口コミ数が急速に増加しているが何か施策を行っているのか？
• 事実①：2018年8月20日にアプリをリリース　※下記URL①参照
• 事実②：2018年5月にキャンペーンを実施　　※下記URL②参照

• URL①：商品レビューが25万件を突破した『シェアビュー』、iOSアプリをリリース
• URL②：【5月限定】ボーナスポイントキャンペーン！合計最大5000ポイント／月！

上記から以下のマーケティング施策が考えられる。

• キャンペーン期間、ポイント付与額、対象商品などを最適化し、口コミを促進しやすい設計にする。
• アプリ内でのキャンペーン告知を強化し、より多くのユーザーにキャンペーン情報を届ける。
• プッシュ通知を活用し、キャンペーン情報やレビュー投稿依頼をタイムリーに配信する。

ユーザー属性

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

年代別口コミ数の集計クエリ

select 
  age, cnt, total_cnt,
  sum(cnt) over(order by cnt desc) as cum_cnt,
  round(cnt/total_cnt * 100, 2) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 2) as cum_rate,
  avg, med
from (
  select
    case 
      when age = "" or age = "-" then "不明"
      when age = "10代未満" then "10代"
      when age = "60代～" then "60代"
      else age end as age,
    round(avg(rating),2) as avg,
    round(bqutil.fn.median(array_agg(rating)),2) as med,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
)

age	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)	avg	med
30代	217803	584058	217803	37.29	37.29	4.16	4.2
40代	166887	584058	384690	28.57	65.87	4.14	4.1
50代	87435	584058	472125	14.97	80.84	4.16	4.2
20代	60069	584058	532194	10.28	91.12	4.2	4.25
60代	31293	584058	563487	5.36	96.48	4.23	4.25
不明	16295	584058	579782	2.79	99.27	4.16	4
10代	4276	584058	584058	0.73	100	4.18	4.25

性別口コミ数の集計クエリ

select 
  gender, cnt, total_cnt,
  sum(cnt) over(order by cnt desc) as cum_cnt,
  round(cnt/total_cnt * 100, 2) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 2) as cum_rate,
  avg, med
from (
  select
    case 
      when gender = "-" then "不明"
      else gender end as gender,
    round(avg(rating),2) as avg,
    round(bqutil.fn.median(array_agg(rating)),2) as med,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
)

gender	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)	avg	med
女性	436648	584058	436648	74.76	74.76	4.18	4.2
男性	140131	584058	576779	23.99	98.75	4.13	4
不明	7279	584058	584058	1.25	100	4.06	4

マーケティングへの応用

集計結果から以下がわかる。

• 30代～50代が全体の80.84%を占める。　※画像1参照
• 30代が最も多く、次いで40代、50代と続く。　※画像1参照
• 女性が全体の74.76%を占める。　※画像3参照
• 女性の評価が4.2であるのに対し、男性の評価は4.0と、男性の方が0.2評価が低い。　※画像4参照

この集計結果から、以下の仮説を立てることができます。

• 30代～50代向けのマーケティング戦略を強化することで、より効果的な成果を期待できる。
• 女性は口コミ投稿に積極的な傾向があると考えられる。
• 女性向けのマーケティング戦略を強化することで、より効果的な成果を期待できる。
• 男性と女性で重視するポイントが異なるため、評価に差が出ている可能性がある。

地域別

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

地方別の集計クエリ

select 
  region, cnt, total_cnt,
  sum(cnt) over(order by cnt desc) as cum_cnt,
  round(cnt/total_cnt * 100, 1) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 1) as cum_rate,
  avg, med
from (
  select
    case
      when pref in ('北海道') THEN '北海道'
      when pref in ('青森県', '岩手県', '秋田県', '宮城県', '山形県', '福島県') then '東北'
      when pref in ('茨城県', '栃木県', '群馬県', '埼玉県', '千葉県', '東京都', '神奈川県') then '関東'
      when pref in ('新潟県', '富山県', '石川県', '福井県', '山梨県', '長野県', '岐阜県', '静岡県', '愛知県') then '中部'
      when pref in ('三重県', '滋賀県', '京都府', '大阪府', '兵庫県', '奈良県', '和歌山県') then '近畿'
      when pref in ('鳥取県', '島根県', '岡山県', '広島県', '山口県', '徳島県', '香川県', '愛媛県', '高知県') then '中国・四国'
      when pref in ('福岡県', '佐賀県', '長崎県', '熊本県', '大分県', '宮崎県', '鹿児島県', '沖縄県') then '九州・沖縄'
    else '不明'
    end as region,
    round(avg(rating),2) as avg,
    round(bqutil.fn.median(array_agg(rating)),2) as med,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
)

region	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)	avg	med
関東	254394	584058	254394	43.56	43.56	4.14	4.1
近畿	139602	584058	393996	23.9	67.46	4.2	4.25
中部	88060	584058	482056	15.08	82.54	4.14	4.17
九州・沖縄	39031	584058	521087	6.68	89.22	4.17	4.2
中国・四国	31638	584058	552725	5.42	94.64	4.2	4.25
東北	17525	584058	570250	3	97.64	4.16	4.2
北海道	13807	584058	584057	2.36	100	4.25	4.25
不明	1	584058	584058	0	100	5	5

都道府県別の集計クエリ

select 
  pref, cnt, total_cnt,
  sum(cnt) over(order by cnt desc) as cum_cnt,
  round(cnt/total_cnt * 100, 1) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 1) as cum_rate,
  avg, mi_r, per25, med, per75, mx_r
from (
  select
    case 
    when pref = "" then "不明"
    else pref end as pref,
    round(avg(rating),2) as 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 `ShareView_tb`
  group by 1
)

pref	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)	avg	mi_r	per25	med	per75	mx_r
東京都	116374	584058	116374	19.9	19.9	4.09	0	3.75	4	4.5	5
大阪府	55731	584058	172105	9.5	29.5	4.22	0.8	4	4.25	4.75	5
愛知県	48233	584058	220338	8.3	37.7	4.11	1	3.75	4	4.5	5
神奈川県	45778	584058	266116	7.8	45.6	4.18	0	4	4.2	4.6	5
埼玉県	44050	584058	310166	7.5	53.1	4.19	1	4	4.2	4.6	5
兵庫県	35508	584058	345674	6.1	59.2	4.2	1	4	4.25	4.6	5
千葉県	29038	584058	374712	5	64.2	4.22	0	4	4.25	4.75	5
福岡県	21793	584058	396505	3.7	67.9	4.17	0	4	4.25	4.5	5
三重県	18708	584058	415213	3.2	71.1	4.21	0.5	4	4.25	4.5	5
北海道	13807	584058	429020	2.4	73.5	4.25	0	4	4.25	4.75	5
新潟県	12354	584058	441374	2.1	75.6	4.19	1	4	4.25	4.6	5
京都府	10271	584058	451645	1.8	77.3	4.22	1	4	4.25	4.67	5
広島県	9390	584058	461035	1.6	78.9	4.31	0	4	4.25	5	5
宮城県	8863	584058	469898	1.5	80.5	4.15	1	4	4.2	4.5	5
栃木県	8655	584058	478553	1.5	81.9	4.22	0.1	4	4.25	4.67	5
滋賀県	7924	584058	486477	1.4	83.3	3.91	1	3.4	4	4.4	5
静岡県	7189	584058	493666	1.2	84.5	4.14	1.33	3.8	4.2	4.5	5
山口県	7071	584058	500737	1.2	85.7	4	1	3.75	4	4.33	5
和歌山県	6790	584058	507527	1.2	86.9	4.2	1	4	4.25	4.6	5
岡山県	6371	584058	513898	1.1	88	4.28	1	4	4.33	4.75	5
群馬県	5920	584058	519818	1	89	3.96	1	3.67	4	4.25	5
岐阜県	5023	584058	524841	0.9	89.9	4.23	1	4	4.33	4.67	5
奈良県	4670	584058	529511	0.8	90.7	4.39	1	4	4.5	4.8	5
長野県	4668	584058	534179	0.8	91.5	4.21	1	4	4.25	4.67	5
茨城県	4579	584058	538758	0.8	92.2	4.18	0	4	4.2	4.5	5
沖縄県	3937	584058	542695	0.7	92.9	4.31	1	4	4.25	4.75	5
香川県	3763	584058	546458	0.6	93.6	4.33	0	4	4.33	4.8	5
山梨県	3714	584058	550172	0.6	94.2	4.22	1.8	4	4.25	4.5	5
鹿児島県	3425	584058	553597	0.6	94.8	4.19	1	4	4	4.5	5
長崎県	3398	584058	556995	0.6	95.4	4.11	1	3.8	4	4.5	5
富山県	3212	584058	560207	0.5	95.9	4.24	2	4	4.25	4.6	5
青森県	2999	584058	563206	0.5	96.4	4.17	2.17	4	4.2	4.5	5
熊本県	2849	584058	566055	0.5	96.9	4.07	1	3.75	4	4.5	5
石川県	2579	584058	568634	0.4	97.4	4.14	1	4	4	4.5	5
大分県	1703	584058	570337	0.3	97.7	4.15	1	3.75	4.17	4.6	5
秋田県	1700	584058	572037	0.3	97.9	4.35	1	4	4.5	5	5
高知県	1549	584058	573586	0.3	98.2	4.28	2	4	4.25	4.75	5
福島県	1533	584058	575119	0.3	98.5	4.28	1	4	4.33	4.75	5
鳥取県	1485	584058	576604	0.3	98.7	3.87	1	3.5	4	4.33	5
岩手県	1441	584058	578045	0.2	99	3.88	1	3.5	4	4.25	5
愛媛県	1183	584058	579228	0.2	99.2	4.15	1	4	4.2	4.5	5
福井県	1088	584058	580316	0.2	99.4	4.03	2	3.75	4	4.5	5
佐賀県	1006	584058	581322	0.2	99.5	4.08	2	3.75	4	4.5	5
山形県	989	584058	582311	0.2	99.7	4.14	1	3.8	4.2	4.5	5
宮崎県	920	584058	583231	0.2	99.9	4.36	2.67	4	4.5	4.75	5
徳島県	667	584058	583898	0.1	100	4.08	1.5	3.75	4	4.5	5
島根県	159	584058	584057	0	100	4.12	1.67	3.8	4.25	4.5	5
不明	1	584058	584058	0	100	5	5	5	5	5	5

マーケティングへの応用

集計結果から以下がわかる。

• 関東・近畿・中部地方で82.54%を占める。　※画像1参照
• 関東地方の評価が他の地方と比べ低い。　※画像2参照
• 評価(中央値)が同じ群馬県と滋賀県は箱ひげ図で比較すると滋賀県に評価のばらつきが見られる。　※画像4参照

この集計結果から、以下の仮説を立てることができます。

• 関東・近畿・中部地方の顧客に特化したターゲティング広告を配信する。
• 滋賀県における口コミの内容を詳細に分析し、具体的な改善点を見つけ出す。

評価

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

評価別の集計クエリ

select 
  rating, cnt, total_cnt,
  sum(cnt) over(order by cnt desc) as cum_cnt,
  round(cnt/total_cnt * 100, 2) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 2) as cum_rate,
from (
  select
    case when  0 <= rating and rating < 1 then "0"
       when  1 <= rating and rating < 2 then "1"
       when  2 <= rating and rating < 3 then "2"
       when  3 <= rating and rating < 4 then "3"
       when  4 <= rating and rating < 5 then "4" 
       when  rating = 5 then "5"
       else "0" end as rating,
    count(0) as cnt,
    sum(count(0)) over() as total_cnt
  from `ShareView_tb`
  group by 1
)

rating	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)
4	361701	584058	361701	61.93	61.93
3	131040	584058	492741	22.44	84.37
5	79860	584058	572601	13.67	98.04
2	10248	584058	582849	1.75	99.79
1	1168	584058	584017	0.2	99.99
0	41	584058	584058	0.01	100

マーケティングへの応用

集計結果から以下がわかる。

• 評価(平均値)が4,5の整数に集中している。　※画像1参照
• 評価が3から5の口コミ数を合計すると、全体の98.04%を占める。　※画像2参照

この集計結果から、以下の仮説を立てることができます。

• 高評価レビューを引用した広告を作成し、信頼性を高めます。
• レビューサイトや比較サイトへの露出を増やし、製品の認知度を高めます。

商品カテゴリ

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

カテゴリ別の集計クエリ

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

category	cnt	total_cnt	cum_cnt	rate(%)	cum_r(%)	avg	med
お菓子・デザート	237008	584058	237008	40.58	40.58	4.23	4.25
飲料	143295	584058	380303	24.53	65.11	4.05	4
食品	114433	584058	494736	19.59	84.71	4.14	4.17
その他	56725	584058	551461	9.71	94.42	4.21	4.25
日用品	32597	584058	584058	5.58	100	4.17	4.2

マーケティングへの応用

集計結果から以下がわかる。

• お菓子・デザート、飲料、食品で口コミ全体の84.71%を占める。　※画像1参照
• 他のカテゴリと比べて飲料の評価が4.0と低い。　※画像2参照

この集計結果から、以下の仮説を立てることができます。

• 飲料の低評価レビューを詳細に分析し、具体的な不満点を洗い出す。
• 競合製品のレビューと比較し、自社製品の弱点を特定する。

メーカー

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

メーカー別の集計クエリ

select 
  maker, cnt, total_cnt,
  sum(cnt) over(order by cnt desc, maker) as cum_cnt,
  round(cnt/total_cnt * 100, 1) as rate,
  round(sum(cnt) over(order by cnt desc)/total_cnt * 100, 1) as cum_rate,
  avg, mi_r, per25, med, per75, mx_r
from (
  select
    v as maker,
    round(avg(rating),2) as 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 `ShareView_tb`
  where k = "メーカー"
  group by 1
) 

マーケティングへの応用

集計結果から以下がわかる。

• 全体の518メーカー中19メーカーで全体の50.75%を占める。　※画像1参照
• キリンビバレッジは評価(中央値)と第3四分位数の差が大きい。　※画像2参照

この集計結果から、以下の仮説を立てることができます。

• 高評価を得ている製品やサービスの成功要因を分析し、他の製品やサービスにも応用する。
• 競合他社の人気商品の動向を常に把握し、自社の戦略に反映させる。

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

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

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

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

まとめ

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

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

お問い合わせ

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