Sales & Order Data using Daasity

This article covers our 10/30/20 code update related to Order Line Revenue and Shopify Sales Report (now Transactional ) Explores.

The Order Line Revenue and Shopify Sales Report Explores have some major changes as a result of a large code update. 

There are many ways to evaluate and look at data. Daasity has always taken a customer-first approach to our data model. However, Shopify, which most merchants rely upon and are used to, has a modified financial first approach to how they report sales.

In order to provide merchants with all the reporting options they need, we've modified several areas of our data models to make either way of reporting easy.

Transactional Sales Report (Formally Shopify Sales Report)

The previous version of the Shopify Sales Report enabled financial reporting on sales, returns, discounts, taxes & shipping by day or product. The NEW Transactional sales report has additional information joined to it, which allows analysis similar to the Order Line Revenue explore. 

It also will contain all of your sales sources, not just Shopify. 

There are some differences in how Shopify's logic is applied to various elements in an order, including shipping fees, taxes and discounts. However, the largest delta that is typically noticed is the treatment of refunded values. 

Shopify records the refund the way your finance team would want to record it - on the day the refund occurred. Transactions containing refunds are treated as simply another transaction. If a customer makes a purchase, that transaction is recorded with a transaction number. If that customer returns the items, the return is simply another transaction and not connected to the original order.

For all reporting using this logic, use the new Transactional Sales Report explore.

Order Line Revenue

Order line revenue has been tweaked so that the individual revenue components match the logic used to calculate the revenue components in the Shopify sales report EXCEPT for refunds.  What this means is that individual revenue components like product sales, shipping revenue, and the discount amount will match Shopify, but Net Sales figures will not.

The Order Line Revenue explore is based on a customer-first data model which ties the refund back to the order in which the item was purchased. This is a typical methodology for customer level reporting, cohort reports and Lifetime value reporting. This also allows the item to be mapped back to exactly which items were originally purchased. 

What does that mean? It means that an order contains 1 row in the database. The order has various attributes such as sales, items, taxes and refunds, all connected to that original order. 

Why Have Two Explores and What Should Each Be Used For?

The reason for two explores or data models is to enable the reporting that is necessary for all direct-to-consumer businesses. 

This Order Line Revenue explore has one row per SKU in an order. The Transactional Sales Report has one line per transaction.  That means if a product is refunded it appears in the database as a separate transaction. This makes it more difficult to tease out what actually happened with an order.  If you are researching one order it is best to use order_line_revenue.

Valid Orders

The new order line revenue explore no longer uses the concept of a “valid” order.  Shopify considers a valid order to be anything that is not deleted or a test order, and the new order line revenue explore matches this definition.  However, we are still including a “Daasity valid order flag” in the ETL that removes any orders that are cancelled, voided, fraud, and free orders.  This logic is the same as what is currently used in the valid order flag.  So if you look at unfiltered sales data from order line revenue, that data will include cancelled, fraud, voided, and free orders.  But the daasity valid order flag removes those orders.

Copyright 2021 by Daasity, Inc.