Create Reports for your Shopify Store in Google Sheets

Analyze your store's performance, track sales trends, and make informed business decisions by leveraging on Google Sheets functions.

Create your own dashboards and reports in Google Sheets

Have you ever wanted to have any data you need from your Shopify store in Google Sheets to create your own custom reports? With eCommix, you can easily export all your store's data and tailor your reports to meet your specific needs.

In this guide, we'll show you how to do it.

1. Export your Shopify Store Data

The very first thing to do is to understand which data you need for your custom dashboard or report. eCommix allows you to extract data about your orders, products, customers, and more.

Follow the steps in Export your Shopify Store Data to Google Sheets to export your Orders.

2. Reference the Data Sheet

After creating your Orders export, you'll get a spreadsheet similar to this:

Your data will be exported into the "Data" sheet every time you run an execution or according to the automatic refresh schedule you set for your export.

Since rows in this sheet are frequently deleted and inserted, you should not edit it.

Additionally, the "Data" sheet cannot be renamed because it is used by eCommix to place the imported data.

To avoid this, create an additional sheet and reference values from the "Data" sheet.

Let's create a "Reference" sheet:

On the "Reference" sheet, select the first cell and input this formula:

=ARRAYFORMULA(Data!1:1000)

This will reference the desired range from the "Data" sheet into the "Reference" sheet, allowing you to edit it as you wish while keeping any incoming data refresh.

This is useful for cases where you want to add additional information, such as assigning orders to members of your staff:

In this example, we've limited the range to column A because that’s all the information we need. However, you can adjust this to fit your requirements.

By doing this, any new order placed in Shopify will be automatically added here, allowing you to assign it to the appropriate staff member later:

3. Grouping, filtering, and sorting your Shopify data

You can even go a step further and use Google Sheets as a SQL database, performing aggregations, filtering, sorting, and more using the QUERY function.

For example, if you want to list your best-selling products, you need to export your Order Line Items dataset.

Make sure to select the following fields:

The spreadsheet you get will be similar to this:

Now create a new sheet called "Summary" and type the following formula in the first cell:

=ARRAYFORMULA(
    QUERY(
        {
            INDEX(Data!A3:H, 0, MATCH("Title (Line Item)", Data!A2:H2, 0)),
            INDEX(Data!A3:H, 0, MATCH("Quantity (Line Item)", Data!A2:H2, 0)) * INDEX(Data!A3:H, 0, MATCH("Original Unit Price (Line Item)", Data!A2:H2, 0)),
            INDEX(Data!A3:H, 0, MATCH("Quantity (Line Item)", Data!A2:H2, 0))
        }, 
        "SELECT Col1, SUM(Col2), SUM(Col3) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 'Product', SUM(Col2) 'Net Sales', SUM(Col3) 'Units Sold'", 0
    )
)

This will find three columns in your Data sheet:

• Title (Line Item)

• Original Unit Price (Line Item)

• Quantity (Line Item)

It will then group by Title to calculate the Net Sales and Units Sold for each product and order the results by Net Sales:

4. Combine all your Shopify Data into a single Spreadsheet

Examining multiple spreadsheets can be time-consuming. By combining different spreadsheets into a single sheet, you can manage all your information in one place.

Let's say you have the following exports:

• Customers

• Product Variants

• Order Line Items

Each of these exports has its own spreadsheet, but you can create a new spreadsheet called "Store Details" and reference data from these three using the IMPORTRANGE formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rA-2udVQQ_MFvv2mAtYIS8e0niqeBRFRLy7UuHPP-mk/edit", "Data!A1:Z1000")

Update the Google Sheet link with your own. After entering the formula, you might be asked to allow access. If so, click on the error and grant the permission:

After this, the data is pulled into your sheet:

Remember, you are not just copying the data but creating a reference. Any updates in your original export spreadsheets will be automatically reflected in this new spreadsheet.

Repeat this process with the other two spreadsheets, and you'll have a comprehensive summary view of your store's data in detail:

Ready to get started? Begin exporting your data today.