Data Mining And Predictive Modeling For Sales Analysis

Research Methodology

What is the problem?

Computer or Laptop is the essential things for everyone. Today we can get the laptop easily by online store. We can found many online store for purchasing desired laptops. We can get the desired product within time at home door.  As the online shopping business increasing exponentially it invites opportunities and challenges for the service provider. Business competition and customer satisfactions are key challenges before all the service provider. Wolfinbarger and Gilly (2001) reported that “consumers report that shopping online results in a substantially increased sense of freedom and control as compared to offline shopping.”  Lee and Lin (2005) developed the research model for examining the relationship among e?service quality dimensions and overall service quality, customer satisfaction and purchase intentions. Koufaris and Hampton-Sosa  (2004) studied the development of trust among the customers for online purchase. Kim and Forsythe (2010) studied the factors affecting adoption of product virtualization technology for online consumer electronics shopping.

For this case study, we have developed the data. We have data regarding the sale of laptops (1600 products) in the month. We considered the following attributes

1. Product Name
2. Product Price (in $)

• Profit (in $)

1. Sale Price (in $)
2. Number of customers who bought this products
3. Shipping Type (Free or Paid)

• Customer Type (New or Existing)
• Region (NSW, QLD, WA, VIC, TAS, SA)

1. Product Brand (HP, Dell, Lenevo and Sony)
2. CPU Type (i3, i5 and i7)
3. Operating System (Windows 7 and Windows 10)

• Screen Size in inches (11.3, 14, 15.6 and 17.3)
• Memory Size (2GB, 4GB, 6GB and 8GB)
• Hard Disk (500GB, 1TB and 2TB)

1. Touch Sensor (TouchScreen and No TouchScreen)

We defined the following variables for the study objectives as

Total Monthly sale amount (in $) = Sale Price (in $) × Number of customers

Total monthly profit (in $) = Profit (in $) × Number of customers

We concentrate on the following

1. Profit analysis by shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor.
2. Whether the mean number of customers are significantly different for different shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor.

• Correlation analysis of variables

1. Regression analysis for total monthly sales

In literature, we can find many tools and techniques for the data analysis. But analysis through statistical tools and techniques provide strong base to the analysis.

We presented the total monthly sale amount (in $) and total monthly profit (in $) along with profit percentage for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor in Profit analysis. We reported the descriptive statistics for number of customers for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor. We used t-test and one way ANOVA for the testing the difference between levels of shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor. We carried the correlation analysis to study the correlation between variables. We predict the total monthly sale by regression analysis. We used Python 3.6.5 and MS-Excel for the data analysis. The sample code are given in appendixes.  We used Grus (2015), McKinney (2012), Zelle (2004), Monk (2015), Hammond and Robinson (2000), Chun (2001), Pedregosa et al. (2011) and Schutt and O’Neil (2013)

In this section, we carried the following

• Profit Analysis
• Description Statistics
• Two sample t test
• One way ANOVA
• Correlation Analysis
• Regression Analysis

We presented the total monthly sale amount (in $) and total monthly profit (in $) along with profit percentage for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor in Profit analysis. We referred Berenson et al. (2012), Black (2009), Groebner et al. (2008), Kvanli et al. (2000) and Mendenhall and Sincich (1993). In Table 1, we have reported the total monthly sale amount (in $) and total monthly profit (in $) along with profit percentage for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor.

Analytical Findings

Table 1: Profit analysis according to different attributes

