top of page

Comprehensive Analysis of Retail Sales Data Part 2 - Detailed Analytical Findings and Insights

This page provides a detailed breakdown of our three dashboards: Overview, RFM Segmentation, and Retention Analysis. Each dashboard is structured to highlight key insights through five visuals, and on this page, I accompany these visuals with interpretations to help explain what the data reveals.

​

Before we dive into it, there are some things to keep in mind:

  1. This analysis assumes TODAY is the last day of the data, as if we were working with real-time information.

  2. Power BI’s interactivity adds another layer of depth. Using filters or selecting elements within visuals allows for a focused analysis by country, product, day, month, hour, and more. While the insights here are general, I want to emphasize that they can be explored further to address specific questions.

  3. The presence of UNKNOWN Customers significantly impacts metrics like averages calculated on a per-customer basis or identifying top customers. While it’s treated as a single entity here, it likely represents several unidentified customers. In a real-world scenario, clarifying this with stakeholders would be a priority.

  4. The drop in activity during December 2011 is due to incomplete data, as the last entry is from December 9th, so interpretations do not emphasize this period.

  5. We have assumed the business started on the earliest entry, which is December 1, 2010, though this does not reflect the actual start date.

Table of Contents:

The Overview Dashboard offers a high-level analysis of the core metrics that drive business performance, with a primary focus on revenue. In total, we served 4,364 unique customers, generating £9.79M in total revenue across 23,220 transactions. These transactions involved the purchase of 3,812 unique items and a total of 5 million units sold.

​

This dashboard is structured into five visuals:

  1. Revenue by Country: A map to understand geographic contributions to total revenue.

  2. Hourly Revenue: A line chart to observe how sales fluctuate throughout hours of the day across all days of the week, highlighting demand patterns and customer support needs.

  3. Top Selling Items: A highlighted table to identify high-demand products driving sales.

  4. Year-Month Revenue: A clustered column chart to track monthly revenue trends while highlighting the impact of specific events on sales performance.

  5. Daily Revenue: A line chart to analyze detailed performance on a day-to-day basis.

Power BI Map Visual

    1. Revenue by Country 

​

​

​​

  • The UK is the dominant market, generating the majority of revenue (£8,301,692.55), which accounts for 84.77% of total revenue. This highlights a strong domestic focus and significant customer engagement within the UK.

  • The Netherlands (£283,479.54, 2.89%), EIRE (Ireland, £259,663.46, 2.65%), Germany (£200,808.40, 2.05%), and France (£182,163.39, 1.86%) represent important secondary markets and are significant contributors outside the UK, which likely have robust customer bases or strategic logistical advantages. Efforts should focus on sustaining and enhancing these existing markets.

  • Switzerland (£52,505.35, 0.54%) and Spain (£51,765.20, 0.53%) show moderate revenues and could benefit from targeted marketing efforts to unlock growth opportunities.

  • Australia generates high revenue (£136,990.00, 1.40%), reflecting strong international operations and a well-established presence in Oceania.

  • The USA, despite its potential as a large market, has low revenue (£1,730.92, 0.02%), suggesting under-penetration or limited marketing efforts.

    2. Hourly Revenue 

​

​

​​

Power BI Line Visual

  • Revenue begins relatively low in the early morning but increases sharply around 9 AM across all weekdays, peaking at 10 AM and gradually slowing down after 3 PM. Customer support needs are moderate in the early hours but escalate during peak times, requiring increased availability to manage inquiries, transactions, and order issues effectively. After 3 PM, as revenue declines, support needs may diminish, though a smaller team should remain available for late-day inquiries.

  • Tuesday and Wednesday consistently generate high revenue between 10 AM and 2 PM, making these crucial periods for focused support and operational efficiency. Thursday also shows strong performance during these hours.

  • Sundays see the lowest revenue, approximately 55.65% lower than the average weekday revenue. However, Sunday afternoons present an opportunity for targeted campaigns to engage customers and boost sales.

  • Saturdays stand out as a rest day since the website was not operational on these days.

    3. Top Selling Items 

​

​

​​

Power BI Table

