If you’re a DTC founder selling goods through an e-commerce store, there’s a pretty good chance you’re using a platform like Shopify, BigCommerce, or Woocommerce, and one of the dozens of analytics extensions like RetentionX, Sensai metrics, or Profitwell that provide off-the-shelf reporting.

At a high level, these tools are excellent for helping you understand “what” is happening in your business, but you’ll inevitably find yourself asking deeper questions that your off-the-shelf extensions can’t answer. You might also be frustrated to learn that the revenue reported by these off-the-shelf dashboards is inconsistent with the revenue that your finance team reports.

When you find that these problems are impeding your ability to make important decisions, it’s time to start thinking about building a data warehouse.

We created this guide for founding teams in M13’s portfolio companies who need a data warehouse but aren’t seasoned data pros. We’ll walk you through the fundamentals of modeling transactional sales data.

In this guide, we’ll cover:

1. Understanding analytical databases
2. Defining your enterprise metrics
3. Asserting your metrics
4. Navigating corner cases and ensuring accuracy
5. Asserting your enterprise dimensions
6. Takeaways & next steps

Here’s what else you’ll need to get started:

• A database like Snowflake, BigQuery, or DigitalOcean receiving raw data from your e-commerce platform
• A SQL developer

This guide also includes sections that may be valuable to the SQL developer building the database, including:

A Retail Modeling Checklist: Questions to ask yourself and your team before you start modeling your data

• Retail Modeling Checklist

SQL templates: Standardized SQL templates that your SQL developer can apply to your own raw data for a head start

• Order Line Detail Template
• Order Line Detail Tests Template

If you’re a founder at an M13 portfolio company who needs references for a contract or full-time SQL developer, please reach out to us on Slack or at propulsion@m13.co.

In this section, we’ll focus on the analytical (e.g. reporting) database. An analytical database is distinct from the production database. Production databases are used to house data that’s used for customer-facing applications, like your website. An analytical database usually includes a replica of your production databases.

Layering your database

A well-designed database should have distinct layers that separate certain tasks. A database layer is a conceptual grouping of queries (i.e. tasks) and tables (i.e. outputs) that correspond to a certain step in the process. The exact number of layers will depend on your exact situation and the complexity of your warehouse. At a minimum, we recommend that all analytical databases have three layers, so for the purposes of this section, we’ll refer to them as the:

• Raw layer
• Transformation layer
• Consumption layer

The first layer is made up of the unadulterated data coming through your integrations. It can go by a few names: “staging,” “landing,” or “raw.” If you’re using a data movement provider like Fivetran or Segment, you can usually count on them landing de-duplicated event data in a raw schema—that’s your raw layer.

The transformation layer is sandwiched between the raw layer and the consumption layer. The transformation layer is a collection of SQL queries (i.e. SQL code) and tables that organize the raw data into vetted business metrics and dimensions. For example, your raw layer will have a table for Order Lines sold and a separate table for Returns or Refunds. Your transformation layer is where you’d combine them to create Net Revenue.

The last layer is your consumption layer, sometimes called the “visualization” or “business intelligence (BI)” layer. The consumption layer is a collection of queries and tables that read from the transformation layer tables and aggregate it into specific curated tables. The tables in the consumption layer are generally more purpose-built and less flexible than the tables in the transformation layer.

Here’s an example to drive these concepts home:

Raw layer tables

Order Line

Retail price

Quantity

Sold date

Order Line Refund

Refunded quantity

Refunded date

‍

Analytical layer tables

Order Line Detail

Gross price (after discount)

Gross quantity (after removing cancellations, tests, fraud)

Gross revenue

Refunded revenue

Net quantity (after returns)

Net revenue

Customer detail (acquisition month, lifetime net revenue)

‍

Consumption layer tables

LTV monthly cohort repor

Calendar month

Acquisition month

Customers in cohort

Average net revenue LTV

You can also think of your business intelligence tool (e.g. Looker, Tableau) as another layer beyond the consumption layer in your database.

Your data should flow downstream, and be enriched at each stage. The queries (i.e. code) in your transformation layer should read from your raw layer and write the output tables to your transformation layer. Your consumption layer should query from the transformation layer and write to the consumption layer.

Now that we’ve roughly sketched out the conceptual layers in your database, let’s zoom in and get more specific about what should be contained in each layer.

We’ll define the exact tables later, but we find it’s generally easier to start thinking about the metrics you want to see before you think about how you need to slice that metric.

Any conversation about business metrics for an e-commerce company has to start with sales metrics. You might be thinking: “Revenue is revenue” But there are nuances that you’ll need to understand before you start building your data assets. At M13, we use a checklist to call attention to some common—and some obscure—edge cases that can have significant impacts on your most fundamental metrics and KPIs.

Retail Modeling Checklist