Attributes	Levels	Total Monthly Sale (in $)	Total Monthly Profit (in $)	Profit %
Shipping Type	Free	3093026.5	152231.5	4.92%
Paid	6951139.9	353702.9	5.09%
Customer Type	Existing	1015929.8	49780.8	4.90%
New	9028236.6	456153.6	5.05%
Region	NSW	1884071.5	91895.5	4.88%
QLD	1774342.6	89607.6	5.05%
SA	1871543.8	94747.8	5.06%
TAS	1812892.9	90971.9	5.02%
VIC	943429.9	50029.9	5.30%
WA	1757885.7	88681.7	5.04%
Brand	Dell	2135337.4	99608.4	4.66%
HP	2105471.1	104055.1	4.94%
Lenevo	2537893.5	157358.5	6.20%
Sony	3265464.4	144912.4	4.44%
CPU Type	i3	2929859.6	158438.6	5.41%
i5	3019792.6	152197.6	5.04%
i7	4094514.2	195298.2	4.77%
Memory Size	2GB	2213641.6	119639.6	5.40%
4GB	2201859.5	113845.5	5.17%
6GB	2586779.8	127227.8	4.92%
8GB	3041885.5	145221.5	4.77%
Operating System	Windows 10	4989962.9	255102.9	5.11%
Windows 7	5054203.5	250831.5	4.96%
Hard Disk	1TB	3434552.6	175334.6	5.11%
2TB	3593685.9	168929.9	4.70%
500GB	3015927.9	161669.9	5.36%
Screen Size (in Inches)	11.6	2531037.1	139326.1	5.50%
14	2309630.8	120317.8	5.21%
15.6	2428520.4	119635.4	4.93%
17.3	2774978.1	126655.1	4.56%
Touch Sensor	No TouchScreen	4749528.9	242770.9	5.11%
TouchScreen	5294637.5	263163.5	4.97%
Total		10044166.4	505934.4	5.04%

From Table 1 we can claim that company earns on average 5.04% profit on each laptop.  Free shipping gives (4.92%) profit less than paid shipping (5.09%). New customers produces 5.05% profit whereas existing gives 4.90%. In the region, VIC gives 5.30% profit whereas NSW produces only 4.88% profit. In the brand, Lenevo produces 6.20% whereas Sony only gives 4.44% profit. In CPU type, i3 gives more profit and i7 gives less profit. Profit percentage decreases as memory size increases. In operating system, Windows 7 produces less profit than Windows 10. Profit percentage increases as memory size and screen size decreases. Company earns more profit in non-touch screen laptops than touch screen laptops.

For any business, total sale and total profit can be maximized by attracting more customers by different marketing strategies. In this subsection, we look at the some summary statistics for number of customers who bought the laptops for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor. We used the well-known books for this section such as Ivezi? et al. (2014), Lind (2013), Bickel  and Doksum (2015), Casella and Berger (2002), DeGroot and Schervish (2012), Hodges Jr and Lehmann (2005), Papoulis (1990), Pillers (2002), Moyé et al. (2017), Larsen and Marx (2017), Devore and Berk (2007), Rao et al. (1973) and Ross (2014). Table 2 represents the size, mean, standard deviation, minimum and maximum of number of customers for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor.

Table 2: Summary statistics for numbers of customer for shipping type, customer type, region, brand, CPU type, operating system, screen size, memory size, hard disk size and touch sensor

Attributes	Levels	Size	Mean	Standard Deviation	Min	Max
Shipping Type	Free	499	7.59	3.48	1	23
Paid	1101	7.72	3.26	1	21
Customer Type	Existing	163	7.58	3.33	2	20
New	1437	7.69	3.33	1	23
Region	NSW	303	7.57	3.51	1	21
QLD	290	7.50	3.12	1	17
SA	296	7.71	3.16	1	18
TAS	269	8.17	3.13	1	21
VIC	167	6.94	3.39	1	17
WA	275	7.92	3.59	1	23
Brand	Dell	375	7.12	2.99	1	17
HP	406	6.93	3.01	1	17
Lenevo	403	7.29	3.04	1	17
Sony	416	9.29	3.66	1	23
CPU Type	i3	536	7.13	3.06	1	21
i5	525	7.07	3.01	1	19
i7	539	8.83	3.58	1	23
Memory Size	2GB	396	7.55	3.48	1	23
4GB	390	7.21	2.92	1	16
6GB	422	7.30	3.19	1	19
8GB	392	8.69	3.51	1	21
Operating System	Windows 10	804	7.68	3.25	1	21
Windows 7	796	7.68	3.41	1	23
Hard Disk	1TB	510	8.24	3.21	1	21
2TB	555	7.49	3.47	1	20
500GB	535	7.34	3.23	1	23
Screen Size (in Inches)	11.6	408	8.31	3.48	1	23
14	393	7.38	3.30	1	20
15.6	387	7.47	3.25	1	21
17.3	412	7.53	3.22	1	18
Touch Sensor	No TouchScreen	811	7.31	3.26	1	23
TouchScreen	789	8.06	3.36	1	21
Total		1600	7.68	3.33	1	23