The top 10 best-selling items are:

​

  • 22423: REGENCY CAKESTAND 3 TIER (£164,762.19, 1.68% of revenue)

  • 85123A: WHITE HANGING HEART T-LIGHT HOLDER (£99,846.98, 1.02% of revenue)

  • 47566: PARTY BUNTING (£98,302.98, 1.00% of revenue)

  • 85099B: JUMBO BAG RED RETROSPOT (£92,356.03, 0.94% of revenue)

  • 23084: RABBIT NIGHT LIGHT (£66,756.59, 0.68% of revenue)

  • 22086: PAPER CHAIN KIT 50'S CHRISTMAS (£63,791.94, 0.65% of revenue)

  • 84879: ASSORTED COLOUR BIRD ORNAMENT (£58,959.73, 0.60% of revenue)

  • 79321: CHILLI LIGHTS (£53,768.06, 0.55% of revenue)

  • 22502: PICNIC BASKET WICKER SMALL (£51,041.37, 0.52% of revenue)

  • 22197: POPCORN HOLDER (£50,987.47, 0.52% of revenue)

    4. Year-Month Revenue 

​

​

​​

  • November 2011 recorded the highest revenue at £1,432,734.99 (14.63%), driven by Thanksgiving, which often triggers early holiday shopping. This highlights the success of pre-Christmas promotions or Black Friday/Cyber Monday sales. 

  • October 2011 followed closely with £1,062,677.66 (10.85%), fueled by Diwali and Halloween.

  • September was another strong month £1,013,430.68 (10.35%), likely boosted by back-to-school shopping and Chuseok celebrations in East Asian markets. 

  • December 2010 saw significant revenue spikes £760,318.89 (7.76%) due to Christmas, New Year's Eve, and Hanukkah.

  • January 2011 held steady with £580,388.64 (5.93%) despite being a quiet post-holiday month, likely benefiting from residual holiday momentum.

  • April 2011 revenue was lower at £482,901.21 (4.93%) despite Easter and UK Mother's Day. The shorter duration of these events may have limited their sustained impact. 

  • February 2011 brought in £500,510.33 (5.11%), reflecting modest revenue despite Chinese New Year and Valentine's Day, suggesting room to optimize promotions for these events.

Power BI Clustered Column Chart

    5. Daily Revenue 

​

​

​​

  • Significant day-to-day fluctuations in revenue indicate high volatility in the data. The standard deviation of the revenue is £18,956.83, reflecting a wide dispersion of values around the mean and further confirming the presence of substantial variability. Potential reasons for this volatility could include issues with payment processing systems or consumer impulse-buying behavior, often driven by factors such as in-store displays, promotions, or online recommendations.

  • An upward trend in revenue is evident from early September, continuing through November 2011.

  • The top five revenue peaks are as follows:

    • November 14, 2011: £110,806.17

    • September 20, 2011: £108,335.17

    • December 8, 2011: £76,609.09

    • September 15, 2011: £74,098.28

    • November 11, 2011: £68,213.13

Power BI Line Chart

The RFM Segmentation Dashboard provides a detailed understanding of customer behavior by analyzing three key dimensions: Recency, Frequency, and Monetary Value (RFM). This model categorizes customers based on how recently they’ve interacted, how often they engage, and how much they spend. Each dimension is scored on a scale of 1 to 5, with higher scores indicating better performance. For example:

  • R Score: A score of 5 represents a recent interaction, while 1 indicates inactivity over a long period.

  • F Score: A score of 5 signifies frequent engagement, whereas 1 reflects infrequent participation.

  • M Score: A score of 5 means higher spending, while 1 points to lower monetary contribution.

 

By combining these scores, customers are grouped into meaningful segments, as detailed in the logic provided     here. Each of these segments must be targeted with tailored strategies and campaigns to optimize their engagement and value.

In total, we have the following averages across the 4,364 customers analyzed:

  • Recency: 91.67 days

  • Frequency: 5.32 times

  • Monetary Value: £2.24K

