Shopify
Order Items Refunds
Aggregates order refund data from Shopify, linking it with customer, order, product, and variant details to provide a comprehensive view of refunds. It includes information such as refund amounts, customer details, product titles, and order statuses. Additionally, it categorizes customers as either first-time or returning based on their order history.
1-- This query aggregates order refund data along with customer, order, product, and variant details.
2-- The intent is to provide a comprehensive view that connects refund information with the respective order, product, and customer metadata.
3select
4 orr.created_at as order_refund_created_at
5 , r.id as order_line_refund_id
6 , r.location_id
7 , r.order_line_id
8 , o.fulfillment_status
9 , o.name as order_name
10 , c.email
11 , c.id as customer_id
12 , concat(c.first_name, ' ', c.last_name) as customer_name
13 , o.billing_address_city
14 , o.billing_address_company
15 , o.shipping_address_city
16 , o.shipping_address_country
17 , o.shipping_address_zip
18 , r.quantity as quantity_returned
19 , orr.id as refund_id
20 , r.restock_type
21 , r.subtotal as total_refund
22 , r.subtotal - r.total_tax as net_refund
23 , r.total_tax
24 , o.id as order_id
25 , o.currency
26 , ol.price as order_line_price
27 , ol.product_id
28 , ol.sku
29 , p.title as product_title
30 , v.id as product_variant_id
31 , v.title as product_variant_title
32 , p.product_type
33 , ol.gift_card as is_gift_card
34 , case
35 when o.order_number = 1 then 'First-time'
36 else 'Returning'
37 end as customer_type
38 , o.financial_status
39from
40 {{raw.shopify.order_line_refund}} as r
41 left join {{raw.shopify.order_refund}} orr on orr.id = r.refund_id
42 left join {{raw.shopify.order_line}} as ol on r.order_line_id = ol.id
43 left join {{raw.shopify.order}} as o on ol.order_id = o.id
44 left join {{raw.shopify.customer}} c on c.id = o.customer_id
45 left join {{raw.shopify.product_variant}} as v on ol.variant_id = v.id
46 left join {{raw.shopify.product}} as p on ol.product_id = p.idExample of output from model:
order_refund_created_at | order_line_refund_id | location_id | order_line_id | fulfillment_status | order_name | email | customer_id | customer_name | billing_address_city | billing_address_company | shipping_address_city | shipping_address_country | shipping_address_zip | quantity_returned | refund_id | restock_type | total_refund | net_refund | total_tax | order_id | currency | order_line_price | product_id | sku | product_title | product_variant_id | product_variant_title | product_type | is_gift_card | customer_type | financial_status
------------------------|----------------------|-------------|---------------|--------------------|------------|---------------------|-------------|-------------------|----------------------|-------------------------|-----------------------|--------------------------|---------------------|------------------|-----------|--------------|--------------|------------|-----------|----------|----------|------------------|------------|----------|----------------|-------------------|-----------------------|--------------|--------------|----------------|-----------------
2023-10-01 12:34:56 | 101 | 1 | 1001 | fulfilled | #1001 | john.doe@example.com | 501 | John Doe | New York | Doe Inc. | Los Angeles | USA | 90001 | 2 | 201 | no_restock | 100.00 | 90.00 | 10.00 | 1001 | USD | 50.00 | 301 | SKU12345 | T-shirt | 401 | Large | Apparel | false | First-time | paid
2023-10-02 14:22:11 | 102 | 2 | 1002 | unfulfilled | #1002 | jane.smith@example.com| 502 | Jane Smith | San Francisco | Smith Co. | Chicago | USA | 60601 | 1 | 202 | return | 150.00 | 135.00 | 15.00 | 1002 | USD | 150.00 | 302 | SKU67890 | Jeans | 402 | Medium | Apparel | false | Returning | refundedThe "Order Items Refunds" SQL template is designed to aggregate detailed refund data from Shopify, integrating it with customer, order, product, and variant information. This comprehensive query provides insights into refund activities by linking each refund with its corresponding order and customer details. The SQL model is particularly useful for businesses looking to analyze refund trends, identify the most refunded products, and understand customer behavior related to refunds. By examining the refund data alongside order fulfillment status, customer demographics, and product specifics, businesses can gain valuable insights into areas for improvement in product quality, customer service, and inventory management. Additionally, the model helps in distinguishing between first-time and returning customers, allowing for targeted marketing strategies and enhanced customer retention efforts.
