Mathew K Analytics

Lesson 8 · Real-World Data Analytics

ABC Analysis & Pareto 80/20 in Python: Product Performance | Data Analytics #8

Do 20% of products really make 80% of revenue? We derive product categories from raw description text, then run a classic ABC (Pareto) analysis in pandas on…

What you'll learn

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 8: Product Category Performance and ABC Analysis#

  • Video eight of the hundred-video real-world data analytics series.
  • Deriving real product categories straight from real description text, then running a real classic ABC analysis on top of it.
  • Let's get into it.

This lesson is for analysts who need to tell a business which products and product groups actually earn its money, even when the data has no category field. The business question: where is revenue concentrated, and which products deserve the most attention in stock planning and promotion?

You will use online_retail_clean.csv, the cleaned online retail transactions from earlier lessons. The columns that matter are StockCode, Description, Quantity, UnitPrice, InvoiceNo, Country and Revenue. Since there is no category column, you will derive one from keywords in the description text.

You will then run an ABC analysis, a classic inventory technique that ranks products by revenue and splits them into tier A (the few products driving most revenue), tier B (the middle) and tier C (the long tail).

In this lesson you will:

  • build a simple, transparent keyword-based categoriser and measure its coverage
  • compare revenue, prices and quantities across categories
  • compute cumulative revenue shares and assign A, B and C tiers
  • test the Pareto (80/20) pattern and profile each tier

You should be comfortable with pandas groupby, apply and value_counts; see earlier lessons in the series if not.

Part 1: This Real Dataset Has No Category Column#

Category-level reporting is one of the first things a retail manager asks for, but this dataset does not have a category column. Before building one, you load the cleaned data and confirm its size. The code reads online_retail_clean.csv with parse_dates=['InvoiceDate'] so dates are real datetimes. The output (391150, 9) shows 391,150 transaction rows and 9 columns, none of which is a category. That gap is what the next few steps fill, using the product description text.

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
clean = pd.read_csv('online_retail_clean.csv', parse_dates=['InvoiceDate'])
clean.shape
(391150, 9)

Part 2: Real Keyword List for Deriving Categories#

The simplest honest way to invent categories is a keyword list: if a description contains a word like MUG or CANDLE, the product belongs to that group. The code defines a Python list of 20 product keywords, and len(keywords) confirms there are 20. The order of this list matters, because the categoriser will stop at the first match. A product described as a candle in a jar, for example, will be labelled by whichever keyword appears first in the list.

keywords = ['BAG', 'MUG', 'CANDLE', 'LIGHT', 'BOX', 'CARD', 'FRAME', 'CLOCK', 'BOTTLE', 'NECKLACE', 'BRACELET', 'CUSHION', 'LANTERN', 'JAR', 'TIN', 'BOWL', 'PLATE', 'APRON', 'NOTEBOOK', 'MIRROR']
len(keywords)
20

Part 3: Real Category Assignment Function#

This step prepares the text and defines the matching rule. The code turns every description into upper-case text with astype(str).str.upper() so matching is not tripped up by case or missing values. The categorize function loops through the keywords and returns the first one found in the description, formatted with .title() (so BAG becomes Bag), or 'Other' if none match. There is no output because nothing is displayed yet. A common pitfall is that in matches substrings, so a short keyword like TIN can also match inside unrelated words such as PAINTING.

desc_upper = clean['Description'].astype(str).str.upper()
def categorize(desc):
    for kw in keywords:
        if kw in desc:
            return kw.title()
    return 'Other'

Part 4: Applying the Real Categorization#

Now the rule is applied to every row. desc_upper.apply(categorize) runs the function once per description and stores the result in a new Category column. value_counts().head(10) then counts rows per category. The output shows 224,742 rows in Other, followed by Bag with 38,035, Tin with 21,058 and Box with 18,952. Note that these are counts of transaction lines, not products or revenue, so a popular product sold many times pushes its category up the list.