This dashboard is structured into five visuals:

  1. Number of Customers by Segment: A treemap that displays the total number of customers in each RFM segment.

  2. Revenue by Customer Segment: A donut chart that breaks down revenue contributions by segment, helping identify the most valuable groups.

  3. RFM Average Overview: A matrix table that provides an aggregated view of average recency, frequency, and monetary values across all customer segments.

  4. Average of Monetary by Customer Segment, Recency and Frequency: A scatter plot that visualizes the spending behavior of customers, showcasing the relationship between recency, frequency, and average monetary values within each customer segment.

  5. Distribution of Recency, Frequency and Monetary: Three clustered column charts that visualize the spread and variability of recency, frequency, and monetary values.

    1. Number of Customers by Segment

​

​

​​

Power BI Treemap Visual

  • A total of 4364 customers are distributed across ten segments. The largest segment is "New Users," comprising 35.79% of the total customers, indicating a significant influx of recent adopters. The "Promising" segment follows with 20.92%, suggesting a substantial group on the verge of becoming loyal customers. Notably, the "Lost" segment represents 13.98%, highlighting a considerable number of customers who have disengaged.

  • The "Hibernating" segment, at 10.95%, indicates a sizable group of inactive but not yet lost customers. Smaller segments like "Champions" (0.09%) and "Loyal" (0.44%) show a limited but highly valuable core of dedicated customers. The "About to Sleep" (4.74%) and "At-risk" (2.47%) segments suggest customers who are at risk of becoming inactive or lost, while the "Cannot Lose Them" segment (1.05%) represents a critical group that must be retained. The "Potential Loyalist" segment (9.56%) indicates customers who are likely to become loyal with the right engagement strategies.

    2. Revenue by Customer Segment

​

​

​​

  • The Donut Chart illustrates the revenue distribution across different customer segments, with the "Potential Loyalist" segment contributing the highest revenue at 32.35%, followed by the "Promising" segment at 21.64%, and the "Champions" segment at 17.42%. These three segments collectively account for over 71% of the total revenue, highlighting their significant financial impact. 

  • The "Loyal" segment contributes 11.92%, while "New Users" bring in 7.74%, indicating that newer customers are already generating notable revenue. The "At-risk" (2.33%), "Hibernating" (2.27%), and "Lost" (1.81%) segments contribute minimally, suggesting that these customers are not currently driving significant revenue. 

  • The "Cannot Lose Them" segment contributes 1.62%, and the "About to Sleep" segment contributes the least at 0.89%, emphasizing the need for targeted strategies to re-engage these customers and boost their revenue contribution.

Power BI Donut Chart

    3. RFM Average Overview

​

​

​​

Power BI Table Visual

  • The data provides valuable insights into customer behavior across segments based on Recency, Frequency, and Monetary Value averages. The "Champions" segment, despite having only 4 customers, stand out as the highest-value group, with an exceptionally low average recency (0.50 days), high frequency (514.25), and monetary value (£426,430.43), underscoring their critical importance to revenue. "Loyal" customers, while engaging less frequently (70.47) than Champions, still contribute significantly (£61,455.46) and show recency of 5.26 days, indicating consistent engagement. "Potential Loyalists" represent a promising segment with moderate recency (13.81 days) and frequency (18.41), paired with a respectable monetary average (£7,597.86). Conversely, "Cannot Lose Them" and "At-risk" segments demonstrate long recency gaps (237.78 and 124.81 days, respectively) and lower frequency, signaling a need for re-engagement to prevent churn.

  • Lower-value segments like "Promising," "New Users," and "Hibernating" present opportunities for growth, although their recency (28.80, 36.32, and 134.82 days) and monetary contributions (£2,320.95, £485.35, and £465.92) require targeted nurturing efforts to move them toward loyalty. Meanwhile, "About to Sleep" and "Lost" customers show alarmingly high recency (227.46 and 287.23 days, respectively) and low frequency, with "Lost" customers contributing the least monetary value (£289.85), highlighting an urgent need for reactivation strategies.

    4. Average of Monetary by Customer Segment, Recency and Frequency 

​

​

​​

