# Import necessary libraries
import numpy as np # NumPy for numerical operations
import pandas as pd # Pandas for data manipulation
import seaborn as sns # Seaborn for data visualization
import matplotlib.pyplot as plt # Matplotlib for plotting
# Import Prophet, a time series forecasting library
from prophet import Prophet
# Ignore any warnings to keep the output clean
import warnings
"ignore")
warnings.filterwarnings(
# Set the style of the plots to 'ggplot' and 'fivethirtyeight'
'ggplot')
plt.style.use('fivethirtyeight')
plt.style.use(
# Read an Excel file
= pd.read_excel('ana.xlsx')
df # Convert the 'Order_Date' column to datetime format
'Order_Date'] = pd.to_datetime(df['Order_Date']) df[
Mo Chen’s Coffee - A Journey Through Data
1 The Data:
Mo Chen has provided access to the data. Click here to access the data download link. The data originally covered the years 2019 to 2022. I just changed it to span from 2020 to 2023 to make it more enjoyable. If you view this project in 2024 you can change the span to 2021 up to 2024 hope you get the idea.
1.1 Project Background:
Mo Chen is my favorite YouTuber specializing in data analytics. He is a data and analytical analyst working in the financial industry. He initiated an Excel-based project centered around a coffee shop. Recently, he established a Discord group in response to subscriber requests to facilitate direct interaction.
I saw this as an opportunity to immerse myself in the data, turning it into a personal challenge and seizing the chance to include this project in my portfolio website. My goal is to enhance the project beyond what Mo Chen has achieved. My approach involves transforming data science projects into easily understandable narratives.
1.2 The Why:
This is my way of giving back to Mo Chen’s YouTube community, Discord community, and the broader data science community. I aim to make my work accessible to everyone, demonstrating that any data project can be explored in greater depth.
1.3 Setting the Scene:
The data comprises tables for orders, products, and customers, all contained within a single Excel workbook. Initially, I used Excel to create three separate workbooks for each table.
Next, I imported the data into Power BI, establishing connections between tables using keys to ensure that any future data entered will automatically update the dashboard.
I employed Power Query in Power BI to refine the data, addressing issues such as missing values and duplicates (though in this data, there were no missing values or duplicates). I also performed column renaming and created new columns, such as ‘total revenue,’ calculated as the unit price multiplied by the quantity sold.
Moving forward, I developed measures using DAX (Data Analysis Expressions), drawing insights from the knowledge I gained from a YouTuber named ‘Sweatpant BI.’ If you’re interested in learning more about Power BI, I recommend checking out his channel.
Lastly, I ensured that my Power BI file is accessible to others, providing them with the opportunity to explore and contribute to the project’s improvement.
2 Dashboard
To view and interact with the dashboard I created, Click here to view and here to download.
3 The project in a Nutshell
Mo Chen had a dream: to reach a revenue target of $14,500 by the end of 2023 in the USA, Ireland, and the United Kingdom. To understand how the business was performing and the loyalty of their customers, Mo Chen decided to bring in a data analyst named Teslim. Teslim’s task was to analyze data from January 2020 to August 2023.
3.1 Loyal vs. Non-Loyal Customers
Teslim started by sorting customers into two groups: loyal and non-loyal. Currently, they had 479 loyal customers and 521 non-loyal customers. The big question was, why do some people keep coming back, while others don’t?
3.2 Revenue and Products
Next, they looked at the money they’ve made so far, which was $45,134. They also checked which products sold the most. It turned out that Excelsa was the top seller. Most of their sales came from the United States, with $35,639, while Ireland and the United Kingdom contributed $6,697 and $2,799, respectively.
3.3 Top Cities and Customers
Together, Mo Chen and Teslim figured out the top 5 cities where their products sold the most:
- Washington, D.C., USA - $1,064
- Houston, USA - $820
- Toledo, USA - $774
- New York City, USA - $773
- Sacramento, USA - $628
They also identified the top 5 customers based on spending:
- Allis Wilmore - $317
- Brenn Dundredge - $307
- Terri Farra - $289
- Nealson Cuttler - $282
- Don Flintiff - $278
Mo Chen planned to send gifts to these top customers in September 2023.
3.4 Looking at Trends
Lastly, they examined the business’s performance for each year from 2020 to August 2023 because the data stopped at August 2023, during which they were currently operating.
But there was more to explore! Mo Chen and Teslim still needed to figure out if they would reach their revenue goal and understand why some customers were loyal while others weren’t. That’s where phase 2 of their analysis began.
3.5 Dashboard Showing All Time Sales Performance
4 Phase Two Analysis
We will investigate the year 2022 and our current year, 2023.
In August 2022, on the exact day from the previous year (2023), our revenue was $7,958. This figure represents the Previous Year to Date (Previous YTD).
Currently, in August 2023, our revenue stands at $7,063, representing Year to Date (YTD).
Our Year-over-Year (YoY) growth percentage is -11.2%, indicating a decline in revenue.
To visualize the trend, we’ve compared 2022 and 2023 in a line chart, clearly showing the drop in August 2023 compared to August 2022.
4.1 Dashboard Showing 2022 vs 2023 Sales Performance
Now, considering our revenue goal for 2023, Mo Chen’s target is to reach $14,500. The question arises: Are we on track to achieve this goal? As Mo Chen reviews the dashboard, he’s visibly concerned but patiently awaits the full story.
Our progress towards the goal is currently at 48.7%. Can we attain it? Mo Chen persists with his questions, and Teslim, our data analyst, reassures him to wait for the complete analysis.
Moving on, in 2023, Liberica is leading in sales, which doesn’t come as a surprise based on historical data. Liberica consistently ranks among the top two coffee types in terms of revenue.
Robusta shows a 10.6% increase, Liberica has grown by 3.7% from 2022, but Excelsa and Arabica have experienced decreases of -18.9% and -34.0%, respectively. Mo Chen has left to confer with the product team, probing for potential issues with quality, quantity, or packaging.
Shifting our focus to countries, it appears that the USA has seen a -5.6% decrease in purchases. This is significant as the USA houses the majority of our customers.
Ireland has experienced a -22.6% decline, and the UK has plummeted by -61.1%. This drastic drop in the UK market raises concerns, especially since one of our top five customers is a UK citizen. Mo Chen questions if there might be issues related to quality, shipping delays, or other factors.
The logistics (shipping) team needs to present their data, and we must analyze the behavior of customers from the UK, including their reviews and time spent on our site. This additional data will be crucial for further investigation.
Resolving these issues is essential for our future prospects.
Based on the data we’ve examined, we can identify some significant issues, which we will communicate directly to Mo Chen.
4.2 Uncovering the Path to Success
Mo Chen, the owner of Mo Chen’s Coffee, was faced with the million-dollar question: would they hit their revenue goal of $14,500? The journey had hit a few bumps, and things weren’t going as planned.
4.3 Hitting Goal?
4.3.1 Using the Power of Python
4.3.2 Sales by Month by Year
Code
# Extract the year and month from the 'Order_Date' column
'Year'] = df['Order_Date'].dt.year
df['Month'] = df['Order_Date'].dt.month
df[
# Group the data by year and month and calculate the total revenue for each group
= df.groupby(['Year', 'Month'])['Total_Revenue'].sum().unstack()
monthly_revenue
# Add a 'Total' column
'Total'] = monthly_revenue.sum(axis=1)
monthly_revenue[
# Rename the columns to use month names
= ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
month_names 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
= month_names + ['Total']
monthly_revenue.columns
# Display the monthly revenue as a table
monthly_revenue.T
Year | 2020 | 2021 | 2022 | 2023 |
---|---|---|---|---|
Jan | 828.985 | 566.950 | 837.685 | 1269.415 |
Feb | 987.405 | 1798.340 | 958.830 | 393.630 |
Mar | 1021.140 | 914.790 | 1544.640 | 1315.205 |
Apr | 1680.750 | 761.810 | 1005.585 | 776.450 |
May | 398.565 | 939.355 | 907.690 | 1002.370 |
Jun | 1384.680 | 1438.440 | 864.530 | 1155.390 |
Jul | 1004.135 | 1308.945 | 763.105 | 906.730 |
Aug | 706.345 | 300.400 | 1075.910 | 244.245 |
Sep | 1277.020 | 713.050 | 1643.575 | NaN |
Oct | 884.970 | 1514.705 | 1400.400 | NaN |
Nov | 823.385 | 1108.865 | 1616.180 | NaN |
Dec | 1189.785 | 751.895 | 1147.980 | NaN |
Total | 12187.165 | 12117.545 | 13766.110 | 7063.435 |
4.3.3 In both 2021 and 2023, August experienced a dip. However, we finished 2021 with $12,117. The question is, can we reach Mo Chen’s goal of $14,500?
Code
'font.family'] = 'Arial'
plt.rcParams['font.size'] = 10
plt.rcParams[
# Filter the DataFrame to include only 2021 and 2023
= df[df['Year'].isin([2021, 2023])]
filtered_df
# Group by Year and Month, and sum the Total_Revenue
= filtered_df.groupby(['Year', 'Month_Short'])['Total_Revenue'].sum().unstack()
grouped_data
# Define the order of months starting from January
= ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
months_order
# Plotting the data
=(8.8, 5))
plt.figure(figsizefor year, data in grouped_data.iterrows():
# Reorder the data based on the defined months_order
= data[months_order]
data =year)
plt.plot(data.index, data.values, label
'')
plt.xlabel('')
plt.ylabel('Total Revenue Over Months by Year (2021 and 2023)')
plt.title(='Year')
plt.legend(titleTrue)
plt.grid( plt.show()
4.3.4 What is our Year-to-Date Sales?
Code
# Filter the data for January to August 2023
print("-" * 80)
= df[(df['Order_Date'].dt.year == 2023) & \
ytd_sales_jan_to_aug_2023 'Order_Date'].dt.month >= 1)\
(df[& (df['Order_Date'].dt.month <= 8)]['Total_Revenue'].sum()
print(f"Year-to-Date Sales from January to August 2023: ${ytd_sales_jan_to_aug_2023:.2f}")
print("-" * 80)
--------------------------------------------------------------------------------
Year-to-Date Sales from January to August 2023: $7063.43
--------------------------------------------------------------------------------
Currently, we have $7,063 in sales from January 2023 to August 2023. We want to forecast how much we will make for the remaining months of the year, which are September, October, November, and December.
4.3.5 Financial Forecasting through Growth Rate Analysis
Projections are educated guesses about what you expect to earn in the future.
Let’s break down the mathematical calculations step by step for forecasting total revenue for the year 2023 based on data from 2020, 2021, and 2022.
Calculate Total Revenue for Each Year:
- For the year 2020:
- Sum up all the ‘Total_Revenue’ values in your data for 2020.
- For the year 2021:
- Sum up all the ‘Total_Revenue’ values in your data for 2021.
- For the year 2022:
- Sum up all the ‘Total_Revenue’ values in your data for 2022.
- For the year 2020:
Calculate the Growth Rate:
The growth rate represents how much the revenue has changed from 2020 to 2022, as a percentage of the revenue in 2020.
Subtract the total revenue for 2020 from the total revenue for 2022: Growth = (Total Revenue in 2022 - Total Revenue in 2020)
Divide the growth by the total revenue for 2020 and multiply by 100 to express it as a percentage: Growth Rate = [(Total Revenue in 2022 - Total Revenue in 2020) / Total Revenue in 2020] * 100
Forecast 2023 Total Revenue:
To estimate the total revenue for 2023, we will use the growth rate calculated in step 2.
- Multiply the total revenue for 2022 by (1 + Growth Rate / 100): Forecasted Total Revenue in 2023 = Total Revenue in 2022 * (1 + Growth Rate / 100)
This forecasted total revenue for 2023 is an estimate based on the observed growth rate from 2020 to 2022. It assumes that the same percentage increase will apply to 2022’s revenue to predict 2023’s revenue.
Code
# Filter data for the years 2020, 2021, and 2022
= pd.to_datetime('2020-01-01')
start_date = pd.to_datetime('2022-12-31')
end_date = df[(df['Order_Date'] >= start_date) & (df['Order_Date'] <= end_date)]
filtered_df
# Calculate total revenue for each year
= filtered_df[filtered_df['Order_Date'].dt.year == 2020]['Total_Revenue'].sum()
revenue_2020 = filtered_df[filtered_df['Order_Date'].dt.year == 2021]['Total_Revenue'].sum()
revenue_2021 = filtered_df[filtered_df['Order_Date'].dt.year == 2022]['Total_Revenue'].sum()
revenue_2022
# Calculate the growth rate from 2020 to 2022
= (revenue_2022 - revenue_2020) / revenue_2020
growth_rate
# Use the growth rate to forecast 2023 total revenue
= revenue_2022 + (growth_rate * revenue_2022)
forecasted_revenue_2023
print("-" * 80)
print(f"Total Revenue in 2020: ${revenue_2020:.2f}")
print(f"Total Revenue in 2021: ${revenue_2021:.2f}")
print(f"Total Revenue in 2022: ${revenue_2022:.2f}")
print(f"Forecasted Total Revenue in 2023: ${forecasted_revenue_2023:.2f}")
print("-" * 80)
--------------------------------------------------------------------------------
Total Revenue in 2020: $12187.17
Total Revenue in 2021: $12117.55
Total Revenue in 2022: $13766.11
Forecasted Total Revenue in 2023: $15549.62
--------------------------------------------------------------------------------
Based on our growth rate calculation, it appears that by the end of 2023, we are projected to achieve a revenue of $15,549.62. However, it’s important to understand what this growth rate represents.
- “Projected to achieve” is used to clarify that the revenue of $15,549.62 is an estimation or projection based on the growth rate calculation.
-“it’s important to understand what this growth rate represents” emphasizes the need to provide context and explanation regarding the significance and implications of the calculated growth rate.
4.3.6 “How Our Sales Went on a Roller Coaster Ride: From 2021’s $12k to 2023’s $15.5k Mystery!”
The sales numbers have got my curiosity piqued, and I couldn’t help but dig deeper into the data. Here’s the deal: in August 2021, we were rocking it with sales at $8,029 , and by the end of that year, we reached a cool $12k. But fast forward to August 2023, and we’re at $7,063. Now, hold onto your hat because our calculations suggest we’re on track to finish 2023 with a whopping $15,549.62! I don’t know about you, but explaining this to Mo Chen is going to be a real brain-teaser! 🤯💰📈
Code
# Extract the year and month from the 'Order_Date' column
'Year'] = df['Order_Date'].dt.year
df['Month'] = df['Order_Date'].dt.month
df[
# Filter the data for months from January (1) to August (8) and years 2021 and 2023
= df[(df['Year'].isin([2021, 2023])) & (df['Month'] >= 1) & (df['Month'] <= 8)]
df_filtered
# Group the filtered data by year and month and calculate the total revenue for each group
= df_filtered.groupby(['Year', 'Month'])['Total_Revenue'].sum().unstack()
monthly_revenue
# Add a 'Total' column
'Total'] = monthly_revenue.sum(axis=1)
monthly_revenue[
# Rename the columns to use month names (January to August)
= ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug']
month_names = month_names + ['Total']
monthly_revenue.columns
# Display the monthly revenue as a table
monthly_revenue.T
Year | 2021 | 2023 |
---|---|---|
Jan | 566.950 | 1269.415 |
Feb | 1798.340 | 393.630 |
Mar | 914.790 | 1315.205 |
Apr | 761.810 | 776.450 |
May | 939.355 | 1002.370 |
Jun | 1438.440 | 1155.390 |
Jul | 1308.945 | 906.730 |
Aug | 300.400 | 244.245 |
Total | 8029.030 | 7063.435 |
5 Using Machine Learning : Simple Facebook Prophet Model
Code
# Create a time series from the Total_Revenue column
= df.groupby('Order_Date')['Total_Revenue'].sum().resample('M').sum()
time_series
# Create a DataFrame for Prophet with 'ds' and 'y' columns
= pd.DataFrame({'ds': time_series.index, 'y': time_series.values})
prophet_df
# Create and fit the Prophet model
= Prophet()
prophet_model
prophet_model.fit(prophet_df)
# Forecast horizon for Prophet (monthly data for 2023)
= 12
forecast_horizon
# Create future dates for Prophet
= prophet_model.make_future_dataframe(periods=forecast_horizon, freq='M')
future_dates
# Forecast with Prophet
= prophet_model.predict(future_dates)
prophet_forecast
# Plot the historical data and Prophet forecast
=(8.8, 5))
plt.figure(figsize='Historical Revenue')
plt.plot(time_series, label'ds'], prophet_forecast['yhat'], label='Prophet Forecast', linestyle='--')
plt.plot(prophet_forecast[
plt.legend()'')
plt.xlabel('')
plt.ylabel('Revenue Forecast with Prophet')
plt.title(True)
plt.grid( plt.show()
19:43:51 - cmdstanpy - INFO - Chain [1] start processing
19:43:51 - cmdstanpy - INFO - Chain [1] done processing
Code
# Create a time series from the Total_Revenue column
= df.groupby('Order_Date')['Total_Revenue'].sum().resample('M').sum()
time_series
# Create a DataFrame for Prophet with 'ds' and 'y' columns
= pd.DataFrame({'ds': time_series.index, 'y': time_series.values})
prophet_df
# Create and fit the Prophet model
= Prophet(yearly_seasonality=True)
prophet_model
prophet_model.fit(prophet_df)
# Forecast horizon for Prophet (assuming monthly data for 2023)
= 12
forecast_horizon
# Create future dates for Prophet
= prophet_model.make_future_dataframe(periods=forecast_horizon, freq='M')
future_dates
# Forecast with Prophet
= prophet_model.predict(future_dates)
prophet_forecast
print("-" * 80)
# Print the Prophet forecast for 2023
print(f"Prophet Forecast for 2023:\n{prophet_forecast[['ds', 'yhat']].tail(forecast_horizon)}")
print("-" * 80)
19:43:52 - cmdstanpy - INFO - Chain [1] start processing
19:43:52 - cmdstanpy - INFO - Chain [1] done processing
--------------------------------------------------------------------------------
Prophet Forecast for 2023:
ds yhat
44 2023-09-30 1313.329582
45 2023-10-31 1395.020153
46 2023-11-30 1569.759191
47 2023-12-31 727.870164
48 2024-01-31 1107.930917
49 2024-02-29 1451.851904
50 2024-03-31 939.740924
51 2024-04-30 1358.201314
52 2024-05-31 484.294435
53 2024-06-30 1377.070654
54 2024-07-31 1163.915293
55 2024-08-31 787.089105
--------------------------------------------------------------------------------
From January to August 2023, we generated $7,603 in revenue. Now, using the Facebook Prophet model, here are our revenue predictions for the upcoming months:
- September: $1,313
- October: $1,395
- November: $1,570
- December: $729
When we add all these numbers together:
$7,603 + $1,313 + $1,395 + $1,570 + $729 = $12,610
According to Facebook Prophet, it seems unlikely that we will achieve our revenue goal. This metric is a better one to report to Mo Chen than the latter.
5.1 Tuning
Note: We can further improve the model by performing cross-validation and tuning.
6 Beyond our Data: What Next?
We couldn’t predict which customers would stay loyal using machine learning. In this case, we should conduct a survey and ask our non-returning customers (those who aren’t loyal) why they haven’t come back.
We noticed a drop in sales from 2022 to 2023:
- In the United Kingdom, sales decreased by 61%. It’s worth noting that one of our top 5 customers is from the UK.
- The USA, which accounts for the majority of our customers, saw a decrease of 5.6%.
- Ireland experienced a 22.6% decrease in sales.
When it comes to our products Excelsa and Arabica, we saw decreases of 18.9% and 34%, respectively. These figures could provide valuable insights for our survey.
To address these challenges, the marketing team should examine our competition, and the website development team should gather more data on customer behavior to make data-driven improvements to our site.
7 A Brighter Future
With all these insights in hand, Mo Chen felt a renewed sense of clarity about his business. He now knows where to focus his efforts to solve problems and ensure he reached his revenue goal. Moreover, he has a dashboard to track crucial metrics, helping him steer the ship toward success.
The journey continued, and Mo Chen was determined to overcome any obstacles on the path to achieving his ambitious goal. With Teslim’s guidance and the power of data analysis, the future looked brighter than ever for Mo Chen’s Coffee.