clean['Category'] = desc_upper.apply(categorize)
clean['Category'].value_counts().head(10)
Category
Other     224742
Bag        38035
Tin        21058
Box        18952
Light      17605
Card       12938
Bottle      8688
Candle      8182
Mug         6114
Clock       5800
Name: count, dtype: int64

Part 5: Being Real Honest About Coverage#

Any derived field needs a coverage figure so readers know how much to trust it. The code compares Category with 'Other' and takes .mean() of the True/False result, which gives the share of rows that matched a keyword. The output reports 42.5% of transactions categorised, leaving more than half in Other. That is a real limitation: any category comparison in this lesson describes less than half the data. Being upfront about this is better than hiding it, and adding more keywords is the obvious way to improve it.

coverage = (clean['Category'] != 'Other').mean()
print(f'Real keyword categorization covers {round(coverage * 100, 1)}% of transactions; the rest fall into an honest Other bucket.')
Real keyword categorization covers 42.5% of transactions; the rest fall into an honest Other bucket.

Part 6: Real Revenue by Category#

Revenue by category shows where the money comes from, which matters more than how many lines each category has. The code groups by Category, sums Revenue and sorts from largest to smallest. The output is led by Other at about 4.77 million GBP, then Bag at about 890,000, Tin at about 579,000 and Light at about 576,000. Among the named categories, bags are clearly the biggest earner, but the size of Other is a reminder that the biggest single group is still unclassified.

category_revenue = clean.groupby('Category')['Revenue'].sum().sort_values(ascending=False)
category_revenue.head(10).round(0)
Category
Other     4770332.0
Bag        890133.0
Tin        579462.0
Light      575857.0
Box        444710.0
Bottle     221032.0
Frame      181913.0
Jar        162903.0
Card       150706.0
Clock      147603.0
Name: Revenue, dtype: float64

Part 7: Visualizing Real Category Revenue#

A horizontal bar chart is the clearest way to compare named categories, since long labels fit easily on the vertical axis. The code drops Other so it does not dwarf everything else, keeps the top 12, and plots them with plt.barh. The [::-1] reverses the order so the largest bar appears at the top. Open category_revenue.png and look for Bag well ahead of the rest, with Tin and Light close together behind it, and how quickly the bars shrink further down.

plt.figure(figsize=(10, 6))
top_categories = category_revenue[category_revenue.index != 'Other'].head(12)
plt.barh(top_categories.index[::-1], top_categories.values[::-1], color='teal')
plt.xlabel('Real Total Revenue (GBP)')
plt.title('Real Revenue by Derived Product Category')
plt.tight_layout()
plt.savefig('category_revenue.png', dpi=120)
plt.close()

Part 8: Real Average Order Value by Category#

Price and volume together explain why a category earns what it does. Despite the heading, this code measures average unit price rather than average order value: it filters out Other, then groups by category and computes the mean UnitPrice and total Quantity. The output shows Necklace with the highest average price at 6.33 GBP but only 920 units, while Bag averages 1.89 GBP yet sells 541,048 units. Cards and notebooks sit at about 1 GBP. In this shop, high revenue comes mostly from cheap items sold in large numbers.

category_stats = clean[clean['Category'] != 'Other'].groupby('Category').agg(AvgUnitPrice=('UnitPrice', 'mean'), TotalQty=('Quantity', 'sum'))
category_stats.sort_values('AvgUnitPrice', ascending=False).round(2)
AvgUnitPrice TotalQty
Category
Necklace 6.33 920
Clock 5.55 34771
Bracelet 4.85 3249
Frame 4.77 49896
Bottle 4.21 61944
Mirror 3.99 22755
Tin 3.77 209583
Cushion 3.58 17154
Lantern 3.51 19850
Jar 3.00 117661
Box 2.99 224944
Apron 2.82 16598
Light 2.52 316766
Bowl 2.30 50263
Candle 1.97 104221
Bag 1.89 541048
Mug 1.78 81242
Plate 1.74 43252
Card 1.00 205748
Notebook 0.99 27180

