Getting payment data out of Shopify using Orderwave

It’s no secret that Shopify is an immensely powerful and widely used ecommerce platform, but sometimes, even doing simple things, like a data extraction, isn’t super straightforward. Organizations large and small need data extracts for financial reporting, partner systems, ERPs, and more.

Let’s take payment data for example; if you want to export all of the refunds from the previous day, you’re going to have to program something in their API, or use a plugin from the Shopify App Store.

But, when you’re using an OMS like Orderwave, you have all of the data already — you just need to export it.

Orderwave makes it easy to export data — any data — not just payment data, but anything; like inventory, stock quantities, customer information, order/shipment data, etc.

There’s two ways to export data from Orderwave:

1. Export to Excel by using the User Interface.
   
   
2. Export to a text-based file (CSV, tab, XML, JSON, or something else) with an Automated Task.
   
   

Exporting to Excel

The easiest method to export payment data (or any data) is to export it to Excel. When viewing payments in Orderwave:

Simply click the ‘Export to Excel’ button:

This will perform the current search and produce an Excel file that you can immediately download from Orderwave.

If you need to customize the Excel document, Orderwave provides Excel Export Templates; these are templating tools to help you create the columns and formatting that you need in your Excel documents. These are located in the Admin area of Orderwave.

Excel Export Templates are text-based templates, that let you specify a ‘header row’ for your Excel file, plus the formatting of the Excel rows.

