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 user's 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. Create a Shopify connection (you can skip this part if you already have one)
3. Go to https://app.coupler.io/app/t/417caa02-248f-11ef-8b1e-835b42e7cc8d
4. Connect and select your Shopify connection from Step 2
   
5. Proceed to "Transformations". You should now see a preview of the report that will be exported to your destination file.

From Scratch

1. Create a Shopify Importer:

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.title
• line_items.variant_title
• line_items.sku
• line_items.price_set.shop_money.amount
• line_items.total_discount
• current_total_tax

7. Add the missing columns by creating formulas:

For calculating "Net Quantity":

IFNULL(({current_subtotal_price}/{subtotal_price}),({total_line_items_price}/{line_items.total_discount}))

For calculating " Returns"

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

For calculating " Net Sales":

For calculating " Total Sales":

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.title >> "Product Title"
• line_items.variant_title >> "Variant Title"
• line_items.sku >> "Variant SKU"
• line_items.price_set.shop_money.amount >> "Gross Sales"
• line_items.total_discount >> "Discounts"
• current_total_tax >> "Tax"

The result will look something like this:

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

Notice that there is no "Summary" column and the results are not aggregated by SKU. But, you can do the aggregation on the destination side (like in Google Sheets by using a pivot table) later on.