Part 9: Switching to ABC Analysis at the Real Product Level#

Categories are useful for reporting, but stock and promotion decisions are made product by product, so ABC analysis works at the StockCode level. The code sums revenue per product and sorts from highest to lowest, which is the ranking every later step depends on. The output shows 3,659 products in the catalogue. Sorting before the next step is essential: a cumulative share only makes sense when the biggest products come first.

product_revenue = clean.groupby('StockCode')['Revenue'].sum().sort_values(ascending=False)
product_revenue.shape[0]
3659

Part 10: Real Cumulative Revenue Share#

The cumulative revenue share tells you what percentage of total revenue the top N products account for. The code uses .cumsum() to build a running total down the sorted list, then divides by the grand total and multiplies by 100. The output shows the top product, 23843, alone accounts for 1.93% of all revenue, and the top five together reach 6.61%. This running percentage is what the A, B and C tiers will be cut from.

cumulative_revenue = product_revenue.cumsum()
cumulative_pct = cumulative_revenue / product_revenue.sum() * 100
cumulative_pct.head(5).round(2)
StockCode
23843     1.93
22423     3.56
85123A    4.71
85099B    5.68
23166     6.61
Name: Revenue, dtype: float64

Part 11: Assigning Real A, B, and C Tiers#

The tier rule is a common convention: products making up the first 80% of cumulative revenue are A, the next 15% (up to 95%) are B, and the remaining 5% are C. The function checks each cumulative percentage against those thresholds, and .apply labels every product. The output shows 783 products in A, 920 in B and 1,956 in C. The tier boundaries are a business choice, not a law, so feel free to try 70/90 or other splits to suit how a team manages stock.

def assign_tier(pct):
    if pct <= 80:
        return 'A'
    elif pct <= 95:
        return 'B'
    else:
        return 'C'
tiers = cumulative_pct.apply(assign_tier)
tiers.value_counts()
Revenue
C    1956
B     920
A     783
Name: count, dtype: int64

Part 12: Real Revenue Share per Tier#

This step confirms how revenue divides across the tiers. The code builds a small table of revenue and tier per product, then computes each tier's percentage of total revenue. Only the last expression, the percentage, is displayed. The output shows A at 80.0%, B at 15.0% and C at 5.0%. These match the cut-offs almost exactly, which is expected because the tiers were defined by cumulative revenue. The interesting finding is not these percentages but how many products sit in each tier.

abc_table = pd.DataFrame({'Revenue': product_revenue, 'Tier': tiers})
abc_table.groupby('Tier')['Revenue'].sum().round(0)
(abc_table.groupby('Tier')['Revenue'].sum() / abc_table['Revenue'].sum() * 100).round(1)
Tier
A    80.0
B    15.0
C     5.0
Name: Revenue, dtype: float64

Part 13: Validating the Real Pareto Principle#

The Pareto principle is the observation that a small share of causes often drives most of the effect, popularly 20% of products driving 80% of revenue. The code takes .mean() of the condition tiers == 'A', which gives the share of products in tier A, and prints it. The output shows 21.4% of products generate 80% of revenue, remarkably close to the classic 80/20 rule. This is a strong, easy-to-communicate finding for any stakeholder.

pct_products_in_a = (tiers == 'A').mean() * 100
print(f'Just {round(pct_products_in_a, 1)}% of products in tier A generate 80% of total real revenue.')
Just 21.4% of products in tier A generate 80% of total real revenue.

Part 14: Visualizing the Real Pareto Curve#

The Pareto curve shows the whole concentration story in one picture. The code plots product rank on the x-axis against cumulative revenue percentage on the y-axis, using np.arange to create the ranks 1 to 3,659, and adds a dashed red line at 80% with plt.axhline. Open pareto_curve.png and look for a curve that rises steeply at first and then flattens into a long, nearly level tail. The point where it crosses the red line sits at about rank 783, the edge of tier A.

