Analyze your store's performance, track sales trends, and make informed business decisions by leveraging on Google Sheets functions.
Last updated
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.
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.
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:
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:
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:
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:
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:
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.