From Table2, we can observed following

1. Company get averagely 7.68 customers for each laptop
2. mean number of customer for the laptops which has free shipping is less than those who has paid shipping

• mean number of customers for laptops in VIC region is less and in TAS it is more.

1. mean number of customers for HP brand laptops is less and for Sony brand it is more.
2. mean number of customers for laptops having i7 CPU type is more than other CPU type
3. mean number of customers for laptops having memory size 8GB is more than other laptops.

• mean number of customers for laptops having Windows 7 and Windows 10 is same. Variation in the number of customers for laptops with Windows 7 operating system is more than Windows 10.
• More number of customers preferred the laptops with hard disk capacity 1 TB than other laptops.

1. Laptops with small screen (11.3 inches) are more preferred among the customers.
2. Laptops with touch sensor preferred over non sensor.

Here we are interested to know whether there is significant difference between the mean of number of customers for shipping type, customer type, operating system and touch sensor. We have following null and alternative hypothesis.

Null hypothesis: There is no significant difference between mean of number of customers for levels of attributes.

Alternative hypothesis: There is significant difference between mean of number of customers for levels of attributes.  

We used two sample independent test assuming unequal variances for testing above null and alternative hypothesis. Table 4 represents the value of test statistic and p-value of two sample independent test assuming unequal variances.

Table 4: Two sample independent test for shipping type, customer type, Operating system and Touch Sensor

Attributes	Levels	Test Statistic	p-value
Shipping Type	Free and Paid	-0.74	0.461
Customer Type	New and Existing	-0.41	0.681
Operating System	Windows 7 and Windows 10	0.02	0.985
Touch Sensor	TouchScreen and No TouchScreen	-4.55	0.000

From Table 4 we observed that

1. There is no significant difference between mean of number of customers for laptops which shipped freely and for which paid shipment.
2. There is no significant difference between mean of number of customers for laptops purchased by new customers and existing customers.

• There is no significant difference between mean of number of customers for laptops with Windows 7 and Windows 10 operating system.

1. There is significant difference between mean of number of customers for laptops with touch screen and without touch screen.

In this subsection, we are interested to know whether there is significant difference between the mean of number of customers for region, brand, CPU type, Memory size, Hard disk size and Screen size. We perform one way ANOVA for testing the following null and alternative hypothesis.

Null Hypothesis: There is no significant difference between mean of number of customers for levels of attributes

Alternative hypothesis: There is significant difference between mean of number of customers for levels of attributes.

Profit Analysis

Table 5, represents the F statistic and P value of one way ANOVA for region, brand, CPU type, Memory size, Hard disk size and Screen size.

Table 5: Output of one way ANOVA for region, brand, CPU type, Memory size, Hard disk size and Screen size

Attributes	Level	F Statistic	P Value
Region	NSW, QLD, WA, VIC, TAS and SA	3.33	0.005
Brand	HP, Dell, Lenevo and Sony	48.65	0.000
CPU Type	i3, i5 and i7	51.25	0.000
Memory Size	2GB, 4GB, 6GB and 8GB	17.06	0.000
Hard Disk Size	500 GB, 1TB and 2TB	11.15	0.000
Screen Size (in inches)	11.1, 14, 15.6 and 17.1	6.86	0.000

From Table 5, we observed that there is significant difference between the mean of number of customers for different region, brand, CPU type, Memory size, Hard disk size and Screen size. Mean number of customers are relatively less in VIC region than other region. Number of customers is more for laptops having following characteristics

Brand: Sony