plt.figure(figsize=(9, 6))
rank = np.arange(1, len(cumulative_pct) + 1)
plt.plot(rank, cumulative_pct.values, color='navy')
plt.axhline(80, color='red', linestyle='--', label='Real 80% revenue line')
plt.xlabel('Real Product Rank (by Revenue)')
plt.ylabel('Real Cumulative % of Total Revenue')
plt.title('Real Pareto Curve of Product Revenue')
plt.legend()
plt.tight_layout()
plt.savefig('pareto_curve.png', dpi=120)
plt.close()

Part 15: Real Top A-Tier Products#

Naming the top earners makes the analysis concrete. The code builds a description lookup using each product's most common description, takes the first 10 A-tier rows (already sorted by revenue), and uses .reindex to line up descriptions with those stock codes. The output is led by PAPER CRAFT , LITTLE BIRDIE at about 168,470 GBP, then REGENCY CAKESTAND 3 TIER at about 142,265 and WHITE HANGING HEART T-LIGHT HOLDER at about 100,547. Keep the first product in mind: a later step shows its revenue comes from just one transaction.

desc_lookup = clean.groupby('StockCode')['Description'].agg(lambda s: s.mode().iloc[0])
a_tier_top = abc_table[abc_table['Tier'] == 'A'].head(10)
pd.DataFrame({'Description': desc_lookup.reindex(a_tier_top.index), 'Revenue': a_tier_top['Revenue']}).round(0)
Description Revenue
StockCode
23843 PAPER CRAFT , LITTLE BIRDIE 168470.0
22423 REGENCY CAKESTAND 3 TIER 142265.0
85123A WHITE HANGING HEART T-LIGHT HOLDER 100547.0
85099B JUMBO BAG RED RETROSPOT 85041.0
23166 MEDIUM CERAMIC TOP STORAGE JAR 81417.0
47566 PARTY BUNTING 68785.0
84879 ASSORTED COLOUR BIRD ORNAMENT 56413.0
23084 RABBIT NIGHT LIGHT 51251.0
22502 PICNIC BASKET WICKER SMALL 47348.0
79321 CHILLI LIGHTS 46265.0

Part 16: Real Long-Tail C-Tier Products#

The long tail is where stock-keeping costs can quietly outweigh sales. The code filters to tier C and calculates the product count, total revenue and mean revenue, but only the mean is displayed because it is the last expression. The output shows an average of about 223 GBP of revenue per C-tier product over the whole period. You already know there are 1,956 of these products, sharing just 5% of revenue. Print the first line too if you want to see the total directly.

c_tier = abc_table[abc_table['Tier'] == 'C']
c_tier.shape[0], c_tier['Revenue'].sum().round(0)
c_tier['Revenue'].mean().round(2)
np.float64(223.37)

Part 17: Real Category Mix Within Tier A#

This step asks which derived categories the A-tier products belong to. The code filters transactions to A-tier stock codes with .isin(...) and counts rows per category. The output shows Other leads with 131,726 rows, followed by Bag with 33,891, Tin with 18,043 and Box with 14,411. These are transaction-line counts, not product counts. Bags dominating here matches their lead in category revenue, while the large Other figure again shows the limits of keyword coverage.

a_tier_categories = clean[clean['StockCode'].isin(abc_table[abc_table['Tier'] == 'A'].index)]['Category']
a_tier_categories.value_counts().head(8)
Category
Other     131726
Bag        33891
Tin        18043
Box        14411
Light      13091
Bottle      7474
Clock       4844
Frame       4359
Name: count, dtype: int64

Part 18: Real Order Frequency by Tier#

