E-Commerce Platform Example

This example shows a complete data model for an e-commerce platform, demonstrating complex relationships, multiple data sources, and comprehensive data products.

Scenario

You're building an e-commerce platform that needs to track:

Customers and their orders
Product catalog and inventory
Payments and shipping
Marketing campaigns and attribution
Analytics and reporting

Conceptual Model

File: `models/conceptual/ecommerce.mml`

mml

domain "Customer" {
  color = "#3498db"
  description = "Customer data and accounts"

  entity "Customer" {
    type = "entity"
    description = "Customer profiles and contact information"
    pii = true

    attribute "email" {
      type = "string"
      required = true
      pii = true
    }

    attribute "first_name" {
      type = "string"
      pii = true
    }

    attribute "last_name" {
      type = "string"
      pii = true
    }

    attribute "created_at" {
      type = "date"
      required = true
    }
  }

  entity "Address" {
    type = "entity"
    description = "Customer shipping and billing addresses"
    pii = true

    belongs-to "Customer.Customer"
  }
}

domain "Product" {
  color = "#e74c3c"
  description = "Product catalog and inventory"

  entity "Product" {
    type = "entity"
    description = "Products available for purchase"

    attribute "sku" {
      type = "string"
      required = true
    }

    attribute "name" {
      type = "string"
      required = true
    }

    attribute "price" {
      type = "number"
      required = true
    }

    attribute "category" {
      type = "string"
    }
  }

  entity "Inventory" {
    type = "entity"
    description = "Product inventory levels by warehouse"

    attribute "quantity" {
      type = "number"
      required = true
    }

    belongs-to "Product.Product"
  }
}

domain "Sales" {
  color = "#2ecc71"
  description = "Orders and transactions"

  entity "Order" {
    type = "entity"
    description = "Customer orders"

    attribute "order_number" {
      type = "string"
      required = true
    }

    attribute "order_date" {
      type = "date"
      required = true
    }

    attribute "status" {
      type = "string"
      required = true
    }

    attribute "total_amount" {
      type = "number"
      required = true
    }

    belongs-to "Customer.Customer"
  }

  entity "Order_Line" {
    type = "entity"
    description = "Individual items in an order"

    attribute "quantity" {
      type = "number"
      required = true
    }

    attribute "unit_price" {
      type = "number"
      required = true
    }

    attribute "line_total" {
      type = "number"
      required = true
    }

    belongs-to "Sales.Order"
    contains "Product.Product"
  }

  entity "Payment" {
    type = "entity"
    description = "Payment transactions"

    attribute "payment_method" {
      type = "string"
      required = true
    }

    attribute "amount" {
      type = "number"
      required = true
    }

    attribute "status" {
      type = "string"
      required = true
    }

    belongs-to "Sales.Order"
  }
}

domain "Analytics" {
  color = "#9b59b6"
  description = "Business metrics and derived data"

  entity "Total_Revenue" {
    type = "metric"
    description = "Total revenue from all completed orders"
    business_logic = "SUM(Order.total_amount WHERE status = 'completed')"

    derived-from "Sales.Order"
  }

  entity "Average_Order_Value" {
    type = "metric"
    description = "Average order value"
    business_logic = "AVG(Order.total_amount WHERE status = 'completed')"

    derived-from "Sales.Order"
  }

  entity "Customer_Lifetime_Value" {
    type = "metric"
    description = "Total revenue per customer"
    business_logic = "SUM(Order.total_amount) GROUP BY customer_id"

    derived-from "Sales.Order"
    contains "Customer.Customer"
  }

  entity "Product_Performance" {
    type = "derivation"
    description = "Product sales performance metrics"
    business_logic = """
      SELECT
        product_id,
        SUM(quantity) as units_sold,
        SUM(line_total) as revenue,
        COUNT(DISTINCT order_id) as order_count
      FROM order_lines
      GROUP BY product_id
    """

    derived-from "Sales.Order_Line"
    derived-from "Product.Product"
  }

  entity "Customer_Cohorts" {
    type = "derivation"
    description = "Customer retention analysis by cohort"
    business_logic = "GROUP BY signup_month WITH retention and revenue metrics"

    derived-from "Customer.Customer"
    derived-from "Sales.Order"
  }
}

