Skip to content

Daily Sales

Query

--Super simple query for getting source-of-truth sales & revenue data, requires a dfate_spine query creating a dataframe named "included_dates"

SELECT
    id.dt,  
    ds.ITEMS_SOLD        AS units_sold,
    ds.ITEMS_REFUNDED    AS units_refunded,
    ds.NET_ITEMS_SOLD    AS net_units_sold,
    ds.GROSS_SALES       AS gross_sales,
    ds.DISCOUNTS         AS discounts,
    ds.REFUNDS           AS refunds,
    ds.NET_SALES_REVENUE AS net_revenue 
FROM 
    included_dates id
    left outer join ANALYTICS_DB.ANALYTICS.DAILY_SALES ds on id.dt = ds.date
where DATE < CURRENT_DATE
ORDER BY DATE

Overview

Pulls source-of-truth sales and revenue data from ANALYTICS_DB.ANALYTICS.DAILY_SALES, joined onto the date spine to ensure a continuous daily series with no gaps. Dates with no sales activity will have NULL values for all metrics.

Dependencies

Dependency Type Description
included_dates Hex dataframe Output of the date_spine query — defines the reporting window and time grain
ANALYTICS_DB.ANALYTICS.DAILY_SALES Snowflake table Source-of-truth sales and revenue data

Output Columns

Column Description
dt Calendar date
units_sold Gross units sold on dt
units_refunded Units refunded on dt
net_units_sold Units sold minus units refunded
gross_sales Total revenue before discounts and refunds
discounts Discount amounts applied
refunds Refund amounts issued
net_revenue Revenue after discounts and refunds

Notes

  • All monetary values reflect Shopify-reported figures. Currency is USD.
  • The left join onto the date spine means days with no sales will appear in the output with NULL metric values — downstream queries should handle these with COALESCE as needed.
  • Filters to DATE < CURRENT_DATE to exclude today
  • Results are ordered chronologically by date