2025.09.29
AIに正しいビジネスロジックでデータ集計させたい
こんにちは、AI研究開発室のS.Yです。
最近、セマンティックレイヤーについての記事やポストを見かけることが多くなったように感じます。
- 「セマンティックレイヤーはビジネスロジックを一元管理できるので、例えば部署間における指標の認識相違が無くなる」
- 「ビジネスロジックを一元管理できるので、AIが正しいロジックに基づいてデータ集計・分析できるようになり、精度が上がる」
セマンティックレイヤー、夢のようですね！
と言うわけでセマンティックレイヤーに興味が湧いた私は、今回下記のことを調査・検証してみました。
- セマンティックレイヤーではどう言う仕組みで一元管理が実現されていて、人間がセマンティックレイヤーを活用する際はどういったUXになるのか？
- AIとセマンティックレイヤーを組み合わせるにはどうするのか？どれくらい正確に集計できそうなのか？
最初に結論
「セマンティックレイヤーではどう言う仕組みで一元管理が実現されているのか？」
- データモデリングの時点で、どんな軸(Dimension)でどういう指標(Mearuse)が集計がされるか、をあらかじめ名前付きで定義しておく。
「人間がセマンティックレイヤーを活用する際はどういったUXになるのか？」
- BIツール上でシンプルなクエリを発行する。基本的には指標+集計軸を指定するだけのようなクエリになる。一部、各セマンティックレイヤー独自の作法を覚える必要はありそう。
- BIツールのNo Queryな機能を使えば、定義した指標や軸がプルダウンの選択肢に現れるのでマウスでポチポチするだけで集計できる。ただ、No Queryとの統合はまだ成熟しきっていない印象。
「AIとセマンティックレイヤーを組み合わせるにはどうするのか？」
- 「AIにセマンティックレイヤー向けのSQL(Cube CoreであればCube SQL)を生成させる」というのが大枠。
- セマンティックレイヤーで管理されているmetadata(スキーマリスト)をAIのコンテキストに含める工夫で実現する。
- GPT-4oレベルのLLMであれば、ユーザー質問+metadataを考慮して正しいSQLを生成できる。
Cube Core
今回はオープンソースのCube Coreを使って検証します。
そもそもセマンティックレイヤーとは簡単に言うと 「データに意味を付与して、一貫した形で利用できるようにする仕組み」 です。
データベースに入っているのは生のカラム名やIDですが、それを「売上」「顧客数」「利益率」といったビジネス的な指標として名前付きで定義しておくことで、みんなが同じ意味でデータを使えるようになります。
これによって──
- 部署ごとに「売上」の定義が微妙に違う…といった認識齟齬がなくなる
- BIツールやAIが「売上」「顧客年齢層」といった人間にわかりやすい指標を直接呼び出せる
などといったメリットが得られます。
CubeのようなOSS/サービスは、このセマンティックレイヤーを データウェアハウスの上にAPIやキャッシュ付きで構築できる基盤 として提供しており、定義した「売上」「顧客単価」などをシンプルに呼び出せるのが特徴です。
今回の対象データはbigqueryのbigquery-public-data.thelook_ecommerceデータセットを使います。
Cubeでは”Cube”と言う単位でデータモデリングしていきます。一つのCubeは一つのテーブルに対応していて、Cubeに対して「どんな集計をなんと呼ぶか」を定義していきます。
サンプルコードを表示
cube(`OrderItems`, {
sql: `SELECT * FROM \`bigquery-public-data.thelook_ecommerce.order_items\``,
measures: {
total_order_items: {
type: `count`,
title: `総注文商品数`,
description: `総注文商品数`
},
total_revenue: {
type: `sum`,
sql: `${CUBE}.sale_price`,
title: `総売上`,
description: `総売上金額`,
format: `currency`
},
average_sale_price: {
type: `avg`,
sql: `${CUBE}.sale_price`,
title: `平均売価`,
description: `商品の平均売価`,
format: `currency`
},
total_profit: {
type: `sum`,
sql: `${CUBE}.sale_price - ${Products.cost}`,
title: `総利益`,
description: `売価から原価を引いた総利益`,
format: `currency`
},
profit_margin_percent: {
type: `number`,
sql: `CASE
WHEN SUM(${CUBE}.sale_price) > 0
THEN (SUM(${CUBE}.sale_price) - SUM(${Products.cost})) / SUM(${CUBE}.sale_price) * 100
ELSE 0
END`,
title: `利益率(%)`,
description: `売上に対する利益の割合`,
format: `percent`
},
average_order_value: {
type: `number`,
sql: `SUM(${CUBE}.sale_price) / COUNT(DISTINCT ${CUBE}.order_id)`,
title: `平均注文額`,
description: `1注文あたりの平均金額`,
format: `currency`
},
unique_customers: {
type: `countDistinct`,
sql: `${CUBE}.user_id`,
title: `ユニーク顧客数`,
description: `重複を除いた顧客数`
},
revenue_per_customer: {
type: `number`,
sql: `SUM(${CUBE}.sale_price) / COUNT(DISTINCT ${CUBE}.user_id)`,
title: `顧客単価`,
description: `1顧客あたりの平均売上`,
format: `currency`
}
},
dimensions: {
id: {
sql: `${CUBE}.id`,
type: `number`,
primaryKey: true,
title: `注文商品ID`,
description: `注文商品の一意識別子`
},
order_id: {
sql: `${CUBE}.order_id`,
type: `number`,
title: `注文ID`,
description: `注文の識別子`
},
user_id: {
sql: `${CUBE}.user_id`,
type: `number`,
title: `ユーザーID`,
description: `注文したユーザーのID`
},
product_id: {
sql: `${CUBE}.product_id`,
type: `number`,
title: `商品ID`,
description: `商品の識別子`
},
inventory_item_id: {
sql: `${CUBE}.inventory_item_id`,
type: `number`,
title: `在庫商品ID`,
description: `在庫商品の識別子`
},
status: {
sql: `${CUBE}.status`,
type: `string`,
title: `商品ステータス`,
description: `注文商品のステータス`
},
sale_price: {
sql: `${CUBE}.sale_price`,
type: `number`,
title: `売価`,
description: `商品の売価`
},
created_at: {
sql: `${CUBE}.created_at`,
type: `time`,
title: `注文商品作成日時`,
description: `注文商品作成日時`
},
shipped_at: {
sql: `${CUBE}.shipped_at`,
type: `time`,
title: `発送日時`,
description: `商品発送日時`
},
delivered_at: {
sql: `${CUBE}.delivered_at`,
type: `time`,
title: `配達日時`,
description: `商品配達日時`
},
returned_at: {
sql: `${CUBE}.returned_at`,
type: `time`,
title: `返品日時`,
description: `商品返品日時`
},
// Products テーブルからの重要なビジネス情報
product_category: {
sql: `${Products.category}`,
type: `string`,
title: `商品カテゴリ`,
description: `商品のカテゴリ（JOINによる取得）`
},
product_brand: {
sql: `${Products.brand}`,
type: `string`,
title: `商品ブランド`,
description: `商品のブランド（JOINによる取得）`
},
product_department: {
sql: `${Products.department}`,
type: `string`,
title: `商品部門`,
description: `商品の部門（JOINによる取得）`
},
// Users テーブルからの重要なビジネス情報
customer_country: {
sql: `${Users.country}`,
type: `string`,
title: `顧客の国`,
description: `顧客の居住国（JOINによる取得）`
},
customer_state: {
sql: `${Users.state}`,
type: `string`,
title: `顧客の州`,
description: `顧客の居住州（JOINによる取得）`
},
customer_gender: {
sql: `${Users.gender}`,
type: `string`,
title: `顧客性別`,
description: `顧客の性別（JOINによる取得）`
},
customer_age_group: {
sql: `CASE
WHEN ${Users.age} < 25 THEN '18-24歳'
WHEN ${Users.age} < 35 THEN '25-34歳'
WHEN ${Users.age} < 45 THEN '35-44歳'
WHEN ${Users.age} < 55 THEN '45-54歳'
WHEN ${Users.age} < 65 THEN '55-64歳'
ELSE '65歳以上'
END`,
type: `string`,
title: `顧客年齢層`,
description: `顧客の年齢グループ`
}
},
joins: {
Orders: {
sql: `${CUBE}.order_id = ${Orders}.order_id`,
relationship: `belongsTo`
},
Users: {
sql: `${CUBE}.user_id = ${Users}.id`,
relationship: `belongsTo`
},
Products: {
sql: `${CUBE}.product_id = ${Products}.id`,
relationship: `belongsTo`
}
}
});
上記の例のprofit_margin_percentやcustomer_age_groupのように複雑なロジックを伴うmeasureやdimensionも定義できますし、別のCubeとJoinする際の結合ルールも定義しておけます。
Cubeは後述のDeveloper Playground上で作成・編集することもできますし、直接js/yamlファイルを作成することでも操作できます。今回はひとまずデータセットにある5つのテーブルについて、Claude Codeにパーっとjsファイルでcubeを作ってもらいました！
さて、Cubeを定義したら早速セマンティックレイヤーを起動してみましょう。
公式のイメージでコンテナを起動すると、ローカルにDeveloper Playgroundが立ち上がりました。
Data Modelタブに作成したファイルが反映されています。いいですねいいですね！
Playgroundタブで、Cubeに定義した諸々を使って実際に集計できます。「年齢層毎の月別の平均売価」をクエリしてみましょう。
customer_age_group, created_atはDimension、average_sale_priceはMeasureとしてOrderItems.jsのCubeに定義してあるので、左側のリストに表示されています。
あとはこれらを選択してRun Queryすると。。
おー。集計結果が出ました。
Generated SQLのタブで、実際にBigqueryに送られたQueryを見てみましょう。
SELECT
CASE
WHEN `users`.age < 25 THEN '18-24歳'
WHEN `users`.age < 35 THEN '25-34歳'
WHEN `users`.age < 45 THEN '35-44歳'
WHEN `users`.age < 55 THEN '45-54歳'
WHEN `users`.age < 65 THEN '55-64歳'
ELSE '65歳以上'
END `order_items__customer_age_group`,
TIMESTAMP(
DATETIME_TRUNC(
TIMESTAMP(DATETIME(`order_items`.created_at, 'UTC')),
MONTH
)
) `order_items__created_at_month`,
avg(`order_items`.sale_price) `order_items__average_sale_price`
FROM
`bigquery-public-data.thelook_ecommerce.order_items` AS `order_items`
LEFT JOIN `bigquery-public-data.thelook_ecommerce.users` AS `users` ON `order_items`.user_id = `users`.id
GROUP BY
1,
2
ORDER BY
2 ASC
LIMIT
10000
Cubeの定義に基づいて、Cubeエンジン側でうまくQueryを生成してくれていますね。(“Cube”という言葉がややこしい。。w)
CubeのSQL APIが受け取るリクエストは下記になります。
SELECT
OrderItems.customer_age_group,
DATE_TRUNC('month', OrderItems.created_at),
MEASURE(OrderItems.average_sale_price)
FROM
OrderItems
GROUP BY
1,
2
LIMIT
SQL APIが受け取るリクエストは非常にシンプルで、JOIN句などは書かれていません。OrderItems.customer_age_groupがSELECTされていることで、Cubeエンジンがcustomer_age_groupが定義されているCustomer CubeをJOINする必要がある、と判断しているのですね。
BIツールと接続
さて、PlaygroundでCubeに対する集計の雰囲気が掴めたので、実際にビジネスの現場で使われるBIツールと接続したときにどんなUXになるのかを確認してみましょう！
ローカルに立ち上げたMetaBaseと接続してみます。
Cube CoreはPostgreSQL互換のエンドポイントを公開していて、MetaBaseはそこに繋ぎにいきます。
接続できたら、クエリを書いてみましょう。Playgroundを試した際の「年齢層毎の月別の平均売価」のクエリを実行してみます。
データが取得されて、MetaBase上で可視化ができました。
Cube SQL独自のMEASURE句を覚える必要がありますが、かなりシンプルなクエリでビジネスロジックについて集計できるのはいいですね。
また、MetaBaseではQuestionsというクエリを書かずに集計できる機能もあります。これを使えば「クエリ要らずで正しいビジネスロジックに従って集計できる！」という期待を込めてこちらも触ってみましたが、結論、こちらはCubeとの親和性は微妙でした。。
Questionsは「Dimensionにするのはどのカラムで」、「集計対象はどのカラムで」、「それをどうAggregationをするか」をプルダウンで指定してやる必要があります。ここでネックなのが、Aggregationは必ず指定しないといけないという点です。
つまり、セマンティックレイヤーでmetrics毎にAggregationまで含めた定義をしているのに、MetabaseのQuestionsだとそれを活かせず、クエリの際に改めて「〜の合計値」「〜の平均値」などAggregationを指定しなければいけないUXになる、ということですね。せっかくセマンティックレイヤーでcubeを定義している意味が薄れてしまいます。
この辺りは、Aggregationロジックの生成については従来だとBIツールの担当領域だったが、セマンティックレイヤーもAggregationロジックの定義を自身でやってしまう思想なため、両者を接続しようとすると設計思想の違いが顕在化する、みたいなことが起きている気がしました。
ひょっとするとこの辺りを解決しているセマンティックレイヤー×BIツールが他にあったり、Cube×Metabaseでも上手くいくやり方があるのかもしれませんが、異なるデータレイヤー間の繋ぎこみの難しさを垣間見た瞬間でした。
AIと接続
ここまでで、cubeにビジネスロジックを定義して、それをBIツール上から簡単なSQLでクエリして可視化できることを確認できました。
このcubeを活用して、AIに対して正しいビジネスロジックに基づいたデータ集計をさせる工夫をしてみましょう。
今回は、「自然言語で質問したら、正しいビジネスロジックで集計してくれるAIアプリケーション」を作ってみたいと思います。
Cubeにはcubeのmetadataを取得できるAPIが実装されているので、これを使います。
こんなmetadataが返ってきます。(長いので展開注意)
curl -H "Authorization: demo_token" \
-H "Content-Type: application/json" \
http://localhost:4000/cubejs-api/v1/meta> -H "Content-Type: application/json" \
> http://localhost:4000/cubejs-api/v1/meta
{"cubes":[{"name":"CustomerMetrics","type":"cube","title":"Customer Metrics","isVisible":true,"public":true,"measures":[{"name":"CustomerMetrics.total_customers","title":"Customer Metrics 総顧客数","description":"ユニークな顧客の総数","shortTitle":"総顧客数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.active_customers","title":"Customer Metrics アクティブ顧客数","description":"実際に購入した顧客数","shortTitle":"アクティブ顧客数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.customer_lifetime_value","title":"Customer Metrics 顧客生涯価値(LTV)","description":"1顧客あたりの総購入金額","shortTitle":"顧客生涯価値(LTV)","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.average_purchase_frequency","title":"Customer Metrics 平均購入回数","description":"1顧客あたりの平均購入回数","shortTitle":"平均購入回数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.total_customer_profit","title":"Customer Metrics 総顧客利益","description":"顧客から得られた総利益","shortTitle":"総顧客利益","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"sum","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.average_time_to_first_purchase","title":"Customer Metrics 初回購入までの平均日数","description":"登録から初回購入までの平均日数","shortTitle":"初回購入までの平均日数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.repeat_customer_indicator","title":"Customer Metrics 購入経験者数","description":"実際に購入した顧客数","shortTitle":"購入経験者数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.new_customers_monthly","title":"Customer Metrics 月次新規顧客数","description":"直近1ヶ月の新規顧客数","shortTitle":"月次新規顧客数","cumulativeTotal":true,"cumulative":true,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.hot_customer_analysis","title":"Customer Metrics ホットカスタマー詳細数","description":"直近30日で3回以上購入した顧客の詳細分析用（社内独自用語：ホットカスタマー）","shortTitle":"ホットカスタマー詳細数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.hot_customer_ltv","title":"Customer Metrics ホットカスタマーLTV","description":"ホットカスタマーの平均生涯価値（社内独自用語）","shortTitle":"ホットカスタマーLTV","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"CustomerMetrics.hot_customer_conversion_rate","title":"Customer Metrics ホットカスタマー転換率(%)","description":"登録顧客からホットカスタマーへの転換率（社内独自KPI）","shortTitle":"ホットカスタマー転換率(%)","format":"percent","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true}],"dimensions":[{"name":"CustomerMetrics.user_id","title":"Customer Metrics 顧客ID","type":"number","description":"顧客の一意識別子","shortTitle":"顧客ID","suggestFilterValues":true,"isVisible":false,"public":false,"primaryKey":true},{"name":"CustomerMetrics.customer_name","title":"Customer Metrics 顧客名","type":"string","description":"顧客のフルネーム","shortTitle":"顧客名","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.age_segment","title":"Customer Metrics 年齢セグメント","type":"string","description":"マーケティング向け年齢層分類","shortTitle":"年齢セグメント","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.customer_tier","title":"Customer Metrics 顧客ランク","type":"string","description":"購入金額に基づく顧客ランク","shortTitle":"顧客ランク","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.geographic_region","title":"Customer Metrics 地域","type":"string","description":"マーケティング向け地域分類","shortTitle":"地域","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.traffic_source","title":"Customer Metrics 流入経路","type":"string","description":"顧客獲得チャネル","shortTitle":"流入経路","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.registration_date","title":"Customer Metrics 登録日","type":"time","description":"顧客登録日","shortTitle":"登録日","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.purchase_date","title":"Customer Metrics 購入日","type":"time","description":"商品購入日","shortTitle":"購入日","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.customer_heat_level","title":"Customer Metrics 顧客熱度レベル","type":"string","description":"購入頻度による顧客熱度分類（社内独自用語：スーパーホット、ホット、ウォーム、コールド）","shortTitle":"顧客熱度レベル","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"CustomerMetrics.hot_customer_segment","title":"Customer Metrics ホットカスタマーセグメント","type":"string","description":"年齢×購入頻度によるホットカスタマー細分化（社内独自セグメント）","shortTitle":"ホットカスタマーセグメント","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false}],"segments":[],"hierarchies":[],"folders":[],"nestedFolders":[]},{"name":"OrderItems","type":"cube","title":"Order Items","isVisible":true,"public":true,"connectedComponent":1,"measures":[{"name":"OrderItems.total_order_items","title":"Order Items 総注文商品数","description":"総注文商品数","shortTitle":"総注文商品数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"count","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.total_revenue","title":"Order Items 総売上","description":"総売上金額","shortTitle":"総売上","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"sum","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.average_sale_price","title":"Order Items 平均売価","description":"商品の平均売価","shortTitle":"平均売価","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.total_profit","title":"Order Items 総利益","description":"売価から原価を引いた総利益","shortTitle":"総利益","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"sum","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.profit_margin_percent","title":"Order Items 利益率(%)","description":"売上に対する利益の割合","shortTitle":"利益率(%)","format":"percent","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.average_order_value","title":"Order Items 平均注文額","description":"1注文あたりの平均金額","shortTitle":"平均注文額","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.unique_customers","title":"Order Items ユニーク顧客数","description":"重複を除いた顧客数","shortTitle":"ユニーク顧客数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.revenue_per_customer","title":"Order Items 顧客単価","description":"1顧客あたりの平均売上","shortTitle":"顧客単価","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.hot_customer_count","title":"Order Items ホットカスタマー数","description":"直近30日で3回以上購入した顧客数（社内独自用語：ホットカスタマー = 頻繁購入者）","shortTitle":"ホットカスタマー数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"countDistinct","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.hot_customer_rate","title":"Order Items ホットカスタマー率(%)","description":"全顧客に占めるホットカスタマーの割合（社内独自用語：ホットカスタマー率 = 頻繁購入者率）","shortTitle":"ホットカスタマー率(%)","format":"percent","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.hot_customer_revenue","title":"Order Items ホットカスタマー売上","description":"ホットカスタマーからの総売上金額（社内独自用語）","shortTitle":"ホットカスタマー売上","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"sum","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"OrderItems.hot_customer_average_order","title":"Order Items ホットカスタマー単価","description":"ホットカスタマーの平均購入額（社内独自用語）","shortTitle":"ホットカスタマー単価","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true}],"dimensions":[{"name":"OrderItems.id","title":"Order Items 注文商品ID","type":"number","description":"注文商品の一意識別子","shortTitle":"注文商品ID","suggestFilterValues":true,"isVisible":false,"public":false,"primaryKey":true},{"name":"OrderItems.order_id","title":"Order Items 注文ID","type":"number","description":"注文の識別子","shortTitle":"注文ID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.user_id","title":"Order Items ユーザーID","type":"number","description":"注文したユーザーのID","shortTitle":"ユーザーID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.product_id","title":"Order Items 商品ID","type":"number","description":"商品の識別子","shortTitle":"商品ID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.inventory_item_id","title":"Order Items 在庫商品ID","type":"number","description":"在庫商品の識別子","shortTitle":"在庫商品ID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.status","title":"Order Items 商品ステータス","type":"string","description":"注文商品のステータス","shortTitle":"商品ステータス","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.sale_price","title":"Order Items 売価","type":"number","description":"商品の売価","shortTitle":"売価","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.created_at","title":"Order Items 注文商品作成日時","type":"time","description":"注文商品作成日時","shortTitle":"注文商品作成日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.shipped_at","title":"Order Items 発送日時","type":"time","description":"商品発送日時","shortTitle":"発送日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.delivered_at","title":"Order Items 配達日時","type":"time","description":"商品配達日時","shortTitle":"配達日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.returned_at","title":"Order Items 返品日時","type":"time","description":"商品返品日時","shortTitle":"返品日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"OrderItems.product_category","title":"Order Items 商品カテゴリ","type":"string","description":"商品のカテゴリ（JOINによる取得）","shortTitle":"商品カテゴリ","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Products.category"},{"name":"OrderItems.product_brand","title":"Order Items 商品ブランド","type":"string","description":"商品のブランド（JOINによる取得）","shortTitle":"商品ブランド","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Products.brand"},{"name":"OrderItems.product_department","title":"Order Items 商品部門","type":"string","description":"商品の部門（JOINによる取得）","shortTitle":"商品部門","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Products.department"},{"name":"OrderItems.customer_country","title":"Order Items 顧客の国","type":"string","description":"顧客の居住国（JOINによる取得）","shortTitle":"顧客の国","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Users.country"},{"name":"OrderItems.customer_state","title":"Order Items 顧客の州","type":"string","description":"顧客の居住州（JOINによる取得）","shortTitle":"顧客の州","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Users.state"},{"name":"OrderItems.customer_gender","title":"Order Items 顧客性別","type":"string","description":"顧客の性別（JOINによる取得）","shortTitle":"顧客性別","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false,"aliasMember":"Users.gender"},{"name":"OrderItems.customer_age_group","title":"Order Items 顧客年齢層","type":"string","description":"顧客の年齢グループ","shortTitle":"顧客年齢層","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false}],"segments":[],"hierarchies":[],"folders":[],"nestedFolders":[]},{"name":"Orders","type":"cube","title":"Orders","isVisible":true,"public":true,"connectedComponent":1,"measures":[{"name":"Orders.total_orders","title":"Orders 総注文数","description":"総注文数","shortTitle":"総注文数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"count","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Orders.avg_items_per_order","title":"Orders 注文あたり平均商品数","description":"1注文あたりの平均商品数","shortTitle":"注文あたり平均商品数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true}],"dimensions":[{"name":"Orders.order_id","title":"Orders 注文ID","type":"number","description":"注文の一意識別子","shortTitle":"注文ID","suggestFilterValues":true,"isVisible":false,"public":false,"primaryKey":true},{"name":"Orders.user_id","title":"Orders ユーザーID","type":"number","description":"注文したユーザーのID","shortTitle":"ユーザーID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.status","title":"Orders 注文ステータス","type":"string","description":"注文の現在のステータス","shortTitle":"注文ステータス","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.gender","title":"Orders 性別","type":"string","description":"注文者の性別","shortTitle":"性別","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.num_of_item","title":"Orders 商品数","type":"number","description":"注文内の商品数","shortTitle":"商品数","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.created_at","title":"Orders 注文日時","type":"time","description":"注文作成日時","shortTitle":"注文日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.shipped_at","title":"Orders 発送日時","type":"time","description":"商品発送日時","shortTitle":"発送日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.delivered_at","title":"Orders 配達日時","type":"time","description":"商品配達日時","shortTitle":"配達日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Orders.returned_at","title":"Orders 返品日時","type":"time","description":"商品返品日時","shortTitle":"返品日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false}],"segments":[],"hierarchies":[],"folders":[],"nestedFolders":[]},{"name":"Products","type":"cube","title":"Products","isVisible":true,"public":true,"connectedComponent":1,"measures":[{"name":"Products.total_products","title":"Products 総商品数","description":"総商品数","shortTitle":"総商品数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"count","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Products.average_cost","title":"Products 平均原価","description":"商品の平均原価","shortTitle":"平均原価","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Products.average_retail_price","title":"Products 平均小売価格","description":"商品の平均小売価格","shortTitle":"平均小売価格","format":"currency","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true}],"dimensions":[{"name":"Products.id","title":"Products 商品ID","type":"number","description":"商品の一意識別子","shortTitle":"商品ID","suggestFilterValues":true,"isVisible":false,"public":false,"primaryKey":true},{"name":"Products.name","title":"Products 商品名","type":"string","description":"商品の名前","shortTitle":"商品名","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Products.category","title":"Products カテゴリ","type":"string","description":"商品カテゴリ","shortTitle":"カテゴリ","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Products.brand","title":"Products ブランド","type":"string","description":"商品ブランド","shortTitle":"ブランド","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Products.department","title":"Products 部門","type":"string","description":"商品部門","shortTitle":"部門","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Products.sku","title":"Products SKU","type":"string","description":"商品SKU","shortTitle":"SKU","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Products.cost","title":"Products 原価","type":"number","description":"商品原価","shortTitle":"原価","suggestFilterValues":true,"format":"currency","isVisible":true,"public":true,"primaryKey":false},{"name":"Products.retail_price","title":"Products 小売価格","type":"number","description":"商品小売価格","shortTitle":"小売価格","suggestFilterValues":true,"format":"currency","isVisible":true,"public":true,"primaryKey":false},{"name":"Products.distribution_center_id","title":"Products 配送センターID","type":"number","description":"配送センターの識別子","shortTitle":"配送センターID","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false}],"segments":[],"hierarchies":[],"folders":[],"nestedFolders":[]},{"name":"Users","type":"cube","title":"Users","isVisible":true,"public":true,"connectedComponent":1,"measures":[{"name":"Users.total_users","title":"Users 総ユーザー数","description":"総ユーザー数","shortTitle":"総ユーザー数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"count","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Users.average_age","title":"Users 平均年齢","description":"ユーザーの平均年齢","shortTitle":"平均年齢","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"avg","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Users.hot_customers","title":"Users ホットカスタマー数","description":"直近30日で3回以上購入したユーザー数（社内独自用語：ホットカスタマー）","shortTitle":"ホットカスタマー数","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"count","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true},{"name":"Users.hot_customer_percentage","title":"Users ホットカスタマー率(%)","description":"全ユーザーに占めるホットカスタマーの割合（社内独自用語）","shortTitle":"ホットカスタマー率(%)","format":"percent","cumulativeTotal":false,"cumulative":false,"type":"number","aggType":"number","drillMembers":[],"drillMembersGrouped":{"measures":[],"dimensions":[]},"isVisible":true,"public":true}],"dimensions":[{"name":"Users.id","title":"Users ユーザーID","type":"number","description":"ユーザーの一意識別子","shortTitle":"ユーザーID","suggestFilterValues":true,"isVisible":false,"public":false,"primaryKey":true},{"name":"Users.first_name","title":"Users 名前","type":"string","description":"ユーザーの名前","shortTitle":"名前","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.last_name","title":"Users 姓","type":"string","description":"ユーザーの姓","shortTitle":"姓","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.email","title":"Users メールアドレス","type":"string","description":"ユーザーのメールアドレス","shortTitle":"メールアドレス","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.age","title":"Users 年齢","type":"number","description":"ユーザーの年齢","shortTitle":"年齢","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.gender","title":"Users 性別","type":"string","description":"ユーザーの性別","shortTitle":"性別","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.state","title":"Users 州","type":"string","description":"ユーザーの居住州","shortTitle":"州","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.city","title":"Users 都市","type":"string","description":"ユーザーの居住都市","shortTitle":"都市","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.country","title":"Users 国","type":"string","description":"ユーザーの居住国","shortTitle":"国","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.traffic_source","title":"Users 流入経路","type":"string","description":"ユーザーの流入経路","shortTitle":"流入経路","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.created_at","title":"Users 登録日時","type":"time","description":"ユーザー登録日時","shortTitle":"登録日時","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.customer_type","title":"Users 顧客タイプ","type":"string","description":"顧客の購入頻度による分類（社内独自用語：ホットカスタマー、通常カスタマー、休眠カスタマー）","shortTitle":"顧客タイプ","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false},{"name":"Users.age_group","title":"Users 年齢層","type":"string","description":"年齢による顧客セグメント","shortTitle":"年齢層","suggestFilterValues":true,"isVisible":true,"public":true,"primaryKey":false}]
接続するAIは、今回はStreamlit + LangchainでパッとAIアプリケーションを作って、それと連携させてみましょう。
質問を返す全体の流れはこんな感じです。LLMに、ユーザーの質問 + metadataの情報からCube SQLを作らせるのがキモです。
LLMに渡すpromptのテンプレートはこんな感じにしました。
prompt = f"""あなたは日本語のビジネス質問をCube JSONクエリに変換するアシスタントです。
利用可能なスキーマ:
{json.dumps(available_cubes, ensure_ascii=False, indent=2)}
変換ルール:
1. 常に有効なJSONを返してください
2. スキーマから正確なmeasure/dimension名を使用してください
3. 時間に関するクエリには適切なgranularityを含めてください
4. 不明な場合は、利用可能なオプションを提案してください
5. 日付範囲の指定:
- "今日" → "today"
- "昨日" → "yesterday"
- "先週" → "last week"
- "今週" → "this week"
- "先月" → "last month"
- "今月" → "this month"
- 特定の期間 → ["2024-01-01", "2024-12-31"] (配列形式)
ユーザーの質問: {user_query}
以下の形式でCubeクエリを返してください:
{{
"measures": ["measure1", "measure2"],
"dimensions": ["dimension1"],
"timeDimensions": [{{
"dimension": "time_dimension",
"granularity": "day|week|month",
"dateRange": ["2024-01-01", "2024-12-31"]
}}],
"filters": [],
"limit": 1000
}}
Cubeクエリ:"""
今回はデモとして、”ホットカスタマー”という独自のビジネスロジックを定義して、AIが正しいロジックで集計してくれるかを試してみます。直近30日間で3回以上購入しているユーザーをホットカスタマーと定義しましょう。
cube(`Users`, {
sql: `SELECT * FROM \`bigquery-public-data.thelook_ecommerce.users\``,
measures: {
total_users: {
type: `count`,
title: `総ユーザー数`,
description: `総ユーザー数`
},
average_age: {
type: `avg`,
sql: `${CUBE}.age`,
title: `平均年齢`,
description: `ユーザーの平均年齢`
},
// ホットカスタマー関連指標（顧客ベース）
hot_customers: {
type: `count`,
sql: `CASE
WHEN (
SELECT COUNT(*)
FROM \`bigquery-public-data.thelook_ecommerce.order_items\` hot_check
WHERE hot_check.user_id = ${CUBE}.id
AND DATE(hot_check.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND DATE(hot_check.created_at) <= CURRENT_DATE()
) >= 3
THEN ${CUBE}.id
ELSE NULL
END`,
title: `ホットカスタマー数`,
description: `直近30日で3回以上購入したユーザー数`
},
hot_customer_percentage: {
type: `number`,
sql: `
COUNT(CASE
WHEN (
SELECT COUNT(*)
FROM \`bigquery-public-data.thelook_ecommerce.order_items\` hot_check
WHERE hot_check.user_id = ${CUBE}.id
AND hot_check.created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND hot_check.created_at <= CURRENT_DATE()
) >= 3
THEN ${CUBE}.id
ELSE NULL
END) * 100.0 / COUNT(${CUBE}.id)
`,
title: `ホットカスタマー率(%)`,
description: `全ユーザーに占めるホットカスタマーの割合`,
format: `percent`
}
},
dimensions: {
id: {
sql: `${CUBE}.id`,
type: `number`,
primaryKey: true,
title: `ユーザーID`,
description: `ユーザーの一意識別子`
},
created_at: {
sql: `${CUBE}.created_at`,
type: `time`,
title: `登録日時`,
description: `ユーザー登録日時`
},
}
});
Users.jsに上記を定義して、AIアプリに質問してみます。
「2024年のホットカスタマー数の月毎の推移を出して」
集計結果が可視化されました！
生成されたCube SQLクエリの要約をアプリに表示するようにして、きちんとMeasureやDimensionが指定されているのかを確認してみました。
きちんとhot_customerのMeasureを集計しているようです。
実際にBQに対して発行されたクエリは以下の通りです。
SELECT
TIMESTAMP(DATETIME_TRUNC(TIMESTAMP(DATETIME(`users`.created_at, 'UTC')), MONTH)) `users__created_at_month`, count(CASE
WHEN (
SELECT COUNT(*)
FROM `bigquery-public-data.thelook_ecommerce.order_items` hot_check
WHERE hot_check.user_id = `users`.id
AND DATE(hot_check.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
AND DATE(hot_check.created_at) <= CURRENT_DATE()
) >= 3
THEN `users`.id
ELSE NULL
END) `users__hot_customers`
FROM
`bigquery-public-data.thelook_ecommerce.users` AS `users` WHERE (`users`.created_at >= TIMESTAMP(?) AND `users`.created_at <= TIMESTAMP(?)) GROUP BY 1 ORDER BY 1 ASC LIMIT 1000
ホットカスタマーのロジックが再現されていますね！
自然言語の問い合わせに対して、正しいビジネスロジックに基づく集計がされていることが確認できました！
まとめ
OSSのセマンティックレイヤーであるCube Coreを使って、セマンティックレイヤーのデータモデリング、BIツールと接続した際のUX、AIとの統合について試してみました。
モデリングの基本は、データソースのテーブルに対応するcubeについて、measureやdimensoinを定義してデータモデリングしていくのでした。どのような指標をどのような軸で集計する需要があるのか、を考えながら運用する必要があると感じました。また今回は触れていませんが、プロダクションレベルでの実践の際にはモデリング以外にも、パフォーマンス・DWHの負荷・コスト・データ鮮度など、検討すべき非機能要件が多くあります。事前集計やキャッシングを駆使してこの辺りをチューニングしていくのも面白そうですが、開発・運用のコストが大きそうな場合はマネージドなセマンティックレイヤーを選択するのも手だと思います。
BIツールとの接続については、Metabase上でシンプルなクエリを実行することで、Cubeを介してデータソースに対してビジネスロジックに基づいた集計をかけられることが体験できました。一方で、クエリ要らずで集計できるような機能との統合は、結局ユーザーがAggregationロジックを指定しないといけない部分があり、あと一歩だと感じました。
AIとの統合では、metadata(スキーマリスト)をコンテキストに含めることでGPT-4oレベルのLLMであれば問題なく正しいCube SQLを生成できそうだ、ということがわかりました。
さいごに
グループ研究開発本部 AI研究開発室では、データサイエンティスト/機械学習エンジニアを募集しています。ビッグデータの解析業務などAI研究開発室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。皆さんのご応募をお待ちしています。
グループ研究開発本部の最新情報をTwitterで配信中です。ぜひフォローください。
Follow @GMO_RD