Simply separate each column of your header or data-template with a comma (“,”) to signify a cell in your spreadsheet. Next, use the Handlebars template language to put data in each column. You can even see some sample data on the right side of the screen, to help you with the different field names. The templates are flexible — if you want to hard-code a column, just put in the text. You can do conditional formatting too, by using {{#if something}} conditions that Handlebars gives you.

Once you’ve prepared an Excel Export Template, they’ll be available for all of your users to click on when they’re exporting data. Combine this with Orderwave’s powerful search features, and you and your team can export anything.

An Excel Export Template for exporting refunds from Shopify

Choose txn/refunds as the bucket to apply to this Excel Export Template.

For your headers, I’ve chosen to export the following (but feel free to customize your export): Billing Number,Transaction Date,Amount,Transaction ID,Prior Payment Amount,Reason Code

These fields are:

1. Billing Number = The unique billing record for each order
2. Transaction Date = The date and time that the refund occurred
3. Amount = The amount of the refund
4. Transaction ID = The unique transaction ID assigned by Shopify
5. Prior Payment Amount = The amount that was originally charged
6. Reason Code = The reason code that was used when refunding this payment

For my data-template, I’ll paste in the following: {{{billingNumber}}},{{{transactionDate}}},{{{amount}}},{{{transactionId}}},{{{payment.amount}}},{{{refundType.reasonCode}}}

Each of the above, separated by a comma, is an individual Handlebars template. The fields correspond to the headers I specified above.

Fields:

1. {{{billingNumber}}} = The unique billing record for each order
2. {{{transactionDate}}} = The date and time that the refund occurred
3. {{{amount}}} = The amount of the refund
4. {{{transactionId}}} = The unique transaction ID assigned by Shopify
5. {{{payment.amount}}} = The amount of the original transaction. I’m referencing a sub-object in the Orderwave JSON to pull the amount from the payment sub-object.
6. {{{refundType.reasonCode}}} = The reason for the refund. I’m pulling the reasonCode from the refundType sub-object.

When I save my template, it’s now available on my Refunds screen in Orderwave:

Automating Exports with Automated Tasks

Exporting to Excel is certainly convenient, especially for quick analysis, but for sending data to other systems, you really need a programmatic process for identifying data to export, queuing it for export, transform the data, and push it to a secure SFTP server for another system to retrieve. You may also want to push the data to an API or Web Service, which I’ll cover in another post.

For this, I’ll be introducing a few of concepts in Orderwave:

Queues

Queues are buckets that you can put data into, for either queuing it for some automated process, or for holding it, or for your team members to work with later. In this case, we’ll be using a queue to stage up refunds for export.

Rules

Orderwave provides you with a business-rules engine that works on any of the data-buckets in Orderwave. Rules let you specify simple or complex “if this, then that” rules on your data.

Data Transformation Templates

Similar to Excel Export Templates, Data Transformation Templates help you transform data from Orderwave’s data-formats to the format that you need.

File Transfer Locations

Configurations of remote SFTP (or FTP) servers.

Automated Tasks

The jobs that run on a predetermined schedule.

First, we will need to create a Queue. This queue will help us identify refunds that need to be exported. I’ll navigate to Settings > Queues, and create the following queue:

Next, I’ll specify some special settings for this queue:

I want to designate that this is a Process Queue, meaning that it is a queue that is used for behind-the-scenes data processing. It’s also set as Only Once, meaning that I only ever want a refund to be queued in this queue one time. This way, I know that I won’t ever accidently export the refund more than once. And finally, I’ll also turn on Hidden, so that my team members don’t see this queue on the various refund screens.

Refunds are not set up as “queueable” by default in Orderwave, so I won’t see it on this screen when I’ve saved my new queue:

But that’s not a problem — these are just tags on the queue, so we can add it ourselves. I click Set Tags and add the “Refund” tag myself:

Now I can apply that tag to my new queue:

Great — step one is done — I have a queue to place refunds into. Now, I just need to get Orderwave to do that for me automatically. For that, I’ll use Rules.

Navigate to Admin > Rules to create a new rule:

It’s important to fill out these fields with accurate and detailed information about what the rule does. You may not always be the one person administering Orderwave for your company, and you will want to leave well-documented configuration for the next person who needs to look at all of these queues, rules, tasks, and more, and figure out what they do.

Next, we need to configure the actual business logic for this rule. Remember that we’re queuing up every refund to be exported, so this rule should apply to all refunds. We’ll use “created” as the trigger, and there’s a simple trick in Orderwave to apply the rule to everything — just specify that the id should be greater than 0. This is because every bucket in Orderwave gets an automatically generated, highly unique numeric “id”:

Next, our action is simple too. We just want to add the refund to our new queue that we just created:

We save and activate this rule, and now we have custom, programmatic logic that:

1. Evaluates every new refund that occurs
2. Queues it to be exported, knowing that we’ll never queue a refund to be exported more than once

We’re really getting somewhere! Now we’ve got the refund data all set to be exported, and we just need to set up the transformation of the data, set up the place to export it to, and configure the scheduled task to run it nightly.

Data Transformation Templates

Orderwave includes a system by which you can transform the data you export from Orderwave. Using Handlebars syntax, you can build extremely simple or wildly complex templates that export data to any number of formats, including CSV files, Tab delimited files, XML files, JSON, EDI, or more.

In this case, I just want to produce a tab-delimited file for my back-office financial systems to consume. The file will look something like this (where \t represents a “tab” character):

The file contains the date of the refund, the amount of the refund, the billing identifier from the order, and the refund reason code.

I navigate to Admin > Data Transformation Templates, and create a new template:

When designing our template, Orderwave gives us some sample JSON data on the right-side of the screen to help us create our Handlebars template. This is because Handlebars templates work with JSON-like data:

Orderwave has pre-filled the Data template area with a sample “loop” — it {{#each refunds}}, meaning that it will loop through each refund inside of that “each” block. I’m just going to change it so that it matches the format that I need for a tab delimited file, so I don’t want extra line breaks, and I want to use a special Orderwave helper for formatting data that is in a tab-delimited file: {{#each refunds}}{{dateFormat transactionDate 'YYYY-MM-DD'}} {{{tabFormatter amount}}} {{{tabFormatter billingNumber}}} {{{tabFormatter refundType.reasonCode}}} {{/each}}

Let’s explain the syntax above.

The template starts with {{#each refunds}}, which will make the template loop over each refund. Next, we’re using the Orderwave helper tag {{dateFormat transactionDate 'YYYY-MM-DD'}} to output the transactionDate in the format of 2021-11-22. After that, we’re using the Orderwave helper tag {{{tabFormatter -field-name-}}} to output the rest of the fields. Notice the 3 opening and closing curly braces — we don’t want our template to HTML encode the data for our tab-delimited file. Finally, there’s a single line break at the end before the closing {{/each}} which closes the loop. I’ve also included a literal tab character in between each field, so that they will be used as our delimiter.

Save your template, and we can move on to creating an File Transfer Location.

File Transfer Location

We need to send this data file somewhere, and for this example, I’d like to post it to my company-managed SFTP server.

Navigate to Admin > Final Transfer Locations and create a new one:

Orderwave only makes you set up a file server once. The folder where we’re going to push these files will be configured in the Automated Task, so be sure to just leave the “root” of the server as the connection host.

Automated Task

Now we’ve got all of the pieces to this puzzle, and we just need to tie them all together. We’ll do that in the actual Automated Task that performs this export.

Navigate to Admin > Automated Tasks and create a new task:

I want my export to run once each night, right after midnight, so I configured it as above.

Next, we need to tell Orderwave what type of task this is, and it is an Export job:

Orderwave breaks down export jobs into 3 sections:

1. What data to get
2. How to transform it
3. Where to send it

For “what data to get”, we specify the txn/refunds bucket, and pull the refunds that are in the “REFUND-EXPORT” queue.

Queues can be set up as “blocking queue” — basically “holding” the record from automated processes, such as exports. So, any refund that is also in a blocking-queue will not be exported.

I specify a file naming convention for the file, and choose my Data Transformation Template that we just created to transform the data to a file.

Finally, I configure that I want to send the data as a file to the SFTP server, in the refunds/ folder. If the file transfer is successful, I also tell Orderwave to remove the refunds from the “REFUND-EXPORT” queue, so that they will not be exported on the next run.

Save and enable this automated task, and you now have a fully customized, programmatic, and fault-tolerant export process for all of your refunds.

Start exporting data like a pro

If you’re interested in streamlining your ecommerce data-management processes (and lots of other things), consider using Orderwave for your OMS. We provide a slick online returns portal out-of-the-box, and tons of other features that supercharge your operations. Reach out to us today to see a demo, or check out a quick video introduction to see what we can do.