Skip to main content

How to reconcile gift card balances on Shopify

Daniel Little avatar
Written by Daniel Little
Updated over 3 weeks ago

  1. In Shopify Admin navigate to Analytics > Reports

  2. Finances - Gift cards balance report

  3. Choose a date period

  4. Export report

Once the report is exported open it in something like Excel and sum up each of the columns to see the total values from that period.

Columns corresponding to Link My Books data include:

Sold value - shows the total amount of gift cards that have been sold.

Redeemed value - shows the total amount of gift cards that have been redeemed.

Refunded value - shows the total amount of gift cards that have been refunded.

If you sum up the total of all 3 of these columns and compare that figure to the figure you have showing in the data sent over from Link My Books to your bookkeeping system they should match.

How to compare with the figures from Link My Books

To download the Link My Books settlement entries in bulk to a CSV file follow these steps:

From the Settlement Dashboard, click the Download icon at the top right:

This screenshot focuses on a section of a user interface, likely from an application managing data exports or transfers. Two identical entries are partially visible, each with a "Status" indicated by a blue dot and the text "Ready to send." To the right of each status, there is a blue "ACTION" button with a dropdown arrow. In the upper right corner of the image, a download icon is prominently displayed, and a thick red arrow points directly from the left towards this download icon, suggesting an action related to downloading or exporting data.

Select a date range you wish to download and click Download to CSV:

This screenshot displays a "Download settlement data" interface, likely from a financial or e-commerce platform, which allows users to export data to a CSV file. At the top left, a download icon is visible next to the title. The main text explains the process: "Download settlement data to a CSV file. Choose a start and end date and click the download button to download settlement data to a CSV. You can then open this file in a program such as Excel." On the right side, there are two input fields for selecting the date range: "Start Date," which is currently set to "Oct 2023," and "End Date," currently set to "Dec 2023." Both date fields have a calendar icon next to them. At the bottom, a blue button reads "DOWNLOAD TO CSV."

The file may take some time to generate depending on the size of your account and the selected date range.

Apply a filter to the description column for Shopify Gift Card and that will reveal all the sales, refunds and redemptions of gift cards.

Sum up the amount column and then compare that number to the figure you obtained from Shopify.

How to compare with the figures in your bookkeeping software

If you're using our default account mappings then the sales, refunds and use of gift cards will be accounted for under the nominal accounts:

LMB30: Shopify Sales

LMB31: Shopify Refunds

To make it easier for you to reconcile your gift card figures directly in your bookkeeping system we'd suggest setting a custom account mapping for gift cards like this:

This screenshot displays the "Account Taxes" configuration page within an accounting integration tool, providing granular control over how different transaction categories are mapped to specific accounts. The introductory text advises that this page is for fine-tuning initial choices and strongly recommends against changing tax rates here, instead suggesting the "Account & Taxes Wizard." The page is divided into two main sections: "Shopify Sales" and "Shopify Refunds," both listed as "Transaction category." Within each category, various sub-transaction types are presented, alongside their corresponding "Account Name" and "Tax Rate" fields (though the Tax Rate column is not fully visible in this image). Two specific rows are highlighted with red rectangular outlines: 1. Under "Shopify Sales," the "Shopify Gift Card" line is highlighted. It shows an "Account Name" of "205: Gift Card Liability (Explicit)." 2. Under "Shopify Refunds," the "Refunded Shopify Gift Card" line is highlighted. It also shows an "Account Name" of "205: Gift Card Liability (Explicit)." Other visible transaction types include "Principal Discount" and "Principal" under sales, and "Refunded Principal Discount" and "Other Refund" under refunds, each mapped to different "400.x" or "200.x" account names. The overall interface allows users to precisely define which general ledger accounts are affected by various Shopify transactions, particularly highlighting the treatment of gift cards as a liability.

This way the sales, refunds and use of gift cards will be accounted for under this one account and they will be separated from your general sales.

If you've already sent data from Link My Books to your bookkeeping system and you want to retrospectively make this change then you can do so by first updating your mappings via the Accounts & Taxes Mapping page and then afterwards using our rollback feature to roll back settlements and then re-send them.

If you have any questions about this article or feedback on how we could make it better please reach out to the support team via the blue chat icon on the bottom right of the page or via email to [email protected].

Related Articles
How to reconcile Shopify sales paid via PayPal
Reconciling Shopify Sales & Fees - Other payment gateways
How to reconcile Shopify sales paid via Klarna
Payout analysis tab - what is it and how to read the data
Shopify Gift Cards
Did this answer your question?