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.
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_idUnique identifier for each order order_item_idUnique identifier for each line item within an order sales_channel_nameChannel where the order was placed (Shopify, Amazon, etc.) sales_channel_idTechnical identifier for the sales channel brand_nameBrand associated with the order or channel
Product Dimension Description product_nameProduct title product_skuMaster SKU, consistent across channels channel_skuSKU as listed on the specific sales channel channel_product_idProduct identifier on the sales-channel platform product_categoryHigh-level product category product_subcategorySubcategory within the category product_sizeSize attribute (S, M, L, XL, etc.)
Customer Dimension Description customer_idUnique customer identifier first_name / last_nameCustomer name fields customer_is_memberBoolean — loyalty/membership program status customer_visit_typeNew vs. returning customer for this order customer_first_order_idOrder ID of the customer’s very first purchase sms_marketing_consent_stateSubscribed / Unsubscribed / Pending email_marketing_consent_stateEmail marketing consent for campaign segmentation
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_timestampExact date + time when the order was placed full_dateOrder date, time component stripped full_timestampOrder timestamp truncated to day yearFour-digit year calendar_yearCalendar year as string month_nameJanuary, February, … week_start_dateWeek label by start-of-week date day_nameMonday, Tuesday, … type_of_dayWeekday / Weekend customer_first_order_timestampDate + time of customer’s first-ever order (for tenure) cancel_request_dateDate a cancellation was requested
Financial & pricing Dimension Description item_quantityUnits of the product in the line item sales_amountList-price amount before discounts discount_amountDiscount applied to the line item discount_amount_excluding_taxDiscount before tax pre_tax_discountDiscount before tax — for margin analysis tax_amountTax charged on the line item cgst_tax_rate / cgst_tax_amountCentral GST rate and amount sgst_tax_rate / sgst_tax_amountState GST rate and amount igst_tax_rate / igst_tax_amountIntegrated (interstate) GST rate and amount utgst_tax_rate / utgst_tax_amountUnion Territory GST rate and amount total_tax_rateCombined rate across all applicable components shipping_chargesShipping fees charged to the customer packaging_amountPackaging fees charged to the customer final_amount_paidTotal the customer paid (items + tax + discounts + fees) currency_unitTransaction currency (INR, USD, etc.) discount_amount_valueNumeric discount value for bucketing discount_rate_percentage_valueDiscount as % of original sales amount discount_rate_bucketDiscount-rate grouped into percentage ranges unit_price_bucketUnit 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.
Dimension Description discount_codeCoupon / promo code used discount_typeType or category of discount discount_application_typeHow the discount was applied — Automatic, Manual, or Code-based promotional_order_flagPromo vs. Non-Promo flag at order level
Payment & gift card Dimension Description is_gift_card_paymentBoolean — gift card used for payment gift_card_amountAmount paid via gift card loyalty_points_usedLoyalty points redeemed payment_statusPaid / Pending / Failed / Refunded payment_gatewayPayment processor (Razorpay, Stripe, etc.)
Status & fulfilment Dimension Description order_item_fulfillment_statusFulfilled / Unfulfilled / Partially Fulfilled order_item_shipment_statusShipped / In Transit / Delivered / Failed fulfilled_bySelf / Marketplace / 3PL cancel_reasonReason for cancellation location_idFulfilment location or warehouse sales_person_idSales person associated with the order shipping_partner_idShipping carrier or logistics partner
Address Dimension Description billing_address / billing_city / billing_state / billing_zip / billing_countryBilling address components shipping_address / shipping_city / shipping_state / shipping_zip / shipping_countryShipping address components
Return & exchange Dimension Description is_returnedBoolean — line item was returned is_full_returnBoolean — all items in the order returned is_partial_returnBoolean — only some items returned is_exchangedBoolean — order included an item exchange is_cancelledBoolean — line item was cancelled returned_units_per_order_idTotal units returned for the order total_refund_amountTotal refunded to the customer refund_amount_excluding_taxRefund excluding tax components average_days_to_approve_returnPre-computed — days from request to approval average_days_to_receive_returnPre-computed — days from request to receipt average_days_to_inspect_returnPre-computed — days from request to inspection average_days_to_refund_returnPre-computed — days from request to refund
Holiday & occasion Dimension Description occasion_nameHoliday or special occasion (Diwali, Christmas, etc.) occasion_religionReligious or cultural association is_national_holidayBoolean — national holiday is_shopping_gifting_occasionBoolean — gift-giving occasion occasion_name_yearOccasion name + year, combined
Dimension Description taxes_included_flagBoolean — displayed prices include taxes updated_atLast update timestamp for the order record gross_units_sold_per_order_idTotal units in the order across all line items total_return_refund_amountTotal refund for returns returned_unitsUnits returned on the line item cancelled_qtyUnits cancelled on the line item cancelled_amountCancelled-item value on the line item tax_per_unit_skuTax per unit of SKU promo_units_valueQuantity of promotional units base_units_valueQuantity 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 .
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
