Export Payouts from Shopify

You can export Shopify Payouts with Transactions to Excel or CSV format.
Export all of the payouts, or filter them by date intervals.

Filter Payouts

Export all payouts or apply the date filters:

  • Relative Date – for x last full minutes, hours, days, months
  • Date Interval – from date to date

Filter by relative date:

To filter for x last full days or months.

Filter the Shopify Payouts export by relative date for last 1 days

The full day means that it will not export for today because it’s not yet ended.

Filter by date interval:

For the date interval, you can set the dates in the past and in the future.

That’s because Shopify actually generates the transactions for the future which are in pending status. But you can already see what the amounts are.

For example, to set up the repeated Payouts export for the whole year, you can set the date range from Jan 1 till Dec 31.

Filter the Shopify Payouts export by whole yearRead further to see how you can make this beneficial for reporting.

Automated Shopify Payouts Report

You can become creative and make the repeated export which is used in the Excel or Google Sheets reports by automatically loading it from the exported URL.

You can also send that exported file to FTP/SFTP server each time it exports.

This example shows you how to create automatic Payouts report on Google Sheets.

1. Select export Payouts without filters

Select whether you want to include Transactions for your payouts.

Don’t apply any filters if you want the full payouts history to be visible on your Google Sheets report. Or you can apply the date filter if you want to limit it in certain timeframe.

2. Set the Export Options to schedule the repeated export to the same CSV file every day

Schedule repeated daily Shopify Payouts export to CSV to use in Google Sheets automated report
  • Select the “Schedule export on” and set the time when do you want the export to start every day. The time is in your shop time zone.
  • Select “Repeat every” and choose it to run every day.
  • Set the “Custom file name” so that each time the export runs, it generates the data in the same URL.
  • Uncheck the “Zip CSV files” checkbox so that the export is not zipped.
  • Choose the format to be “Excelify: CSV” to generate export as a CSV file to be imported into Google Sheets automatically.

3. Press “Schedule Export”

After pressing “Schedule Export“, wait a couple of minutes because your first scheduled export for today will run automatically.

Then go to “History & Schedule” and find the finished export to “payouts”. If it’s not yet finished, refresh the page – it should be done within 1-2 minutes depending on how large are your payouts.

You should see the history like this:

Exported Shopify Payouts history for custom URL

The top row is the next scheduled export at midnight.

And after that is the OK export which got just finished now.

In the Files column, right-click on the “payouts.csv” file and press “Copy link address”.

Copy the Shopify exported Payouts URL link

4. Use this link in the Google Sheets =IMPORTDATA formula

The link will be something like this:

https://fuji-excel-export-import-dev.s3.amazonaws.com/excelify-clean-1-4db63be6/export/payouts.csv

So the formula in your Google Sheets will look like this:

=IMPORTDATA("https://fuji-excel-export-import-dev.s3.amazonaws.com/excelify-clean-1-4db63be6/export/payouts.csv")

Your Google Sheet will be like:

Shopify Payouts exported to Google Sheets

Notice that the ID is repeating for each Transaction row, and the Amount is filled only for the first row of that payout.

That’s important so that you can sum the Amount column in Pivot tables, and at the same time be able to see what transactions make up that Payout.

5. Make the Pivot Table

Here you can knock yourself out by using this data in Pivot Table to make whatever report you need.

For example, here is how you can make the aggregated payouts by month:

Shopify Payouts export aggregated by Month

Good to know

  • If you already had Excelify installed, next time when you launch the Excelify app, it will ask you to confirm the changes in permissions because to export Payouts, the app needs access to this data.
  • If your Date and Month columns don’t show as a proper date but as some number, you should change the format of that column to display as Date.
    Google Sheets menu: Format -> Number -> Date
  • See the detailed documentation of each Payouts columns.

 

Scheduled exports from Shopify to Google Sheets

Google Sheets

Here we will take a look at how to make scheduled exports to Google Sheets from Shopify. You can use this to have always the newest exported information for better and automated report constructing

Steps exporting to Google Sheets

1. Setup export.

In the Excelify app select checkboxes with information that you wish to export to Google Sheets. Here we will use product information, but you can also apply this tutorial for any other export combintion.Export Products Shopify

2. Configure scheduled export options.

