Explore a real ontology

cassis/domains/README.md

---
type: Domain
title: Stallora marketplace
description: Global context for the Stallora pan-European marketplace dataset.
---

Stallora is a pan-European online marketplace where independent sellers list products across many categories (home and garden, electronics, fashion, health and beauty, sports and leisure, and more) and customers across Europe place orders. An order contains one or more order items, and each order item is one product sold and fulfilled by a single seller.

## How the data is organized

Two connected domains:

- [marketplace](marketplace): the transactional core (orders, order items, products, sellers, customers, payments, reviews, geolocation) plus curated rollup tables.
- [seller_acquisition](seller_acquisition): how new sellers are won (marketing qualified leads and closed deals).

The only bridge between the two domains is closed deal to seller. The marketplace domain carries topic docs as child domains; open them with get() like any domain path. Table-level rules (grain, dedup keys, value lists, data gaps, lineage) live on the tables and columns themselves.

## Raw vs curated tables

The raw transactional tables are the source of truth. Four curated rollup tables are pre-aggregated for common questions and carry their lineage in the table description: FCT_ORDERS (one row per order), DIM_SELLER (one row per seller), FCT_SALES_MONTHLY (one row per month) and FCT_SELLER_ACQUISITION (one row per acquisition channel). Prefer the table whose grain matches the question; raw and curated reconcile to the same governed metrics.

## Rules that apply to every question

- Monetary amounts are in EUR. Always render amounts with the EUR currency (the euro symbol or 'EUR'); never use '$' or any other currency symbol.
- Realized sales are delivered orders: unless the user asks otherwise, restrict revenue, sales and business-volume questions to orders whose status is 'delivered'.
- A customer id is issued per order, so the same real person appears under many customer ids over time. Count distinct customers by CUSTOMER_UNIQUE_ID, never by the per-order CUSTOMER_ID.
- The data is a static historical snapshot spanning about two years of orders, up to the most recent order date in the data. Measure any recency window (e.g. 'the last 90 days') relative to the latest order date in the data, not today's calendar date; there is no data beyond it.

## Governed metrics

Business volume (BV / GMV), average order value (AOV), on-time delivery rate, late delivery rate, order cancellation rate, average delivery time, average review score, repeat customer rate, active sellers and lead-to-deal conversion rate are governed metrics. When a question uses one of these terms, use the metric definition exactly; do not re-derive or approximate it.

cassis/domains/marketplace/README.md

---
type: Domain
title: Marketplace
description: Orders, order items, products, sellers, customers, payments, reviews, geolocation, and the curated marketplace
  rollups.
---

The transactional heart of Stallora: customers place orders; an order contains one or more order items; each order item is one product sold and fulfilled by one seller.

## Tables and routing

- **ORDERS** is the hub: items, payments, reviews and customers all join through it. One row per order, carrying status and the delivery timestamps; money does NOT live here.
- **ORDER_ITEMS** is the sale grain (one row per order line). PRICE carries business volume; FREIGHT_VALUE is shipping. Use this table for any breakdown of sales by product, category, seller or geography.
- **PRODUCTS** describe what is sold; **DIM_CATEGORY** groups the catalog category into a department.
- **SELLERS** and **CUSTOMERS** describe who sells and who buys. Customers are per-order records; the real person is CUSTOMER_UNIQUE_ID.
- **ORDER_PAYMENTS** and **ORDER_REVIEWS** hang off orders.
- **GEOLOCATION** maps postal-code prefixes to coordinates; dedup it before any join (see [geography](marketplace/geography)).

## Curated rollups

- **FCT_ORDERS**: one row per order, precomputing delivered/on-time flags, delivery days, order value and the per-order review score. The home for order-level metrics.
- **DIM_SELLER**: one row per seller with lifetime rollups (delivered orders, GMV, on-time rate, average review, average delivery days).
- **FCT_SALES_MONTHLY**: GMV, orders and AOV by month, for trends.

Each table carries its own rules on the table and column descriptions (grain, value lists, dedup keys, data gaps, lineage): read them before writing SQL.

## Topic docs

- [Measuring sales](marketplace/measuring-sales): where money lives, the delivered-only rule, BV/GMV vocabulary, counting rules.
- [Geography](marketplace/geography): countries, market concentration, where coordinates live.
- [Seller performance](marketplace/seller-performance): the per-seller signals available in the data.

<!-- cassis:nav:begin (generated, do not edit) -->

## Tables
- [STALLORA.CUSTOMERS](../../tables/STALLORA/CUSTOMERS.yml)
- [STALLORA.DIM_CATEGORY](../../tables/STALLORA/DIM_CATEGORY.yml)
- [STALLORA.DIM_SELLER](../../tables/STALLORA/DIM_SELLER.yml)
- [STALLORA.FCT_ORDERS](../../tables/STALLORA/FCT_ORDERS.yml)
- [STALLORA.FCT_SALES_MONTHLY](../../tables/STALLORA/FCT_SALES_MONTHLY.yml)
- [STALLORA.GEOLOCATION](../../tables/STALLORA/GEOLOCATION.yml)
- [STALLORA.ORDERS](../../tables/STALLORA/ORDERS.yml)
- [STALLORA.ORDER_ITEMS](../../tables/STALLORA/ORDER_ITEMS.yml)
- [STALLORA.ORDER_PAYMENTS](../../tables/STALLORA/ORDER_PAYMENTS.yml)
- [STALLORA.ORDER_REVIEWS](../../tables/STALLORA/ORDER_REVIEWS.yml)
- [STALLORA.PRODUCTS](../../tables/STALLORA/PRODUCTS.yml)
- [STALLORA.SELLERS](../../tables/STALLORA/SELLERS.yml)

