In Shopify Admin navigate to Analytics > Reports
Finances - Gift cards balance report
Choose a date period
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:
Select a date range you wish to download and click 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.
If the totals do not match
If your Shopify figure and your Link My Books figure do not tie up, work through the checks below in order.
1. Check whether the orders were paid fully by gift card
Orders paid entirely with a gift card are separated into their own payment gateway called gift_card_only, which generates its own settlement. Orders paid partly with a gift card are included in the settlement for the gateway that took the rest of the payment (for example Shopify Payments or PayPal).
This means that if you are comparing a single settlement against a whole-period Shopify report, the two figures will not match, because the gift card activity is spread across more than one settlement. Make sure you are including every gateway for the period, including gift_card_only.
For more on how gift card sales and redemptions are treated, see Shopify Gift Cards.
2. Trace the difference back to an individual order
The quickest way to find what is causing a difference is to work back from the audit data to the original order in Shopify.
From your Settlement Dashboard, click the Download icon at the top right and select Download Audit Data.
Set the date range to match the settlement period you are querying and download to CSV.
Open the CSV and find the gift card lines. Each line has an order number against it.
Paste that order number straight into the Shopify search bar. It will pull up the order the line relates to.
Compare the order in Shopify against how the line appears in Link My Books.
This also tells you whether a given line is a gift card sale or a redemption, which is not always obvious from the settlement summary alone.
Full steps for the audit download are in Download Settlement and Cost of Goods Sold (COGS) Data to CSV.
3. Check both reports cover the same period
Settlement periods do not always line up with calendar dates, so set your Shopify report to the exact start and end dates of the settlement rather than a full week or month.
đĄ Still not matching? Send our support team the settlement ID, the date range, and a screenshot of the Shopify order you traced in step 2. That gives us enough to investigate the specific order rather than the totals.
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 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.