Many companies get into the bad habit of reporting sales metrics (e.g. Revenue, Units Sold, AOV, LTV, etc) without explicitly specifying whether the numbers are Gross or Net. In many cases, they haven’t even explicitly defined Gross and Net.If you think about it, each of these metrics has several variants: Raw, Retail, Gross, Net.If you export an order report from Shopify and sum your “subtotals,” you might think you just calculated Revenue, but there are a few problems with this approach. For starters:

• You might be counting revenue from test orders and fraudulent orders
• You’re taking credit for units that were canceled by your warehouse and never fulfilled
• Subtotals often refer to pre-discounted item prices

And the list goes on…We like to start by defining metric “prefixes” and applying those rules to create several different versions of each sales metric. Here’s a concrete example:

Raw: Every unit in shopify.order_line

Retail: Units that represent real demand

• Excludes test orders
• Excludes fraudulent orders
• Excludes 100% discounted orders (employee/influencer/press freebies; does not exclude BOGO, or free gifts with purchase)
• Excludes orders canceled by customer
• Revenue is the total price before discount. Includes international surcharge. Does not include taxes.

Gross: Retail units minus point of sale discounts

• Excludes gift cards payments (gift card sales only count toward revenue when they’re distributed, not when they’re redeemed)
• Excludes units canceled due to stock shortage or 3PL delay
• Revenue excludes product discounts (shipping discounts are not deducted at this step)

Net: Gross units minus refunded units

• Excludes refunded units (regardless of whether they were returned or not)
• Includes shipping revenue (shipping price minus shipping discounts)
• Includes retroactive order adjustments
  

It’s endlessly frustrating to have multiple slightly different—and poorly labeled—versions of the “same” metric floating around your organization. Make sure your management team understands each of these metrics well enough to describe the differences to their own teams.

You don’t want to end up in a situation where your finance team’s dashboards and your e-commerce team’s dashboards don’t have the same week-over-week revenue numbers and they don’t know why. These misunderstandings cause friction that makes it difficult for teams to collaborate effectively.

There’s no one-size-fits-all answer to the questions on this list. If you’re an M13 portfolio founder who is unsure of the consequences of these decisions, reach out to us at propulsion@m13.co—we’re always happy to discuss.

Now that you have metrics defined in English, you’re ready to “assert” them—this means writing the SQL queries that create the metrics as columns and then storing them in a table in your database.

• Assert your metrics as far upstream as possible
• Assert your metrics at the finest grain possible

‍

These aren’t hard and fast rules, but they’re good practices to keep your code DRY. DRY stands for Don’t Repeat Yourself. The concept of DRY isn’t about being lazy—it’s about being consistent. If you create a metric in five different places in your data warehouse, then you’ll have five different queries that you’ll need to fix whenever the definition is updated. It’s better to assert it once—as far upstream as possible and at the finest grain—and reference it downstream so that any changes to the original assertion propagate through the rest of your data stack.

In the case of a retail business, there’s one obvious place to start: the Order Line. Each order line represents a unique product in a unique order. Order lines have a retail price, a discount amount, a quantity, and a product id—pretty much everything you need to know to calculate gross revenue, gross quantity, and contribution margin.

“Order Lines” are Shopify nomenclature that we’re adopting in this guide and the supporting templates, but the concepts apply to any e-commerce schema. For example, these are referred to as “Line Items” in Spree.

At M13, we’ve developed the following templates to help our founding teams get started. At M13, we’ve developed the following templates to help our founding teams get started. You can use the Order Line Detail Template for a head start. This template shows how we’ve helped other companies:

• Filter out non-retail orders
• Assert enterprise metrics at the order line grain

This generalized template won’t capture the intricacies of your business, so you’ll need to modify it to accommodate your specific metric definitions, your e-commerce platform, and your implementation.In the next section, we’ll provide a framework and a few tips for doing so.

We provided SQL templates for all founders in M13’s community because they’re a helpful starting point, but there are a few specific lines in the templates that you’ll need to modify to suit your e-commerce implementation and CX flows. For example:

• Return/exchange logic (valid_orders_template.sql, line 112-139)
• Invalid orders (valid_orders_template.sql, line 143-153)

For this phase of the project to go smoothly, the SQL developer asserting metrics in the data warehouse must be in close communication with a stakeholder that has in-depth knowledge of the e-commerce platform’s configuration (i.e. the person who knows how order tags and notes are auto-generated, how returns are tracked in the system, what happens when a customer requests an exchange, etc.) Typically, this can be someone on the e-commerce team or a senior member of the Customer Service/Experience team.

This Retail Modeling Checklist highlights questions that the stakeholder should be able to answer, including “How do common retail practices translate into the raw data?” The answers to these questions will help the SQL developer find the tables and column names that store the data needed to calculate the metrics.

Retail Modeling Checklist