Logical Model

File: `models/logical/sources.mml`

mml

source "PostgreSQL_Production" {
  type = "postgres"
  description = "Production PostgreSQL database"
  host = "prod-db.ecommerce.com"
  database = "ecommerce"
  owner = "platform-team"
  refresh_frequency = "realtime"

  entity "Customer.Customer" {
    table = "customers"
  }

  entity "Customer.Address" {
    table = "addresses"
  }

  entity "Product.Product" {
    table = "products"
  }

  entity "Product.Inventory" {
    table = "inventory"
  }

  entity "Sales.Order" {
    table = "orders"
  }

  entity "Sales.Order_Line" {
    table = "order_items"
  }
}

source "Stripe_API" {
  type = "api"
  description = "Stripe payment processing API"
  host = "api.stripe.com"
  owner = "finance-team"
  refresh_frequency = "hourly"

  entity "Sales.Payment" {
    endpoint = "charges"
    notes = "Payment transactions from Stripe"
  }
}

source "Segment_Events" {
  type = "kafka"
  description = "User behavior events via Segment"
  host = "events.ecommerce.com:9092"
  owner = "analytics-team"
  refresh_frequency = "realtime"

  entity "Analytics.Page_Views" {
    topic = "page_views"
  }

  entity "Analytics.Product_Views" {
    topic = "product_views"
  }
}

Data Products

File: `products/sales_analytics.mml`

mml

data_product "Sales_Analytics" {
  description = "E-commerce sales performance metrics"
  owner = "analytics-team"
  refresh_schedule = "0 * * * *"  // Hourly

  consumer "sales-team"
  consumer "executives"
  consumer "https://analytics.ecommerce.com/sales"

  report "Revenue_Dashboard" {
    type = "dashboard"
    description = "Real-time revenue and order metrics"

    uses "Sales.Order"
    uses "Sales.Order_Line"
    uses "Analytics.Total_Revenue"

    metric "Daily_Revenue" {
      description = "Revenue for today"
      calculation = "SUM(Order.total_amount WHERE order_date = TODAY AND status = 'completed')"
      target_value = "$50K daily"
      uses "Sales.Order"
    }

    metric "Orders_Today" {
      description = "Number of orders placed today"
      calculation = "COUNT(DISTINCT order_id WHERE order_date = TODAY)"
      target_value = "500 orders"
      uses "Sales.Order"
    }

    metric "Average_Order_Value" {
      description = "Average order value today"
      calculation = "SUM(total_amount) / COUNT(order_id) WHERE order_date = TODAY"
      target_value = "$100"
      uses "Sales.Order"
    }

    metric "Conversion_Rate" {
      description = "Orders / Sessions percentage"
      calculation = "(COUNT(orders) / COUNT(sessions)) * 100"
      target_value = "2.5%"
      uses "Sales.Order"
      uses "Analytics.Page_Views"
    }
  }

  report "Product_Performance" {
    type = "dashboard"
    description = "Product-level sales metrics"

    uses "Product.Product"
    uses "Sales.Order_Line"
    uses "Analytics.Product_Performance"

    metric "Top_Products" {
      description = "Best-selling products by revenue"
      calculation = "SUM(line_total) GROUP BY product_id ORDER BY revenue DESC LIMIT 10"
      uses "Sales.Order_Line"
      uses "Product.Product"
    }

    metric "Low_Stock_Products" {
      description = "Products with low inventory"
      calculation = "SELECT product_id WHERE inventory_quantity < reorder_point"
      uses "Product.Inventory"
      uses "Product.Product"
    }
  }
}

File: `products/customer_analytics.mml`

mml