If A-tier revenue came from a few huge one-off orders, it would be risky to plan stock around it. The code counts distinct invoices per product with nunique, maps that onto the ABC table, and averages by tier. The output shows A-tier products appear on 314.0 orders on average, against 104.5 for B and 22.7 for C. So A-tier products are generally ordered often, not just once, which makes their revenue more dependable. As you will see, there are still exceptions at the very top.

orders_per_product = clean.groupby('StockCode')['InvoiceNo'].nunique()
abc_table['Orders'] = abc_table.index.map(orders_per_product)
abc_table.groupby('Tier')['Orders'].mean().round(1)
Tier
A    314.0
B    104.5
C     22.7
Name: Orders, dtype: float64

Part 19: Real Inventory Implication#

Order size affects how much stock you need on hand for each sale. The code computes units per order for each product (total quantity divided by distinct invoices), maps it onto the ABC table and averages by tier. The output shows 118.85 units per order for tier A, against 12.68 for B and 9.53 for C. Treat the A figure with care: it is a mean of product-level values, so one or two products with extreme single orders can pull it up sharply. Checking the median would give a more typical picture.

avg_qty_per_order = clean.groupby('StockCode').agg(TotalQty=('Quantity', 'sum'), Orders=('InvoiceNo', 'nunique'))
avg_qty_per_order['QtyPerOrder'] = avg_qty_per_order['TotalQty'] / avg_qty_per_order['Orders']
abc_table['QtyPerOrder'] = abc_table.index.map(avg_qty_per_order['QtyPerOrder'])
abc_table.groupby('Tier')['QtyPerOrder'].mean().round(2)
Tier
A    118.85
B     12.68
C      9.53
Name: QtyPerOrder, dtype: float64

Part 20: Real Sanity Check on the Tier Cutoffs#

A quick check confirms the tier logic was applied as intended. The code takes the last product in tier A, which sits right at the boundary, and checks that its cumulative percentage is at or below 80. The output True confirms the cut-off was respected. Testing the boundary case like this is a cheap way to catch off-by-one mistakes, such as using < where you meant <=.

boundary_product = abc_table[abc_table['Tier'] == 'A'].index[-1]
round(cumulative_pct[boundary_product], 2) <= 80
np.True_

Part 21: Saving the Real ABC Classification#

Saving the classification lets buyers and planners use it without running any code. The code copies the ABC table, adds descriptions with .reindex, rounds numbers to two decimals and writes product_abc_classification.csv, keeping StockCode as the index column. Reloading the file and comparing row counts gives True, so all 3,659 products were saved. Working on a .copy() keeps the original table unchanged for later steps.

abc_output = abc_table.copy()
abc_output['Description'] = desc_lookup.reindex(abc_output.index)
abc_output.round(2).to_csv('product_abc_classification.csv')
reloaded = pd.read_csv('product_abc_classification.csv')
reloaded.shape[0] == abc_output.shape[0]
True

Part 22: Real Recap Print#

A single summary sentence is what most people will remember from the analysis. The code prints the number of products classified and the share sitting in tier A. The output reads 3,659 products classified, with tier A holding just 21.4% of products but driving 80% of revenue. When you present this, pair it with the Pareto chart so the audience can see the shape behind the number.

print(f'Classified {len(abc_table)} real products into ABC tiers; tier A holds just {round(pct_products_in_a, 1)}% of products but drives 80% of real revenue.')
Classified 3659 real products into ABC tiers; tier A holds just 21.4% of products but drives 80% of real revenue.

Part 23: Real Category Share Including the Other Bucket#

Earlier charts left Other out, so it is worth showing its share honestly. The code divides each category's revenue by the total and multiplies by 100. The output shows Other at 54.6% of revenue, then Bag at 10.2%, Tin and Light at 6.6% each, Box at 5.1% and Bottle at 2.5%. In other words, more than half of revenue is not covered by any named category. Any category-level conclusion should carry that caveat.