## Metrics
- [Active sellers](../../metrics/active_sellers.yml)
- [Average delivery time](../../metrics/average_delivery_time.yml)
- [Average order value (AOV)](../../metrics/average_order_value.yml)
- [Average review score](../../metrics/average_review_score.yml)
- [Business volume (BV)](../../metrics/business_volume.yml)
- [Late delivery rate](../../metrics/late_delivery_rate.yml)
- [On-time delivery rate](../../metrics/on_time_delivery_rate.yml)
- [Order cancellation rate](../../metrics/order_cancellation_rate.yml)
- [Repeat customer rate](../../metrics/repeat_customer_rate.yml)
<!-- cassis:nav:end -->

cassis/metrics/business_volume.yml

description: Sum of order item price over delivered orders, excluding freight, in EUR. Also called GMV, revenue or sales.
  Use ORDER_ITEMS for breakdowns by product, category, seller or country.
display_name: Business volume (BV)
domain_path: marketplace
expression: SUM("PRICE")
filters: STALLORA.ORDERS.ORDER_STATUS = 'delivered' (requires JOIN ORDER_ITEMS.ORDER_ID = ORDERS.ORDER_ID)
name: business_volume
synonyms:
- BV
- GMV
- gross merchandise value
- revenue
- sales
table_name: ORDER_ITEMS
table_schema: STALLORA
unit: EUR

cassis/tables/STALLORA/DIM_SELLER.yml

approximate_row_count: 37140
columns:
- data_type: TEXT
  description: Unique identifier of the seller. One row per seller.
  name: SELLER_ID
  ordinal: 0
- data_type: TEXT
  description: City the seller is based in.
  name: SELLER_CITY
  ordinal: 1
- data_type: TEXT
  description: Country the seller is based in (ES, IT, DE, NL, FR).
  name: SELLER_COUNTRY
  ordinal: 2
  synonyms:
  - country
- data_type: TIMESTAMP_NTZ
  description: Purchase timestamp of the seller's first order. Null for sellers with no sales.
  name: FIRST_SALE_DATE
  ordinal: 3
- data_type: NUMBER
  description: Count of the seller's distinct delivered orders.
  name: DELIVERED_ORDERS
  ordinal: 4
  synonyms:
  - delivered order count
  unit: orders
- data_type: FLOAT
  description: 'The seller''s business volume in EUR: sum of the seller''s item PRICE over delivered orders.'
  name: GMV
  ordinal: 5
  synonyms:
  - seller gmv
  - seller revenue
  - seller sales
  unit: EUR
- data_type: NUMBER
  description: Number of distinct products the seller has sold in delivered orders.
  name: PRODUCTS_SOLD
  ordinal: 6
  unit: count
- data_type: NUMBER
  description: Share of the seller's distinct delivered orders that arrived on or before the estimated delivery date, as a
    fraction from 0 to 1 (0.92 = 92%). Null for sellers with no delivered orders.
  name: ON_TIME_RATE
  ordinal: 7
  synonyms:
  - on time rate
  - punctuality
- data_type: NUMBER
  description: The seller's mean days from purchase to delivery, over delivered orders.
  name: AVG_DELIVERY_DAYS
  ordinal: 8
  unit: days
- data_type: NUMBER
  description: The seller's average order review score on a 1 to 5 scale, averaged per distinct delivered order. Null for
    sellers with no reviewed delivered orders.
  name: AVG_REVIEW_SCORE
  ordinal: 9
  synonyms:
  - seller rating
  - average rating
  unit: score
description: 'Curated seller dimension, one row per seller. Built from SELLERS plus the seller''s delivered orders in FCT_ORDERS,
  ORDER_ITEMS and ORDER_REVIEWS. Carries lifetime rollups over the seller''s delivered orders: DELIVERED_ORDERS, GMV, PRODUCTS_SOLD,
  ON_TIME_RATE (a 0 to 1 fraction), AVG_DELIVERY_DAYS and AVG_REVIEW_SCORE (1 to 5). Sellers with no delivered orders carry
  zero or null rollups.'
domain_path: marketplace
grain:
- SELLER_ID
schema_name: STALLORA
synonyms:
- seller dimension
- seller rollup
- seller scorecard
table_name: DIM_SELLER

cassis/joins.yml contains all 13 joins. This is the complete seller-acquisition bridge object:

- condition_sql: STALLORA.DIM_SELLER.SELLER_ID = STALLORA.CLOSED_DEALS.SELLER_ID
  description: '[one_to_one] A seller''s acquisition deal, for combining seller performance with how the seller was acquired.
    Partial: only the subset of sellers acquired through a tracked deal match.'
  from_schema: STALLORA
  from_table: DIM_SELLER
  source: manual
  to_schema: STALLORA
  to_table: CLOSED_DEALS