Most e-commerce companies have multiple relatively common corner cases that require manual CX intervention in the e-commerce UI to correct an atypical transaction. Take, for example, an exchange/replacement due to the incorrect item being shipped. These atypical corner cases usually take a disproportionate amount of time to resolve—they’re on the far end of the Pareto distribution where you can easily spend half your time resolving the last 1% of discrepancies.

‍

This may beg the question: “How do you know how many discrepancies you have left?” The answer isn’t obvious. Not every company has a vetted source of truth at the Order level that the SQL developer can QA against. In some cases, you may think you have a source of truth, until you realize that it isn’t 100% consistent with the metric definition your stakeholders defined for every corner case.

We recommend writing SQL tests that compare the total amount you charged the customers to the “Net Revenue” for each order. For an example of this, see rows 70-90 in Order Line Detail Tests Template. This test will fail if there’s a difference between Net Revenue, as you’ve calculated it, and the total amount you billed the customer.

When the test fails, you should investigate the records that caused the test to fail to determine if you need to change your Net Revenue definition. If you determine that your net revenue definition is correct, but it doesn’t align with the amount that you charged the customer, then you should edit your SQL logic in rows 70-90 so that it only returns records where the Net Revenue is incorrect. Tests should always be written so that any resulting record is indicative of an issue. When your test SQL doesn’t return any records, your code is clear of the errors that you’ve tested for.

To summarize, you should confirm your enterprise metrics are being defined correctly by:

• Asserting your metric in the transformation layer (i.e. order_line_detail)
• Writing a test that fails (i.e. returns a result) whenever a value in your transformation layer doesn’t match a trusted source of truth
• Investigating the records that cause the test to fail, and updating the assertion or test query accordingly
• Iterating until each metric matches the trusted source of truth

In the next section, we’ll cover the process for identifying and defining metrics.

We’ve spent a lot of time covering metric definitions, assertion, and testing, but we haven’t covered dimensions at all. Similar to metrics, you’ll want to define your dimensions as far upstream as possible. Here are a few examples of enterprise dimensions that will cut across the entire company:

• Pay As You Go (PAYG) orders vs. subscription orders
• New customer orders vs. repeat customer orders
• Paid marketing last touch vs. organic last touch
• DTC vs. wholesale vs. Amazon sales channels

You should be just as thoughtful—and communicative—about your enterprise dimension definitions as you are about your enterprise metric definitions. The last thing you want is several different versions of the same dimension asserted in different layers of your data warehouse. These are anti-patterns that will cause avoidable headaches.

‍

Style conventions in the attached templatesWe decided to make Order Line Detail table wide, including many dimensions (is_net, is_subscription, is_payg, etc.) and many intermediary metrics (e.g. shipping revenue, shipping_revenue_per_unit, discounts_per_unit, etc). We made this decision because columnar databases can efficiently run aggregations on tables with a large number of extraneous columns, so they’re cheap to include and they provide flexibility and convenience downstream.

We deliberately chose to materialize flags (e.g. is_gross, is_gross_new, and is_gross_repeat) as binary integer values instead of booleans so that we could multiply other metrics to conveniently create subset metrics. For example:

Input:

SELECT

SUM(is_gross_new = gross_revenue) / SUM(is_gross_new = gross_quantity) AS gross_new_aur FROM order_line_detail

Instead of:

SELECT

SUM(CASE WHEN is_gross_new = 'true' THEN gross_revenue ELSE 0 END) / SUM(CASE WHEN is_gross_new = 'true' THEN gross_revenue ELSE 0 END) AS gross_new_aur_alt1, SUM(is_gross_new::INTEGER = gross_revenue) / SUM(is_gross_new::INTEGER = gross_quantity) AS gross_new_aur_alt2 FROM order_line_detail

Materializing local dates alongside UTCsMany SQL developers are uncomfortable with the idea of having any dates in their warehouse that aren’t in UTC, but we recognize the convenience of having local dates in the transformation layer. One major risk of doing this is that it becomes easy to lose track of which dates are in UTC and which are in local time zones. We use a convention that all dates shifted to local time in the accompanying SQL files are labeled with a “local_” prefix.

We urge you to articulate an intentional date labeling philosophy to avoid confusion. Your raw data will almost always be in UTC, and we recommend leaving your data in UTC if you plan to re-use the original raw field name in your analytics layer. However, we recommend that any local dates are clearly labeled with a “local” or “report” prefix so as never to be confused with UTC dates.

M13 portfolio companies have used these templates and best practices to save time and avoid costly missteps.

However, there are some important topics that we glossed over, or didn’t cover at all. Here are just a few other topics you should be thinking about when you begin building your first analytical data warehouse:

• Data movement
• Materialization and refresh strategy
• Non-transactional data domains (marketing, inventory, clickstream, customer, etc.)
• Test automation
• Choice of business intelligence tool

If you’re part of a founding team in M13’s portfolio that would like to learn more about these topics, please reach out to propulsion@m13.co.