> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocular.dev/llms.txt
> Use this file to discover all available pages before exploring further.
# Sales
> Order-level e-commerce sales data in Ocular — gross & net revenue, AOV, returns, discounts, customer mix, and per-channel sales performance.
The unified data model for e-commerce sales. Order, customer, product, and return data consolidated across every sales channel, with revenue accurately net of returns, cancellations, and tax adjustments.
## At a glance
Order × line item × channel
Shopify, Amazon, etc.
60+ across 10 categories
100+ across 13 categories
## What this data model represents
**Grain:** one row per `order_id × order_item_id × sales_channel`. A single multi-item order placed on one channel produces multiple rows; the same SKU sold across channels produces one row per channel.
**Metrics, grouped by category** — every number you can compute on this data model. Expand below for examples in each.
```mermaid theme={null}
flowchart LR
R1[Revenue]:::cat --- S
R2[Orders]:::cat --- S
R3[Customers]:::cat --- S
R4["Units & inventory"]:::cat --- S
R5["Cost & profitability"]:::cat --- S
S(["Metrics"]):::root
S --- R6["Discount & promotion"]:::cat
S --- R7["Return & exchange"]:::cat
S --- R8["Percentage & rate"]:::cat
S --- R9["Average value & ratio"]:::cat
S --- R10["Behavioral & frequency"]:::cat
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#2563eb,color:#fff,stroke:#1d4ed8,stroke-width:1.5px
```
```mermaid theme={null}
flowchart LR
r1[Net Revenue]:::leaf --- Revenue
r2[Gross Revenue]:::leaf --- Revenue
r3[Total Sales]:::leaf --- Revenue
o1[Net Orders]:::leaf --- Orders
o2[Gross Orders]:::leaf --- Orders
o3[Returned Orders]:::leaf --- Orders
c1[Total Customers]:::leaf --- Customers
c2[New Customers]:::leaf --- Customers
c3[Repeat Customers]:::leaf --- Customers
u1[Net Units Sold]:::leaf --- Units["Units & inventory"]
u2[Units per Order]:::leaf --- Units
Revenue:::cat --- S
Orders:::cat --- S
Customers:::cat --- S
Units:::cat --- S
S(["Metrics"]):::root
S --- Profit["Cost & profitability"]:::cat
S --- Returns["Return & exchange"]:::cat
S --- Rates["Percentage & rate"]:::cat
S --- Avg["Average value & ratio"]:::cat
Profit --- p1[Gross Profit CM1]:::leaf
Profit --- p2[Net COGS]:::leaf
Returns --- rt1[Avg Days to Refund]:::leaf
Rates --- pc1["% Orders Returned"]:::leaf
Rates --- pc2["% New Customers"]:::leaf
Avg --- av1[Net AOV]:::leaf
Avg --- av2[Customer LTV]:::leaf
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#2563eb,color:#fff,stroke:#1d4ed8,stroke-width:1.5px
classDef leaf fill:#eff6ff,color:#1e3a8a,stroke:#bfdbfe,stroke-width:1px
```
**Dimensions, grouped by category** — every way you can split, filter, or group those metrics. Expand below for examples in each.
```mermaid theme={null}
flowchart LR
D1["Order & channel"]:::cat --- S
D2[Product]:::cat --- S
D3[Customer]:::cat --- S
D4[Time]:::cat --- S
D5["Financial & pricing"]:::cat --- S
D6["Discount & promotion"]:::cat --- S
S(["Dimensions"]):::root
S --- D7["Payment & gift card"]:::cat
S --- D8["Status & fulfilment"]:::cat
S --- D9[Address]:::cat
S --- D10["Return & exchange"]:::cat
S --- D11["Holiday & occasion"]:::cat
S --- D12["System & metadata"]:::cat
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#9333ea,color:#fff,stroke:#7e22ce,stroke-width:1.5px
```
```mermaid theme={null}
flowchart LR
ch1[Sales Channel Name]:::leaf --- Channel["Order & channel"]
ch2[Brand Name]:::leaf --- Channel
pr1[Product Category]:::leaf --- Product
pr2[Product Subcategory]:::leaf --- Product
pr3[Product Name]:::leaf --- Product
cu1[Customer Visit Type]:::leaf --- Customer
cu2[Customer is Member]:::leaf --- Customer
ti1[Month Name]:::leaf --- Time
ti2[Week Start Date]:::leaf --- Time
ti3[Day Name]:::leaf --- Time
Channel:::cat --- M
Product:::cat --- M
Customer:::cat --- M
Time:::cat --- M
M(["Dimensions"]):::root
M --- Discount["Discount & promotion"]:::cat
M --- Status["Status & fulfilment"]:::cat
M --- Address:::cat
M --- ReturnEx["Return & exchange"]:::cat
Discount --- di1[Discount Code]:::leaf
Discount --- di2[Discount Type]:::leaf
Status --- st1[Payment Status]:::leaf
Status --- st2[Order Fulfilled By]:::leaf
Address --- ad1[Shipping City]:::leaf
Address --- ad2[Shipping State]:::leaf
ReturnEx --- re1[Is Returned]:::leaf
ReturnEx --- re2[Is Exchanged]:::leaf
classDef root fill:#0f172a,color:#f8fafc,stroke:#0f172a,stroke-width:2px
classDef cat fill:#9333ea,color:#fff,stroke:#7e22ce,stroke-width:1.5px
classDef leaf fill:#faf5ff,color:#581c87,stroke:#e9d5ff,stroke-width:1px
```
**Source:** sales-channel platforms (Shopify, Amazon, etc.), refreshed daily.
**What's special:** revenue adjustments for returns, cancellations, and tax-inclusive vs. tax-exclusive pricing are pre-calculated. Net Revenue and Contribution Margin 1 stay accurate at any time grain and across any dimension — you don't have to re-derive them per query.
## Slice by
Every dimension you can group or filter by, grouped by category.
### Order & channel
| Dimension | Description |
| ------------------ | ---------------------------------------------------------- |
| Order ID | Unique identifier for each order |
| Order Item ID | Unique identifier for each line item within an order |
| Sales Channel Name | Channel where the order was placed (Shopify, Amazon, etc.) |
| Sales Channel ID | Technical identifier for the sales channel |
| Brand Name | Brand associated with the order or channel |
### Product
| Dimension | Description |
| ------------------- | ------------------------------------------------ |
| Product Name | Product title |
| Product SKU | Master SKU, consistent across channels |
| Channel SKU | SKU as listed on the specific sales channel |
| Channel Product ID | Product identifier on the sales-channel platform |
| Product Category | High-level product category |
| Product Subcategory | Subcategory within the category |
| Product Size | Size attribute (S, M, L, XL, etc.) |
### Customer
| Dimension | Description |
| ---------------------------------------- | ------------------------------------------------------ |
| Customer ID | Unique customer identifier |
| Customer First Name / Customer Last Name | Customer name fields |
| Customer is Member | Boolean — loyalty/membership program status |
| Customer Visit Type | New vs. returning customer for this order |
| Customer First Order ID | Order ID of the customer's very first purchase |
| SMS Marketing Consent | Subscribed / Unsubscribed / Pending |
| Email Marketing Consent | Email marketing consent for campaign segmentation |
| Customer Email | Customer's registered email address (masked) |
| Customer Phone Number | Customer's registered phone number (masked) |
| Email Verified | Whether the customer's email address has been verified |
| Customer Last Updated | Date the customer record was last updated |
| Default Customer Address | Customer's saved default address |
| Default Customer City | City of the customer's default address |
| Default Customer State | State of the customer's default address |
| Default Customer Zip | Postal code of the customer's default address |
| Default Customer Country | Country of the customer's default address |
Customer Visit Type distinguishes a **first-time vs. returning customer** at order time. It's about visit history, not returned items — those flags live under [Return & exchange](#return--exchange).
### Time
| Dimension | Description |
| ------------------------- | ------------------------------------------------------- |
| Order Date | Exact date + time when the order was placed |
| Full Date | Order date, time component stripped |
| Full Timestamp | Order timestamp truncated to day |
| Year | Four-digit year |
| Calendar Year | Calendar year as string |
| Month Name | January, February, … |
| Week Start Date | Week label by start-of-week date |
| Day Name | Monday, Tuesday, … |
| Type of Day | Weekday / Weekend |
| Customer First Order Date | Date + time of customer's first-ever order (for tenure) |
| Cancellation Request Date | Date a cancellation was requested |
| Refunded At | Date and time the order was refunded |
### Financial & pricing
| Dimension | Description |
| --------------------------- | -------------------------------------------------------- |
| Item Quantity | Units of the product in the line item |
| Selling Price | List-price amount before discounts |
| Discount Amount | Discount applied to the line item |
| Discount Amount (Excl. Tax) | Discount before tax |
| Pre-Tax Discount | Discount before tax — for margin analysis |
| Tax Amount | Tax charged on the line item |
| CGST Rate / CGST Amount | Central GST rate and amount |
| SGST Rate / SGST Amount | State GST rate and amount |
| IGST Rate / IGST Amount | Integrated (interstate) GST rate and amount |
| UTGST Rate / UTGST Amount | Union Territory GST rate and amount |
| Total Tax Rate | Combined rate across all applicable components |
| Shipping Charges | Shipping fees charged to the customer |
| Packaging Amount | Packaging fees charged to the customer |
| Total Sales | Total the customer paid (items + tax + discounts + fees) |
| Currency | Transaction currency (INR, USD, etc.) |
| Discount Amount | Numeric discount value for bucketing |
| Discount Rate% | Discount as % of original sales amount |
| Discount Rate Bucket | Discount-rate grouped into percentage ranges |
| Unit Price Bucket | Unit price grouped by percentile distribution |
CGST, SGST, IGST, and UTGST are India-specific GST components. For brands outside India these are null; combined tax lives in Tax Amount and Total Tax Rate.
### Discount & promotion
| Dimension | Description |
| ------------------------- | --------------------------------------------------------------- |
| Discount Code | Coupon / promo code used |
| Discount Type | Type or category of discount |
| Discount Application Type | How the discount was applied — Automatic, Manual, or Code-based |
| Promotional Order Flag | Promo vs. Non-Promo flag at order level |
### Payment & gift card
| Dimension | Description |
| ------------------- | ------------------------------------------ |
| Paid via Gift Card | Boolean — gift card used for payment |
| Gift Card Amount | Amount paid via gift card |
| Loyalty Points Used | Loyalty points redeemed |
| Payment Status | Paid / Pending / Failed / Refunded |
| Payment Gateway | Payment processor (Razorpay, Stripe, etc.) |
### Status & fulfilment
| Dimension | Description |
| ------------------- | --------------------------------------------- |
| Fulfillment Status | Fulfilled / Unfulfilled / Partially Fulfilled |
| Shipment Status | Shipped / In Transit / Delivered / Failed |
| Order Fulfilled By | Self / Marketplace / 3PL |
| Cancellation Reason | Reason for cancellation |
| Location ID | Fulfilment location or warehouse |
| Sales Person ID | Sales person associated with the order |
| Shipping Partner ID | Shipping carrier or logistics partner |
### Address
| Dimension | Description |
| ----------------------------------------------------------------------------------- | --------------------------- |
| Billing Address / Billing City / Billing State / Billing Zip / Billing Country | Billing address components |
| Shipping Address / Shipping City / Shipping State / Shipping Zip / Shipping Country | Shipping address components |
### Return & exchange
| Dimension | Description |
| --------------------------- | ---------------------------------------------- |
| Is Returned | Boolean — line item was returned |
| Is Full Return | Boolean — all items in the order returned |
| Is Partial Return | Boolean — only some items returned |
| Is Exchanged | Boolean — order included an item exchange |
| Is Cancelled | Boolean — line item was cancelled |
| Returned Units per Order | Total units returned for the order |
| Refund Amount | Total refunded to the customer |
| Refund Amount (Excl. Tax) | Refund excluding tax components |
| Avg. Days to Approve Return | Pre-computed — days from request to approval |
| Avg. Days to Receive Return | Pre-computed — days from request to receipt |
| Avg. Days to Inspect Return | Pre-computed — days from request to inspection |
| Avg. Days to Refund Return | Pre-computed — days from request to refund |
### Holiday & occasion
| Dimension | Description |
| ---------------------------- | ----------------------------------------------------- |
| Occasion/Holiday Name | Holiday or special occasion (Diwali, Christmas, etc.) |
| Occasion Religion | Religious or cultural association |
| Is National Holiday | Boolean — national holiday |
| Is Shopping/Gifting Occasion | Boolean — gift-giving occasion |
| Occasion (Year) | Occasion name + year, combined |
### System & metadata
| Dimension | Description |
| ----------------------- | ---------------------------------------------- |
| Taxes Included in Price | Boolean — displayed prices include taxes |
| Order Last Updated | Last update timestamp for the order record |
| Gross Units per Order | Total units in the order across all line items |
| Total Refunds | Total refund for returns |
| Returned Units | Units returned on the line item |
| Cancelled Quantity | Units cancelled on the line item |
| Cancelled Amount | Cancelled-item value on the line item |
| Tax per Unit | Tax per unit of SKU |
| Promotional Units | Quantity of promotional units |
| Non-Promotional Units | Quantity of non-promotional units |
## Use it to answer
What's our actual Net Revenue after returns and cancellations?
How do they differ in order value and frequency?
Which products and categories have the worst return rates?
CM1 by product category or sales channel.
Incremental promo units vs. non-promo baseline.
What share of revenue comes from gift card redemption?
How long from return request to refund?
Days, hours, and holidays driving disproportionate sales by channel.
## Available metrics
Everything you can compute on this data model, grouped by category.
### Revenue
| Metric | Formula |
| --------------------------------- | ----------------------------------------------------------------------------------------------- |
| Gross Revenue | Final Amount Paid + Discounts − Shipping − Taxes (when tax-inclusive) |
| Total Sales | Sum of Final Amount Paid (everything customer paid) |
| Net Revenue | Final Amount Paid − Returns − Cancellations − Taxes (per-unit tax adjusted for partial returns) |
| Gross Revenue Without Tax | Gross Revenue − Total Tax |
| Total Sales Amount | Sum of Product List Price (before discounts) |
| Total Refund Amount Excluding Tax | Sum of refunds, net of tax components |
| Total Cancelled Amount | Sum of cancelled item value (pre-shipment) |
| Total Tax | Sum of CGST + SGST + IGST + UTGST collected |
| Total Refunds | Sum of all refunds (returns + cancellations + exchanges) |
**Three revenue numbers — pick the right one.** `Total Sales` is everything the customer paid (gross of nothing). `Gross Revenue` strips out shipping and taxes (when tax-inclusive). `Net Revenue` is the true bottom line — Gross minus Returns, Cancellations, and Taxes, with per-unit tax adjusted for partial returns. For period-over-period business reporting, use **Net Revenue**.
### Discount & promotion
| Metric | Formula |
| ----------------------------------- | ------------------------------------------------------------- |
| Total Discounts | Sum of discounts applied |
| Total Discount Amount Excluding Tax | Sum of discounts, pre-tax |
| Total Promo Units | Units sold with a promotional discount applied |
| Total Base Units | Units sold without any promotional discount |
| Promo Uplift | (Promo Units − Base Units) ÷ Base Units × 100 |
| Average Discount Rate | Discounts ÷ Sales Amount × 100, across discounted orders only |
| Shipping Charges | Sum of shipping charges collected |
### Orders
| Metric | Formula |
| ----------------------------- | --------------------------------------------------------------- |
| Gross Orders | Count of unique orders, all statuses |
| Gross Orders (No Promotion) | Orders without any promotional discount |
| Gross Orders (With Promotion) | Orders with at least one promotional discount |
| Cancelled Orders (Full) | Orders where all items cancelled pre-fulfilment |
| Returned Orders | Orders with at least one item returned |
| Orders Returned (Full) | Orders where all items returned |
| Orders Returned (Partial) | Orders where only some items returned |
| Exchanged Orders | Orders with at least one item exchange |
| Orders with Discount | Orders with any discount applied |
| Unfulfilled Orders | Orders not yet fulfilled or shipped |
| Unpaid Orders | Orders with payment incomplete or unconfirmed |
| Orders with Gift Card | Orders where gift cards were used at least partially |
| Net Orders | Gross Orders − Cancelled Orders (Full) − Orders Returned (Full) |
### Percentage & rate
| Metric | Formula |
| ------------------------- | ------------------------------------------------- |
| % Orders Returned | Returned Orders ÷ Gross Orders × 100 |
| % Orders Cancelled | Cancelled Orders (Full) ÷ Gross Orders × 100 |
| % Orders Exchanged | Exchanged Orders ÷ Gross Orders × 100 |
| % Orders with Discount | Orders with Discount ÷ Gross Orders × 100 |
| Order Fulfillment Rate | Fulfilled Orders ÷ Gross Orders × 100 |
| % Gift Card Orders | Orders with Gift Card ÷ Gross Orders × 100 |
| % Orders Returned (Full) | Orders Returned (Full) ÷ Returned Orders × 100 |
| Partial Order Return Rate | Orders Returned (Partial) ÷ Returned Orders × 100 |
| % New Customers | New Customers ÷ Total Customers × 100 |
| % Units Returned | Returned Units ÷ Gross Units Sold × 100 |
### Units & inventory
| Metric | Formula |
| -------------------- | ------------------------------------- |
| Gross Units Sold | Sum of items sold across all orders |
| Total Returned Units | Sum of items returned |
| Total Cancelled Qty | Sum of items cancelled pre-fulfilment |
| Net Units Sold | Gross Units − Returned Units |
| Units per Order | Gross Units ÷ Gross Orders |
### Customers
| Metric | Formula |
| ---------------- | ------------------------------------------------------- |
| Total Customers | Count of unique customers in the period |
| New Customers | Customers placing their first order in the period |
| Repeat Customers | Customers placing at least one order beyond their first |
### Cost & profitability
| Metric | Formula |
| ------------------ | ------------------------------------------ |
| Gross COGS | Sum of cost of goods for all items ordered |
| Return COGS | Sum of cost of goods for returned items |
| Net COGS | Gross COGS − Return COGS |
| Gross Profit (CM1) | Net Revenue − Net COGS |
### Return & exchange
| Metric | Formula |
| ------------------------------- | ----------------------------------------------- |
| Return/Exchange Shipping Amount | Sum of shipping costs for returns and exchanges |
| Average Days to Approve Return | Avg(approval date − request date) |
| Average Days to Receive Return | Avg(warehouse receipt − request date) |
| Average Days to Inspect Return | Avg(inspection complete − request date) |
| Average Days to Refund Return | Avg(refund issued − request date) |
| Average Days to Cancel Order | Avg(cancellation request − order date) |
### Average value & ratio
| Metric | Formula |
| ------------------------------------- | ---------------------------------------- |
| Gross AOV | Gross Revenue ÷ Gross Orders |
| Net AOV | Net Revenue ÷ Net Orders |
| Average Discount per Discounted Order | Total Discounts ÷ Orders with Discount |
| Net Revenue per Customer | Net Revenue ÷ Total Customers |
| Gross Revenue per Customer | Gross Revenue ÷ Total Customers |
| Customer Lifetime Value | Avg Order Value × Avg Purchase Frequency |
| Gross Revenue per Unit Sold | Gross Revenue ÷ Gross Units Sold |
**Customer Lifetime Value and Net Revenue per Customer are mathematically equivalent** in this data model — `AOV × Purchase Frequency` reduces to `Net Revenue ÷ Total Customers`. Both are exposed because teams reference them by different names; pick whichever matches your team's vocabulary.
### Behavioral & frequency
| Metric | Formula |
| ----------------------- | -------------------------------- |
| Purchase Frequency | Gross Orders ÷ Total Customers |
| Purchase Hour Frequency | Count of orders per hour of day |
| Purchase Week Frequency | Count of orders per week of year |