CPU type : i7

Memory Size : 8GB

Hard Disk: 1TB

Screen Size: 11.3 inches

Here we studied the correlation between product price, Sale price, profit and number of customers. Table 6 shows the correlation matrix for product price, Sale price, profit and number of customers.

Table 6: Pearson’s correlation coefficient for Product Price, Sale Price, Profit and Numbers of customers

	Product Price	Sale Price	Profit	Numbers of customer
Product Price	1	0.994	0.130	0.186
Sale Price	0.994	1	0.240	0.183
Profit	0.130	0.240	1	0.011
Numbers of customer	0.186	0.183	0.011	1

From Table 6, we observed that product price is positive correlated with sale price, profit and number of customers, sale price is positively related with profit and number of customers and profit is also positively correlated with number of customers.

We used simple linear regression model for predicting the monthly sale using number of customers as predictor variable. Table 7 represents the F Statistics, P value, R² and regression coefficients of simple linear regression.

Table 7: Output of Regression Analysis

F Statistic	14851.58
P Value	0.000
R2	0.903
Intercept	-407.8
Slope	870.6

We observed that P Value =0.000 suggests that there is significant relationship between total monthly sale and number of customers. We also observed R² as 0.903 suggests that model fitting is adequate. We fitted the following straight line as

Total sale (in $) = -407.8 + 870.6  × Number of Customers

Each additional customer gives the $870.6 sale to the company.

1. Mean number of customers for VIC region is less than other region. Company should focus on attract the customers from VIC region.
2. For increase of sale company should give the priorities to the laptops with Sony brand, i7 CPU type, 8GB memory, 1TB hard disk and small screen as the mean number of customers for laptops with this characteristics is significantly more than others.

• As it is electronic, costly products customers for free shipping and paid shipping is not significantly different.   

1. Company needs to attract the number of customers so that profit will be maximize.
2. Company should focus on products which gives more profit.

An implementation plan based on the recommendations you have provided

1. Company can use more marketing techniques in VIC region to increase the customers from VIC region.
2. Company should keep stock of laptops which has high demand.

• Company should give the assurance that shipping of product is safe so that more customer got interest in buying the laptops online.  

1. Company can be used more advertising hording to know about company and products.

Conclusions

We observed that company earns on average 5.04% profit on each laptop. Free shipping gives (4.92%) profit less than paid shipping (5.09%). New customers produces 5.05% profit whereas existing gives 4.90%. In the region, VIC gives 5.30% profit whereas NSW produces only 4.88% profit. In the brand, Lenevo produces 6.20% whereas Sony only gives 4.44% profit. In CPU type, i3 gives more profit and i7 gives less profit. Profit percentage decreases as memory size increases. In operating system, Windows 7 produces less profit than Windows 10. Profit percentage increases as memory size and screen size decreases. Company earns more profit in non-touch screen laptops than touch screen laptops. Company get averagely 7.68 customers for each laptop.

We observed no significant difference between mean of number of customers for shipping type, customer type and operating system whereas we observed there is significant difference between mean of number of customers for laptops with touch screen and without touch screen. We observed that there is significant difference between the mean of number of customers for different region, brand, CPU type, Memory size, Hard disk size and Screen size. We also observed that profit is positively correlated with number of customers. From the regression analysis, we observed that monthly sale and number of customers are significantly related with each other.

Descriptive statistics for number of customer

We have also provided recommendations and plan for company.

References

Bachmeyer, C., Bensaid, P., & Bégon, E. (2009). Laptop computer as a modern cause of erythema ab igne. Journal of the European Academy of Dermatology and Venereology, 23(6), 736-737.

Berenson, M., Levine, D., Szabat, K.A. and Krehbiel, T.C., (2012). Basic business statistics: Concepts and applications. Pearson higher education AU.

Bickel, P.J. and Doksum, K.A., (2015). Mathematical statistics: basic ideas and selected topics, volume I (Vol. 117). CRC Press.

Black, K., (2009). Business statistics: Contemporary decision making. John Wiley & Sons.

