How to connect Shopify data into DashThis using Google Sheets?

By using Google Sheets with a third party tool, you can easily import your Shopify data into DashThis without any manual intervention.

DashThis does not provide any native integration for Shopify data at the moment.
But, the Data Export app is built to export the right type of data from your Shopify account and import it into a spreadsheet that DashThis will be able to read and display in your dashboards.

 

Here’s how to connect Shopify into DashThis using the Data Export app and Google Sheets.

 

  1. Add the Data Export app
  2. Create reports with Data Export
    1. Aggregated data
    2. Product Detailed data
  3. Schedule a report to GSheets
  4. Connect GSheets into DashThis
  5. Notes

 

 

1- Add the Data Export app in your Shopify account

 

  • Visit the Shopify app store and look for the Data Export third-party: https://apps.shopify.com/data-export.
  • Click on “Add app”. You’ll be redirected towards your Shopify account in order to install the app.
  • Review the details and click on “Install app”.

 

We recommend you do the same for each Shopify account you want to import data from.

2- Create a report with Data Export

 

Now that you’ve installed the Data Export app, you need to create “reports” that will automatically send your Shopify data into your Google spreadsheet(s).

There are two types of Data Export reports that you may want to use, depending on the data you need to see in DashThis: one with Aggregated data, the other with Product Detailed data.

 

Aggregated data

  • In the “Apps” section of your Shopify account, go in the Data Export app.
  • Click on “Create new report” and give a name to your first report.


  • Select the type of report "Order".
    You won’t be able to mix up KPIs from different report types. That’s why you’ll need to create a second report for product detailed data for example.

  • Check the KPIs that are relevant to your reporting and that you want to import into DashThis. Here’s what we recommend:
    • Order Date
    • Order Amount
    • Net Order Amount
    • Total Quantity
    • Net Quantity
    • Total Tax
    • Total Discounts
    • Total Shipping
    • Total Refunded
    • Quantity Refunded

  • Make sure the “Order Date” field is selected and is located in the first column.
    To do so, click on “rearrange fields” and drag & drop elements.

  • Once satisfied, click on “Save”.

 

Product detailed data

  • Click on “Create new report” and give a name to your first report.


  • Stay in the “Order Line Items” report type.
  • Check the KPIs that are relevant to your reporting and that you want to import into DashThis. Here’s what we recommend:
    • Order Date
    • Product ID
    • Product Title
    • Variant Title
    • Product Type
    • Quantity
    • Net Quantity
    • Total Tax with Shipping Tax
    • Total Discount
    • Total Shipping
    • Total Refunded Quantity
    • Total Refunded

  • Make sure the “Order Date” field is selected and is located in the first column.
    To do so, click on “rearrange fields” and drag & drop elements.

  • Once satisfied, click on “Save”.

 

3- Schedule a report from Data Export to Google Sheets

 

To ensure your Shopify data will automatically be updated in your DashThis report, you must now schedule your report export to Google sheets.

 

  • Click on “Schedule | Run” next to the report you’ve just created.

  • Choose the frequency of the report export, the start date and the end date if applicable.

  • Choose “Google Sheets” as the delivery destination of your report, and indicate the folder where the file will be sent and the title of the spreadsheet.
    Choose “Overwrite” for the last question.



  • Click on “Run / Save Schedule”.

You’ll be able to find your scheduled reports in the “Actual Schedule” section of your Data Export app.



Here’s an example of the spreadsheet you’re supposed to end up with for the Aggregated data, and the one for the Product Detailed data.

 

4- Connect your Google Sheets in DashThis

 

Back in your DashThis account, connect the Google Sheets integration in the Integration Manager and add the spreadsheet file(s) containing your Shopify data.

 

You can now create a dashboard with your spreadsheet data source and add your widgets like with any other integration.

 

Data Export will now take care of the export of your Shopify data into your spreadsheet file(s) on a regular basis. And DashThis, on the other hand, will take care of importing this data into your dashboards.

 

And you’re done!

 

5- Notes

 

For performance reasons, DashThis can only import 52 columns and 5,000 rows from a spreadsheet file.

If your spreadsheet contains more data, we recommend you to use the CSV export instead.