{"queries":[{"id":"pareto_curve","compiledQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n sales,\n ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn,\n COUNT(*) OVER () AS total_n,\n SUM(sales) OVER () AS total_sales\n FROM customer_sales\n)\n\nSELECT\n ROUND(rn * 100.0 / total_n, 1) AS customer_pct,\n ROUND(SUM(sales) OVER (ORDER BY rn) * 100.0 / total_sales, 1) AS sales_pct,\n ROUND(rn * 100.0 / total_n, 1) AS line_45\nFROM ranked\nORDER BY rn","inputQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n sales,\n ROW_NUMBER() OVER (ORDER BY sales DESC) AS rn,\n COUNT(*) OVER () AS total_n,\n SUM(sales) OVER () AS total_sales\n FROM customer_sales\n)\n\nSELECT\n ROUND(rn * 100.0 / total_n, 1) AS customer_pct,\n ROUND(SUM(sales) OVER (ORDER BY rn) * 100.0 / total_sales, 1) AS sales_pct,\n ROUND(rn * 100.0 / total_n, 1) AS line_45\nFROM ranked\nORDER BY rn","compiled":false,"inline":false},{"id":"segment_share","compiledQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n customer_id,\n sales,\n NTILE(10) OVER (ORDER BY sales DESC) AS sales_decile\n FROM customer_sales\n),\n\nsegmented AS (\n SELECT\n customer_id,\n sales,\n CASE\n WHEN sales_decile = 1 THEN 'ロイヤル'\n WHEN sales_decile <= 5 THEN '中堅'\n ELSE 'ライト'\n END AS segment\n FROM ranked\n),\n\nagg AS (\n SELECT\n segment,\n COUNT(*) AS n,\n SUM(sales) AS total_sales\n FROM segmented\n GROUP BY segment\n),\n\nshares AS (\n SELECT\n segment,\n ROUND(n * 100.0 / SUM(n) OVER (), 1) AS customer_share_pct,\n ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 1) AS sales_share_pct\n FROM agg\n)\n\nSELECT\n '人数比' AS metric,\n segment,\n customer_share_pct AS value\nFROM shares\nUNION ALL\nSELECT\n '売上シェア' AS metric,\n segment,\n sales_share_pct AS value\nFROM shares\nORDER BY metric ASC, segment ASC","inputQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n customer_id,\n sales,\n NTILE(10) OVER (ORDER BY sales DESC) AS sales_decile\n FROM customer_sales\n),\n\nsegmented AS (\n SELECT\n customer_id,\n sales,\n CASE\n WHEN sales_decile = 1 THEN 'ロイヤル'\n WHEN sales_decile <= 5 THEN '中堅'\n ELSE 'ライト'\n END AS segment\n FROM ranked\n),\n\nagg AS (\n SELECT\n segment,\n COUNT(*) AS n,\n SUM(sales) AS total_sales\n FROM segmented\n GROUP BY segment\n),\n\nshares AS (\n SELECT\n segment,\n ROUND(n * 100.0 / SUM(n) OVER (), 1) AS customer_share_pct,\n ROUND(total_sales * 100.0 / SUM(total_sales) OVER (), 1) AS sales_share_pct\n FROM agg\n)\n\nSELECT\n '人数比' AS metric,\n segment,\n customer_share_pct AS value\nFROM shares\nUNION ALL\nSELECT\n '売上シェア' AS metric,\n segment,\n sales_share_pct AS value\nFROM shares\nORDER BY metric ASC, segment ASC","compiled":false,"inline":false},{"id":"segment_metrics","compiledQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales,\n COUNT(*) AS visits,\n ROUND(SUM(amount) * 1.0 / COUNT(*), 0) AS avg_unit\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n customer_id,\n sales,\n visits,\n avg_unit,\n NTILE(10) OVER (ORDER BY sales DESC) AS sales_decile\n FROM customer_sales\n),\n\nsegmented AS (\n SELECT\n customer_id,\n sales,\n visits,\n avg_unit,\n CASE\n WHEN sales_decile = 1 THEN 'ロイヤル'\n WHEN sales_decile <= 5 THEN '中堅'\n ELSE 'ライト'\n END AS segment\n FROM ranked\n)\n\nSELECT\n segment,\n ROUND(AVG(avg_unit), 0) AS avg_transaction,\n ROUND(AVG(visits), 1) AS avg_visits\nFROM segmented\nGROUP BY segment\nORDER BY\n CASE segment\n WHEN 'ロイヤル' THEN 1\n WHEN '中堅' THEN 2\n WHEN 'ライト' THEN 3\n END ASC","inputQueryString":"WITH customer_sales AS (\n SELECT\n customer_id,\n SUM(amount) AS sales,\n COUNT(*) AS visits,\n ROUND(SUM(amount) * 1.0 / COUNT(*), 0) AS avg_unit\n FROM retail.receipt\n WHERE customer_id <> 'ZZ000000000000'\n GROUP BY customer_id\n),\n\nranked AS (\n SELECT\n customer_id,\n sales,\n visits,\n avg_unit,\n NTILE(10) OVER (ORDER BY sales DESC) AS sales_decile\n FROM customer_sales\n),\n\nsegmented AS (\n SELECT\n customer_id,\n sales,\n visits,\n avg_unit,\n CASE\n WHEN sales_decile = 1 THEN 'ロイヤル'\n WHEN sales_decile <= 5 THEN '中堅'\n ELSE 'ライト'\n END AS segment\n FROM ranked\n)\n\nSELECT\n segment,\n ROUND(AVG(avg_unit), 0) AS avg_transaction,\n ROUND(AVG(visits), 1) AS avg_visits\nFROM segmented\nGROUP BY segment\nORDER BY\n CASE segment\n WHEN 'ロイヤル' THEN 1\n WHEN '中堅' THEN 2\n WHEN 'ライト' THEN 3\n END ASC","compiled":false,"inline":false},{"id":"member_nonmember_mix","compiledQueryString":"WITH base AS (\n SELECT\n CASE\n WHEN r.customer_id <> 'ZZ000000000000' THEN '会員'\n ELSE '非会員'\n END AS segment,\n c.category_major_name,\n SUM(r.amount) AS total_sales\n FROM retail.receipt r\n INNER JOIN retail.product p ON r.product_cd = p.product_cd\n INNER JOIN retail.category c ON p.category_small_cd = c.category_small_cd\n GROUP BY segment, c.category_major_name\n)\n\nSELECT\n segment,\n category_major_name,\n total_sales,\n ROUND(total_sales * 100.0 / SUM(total_sales) OVER (PARTITION BY segment), 1)\n AS share_pct\nFROM base\nORDER BY segment ASC, total_sales DESC","inputQueryString":"WITH base AS (\n SELECT\n CASE\n WHEN r.customer_id <> 'ZZ000000000000' THEN '会員'\n ELSE '非会員'\n END AS segment,\n c.category_major_name,\n SUM(r.amount) AS total_sales\n FROM retail.receipt r\n INNER JOIN retail.product p ON r.product_cd = p.product_cd\n INNER JOIN retail.category c ON p.category_small_cd = c.category_small_cd\n GROUP BY segment, c.category_major_name\n)\n\nSELECT\n segment,\n category_major_name,\n total_sales,\n ROUND(total_sales * 100.0 / SUM(total_sales) OVER (PARTITION BY segment), 1)\n AS share_pct\nFROM base\nORDER BY segment ASC, total_sales DESC","compiled":false,"inline":false}]}

マーケ / CRM 担当

顧客累積売上カーブ (パレート分析)

ロイヤル / 中堅 / ライト の人数比 vs 売上シェア

セグメント別 平均客単価

セグメント別 平均来店回数

会員 / 非会員 カテゴリミックス

ロイヤル / 中堅 / ライトの人数比 vs 売上シェア

セグメント別平均客単価

セグメント別平均来店回数

会員 / 非会員カテゴリミックス