category_share = (category_revenue / category_revenue.sum() * 100).round(1)
category_share.head(6)
Category
Other     54.6
Bag       10.2
Tin        6.6
Light      6.6
Box        5.1
Bottle     2.5
Name: Revenue, dtype: float64

Part 24: Real Preview of Tier B Products#

Tier B products are the middle ground: not stars, but not negligible either, and they are often where growth opportunities hide. The code takes the first five B-tier rows, which are the largest B products, and shows their descriptions and revenue. The output lists products such as 12 MESSAGE CARDS WITH ENVELOPES and HEART WREATH DECORATION WITH BELL, each with about 2,560 to 2,580 GBP. Their revenues are almost identical, which shows how gradual the ranking is once you leave the top of the list.

b_tier_preview = abc_table[abc_table['Tier'] == 'B'].head(5)
pd.DataFrame({'Description': desc_lookup.reindex(b_tier_preview.index), 'Revenue': b_tier_preview['Revenue']}).round(0)
Description Revenue
StockCode
22906 12 MESSAGE CARDS WITH ENVELOPES 2579.0
23212 HEART WREATH DECORATION WITH BELL 2578.0
21115 ROSE CARAVAN DOORSTOP 2568.0
22187 GREEN CHRISTMAS TREE CARD HOLDER 2562.0
23100 SILVER BELLS TABLE DECORATION 2562.0

Part 25: Real Revenue Concentration at the Very Top#

Looking at the very top 1% shows how extreme concentration can get. The code takes 1% of the product count, using max(1, ...) to guarantee at least one product, then sums their revenue as a share of the total. The output says the top 36 products, just 1% of the catalogue, generate 18.7% of total revenue. Losing supply of even one of these products would be very noticeable, so they deserve close monitoring.

top_1pct_count = max(1, int(len(product_revenue) * 0.01))
top_1pct_share = product_revenue.head(top_1pct_count).sum() / product_revenue.sum() * 100
print(f'The top {top_1pct_count} real products, just 1% of the catalog, generate {round(top_1pct_share, 1)}% of total real revenue.')
The top 36 real products, just 1% of the catalog, generate 18.7% of total real revenue.

Part 26: Visualizing the Real Revenue Distribution#

Product revenues span several orders of magnitude, so a normal histogram would show one tall bar and nothing else. The code keeps products with positive revenue and plots the base-10 logarithm with np.log10, so 2 means 100 GBP, 3 means 1,000 GBP and so on. Open product_revenue_distribution.png and look for where the bulk of products sits on this log scale, and for the long left and right tails. A log scale is the standard trick for any heavily skewed money column.

plt.figure(figsize=(9, 5))
plt.hist(np.log10(product_revenue[product_revenue > 0]), bins=40, color='crimson', edgecolor='white')
plt.xlabel('Real Log10 of Product Revenue')
plt.ylabel('Real Number of Products')
plt.title('Real Distribution of Revenue Across the Product Catalog')
plt.tight_layout()
plt.savefig('product_revenue_distribution.png', dpi=120)
plt.close()

Part 27: Real Categories Entirely Missing from Tier A#

Some categories may contribute nothing to the top tier at all, which can inform range decisions. The code builds a Python set of all named categories and another of categories found among A-tier transactions, then subtracts one from the other to find the difference. The output shows Bracelet and Necklace are entirely missing from tier A. These are also the categories with the highest average prices and lowest volumes, so they behave like niche lines in this shop.

all_categories = set(clean[clean['Category'] != 'Other']['Category'].unique())
categories_in_a = set(a_tier_categories.unique())
all_categories - categories_in_a
{'Bracelet', 'Necklace'}

Part 28: Real Median Revenue by Tier#

Medians give a typical product's revenue in each tier, free from the pull of extreme values. The code groups the ABC table by tier and takes the median revenue. The output shows about 5,696 GBP for a typical A-tier product, about 1,309 for B and just 143 for C. A typical A product earns about 40 times what a typical C product does, which helps justify giving A products priority in stock and display decisions.