Here we will need to do two things – configure our export to run on specific times and set the custom file name for the export. We will need this to ensure that our exported file link is always the same. To do this we click on ‘Options’.

  • Select option Repeat every: 1 hour until cancelled. But you can choose any other time interval for your scheduled export.
  • And we will set Custom File name to – ‘ExcelifyProductsExport’
  • Unckeck – Zip CSV files.
  • Chose an export format – ‘Excelify: CSV
  • Press ‘Export’Shopify scheduled export

3. Copy exported file link.

You can do this by right clicking ‘Download Exported File’ button and selecting the option ‘Copy link address’.3 - Copy link address

4. Create a new Google Sheets sheet.

Head over to https://docs.google.com/spreadsheets/ and select ‘Blank’ sheet4 - Create new sheet

5. Use a formula to automatically get data from the exported file.

In cell A1 use following formula =IMPORTDATA(“”). In this formula, you need to paste the link you copied from ‘Download Export File’ button, so it looks something like this –

=IMPORTDATA("https://shopify-excel-export-import.s3.amazonaws.com/excelify-renars-5c851cb4/export/ExcelifyProductsExport.csv")

Import data into Google Sheets from Shopify

Success!

Now you have the newest data always at hand and always in the same place. It’s worth noting that Google Sheets will only automatically export IMPORTDATA function every hour so, there is no reason to set your scheduled exports too often.Export from Shopify to Google Sheets

Good to know!

How to manage Shopify Metafields?

Metafield

Here we will take a look at how to create, update and delete your Shopify metafields. As well as we will have a small insight on how to display your metafields in your storefront. We will take a look at the metafields for Products, but you can also manage your metafields with the Excelify for:


Creating Metafields

1. Export your Products with the Excelify app.

Export Products in Shopify

2. To create and manage our Metafields we will only need basic columns from exported file to identify the Product, so from exported file delete all columns except:

  • ID
  • Handle
Product identification columns Excelify

3. How to construct Metafield column.

Metafields are constructed by principle – Metafield: Namespace.Key [Metafield type], so let’s break it down:

  • Metafield: – This part allows our app to understand that this column will contain Metafield.
  • Namespace – This is a category or, how I like to say, a folder that contains your Metafields, so you can group different Metafields into these imaginary “folders”.
  • Key – The name of Metafield.
  • Metafield Type – There are 3 types of Metafields in Shopify – integer, string and json_string. Today we will be looking at string Metafields as they are the most popular.

4. Create your own Metafield column.

We will assume that we wish to display some custom product specification on our storefront so our Metafield column name will be ‘Metafield: specs.material [string].

Metafield column Excelify Shopify

5. Fill your Metafield column with values that you wish to be displayed.

Create metafield Shopify

6. Save & Import.

7. Check your new Metafields.

Sadly we cannot really see Metafields anywhere in Shopify so at the moment we can export our Products with Metafields to see in the exported file if they have stored correctly.


Updating Metafields

1. Export your Products with Metafields.

2. Delete unnecessary columns.

To update metafields we will only need Product identification columns and Metafield columns, so delete all columns except:

  • Id
  • Handle
  • Metafield: … (all your metafields columns)

3. Edit values for the Metafields you wish to update.

4. Save & Import your updated file.

5. Check your updated Metafields. 

To see updated Metafields you can export your Products with Metafields just as in the #8 step.


Deleting Metafields

1. Export your Products with the Metafields.

2. Delete unnecessary columns.

To delete metafields we will only need Product identification columns and Metafield columns you wish to delete, so delete all columns except:

  • Id
  • Handle
  • Metafield: … (Metafield columns you wish to delete)

3. Delete values from Metafield column.

To delete a metafield we do need to just delete all values within its column and import this metafield empty.

4. Save & Import.

The app now deleted metafields for the products that had empty value for those metafields.


Displaying metafields

Metafields are custom fields that you can use in various places, for example if you need to store some custom information that you will use in your exported reports, but one of main metafield usage is to display this custom stored information in your store, so we will now take a quick look on how to acheive this.

1. Create your metafields.

For this please follow list points #1 – #7.

2. Go to your Themes code in your Shopify Admin.

You can locate your themes code going in your Shopify Admin -> Online Store -> Themes -> Actions -> Edit Code

Edit themes code in Shopify

3. Locate product.liquid

product.liquid contains the code for the page that displays your products.

Liquid product Code file

4. Add code to display your Metafield

Now we will add just one line of code to display this new Metafields we created in the ‘Create Metafield’ section.

Material: {{ product.metafields.specs.material }}

Shopify liquid code Metafield

5. Save and go check any product in your store.

Custom text in storepage

Good to know!

Shopify “Daily SKU limit” for Products

