This is a brief guide on performing reconciliation between Lightspeed POS and Lightspeed Payments. We will cover what to reconcile and how to isolate variances to specific transaction events.
What’s in this guide:
- What to reconcile?
- What if doesn't match?
- Finding the data
- Comparing the reports
- Working out Variances
- Locating a missing transaction
Before you begin
You would need:
- Access to Back Office
- Access to a spreadsheet program e.g. Excel or Google sheets
What to reconcile?
First, understand the data required to perform troubleshooting.
When balancing the books between Lightspeed and your Accounting please remember that the reconciliation is always the day before your settlement.
If your settlement is the 5th of Jan - you would go to:
Insights > Reports > Reconciliation and search the 4th.
If a settlement is after a weekend then Friday through Sunday will be batched together. In this instance for a settlement on the 10th - search for the 7th UNTIL BEFORE the 10th of Jan
From here focus on the Payment summary for Lightspeed Payments
Compare this with the settlements found here
Insights > Lightspeed Payments > Settlement
Your total reconciliation should match the Amount column.
If you are using Surcharging remove the Surcharging total from Amount before beginning your reconciliation.
Note: If you have surcharging enabled there is a residual Net fee. This is caused by variance between the fees and the surcharge.
- The surcharge is calculated by the initial original amount from Lightspeed.
- The fee is calculated by the processor, based on actual paid amount. Original amount + Surcharge = Paid amount.
As a result there will always be a very small variance that may sometimes round up to no more than a few cents.
What if doesn't match?
How to find the data and compare to identify inconsistencies. Downloading raw data is a critical step in the data analysis step.
This is where we need to go for a deep dive. There are many instances that can cause a discrepancy such as manual payment or the delayed processing of a Transaction.
Download the Settlement Breakdown and the Sales feed for the day in question.
- Back office > Reports download the Sales Feed. (Filter by Lightspeed Payments)
- Insights > Lightspeed Payments > Settlement > Settlement Breakdown
Comparing the reports
Use excel to correlate the reports from Settlement and POS.
- Import both reports into excel sheets -
- Sort each by the View Sale Link and SaleID in the Breakdown and Sales Feed respectively.
- Create a third working sheet where you can sort this out without changing the original data set.
- From the Settlement Breakdown select and copy to new working sheet
- View Sale Link
- Created Time
- Net Amount (If you are not using Surcharging you can use the Amount column)
Important to note that if Surcharging then Amount when you're looking at INSIGHTS contains Surcharge.
5. From the Sales Feed select and copy to new working sheet
- Total (I would recommend creating a composite column of TIPS+TOTAL)
Scenario below highlights what can occur when TIPS are not included.
Now you can just differences - so the rudimentary approach I will provide is to create another column that compares Net Amount and Total
(This is at your own discretion - ultimately our goal is to match each transaction form the Sales feed to the Settlement Breakdown - how ever you achieve this is up to you)
If you use the method as prescribed and the columns balance it will display = 0. If there is a small variance it is likely a rounding event.
Working out variances
There are two leading situations that will result in variances:
Cannot locate transaction in the Sales Feed data - This could mean it was a manual payment or mismanaged on the POS. Perhaps an order was deleted or marked as cash. We have provided an example below.
Cannot locate transaction in the Settlement Breakdown data - E.g. A payment marked as Lightspeed Payments checkout where in fact it was not. However, if it was correctly managed at checkout and still not present in Settlement this is something that needs to be escalated to the Payment team via support.
Here a few other examples of variances that can arise when matching transaction data.
At times you will see that there is a zero in the View Sale Link column. Simply use the Created Time column to match the Sales Feed to the Settlement Breakdown columns. This can occur when there is an issue at time of processing leading the payment to be passed to the processor without the orderID.
Multiple Lightspeed payments per order
Another variance you will see is when there are two or more Lightspeed payments for one order in the Sales Feed. In the below example, you will see six payments in the Settlement breakdown for the one order in Sales feed. As long as the total of the payments is correct it's not a concern.
If there is a small variance and Sales IDs match it could mean there was a tip placed on the Lightspeed terminal. A tip won't be included in the SalesFeed total but it will be included in the payment if made by Lightspeed terminal.
Note: This can be highlighted by creating an additional column at Step 5 where we are importing Sales Feed into Google sheets. TIPS and TOTAL combined is only of interest if you are doing a lot of tips.
Below there is an example of what this might look like with "Total+TIP"
Another reason for variances in your settlement reporting is Refunds (only when surcharging is enabled).
- The Salesfeed has a 11.90 refund.
- The Settlement Breakdown has a 12.03 refund because it includes Surcharge.
This caused a 13c discrepancy
This is visible in the Settlement Breakdown because it had two line items and one has a negative value.
Also there was a negative in the Salesfeed without a corresponding order in Settlement Breakdown
To balance payments against the POS you must remove the negative value (-12.03) and replace it with the corresponding order (11.90) which is the true refund amount for the POS.
By doing this - you can match the POS to the transactions in Settlement Breakdown.
By discounting the Settlement Breakdown refund and substituting the Salesfeed refund it may indicate the customer refunded more than they charged. This is purely cosmetic.
The customer was refunded precisely what they were charged. 12.03
The issue is the POS Salesfeed doesn't display surcharge resulting in an imbalanced representation of the refund.
Locating a missing transaction
Locate a transaction in POS that may have been misrepresented in a settlement. E.g. a payment without an order number associated with it.
This can happen if a manual transaction was processed or an order was mismanaged on the POS.
1. Go back to Sales Feed - this time not searching for Lightspeed Payments but looking for all sales at the time provided. In this example, at 2022-02-17 21:58:56 you can see this order was entered as cash.
2. Look at the URL to find the order number "1330319527"
3. Compare it to the order in the sheet. It proves that this was in fact a payment processed through Lightspeed Payments.
What's my next step?
This is not a catch-all guide and there are many ways that a reconciliation can have variances. However, you will be able to isolate variances to specific transaction events and raise these to our support team for clarity.