Reporting on sales containing bundles

When selling bundles, reporting on their sales can be complicated as the cost and revenue are split out between the bundle parents and the components, especially if the components are also sold separately. This article will guide you through how bundles appear on the sales reports, and describe some techniques to use to analyze the reports.

Bundles on the sales by product report

When a bundle sells on a sale, the sales by product report will list the bundle parent and the bundle's components separately.

mceclip0.png

All of the revenue from the sale will be recorded against the bundle parent, while all of the cost from the sale will be recorded against the bundle components.

What this means is that the bundle parent will display a 100% profit margin while the components will display a 100% loss. While the overall gross profit is correct, looking at the individual rows may not provide useful data, particularly if the components are also sold individually.

Analyzing bundle sales using sales by product

In order to pull meaningful line-by-line data about your bundle sales it is necessary to do some analysis using a spreadsheet.

The cost related to the components must be split out and attributed to the bundle parent.

This can be done by determining what proportion of the component sales is due to bundles and what proportion is due to the components being sold individually.

In order to do so, the following steps can be followed:

  1. Multiply the bundle's # items sold by the number of units of the component(s) in one bundle. This will give you the total number of units sold related to the bundle.
  2. Divide the number from Step 1 by the # items sold by the relevant component. This will give you the relative proportion of units sold in the bundle compared to the total number of units sold.
  3. Multiply the number from Step 2 and the component's cost.

This will give you a number representing the cost figure attributable to the bundle (on average).

Repeat the steps for the other bundle components and sum up all of the costs to get the total cost attributable to the bundle.

Example

Take the following sales data pertaining to a bundle containing 1 x Product A and 2 x Product B.

mceclip2.png

We can see 4 bundles sold, but the quantities of the components indicate that not all of the component sales were related to the bundle. So in order to work out the bundle profitability, come calculations are needed:

  Bundle Quantity Proportion related to bundle Cost attributable to bundle
Product A 4
(4 × 1)
0.44
(4 ÷ 9)
4
(0.44 × 9)
Product B 8
(4 × 2)

0.5
(8 ÷ 16)

16
(0.5 × 32)
Total bundle cost 20
Net bundle revenue 48
Gross profit 28

Note: The bundle cost calculated from this method is an average! If the components have sold at different costs and you want a more precise figure, you may want to use the sales detail report.

This method will also work if if bundles share multiple components.

Analyzing bundle sales using the sales detail report

For a more granular method of working out bundle costs, you can use the sales detail report under Reports > Sales > Sales detail. This method will require some prior set up in order to be able to filter the sales detail report effectively. It will also require knowledge of how to use some basic spreadsheet functions.

Setting up the bundles

The very first thing you will need to do is ensure all bundle parents share a common product attribute - the most appropriate attributes would be a category or a custom field.

Bundle components should also share a common product attribute (separate to their parents).

The product attribute can be imported against the bundle parents and components using a product import, or the products can be updated individually.

mceclip3.png

Filtering the sales detail report

Once the bundles and their components have had the appropriate attribute assigned to them, you can either filter the sales detail report by that attribute, or include the attribute in a custom column.

You should also include the 'Row cost' and 'Row net' columns.

Producing the report

Please note the below steps are a general guideline to how to approach building a report - specifics may  vary depending on your individual setup.

Step 1: Identify what sales contain the bundle parents.

Either filter the sales detail report by the bundle parent attribute, or export the entire sales detail report and filter based on the attribute column.

mceclip2.png

This will give you a list of all sales containing bundles:

mceclip3.png

Step 2: Identify the sales containing the components related to bundles.

Either filter the sales detail report by the bundle component attribute, or use your export to filter only on bundle components.

Now use the list you obtained in Step 1 to identify only the sales containing bundle components. You can use a formula such as VLOOKUP or MATCHIF to do so.

Tip: You can automatically get rid of any component rows which have an amount in the 'Row Net' column - even if they're used in bundles, a price in that column means they were sold outside of a bundle!

This will give you a list of all bundle components and their costs.

mceclip4.png

Step 3: Allocate the component costs to a bundle

You now have a list of all bundle parents and the orders they're present on, and all bundle components on those orders. Now it's a question of combining the lists and summarising them.

One technique for combining the lists may be to use a list of your bundle parents and their components. You would do this using a list of all of your bundles and their components, which would be formatted along these lines:

mceclip5.png

Then, using similar technique to Step 2, use this list alongside a formula such as VLOOKUP or MATCHIF to match assign each component row with the relevant bundle parent SKU.

Warning: In the event where components are in multiple bundles, you will need to use some extra logic to ensure the right parent is allocated to the component rows, for example by using the IF function.

You should end up with a file along the lines of:

mceclip6.png

Tip: If you populate the 'Parent SKU' field against the bundle parent rows too, you can save some time in the next step!

Step 4: Find the cost and revenue per bundle parent

Using a function such as a pivot table, sum up the cost per bundle parent using the data from Step 3.

mceclip7.png

Using the Sales by Product report (or by using another pivot table on the Sales Detail data), sum up the revenue per bundle parent.

mceclip8.png

Finally, combine the two - you should be able to put them side-by-side - and perform your final calculations:

mceclip10.png

 

 

Have more questions? Submit a request