<jupyter_start><jupyter_text>**Fall 2022 Data Science Intern Challenge****<NAME>****Junior Data Scientist***Please complete the following questions, and provide your thought process/work. You can attach your work in a text file, link, etc. on the application page. Please ensure answers are easily visible for reviewers!*  Question 1: Given some sample data, write a program to answer the following: [click here to access the required data set](https://docs.google.com/spreadsheets/d/16i38oonuX1y1g7C_UAmiK9GkY7cS-64DfiDMNiR41LM/editgid=0)*On Shopify, we have exactly 100 sneaker shops, and each of these shops sells only one model of shoe. We want to do some analysis of the average order value (AOV). When we look at orders data over a 30 day window, we naively calculate an AOV of $3145.13. Given that we know these shops are selling sneakers, a relatively affordable item, something seems wrong with our analysis.*- *Think about what could be going wrong with our calculation. Think about a better way to evaluate this data.*- *What metric would you report for this dataset?*- *What is its value?*  **Introduction**Identifying the right metrics is crucial for any brand tracking its growth over time, identifying key customer segments, or forecasting revenue streams as examples of critical business functions. For example, metrics such as **average order value (AOV)** tell us the average price at which a customer is willing to pay for a class of items. It's a crucial metric for any retailer because it helps decision-makers understand how much revenue each customer drives and how customers spend their money. Alone, the scope of information we can infer about a business is limited. However, when combined with other metrics such as the volume of customer purchases, or the peak hours during a business day, we can obtain a more accurate view of the business' health.As one of the world's leading eCommerce platforms, Shopify's business model provides online merchants with the tooling to succeed in their business endeavours. As a result, Shopify has a huge incentive to see its merchant userbase succeed since a significant portion of its revenue comes from the transaction volume that merchants generate. Therefore, having an accurate understanding of a business' health is vital if Shopify is to continue fulfilling its value proposition of providing merchants with the right data and insights to make good decisions. And when data teams feed business leaders with poorly designed metrics that inaccurately measure the outcomes they're interested in tracking, bad decisions then follow suit.In the following paragraphs, we will highlight an example of how a  poorly designed metric can aversely affect Shopify's platform, how we can improve it, and what we can learn from this incident.  One day, an executive team member came to us with a dataset containing five thousand rows of transaction data on one hundred unique sneaker merchants using the Shopify platform. The executive was very excited by the recent report that their sneaker merchant's customers spent an average of $3,145.13 per order over the past month. May isn't a typical month where we'd see sneaker sales rise this much, so the executive tasked the marketing department with exploring an initiative to keep this trend going while our job, as the data team, was to learn from this trend and dig into what was causing it. Once we had the datasets, we sat down and applied the typical data science procedures, such as importing the software packages needed for our analysis, uploading the data, and checking for null and duplicate values.<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
url = 'https://docs.google.com/spreadsheets/d/16i38oonuX1y1g7C_UAmiK9GkY7cS-64DfiDMNiR41LM/export?format=csv&gid=0'
orders_df = pd.read_csv(url)
orders_df.head()<jupyter_output><empty_output><jupyter_text>**Data Cleaning**Luckily for us, the dataset was very clean. There were no duplicate rows or null values. In terms of features, the only area that needed to be adjusted was the `created_at` column as it was stored as a string rather than in a DateTime format which we then modified using pandas' `pd.to_datetime()` function.<jupyter_code>orders_df.info()
print(f"There are {orders_df[orders_df.duplicated()].shape[0]} duplicates in this dataset")
orders_df['created_at'] = pd.to_datetime(orders_df['created_at'])<jupyter_output><empty_output><jupyter_text>**Metric Evaluation**However, when we examined the numeric columns using pandas’ `.describe()`, we noticed a critical error in how the previous team reported average order value (AOV) to the executives. If we look at the `order_amount` and `total_items` columns, we’ll find that the mean for each column is vastly much greater than the median. For `order_amount`, which tracks the amount that customers pay, our median order value is \\$284, while the average is \$3,145.13, as reported earlier. For the `total_items` that customers purchased, we’ll find that the median value is 2 items while the average value is almost 9! Why is there such a difference? As we dug deeper, we found several reasons for this misconception. In the context of sneakers, AOV should allow us to infer the average price that a customer is willing to pay for these products. Unless the dataset lists these values in another currency, \$3,145.13 is quite expensive for the average consumer to pay for a sneaker, assuming we’re denominating in Canadian Dollars (CAD). One of the reasons that AOV is so high is that we’re dividing the sum of the total `order_amount` by the number of transactions which will inflate these numbers as it fails to account for the orders where consumers purchased two or more items. If we’re using AOV to identify the average price a customer is willing to pay for a sneaker, we should instead divide the sum of the `order_amount` by the sum of the `total_items` purchased.$$ TrueAOV = \frac{\sum_{i=1}^n Order Amount_{i}}{\sum_{i=1}^n Total Items_{i}} $$<jupyter_code>orders_df.describe()
print(f"Verifying the Average Order Value (AOV): $ {round(orders_df['order_amount'].sum() / orders_df.shape[0], 2)}")<jupyter_output>Verifying the Average Order Value (AOV): $ 3145.13<jupyter_text>If we follow this logic, then the true AOV for our dataset should be $357.92, which is still relatively high in terms of an average price of a sneaker, but almost 90% less than the original figure reported.<jupyter_code>print(f"The way that Average Order Value (AOV) should be calculated: $ {round(orders_df['order_amount'].sum() / orders_df['total_items'].sum(), 2)}")<jupyter_output>The way that Average Order Value (AOV) should be calculated: $ 357.92<jupyter_text>We can also do some minor feature engineering here by constructing an `aov` column which divides `order_amout` by `total_items` for each row on our dataset to get the approximate range of individual sneaker prices for each order (since the individual sneaker prices aren't listed) amongst our merchant base.<jupyter_code>orders_df['aov'] = round(orders_df['order_amount'] / orders_df['total_items'], 2)
orders_df['aov'].describe()<jupyter_output><empty_output><jupyter_text>Now that we have the approximate range of sneaker prices amongst our sneaker prices, we can begin digging for insights on our merchants and their consumer's purchasing patterns. For example, we'll find that the lowest AOV which we can infer as the cheapest shoes offered is \$90, while the most expensive shoe (inferred from AOV) is a whopping \$25,725! Furthermore, if we construct a box plot for our `aov` metric, we'll find that the sneakers costing \$25K are clear outliers in our dataset. In addition, the data from this high-end sneaker merchant represents about 1% of the dataset.<jupyter_code>fig1 = px.box(orders_df, y="aov")
fig1.update_yaxes(title_text="AOV Values")
fig1.update_layout(title='Average Order Value Box Plot',template='plotly_dark', showlegend=True)
fig1.show()
print(f"The number of orders for the shoe costing $25,725: {orders_df[orders_df['aov'] >= 20000].shape[0]}")
print(f"The proportion of outlier values in the dataset: {round(100 * (orders_df[orders_df['aov'] >= 20000].shape[0] / orders_df.shape[0]))} %")
orders_df[orders_df['aov'] >= 20000].head(10)<jupyter_output>The number of orders for the shoe costing $25,725: 46
The proportion of outlier values in the dataset: 1 %<jupyter_text>If we wanted to analyze the AOV of our sneaker merchant's customers, we could consider dropping the data from our sole high-end sneaker merchant. Without our outlier data, the average AOV falls slightly closer to our median AOV at approximately \$307. The histogram below demonstrates just how much the outliers skew our data. For example, the mean AOV we reported earlier with the outliers included (highlighted in green) would be the maximum value amongst the distribution of sneaker prices (excluding outliers, of course).<jupyter_code>excl_outliers = orders_df[orders_df['aov'] < 25725]

fig2 = px.histogram(excl_outliers, x='aov')
fig2.update_xaxes(title_text="Sneaker Price Ranges")
fig2.update_yaxes(title_text="Order Count")
fig2.add_vline(x=round(excl_outliers['order_amount'].sum() / excl_outliers['total_items'].sum(), 2), annotation_text="Mean AOV", line_color='red', annotation_position="top left", line_dash="dot")
fig2.add_vline(x=orders_df['aov'].median(), annotation_text="Median AOV", annotation_position="top right", line_color='gold')
fig2.add_vline(x=round(orders_df['order_amount'].sum() / orders_df['total_items'].sum(), 2), annotation_text="Mean AOV (incl. outliers)", annotation_position="top left", line_color='green')
fig2.update_layout(title='Sneaker Price Order Distribution (excl. outliers)',template='plotly_dark', showlegend=True)
fig2.show()
excl_outliers = orders_df[orders_df['aov'] < 25725]
print(f"The Average Order Value (AOV) excluding outliers: $ {round(excl_outliers['order_amount'].sum() / excl_outliers['total_items'].sum(), 2)}")<jupyter_output>The Average Order Value (AOV) excluding outliers: $ 307.01<jupyter_text>The box plot below can also help us visualize how outliers in the dataset can still affect the AOV we calculated, even if we exclude the extreme outliers.<jupyter_code>fig3 = px.box(excl_outliers, y="aov")
fig3.update_yaxes(title_text="AOV Values")
fig3.update_layout(title='Average Order Value Box Plot',template='plotly_dark', showlegend=True)
fig3.show()
excl_outliers.describe()
print(f"Order Amount Sum (${orders_df['order_amount'].sum()}) / Total Items Sum ({orders_df['total_items'].sum()}) = ${round(orders_df['order_amount'].sum() / orders_df['total_items'].sum(), 2)}")
print(f"AOV Sum $({orders_df['aov'].sum()}) / Total Rows ({orders_df.shape[0]}) = {round(orders_df['aov'].sum() / orders_df.shape[0], 2)}")<jupyter_output>Order Amount Sum ($15725640) / Total Items Sum (43936) = $357.92
AOV Sum $(1938714.0) / Total Rows (5000) = 387.74<jupyter_text>**Utility**As we mentioned earlier, in this dataset alone,  we can infer the average price of a customer's order using `aov`, but when combined with other features, we can glean much richer insights. For example, if we plot an area chart with AOV and the timestamps of customer purchases, we can determine the peak selling times on the platform, which in our graph seems to be at 9:00 PM. This information might be helpful for the marketing team in optimizing ads, for example.<jupyter_code>peak_hours = orders_df.groupby(orders_df['created_at'].dt.hour)['aov'].mean()

fig4 = px.area(peak_hours, x=peak_hours.index, y='aov')
fig4.update_xaxes(title_text="Hours")
fig4.update_yaxes(title_text="Average Order Value (AOV)")
fig4.update_layout(height=600, title='Peak Sales Hours', template='plotly_dark', showlegend=True)
fig4.show()<jupyter_output><empty_output><jupyter_text>Additionally, we can also use the `total_items` sold to visualize sales volume. Here we see that, for some reason, there are much more transactions happening at 4 a.m than at any other time on the platform. Could this be an error with our data? Or are there any potential security concerns? Let's find out.<jupyter_code>peak_volume = orders_df.groupby(orders_df['created_at'].dt.hour)['total_items'].mean()

fig5 = px.area(peak_volume, x=peak_volume.index, y='total_items')
fig5.update_xaxes(title_text="Hours")
fig5.update_yaxes(title_text="Total Items")
fig5.update_layout(height=600, title='Peak Sales Volume',template='plotly_dark', showlegend=True)
fig5.show()<jupyter_output><empty_output><jupyter_text>What's interesting about purchases during this hour is that the same customer (`user_id` = 607) repeatedly buys 2,000 items from the same merchant at exactly 4:00 AM. Each transaction always results in the same `order_amount`, `aov`, and `payment_method`. If we had to hazard a guess, we'd suspect that these transactions may be automated, which may or may not warrant further investigation. It's worth noting that when comparing time series plots between `aov` and its constituent predictors, `total_items` and `order_amount`, it wasn't as affected by outliers. Through this metric, we were able to gain a more robust understanding of the times on the platform which saw the most sales. However, we should add a disclaimer that there's no information in the dataset indicating which time zone these DateTime stamps are referring to. Additionally, we have no information on the customer's geographic location at the time of the transaction, so we shouldn't be making any conclusions about these insights without the appropriate contextual data.<jupyter_code>purchases_4am = orders_df[orders_df['created_at'].dt.hour == 4]
print(purchases_4am['total_items'].value_counts(sort=True))
print(f"The number of times where a customer purchased more than 6 items: {orders_df[orders_df['total_items'] > 6].shape[0]}")
purchases_4am[purchases_4am['total_items'] > 6]<jupyter_output>The number of times where a customer purchased more than 6 items: 18<jupyter_text>**Final Thoughts**Average order value (AOV) in the way that stakeholders had calculated initially did not do a good job measuring the outcomes that it was intended to measure. The previous iteration of AOV did not account for orders where customers purchased two or more items which inflated the resulting figure. If every transaction only involved a single item, it would have been correct to divide the total `order_amount` by the total number of transactions in the dataset. However, this was not the case. So to improve the metric and account for the variable number of items purchased, we divided the sum of `order_amount` by the sum of `total_items` to get an accurate figure of the average that customers spent per order.By dividing `order_amount` by `total_items`, we can now accurately track the true average value of our customer's purchases, which turned out to be \$357.92 for the entire dataset. However, if we remove the store that was selling sneakers at outlier prices north of \$25k, we'll find that the average value of our customer's purchases drops slightly down to \$307.  This new AOV value is still affected somewhat by outlier data points valued at \$352. Therefore, another metric we can consider **reporting** once we've calculated each customer's individual AOV is to look at the median AOV, which isn't affected at all by outliers. When combining AOV with other features like the transaction timestamps or by segmenting the data per store, we can start to have a more robust snapshot of the state of our merchant's businesses. In doing so, we've ensured that stakeholders accurately track the outcomes they're interested in following. And from a broader perspective, we've also added **value** to the bottom line by establishing a common ground for truth and empowering leaders to make data-driven decisions about the future of the business.  *Question 2: For this question you’ll need to use SQL. [Follow this link](https://www.w3schools.com/SQL/TRYSQL.ASP?FILENAME=TRYSQL_SELECT_ALL) to access the data set required for the challenge.**Please use queries to answer the following questions. Paste your queries along with your final numerical answers below.*- *How many orders were shipped by Speedy Express in total?*- *What is the last name of the employee with the most orders?*- *What product was ordered the most by customers in Germany?* *Q: How many orders were shipped by Speedy Express in total?*A: There were 54 orders shipped by Speedy Express.<jupyter_code># Q2.1 - How many orders were shipped by Speedy Express in total?
# Since there were only 3 shippers in the "Shippers" table and for computational efficiency, 
# we decided to directly reference Speedy Express' ShipperID in the "WHERE" clause of our SQL query rather than joining the "Shippers" and "Orders" table together.

SELECT COUNT(*) 
FROM Orders
WHERE ShipperID = 1;<jupyter_output><empty_output><jupyter_text>*Q: What is the last name of the employee with the most orders?*A: The employee with the last name Peacock processed the most orders at 40.<jupyter_code># Q2.2 - What is the last name of the employee with the most orders?

SELECT e.LastName, o.EmployeeID, COUNT(o.EmployeeID) AS orders_by_employees
FROM Orders AS o
INNER JOIN Employees AS e ON o.EmployeeID = e.EmployeeID
GROUP BY o.EmployeeID
ORDER BY orders_by_employees DESC
LIMIT 1;<jupyter_output><empty_output><jupyter_text>*Q: What product was ordered the most by customers in Germany?*A: Boston Crab Meat was the most popular item ordered by customers in Germany, amounting to 160 orders and generating $73.60 in total revenue.<jupyter_code># Q2.3 - What product was ordered the most by customers in Germany?

SELECT c.Country, d.ProductID, p.ProductName, ROUND(SUM(p.Price), 2) AS total_product_rev, SUM(d.Quantity) AS total_product_vol
FROM OrderDetails AS d
INNER JOIN Orders AS o ON o.OrderID = d.OrderID
INNER JOIN Products AS p ON p.ProductID = d.ProductID
INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID
WHERE c.Country = 'Germany'
GROUP BY d.ProductID
ORDER BY total_product_vol DESC
LIMIT 1;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07696116184398724, 'fraction_numerical': 0.016118384860820772, 'mean_word_length': 5.735457576880326, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 57, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '362502', 'license': 'CC0-1.0', 'n_tokens_mistral': 5281, 'n_tokens_neox': 4859, 'n_words': 2365, 'path': "Shopify's_Data_Science_Intern_Challenge-Ruiz_Rivera.ipynb", 'repo_name': 'vanislekahuna/Portfolio-Projects', 'size': '18183'}