abc_table.groupby('Tier')['Revenue'].median().round(2)
Tier
A    5695.86
B    1309.14
C     143.05
Name: Revenue, dtype: float64

Part 29: Real Countries Buying the Top Product#

Checking who buys the top product is a good way to spot data quirks. The code takes the highest-revenue stock code and counts rows by country. The output shows a single row from the United Kingdom. In other words, the top product's revenue comes from one transaction, not a steady stream of orders. One very large order like this can dominate a ranking, so it is worth checking against the raw data for any related cancellation before treating the product as a reliable best-seller.

top_product_code = product_revenue.index[0]
clean[clean['StockCode'] == top_product_code]['Country'].value_counts().head(5)
Country
United Kingdom    1
Name: count, dtype: int64

Part 30: Real Total Revenue Cross-Check#

A final reconciliation confirms nothing was lost or double-counted along the way. The code compares total revenue in the ABC table with total revenue in the cleaned transactions, both rounded to whole pounds. The output True shows they match, so every pound of revenue is assigned to exactly one product and one tier. Ending an analysis with a totals check like this is a good habit.

abc_table['Revenue'].sum().round(0) == clean['Revenue'].sum().round(0)
np.True_

Wrap-Up: What You Learned#

  • When a real dataset has no category field, a real transparent keyword-based scheme can derive one honestly, coverage gaps and all.
  • ABC analysis ranks real products by revenue and splits them into real A, B, and C tiers based on real cumulative contribution.
  • The real Pareto pattern, a small share of products driving most revenue, held up directly against this real dataset's own numbers.
  • Tier A products are genuinely both high-revenue and high-frequency, not just a few real lucky big sales.
  • Real long-tail C-tier products individually contribute very little, informing real decisions about what to keep in stock.
  • Next video: real scraping product prices for competitive analysis, comparing this catalog against real external pricing.

Practice on your own

  1. Add five more keywords (for example HOLDER, DECORATION, SIGN, BUNTING and ORNAMENT) to the keyword list, rerun the categoriser and see how far coverage rises above 42.5%.
  2. Repeat the ABC analysis using total Quantity instead of Revenue and compare how many products land in tier A, and which products move between tiers.
  3. Remove the top product, rebuild product_revenue, the cumulative share and the tiers, and check how the tier A product count and the top 1% revenue share change.