Key Trends:

  • Champions: Positioned at the bottom-right (low Recency, high Frequency) with the largest bubble size, reflecting their frequent engagement and significant monetary contribution. Their cluster highlights their critical role as high-value and loyal customers.

  • Loyal Customers: Located near the Champions but with slightly lower frequency and smaller bubble sizes. They exhibit consistent engagement and solid monetary value but still trail Champions in activity level.

  • Potential Loyalists: Positioned moderately in both dimensions, with medium recency and frequency and slightly smaller bubbles. They have the potential to become Champions with targeted nurturing efforts to boost engagement and spending.

  • Promising and New Users: Scattered in the mid to higher Recency range but with lower Frequency. Their smaller bubble sizes reflect their relatively low monetary contributions. These segments indicate opportunities for engagement campaigns to build loyalty.

  • Lost, About to Sleep, and Hibernating: Occupy the top-left region of high Recency and low Frequency, with the smallest bubbles signifying minimal monetary value. These groups represent churned or disengaged customers requiring reactivation strategies.

  • Cannot Lose Them: Despite higher recency, they exhibit a moderate frequency and slightly larger bubbles, suggesting past high-value customers who might return with re-engagement efforts.

Power BI Scatter Visual

    5. Distribution of Recency, Frequency and Monetary

​

​

​​

  • Recency Distribution:

    • The majority of customers show a low Recency value, indicating recent engagement with the business. This suggests that many customers are actively interacting with the store.

    • There is a gradual decrease in the number of customers as Recency increases, with fewer customers making purchases after prolonged periods. This trend highlights a core group of loyal or recently acquired customers.

    • Some outliers with very high Recency values indicate customers who have not made purchases in a long time. 

  • Frequency Distribution:

    • A significant number of customers fall in the lower frequency range (1-5 purchases), indicating that most customers are occasional buyers.

    • There is a gradual decline in the number of customers as Frequency increases, with fewer customers making frequent purchases. However, the presence of customers in higher frequency brackets suggests a group of highly engaged and loyal customers.

  • Monetary Distribution:

    • Most customers' monetary values are clustered within a modest range, indicating that the majority of customers make small to medium-sized purchases.

    • A few outliers in the upper range indicate high-value customers who contribute disproportionately to revenue. 

Power BI Clustered Column Chart

The Retention Analysis Dashboard offers detailed insights into customer loyalty and retention patterns over time, analyzed based on cohort month. A cohort month groups customers by their initial month of purchase, enabling a clear view of how each group behaves and retains over time. To align with the cohort month structure, retention trends are also analyzed on a monthly basis.

​

Customers are segmented into three distinct groups:

  • New: Customers making their first purchase during the month.

  • Returned: Customers making a repeat purchase one month after their last purchase.

  • Recovered: Customers who had lapsed and did not return in the subsequent month(s) but made another purchase in the month.

 

Retained customers consist of both returned and recovered groups. The Retention Rate is calculated to measure the proportion of retained customers using the following formula:

      Retention Rate = (Number of Retained Customers from a prior period ÷ Number of New Customers in that prior period) × 100

​

For example, the Retention Rate in 2011-8 for customers who first purchased in 2011-3 (cohort month: 2011-3) is the proportion of those customers from that cohort, who are buying again in 2011-8.

​

In addition to retention metrics, the dashboard also examines the average ticket size—a key indicator of customer spending behavior and its revenue impact. Across a total of 23.22k transactions, the average ticket size is calculated at £421.79 per transaction.

​

This dashboard is structured into five visuals:

  1. Monthly Retention Rate by Cohort: A heatmap visualizing customer retention rates over time by cohort.

  2. Top Customers: A highlighted table ranking the top revenue-contributing customers.

  3. Monthly Average Ticket Size by Cohort: A heatmap showing how average transaction sizes evolve across cohorts.

  4. Year-Month Revenue and Average Ticket Size: A clustered column and line chart displaying revenue and ticket size trends.

  5. Number of Customers in Year-Month: A stacked area chart tracking monthly customer counts by type.

    1. Monthly Retention Rate by Cohort 

​

​

​​

