This project involves a dataset of sales and orders from a laptop importing company, containing information such as Order_ID, Branch, Order_Date, Order_Priority, Manufacturer, Model_Name, Category, Screen_Size, Screen, CPU, RAM, Storage, GPU, OS, OS_Version, Weight, Price, Quantity, Discount, Total_Price, Profit, and Ship_Duration. The dataset includes 1,017,081 recorded orders spanning from the year 1390 to 1402 (Solar Hijri calendar).

Note: Due to high inflation rates in Iran and the fluctuating value of the Toman/Rial relative to the dollar, we used a more stable exchange rate for our analysis. Additionally, laptop manufacturers have specific production policies for each year, producing models with varying price ranges. To facilitate our analysis, we obtained the USD to Toman/Rial exchange rates from a specific API based on the order dates. It's important to consider that some dates may lack exchange rates due to official market closures, in which case, we use the previous day's rate.

After examining the available data, the first step is to store the data in a normalized and structured way in the database. Entity identification is performed, and tables are designed based on these entities. Each entity has attributes that name the columns of each table. Relationships between tables are established for optimal join performance, considering time efficiency. Unique values are stored in each table to address the high complexity in the dataset.

Following the design and storage of data in the database, the next step is to design a suitable data warehouse. Power BI is used for this purpose, employing the Star schema design principle. Fact tables, containing transaction-oriented data with a large volume, are utilized, while Dimension tables contain descriptive data.

The project involves extracting data from competitors' websites to identify and complete missing products. Additionally, pricing strategies and current company policies are analyzed for potential adjustments.

Competitor information is available at link1, link2.

To meet the company's needs, a dashboard is created to display the current status of the company and address specific requests. Power BI is employed for dashboard creation.

Download the Power BI output at link.

To increase customer base, the company plans to allocate a budget for advertising. We recommend cities with higher sales relative to their population and better profitability for targeted advertising.

The company wants to assess if discounts affect sales and how. T-tests are used to determine the significance of these effects. Data is divided into two sections: with and without discounts, and the average sales in both sections are compared.

Building on the previous request, the company is concerned about potential profit reductions due to discounts. The average profitability is used for dashboard representation, especially during high-cash-volume orders.

The company seeks advice on allocating capital among different laptop brands and categories. Profitability for each brand in specific categories is calculated, and 20% of the profit is considered for product import and inventory completion.

Considering the company's return to the market after a long period, it needs a solution for predicting laptop prices based on technical specifications. Machine learning techniques are applied to predict prices based on influential features.

The company plans to import goods monthly and wants to know if there is a difference in sales across different months. If differences exist, the company seeks guidance on how to allocate its annual budget among these months.

To address this request, a prediction is made that sales may perform well in months at the end of the year or the beginning of the following year (e.g., Esfand and Farvardin). The popularity of sales in each month is determined based on obtained outputs, aligning with predictions.

In the past, the company had a policy favoring certain priority levels, impacting various aspects such as shipping methods. However, this policy has proven challenging. The company requests an assessment of whether maintaining this policy is justified based on the average profitability under these conditions.

From the obtained average profitability results, it is evident that high priority levels or short order durations have a minimal impact on profitability.

Examine which laptop feature is the most crucial factor for customer purchases.

To answer this request, it is essential to consider that the most critical factor during purchases is the price. Eliminating its influence allows us to identify other features that attract or repel customers. For instance, an increase in CPU frequency, screen resolution, or RAM size may attract more customers, while an increase in weight or screen size may deter them.

In this section, an alerting system is implemented based on Key Performance Indicators (KPI) and predefined thresholds. It compares the average sales for past years or months with the current year or month. If the average falls below the threshold, an alert is triggered, indicating a warning state. Conversely, if it exceeds the threshold, it signifies a stable state.

The current status is undetermined for the current year as the sales data for the current year is incomplete. However, for the current month, the system indicates a stable state.

Total sales are examined for the current month, season, and year, both in dollars and Iranian rials.

Total profits are analyzed for the current month, season, and year, both in dollars and Iranian rials.

Sales and profits are evaluated across various cities, presented in both dollars and Iranian rials.

A dynamic menu allows managers to select specific cities, years, months, and days to observe the changing trends in sales and profits.

Another menu enables managers to choose specific laptop brands and categories to visualize changing trends.

In this section, a menu is provided for the user to select specific cities and time intervals (month and year). Based on the user's selection, various outputs are updated accordingly.

1. Overview of Sales, Orders, and Averages: The total sales, order count, average profit of all orders, and the average sales and profit of all orders are displayed based on the user's selected criteria.

2. Popularity of Brands and Categories: The popularity of each brand and category is visualized to provide insights into customer preferences.

3. Popularity of CPU and GPU Models: The popularity of each CPU and GPU model is depicted to understand customer preferences in terms of hardware.

4. Order Priority and RAM Size Popularity: The priority of orders and the popularity of RAM sizes are illustrated to help identify trends in customer choices.

5. Sales and Order Count by Province: The sales and order count are presented for each province, offering a regional perspective.

6. Impact of Discounts on Sales Count: The effect of discounts on the number of sales is highlighted, providing insights into customer behavior.

7. Popularity of Screen Sizes: The popularity of different screen sizes is visualized to understand customer preferences.

8. Order Dispersion by Laptop Price: The dispersion of orders based on laptop prices is showcased, revealing the distribution of customer preferences.