Sales table

The sales table contains the actual details about an order or refund at the time that these events occur. This is why you should use the sales table when you need to make financial calculations.

The sales table does not contain data about the actual exchange of money. It records the value of the products sold and returned. A sale of a $10 t-shirt is recorded as $10 regardless of whether it’s fully paid, partially paid, or not paid at all.

Similarly, when an item is returned, it appears in this table regardless of whether the customer actually receives the refund payment or even the returned item. For example, if a customer returns a $20 t-shirt and the merchant charges them a $2 restocking fee, then the amount returned is recorded as $20. To learn more, see How returns are recorded.

The happened_at column contains the date and time when an order is created. In most cases, an order is created when the customer clicks Complete order in the checkout. For draft orders, an order is created when you mark the draft order as paid or as payment pending in your Shopify admin. For orders created using the Admin API, the order is created at the time set by the application.

When you cancel an order in the Shopify admin, it is recorded as a return in the sales table. So from the point-of-view of the sales table, there’s no such thing as a cancellation, there's only a return.

Test orders and deleted orders are included in this table. To avoid including these items in your results, use the is_deleted and test columns.

The way that you process a return in your Shopify admin affects the data that appears in the sales table.

The sales table can contain the following rows:

1. One row per order line item. The line_type for this row is product or gift card.
2. One row per refund line item. The line_type for this row is return.
3. One row per shipping line. The line_type for this row is shipping.
4. One row per return adjustment. The line_type for this row is unknown.

Returns are recorded in the sales table as separate rows. Usually there is one row in the sales table for each product returned, and the line_type for this row is return (but this is not always the case). The rows that appear in the sales table for returns depend upon how the merchant processes the refund. If you don't specify the products that are returned, then this return can result in an adjustment row.

Whenever you have a row in the sales table that has a line_type of unknown, you will likely have to investigate the order that it's associated with and make related changes in your accounting records. A line_type can be unknown when there is a difference between the amount of money refunded and the value of the goods being returned by the customer.

There are two reasons for these adjustments:

1. There’s a discrepancy between the value of the products changing hands and the value of money changing hands. For example, the customer returns a $20 t-shirt to a store that charges a $2 restocking fee. In this case, a row is added to the sales table for the return of the $20 t-shirt. To account for the $2 stocking fee, an adjustment row is added to the sales table, which has the line_type of unknown.

2. The products that are returned are not tracked, so there is no way to know which items were returned. When the merchant processes a return, they do not select the products that are returned. Instead, the merchant leaves these fields blank and changes the amount refunded directly. This amount becomes the value of the products returned, but there is no way to know which products this value represents.

Order example

A customer orders three t-shirts for $20 each and $9 shipping. Their order results in the following rows being added to the sales table:

• 1 line item row that records the sale of the t-shirt. This row includes the following column values:

  • line_type: product
  • kind: order
  • quantity: 3
  • total_sales: $60
  • tax: $3

• 1 line item row for the shipping charge. This row includes the following column values:

  • line_type: shipping
  • kind: order
  • shipping: $9

The customer decides to return one of the t-shirts. The merchant refunds part of the order.

Return example with no adjustments

The merchant processes the return by selecting 1 for the number of t-shirts returned. This is an example of a simple return because the merchant doesn't need to make any adjustments to the refund form:

In the sales table, one row is added for the line item of the returned t-shirt. This row contains the following values:

• line_type: product
• kind: return
• quantity: 1
• total_sales: -20
• tax: -1

Return example with a shipping refund

The merchant processes the return by selecting 1 for the number of t-shirts returned. The merchant also decides to refund $3 for a part of the shipping charge. The merchant needs to make one manual change to refund the shipping charge:

In the sales table, two rows are added to track this return:

• One row for the line item of returned t-shirt:

  • line_type: product
  • kind:return
  • quantity: 1
  • total_sales: -20
  • tax: -1

• One row for the partial return of the shipping charge:

  • line_type: shipping
  • kind: return
  • quantity: 1
  • total_sales: -3

