This project involves a comprehensive analysis of an e-commerce company, "Happy Bear", specializing in the sale of captivating teddy bears. The company currently offers four different stuffed animal models. The analysis addresses various key business questions, encompassing both financial and marketing aspects.
"Happy Bear" is an online e-commerce company exclusively dedicated to the sale of irresistibly charming teddy bears. The product line consists of four distinct stuffed animal models.
1 - Determine yearly and monthly sales figures in terms of gross revenue and absolute margin. 2 - Calculate the average gross sales for each month and year, identifying the top 10 performers. Draw any noteworthy observations. 3 - Identify the product with the highest monetary sales (Gross Sales). 4 - Determine the product with the highest margin. 5 - Retrieve the release date for each product. 6 - Compute the yearly gross sales, as well as the numerical and percentage margins for each product, and organize the data by product. 7 - Identify the months with the highest gross sales, providing the top 3.
8 - Identify the advertisements or content pieces that have generated the highest number of sessions. 9 - Clarify if sessions equate to users. Provide the count of individual users. 10 - Break down users and sessions by source. 11 - Identify the sources that have resulted in the most sales. 12 - Determine the months with the highest traffic. 13 - For the highest traffic month, provide the number of sessions from mobile devices and computers. 14 - Identify the campaigns that have generated the highest margin for products.
The entire data set was analyzed using SQL.
The original data is in CSV format, consisting of four separate files: Orders, Products, Website Sessions, and Items within each order. The tables range from 3000 to 7000 records in size.
Within our dataset, the highest average sales ticket was achieved in February 2013, with an average ticket value of €51.59, followed by January 2013 with an average ticket value of €51.20. In 2012, the average ticket value was €49.99.
This trend aligns with the fact that in 2012, we only had one product, and in 2013, a second, more expensive product was introduced.
The dataset only includes sales data from March 2012 to February 2013. During this period, we had two products: "Cuddly Bear" and "Forever Love Bear". The best-selling bear was the "Cuddly Bear", achieving a gross sale of €149,570.08 for the analyzed period.
The highest profit margin was obtained from the sale of the "Cuddly Bear", totaling €91,256.
For the year 2012, with only the "Cuddly Bear" available, gross sales amounted to €129,274.14, yielding a margin of €78,873, or 61.0122%. In 2013, sales of the "Cuddly Bear" reached €20,295.94, resulting in a margin of €12,383, also at 61.0122%. In January of the same year, the "Forever Love Bear" was introduced as a new product, achieving gross sales of €3,539.41, a profit margin of €2,212.5, representing a 62.5104% margin.
The top three months in terms of gross sales were November 2012 with €30,893.82, December 2012 with €25,294.94, and January 2013 with €19,966.10 in gross sales.
The highest number of sessions originated from ad g_ad_1 posted on www.gsearch.com, accounting for 49,978 sessions out of a total of 70,000. Of the 70,000 sessions, there were 62,224 unique users. Some users had more than one session.
Breaking down users and sessions by source:
- 5,899 users with 7,248 sessions with an unknown source.
- 9,808 users with 9,898 sessions from source www.bsearch.com.
- 51,118 users with 52,845 sessions from source www.gsearch.com.
The advertising source that resulted in the highest sales was www.gsearch.com, generating a total of €135,602.97 in gross sales. The second most successful source of advertising was from an unknown origin, with sales totaling €43,531.33. Finally, €30,903.85 in gross sales were attributed to advertising on www.bsearch.com.
The months with the highest web traffic were recorded in November 2012 and December 2012, which aligns with the holiday season. The most frequently used device for browsing was the desktop, followed by mobile.
Finally, the campaign with the highest profit margin was "BRAND AWARENESS", yielding a margin of €73,566.