# Finance E-commerce Data Model

### Computable Metrics

<table data-full-width="false"><thead><tr><th>Metric</th><th>Description</th></tr></thead><tbody><tr><td>Gross Revenue</td><td>Total sales revenue before any deductions.</td></tr><tr><td>Net Revenue</td><td>Sales revenue after discounts, returns, and refunds.</td></tr><tr><td>Gross Margin</td><td>Gross profit as a percentage of gross revenue.</td></tr><tr><td>Contribution Margin</td><td>Profit after variable costs, supporting fixed costs and profit.</td></tr><tr><td>Product-Level Profitability</td><td>Margin and profit by individual product.</td></tr><tr><td>Order Profitability</td><td>Margin and profit by each order.</td></tr><tr><td>Gross Merchandise Value (GMV)</td><td>Total merchandise sold value, regardless of returns or discounts.</td></tr><tr><td>Average Order Value (AOV)</td><td>Average value of an order before costs.</td></tr><tr><td>Customer Lifetime Value (CLV)</td><td>Projected revenue from a customer over their lifetime.</td></tr><tr><td>Repeat Purchase Contribution</td><td>Percentage of revenue from repeat customers.</td></tr><tr><td>Refund Rate</td><td>Percentage of orders or items refunded.</td></tr><tr><td>Return Costs</td><td>Total costs associated with returns.</td></tr><tr><td>Shipping Revenue</td><td>Total amount charged to customers for shipping.</td></tr><tr><td>Shipping Costs</td><td>Actual shipping spend.</td></tr><tr><td>Shipping Margin</td><td>Shipping revenue minus shipping costs.</td></tr><tr><td>Payment Transaction Costs</td><td>Fees charged by payment processors.</td></tr><tr><td>Payment Method Fee Analysis</td><td>Breakdown of transaction costs by payment method.</td></tr><tr><td>Discount Costs</td><td>Total discounts given, in value and as a percentage of sales.</td></tr><tr><td>Marketing Costs per Order</td><td>Average marketing cost allocated to each order.</td></tr><tr><td>CAC Payback Period</td><td>Time to recover customer acquisition costs.</td></tr><tr><td>Revenue per Customer Segment</td><td>Net revenue by customer segments (e.g., new vs returning).</td></tr><tr><td>Revenue per Product Category</td><td>Net revenue by product category.</td></tr><tr><td>Revenue per Region</td><td>Net revenue by region or geography.</td></tr><tr><td>Total Refunds</td><td>Total refunded amount and number of refund events.</td></tr><tr><td>Fraud Loss Rate</td><td>Share of revenue lost to fraud.</td></tr><tr><td>Inventory Holding Costs</td><td>Costs related to holding stock over time.</td></tr><tr><td>Stockouts Lost Revenue</td><td>Estimated lost revenue due to stockouts.</td></tr><tr><td>Inventory Turnover Rate</td><td>Frequency of inventory being sold and replaced.</td></tr><tr><td>On-Time Payment Rate</td><td>Percentage of payments received on time.</td></tr><tr><td>Average Payment Lead Time</td><td>Average time to collect payment after order confirmation.</td></tr><tr><td>Average Payment Collection Rate</td><td>Overall collection rate on payment requests.</td></tr><tr><td>Payment Failure Rate</td><td>Percentage of payment transactions that fail.</td></tr><tr><td>Total Transaction Costs</td><td>Sum of payment fees, refunds, and chargebacks.</td></tr><tr><td>Net Revenue after Payment and Logistics Costs</td><td>Final net revenue after subtracting payment and shipping costs.</td></tr><tr><td>Operating Cashflow from Orders</td><td>Cashflow directly attributable to fulfilled orders.</td></tr></tbody></table>

### Data Model

{% code overflow="wrap" lineNumbers="true" fullWidth="false" %}

```mermaid
erDiagram

orders {
  int order_id PK "Surrogate Key"
  int customer_id FK "Who placed the order"
  float order_total_value "Total order value before costs"
  float net_order_value "Order value net of discounts and returns"
  float shipping_charged "Shipping charged to the customer"
  float shipping_costs "Effective shipping costs"
  float payment_transaction_fee "Fee for processing the payment"
  float marketing_cost_per_order "Marketing cost attributed to this order"
  date ordered_at "Date of the order"
  date updated_at "Data update date"
}

order_items {
  int order_item_id PK "Surrogate Key"
  int order_id FK "The order"
  int product_id FK "The product"
  int quantity "Quantity ordered"
  float product_price "Effective price of product"
  float product_cost "Cost of goods sold"
  float discount_applied "Discount amount on product"
  float refund_amount "Refund issued if any"
  date updated_at "Data update date"
}

order_payments {
  int order_payment_id PK "Surrogate Key"
  int order_id FK "Order reference"
  string payment_method "Payment method"
  float payment_amount "Amount paid"
  float transaction_fee "Transaction fee"
  date payment_date "Date of payment"
  date updated_at "Data update date"
}

order_refunds {
  int order_refund_id PK "Surrogate Key"
  int order_id FK "Related order"
  float refund_amount "Refunded value"
  string refund_reason "Why refunded"
  date refund_date "Date of refund"
  date updated_at "Data update date"
}

discounts {
  int discount_id PK "Surrogate Key"
  int order_id FK "Order reference"
  string discount_code "Code applied"
  float discount_amount "Total discount"
  string reason "Reason for discount"
  date updated_at "Data update date"
}

products {
  int product_id PK "Surrogate Key"
  string product_name "Product name"
  string product_category "Category"
  string product_type "Type"
  float product_price "Selling price"
  float product_cost "Cost price"
  date updated_at "Data update date"
}

customers {
  int customer_id PK "Surrogate Key"
  string customer_name "Customer name"
  string customer_email "Email"
  string customer_segment "Segment classification"
  date first_order_date "First order date"
  date updated_at "Data update date"
}

shipments {
  int shipment_id PK "Surrogate Key"
  int order_id FK "Order shipped"
  string carrier "Carrier name"
  float shipping_cost "Cost of shipment"
  float shipping_charged "Amount charged to customer"
  date shipped_at "Shipping date"
  date delivered_at "Delivery date"
  date updated_at "Data update date"
}

orders ||--o{ order_items : ""
orders ||--o{ order_payments : ""
orders ||--o{ order_refunds : ""
orders ||--o{ discounts : ""
orders ||--o{ shipments : ""
order_items || -- || products : ""
orders || -- || customers : ""

```

{% endcode %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.dexlabs.io/data-models/page-1/finance-e-commerce-data-model.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.