1. 基本サマリー（再来店率）
WITH visit_dates AS (
SELECT DISTINCT
user_id,
DATE(TIMESTAMP(created_at), "Asia/Tokyo") AS visit_date
FROM aledata.mobileorder.product_orders
WHERE shop_id = 92
AND status = 5
AND deleted_at IS NULL
),
user_visits AS (
SELECT
user_id,
COUNT(DISTINCT visit_date) AS visit_days
FROM visit_dates
GROUP BY user_id
)
SELECT
COUNT(*) AS total_customers,
SUM(CASE WHEN visit_days >= 2 THEN 1 ELSE 0 END) AS revisitors,
ROUND(SAFE_DIVIDE(SUM(CASE WHEN visit_days >= 2 THEN 1 ELSE 0 END), COUNT(*)) * 100, 2) AS retention_rate
FROM user_visits
2. 月別再来店率推移
WITH visit_dates AS (
SELECT DISTINCT
user_id,
DATE(TIMESTAMP(created_at), "Asia/Tokyo") AS visit_date
FROM aledata.mobileorder.product_orders
WHERE shop_id = 92
AND status = 5 AND deleted_at IS NULL
),
first_visits AS (
SELECT user_id, MIN(visit_date) AS first_visit_date
FROM visit_dates
GROUP BY user_id
),
user_visit_counts AS (
SELECT user_id, COUNT(DISTINCT visit_date) AS total_visit_days
FROM visit_dates
GROUP BY user_id
),
combined AS (
SELECT
FORMAT_DATE('%Y-%m', fv.first_visit_date) AS first_visit_month,
fv.user_id,
CASE WHEN uvc.total_visit_days >= 2 THEN 1 ELSE 0 END AS is_revisitor
FROM first_visits fv
JOIN user_visit_counts uvc ON fv.user_id = uvc.user_id
)
SELECT
first_visit_month,
COUNT(*) AS new_customers,
SUM(is_revisitor) AS revisitors,
ROUND(SAFE_DIVIDE(SUM(is_revisitor), COUNT(*)) * 100, 2) AS retention_rate
FROM combined
GROUP BY first_visit_month
ORDER BY first_visit_month
3. 常連ランク分布（リピーターのみ）
WITH visit_dates AS (
SELECT DISTINCT user_id,
DATE(TIMESTAMP(created_at), "Asia/Tokyo") AS visit_date
FROM aledata.mobileorder.product_orders
WHERE shop_id = 92 AND status = 5 AND deleted_at IS NULL
),
user_visit_summary AS (
SELECT user_id,
COUNT(DISTINCT visit_date) AS total_visits,
COUNTIF(visit_date BETWEEN DATE_SUB(DATE '2026-04-30', INTERVAL 13 DAY) AND DATE '2026-04-30') AS visits_14d,
COUNTIF(visit_date BETWEEN DATE_SUB(DATE '2026-04-30', INTERVAL 29 DAY) AND DATE '2026-04-30') AS visits_30d,
COUNTIF(visit_date BETWEEN DATE_SUB(DATE '2026-04-30', INTERVAL 59 DAY) AND DATE '2026-04-30') AS visits_60d,
COUNTIF(visit_date BETWEEN DATE_SUB(DATE '2026-04-30', INTERVAL 59 DAY) AND DATE_SUB(DATE '2026-04-30', INTERVAL 30 DAY)) AS visits_30_60d
FROM visit_dates
GROUP BY user_id
),
repeaters AS (
SELECT * FROM user_visit_summary WHERE total_visits >= 2
)
SELECT
CASE
WHEN visits_14d >= 2 THEN 'Gold'
WHEN visits_30d >= 2 THEN 'Silver'
WHEN visits_60d >= 2 THEN 'Bronze'
WHEN visits_30_60d >= 1 AND visits_30d = 0 THEN 'AtRisk'
ELSE 'Churned'
END AS loyalty_rank,
COUNT(*) AS customer_count
FROM repeaters
GROUP BY loyalty_rank
4. 単価セグメント分布
WITH valid_details AS (
SELECT
po.user_id, pod.price, p.report_category
FROM aledata.mobileorder.product_order_details pod
JOIN aledata.mobileorder.product_orders po
ON pod.product_order_id = po.id
JOIN aledata.mobileorder.products p
ON pod.product_id = p.id
WHERE po.shop_id = 92
AND po.status = 5 AND po.deleted_at IS NULL
AND pod.status = 5 AND pod.deleted_at IS NULL
),
price_avgs AS (
SELECT AVG(CASE WHEN report_category = 'main' THEN price END) AS main_avg
FROM valid_details
),
user_stats AS (
SELECT user_id,
SUM(price) AS total_spent,
COUNTIF(report_category = 'main') AS main_count
FROM valid_details
GROUP BY user_id
HAVING main_count > 0
),
user_segments AS (
SELECT us.*,
ROUND(us.total_spent / us.main_count, 0) AS unit_price,
CASE WHEN us.total_spent / us.main_count < pa.main_avg
THEN 'Minnow' ELSE 'Whale'
END AS segment
FROM user_stats us CROSS JOIN price_avgs pa
)
SELECT segment,
COUNT(*) AS customer_count,
ROUND(SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER()) * 100, 2) AS customer_ratio,
SUM(total_spent) AS total_sales,
ROUND(SAFE_DIVIDE(SUM(total_spent), SUM(SUM(total_spent)) OVER()) * 100, 2) AS sales_ratio,
ROUND(AVG(unit_price), 0) AS avg_unit_price
FROM user_segments
GROUP BY segment
5. 月再来店ユーザ推移（R-19）
WITH monthly_visitors AS (
SELECT DISTINCT
po.shop_id, po.user_id,
FORMAT_DATE('%Y-%m', DATE(TIMESTAMP(po.created_at), "Asia/Tokyo")) AS visit_month
FROM aledata.mobileorder.product_orders po
WHERE po.shop_id = 92
AND po.status = 5 AND po.deleted_at IS NULL
),
monthly_totals AS (
SELECT visit_month AS month, COUNT(DISTINCT user_id) AS total_users
FROM monthly_visitors
GROUP BY visit_month
),
returning_users AS (
SELECT cur.visit_month AS month, COUNT(DISTINCT cur.user_id) AS returning_users
FROM monthly_visitors cur
JOIN monthly_visitors prev
ON cur.user_id = prev.user_id AND cur.shop_id = prev.shop_id
AND prev.visit_month = FORMAT_DATE('%Y-%m',
DATE_SUB(PARSE_DATE('%Y-%m', cur.visit_month), INTERVAL 1 MONTH))
GROUP BY cur.visit_month
),
new_returning AS (
SELECT cur.visit_month AS month, COUNT(DISTINCT cur.user_id) AS new_returning_users
FROM monthly_visitors cur
JOIN monthly_visitors prev
ON cur.user_id = prev.user_id AND cur.shop_id = prev.shop_id
AND prev.visit_month = FORMAT_DATE('%Y-%m',
DATE_SUB(PARSE_DATE('%Y-%m', cur.visit_month), INTERVAL 1 MONTH))
LEFT JOIN monthly_visitors prev2
ON cur.user_id = prev2.user_id AND cur.shop_id = prev2.shop_id
AND prev2.visit_month = FORMAT_DATE('%Y-%m',
DATE_SUB(PARSE_DATE('%Y-%m', cur.visit_month), INTERVAL 2 MONTH))
WHERE prev2.user_id IS NULL
GROUP BY cur.visit_month
),
churned AS (
SELECT
FORMAT_DATE('%Y-%m',
DATE_ADD(PARSE_DATE('%Y-%m', prev.visit_month), INTERVAL 1 MONTH)) AS month,
COUNT(DISTINCT prev.user_id) AS churned_users
FROM monthly_visitors prev
JOIN monthly_visitors prev2
ON prev.user_id = prev2.user_id AND prev.shop_id = prev2.shop_id
AND prev2.visit_month = FORMAT_DATE('%Y-%m',
DATE_SUB(PARSE_DATE('%Y-%m', prev.visit_month), INTERVAL 1 MONTH))
LEFT JOIN monthly_visitors cur
ON prev.user_id = cur.user_id AND prev.shop_id = cur.shop_id
AND cur.visit_month = FORMAT_DATE('%Y-%m',
DATE_ADD(PARSE_DATE('%Y-%m', prev.visit_month), INTERVAL 1 MONTH))
WHERE cur.user_id IS NULL
GROUP BY prev.visit_month
)
SELECT
t.month, t.total_users AS total,
COALESCE(r.returning_users, 0) AS returning,
COALESCE(n.new_returning_users, 0) AS new_ret,
COALESCE(c.churned_users, 0) AS churned
FROM monthly_totals t
LEFT JOIN returning_users r ON t.month = r.month
LEFT JOIN new_returning n ON t.month = n.month
LEFT JOIN churned c ON t.month = c.month
ORDER BY t.month
6. メインメニュー当り単価分布
WITH valid_details AS (
SELECT po.user_id, pod.price, p.report_category
FROM aledata.mobileorder.product_order_details pod
JOIN aledata.mobileorder.product_orders po ON pod.product_order_id = po.id
JOIN aledata.mobileorder.products p ON pod.product_id = p.id
WHERE po.shop_id = 92
AND po.status = 5 AND po.deleted_at IS NULL
AND pod.status = 5 AND pod.deleted_at IS NULL
),
user_stats AS (
SELECT user_id,
SUM(price) AS total_spent,
COUNTIF(report_category = 'main') AS main_count
FROM valid_details
GROUP BY user_id HAVING main_count > 0
),
user_unit_prices AS (
SELECT user_id,
ROUND(total_spent / main_count, 0) AS unit_price
FROM user_stats
),
bucketed AS (
SELECT CAST(FLOOR(unit_price / 100) * 100 AS INT64) AS bucket_start
FROM user_unit_prices
)
SELECT
CONCAT(CAST(bucket_start AS STRING), '~', CAST(bucket_start + 99 AS STRING), '円') AS price_range,
COUNT(*) AS customer_count,
ROUND(SAFE_DIVIDE(COUNT(*), SUM(COUNT(*)) OVER()) * 100, 2) AS ratio
FROM bucketed
GROUP BY bucket_start
ORDER BY bucket_start