Decision Interface Report – Sephora
Product Intelligence Dashboard (Power BI)
Submitted by:
Sandhya Rani. Yellapu
Course / Module:
Data Visualization and Decision Interfaces
Institution:
Badruka School of Management
Submission Date:
October 23rd, 2025.
EXECUTIVE SUMMARY
This decision interface supports Sephora’s product, it’s brand and their marketing leadership
in tracking product performance, customer engagement and pricing dynamics across multiple
beauty brands and categories. It converts Sephora’s raw product catalog data from KAGGLE,
into a structured, visual and interactive analytics layer that highlights how customers respond
to a different brand, price range and engagement attributes. The dashboard enables quick
insight into product strength, brand consistency and emerging trends in consumer
preferences.
PRIMARY OBJECTIVES -
• Optimize category mix: By identifying which categories (Skincare, Makeup, Hair,
Fragrance) achieve higher average ratings and review volumes.
• Refine pricing strategy – By detecting mid-range products clusters that deliver the
highest average ratings (> or equal to 4.2) and customer satisfaction.
• Enhance brand engagement – By focusing on top-performing brands such as AMIKA,
The Ordinary, and Fenty Beauty, which show consistently high “love” counts and
review metrics.
• Evaluate exclusivity performance – By analyzing whether exclusive or limited-edition
products drive better engagement or narrower niche appeal.
DATA OVER VIEW-
This dataset has hundreds of beauty brand products across multiple categories and over 2
million reviews from this data include:
Average Rating – 3.99, Average Price – 50.21, High-performing categories – Skincare and
Fragrance, Engagement clusters – Mid-priced items show the best combination of rating and
review volume.
These measures help quantify value perception, price efficiency and brand loyalty
transforming descriptive catalog data into business -ready KPIs.
OPERATIONAL CADENCE-
Refresh frequency – Manual
Recommended update – First week of every month
Data Structure – One record per product, metric updated through power BI data model
INTENDED AUDIENCE-
Brand Manager – Review and monitor KPIs (Identify the top performing brands)
Category Lead – Drill through and compare segments (Evaluate category balance and
customer sentiment)
Marketing Analyst – Author and explore data (Correlate engagement with rating and price)
PERSONAS AND DECISION QUESTIONS
The Sephora Product Intelligence Dashboard is designed to support data-driven decision
making across key personas within the brand ecosystem – The Brand Manager, Category
Lead and Marketing Analyst. Each question below is answered using metrics and
relationships visualized in your Power BI dashboard
BRAND MANAGER -
Dashboard use: Multi-Row Card (top brands by love), Stacked Bar (Exclusivity Mix).
1Q) Which brands are most loved and have the best reviews in their respective categories?
Ans - Amika, The Ordinary and Fenty Beauty lead across most engagement metrics. Amika
drives the highest “LOVE” interactions, dominating Hair care, The Ordinary holds
consistently high ratings with steady review volumes in Skincare and Fenty Beauty excels in
makeup, combining premium pricing with large review bases.
2Q) Are exclusive or limited-edition products driving higher engagement?
Ans - Exclusive lines show a slight uplift in average ratings but lower review volumes.
Limited editions attract curiosity spikes yet have mixed rating stability.
3Q) Which brands should be maintained as strategic priorities?
The top 3 brands mentioned above combine strong ratings, high love counts and stable
pricing from Sephora’s most reliable performance base.
CATEGORY LEAD-
Dashboard use: Matrix, Line Chart, Ribbon Chart, Scatter
4Q) Could you please provide information on the categories that demonstrate the highest
average ratings and the most favourable price-to-value ratios?
Ans - Skincare and Fragrance maintain the highest ratings at moderate prices. Bath & Body
and accessories trail below 3.8 despite similar or lower pricing. Value perception peaks in
mid – priced skincare and fragrance which is ideal for promotional investment.
5Q) How do product types differ in price – rating relationships?
Ans - Scatter visuals reveal no strong positive correlation, beyond 80$. Makeup shows wide
price dispersion, Skincare stays stable with high ratings across price bands. Consumer reward
perceived effectiveness over luxury pricing, affordability with quality which drives
satisfaction.
6Q) Which categories have been outperforming consistently then others?
Ans - Within skincare, serums and moisturizers ate top ratings.
MARKETING ANALYST-
Dashboard Use: Scatter Plot
7Q) Which price ranges correspond to top – rated products?
Ans - Products priced between 30 and 60$ achieve the highest average ratings and strongest
love counts. Low-price items show moderate ratings, ultra-luxury. The mid-tier “affordable
luxury” band drives peak satisfaction.
8Q) Which factors most influence product rating?
Ans- Number of reviews – highest positive correlation with rating, Price – mild inverse
effect beyond 80$, Exclusivity – small positive lift and Love count – supports after threshold.
Social proof through reviews drives ratings more powerfully than price or exclusivity
9Q) How can engagement insights guide marketing strategies?
Campaigns emphasizing authentic reviews and user testimonials yields higher rating stability
then discount – driven promotions. Product with balanced pricing and high engagement are
ideal for influencer collaborations and re-marketing pushes.
KPIs
The dashboard translates raw Sephora catalog data – including price, ratings, reviews and
engagement metrics into a compact KPI framework that captures customer satisfaction,
pricing dynamics and engagement intensity. These KPIs appear as cards and gauges on the
main overview page
AVERAGE RATING-
Definition – mean of all product ratings (AVERAGE(Sephora[rating]))
Purpose – primary indicator of perceived product quality and customer satisfaction
Target - > or equal to 4.0 (GOOD) and > or equal to 4.3 (PREMIUM BRANDS)
Current Value – 3.99
Visualization – Green > or equal to 4.0, Amber 3.5-3.9 and Red < 3.5
AVERAGE PRICE -
Definition – mean product selling price (AVERAGE(Sephora[price]))
Purpose –Assess affordability and value for money balance
Target - $30-$60
Current Value – 50.2$
Visualization –Line Chart vs Rating to evaluate price – quality relationship
TOTAL REVIEWS-
Definition – sum of customer review counts (SUM(Sephora[number_of_reviews]) )
Purpose – Measure social proof and engagement volume
Current Aggregate - >2 million reviews across all products
Visualization –Bubble size in Scatter plots.
AVERAGE LOVE-
Definition – Mean od the “LOVE” metric (AVERAGE(Sephora[love]) )
Purpose – Represents emotional engagement
Usage – Used in ranking cards
Interpretation – Higher love count indicates loyal customer base
PRODUCT COUNT-
Definition – COUNT(Sephora(id) )
Purpose – Represents the total catalog size or brand assortment scope
Visualization – Base value for share calculations in donut chart.
DATA QUALITY CHECKS-
No null values in price or rating fields
Rating range : 0-5 verified
Price Validation : Positive values only
DATA AND MODEL
Source – Sephora Product Dataset (KAGGLE)
Grain – 1 row = 1 product listing
Core columns: Brand, Category, Name, Size , Price , Rating , Number of reviews , Love ,
Exclusive , Online only , Limited Edition , Value Price
Data Table – A simple data dimension with year , month m and name of the month for
version tracking and trend comparison during future dataset refreshes.
Measure (DAX):
Average Rating = AVERAGE('Sephora'[rating])
Average Price = AVERAGE('Sephora'[price])
Total Reviews = SUM('Sephora'[number_of_reviews])
Average Love = AVERAGE('Sephora'[love])
Product Count = COUNT('Sephora'[id])
PAGE STRUCTURE AND WIREFRAME
An Executive Overview (for upper management)
Header: Title (“Sephora Product Intelligence Dashboard”), refresh date/time, global slicers
(Category, Brand, Price Range).
KPI Row (4 Cards): Avg Rating |Avg Price |Total Reviews |Avg Love.
Drivers: Donut – Category Composition (% of total products); Tree map – Brand Share
within Each Category.
Trend: Line – Avg Price vs Avg Rating per Category.
Concentrate Table: Top 10 Items (Brand, Category, Price, Rating, Love).
Interactions: Slicers cross-filter all visualizations; Clicking on a
Category flows through to Tree map and Focus Table.
Brand & Category Insights (for category leads)
Matrix: Category → Brand with Avg Rating, Avg Price, Total Reviews, Avg Love.
Ribbon Chart: Brand Ranking Changes in Different Categories.
Clustered Bar: Avg Rating by Category.
Multi-Row Card: Brands Loved Most by Everybody.
Interaction: Clicking on a brand updates the matrix context and all visuals filter by that brand.
Engagement & Customer Behaviour (for marketing analysts)
Funnel: All Products → Those Reviewed → Those Loved → Those Highly Rated.
Waterfall: How Engagement Factors (whether Reviews, Love or Exclusivity) Affect Ratings.
Stacked Bar: Product Availability (Exclusive or Non-exclusive) by Category.
Decomposition: Analyse = Rating
NAVIGATION AND USABILITY
Navigation : Top navigation buttons on every page – Overview / Brand Insights /
Engagement / Exploration.
Bookmarks : “High Love Products” , “Exclusive Only” , “Top Rated” for quick recall
Drill through paths : From Category to Brand to Product Detail for deeper exploration .
Accessibility and Design : High contrast theme with consistent colour coding by category ,
Labels and titles use > or equal to 12 font for readability , ALT text added to visual elements
for clarity and dynamic colour indicators like green red and amber
User roles and permissions : Management – Read only view (KPIs focus) , Category Leads –
Full navigation with drill through enabled , Marketing Analysts – Authoring and insight
creation access.
Interactivity : All visuals cross filter each other for contextual insights , smart narratives auto
update when filters are applied and tooltips reveal price , rating and loci comparisons.
DECISION RULES AND ALERTS
ALERT 1: Rating drop alert – if Avg rating is below 3.8 for any category
ALERT 2: Pricing Outlier alert – If Avg price is greater than 100 for any category with Avg
rating 4
ALERT 3: Engagement decline alert – If Avg love decreases by 20 % month on month for
any brand
ALERT 4: Exclusivity performance check – If exclusive products are rated 4 on average
ALERT 5: High performer recognition – if the product rating is above 4.5 and review counts
more than 500 .
WHAT-IF & SCENARIOS
1) Premium push – Increase prices of exclusive products ( then average rating decreases)
2) Value realignment – Reduce prices of mid-range products by 5% ( review count increases ,
and average rating improves)
3)Engagement Campaign – Launch “love it and & review it” challenge to raise customer love
counts ( average rating will increase and engagement conversion rate also improves )
4)Exclusivity experiment – Introduce limited edition version of top 10 products
(Average rating increase)
SCENARIO COMPARISION VISUAL:
Card – NRI %, Table – Scenario Avg Rating | Average Price | Reviews | Engagement %
GOVERNANCE AND QUALITY
OWNER – Sephora analytics & Insights team
STORAGE – Power BI with transformations via Power Query
VALIDATION CHECKS:
- Rating values must be within the range of 0 to 5.
- Prices must be verified as positive decimal values.
- Null or missing values in fields (price, rating, reviews) will be removed prior to data
loading.
- Brand and category fields will be trimmed and standardized.
- Outliers will be flagged where the price exceeds 500 USD.
- The sum of Total Reviews will be cross-checked against the raw dataset with an acceptable
margin of error of 0.1%.
CHANGE CONTROL:
- Each dashboard iteration will be versioned in GitHub (PBIX + Theme JSON).
- Release notes will be maintained for all new measures and visuals.
- Peer review is required for any new KPI addition or formula modification.
DATA SECURITY:
- Management and Category Leads will have read-only access.
- Edit permissions are restricted to Analysts.
- Sensitive metrics, including pricing data, will be protected through row-level security.
QUALITY ASSURANCE:
- Consistency checks on DAX measures post-refresh.
- Verification of visual alignment under default and filtered states.
- Monthly performance audits, including render time and refresh validation.
PERFORMANCE CHECKLIST
-Used Explicit Dax measures instead of implicitaggregartions
- Limited Tables with Top 10 brand products with conditional formatting
- Avoided Bi- directional relationships
- Summarized high cardinality columns through drill pages instead of displaying full
product lists
ROLLOUT PLAN
- Prototype the developed by using Kaggle Sephora dataset.
- User testing with Brand Managers, Category Leads, and Marketing Analysts to
capture feedback.
- Connect to updated monthly data source; apply role-based access (Category-level).
- Document data dictionary, DAX measure catalogue, and usage guide.
- Go-live with a short training session and walkthrough for all stakeholders.
APPENDIX
Measure Catalogue
- Avg Rating, Avg Price, Total Reviews, Avg Love, Product Count, Profitability Index, High-
Rating Share, Exclusive Impact
Drill Paths
- Category | Brand | Product Detail
- Engagement | Reviews | Rating
Mock Targets
- Category Ratings: Skincare ≥4.3, Makeup ≥4.1, Hair ≥4.2, Fragrance ≥4.0
- Avg Price Range: $30–$60 optimal satisfaction band