Gallery
Share
Explore

icon picker
US Sales Metrics Definition


2.1 Fields pulled directly from Shopify

Table 2
Metric in final view
Raw Shopify field
Shopify definition
lineitem_sales
total_line_items_price
“The sum of all line-item prices in the shop currency.”
discount_amount
total_discounts
“The total discounts applied to the price of the order in the shop currency.”
return_amount
refunds[].transactions[].amount
Refund → total amount across all transactions for the refund.
tax_amount
total_tax
“The sum of all taxes applied to the order in the shop currency.”
shipping_amount
total_shipping_price_set.shop_money.amount
total_shipping_price_set holds order-level shipping charges and discounts in shop & presentment currencies.
duties_amount
original_total_duties_set.shop_money.amount
Field captures duties on the original order prior to edits.
additional_fees_amount
original_total_additional_fees_set.shop_money.amount
Added in API 2023-04 to represent “extra costs that aren’t duties or taxes.”
There are no rows in this table
⁠

2.2 Fields pulled from Amazon fact table

Table 3
Metric
Raw column(s)
Logic
lineitem_sales
total_sales
SUM of sales where order_status <> 'Cancelled'
discount_amount
discount
SUM of discounts on those same rows
return_amount
total_sales
SUM of total_sales only on rows flagged is_refund = 1
tax_amount
tax
SUM
shipping_amount
shipping_price
SUM
duties_amount / additional_fees_amount
n/a
Set to 0 because Amazon dataset does not break these out
There are no rows in this table
⁠
⁠

3 Final Metrics

Table 4
Metric label in final view
Formula in SQL
Business meaning
gross_sales
lineitem_sales + shipping_amount + tax_amount – discount_amount – return_amount
“Top-line” collected value after discounts & returns, inclusive of shipping and tax
net_sales
lineitem_sales – discount_amount – return_amount + shipping_amount
Product revenue after discounts & returns, excluding tax but including shipping
​
​
​
There are no rows in this table
⁠
(Because the intermediate column names are reused, remember that the gross_sales inside this formula is the pre-tax product subtotal fetched from Shopify/Amazon.)
⁠

4 Customer and channel tagging (non-numeric but critical)

Channel — literal 'SHOPIFY' or 'AMAZON' assigned in each branch before the UNION.
customer_tag — hierarchy applied to avoid duplication: ​Explicit overrides:
APOGEN (Shopify order tagged with Apogen)
APOS&A (tagged APOS&A) ​Mapped tiers (non-Apogen): S&A DISTRIBUTOR → DISTRIBUTOR → S&A → OTHERS → GENERAL (default).
If multiple tags match an order, the ROW_NUMBER() window in ranked_shopify keeps the highest-priority tag and discards the rest.
⁠

5 Date handling

order_date is always computed as
DATE(TIMESTAMP(created_at), 'America/Los_Angeles')

ensuring that the reporting day matches the US-Pacific business calendar, regardless of the store’s native timezone.
⁠

6 Key take-aways / implementation reminders

Shopify test & migrated orders are removed upfront (test = FALSE, no Migrated_Orders tag).
Fully refunded orders are excluded early by financial_status != 'refunded'; partial refunds still flow through as return_amount.
Currency consistency – all Shopify amounts are in the shop currency by design, so you do not need forex conversions before combining with Amazon (which already reports in USD for the US seller account).
To extend the model to other geographies, update the timezone in every DATE(TIMESTAMP()) call and revisit duties/additional-fees availability for Amazon.

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.