Carroll, R. J. (2017). Transformation and weighting in regression. Routledge.Casella, G. and Berger, R.L., (2002). Statistical inference (Vol. 2). Pacific Grove, CA: Duxbury.

Chun, W. (2001). Core python programming (Vol. 1). Prentice Hall Professional.

DeGroot, M.H. and Schervish, M.J., (2012). Probability and statistics. Pearson Education.

Devore, J. L., & Berk, K. N. (2007). Modern mathematical statistics with applications. Cengage Learning.

Groebner, D.F., Shannon, P.W., Fry, P.C. and Smith, K.D., (2008). Business statistics. Pearson Education.

Grus, J., (2015). Data science from scratch: first principles with python. ” O’Reilly Media, Inc.”.

Hammond, M., & Robinson, A. (2000). Python Programming on Win32: Help for Windows Programmers. ” O’Reilly Media, Inc.”.

Hodges Jr, J.L. and Lehmann, E.L., (2005). Basic concepts of probability and statistics. Society for Industrial and Applied Mathematics.

Ivezi?, Ž., Connolly, A. J., VanderPlas, J. T., & Gray, A. (2014). Statistics, Data Mining, and Machine Learning in Astronomy: A Practical Python Guide for the Analysis of Survey Data. Princeton University Press.

Kim, J., & Forsythe, S. (2010). Factors affecting adoption of product virtualization technology for online consumer electronics shopping. International Journal of Retail & Distribution Management, 38(3), 190-204.

Koufaris, M., & Hampton-Sosa, W. (2004). The development of initial trust in an online company by new customers. Information & management, 41(3), 377-397.

Kvanli, A.H., Pavur, R.J. and Guynes, C.S., (2000). Introduction to business statistics. Cincinnati, OH: South-Western.

Larsen, R. J., & Marx, M. L. (2017). An introduction to mathematical statistics and its applications (Vol. 5). Pearson.

Lee, G. G., & Lin, H. F. (2005). Customer perceptions of e-service quality in online shopping. International Journal of Retail & Distribution Management, 33(2), 161-176.

Lind, D. A., Marchal, W. G., Wathen, S. A., & Waite, C. A. (2013). Basic statistics for business and economics. McGraw-Hill/Irwin.

McKinney, W., (2012). Python for data analysis: Data wrangling with Pandas, NumPy, and IPython. ” O’Reilly Media, Inc.”.

Mendenhall, W. and Sincich, T., (1993). A second course in business statistics: Regression analysis. San Francisco: Dellen.

Monk, S. (2015). Programming the Raspberry Pi: getting started with Python. TAB Electronics.

Moyé, L. A., Chan, W., & Kapadia, A. S. (2017). Mathematical statistics with applications. CRC Press.

Papoulis, A., (1990). Probability & statistics (Vol. 2). Englewood Cliffs: Prentice-Hall.

Pedregosa, F., Varoquaux, G., Gramfort, A., Michel, V., Thirion, B., Grisel, O., Blondel, M., Prettenhofer, P., Weiss, R., Dubourg, V. and Vanderplas, J., (2011). Scikit-learn: Machine learning in Python. Journal of machine learning research, 12(Oct), pp.2825-2830.

Pillers Dobler, Carolyn. “Mathematical statistics: Basic ideas and selected topics.” (2002): 332-332.

Rao, C. R., Rao, C. R., Statistiker, M., Rao, C. R., & Rao, C. R. (1973). Linear statistical inference and its applications (Vol. 2, pp. 263-270). New York: Wiley.

Ross, S.M., (2014). Introduction to probability and statistics for engineers and scientists. Academic Press.

Schutt, R. and O’Neil, C., (2013). Doing data science: Straight talk from the frontline. ” O’Reilly Media, Inc.”.

Wolfinbarger, M. and Gilly, M.C., (2001). Shopping online for freedom, control, and fun. California Management Review, 43(2), pp.34-55.

Zelle, J. M. (2004). Python programming: an introduction to computer science. Franklin, Beedle & Associates, Inc..