How can we help?

Lightspeed’s Help Centre is here to provide support, tips and troubleshooting

Troubleshooting your Lightspeed Payments reconciliation


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:


 

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.

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.

Screen_Shot_2022-02-24_at_3.57.20_pm.png

Weekend

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  

Screen_Shot_2022-02-24_at_3.58.03_pm.png

From here focus on the Payment summary for Lightspeed Payments

Screen_Shot_2022-02-24_at_4.04.21_pm.png

 

Compare this with the settlements found here
Insights > Lightspeed Payments > Settlement

Screen_Shot_2022-02-24_at_4.59.37_pm.png

 

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

Screen_Shot_2022-02-24_at_4.09.14_pm.png       Screen_Shot_2022-02-24_at_4.09.50_pm.png

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 View Sale Link and SaleID in the Breakdown 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
  • 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

  • SaleID
  • SaleDate
  • Total (I would recommend creating a composite column of TIPS+TOTAL)
    Scenario below highlights what can occur when TIPS are not included.

Screen_Shot_2022-02-24_at_4.15.12_pm.png

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)

Screen_Shot_2022-02-24_at_4.16.17_pm.png

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. 

 

Zero Value

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.

Screen_Shot_2022-02-24_at_4.21.17_pm.png

Screen_Shot_2022-02-24_at_4.21.59_pm.png

 

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.

Screen_Shot_2022-02-24_at_4.25.12_pm.png

 

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.



Screen_Shot_2022-02-25_at_9.10.46_am.png

 

Screen_Shot_2022-02-25_at_9.24.41_am.png

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"

Screen_Shot_2022-03-11_at_7.50.25_am.png

 

Refunds

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.

Screen_Shot_2022-02-24_at_4.33.59_pm.png

2. Look at the URL to find the order number "1330319527"

Screen_Shot_2022-02-24_at_4.34.54_pm.png

3. Compare it to the order in the sheet. It proves that this was in fact a payment processed through Lightspeed Payments.

Screen_Shot_2022-02-24_at_5.04.16_pm.png

 

 


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.

 

 

 

Was this article helpful?
1 out of 3 found this helpful