Return example where the value of the products returned do not match the value of products purchased

The merchant processes the return by selecting 1 for the number of t-shirts returned. The merchant also decides to charge a $2 restocking fee. The merchant changes the value of the refund amount:

In the sales table, two rows are added to track this return:

• One row for the line item of the returned t-shirt:

  • line_type: product
  • kind: return
  • quantity: 1
  • total_sales: -20
  • tax: -1

• One adjustment row that accounts for the restocking charge:

  • line_type: unknown
  • kind: return
  • quantity: 1
  • total_sales: 2

This adjustment row appears because the value of the product returned ($20) is different from the value of the refund ($18). You will likely need to make an adjustment in your accounting records to account for this difference.

Return example where returned products are not tracked

When the merchant processes the return, they do not select the number of products returned. Instead, the merchant just changes the value of the refund amount:

In the sales table, one adjustment row is added to track this return:

• line_type: unknown
• kind:return
• quantity: 1
• total_sales -20

Only this adjustment row appears because this return does not track the product returned. This method of returning products is not recommended because you cannot use the sales table to figure out which items were actually returned. You will likely need to make some adjustments in your accounting records to compensate for this missing information.

Multiple returns

If there are multiple returns on the same order, then there will be multiple rows for each return.

Shipping is always applied to an entire order. So it is recorded in a separate row with its line_type set to shipping.

In some countries, such as the United States, when a customer returns an item, the full value of the sales tax is considered to have been refunded, regardless of how much money you refund to the customer. If you refund a product for an amount that is less than the value of the product, then you will likely need to adjust your payment books to account for the taxes. For example, a customer returns a $20 t-shirt and you refund them $18 (cost of t-shirt - $2 restocking fee). In addition to the $18, you also return the tax that the customer paid on the $20 t-shirt. This discrepancy will need to be adjusted in your payment books.

When an order has an order-level discount applied to it, then that discount is divided proportionally amongst the line items, so that a portion of the order-level discount applies to each line item.

Discounts are always applied before taxes.

When a gift card is sold, a row is recorded in the sales table, but the value of the card is not included in any of the money columns such as gross_sales or total_sales.

In order to avoid double-counting gift cards (once when they are sold, and again when they are used), the sale of a gift card should be reflected not as revenue, but as an increase in liabilities. The sales table provides columns to track the sale of gift cards separately from product sales. For example, the following columns track the value of gift cards: gift_card_gross_sales, gift_card_issued, and gift_card_discounts.

When a gift card is used to purchase products, the value of those products will be reflected in gross_sales, total_sales, and other columns.

The online store sessions table contains data about when customers use gift cards to pay for their orders.

All money columns are stored as decimals to two digits of precision, and are formatted in the currency of the store. See below for notes on currency conversion and comparing stores across multiple currencies.

All money columns are also available with a _fx suffix. These columns can be used for cross-store calculations involving different currencies. The conversion rates used in these calculations are taken at 9:20 AM UTC everyday. These rates are calculated based on the mid-point between the buy and sell rates from the global currency markets. For returns, the conversion rate used is that of the day when the returns occurred.

Every value in a money column is represented to the penny (not fractional pennies). When allocating (dividing) amounts across multiple line items (such as taxes or order discounts), the amounts may not divide evenly. The extra pennies (remainders) are allocated one at a time starting with the first line item, until they are gone. In aggregate, they sum up correctly. In isolation, one line item may have a different tax amount (or order discount amount) than another line item of the same price, because a penny was allocated to one line item but not the other.

After allocated, the pennies are never re-allocated or redistributed amongst the line items (for example, in the case of a partial return).

Examples

The following example returns the number of discounts by month:

The following example returns the total gift card sales by month:

The following example lists returns by month:

The following example returns the average duration of returns:

The following example returns the average number of variants ordered in a line item:

The following example returns shipping costs as a ratio of the sales amount:

The following example returns the number of gift card sales and the number of gift cards issued by month:

• Sales, orders, and line items
• How returns are recorded