Adventure Works: Optimizing Sales Performance.
Introduction
The absolute goal of every business is to make money; not just make money but make as much profit as possible. There is a saying that “too much money isn’t enough money,” thus all business, irrespective of their profit margin would love to make more profit as the years roll by. The case is the same for Adventure works, an e-commerce store who specializes in the sale of sporting equipment, ranging from different bicycle models to shirts, shorts, and caps. Adventure works has, over the years collected data on its customers’ purchases, customer biodata, products being sold, etc., and now, the store is interested in finding out how they can optimize their sales performance despite a continuous increase in revenue and profit over the years which the data has been collected.
As a data analyst, I have been assigned the burden of this task and who better if not me? To find out how sales can be optimized, the major focus of my analysis will be to understand customer behaviour and buying patterns. Further analysis will show patterns, trends, and answer simple questions such as how much revenue and profit has been made, what cities make the most, demographic segmentation, what goods are being bought together, etc., before I can decide how the store can improve its sales performance.
Dataset Information
The dataset contains information from July 2005 up till July 2008, and consists of 6 different sheets/tables namely;
FactInternetSales (Sales Table): This table contains sales information for every product bought. It holds more information such as customer key, order date, sales order number, number of units sold, product amount, product key, tax paid, etc. This table would be the fact table when I carry out the data modelling process.
DimProduct (Product Table): This is the product table and contains every information about each product the store sells. Some information contained in this table includes Product key, product name, product model, etc.
DimSalesTerritory (Sales Territory Table): This table holds information on the countries and continents which the store operates and its goods are being sold.
DimDate (Date Table): This is the date table containing information such as the order date, day of the week these orders came in, Fiscal Year, Quarter, etc.
DimCustomer (Customer Table): The customer table contains every valid information about each customer such as customer key, customer name, profession, date of birth, country of residence, marital status, gender, and many more generic but useful information.
DimGeography (Geography Table): This table, although very similar to the Sales Territory table has just a few differentials. This table hold more information containing geographic location such as city, state, country code, IP Address, etc.
Data Cleaning
Following standard practice, I have to clean the data due to many inconsistencies and ensure it is suitable for further analysis. The entire cleaning process for this dataset was done with Power Query in Power BI.
The cleaning and transformation process for the six tables includes removing of unnecessary columns, filtering empty rows when they have bee deemed to be inconsequential or not useful for further analysis, replacing values, changing datatypes, and feature engineering.
Data Modelling
Due to the existence of different tables there is a need to establish a relationship between the 6 tables before going into analysis. The sales table has already been established as the fact table and it consists of many primary keys while other tables are dimension tables. All tables were linked to the Sales table with the exception of the Geography and Sales Territory table. The Geography table and Sales Territory table are linked to each other while the Geography table was linked to the Customers table, creating an indirect link to the Sales table.
If I wanted to, I could severe the link between the Geography table and the Sales Territory table, leaving the link between the Geography and Customer table while the Sales Territory table has its link to the Sales table re-established.
Key Insights
Revenue and Profit Overview
The revenue and profit dashboard are similar with few variations. From the visualizations, a few things are noteworthy;
- For profit and revenue, there is usually an increase in performance during the festive period.
- Total Revenue and Profit increased over the years, with 2007 raking in the most revenue at $102.4m and $42.5m in profit, slightly edging out 2008. This is largely due to the fact that the last order date available in the dataset is July 2008. In addition, it is worthy to note that more products were added to inventory as the years go by, so this is also another reason why sales have increased over time.
- United States and Australia lead the way in terms of revenue and profit but looking at the average revenue and profit which each country provides, Australia ranks much higher than United States, so does Germany rank higher than United Kingdom. The huge disparity in total revenue and profit between United States and other countries except Australia could be attributed to the fact that the number of cities in each country differs. United States has the most number of cities. The number of cities in other countries combined is not up to what United States have. Purchasing Power Parity and way of life could to a large extent explain the disparity in average profit.
- Although not all products are classified into a particular model, the products raking in the most revenue and profits are classified under one model or the other and the best selling model is the Mountain 200, and the product which brings in the most revenue is the Mountain 200 Black 46. Same with profit
- The top 5 customers in terms of revenue generated have spent between $150,000 and $170,000. Margret He edges out every other customer after spending $168,633.38, and in second place is Nicole Nara, spending a total of $162,692.2. In terms of profit, although Margret He and Nicole Nara lead the way, Dawn L. Nath, Ricky D. Vasquez, and Emily R. Miller join these two as the top 5 customers who provide the most profit.
- Customers who fall under the tag “Generation X” and “Millennials” consistently make up a huge chunk of revenue and profit in all regions.
Demographic Segmentation
- The distribution between Male and Female customers is really close. 55% of all customers are married and 69% own a house.
- Unsurprisingly, United States produces the highest number of customers, followed by Australia.
- Most of the store’s customers are either Millennials or Generation X born, so this gives credibility to why they pull in the highest revenue and profit irrespective of region.
- The number of new customers has been on a steady rise except for 2008 and once again, that is due to the fact that the dataset ends in July 2008. There were no returning customers for the first two years, possibly because the store was not selling what the people wanted at the time. 2007 is the first time the store recorded returning customers and this increased in 2008. Also, the total number of customers increases each year.
Recommendations
Despite Adventure Works recording tremendous success in terms of increasing its customer base, revenue, profits, and the number of goods sold, there is still a lot of room for improvement if they want to optimize sales. Exploratory Data Analysis and further research has pointed to the fact that Adventure works is not capitalizing on the strengths of the countries in which it operates. Here, I will dive further into how Adventure works can capitalize on the huge sports market.
- The city with the highest number of orders, customers, and revenue is London followed by Paris. This is a testament to London being one of the biggest sporting cities in the world. Although the store has London on lock, it does not capitalize on other great sporting cities in the U.K such as Manchester, Glasgow, Edinburgh, etc. The store has customers in cities like Liverpool and Leeds but the revenue from these cities amounts to around $660k and $1.1m respectively.
How can the store take advantage of great sporting cities in United Kingdom? One way to do this is to capitalize on sporting events coming up. For example, most of Adventure works’ products are cycling goods, so leveraging on the annual Tour of Britain cycling event especially in cities where the event will take place. Another way is to latch on campaigns that advocate for the use of bicycles and/working as a means of transportation e.g., campaigns to improve air quality in UK; cities like Glasgow, Manchester, Edinburgh, Liverpool, London, Aberdeen, etc., lead the way when it comes to these type of campaigns. This type of marketing can also be replicated in countries such as France who hosts the Tour de France and U.S.A, who is also keen on reducing air pollution.
- Adventure Works is yet to take advantage of the great biking culture in some cities. Cities with great biking cultures need to be at the forefront of the store’s campaigns. Between 200 and 2008, the number of people biking to work in United States and Canada doubled; this is only on average. Cities like Portland, recognized as the greatest biking city had its number of bike trips increased by almost six-fold between 1990 and 2009, accounting for almost 6 percent of overall transportation in the city. For Portland’s work-specific commutes, bike-use peaked at 18 percent in 2008. Focus should also be on Western North America because research has shown that biking is more popular in this area — especially in dense urban areas, gentrified neighborhoods and university/college locales — than in the east.
- In anticipation of future policies in countries like United States and United Kingdom, Adventure Works can work with city councils to invest biking infrastructure like bike lanes, and sports hangouts e.g., basketball, skate parks, etc.
- From the dataset, it is clear to see that the store already has its roots in a couple of European countries, but not the best European countries when it comes to biking culture. Europe has a rich cycling heritage that stretches back decades and cities like Denmark and Netherlands lead the pack. In Denmark, 16% of all trips and 25% of all trips less than 3 miles are made by cycling, 90% of Denmark’s population own a bike, and an estimated 50% of residents in Copenhagen bike to school or work. In Amsterdam, prior to 2010, there were about 800,000 bikes in the city and over 60% of Dutch citizens ride their bikes daily.
The main point for all this statistical jargon is, Adventure Works need to penetrate other European cities with an already established biking culture. Yes, there would be a lot of stores selling bikes but not all sell other bike accessories and Adventure Works has a large array of products at its disposal. - What better way to optimize sales if not by understanding customers buying pattern? To understand what the store’s customers buy, I have relied on Cross-sell Analytics to show what products customers tend to buy together.
At basic level, cross-selling is the practice or selling an additional product to a customer. This is a recognized method to increase average order value and revenue. To carry out cross-selling, a business must first know what products customers prefer to buy together, and this is where cross-sell analytics comes in.
With cross-sell analytics, I have been able to show the percentage of customers who buy one product and buy another product alongside it. For example, if 500 people buy a mountain red 200 and 300 people (60%) of those buy a patch or a tire tube alongside the mountain red 200, it is wise to recommend a tire tube or patch to anyone who is willing to buy a mountain red 200. In a physical store, if possible, it is best to place both products close to each other but in the case of adventure works, an e-commerce store, a recommendation system to suggest these products would be the way to go. With cross-sell analytics, recommendations can be made based on the country a customer is buying from and the age generation the customer falls into.
- As much as we can market to existing customers on a general scale via TV, signing local sporting heroes to endorsement deals, etc., it would not always resonate with everyone. In this case, blind marketing becomes bad marketing. But with a more tailored/targeted marketing approach, the store could make its existing customers feel loved and valued. To achieve this goal, the customers were segmented into different categories using RFM Analysis. RFM Analysis is a marketing technique which is used to segment customers based on their purchasing value. RFM stands for Recency, Frequency, and Monetary Value. With this at our disposal, targeted email marketing and SMS messaging could be very efficient. For champions, who have also provided the most revenue, the simple things such as birthday messages, discounted services, congratulatory messages in the case of any milestone goes a long way in making them feel special.
It is noteworthy to try and salvage customers who fall under the “At Risk” and “Cannot Lose Them” segments. They have brought a big chunk of revenue and if the store loses them, that’s potentially a large chunk of future revenue being lost. These customers need to be spoken to and if possible, offer mouth-watering deals; they must not be lost to a competitor.
A breath of fresh air comes in form of New customers and Promising customers. The hold the top two spots for number of customers and this means they can potentially contribute a great deal to future revenue. These customers can also be tempted with loyalty programs, referral incentives, personalized experiences, etc.
Conclusion
Despite recording staggering numbers over the years, Adventure Works is at an integral juncture in its lifetime. The decisions it makes now can lead to astronomical revenue and profits in the nearest future.
Adventure Works already has a strong customer base especially in United States and Australia, but it needs to capitalize on cities with a high biking rate and leverage on sporting events in Germany, Canada, United Kingdom, and France. The European market shows a lot of promise for Adventure Works products with countries like Netherlands and Denmark showing the most promise, but this should not be done without some market feasibility studies.
Finally, the customer segmentation shows Adventure Works is at risk of losing potentially a large chunk of its future revenue. Reaching out to a particular segment of its customers would make increase its opportunities to optimize sale and in turn, increased revenue.
The interactive dashboard for this project can be viewed here.
Comments and questions are duly appreciated. Thank you and cheers.
