Shopify
Order Master
Consolidates sales, refunds, and adjustments from Shopify into a unified view by combining data from sales, refund, and adjustment sources. It includes additional fields to indicate whether a record represents a sale, refund, or adjustment and provides detailed order information such as customer details, product information, and financial status. The template uses union operations to merge data from different sources into a single dataset.
1-- This model consolidates order sales, refunds, and adjustments into a single unified view.
2-- Data Sources, SQL templates:
3-- 1. Sales data from {{order_items_sales}}
4-- 2. Refund data from {{order_items_refund}}
5-- 3. Adjustment data from {{order_adjustments}}
6--
7-- Additional fields:
8-- - orders: A flag indicating if the record represents a sale (1) or not (0).
9-- - order_type: A label to differentiate between 'sale', 'refund', and 'adjustment'.
10select
11 store
12 , order_created_at as date
13 , order_id
14 , order_line_id
15 , order_name
16 , fulfillment_status
17 , customer_id
18 , customer_name
19 , email
20 , sku
21 , product_title
22 , product_variant_title
23 , billing_address_city
24 , billing_address_company
25 , shipping_address_city
26 , shipping_address_country
27 , shipping_address_zip
28 , quantity
29 , currency
30 , net_sales
31 , total_sales
32 , customer_type
33 , financial_status
34 , 1 as orders
35 , 'sale' as order_type
36from
37 {{order_items}}
38union all
39select
40 store
41 , order_refund_created_at
42 , order_id
43 , order_line_id
44 , order_name
45 , fulfillment_status
46 , customer_id
47 , customer_name
48 , email
49 , sku
50 , product_title
51 , product_variant_title
52 , billing_address_city
53 , billing_address_company
54 , shipping_address_city
55 , shipping_address_country
56 , shipping_address_zip
57 , - quantity_returned as quantity
58 , currency
59 , - net_refund as net_sales
60 , - total_refund as total_sales
61 , customer_type
62 , financial_status
63 , 0 as orders
64 , 'refund' as order_type
65from
66 {{order_items_refund}}
67union all
68select
69 cast(store as string) as store
70 , order_refund_created_at
71 , order_id
72 , order_line_id
73 , order_name
74 , fulfillment_status
75 , customer_id
76 , customer_name
77 , email
78 , sku
79 , product_title
80 , cast(product_variant_title as int) as product_variant_title
81 , billing_address_city
82 , billing_address_company
83 , shipping_address_city
84 , shipping_address_country
85 , shipping_address_zip
86 , quantity_returned as quantity
87 , currency
88 , net_refund as net_sales
89 , total_refund as total_sales
90 , customer_type
91 , financial_status
92 , 0 as orders
93 , 'adjustment' as order_type
94from
95 {{order_adjustments}}Example of output from model:
store | date | order_id | order_line_id | order_name | fulfillment_status | customer_id | customer_name | email | sku | product_title | product_variant_title | billing_address_city | billing_address_company | shipping_address_city | shipping_address_country | shipping_address_zip | quantity | currency | net_sales | total_sales | customer_type | financial_status | orders | order_type
---------------|---------------------|----------|---------------|------------|--------------------|-------------|---------------|-------------------------|-----------|----------------|-----------------------|----------------------|-------------------------|-----------------------|-------------------------|----------------------|----------|----------|-----------|-------------|----------------|-----------------|--------|-----------
Shopify Store | 2023-10-01 10:30:00 | 1001 | 2001 | #1001 | fulfilled | C123 | John Doe | johndoe@example.com | SKU123 | T-Shirt | Large | New York | Doe Inc. | Los Angeles | USA | 90001 | 2 | USD | 40.00 | 50.00 | regular | paid | 1 | sale
Shopify Store | 2023-10-02 11:00:00 | 1002 | 2002 | #1002 | refunded | C124 | Jane Smith | janesmith@example.com | SKU124 | Jeans | Medium | San Francisco | Smith LLC | San Francisco | USA | 94101 | -1 | USD | -30.00 | -35.00 | regular | refunded | 0 | refund
Shopify Store | 2023-10-03 12:00:00 | 1003 | 2003 | #1003 | adjusted | C125 | Alice Brown | alicebrown@example.com | SKU125 | Jacket | 101 | Chicago | Brown Co. | Chicago | USA | 60601 | 1 | USD | 25.00 | 30.00 | regular | adjusted | 0 | adjustmentThe "Order Master" SQL model is designed to integrate with Shopify and provides a comprehensive view of all order-related activities by consolidating sales, refunds, and adjustments into a single dataset. This model draws from three primary data sources: sales data, refund data, and adjustment data. It includes additional fields such as a flag to indicate whether a record is a sale and a label to differentiate between sales, refunds, and adjustments. By unifying these data points, the model allows businesses to gain insights into their overall sales performance, track customer interactions, and analyze financial statuses. This can be particularly useful for identifying trends in sales and refunds, understanding customer behavior, and making informed decisions to optimize inventory and customer service strategies.
