Shopify Custom Report: Sales by product variant SKU

Shopify API does not support pulling data from a custom report. However, you can manipulate the raw data from Shopify to create a report similar to it.

One of the most requested custom report is the "sales by product variant SKU". In this article, we have provided the steps on how to create a report similar to it, but, we also prepared a template our users can use for their convenience. 

Disclaimer: We cannot guarantee that it would match what you are seeing on the Shopify UI 100% as the values are just derived from the existing columns we can pull from Shopify. This will just serve as an idea of how you can manipulate the data to achieve similar reports.

Template

1. Make sure that you are signed in to Coupler.io 
2. Go to https://app.coupler.io/app/t/dc8a8cb4-e1f7-11ef-a790-d72d5ee19437
3. Connect or create your Shopify connection
4. Configure your destination settings. By default, it is set to Google Sheets but feel free to edit it as per your requirement.
5. Run the dataflow.

From Scratch

1. Create a Shopify Dataflow:

2. Select/Add your source account. See how to connect your Shopify account here.

3. On the basic settings, select the "Orders with line items" data entity:

4. On the advanced settings, you can specify some filters (e.g. date filters).

5. During the "Transformations" step, hide all your columns:

6. We will then show the needed columns only. Please type and tick the checkbox corresponding to the following columns:

• Line items other: Title
• Line items other: Variant Title
• Line items: Sku
• Line items price set: Shop amount
• Line items other: Total Discount
• Current total: Tax

7. Add the missing columns by creating formulas:

For calculating "Net Quantity":

IF(({subtotal_price}-{current_subtotal_price})=={line_items.price}, 0, IF(({subtotal_price}-{current_subtotal_price})==0, IFNULL(({current_subtotal_price}/{subtotal_price}),({total_line_items_price}/{line_items.total_discount})),{current_subtotal_price}/{line_items.price}))

For calculating "Returns"

({line_items.quantity}-IF(({subtotal_price}-{current_subtotal_price})=={line_items.price}, 0, IF(({subtotal_price}-{current_subtotal_price})==0, IFNULL(({current_subtotal_price}/{subtotal_price}),({total_line_items_price}/{line_items.total_discount})),{current_subtotal_price}/{line_items.price})))*{line_items.price_set.shop_money.amount}

For calculating "Net Sales":

({line_items.price_set.shop_money.amount}-{line_items.total_discount})-({line_items.quantity}-IF(({subtotal_price}-{current_subtotal_price})=={line_items.price}, 0, IF(({subtotal_price}-{current_subtotal_price})==0, IFNULL(({current_subtotal_price}/{subtotal_price}),({total_line_items_price}/{line_items.total_discount})),{current_subtotal_price}/{line_items.price})))*{line_items.price_set.shop_money.amount}

For calculating "Total Sales":

(({line_items.price_set.shop_money.amount}-{line_items.total_discount})-({line_items.quantity}-IF(({subtotal_price}-{current_subtotal_price})=={line_items.price}, 0, IF(({subtotal_price}-{current_subtotal_price})==0, IFNULL(({current_subtotal_price}/{subtotal_price}),({total_line_items_price}/{line_items.total_discount})),{current_subtotal_price}/{line_items.price})))*{line_items.price_set.shop_money.amount})-{current_total_tax}  

8. Rearrange the columns so it would be similar to the sales by product variant SKU report on Shopify. Please see how to Reorder columns.

9. You can rename all the other columns so it is in the same format as the report:

• Line items other: Title >> "Product Title"
• Line items other: Variant Title >> "Variant Title"
• Line items: Sku >> "Variant SKU"
• Line items price set: Shop amount >> "Gross Sales"
• Line items other: Total Discount >> "Discounts"
• Current total: Tax >> "Tax"

10. You can also sort it by Total Sales:

11. The result is separated by line item so you can aggregate it by Product title. Click the Aggregate button.

12. Choose Product Title , Variant Title, and Variant SKU as the Dimensions:

13. Select the rest of the columns as Metrics and choose SUM as the operation:

14. Click the Aggregate Data button. The result will look something like this:

Comparing with the "Sales by product variant SKU" report on Shopify:

Note: Notice that there is no "Summary" column. But, you can add it on the destination side later on.