Order Items Adjustmens
1-- This query creates a order adjustment summary by combining data from order adjustments, order refunds, orders, and customers.
2-- It provides information about the order, refund, transactions, order details, and customer characteristics.
3select
4 r.created_at as order_refund_created_at
5 , oa.order_id
6 , null as order_line_id
7 , o.name as order_name
8 , o.fulfillment_status
9 , o.customer_id
10 , concat(c.first_name, ' ', c.last_name) as customer_name
11 , o.email
12 , cast(null as string) as sku
13 , cast(null as string) as product_title
14 , cast(null as string) as product_variant_title
15 , o.shipping_address_city
16 , o.billing_address_company
17 , o.shipping_address_city as shipping_address_city
18 , o.shipping_address_country
19 , o.shipping_address_zip
20 , null as quantity_returned
21 , o.currency
22 , (oa.amount - oa.tax_amount) as net_refund
23 , oa.amount as total_refund
24 , case
25 when o.number = 1 then 'First-time'
26 else 'Returning'
27 end as customer_type
28 , 0 as orders
29 , o.financial_status
30from
31 {{raw.shopify.order_adjustment}} as oa
32 left join {{raw.shopify.order_refund}} r on oa.refund_id = r.id
33 left join {{raw.shopify.order}} as o on oa.order_id = o.id
34 left join {{raw.shopify.customer}} c on c.id = o.customer_idorder_refund_created_at | order_id | order_line_id | order_name | fulfillment_status | customer_id | customer_name | email | sku | product_title | product_variant_title | shipping_address_city | billing_address_company | shipping_address_city | shipping_address_country | shipping_address_zip | quantity_returned | currency | net_refund | total_refund | customer_type | orders | financial_status
------------------------|----------|---------------|------------|--------------------|-------------|---------------|-----------------------|------|---------------|----------------------|-----------------------|-------------------------|-----------------------|--------------------------|---------------------|------------------|----------|------------|--------------|---------------|--------|-----------------
2023-09-15 10:23:45 | 1001 | NULL | #1001 | fulfilled | 501 | John Doe | johndoe@example.com | NULL | NULL | NULL | New York | Doe Inc. | New York | USA | 10001 | NULL | USD | 50.00 | 55.00 | First-time | 0 | paid
2023-09-16 14:12:30 | 1002 | NULL | #1002 | pending | 502 | Jane Smith | janesmith@example.com | NULL | NULL | NULL | Los Angeles | Smith LLC | Los Angeles | USA | 90001 | NULL | USD | 30.00 | 35.00 | Returning | 0 | pendingThe "Order Items Adjustments" SQL model for Shopify integration is designed to generate a comprehensive summary of order adjustments by aggregating data from order adjustments, order refunds, orders, and customer information. This model provides detailed insights into various aspects of the order process, including refund transactions, order details, and customer characteristics. It captures key information such as order IDs, customer names, email addresses, and financial statuses, alongside refund amounts and customer types (first-time or returning). This SQL model is particularly useful for businesses seeking to analyze refund patterns, customer behavior, and the financial impact of order adjustments. By leveraging this data, businesses can enhance their understanding of customer interactions and improve their refund management strategies.