The next video in the series covers scraping product prices for competitive analysis, comparing this catalogue against external pricing.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. Why does the lesson build product categories from keywords in the Description?

  2. In the lesson's ABC analysis, how are tiers assigned?

  3. Product revenues sorted high to low are 50, 30, 10, 6, 4 (total 100). Using the A <= 80, B <= 95, C otherwise rule on cumulative %, how many products are tier A?

  4. What does the Pareto pattern say about the retail data?

  5. Why must product_revenue be sorted in descending order before cumsum()?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Create 10 product descriptions (e.g. 'RED COFFEE MUG', 'SOY CANDLE', 'GIFT BAG LARGE', 'WALL CLOCK', 'PLAIN NOTEBOOK') with a Revenue column. Write a keyword list and a categorize function that returns the first matching keyword in title case, or 'Other'. Add a Category column, print revenue by category and the share of rows not in 'Other'.

    💡 Hint

    Convert the description to upper case and loop through the keywords.

    ✅ Show solution
    import pandas as pd
    
    df = pd.DataFrame({
        'Description': ['RED COFFEE MUG', 'SOY CANDLE', 'GIFT BAG LARGE', 'WALL CLOCK', 'PLAIN NOTEBOOK',
                        'BLUE MUG SET', 'VANILLA CANDLE', 'PAPER BAG', 'DOOR SIGN', 'CLOCK RADIO'],
        'Revenue': [120, 200, 80, 150, 60, 90, 110, 40, 30, 70],
    })
    keywords = ['BAG', 'MUG', 'CANDLE', 'CLOCK']
    
    def categorize(desc):
        for kw in keywords:
            if kw in desc:
                return kw.title()
        return 'Other'
    
    df['Category'] = df['Description'].str.upper().apply(categorize)
    print(df.groupby('Category')['Revenue'].sum().sort_values(ascending=False))
    print('Coverage:', round((df['Category'] != 'Other').mean() * 100, 1), '%')
    
  2. Make a Series of 10 product revenues (index = stock codes). Sort descending, compute cumulative revenue %, assign tiers A (<= 80), B (<= 95) or C with a function, and print how many products are in each tier and each tier's share of revenue.

    💡 Hint

    Use cumsum() / sum() * 100 and .apply() with a tier function.

    ✅ Show solution
    import pandas as pd
    
    product_revenue = pd.Series(
        [5000, 3000, 900, 400, 250, 150, 100, 80, 70, 50],
        index=['P01','P02','P03','P04','P05','P06','P07','P08','P09','P10'],
    ).sort_values(ascending=False)
    cumulative_pct = product_revenue.cumsum() / product_revenue.sum() * 100
    
    def assign_tier(pct):
        if pct <= 80:
            return 'A'
        elif pct <= 95:
            return 'B'
        return 'C'
    
    tiers = cumulative_pct.apply(assign_tier)
    abc = pd.DataFrame({'Revenue': product_revenue, 'Tier': tiers})
    print(abc['Tier'].value_counts().sort_index())
    print((abc.groupby('Tier')['Revenue'].sum() / abc['Revenue'].sum() * 100).round(1))
    
  3. Create 16 transaction lines (StockCode, Description, Quantity, UnitPrice) for 8 products. Derive a Category with keywords, compute Revenue, run ABC tiers by product, then print the percentage of products in tier A, the revenue share of tier A, and the category mix (row counts) inside tier A.

    💡 Hint

    Merge the tier labels back with isin on StockCode to count categories.

    ✅ Show solution
    import pandas as pd
    
    tx = pd.DataFrame({
        'StockCode': ['A1','A1','A1','B2','B2','C3','C3','D4','E5','F6','G7','H8','A1','B2','C3','E5'],
        'Description': ['RED MUG']*3 + ['SOY CANDLE']*2 + ['GIFT BAG']*2 + ['WALL CLOCK','PLAIN NOTEBOOK',
                        'DOOR SIGN','KEY RING','PEN SET','RED MUG','SOY CANDLE','GIFT BAG','PLAIN NOTEBOOK'][:9],
        'Quantity': [20, 30, 25, 40, 35, 10, 12, 5, 3, 2, 2, 1, 50, 45, 8, 2],
        'UnitPrice': [4.0, 4.0, 4.0, 6.0, 6.0, 2.0, 2.0, 12.0, 3.0, 5.0, 2.0, 3.0, 4.0, 6.0, 2.0, 3.0],
    })
    tx['Revenue'] = tx['Quantity'] * tx['UnitPrice']
    keywords = ['MUG', 'CANDLE', 'BAG', 'CLOCK']
    
    def categorize(desc):
        for kw in keywords:
            if kw in desc.upper():
                return kw.title()
        return 'Other'
    
    tx['Category'] = tx['Description'].apply(categorize)
    product_revenue = tx.groupby('StockCode')['Revenue'].sum().sort_values(ascending=False)
    cum_pct = product_revenue.cumsum() / product_revenue.sum() * 100
    tiers = cum_pct.apply(lambda p: 'A' if p <= 80 else ('B' if p <= 95 else 'C'))
    
    a_codes = tiers[tiers == 'A'].index
    print('Tier A share of products:', round((tiers == 'A').mean() * 100, 1), '%')
    print('Tier A share of revenue:', round(product_revenue[a_codes].sum() / product_revenue.sum() * 100, 1), '%')
    print(tx[tx['StockCode'].isin(a_codes)]['Category'].value_counts())
    

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.