Managers can export sales data from Shopify and import it into Deputy.
Using sales data to generate meaningful statistics about your business will help you schedule more efficiently, minimise costs and maximise revenue.
Here are the steps to bring your Shopify sales data into Deputy:
1. Export data from Shopify
You can export a CSV spreadsheet list of your orders from the Orders page in Shopify. Read more about the data you can export from Shopify.
1. Log in to Shopify and check you have selected the correct Shopify location you wish to export orders for, or select All locations if you wish to export all locations orders.
2. If you need to, you can sort and filter your orders to display the orders (such as fulfilled or unfulfilled orders) you want to export or leave unfiltered to display all orders.
3. Click Export on the top right of the page.
4. Click on Orders by date and select the date range of order you wish to export as CSV. Click Export orders.
5. The administrator or your Shopify account will receive an email with a link to download the CSV file. Click on the link to download the file to your computer.
2. Review the data
Open the CSV file in a spreadsheet editing software such as Excel.
Since Shopify exports some data in a different format than what Deputy is expecting, we need to amend some of the data in the spreadsheet. There are three changes we need to make:
1. Remove unwanted columns
Deputy only needs this specific column data from the Shopify export:
- "Name" (Colum A)
- "Employee" (Column BA)
- "Created at" (Column P) or "Paid" at (Column D) depending on which timestamp you prefer to use
- Location
- Total (Column L) to import the total sale price of the order
The rest of the columns are not used in Deputy and can be deleted.
2. Add new columns
Add four new columns to the excel spreadsheet:
| Column title: | "Area" | "Metric Type" | "Metric Status" | "Timestamp" |
| Data to populate all the rows underneath the column | Populate all data rows in this column with a valid Area from your Deputy account where you want the sales for each location to be mapped. | Sales | Actuals | add the empty column to the right of "Created at" or Paid at" column and leave rows underneath the header empty and see below |
Hint: To fill rows quickly, enter text in one cell and click and drag down the column to fill the cell.
3. Reformat the timestamp data
The Shopify timestamp column data needs to be reformatted to be able to be imported into Deputy.
The data time stamp is from Shopify exported as:
2024-06-03 11:33:09 +1000
but Deputy needs it in the following format:
2024-06-03T11:33:09
We can use the following formula in Excel to reformat the Shopify date timestamp data:
=LEFT(A2,10)&"T"&MID(A2,12, 8)
where A2 is assumed to be the cell containing the Shopify timestamp and B2 is the empty cell you are reformatting the data into. If you are using different columns for your timestamp data just change the reference cell in the formula.
Below is a demonstration of the formula being used to reformat the timestamp for one row of data and then click and drag down the column to reformat all timestamps.
Once you have amended your CSV file in Excel it may look something like this:
Save your CSV file to your computer and you are ready to import.
3. Import sales data into Deputy
To import the CSV file into Deputy, you can use Deputy's bulk importer tool.
1. On the Schedule page, click on Insights.
2. Select Sales on the left-hand side then click on the three dots on the right-hand side of the page. Select Import from file from the drop-down menu.
3. Click Continue to upload.
4. Click Upload file and select your amended CSV file from your computer to upload it.
5. Review and map the data columns:
- The Shopify columns are on the left and Deputy data columns are on the right.
- Use the drop-down menus to select the correct field. The table below will assist you in knowing what fields to map. Note: some fields will be ignored (such as the original timestamp column in the incorrect format) and you will not map this field to any Deputy field.
- When you hover over the fields with your cursor, sample data is displayed on the far right-hand side column to ensure you are selecting the correct column.
Mapping
| Shopify default data column title (incoming fields) | Maps to Deputy data (destination) | Mandatory for Deputy |
| Name | Unique reference number | NO |
| Employee (do not use the "Email" column from Shopify as this is the customer's email) | NO | |
| Timestamp | Date Timestamp | YES |
| Location | Location | YES |
| Total | Metric Quantity/Metric Amount | YES |
| New column created in Excel | Maps to Deputy data (destination) | Mandatory for Deputy |
| Area | Area | YES |
| Metric Type | Metric Type | YES |
| Metric Status | Metric Status | YES |
6. You will now see a count of how many of your records are Valid and ready to import and how many have errors in the Invalid tab.
Click on the Invalid tab to display all records with errors. You will see the fields coloured red if they have an error. You can find out more information about the expected format of the field with the error by hovering your mouse over the column names or you can click on the highlighted field to learn more about errors. You can use the Filter to review which fields are invalid and you have the choice to correct the errors in the original CSV file and upload or simply fix the errors now. If you have many data errors it may be faster to correct them in the original file and re-load the file, but it is also possible to correct them in Deputy as shown below.
Read more details about solving errors in Bulk import sales data.
Once you are ready to import click Submit.
7. Once the data has been uploaded you will see a job complete message, click Close.
8. When you review the Sales data that you uploaded data for and filter for the relevant date and area you imported data into you will see the data displayed as shown in the example below:
Read more about using sales data in Business Insights