data_product "Customer_Analytics" {
  description = "Customer behavior and lifecycle analytics"
  owner = "customer-success-team"
  refresh_schedule = "0 6 * * *"  // Daily at 6am

  consumer "customer-success"
  consumer "marketing-team"

  report "Customer_Overview" {
    type = "dashboard"
    description = "Customer metrics and segments"

    uses "Customer.Customer"
    uses "Sales.Order"
    uses "Analytics.Customer_Lifetime_Value"

    metric "Total_Customers" {
      description = "Total number of customers"
      calculation = "COUNT(DISTINCT customer_id)"
      uses "Customer.Customer"
    }

    metric "Active_Customers" {
      description = "Customers who ordered in last 90 days"
      calculation = "COUNT(DISTINCT customer_id WHERE last_order_date >= NOW() - 90 days)"
      uses "Customer.Customer"
      uses "Sales.Order"
    }

    metric "Average_LTV" {
      description = "Average customer lifetime value"
      calculation = "AVG(lifetime_value)"
      target_value = "$500"
      uses "Analytics.Customer_Lifetime_Value"
    }

    metric "Repeat_Purchase_Rate" {
      description = "Percentage of customers with multiple orders"
      calculation = "(COUNT(customers WHERE order_count > 1) / COUNT(customers)) * 100"
      target_value = "30%"
      uses "Customer.Customer"
      uses "Sales.Order"
    }
  }

  report "Cohort_Analysis" {
    type = "table"
    description = "Monthly cohort retention analysis"

    uses "Analytics.Customer_Cohorts"

    metric "Cohort_Retention" {
      description = "Retention rate by cohort and month"
      calculation = "active_customers / cohort_size * 100"
      uses "Analytics.Customer_Cohorts"
    }
  }
}

Key Features Demonstrated

Multiple Domains
- Customer, Product, Sales, Analytics
- Clear separation of concerns
Complex Relationships
- Orders contain order lines
- Order lines reference products
- Payments belong to orders
Multiple Source Types
- PostgreSQL for transactional data
- Stripe API for payments
- Kafka for behavioral events
Entity Types
- entity - Base entities (Customer, Product, Order)
- metric - Calculated KPIs (Revenue, AOV, LTV)
- derivation - Transformed datasets (Cohorts, Product Performance)
Data Products
- Sales Analytics for real-time monitoring
- Customer Analytics for retention
- Multiple reports per product
PII Handling
- Customer data marked as PII
- Addresses marked sensitive
- Clear data governance

Visual Model

Customer Domain (Blue)
├── Customer (entity)
│   └── PII: email, name
└── Address (entity)
    └── belongs-to → Customer

Product Domain (Red)
├── Product (entity)
│   └── sku, name, price
└── Inventory (entity)
    └── belongs-to → Product

Sales Domain (Green)
├── Order (entity)
│   ├── belongs-to → Customer
│   └── order_date, total
├── Order_Line (entity)
│   ├── belongs-to → Order
│   └── contains → Product
└── Payment (entity)
    └── belongs-to → Order

Analytics Domain (Purple)
├── Total_Revenue (metric)
│   └── derived-from → Order
├── Average_Order_Value (metric)
│   └── derived-from → Order
├── Customer_Lifetime_Value (metric)
│   └── derived-from → Order + Customer
├── Product_Performance (derivation)
│   └── derived-from → Order_Line + Product
└── Customer_Cohorts (derivation)
    └── derived-from → Customer + Order

Data Products
├── Sales_Analytics
│   ├── Revenue_Dashboard
│   └── Product_Performance
└── Customer_Analytics
    ├── Customer_Overview
    └── Cohort_Analysis

Next Steps

Add shipping tracking - Model fulfillment workflow
Add marketing attribution - Track campaign performance
Add returns/refunds - Handle reverse logistics
Add recommendations - Build product recommendation engine
Add real-time inventory - Track stock levels dynamically

Basic Model - Simple introduction
SaaS Analytics - Subscription metrics

E-Commerce Platform Example ​

Scenario ​

Conceptual Model ​

File: models/conceptual/ecommerce.mml ​

Logical Model ​

File: models/logical/sources.mml ​

Data Products ​

File: products/sales_analytics.mml ​

File: products/customer_analytics.mml ​

Key Features Demonstrated ​

Visual Model ​

Next Steps ​

Related Examples ​