---
title: "E-commerce data model: a free, editable template"
canonical: "https://www.owox.com/blog/articles/ecommerce-data-model"
updated: "2026-07-09"
---

# E-commerce data model: a free template to open and edit

[Data Modeling](/blog/topics/data-modeling) · Updated July 9, 2026 · [Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · 5 min read

![E-commerce data model: a free template to open and edit](https://cdn.owox.ai/www/webflow/6a4feb15816fd7df2e186e0e_E-commerce-data-model.png/public)

An **e-commerce data model** is the set of tables – customers, products, orders, and the events around them – plus the keys that join them into something you can actually report from. Get it right and questions like _“what’s our true margin by category?”_ take one query. Get it wrong and every report becomes an argument about which number is correct.

This page gives you a **free, ready-made e-commerce data model** you can open in your browser, edit like a diagram, and export to **OKF** – Google’s open, portable format. No sign-up, no install. It’s one of nine in our [data model template gallery](https://www.owox.com/blog/articles/data-model-templates); this one is built for online retail.

![e-commerce data model template](https://cdn.owox.ai/www/webflow/6a4fe91c889dc2d7120e3e8e_50342d59.png/public)

## What an e-commerce data model is, and the two kinds

There are really two models hiding behind the phrase “e-commerce data model,” and most search results only show you one.

The first is the **transactional (OLTP) schema** that runs the store: highly normalized tables for products, variants, carts, checkouts, and payments, tuned for fast writes. The second is the **analytics data model**: a denormalized, **dimensional** shape – facts surrounded by dimensions – tuned for fast questions about revenue, retention, and behavior. They aren’t competitors; the analytics model is downstream of the transactional one.

This template is the **analytics model** – the one you build reports on. It follows a **Kimball-style star**: a few central fact tables (the things that happen) joined to dimension tables (the things they happen to). If the vocabulary is new, our [guide to data modeling](https://www.owox.com/blog/articles/what-is-data-modeling) and the breakdown of [data model types](https://www.owox.com/blog/articles/types-of-data-models-and-benefits) are good companions.

## The e-commerce template

The template is six **data marts** – two dimensions and four facts – wired into a sales star. Here’s the whole thing.

![Entity relationship diagram of an e-commerce data model: Customers and Products dimensions joined to Orders, Order Items, Web Sessions, and Returns fact tables in a Kimball-style sales star.](https://cdn.owox.ai/www/webflow/6a4fdb0d40b9256036426177_f287d388.png/public)

• **Customer** (dimension) – one row per buyer, with acquisition channel, first-order date, and region.

• **Product** (dimension) – one row per SKU, carrying category, brand, variant, and cost. This is where SKU, variant, and category attributes live.

• **Orders** (fact) – one row per order: totals, status, channel, and the **Customer** key.

• **Order Items** (fact) – one row per order line (order × SKU). This is where **true line margin** lives, because price, discount, and cost are per item, not per order.

• **Web Sessions** (fact) – one row per session, joined to **Customer**, for the behavioral funnel that orders alone can’t show.

• **Returns** (fact) – one row per returned line, joined back to **Order Items** and **Product**.

The joins make it a star: **Orders → Customer**, **Order Items → Orders** and **Order Items → Product**, **Web Sessions → Customer**, and **Returns → Order Items** and **Returns → Product**. Every table here is a reporting-ready **data mart** in the sense we describe in our [approach to data marts](https://www.owox.com/blog/articles/business-reporting-with-data-marts).

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](https://www.owox.com/demo)

We'll use your actual use case

## The grain that makes it work

The single most important decision in this model is **grain** – what one row represents – and it’s why there are two order tables instead of one.

**Orders** sit at order grain (one row per order), which is right for order counts, shipping, and status. **Order Items** sit at order-line grain (one row per order × SKU), which is the only place you can compute **margin, discount, and units per product** correctly. Collapse them into one table and you either double-count order totals or lose per-product economics. Keeping both, joined by the order key, is the classic move – the same fact-table thinking covered in [the three types of fact tables](https://www.owox.com/blog/articles/types-of-fact-tables) and [understanding star schema](https://www.owox.com/blog/star-schema-explained). For the broader pattern, see [dimensional data modeling](https://www.owox.com/blog/articles/dimensional-data-modeling).

## What this model answers

Because the grain and joins are right, the hard questions become straightforward:

• **True line margin by category** – from Order Items (price − discount − cost), rolled up through Product.

• **Repeat-buyer rate by acquisition channel** – from Orders joined to Customer.

• **Return rate by product** – from Returns joined to Order Items and Product.

• **New vs. returning revenue** – from order sequence per Customer.

• **Browse-to-buy conversion** – from Web Sessions joined to Customer and Orders.

None of these need a new table. They’re all just different paths across the same six data marts.

## Analytics model vs. transactional schema

A common question when people first see this template is _“where are the cart, checkout, and payment tables?”_ The short answer: those are **transactional tables**, and the analytics model starts one step downstream, at the order.

Carts, checkout sessions, and payment attempts belong to the OLTP schema that runs the storefront. For analytics you usually land the **completed order** and its lines, because that’s the grain reporting cares about. (And to clear up a frequent mix-up: _schema_ in the SEO sense – product schema markup – is structured data for search engines, not a data model. Different thing entirely).

| Aspect | Transactional (OLTP) schema | Analytics data model (this template) |
| --- | --- | --- |
| Shape | Normalized (carts, checkouts, payments) | Dimensional star (facts + dimensions) |
| Tuned for | Fast writes, app correctness | Fast questions, reporting |
| Starts at | Cart / checkout | Completed order + lines |
| Export | SQL DDL | OKF + diagram image |

## How to open and customize the template

Opening the template and shaping it to your store takes about **two minutes**, then as long as you want to refine.

(1) **Open it.** Use the link under the diagram above. It loads in your browser with no sign-up.

(2) **Reshape it.** Rename tables, add fields (loyalty tier, fulfillment center, marketing cost), and redraw joins on the canvas.

(3) **Set grain and keys.** Confirm what one row means on each fact, and the keys that join them.

(4) **Export it.** Use **Export → OKF** for a portable model, or grab a diagram image. Keep the OKF in git or [push it into OWOX Data Marts](https://www.owox.com/app-signup) to make it live in your warehouse.

If you’re weighing this against other diagramming apps, our roundup of [free database diagram design tools](https://www.owox.com/blog/articles/database-diagram-design-tools) puts it in context.

## Export to OKF: a portable e-commerce model

The reason this beats a static ER picture is what happens after the diagram. A drawing can’t be diffed or fed to a warehouse.

This template exports to **OKF (Open Knowledge Format)**, Google’s open, markdown-based standard. Because it’s plain text, you can **keep the model in git, review it in a pull request, and hand it off without lock-in**. New to it? See our explainer on [what OKF is](https://www.owox.com/blog/articles/open-knowledge-format-okf), then [open the e-commerce model](https://model.owox.com/?template=ecommerce&utm_source=owox-blog&utm_medium=article&utm_campaign=ecommerce-data-model&utm_content=okf-section) and export your own.

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## FAQ

## Frequently Asked Questions

Is the e-commerce data model template free, and what can I export?

Yes, it is free with no sign-up. You can export the model as OKF or a diagram image. An OWOX account is only needed if you push the model into OWOX Data Marts.

What is schema in e-commerce?

It depends on context. In data modeling, a schema is the structure of your tables and relationships. In SEO, ecommerce schema means product schema markup, which is structured data for search engines and is unrelated to a data model.

How do I model returns and refunds in an e-commerce data model?

As a Returns fact at returned-line grain, joined back to Order Items and Product. That lets you measure return rate by product and net revenue after returns.

What is the grain of the orders vs order items table?

Orders is one row per order; Order Items is one row per order line (order × SKU). Line margin, discount, and units are computed at the Order Items grain, which is why the two tables are kept separate.

Where are the cart, checkout, and payment tables in an e-commerce data model?

Those live in the transactional schema. The analytics model starts at the completed order, which is the grain reporting needs, so cart and payment detail are intentionally upstream.

Is an e-commerce data model a transactional schema or an analytics model?

This template is an analytics model: a dimensional star you report from. The transactional (OLTP) schema that runs the storefront is a separate, normalized model upstream of it.

What tables are in an e-commerce data model?

At minimum: a Customer dimension, a Product dimension (carrying SKU, variant, and category), an Orders fact at order grain, and an Order Items fact at order-line grain. The OWOX e-commerce template adds Web Sessions and Returns facts for behavior and reverse logistics.

## Who wrote this

![Ievgen Krasovytskyi](https://cdn.owox.ai/www/webflow/68404586b341508a789a4aa5_.png/public)

[Ievgen Krasovytskyi](/team/ievgen-krasovytskyi) · Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.

[LinkedIn](https://www.linkedin.com/in/ievgen-krasovytskyi-a38a1253/) · [All articles](/team/ievgen-krasovytskyi)

[Data Modeling](/blog/topics/data-modeling)

## Related articles

[Data Modeling · Key Benefits of Data Modeling for Reporting (2025) · March 3, 2025](/blog/articles/benefits-of-data-modeling)

[Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](/blog/articles/best-data-modeling-tools)

[Data Modeling · Best Free ERD Tools in 2026 · July 10, 2026](/blog/articles/best-free-erd-tools)

[See all articles →](/blog/articles)

## References

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [Data Modeling](https://www.owox.com/blog/topics/data-modeling) — /blog/topics/data-modeling.md
- [Ievgen Krasovytskyi](https://www.owox.com/team/ievgen-krasovytskyi) — /team/ievgen-krasovytskyi.md
- [data model template gallery](https://www.owox.com/blog/articles/data-model-templates) — /blog/articles/data-model-templates.md
- [guide to data modeling](https://www.owox.com/blog/articles/what-is-data-modeling) — /blog/articles/what-is-data-modeling.md
- [data model types](https://www.owox.com/blog/articles/types-of-data-models-and-benefits) — /blog/articles/types-of-data-models-and-benefits.md
- [approach to data marts](https://www.owox.com/blog/articles/business-reporting-with-data-marts) — /blog/articles/business-reporting-with-data-marts.md
- [Book a Demo](https://www.owox.com/demo) — /demo.md
- [the three types of fact tables](https://www.owox.com/blog/articles/types-of-fact-tables) — /blog/articles/types-of-fact-tables.md
- [understanding star schema](https://www.owox.com/blog/star-schema-explained)
- [dimensional data modeling](https://www.owox.com/blog/articles/dimensional-data-modeling) — /blog/articles/dimensional-data-modeling.md
- [push it into OWOX Data Marts](https://www.owox.com/app-signup)
- [free database diagram design tools](https://www.owox.com/blog/articles/database-diagram-design-tools) — /blog/articles/database-diagram-design-tools.md
- [what OKF is](https://www.owox.com/blog/articles/open-knowledge-format-okf) — /blog/articles/open-knowledge-format-okf.md
- [Data Modeling · Key Benefits of Data Modeling for Reporting (2025) · March 3, 2025](https://www.owox.com/blog/articles/benefits-of-data-modeling) — /blog/articles/benefits-of-data-modeling.md
- [Data Modeling · Best Data Modeling Tools in 2026 · July 21, 2026](https://www.owox.com/blog/articles/best-data-modeling-tools) — /blog/articles/best-data-modeling-tools.md
- [Data Modeling · Best Free ERD Tools in 2026 · July 10, 2026](https://www.owox.com/blog/articles/best-free-erd-tools) — /blog/articles/best-free-erd-tools.md
- [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md