Analytics can be used to prepare data for further analysis in programs such as Excel or Numbers. Maximize data preparation within Analytics to facilitate the next steps.

Understanding Payment Methods and Categories

Sometimes there are 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, for example:

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 a Credit Card.

If there are multiple categories and multiple products, there is no way to tell what payment methods are associated with which products.

However, there are a few scenarios where it's easier to track:

• 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 because there's either parity between payment methods and categories or an even distribution of one or the 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.

Instead of using a template, you can:

1. Export your data from Analytics.
2. Open the exported data in a spreadsheet software such as Excel.
3. Create 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 you can start with.

Understanding the logic of multiple Payment Methods and Categories

You can take the total funds and determine the percentage that each method contributed to the total. For example:

• 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 a Credit Card.
• The Cash made up $60 of $150 = 40%
• The Credit Card made up $90 of the $150 = 60%

So you can then decide that:

• 40% of the Hat was paid for by Cash
• 60% was paid for by Credit Card, likewise for the Suit.

This way, you can read the payments as:

• $20 Cash for the Hat
• $30 Credit Card for the Hat
• $40 Cash for the Suit
• $60 Credit Card for the Suit

Although this might be different than what occurred, it works, and is enough for you to start building a simple working model. In addition to this, you can use Analytics to start preparing the data.

Defining Payment Percentages per Invoice

To determine how much each payment contributed to each invoice:

1. Start by opening up the Sale Payments report.
2. The default dimension is by Sale Date, so start by adding Sale ID as a dimension.

   

3. Remove the dimension of Sale Date, because it's not required in this analysis.

   

4. The default report is looking at the previous complete month (one month). To ensure your report isn't too big, open the filters and switch the previous one month to the previous one complete week.

   

5. Since you don't need the payment amount, all you need here is the percentage that each payment made towards the total. So, add a calculation to divide the payment amount by the total amount of the payment amount.

   

   

6. Click Save.

This will give you a calculation that mostly returns the number 1.

You may find some instances where the payment is broken up across columns.

1. Go back to your calculation and define it as a percentage with four decimals.

   

2. Rename the title, for example: Distribution.
   

   

3. Save your working Payments Report.

   

4. Finally, you don't need to see the value of payments on the report here, so hide the amount column.

   

5. You'll have a visualized report that shows you, by Sale ID, only the percentage of payment that each method provided.
   

   

6. Click Save As New.

   

7. Then download your report as an Excel file.

   

Defining Categories per Invoice

Now you can view the payment methods on each Invoice. You can run a similar Sales report to show Top-Level Categories by Invoice as well. To start:

1. Open up the Recent Sales report.

   

2. Add dimensions, starting with the Top Level Category.

   

3. Next, add the Sale ID.

   

4. Remove the Sale Date field.

   

5. Make sure your date filters are set to: is in the past, 1, and complete weeks to obtain sales from the past 1 complete week.

   

6. When you run this report, you'll notice some sales have multiple Top-Level Categories.

   

7. Enter a Title and Description and Save As New.

   

8. Download the report as an Excel file.

   

Mapping Payment Values to Category Amounts

After completing the previous steps, you can map payment values to category amounts:

1. Open your Payments and Categories reports in Excel.
   • The Payments report lists each Method per Invoice as a Column.
     

   • The Categories report lists each Category per Invoice as a separate line.

     

2. To get the data on one sheet, copy the columns from the Category report.

   

3. Then, paste the columns on the Payments report to the right of all the Payment data.

   

4. Optionally, you can change the colors to make it easier to visualize.

   

5. Next, to determine how much of each Category's total was assigned to a certain payment method, you need to match and index each Category to its respective distribution of methods. Start by using the Excel Match function to find where the Category's Sale ID can be found in the list of payments.
   For example: On this Excel sheet, the Category Sale ID is in Column N, the Payment Sale ID is in Column B, so the Excel formula in cell R2 should be: =MATCH(N2,B:B,0)
   The corresponding Sale ID is in Row 3.

   

6. Drag or copy and paste the formula to the bottom of the category data.

   

7. Next, Index from each of the payment columns the corresponding percentage. You need a different column for each payment method, so in Cell T2, use the function:=INDEX(C:C,$R2)
   

   

   This way, when you 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 you drag the formula to the end of the data, you'll get a corresponding percentage value for each Category on each Invoice.

   

8. Now, you want to multiply each category's total against its respective payment amounts. Most of the payment amounts on the list you'll see are 100% or 0, and some will be split payments.
   Create one final calculation, this time in column AE, multiplying the Value in Column P by the percentages, starting with column T
   The Excel formula in cell AE2 is:=$P2*T2

   

9. This way, when you drag the formula over, it will follow the dollar amount from Column P, and apply the percentage amount from all the payment columns: T-AB
   

   

10. Now, to finish preparing your data, reference the Product Categories you can find in Column O.

    

11. Now, you have a full set of data for Categories with Payment Methods distributed proportionally among them per sale, you can complete the analysis using a pivot table or a sumif function.
    

    

    

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 are not necessarily limited to "Sales" transactions.