Shopify has introduced the global “Daily SKU limit” for importing data for large stores.

There was no public announcement about this (yet), but several customers have started to experience this issue when importing large volumes of data.

In this article, we will keep updating you about everything we find out.

How to know if you have reached the Shopify daily SKU limit?

You have reached Shopify daily SKU limit if you see this error message when importing your Products:

Response code = 429.  Response message = Too Many Requests. Product: Daily SKU limit reached. Please try again later.

What Shopify says about it?

The daily SKU limit has been introduced for some shops that have over 100,000 variants, and are rapidly creating more.

The limit prevents the shop from creating more than 5,000 new variants a day with the API.

This is done to ensure the stability of the platform.

What can you do about it?

1. Ask Shopify to increase your daily limit

Contact the Shopify Support and ask them to increase your limit – explain the reasons behind needing that.

2. Don’t hit the limit

Organize your imports in such a way that you import the data not more than 5000 variants per day.

3. Tell us that this is important for you

We at the Excelify are collecting the list of shops for whom this is important – to pass this information to Shopify.

If this is important for you, please, send an e-mail to our [email protected] and tell why this is important for you to not have such a limit.

Latest update

Dec 5, 2018

Guess the time for imported data fields

Time zone of the Shopify import

Whatever time you put in the date/time columns – the Excelify app should now recognize the correct time format.

If your time will not have specified time zone, it will always assume the date and time in your Shop time zone.

The columns are like “Published At” or “Processed At“, etc.

Examples of different time formats that can be recognized:

  • 2018-11-20 21:23:20 GMT+2
  • 20.11.2018 21:23:20
  • 11/20/18
  • 11/20/2018 21:23:20
  • 20181120
  • …and so on

 

Import Shopify Metafields as json_string

Shopify import Metafields as json_string

To import the Metafields to Shopify as json_string type, import the Metafield column with the “[json_string]” suffix.

For example, your Metafield import column can be:

Metafield: custom.json [json_string]

Then you can use this field in your Shopify Liquid theme as the JSON object.

Read more about importing Metafields to Shopify using Excelify.

Removal of Customer Data

Shopify Remove Customer Personal Data

When you request from Shopify Admin to remove the Customer data, then the app will receive this request from Shopify and will remove all the exported, uploaded and imported files which were related to Customers or Orders.

When you delete the app, then all your files are deleted from the app history.

The data is kept only in your Shopify store.

Import from SFTP to Shopify

SFTP to Shopify

This is now to Import data from an SFTP server to Shopify

You can import to Shopify from SFTP by pointing the address exactly to the file which you need to import.

To schedule the import automatically at regular time intervals, do the following.

In the Excelify app, in the Import section:

  1. Paste the link to your SFTP server file path which should be in the following format:
sftp://user:[email protected]:port/path/to/directory/file-name.xlsx

For example:

sftp://foo:[email protected]:22/test/my-file.xlsx

2. Press “Upload from URL” buttonImport to Shopify from SFTP - upload file

3. Wait for the app to download the file for the first time, and let it analyze it (or fix any issues if it says that the URL was not correct).

4. If you want to set up the scheduled or automatically repeated import, press the “Options” button and set it up there.

5. Press the “Import” button (or “Schedule Import” button if you are scheduling import to start at a certain time).

Schedule automated import from SFTP to ShopifyEach time when the next automatic import will run, it will download the fresh file from SFTP again and again.

Good to know

Export from Shopify to SFTP server

Shopify to SFTP

This is how to Export from Shopify to an SFTP server

In the Excelify app, in the Export section:

  1. Choose what data you want to export with checkboxes.
  2. Press the “Options” button
  3. Select the scheduling and repeating options if you want to repeat the export automatically.
  4. On the “Upload to:” line, choose the SFTP.
  5. Enter the SFTP link in the following format:
sftp://user:[email protected]:port/path/to/directory/

For example:

sftp://foo:[email protected]:22/test/

Don’t put the file name or file extension in that path – as this is generated automatically.

If you want your file name to be custom, then fill the field “Custom file name:” in the Options section.

Export from Shopify to SFTP - Options

Good to know

Upload the Export to FTP directory

Shopify Export to FTP

You can make your Shopify export to send the exported file to an FTP directory.

That export can be scheduled and repeated at any needed time intervals.

The FTP address needs to be written in the following format:

ftp://user:[email protected]/path/to/directory/

If you need to have the specific file name for the exported file, you can set it in the Options field “Custom file name”.