Sales - Ocular

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

Grain

Order × line item × channel

Source

Shopify, Amazon, etc.

Metrics

60+ across 10 categories

Dimensions

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.

Show sample metrics in each category

Dimensions, grouped by category — every way you can split, filter, or group those metrics. Expand below for examples in each.

Show sample dimensions in each category

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.

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

True Net Revenue

What’s our actual Net Revenue after returns and cancellations?

New vs. Repeat customers

How do they differ in order value and frequency?

High-return products

Which products and categories have the worst return rates?

Contribution Margin 1

CM1 by product category or sales channel.

Promotion effectiveness

Incremental promo units vs. non-promo baseline.

Gift card share

What share of revenue comes from gift card redemption?

Returns pipeline timing

How long from return request to refund?

Sales seasonality

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

​At a glance

Grain

Source

Metrics

Dimensions

​What this data model represents

​Slice by

​Order & channel

​Product

​Customer

​Time

​Financial & pricing

​Discount & promotion

​Payment & gift card

​Status & fulfilment

​Address

​Return & exchange

​Holiday & occasion

​System & metadata

​Use it to answer

True Net Revenue

New vs. Repeat customers

High-return products

Contribution Margin 1

Promotion effectiveness

Gift card share

Returns pipeline timing

Sales seasonality

​Available metrics

​Revenue

​Discount & promotion

​Orders

​Percentage & rate

​Units & inventory

​Customers

​Cost & profitability

​Return & exchange

​Average value & ratio

​Behavioral & frequency

At a glance

What this data model represents

Slice by

Order & channel

Product

Customer

Time

Financial & pricing

Discount & promotion

Payment & gift card

Status & fulfilment

Address

Return & exchange

Holiday & occasion

System & metadata

Use it to answer

Available metrics

Revenue

Discount & promotion

Orders

Percentage & rate

Units & inventory

Customers

Cost & profitability

Return & exchange

Average value & ratio

Behavioral & frequency