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
  • Zero Value
  • Multiple Lightspeed payments per order
  • Tips
  • Refunds
• Locating a missing transaction

Before you begin

You would need:

• Access to Lightspeed Insights
• Access to Lightspeed Financial Services
• Access to a spreadsheet program e.g. Excel or Google sheets

What to reconcile?

First, understand the data required to perform troubleshooting.

Note:

When balancing the books between Lightspeed and your Accounting please remember that the reconciliation is always the day before your settlement.

Single Day

If your settlement is the 5th of Jan - you would go to:
Insights > Reports > Reconciliation and search the 4th.

Weekend

Transactions processed on Fridays, Saturdays & Sundays will be credited to your account on Monday. You'll receive a deposit for each days takings.

In this instance, for a Monday 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 Payouts found here:
Back Office > Financial Services > Payouts

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 do if data doesn't match? 

If your reconciliation doesn’t match, you’ll need to identify inconsistencies by comparing raw data. Discrepancies can arise from standalone payments, delayed processing, or other issues. Here’s how to proceed:

1. Download the Payments Export:

   • Navigate to Back Office > Financial Services > Payments.
   • Set the date range and click Export.
     

2. Download the Sales Feed:

   • Current Reporting: Go to Back Office > Home > Sales Feed, set the date range, filter by Lightspeed Payments, and download.
   • Legacy Reporting: Access via Back Office > Reports, then set the date range, filter by Lightspeed Payments, and download.
               

Comparing the reports

Use excel to correlate the reports from Settlement and POS.

1. Import both reports into excel sheets.
2. Sort each by the date in the Payments Export and Sales Feed respectively. 
3. Create a third working sheet where you can sort this out without changing the original data set.
4. From the Settlement Breakdown select and copy to new working sheet.

• OrderID
• Date
• 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.

• SaleID
• SaleDate
• 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 standalone 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. 

Zero Value

At times you will see that there is a zero in the OrderID column. Simply use the Created Time column to match the Sales Feed to the Payments Export 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.

If there is no matching timestamp in the Lightspeed Sales Feed, this may have been a standalone payment.

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.

As long as the total of the payments is correct it's not a concern.

Tips

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"

Refunds

Another reason for variances in your settlement reporting is Refunds (only when surcharging is enabled).

• The Salesfeed has a 11.90 refund.
• The Payments Export 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 standalone transaction was processed or an order was mismanaged on the POS. 
Here we have a payment that has an OrderID, but there is no matching Date in the SaleDate column for Sales Feed.

1. Select the impacted OrderID, and right click -> copy.

2. Go to Back Office -> Sales Feed then open any random invoice.

3. Look at the URL to find the numbers after id=

4. Highlight that number, delete it, then paste in your OrderID in it's place and press enter.

You should see the offending sale load up, this was somehow processed as cash instead on the POS even though it was processed on Lightspeed Payments.

(Note: The most common cause for this is the POS not registering the payment, users can get around this when it happens by simply going Checkout -> Lightspeed Payments again. As long as the payment was approved, and the order was not changed at all, the terminal will not request the card and retrieve the approved payment. Note this must be done within 5 minutes of the payment being done so this likely won't help you during a reporting investigation but can highlight a staff training opportunity).

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.