Power BI Matrix Visual

  • When comparing cohorts, the 2010-12 cohort consistently outperforms others, maintaining higher retention rates throughout the year. In contrast, cohorts from 2011 exhibit lower initial retention rates and greater volatility. This trend suggests that user retention is influenced by the time of year when customers join, with earlier cohorts demonstrating stronger long-term retention overall.

  • Notably, some cohorts, such as 2011-01 and 2011-09, display a gradual increase in retention rates over time. This pattern indicates that certain months might offer better opportunities for user acquisition and retention strategies, potentially influenced by external factors or targeted marketing efforts during those periods.​

    2. Top Customers    

​

​

​​

  • Customers categorized as "UNKNOWN" generated £1,506,019.83, representing a substantial 15.38% of total revenue.  Among identified customers, Customer 14646 (£278,778.02, 2.85%) and Customer 18102 (£259,657.30, 2.65%) are top contributors, with other notable customers, like Customer 16684 (£65,920.12, 0.67%). These high contributions suggest they may primarily be wholesale customers. Given the scale of their transactions, focusing on tailored strategies to retain and expand relationships with these wholesale customers could yield substantial benefits.

  • The revenue percentiles reveal the following thresholds:

    • 25th percentile: £294.25

    • 50th percentile (median): £645.69

    • 75th percentile: £1,597.90

    • 90th percentile: £3,450.66

  • These thresholds indicate that the top 10% of customers contribute significantly higher revenue, confirming a skewed distribution where a small group of customers drives a substantial portion of total revenue.

Power BI Table Visual

    3. Monthly Average Ticket Size by Cohort

​

​

​​

Power BI Matric Visual

  • The 2010-12 cohort sees a steady increase in average ticket size, from £410.32 in December 2010 to a peak of £664.61 in November 2011, indicating strong customer loyalty and upselling opportunities. 

  • Cohorts from January 2011 onwards generally have lower and more volatile ticket sizes. For example, the 2011-02 cohort starts at £320.16, drops to £192.22, and fluctuates throughout the yea. This pattern is seen in other cohorts like 2011-03 and 2011-04.

  • Notably, the 2011-08 cohort shows a significant increase in ticket size towards the year's end, suggesting customers acquired in August 2011 quickly became high-value.

    4. Year-Month Revenue and Average Ticket Size

​

​

​​

  • This chart reveals an inverse correlation between average ticket size and total revenue in most months. While higher average ticket sizes (e.g., £478.71 in September 2011 and £462.16 in December 2011) indicate that customers spent more per transaction, total revenue during these months does not consistently peak. Conversely, months with lower average ticket sizes, such as April 2011 (£331.89), exhibit steadier revenue (£482,901.21), likely driven by higher transaction volumes. This is understandable, as increased transaction volumes typically lower the average ticket size due to the division across a larger number of transactions.

  • Revenue peaks in September, October, and November 2011 are likely the result of a combination of increased transaction volumes and moderate ticket sizes. These months may coincide with seasonal shopping patterns, such as back-to-school promotions, holiday shopping, or end-of-year sales, which drive broader customer participation. Notably, November 2011, with the highest total revenue (£1,432,734.99), demonstrates that moderate ticket sizes (£454.84) can achieve significant revenue when coupled with high transaction volumes.

Power BI Clustered Column and Line Chart

    5. Number of Customers in Year-Month 

​

​

​​

  • From August (167 new customers) to September (299 new customers) and October 2011 (353 new customers), new customer numbers steadily increased, likely driven by seasonal events, holidays, or successful campaigns. Notably, in May (279 new customers) and November (321 new customers), although new customer acquisition declined, campaigns effectively retained older customers. This is evident from the upward trend in the number of returning (398 in May, 684 in November) and recovered customers (400 in May, 702 in November).

  • Note: The sharp drop in new customers in January 2011 (420 new customers) is because the dataset starts in December 2010. While December’s 948 customers may seem entirely new, they could include returning customers from prior operations. However, without confirmation, we must assume December 2010 represents the first month of business.

Power BI Stacked Area Chart

© Jasmine Abtahi 2025

bottom of page