@prefix owl: . @prefix rdf: . @prefix rdfs: . @prefix xsd: . @prefix skos: . @prefix dct: . @prefix b: . @prefix bv: . @prefix m: . @prefix t: . # =========================================================================== # ONTOLOGY HEADER # =========================================================================== a owl:Ontology ; dct:title "Acme Shop Optimized Business Ontology" ; dct:description "Business-facing ontology for Acme Shop e-commerce platform, optimized for clarity and mapped to the analytics data warehouse." ; dct:created "2026-03-03"^^xsd:date ; dct:modified "2026-03-03"^^xsd:date ; dct:contributor "Magnowlia Platform Team" ; owl:versionInfo "0.2.0"^^xsd:string ; rdfs:comment "Design decisions: (1) order_summary_view is the primary fact table - a denormalized view joining orders, customers and products. Properties like customerCountry and productCategory have domain b:Order because they are only available through this view. (2) Constants are inlined in metric expressions; bv:BusinessConstant declarations serve as documentation. (3) Metrics use structured properties (metricExpression/sourceEntity) for single-table queries and metricSql for multi-table joins." . # =========================================================================== # TECHNICAL LAYER - TABLES # =========================================================================== t:order_summary_view a m:Table ; rdfs:label "Order Summary View" ; rdfs:comment "Denormalized view joining orders, customers, and products. Primary fact table for order analytics." . t:customers a m:Table ; rdfs:label "Customers" ; rdfs:comment "Customer master data including segmentation and demographics." . t:products a m:Table ; rdfs:label "Products" ; rdfs:comment "Product catalog with categories and pricing." . t:service_items a m:Table ; rdfs:label "Service Items" ; rdfs:comment "Service offerings such as warranties, installations, and subscriptions." . # =========================================================================== # TECHNICAL LAYER - COLUMNS # =========================================================================== t:order_summary_view.order_id a m:Column ; rdfs:label "Order ID" ; rdfs:comment "Unique order identifier (PK)." ; m:belongsToTable t:order_summary_view . t:order_summary_view.order_date a m:Column ; rdfs:label "Order Date" ; rdfs:comment "Date the order was placed. Used as the primary time dimension for order metrics." ; m:belongsToTable t:order_summary_view . t:order_summary_view.order_total a m:Column ; rdfs:label "Order Total" ; rdfs:comment "Total order amount. Currency is indicated by the currency column; may be EUR or GBP." ; m:belongsToTable t:order_summary_view . t:order_summary_view.order_status a m:Column ; rdfs:label "Order Status" ; rdfs:comment "Current order fulfillment status: pending, confirmed, shipped, delivered, returned, cancelled." ; m:belongsToTable t:order_summary_view . t:order_summary_view.customer_country a m:Column ; rdfs:label "Customer Country" ; rdfs:comment "ISO country code of the ordering customer. Denormalized from customer master." ; m:belongsToTable t:order_summary_view . t:order_summary_view.product_category a m:Column ; rdfs:label "Product Category" ; rdfs:comment "Category of the ordered product. Denormalized from product catalog." ; m:belongsToTable t:order_summary_view . t:order_summary_view.payment_method a m:Column ; rdfs:label "Payment Method" ; rdfs:comment "Payment method used for the order: credit_card, paypal, bank_transfer, apple_pay." ; m:belongsToTable t:order_summary_view . t:order_summary_view.currency a m:Column ; rdfs:label "Currency" ; rdfs:comment "ISO currency code for the order amount (EUR, GBP)." ; m:belongsToTable t:order_summary_view . t:order_summary_view.customer_type a m:Column ; rdfs:label "Customer Type" ; rdfs:comment "Customer classification: regular, internal (staff), wholesale." ; m:belongsToTable t:order_summary_view . t:order_summary_view.is_test_order a m:Column ; rdfs:label "Is Test Order" ; rdfs:comment "Boolean flag indicating whether this is a test order that should be excluded from reporting." ; m:belongsToTable t:order_summary_view . t:order_summary_view.customer_id a m:Column ; rdfs:label "Customer ID" ; rdfs:comment "FK to customers table. Denormalized into the view for join-free access." ; m:belongsToTable t:order_summary_view . t:order_summary_view.quantity a m:Column ; rdfs:label "Quantity" ; rdfs:comment "Number of items in the order line." ; m:belongsToTable t:order_summary_view . t:order_summary_view.product_id a m:Column ; rdfs:label "Product ID" ; rdfs:comment "FK to products table. Identifies the product in the order." ; m:belongsToTable t:order_summary_view . t:customers.customer_id a m:Column ; rdfs:label "Customer ID" ; rdfs:comment "Customer unique identifier (PK)." ; m:belongsToTable t:customers . t:customers.customer_segment a m:Column ; rdfs:label "Customer Segment" ; rdfs:comment "Customer segment classification: Premium, Standard, New, Churned." ; m:belongsToTable t:customers . t:customers.customer_name a m:Column ; rdfs:label "Customer Name" ; rdfs:comment "Full name of the customer." ; m:belongsToTable t:customers . t:customers.customer_email a m:Column ; rdfs:label "Customer Email" ; rdfs:comment "Primary email address of the customer." ; m:belongsToTable t:customers . t:customers.customer_country a m:Column ; rdfs:label "Customer Country" ; rdfs:comment "ISO country code of the customer. Master data source for the denormalized order_summary_view.customer_country." ; m:belongsToTable t:customers . t:customers.customer_type a m:Column ; rdfs:label "Customer Type" ; rdfs:comment "Customer classification: regular, internal (staff), wholesale. Master data source for the denormalized order_summary_view.customer_type." ; m:belongsToTable t:customers . t:customers.created_at a m:Column ; rdfs:label "Created At" ; rdfs:comment "Timestamp when the customer record was created (registration date)." ; m:belongsToTable t:customers . t:products.product_id a m:Column ; rdfs:label "Product ID" ; rdfs:comment "Product unique identifier (PK)." ; m:belongsToTable t:products . t:products.product_name a m:Column ; rdfs:label "Product Name" ; rdfs:comment "Display name of the product." ; m:belongsToTable t:products . t:products.product_category a m:Column ; rdfs:label "Product Category" ; rdfs:comment "Category the product belongs to: Electronics, Clothing, Home & Garden, Sports, Books." ; m:belongsToTable t:products . t:products.product_price a m:Column ; rdfs:label "Product Price" ; rdfs:comment "Current list price of the product in EUR." ; m:belongsToTable t:products . # --- service_items columns --- t:service_items.service_id a m:Column ; rdfs:label "Service ID" ; rdfs:comment "Unique service item identifier (PK)." ; m:belongsToTable t:service_items . t:service_items.service_name a m:Column ; rdfs:label "Service Name" ; rdfs:comment "Display name of the service item." ; m:belongsToTable t:service_items . t:service_items.service_category a m:Column ; rdfs:label "Service Category" ; rdfs:comment "Category the service belongs to: Warranty, Installation, Subscription, Support." ; m:belongsToTable t:service_items . t:service_items.service_price a m:Column ; rdfs:label "Service Price" ; rdfs:comment "Current price of the service item in EUR." ; m:belongsToTable t:service_items . t:order_summary_view.service_id a m:Column ; rdfs:label "Service ID" ; rdfs:comment "FK to service_items table. Identifies the service in the order. Null when the order line is for a product." ; m:belongsToTable t:order_summary_view . # =========================================================================== # BUSINESS LAYER - CLASSES # =========================================================================== b:Order a owl:Class ; rdfs:label "Order" ; rdfs:comment "A customer order placed on the Acme Shop platform. Backed by the order_summary_view which is a denormalized view joining orders, customers, and products." ; rdfs:seeAlso ; skos:example "ORD-2025-001234, ORD-2025-005678" ; m:mapsToTable t:order_summary_view . b:Customer a owl:Class ; rdfs:label "Customer" ; skos:altLabel "Buyer", "Account" ; rdfs:comment "A registered customer of Acme Shop." ; rdfs:seeAlso ; skos:example "Jane Smith, Acme Corporation" ; m:mapsToTable t:customers . # --- Polymorphic relationship: Purchasable (Product or ServiceItem) --- # An Order can reference either a Product or a ServiceItem via the common # superclass Purchasable. This models a polymorphic association in OWL: # orderHasPurchasable has range b:Purchasable, and both Product and ServiceItem # are subclasses, so the relationship accepts either type. b:Purchasable a owl:Class ; rdfs:label "Purchasable" ; rdfs:comment "Abstract superclass for anything that can appear as a line item in an order. Subclassed by Product (physical goods) and ServiceItem (services, warranties, subscriptions). This class has no direct table mapping — use the concrete subclasses Product (t:products) or ServiceItem (t:service_items) for queries." . b:Product a owl:Class ; rdfs:subClassOf b:Purchasable ; rdfs:label "Product" ; skos:altLabel "Item", "SKU" ; rdfs:comment "A physical product available in the Acme Shop catalog." ; rdfs:seeAlso ; skos:example "Wireless Mouse Pro, Organic Cotton T-Shirt, Standing Desk" ; owl:disjointWith b:ServiceItem ; m:mapsToTable t:products . b:ServiceItem a owl:Class ; rdfs:subClassOf b:Purchasable ; rdfs:label "Service Item" ; skos:altLabel "Service", "Add-on" ; rdfs:comment "A service offering such as a warranty, installation, or subscription that can be purchased alongside or independently of a product." ; rdfs:seeAlso ; skos:example "2-Year Extended Warranty, Professional Installation, Monthly Support Plan" ; owl:disjointWith b:Product ; m:mapsToTable t:service_items . # --- Subclasses with filters --- b:WholesaleCustomer a owl:Class, m:BusinessView ; rdfs:subClassOf b:Customer ; rdfs:label "Wholesale Customer" ; rdfs:comment "Customer purchasing in bulk at wholesale prices. Identified by customer_type = 'wholesale' in the order view." ; m:mapsToTable t:order_summary_view ; m:joinCondition "order_summary_view.customer_type = 'wholesale'"^^xsd:string . b:InternalCustomer a owl:Class, m:BusinessView ; rdfs:subClassOf b:Customer ; rdfs:label "Internal Customer" ; rdfs:comment "Internal staff purchases. Typically excluded from revenue reporting. Identified by customer_type = 'internal'." ; m:mapsToTable t:order_summary_view ; m:joinCondition "order_summary_view.customer_type = 'internal'"^^xsd:string . b:PremiumWholesaleCustomer a owl:Class, m:BusinessView ; rdfs:subClassOf b:WholesaleCustomer ; rdfs:label "Premium Wholesale Customer" ; rdfs:comment "High-value wholesale customers with premium pricing agreements. Identified by customer_type = 'wholesale' AND customer_segment = 'Premium'." ; m:mapsToTable t:order_summary_view ; m:joinCondition "order_summary_view.customer_type = 'wholesale' AND customers.customer_segment = 'Premium'"^^xsd:string . b:WholesaleCustomer owl:disjointWith b:InternalCustomer . b:ReturnedOrder a owl:Class, m:BusinessView ; rdfs:subClassOf b:Order ; rdfs:label "Returned Order" ; rdfs:comment "Orders that were returned or refunded by the customer." ; m:mapsToTable t:order_summary_view ; m:joinCondition "order_summary_view.order_status = 'returned'"^^xsd:string . b:CancelledOrder a owl:Class, m:BusinessView ; rdfs:subClassOf b:Order ; rdfs:label "Cancelled Order" ; rdfs:comment "Orders that were cancelled before fulfillment. NOTE: Mutually exclusive with ReturnedOrder - an order is either cancelled or returned, not both." ; rdfs:seeAlso b:ReturnedOrder ; m:mapsToTable t:order_summary_view ; m:joinCondition "order_summary_view.order_status = 'cancelled'"^^xsd:string . b:ReturnedOrder owl:disjointWith b:CancelledOrder . # =========================================================================== # BUSINESS LAYER - RELATIONSHIPS # =========================================================================== b:customerHasOrder a owl:ObjectProperty ; rdfs:domain b:Customer ; rdfs:range b:Order ; rdfs:label "customer has order" ; rdfs:comment "Links a customer to all orders they have placed. Joined via customer_id." ; owl:inverseOf b:orderForCustomer ; m:realizedBy "customers.customer_id = order_summary_view.customer_id"^^xsd:string . b:orderForCustomer a owl:ObjectProperty ; rdfs:domain b:Order ; rdfs:range b:Customer ; rdfs:label "order for customer" ; rdfs:comment "Links an order back to the customer who placed it. Inverse of customerHasOrder." ; owl:inverseOf b:customerHasOrder ; m:realizedBy "order_summary_view.customer_id = customers.customer_id"^^xsd:string . b:orderHasProduct a owl:ObjectProperty ; rdfs:subPropertyOf b:orderHasPurchasable ; rdfs:domain b:Order ; rdfs:range b:Product ; rdfs:label "order has product" ; rdfs:comment "Links an order to the physical product purchased. Joined via product_id." ; owl:inverseOf b:productInOrder ; m:realizedBy "order_summary_view.product_id = products.product_id"^^xsd:string . b:productInOrder a owl:ObjectProperty ; rdfs:domain b:Product ; rdfs:range b:Order ; rdfs:label "product in order" ; rdfs:comment "Links a product to all orders in which it appears. Inverse of orderHasProduct." ; owl:inverseOf b:orderHasProduct ; m:realizedBy "products.product_id = order_summary_view.product_id"^^xsd:string . # --- Polymorphic purchasable relationship --- # orderHasPurchasable accepts any Purchasable subclass (Product or ServiceItem). # The specific subtype relationships (orderHasProduct, orderHasServiceItem) are # declared as sub-properties, so navigating orderHasPurchasable returns both. b:orderHasPurchasable a owl:ObjectProperty ; rdfs:domain b:Order ; rdfs:range b:Purchasable ; rdfs:label "order has purchasable" ; rdfs:comment "Links an order to any purchasable item (product or service). This is the polymorphic relationship — use orderHasProduct or orderHasServiceItem for type-specific access. Realized via product_id or service_id depending on the subtype." ; m:realizedBy "order_summary_view.product_id = products.product_id OR order_summary_view.service_id = service_items.service_id"^^xsd:string . b:orderHasServiceItem a owl:ObjectProperty ; rdfs:subPropertyOf b:orderHasPurchasable ; rdfs:domain b:Order ; rdfs:range b:ServiceItem ; rdfs:label "order has service item" ; rdfs:comment "Links an order to the service item purchased. Joined via service_id." ; owl:inverseOf b:serviceItemInOrder ; m:realizedBy "order_summary_view.service_id = service_items.service_id"^^xsd:string . b:serviceItemInOrder a owl:ObjectProperty ; rdfs:domain b:ServiceItem ; rdfs:range b:Order ; rdfs:label "service item in order" ; rdfs:comment "Links a service item to all orders in which it appears. Inverse of orderHasServiceItem." ; owl:inverseOf b:orderHasServiceItem ; m:realizedBy "service_items.service_id = order_summary_view.service_id"^^xsd:string . # =========================================================================== # BUSINESS LAYER - PROPERTIES (Dimensions) # =========================================================================== b:orderId a owl:DatatypeProperty ; rdfs:label "Order ID" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Unique order identifier." ; m:mapsToColumn t:order_summary_view.order_id . b:orderDate a owl:DatatypeProperty ; rdfs:label "Order Date" ; rdfs:domain b:Order ; rdfs:range xsd:date ; rdfs:comment "Date when the order was placed. Primary time dimension for all order-related metrics." ; m:mapsToColumn t:order_summary_view.order_date . b:orderTotal a owl:DatatypeProperty ; rdfs:label "Order Total" ; rdfs:domain b:Order ; rdfs:range xsd:decimal ; rdfs:comment "Total monetary value of the order. Currency varies (see orderCurrency)." ; m:mapsToColumn t:order_summary_view.order_total . b:orderStatus a owl:DatatypeProperty ; rdfs:label "Order Status" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Current fulfillment status of the order." ; m:mapsToColumn t:order_summary_view.order_status ; bv:enumType "dynamic" . b:customerCountry a owl:DatatypeProperty ; rdfs:label "Customer Country" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "ISO country code of the ordering customer. Only available for customers who have placed orders. See b:customerCountryMaster for the master data version." ; bv:masterProperty b:customerCountryMaster ; rdfs:seeAlso b:customerCountryMaster ; m:mapsToColumn t:order_summary_view.customer_country ; bv:enumType "country" . b:productCategory a owl:DatatypeProperty ; rdfs:label "Product Category" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Category of the ordered product. Only available for products that appear in orders. See b:productCategoryMaster for the master data version." ; bv:masterProperty b:productCategoryMaster ; rdfs:seeAlso b:productCategoryMaster ; m:mapsToColumn t:order_summary_view.product_category ; bv:enumType "dynamic" . b:paymentMethod a owl:DatatypeProperty ; rdfs:label "Payment Method" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Method of payment used for the order." ; m:mapsToColumn t:order_summary_view.payment_method ; bv:enumType "dynamic" . b:orderCurrency a owl:DatatypeProperty ; rdfs:label "Order Currency" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "ISO currency code for the order amount." ; m:mapsToColumn t:order_summary_view.currency ; bv:enumType "currency" . b:customerType a owl:DatatypeProperty ; rdfs:label "Customer Type" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Classification of the customer. Determines eligibility for reporting (internal/test orders are typically excluded). See b:customerTypeMaster for the master data version." ; bv:masterProperty b:customerTypeMaster ; rdfs:seeAlso b:customerTypeMaster ; m:mapsToColumn t:order_summary_view.customer_type ; bv:enumType "dynamic" . b:customerId a owl:DatatypeProperty ; rdfs:label "Customer ID" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Customer identifier. FK to customers table, available directly in order_summary_view." ; m:mapsToColumn t:order_summary_view.customer_id . b:orderQuantity a owl:DatatypeProperty ; rdfs:label "Order Quantity" ; rdfs:domain b:Order ; rdfs:range xsd:integer ; rdfs:comment "Number of items in the order." ; m:mapsToColumn t:order_summary_view.quantity . b:isTestOrder a owl:DatatypeProperty ; rdfs:label "Is Test Order" ; rdfs:domain b:Order ; rdfs:range xsd:boolean ; rdfs:comment "Boolean flag indicating whether this is a test order. Test orders are excluded from revenue and performance metrics." ; m:mapsToColumn t:order_summary_view.is_test_order ; bv:enumType "boolean" . b:productId a owl:DatatypeProperty ; rdfs:label "Product ID" ; rdfs:domain b:Order ; rdfs:range xsd:string ; rdfs:comment "Product identifier. FK to products table, available directly in order_summary_view." ; m:mapsToColumn t:order_summary_view.product_id . b:productName a owl:DatatypeProperty ; rdfs:label "Product Name" ; rdfs:domain b:Product ; rdfs:range xsd:string ; rdfs:comment "Display name of the product." ; m:mapsToColumn t:products.product_name . b:productCategoryMaster a owl:DatatypeProperty ; rdfs:label "Product Category (Master)" ; rdfs:domain b:Product ; rdfs:range xsd:string ; rdfs:comment "Category from the product catalog master table. See also b:productCategory on b:Order for the denormalized version available in order analytics." ; rdfs:seeAlso b:productCategory ; m:mapsToColumn t:products.product_category ; bv:enumType "dynamic" . b:productPrice a owl:DatatypeProperty ; rdfs:label "Product Price" ; rdfs:domain b:Product ; rdfs:range xsd:decimal ; rdfs:comment "Current list price of the product in EUR." ; m:mapsToColumn t:products.product_price . # --- ServiceItem properties --- b:serviceId a owl:DatatypeProperty ; rdfs:label "Service ID" ; rdfs:domain b:ServiceItem ; rdfs:range xsd:string ; rdfs:comment "Unique service item identifier." ; m:mapsToColumn t:service_items.service_id . b:serviceName a owl:DatatypeProperty ; rdfs:label "Service Name" ; rdfs:domain b:ServiceItem ; rdfs:range xsd:string ; rdfs:comment "Display name of the service item." ; m:mapsToColumn t:service_items.service_name . b:serviceCategory a owl:DatatypeProperty ; rdfs:label "Service Category" ; rdfs:domain b:ServiceItem ; rdfs:range xsd:string ; rdfs:comment "Category of the service item." ; m:mapsToColumn t:service_items.service_category ; bv:enumType "dynamic" . b:servicePrice a owl:DatatypeProperty ; rdfs:label "Service Price" ; rdfs:domain b:ServiceItem ; rdfs:range xsd:decimal ; rdfs:comment "Current price of the service item in EUR." ; m:mapsToColumn t:service_items.service_price . b:customerSegment a owl:DatatypeProperty ; rdfs:label "Customer Segment" ; rdfs:domain b:Customer ; rdfs:range xsd:string ; rdfs:comment "Customer segment classification from the customer master table." ; m:mapsToColumn t:customers.customer_segment ; bv:enumType "dynamic" . b:customerName a owl:DatatypeProperty ; rdfs:label "Customer Name" ; rdfs:domain b:Customer ; rdfs:range xsd:string ; rdfs:comment "Full name of the customer." ; m:mapsToColumn t:customers.customer_name . b:customerEmail a owl:DatatypeProperty ; rdfs:label "Customer Email" ; rdfs:domain b:Customer ; rdfs:range xsd:string ; rdfs:comment "Primary email address of the customer. Contains PII — access is restricted." ; bv:sensitivityLevel "Confidential" ; bv:complianceCategory "PII" ; bv:maskingRule "redact" ; m:mapsToColumn t:customers.customer_email . b:customerCountryMaster a owl:DatatypeProperty ; rdfs:label "Customer Country (Master)" ; rdfs:domain b:Customer ; rdfs:range xsd:string ; rdfs:comment "ISO country code from the customer master table. See also b:customerCountry on b:Order for the denormalized version available in order analytics." ; rdfs:seeAlso b:customerCountry ; m:mapsToColumn t:customers.customer_country ; bv:enumType "dynamic" . b:customerTypeMaster a owl:DatatypeProperty ; rdfs:label "Customer Type (Master)" ; rdfs:domain b:Customer ; rdfs:range xsd:string ; rdfs:comment "Customer classification from the master table. See also b:customerType on b:Order for the denormalized version available in order analytics." ; rdfs:seeAlso b:customerType ; m:mapsToColumn t:customers.customer_type ; bv:enumType "dynamic" . b:customerCreatedAt a owl:DatatypeProperty ; rdfs:label "Customer Registration Date" ; rdfs:domain b:Customer ; rdfs:range xsd:dateTime ; rdfs:comment "Timestamp when the customer registered." ; m:mapsToColumn t:customers.created_at . # =========================================================================== # BUSINESS LAYER - CONSTANTS # =========================================================================== b:gbpToEurExchangeRate a bv:BusinessConstant ; rdfs:label "GBP to EUR Exchange Rate" ; rdfs:comment "Fixed exchange rate for converting GBP to EUR. Rate: 1 GBP = 0.85 EUR. To convert GBP amounts to EUR, divide by 0.85. This value is inlined in metric expressions." ; bv:constantValue "0.85"^^xsd:decimal . # =========================================================================== # ACCESS CONTROL # =========================================================================== # =========================================================================== # BUSINESS LAYER - METRICS (with structured properties) # =========================================================================== b:totalRevenueMetric a bv:Metric ; rdfs:label "Total Revenue" ; skos:altLabel "Gross Revenue", "Total Sales" ; rdfs:comment "Total order revenue with automatic GBP to EUR conversion. Includes ALL order types and statuses. For order count see b:orderCountMetric; for average value see b:averageOrderValueMetric." ; rdfs:seeAlso b:orderCountMetric, b:averageOrderValueMetric ; bv:metricExpression "SUM(CASE WHEN b:orderCurrency = 'GBP' THEN b:orderTotal / {{gbpToEurExchangeRate}} ELSE b:orderTotal END)" ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderTotal, b:orderCurrency ; bv:dependsOnConstant b:gbpToEurExchangeRate ; bv:metricCategory "Revenue" . b:orderCountMetric a bv:Metric ; rdfs:label "Order Count" ; skos:altLabel "Number of Orders", "Total Orders" ; rdfs:comment "Total number of orders placed. Counts all orders regardless of status. For revenue see b:totalRevenueMetric." ; rdfs:seeAlso b:totalRevenueMetric ; bv:metricExpression "COUNT(*)" ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderId ; bv:metricCategory "Orders" . b:averageOrderValueMetric a bv:Metric ; rdfs:label "Average Order Value" ; skos:altLabel "AOV", "Avg Order Value" ; rdfs:comment "Average revenue per order. Equivalent to total_revenue / order_count." ; rdfs:seeAlso b:totalRevenueMetric, b:orderCountMetric ; bv:metricExpression "AVG(b:orderTotal)" ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderTotal ; bv:metricCategory "Revenue" . b:returnRateMetric a bv:Metric ; rdfs:label "Return Rate" ; skos:altLabel "Return %", "Refund Rate" ; rdfs:comment "Percentage of orders that were returned or refunded, excluding test orders and internal staff purchases. Formula: returned_orders / total_orders * 100. A value of 5% means one in twenty orders was returned." ; bv:metricExpression """100.0 * SUM(CASE WHEN b:orderStatus = 'returned' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0)""" ; bv:sourceEntity b:Order ; bv:metricPreFilter "b:customerType <> 'internal' AND b:isTestOrder = false" ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderStatus, b:customerType, b:isTestOrder ; bv:metricCategory "Orders" . b:uniqueCustomersMetric a bv:Metric ; rdfs:label "Unique Customers" ; skos:altLabel "Distinct Customers", "Active Buyers" ; rdfs:comment "Number of distinct customers who placed orders in the period. NOTE: This is an ACTIVITY-BASED count - only customers with at least one order are counted. This is NOT a master data count of all registered customers." ; bv:metricExpression "COUNT(DISTINCT b:customerId)" ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:customerId ; bv:metricCategory "Customers" . b:totalItemsSoldMetric a bv:Metric ; rdfs:label "Total Items Sold" ; skos:altLabel "Units Sold", "Total Quantity" ; rdfs:comment "Total number of items sold across all orders." ; bv:metricExpression "SUM(b:orderQuantity)" ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderQuantity ; bv:metricCategory "Orders" . # Multi-table metric using SQL template escape hatch b:revenuePerCustomerSegmentMetric a bv:Metric ; rdfs:label "Revenue Per Customer Segment" ; rdfs:comment "Average revenue per customer, broken down by customer segment. Requires joining orders with customer master data. For total revenue without segmentation, see b:totalRevenueMetric." ; rdfs:seeAlso b:totalRevenueMetric ; bv:metricSql """ SELECT DATE_TRUNC('{{granularity}}', order_summary_view.order_date) AS time_dimension {{select_dimensions}}, CASE WHEN COUNT(DISTINCT customers.customer_id) > 0 THEN SUM(order_summary_view.order_total) / COUNT(DISTINCT customers.customer_id) ELSE 0 END AS metric_value FROM order_summary_view INNER JOIN customers ON order_summary_view.customer_id = customers.customer_id {{join_clauses}} WHERE order_summary_view.order_date >= '{{start_date}}' AND order_summary_view.order_date < '{{end_date}}' {{where_filters}} GROUP BY 1 {{group_by_dimensions}} ORDER BY 1 """ ; bv:sourceEntity b:Order ; bv:timeDimension b:orderDate ; bv:dependsOnProperty b:orderTotal, b:customerId, b:customerSegment ; bv:dependsOnRelationship b:customerHasOrder ; bv:metricCategory "Revenue" . b:newCustomerRegistrationsMetric a bv:Metric ; rdfs:label "New Customer Registrations" ; skos:altLabel "New Signups", "Customer Registrations" ; rdfs:comment "Number of new customer registrations per period. Excludes internal (staff) customers. For activity-based customer counts (customers who placed orders), see b:uniqueCustomersMetric." ; rdfs:seeAlso b:uniqueCustomersMetric ; bv:metricExpression "COUNT(*)" ; bv:sourceEntity b:Customer ; bv:metricPreFilter "b:customerTypeMaster <> 'internal'" ; bv:timeDimension b:customerCreatedAt ; bv:dependsOnProperty b:customerTypeMaster ; bv:metricCategory "Customers" . # ================================================================ # CORRELATIONS — How metrics relate to each other # ================================================================ # Statistical: new customer registrations correlate with return rate b:newRegistrations_correlates_returnRate a bv:MetricCorrelation ; rdfs:label "New Registrations ↔ Return Rate" ; rdfs:comment "New customers return items at higher rates than repeat buyers — unfamiliar with sizing and product quality. The effect flattens at high registration volumes." ; bv:sourceMetric b:newCustomerRegistrationsMetric ; bv:targetMetric b:returnRateMetric ; bv:correlationType "statistical" ; bv:correlationStrength "0.62" ; bv:correlationCurve "logarithmic" . # Causal: high return rates drive down average order value b:returnRate_drives_averageOrderValue a bv:MetricCorrelation ; rdfs:label "Return Rate → AOV" ; rdfs:comment "Higher return rates tend to reduce effective AOV as returned items deflate averages." ; bv:sourceMetric b:returnRateMetric ; bv:targetMetric b:averageOrderValueMetric ; bv:correlationType "causal" ; bv:correlationStrength "moderate" ; bv:correlationCurve "inverse" .