Manage a product line of several hundred or even several thousand SKUs quickly raises a problem: how can you tell if each product truly belongs in the assortment? Some SKUs generate sales but compete with one another. Others tie up inventory or shelf space without offering any real synergy. And despite an already very broad product offering, certain customer needs may still not be adequately met.
In practice, Excel is sufficient for one-off analyses and limited product assortments. BI tools provide a better way to visualize performance, while specialized assortment analytics solutions are better suited when the analysis needs to account for redundancies, assortment gaps, competitive data, and differences between stores.
This is one of the main challenges of assortment analysis: distinguishing a product’s performance from its actual contribution to the coherence of the product line. A product that sells well may overlap with several very similar products. Conversely, an SKU with lower sales volumes can play an essential role if it serves a specific use, price point, or customer segment.
Focusing solely on revenue, margin, or inventory turnover can therefore lead to hasty decisions. Analyzing the coherence of a product line requires understanding the role of each SKU in the assortment: how the products complement each other, where there are redundancies, which needs remain inadequately met, and whether certain SKUs can be streamlined without weakening the product offering.
So, what tool should you use to analyze the consistency of your product line? What metrics should you track, and to what extent can Excel or business intelligence tools meet this need?
What does “consistency within a product line” really mean?
The consistency of a product line is not measured by the number of items it contains. It depends above all on the role each product plays and what it brings to the table compared to the others.
Let’s consider a category of 200 products. Several products may sell well even though they are very similar in terms of features, price, or use. Their results are satisfactory when viewed individually, but is their simultaneous presence really justified? If several SKUs address the same need, some of their sales may simply be split among very similar products.
This is also why low sales volume alone is not sufficient grounds for delisting a product. Some products sell less but still serve a valuable purpose within the product line. They may offer an entry-level price point, address a specific need, target a particular market segment, or ensure the presence of a sought-after brand.
Revenue, margin, and inventory turnover remain essential for tracking performance. However, to analyze the coherence of a product line, these metrics must be considered alongside other information. Price positioning, product attributes, coverage of different segments, and competitive data, in particular, help determine whether the products truly complement one another.
In practical terms, analyzing the coherence of a product line involves answering several questions: Does each product meet a need that is distinct enough to justify its inclusion? Are certain SKUs highly interchangeable with one another? Are certain price segments, formats, brands, or uses overrepresented or, conversely, underrepresented? Finally, is the overall structure of the product range consistent with demand and with the market share held by these various segments?
Imbalances then become easier to spot. One product line may have a large number of items in a single price range, while another range is underrepresented. Several products may have nearly identical features. A brand or product format that is well-represented among competitors may also be missing from the offering.
An assortment analysis tool should make it possible to highlight these situations without requiring teams to create multiple files and perform manual cross-referencing, while going beyond simply ranking SKUs based on their performance to better understand the role of each product in the lineup and identify the trade-offs that need to be made.
Why does Excel quickly reach its limits when analyzing a product line?
Excel remains perfectly suitable for ad hoc analysis of a few dozen SKUs. It allows you to cross-analyze revenue, margin, volume, inventory turnover, and sales trends.
Limitations arise as the product assortment grows in depth and the amount of data to be considered increases. A retailer may have several thousand SKUs spread across hundreds of stores, various retail formats, multiple regions, and sales channels. Added to this are product attributes, brands, price segments, competitive data, seasonality, and space constraints. Excel’s limitations do not depend solely on the number of SKUs. They become particularly apparent when the analysis must cross-reference multiple dimensions simultaneously: SKUs, stores, product attributes, price segments, competition, seasonality, and product mix scenarios.
At this scale, files and spreadsheets pile up quickly. Teams then spend a significant portion of their time consolidating, verifying, and formatting the data before they can even begin to analyze it.
Excel also has its limitations when it comes to anticipating the impact of a decision on the product lineup. Discontinuing a product does not necessarily mean losing all associated sales. Some of the demand may shift to a similar product. Similarly, introducing a new product does not guarantee additional sales if it primarily takes away from sales of an existing SKU.
The regional nature of the product assortment adds another challenge. An assortment that is relevant on a national scale does not necessarily meet the needs of every store in the same way, particularly when customer profiles, local competition, or space constraints differ.
At this point, looking at past sales is no longer enough. The analysis must also make it possible to measure the effects of removing, adding, or changing a product on the rest of the product line.
Excel remains particularly effective for analyzing what has happened. However, it becomes less suitable when it comes to estimating what might happen after a reference is added or removed.
The 6 analyses that a product line consistency tool should be able to perform
1. Measure performance without focusing solely on revenue
The analysis typically begins with a review of the performance of the benchmark products. Revenue, volume, margin, turnover, contribution to the category, and sales trends help identify which products are driving results and which are underperforming.
These metrics can also be correlated with the position each product holds within the product assortment. Sales or margin per SKU, the SKU’s contribution to the category’s results, or—when merchandising data is available—sales or margin relative to shelf space allow for an assessment of the product line’s productivity. The goal is, in particular, to identify the SKUs or segments that take up a significant portion of the product mix or shelf space without making a proportional contribution to performance.
However, these indicators alone are not sufficient to determine which items to keep in the lineup or remove from it. Relying solely on sales figures often means favoring bestsellers, at the risk of reducing the diversity of the product lineup and excluding products that sell less but are important for meeting certain customer needs.
An assortment analysis tool must therefore make it possible to correlate the financial performance of each SKU with the role it actually plays in the product line.
2. Identify duplicates and areas of cannibalization
Two products that are similar in terms of use, format, features, or price may meet the same need. Adding a new SKU therefore does not necessarily generate new sales: some of the demand may simply shift away from products already in the lineup.
The analysis must therefore distinguish between incremental sales—which actually drive category growth—and sales resulting from cannibalization. When a product is discontinued, some sales may be lost, while others are shifted to substitute products.
This distinction is essential for streamlining a product lineup. An item with low sales may still be strategic if it meets a specific need, while a better-performing product can be discontinued with limited impact if demand for it can easily be shifted to other items.
Assortment optimization solutions make it possible, in particular, to analyze these shifts in demand in order to better measure the actual contribution of each product.
3. Identify gaps in the product lineup
Even a very broad product range may still fail to meet certain needs. For example, a retailer might offer many mid-range and high-end products while having limited offerings in the entry-level price range. The same imbalance can be seen in package sizes, with a wide selection of large-size products but limited choices for individual consumption.
To identify these gaps in the product lineup, you need to examine how SKUs are distributed according to the criteria that actually define the category. Depending on the product, these criteria may include price, brand, size, intended use, packaging, or product tier.
This makes it easier to see which segments have a high concentration of products and which have limited choices. However, just because a segment is underrepresented does not necessarily mean new products should be added. The key question is whether the product meets a customer need and represents a real opportunity for the retailer.
Market and competitive data are precisely what allow us to verify this point. They show whether a segment that is underrepresented in the product line is also underrepresented in the market, or whether it plays a more significant role among competitors.
4. Understanding the customer’s selection criteria
An analysis of a product assortment also benefits from taking into account how customers actually choose their products. Their shopping journey does not always follow the classification used by the retailer. Depending on the category, the brand may carry more weight in the decision, as may price, size, intended use, or a specific product feature.
Consumer Decision Trees are used to represent these criteria and their weight in the decision-making process.
Two products that appear very similar on paper may thus meet different expectations. Products classified by the retailer into distinct segments may also be viewed by the customer as alternatives at the time of purchase.
Taking these behaviors into account in assortment analysis makes it easier to distinguish complementary SKUs from substitutable ones, and to verify whether the product line meets the criteria that actually drive purchasing decisions.
5. Compare your product lineup to the market and your competitors
A product line may appear balanced when viewed on its own but reveal obvious weaknesses when compared to the market.
Competitive analysis helps answer key questions such as: Which brands are carried by my competitors but not by me? Which price segments are underrepresented? In terms of which attributes does my product assortment truly stand out? And where, conversely, is my offering excessively similar?
Benchmarking thus transforms internal analysis into positioning analysis.
Comparing your product lineup to that of competitors allows you, above all, to see what’s missing, what’s already widely available, and what truly sets your offering apart. The goal isn’t to carry the same products as the rest of the market, but to determine whether the differences you observe are intentional or whether they reveal an unmet need.
6. Analyze consistency at the local level
A product line that is consistent nationwide is not necessarily consistent at every retail location. A product may perform very well overall but may be much less suitable for certain stores. Shopping habits vary from one region to another, as do local competition, store size, and available shelf space.
These differences are prompting retailers to tailor their product assortments more closely to local conditions. McKinsey estimates, in fact, that store-level SKU selection could represent a 2- to 4-percent sales growth potential in the food retail sector. However, this level of granularity requires sufficiently reliable data and an organization capable of managing differentiated product assortments.
A product SKU may therefore be essential in one store cluster but much less relevant in another. Rather than applying the same product listing rules across the entire network, local analysis makes it possible to identify which products should be carried everywhere, which ones are justified only in certain areas, and which items are taking up shelf space without meeting sufficient local demand.
Which tool should you choose to analyze the consistency of your product line?
The right tool for analyzing the consistency of a product line depends primarily on internal needs. Tracking the performance of a few hundred SKUs, comparing product assortments across stores, or identifying redundant products does not require the same data or the same analytical capabilities.
Excel is suitable for one-time analyses of limited product assortments, while business intelligence tools make it easier to consolidate and visualize performance.
Category management and planogram solutions are better suited to addressing the challenges of product placement and shelf space allocation. Conversely,assortment analytics platforms enable a more detailed analysis of product mix, redundancies, coverage gaps, and opportunities for differentiation.
This is precisely the roleof OptimiX Assortment Benchmark, which enables users to compare product assortments and performance in order to identify the strengths and weaknesses of the product offering and help teams decide whether to retain, streamline, or expand product lines.
Factors to Consider Before Choosing an Assortment Analytics Solution
The first criterion is the level of detail in the analysis. An effective solution must allow users to drill down from the aggregate level to the individual item, as well as to compare categories, subcategories, brands, attributes, stores, or clusters.
The second is the ability to enrich internal data. A retailer’s sales figures reflect what is happening within its own scope of operations. They do not necessarily reveal opportunities that are missing from its offering. Integrating market, competitive, or product data allows internal performance to be viewed in its true context.
The third factor is the quality of the visualization. A sophisticated analysis that requires several days of interpretation by a data team loses some of its operational value. Category managers and product managers need to be able to quickly identify anomalies, opportunities, and trade-offs.
The fourth point concerns simulation. Before removing or adding references, teams must be able to test different scenarios and anticipate their impact on performance.
Finally, the tool must be accessible enough for business users to use. Algorithmic sophistication is only valuable if it actually improves the quality and speed of decisions.
From Descriptive Analysis to Recommendations: How AI Is Changing Things
When a product line includes several thousand SKUs, certain anomalies or redundancies can easily go unnoticed. The data is there, but analyzing it product by product quickly becomes impossible. Applying AI to product line analysis makes it possible to handle this volume without significantly increasing the number of hours spent on manual analysis.
Algorithms can cross-reference a large number of data points and criteria to identify very similar products, performance discrepancies, or opportunities that are difficult to spot in files. As a result, the Category Manager spends less time searching for information and can focus more on what that information means for their product line.
However, AI does not decide which products should remain in or be removed from the product lineup. A recommendation to delist a product must be considered within the context of the category. The product may meet a specific need, play a role in the pricing strategy, or be particularly important in certain stores. Market knowledge and business decisions remain in the hands of the business teams.
In 2026, McKinsey noted that AI applied to merchandising can reduce the time spent on reporting. For a Category Manager, this means less time spent consolidating files or reviewing hundreds of SKUs one by one. Products that show discrepancies, redundancies, or particular potential are flagged more quickly, and business expertise comes into play where it adds the most value—when deciding what to do with them.
OptimiX Assortment Benchmark: Objectively Evaluating Assortment Choices
What tools should be used to analyze and optimize a product line?
Analyzing a product line requires cross-referencing multiple data sources: sales, margins, prices, promotions, inventory, product attributes, forecasts, and supplier data. This information is typically spread across several tools.
In particular, the ERP system centralizes transactions, purchases, and inventory levels, while the PIM system organizes product characteristics. Business Intelligence solutions then make it possible to analyze historical performance and develop key performance indicators.
Pricing tools enable you to analyze price positioning, price differences between SKUs, and the consistency of the pricing structure.
Assortment consistency analysis tools make it possible to evaluate the structure of the product range, the complementarity among SKUs, redundancies, unmet needs, and opportunities to streamline or expand the product offering.
Finally, demand planning solutions incorporate forecasts and demand variability to anticipate inventory needs and adjust procurement accordingly.
Linking decisions regarding product assortment, pricing, forecasting, and inventory provides a comprehensive view of product line performance. The tools developed by
A high-performance product line is built one product at a time
Analyzing the coherence of a product line mainly involves understanding what each product contributes to the overall offering. Two products that perform well may be redundant, while a product that doesn’t sell as well may still be entirely relevant if it meets a specific need.
The level of tooling required then depends on the complexity of these trade-offs. Excel may be sufficient for working with a limited product assortment. A business intelligence (BI) tool facilitates performance tracking and comparison. When the analysis must account for interactions between SKUs, market positioning, gaps in the product assortment, or differences between stores, a specialized product assortment analysis tool provides additional depth.
For a retailer, the issue is therefore not about reducing or expanding its product line at any cost. It is about determining where adding more choices truly creates value, where an increase in the number of SKUs dilutes performance, and which products play a sufficiently distinct role to warrant keeping them in the lineup.
That’s also what sets a simply broad product line apart from a truly well-curated selection.
