eBayオーストラリア（AU）バイヤー購買傾向｜実データで読み解く日本文化需要の高い市場

with raw as (
  select json_value(data, "$.category.CategoryName") cat,
    cast(json_value(data, "$.transaction_price") as float64) price
  from [販売データテーブル]
  where operation = "CREATE"
  and timestamp >= timestamp("2025-11-01")
  and timestamp < timestamp("2026-04-01")
  and json_value(data, "$.buyer_detail.address.Country") = "AU"
  and cast(json_value(data, "$.transaction_price") as float64) > 0
),
categorized as (
  select case
    when cat like "%Animation%" then "アニメグッズ"
    when cat like "%Figure%" or cat like "%Toy%" then "フィギュア・おもちゃ"
    when cat like "%Music%" or cat like "%CD%" then "CD/音楽"
    when cat like "%Watches%" then "腕時計"
    when cat like "%Diecast%" then "ダイキャストカー"
    when cat like "%Bag%" or cat like "%Handbag%" then "バッグ"
    when cat like "%Fish%" or cat like "%Lure%" then "釣具"
    when cat like "%Video Game%" then "ゲーム"
    when cat like "%Book%" then "書籍"
    when cat like "%Camera%" then "カメラ"
    when cat like "%Vinyl%" or cat like "%Record%" then "レコード"
    when cat like "%Manga%" then "マンガ"
    when cat like "%Pen%" or cat like "%Stationery%" then "文具"
    else "その他" end category, price
  from raw
)
select category, count(*) cnt, round(avg(price), 0) avg_price,
  round(sum(price), 0) total_sales,
  round(count(*) * 100.0 / sum(count(*)) over(), 1) share_pct
from categorized
group by 1 order by cnt desc limit 15

select case
    when price between 0.01 and 20 then "$1-20"
    when price between 20.01 and 50 then "$21-50"
    when price between 50.01 and 100 then "$51-100"
    when price between 100.01 and 200 then "$101-200"
    when price between 200.01 and 500 then "$201-500"
    else "$501+" end price_range,
  count(*) cnt,
  round(count(*) * 100.0 / sum(count(*)) over(), 1) share_pct
from (
  select cast(json_value(data, "$.transaction_price") as float64) price
  from [販売データテーブル]
  where operation = "CREATE"
  and timestamp >= timestamp("2025-11-01")
  and timestamp < timestamp("2026-04-01")
  and json_value(data, "$.buyer_detail.address.Country") = "AU"
  and cast(json_value(data, "$.transaction_price") as float64) > 0
)
group by 1 order by min(price)

-- 州別
select json_value(data, "$.buyer_detail.address.StateOrProvince") state,
  count(*) cnt, round(avg(cast(json_value(data, "$.transaction_price") as float64)), 0) avg_price
from [販売データテーブル]
where operation = "CREATE"
and timestamp >= timestamp("2025-11-01")
and timestamp < timestamp("2026-04-01")
and json_value(data, "$.buyer_detail.address.Country") = "AU"
and cast(json_value(data, "$.transaction_price") as float64) > 0
group by 1 order by cnt desc limit 10

-- 時間帯別（AEST基準）
select case
  when extract(hour from timestamp at time zone "Australia/Sydney") between 0 and 5 then "深夜（0-5時AEST）"
  when extract(hour from timestamp at time zone "Australia/Sydney") between 6 and 11 then "午前（6-11時AEST）"
  when extract(hour from timestamp at time zone "Australia/Sydney") between 12 and 17 then "午後（12-17時AEST）"
  else "夜間（18-23時AEST）" end time_band,
  count(*) cnt, round(avg(cast(json_value(data, "$.transaction_price") as float64)), 0) avg_price
from [販売データテーブル]
where operation = "CREATE"
and timestamp >= timestamp("2025-11-01")
and timestamp < timestamp("2026-04-01")
and json_value(data, "$.buyer_detail.address.Country") = "AU"
and cast(json_value(data, "$.transaction_price") as float64) > 0
group by 1 order by cnt desc