Analytics Calculations: Matching Product Data to Payment Methods
josephmckeown
moderatorPosts: 54Moderator, Lightspeed Staff moderator
Sometimes Analytics may be used to prepare data for further analysis in programs such as Excel or Numbers. This section of discussions touches on ways to maximize data-preparation within Analytics to facilitate the next steps
Sometimes we have received requests for Analytics to report on what payment methods were used on specific Product Categories or specific Products. This type of analysis is not completely possible, because of the following occurrence:
If there are multiple categories and multiple products, there is no way to tell what payment methods were associated with which products.
However, there are a few easier scenarios:
These scenarios are all easy to account for as there is either parity between payment methods and
categories, or an even distribution of one or other.
Because not every sale will be one of the easier scenarios, Analytics cannot completely determine which payment methods were associated with which products, and for that reason, the template for such a report does not exist.
But we could export our data from Analytics, open it in a spreadsheet software such as Excel, and then come up with a logical rule to distribute funds received by payment method across their respective categories.
This will involve coming up with a logical rule for distributing values from multiple payments across multiple categories.
Fortunately, there is a simple one that we can start with.
We could take the total funds and determine the percentage that each method contributed to the total.
For example.
In the above model, the payment methods totalled $150.
The Cash made up 60 of 150 dollars, or 40%
The Credit Card made up 90 of the 150 dollars, or 60%
So we could then decide that 40% of the Hat was paid for by Cash, and 60% was paid for by Credit Card, likewise for the Suit.
This way, we could read the payments as such:
$20 Cash for the Hat
$30 Credit Card for the Hat
$40 Cash for the Suit
$60 Credit Card for the Suit
Is it definitely what happened? No. But it works, and is enough for us to start building a simple working model.
In addition to this, we can use Analytics to start preparing our data.
To determine how much each payment contributed to each invoice, let's start by opening up the Sale Payments report
The default dimension is by Sale Date, so let's start by adding "Sale ID" as a dimension...
...and let's also drop the dimension of Sale Date, this will be of no value in our analysis...
...also, the default report is looking at the previous 1 complete month. Just so that our report isn't enormous, let's open the filters and switch this up to being the previous 1 complete week...
Now, we're not actually interested in the payment amount, all we need here is the percentage that each payment made towards the total.
So, we could add a calculation to divide the payment amount...
...by the row total of the payment amount...
and saving...
This will give us a calculation that mostly returns the number 1...
but we may find some instances where the payment is broken up across columns...
Let's go back to our calculation, and perhaps define it as being a percentage with four decimals...
...I'm also giving it a title of "Distribution"
And let's save our working Payments Report...
Finally, we don't need to see the value of payments on the report here, so let's hide the amount column...
So we have a visualized report that shows us by Sale ID only the percentage of payment that each method provided...
Let's save again as new...
...and then download our report as an Excel file...
So now we can see the payment methods on each Invoice, we can run a similar Sales report to show us Top-Level Categories by Invoice as well. To start, let's open up the Recent Sales report...
And let's add a few dimensions, first, the Item Top Level Category...
...and then the Sale ID...
...and we can also get rid of the "Sale Date" field...
Also, let's make sure our date filter matches: Sales from the Past 1 complete week...
So when we run this report, we can see some sales have multiple Top-Level Categories on them...
So this is ready now, let's Save this with a custom title...
and again, we can download it as an Excel file...
So now, we can open our "Payments" and "Categories" reports in Excel...
Our Payments Report lists each Method per Invoice as a Column...
and our Categories report lists each Category per Invoice as its own line...
First, we want all the data on one sheet, so I'm going to copy the columns from the "Category" report...
...then paste them all on the "Payments" report to the right of all our Payment data...
...Sometimes adding a bit of colour makes things easier...
...So now, we want to determine how much of each Category's total was assigned to a certain payment method...
To do this, we need to match+index each Category to its respective distribution of methods...
Let's start by using the Excel "Match" function to find where the Category's Sale ID can be found in the list of payments.
On my excel sheet, the "Category" Sale ID is in Column N, the "Payment" Sale ID is in Column B, so my Excel formula looks like this in cell R2:
...and it shows me that the corresponding Sale ID is in Row 3...
...so far so good, let's drag or copy+paste the formula all the way to the bottom of the category data...
and it's looking good.
Next, we want to Index from each of the payment columns the corresponding percentage. We will want a different column for each payment method, so in Cell T2, I will use the following function
This way, when I drag the formula to the right for each payment method, the payment method column will change, but the Index source (Column R), will stay locked in place.
So when we drag the formula to the end of the data, we get a corresponding percentage value for each Category on each Invoice...
We're almost done!
Now, what we want to do is multiply each category's total against its respective payment amounts.
Most of the payment amounts we see are 100% or 0, although we're seeing some split payments on the list.
So we'll create one final calculation, this time in column AE, multiplying the Value in Column P by the percentages, starting with column T
So, my Excel formula in cell AE2 is
This way, when I drag my formula over, it will follow the dollar amount from Column P, and apply the percentage amount from all the payment columns: T-AB
Like so...
So now, to finish preparing our data, all we need to do is reference our Product Categories one final time...
That we can find in Column O...
So now, we have a full set of data for Categories with Payment Methods distributed proportionally among them per sale, we can complete our analysis using a pivot table...
a sumif function...
Or any other way you wish!
Discerning minds may notice that the totals from the Payments and Sales reports may not match. This does not necessarily mean that your math is incorrect. Keep in mind that the "Payments" reports will include values applied to Credit Accounts, and is not necessarily limited to "Sales" transactions.
Calculations Home
Part One: Thinking about Payment Methods and Categories
Sometimes we have received requests for Analytics to report on what payment methods were used on specific Product Categories or specific Products. This type of analysis is not completely possible, because of the following occurrence:
A Customer purchased a suit for $100, and a hat for $50. The customer's total was $150. The customer gave the store $60 in Cash, and then paid the remaining $90 on Credit Card.
If there are multiple categories and multiple products, there is no way to tell what payment methods were associated with which products.
However, there are a few easier scenarios:
- If one product was purchased and one payment method was used.
- If products in five categories were purchased and one payment method was used.
- If multiple products in one category were purchased and multiple payment methods were used.
These scenarios are all easy to account for as there is either parity between payment methods and
categories, or an even distribution of one or other.
Because not every sale will be one of the easier scenarios, Analytics cannot completely determine which payment methods were associated with which products, and for that reason, the template for such a report does not exist.
But we could export our data from Analytics, open it in a spreadsheet software such as Excel, and then come up with a logical rule to distribute funds received by payment method across their respective categories.
This will involve coming up with a logical rule for distributing values from multiple payments across multiple categories.
Fortunately, there is a simple one that we can start with.
Part Two: Thinking about logic of multiple Payment Methods and Categories
We could take the total funds and determine the percentage that each method contributed to the total.
For example.
In the above model, the payment methods totalled $150.
The Cash made up 60 of 150 dollars, or 40%
The Credit Card made up 90 of the 150 dollars, or 60%
So we could then decide that 40% of the Hat was paid for by Cash, and 60% was paid for by Credit Card, likewise for the Suit.
This way, we could read the payments as such:
$20 Cash for the Hat
$30 Credit Card for the Hat
$40 Cash for the Suit
$60 Credit Card for the Suit
Is it definitely what happened? No. But it works, and is enough for us to start building a simple working model.
In addition to this, we can use Analytics to start preparing our data.
Part Three: Defining Payment Percentages per Invoice
To determine how much each payment contributed to each invoice, let's start by opening up the Sale Payments report
The default dimension is by Sale Date, so let's start by adding "Sale ID" as a dimension...
...and let's also drop the dimension of Sale Date, this will be of no value in our analysis...
...also, the default report is looking at the previous 1 complete month. Just so that our report isn't enormous, let's open the filters and switch this up to being the previous 1 complete week...
Now, we're not actually interested in the payment amount, all we need here is the percentage that each payment made towards the total.
So, we could add a calculation to divide the payment amount...
...by the row total of the payment amount...
and saving...
This will give us a calculation that mostly returns the number 1...
but we may find some instances where the payment is broken up across columns...
Let's go back to our calculation, and perhaps define it as being a percentage with four decimals...
...I'm also giving it a title of "Distribution"
And let's save our working Payments Report...
Finally, we don't need to see the value of payments on the report here, so let's hide the amount column...
So we have a visualized report that shows us by Sale ID only the percentage of payment that each method provided...
Let's save again as new...
...and then download our report as an Excel file...
Part Four: Defining Categories per Invoice
So now we can see the payment methods on each Invoice, we can run a similar Sales report to show us Top-Level Categories by Invoice as well. To start, let's open up the Recent Sales report...
And let's add a few dimensions, first, the Item Top Level Category...
...and then the Sale ID...
...and we can also get rid of the "Sale Date" field...
Also, let's make sure our date filter matches: Sales from the Past 1 complete week...
So when we run this report, we can see some sales have multiple Top-Level Categories on them...
So this is ready now, let's Save this with a custom title...
and again, we can download it as an Excel file...
Part Five: Mapping Payment Values to Category Amounts
So now, we can open our "Payments" and "Categories" reports in Excel...
Our Payments Report lists each Method per Invoice as a Column...
and our Categories report lists each Category per Invoice as its own line...
First, we want all the data on one sheet, so I'm going to copy the columns from the "Category" report...
...then paste them all on the "Payments" report to the right of all our Payment data...
...Sometimes adding a bit of colour makes things easier...
...So now, we want to determine how much of each Category's total was assigned to a certain payment method...
To do this, we need to match+index each Category to its respective distribution of methods...
Let's start by using the Excel "Match" function to find where the Category's Sale ID can be found in the list of payments.
On my excel sheet, the "Category" Sale ID is in Column N, the "Payment" Sale ID is in Column B, so my Excel formula looks like this in cell R2:
=MATCH(N2,B:B,0)
...and it shows me that the corresponding Sale ID is in Row 3...
...so far so good, let's drag or copy+paste the formula all the way to the bottom of the category data...
and it's looking good.
Next, we want to Index from each of the payment columns the corresponding percentage. We will want a different column for each payment method, so in Cell T2, I will use the following function
=INDEX(C:C,$R2)
This way, when I drag the formula to the right for each payment method, the payment method column will change, but the Index source (Column R), will stay locked in place.
So when we drag the formula to the end of the data, we get a corresponding percentage value for each Category on each Invoice...
We're almost done!
Now, what we want to do is multiply each category's total against its respective payment amounts.
Most of the payment amounts we see are 100% or 0, although we're seeing some split payments on the list.
So we'll create one final calculation, this time in column AE, multiplying the Value in Column P by the percentages, starting with column T
So, my Excel formula in cell AE2 is
=$P2*T2
This way, when I drag my formula over, it will follow the dollar amount from Column P, and apply the percentage amount from all the payment columns: T-AB
Like so...
So now, to finish preparing our data, all we need to do is reference our Product Categories one final time...
That we can find in Column O...
So now, we have a full set of data for Categories with Payment Methods distributed proportionally among them per sale, we can complete our analysis using a pivot table...
a sumif function...
Or any other way you wish!
Part Six: A note on "Payment" and "Sales" Totals
Discerning minds may notice that the totals from the Payments and Sales reports may not match. This does not necessarily mean that your math is incorrect. Keep in mind that the "Payments" reports will include values applied to Credit Accounts, and is not necessarily limited to "Sales" transactions.
Calculations Home
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Lightspeed Analytics and reporting consultant
Lightspeed HQ