Background:
DataCo Analysis Group, a leading consultancy in business analytics, specializes in providing comprehensive insights into various industry sectors. This case study, "Streamline & Spotlight," focuses on the intersection of supply chain operations and digital consumer behavior. With a detailed dataset encompassing supply chain metrics (DataCoSupplyChainDataset.csv) and digital access logs (TokenizedAccessLogs.csv), DataCo Analysis Group plays an instrumental role in helping businesses optimize their operations and understand digital engagement. The supply chain dataset offers insights into sales, logistics, and delivery performance, while the access logs shed light on consumer online interactions and preferences. In an era where efficient supply chain management and robust digital presence are crucial for business success, analyzing these datasets becomes imperative to uncover strategies for operational excellence and enhanced digital outreach.
Objective:
The objective of this case study is to conduct an in-depth analysis of the combined supply chain and digital access datasets provided by DataCo Analysis Group. Students will utilize advanced Excel techniques to dissect these datasets, revealing key patterns and insights. The primary tasks include data cleaning, integration, analysis, and the development of a comprehensive, interactive dashboard. This dashboard will serve as a critical tool in visualizing supply chain efficiencies, customer engagement metrics, and the synergy between them. The project aims to augment DataCo Analysis Group's consulting capabilities, offering actionable insights to clients for optimizing supply chain processes and improving digital marketing strategies. This analysis is also expected to contribute to a broader understanding of how supply chain and online consumer behavior are interlinked, thereby influencing strategic decisions in product management, marketing, and customer relationship management.
1.Date Analysis: The average shipping delay (difference between 'Days for shipping (real)' and 'Days for shipment (scheduled)') for each product category.
2.Profit Margin Calculation: The profit margin for each order, categorized as 'High', 'Medium', or 'Low'.
3.Customer Geographic Analysis: The distribution of customers across different cities and countries.
4.Sales Trend Analysis: Monthly sales trends over the years and peak sales months.
5.Top 5 products: The top 5 products with the highest sales.
6.Product Popularity Index: Popularity index to rate product popularity based on sales volume and frequency.
7.Dynamic Data Range for Sales Analysis: Total sales for each category.
8.Departmental Traffic Analysis: Analyze which department's products are most frequently accessed using COUNTIF and SUMIF functions.
9.Most Visited URLs: The most frequently visited URLs.
10.Product Interest vs. Sales Performance Analysis: The frequency of product views from the Access Logs dataset with the sales data for the same products in the Supply Chain dataset.
11.Customer Geographic Interest Analysis: Match the customer cities from the Supply Chain dataset with IP addresses from the Access Logs dataset (approximate analysis due to the nature of IP geolocation). Analyze which cities show the most online engagement compared to actual sales.
12.Time Series Analysis of Product Interest and Sales: Compare the monthly trends in product views from the Access Logs dataset with the monthly sales trends of those products in the Supply Chain dataset. Identify any lag or lead relationship between interest and sales.
Leverage Excel's data visualization and interactive tools to create an insightful and dynamic dashboard. This dashboard should provide a clear and interactive overview of key performance indicators (KPIs), trends, and insights in the supply chain and web access areas. The goal is to develop a tool that facilitates data-driven decision-making and offers a comprehensive understanding of the underlying business processes.
