Question 1

The Average Order Value (AOV) is the average $ spent each time a customer places an order. It is calculated using the total revenue by the total number of customers’ orders. Validating the AOV stated in the challenge instructions for the existing 30 days, it was confirmed that the monthly AOV is $3145.

Table 1 shows the daily AOV, delta = daily AOV - monthly AOV and its delta classification.

created_date order_day order_amount_day daily_AOV AOV30d delta margin
2017-03-01 182 106154 583 3145 -2562 below monthly AOV
2017-03-02 182 1588109 8726 3145 5581 above monthly AOV
2017-03-03 162 48729 301 3145 -2844 below monthly AOV
2017-03-04 165 855504 5185 3145 2040 above monthly AOV
2017-03-05 163 126293 775 3145 -2370 below monthly AOV
2017-03-06 148 42266 286 3145 -2860 below monthly AOV
2017-03-07 196 1465850 7479 3145 4334 above monthly AOV
2017-03-08 156 43432 278 3145 -2867 below monthly AOV
2017-03-09 199 191287 961 3145 -2184 below monthly AOV
2017-03-10 167 48908 293 3145 -2852 below monthly AOV
2017-03-11 167 781305 4678 3145 1533 above monthly AOV
2017-03-12 159 856797 5389 3145 2244 above monthly AOV
2017-03-13 159 51362 323 3145 -2822 below monthly AOV
2017-03-14 148 122336 827 3145 -2319 below monthly AOV
2017-03-15 143 795442 5563 3145 2417 above monthly AOV
2017-03-16 143 350442 2451 3145 -694 below monthly AOV
2017-03-17 198 915709 4625 3145 1480 above monthly AOV
2017-03-18 173 259864 1502 3145 -1643 below monthly AOV
2017-03-19 149 773732 5193 3145 2048 above monthly AOV
2017-03-20 158 98665 624 3145 -2521 below monthly AOV
2017-03-21 179 101563 567 3145 -2578 below monthly AOV
2017-03-22 175 857995 4903 3145 1758 above monthly AOV
2017-03-23 152 50003 329 3145 -2816 below monthly AOV
2017-03-24 155 1456430 9396 3145 6251 above monthly AOV
2017-03-25 170 857247 5043 3145 1898 above monthly AOV
2017-03-26 175 207534 1186 3145 -1959 below monthly AOV
2017-03-27 170 334819 1970 3145 -1176 below monthly AOV
2017-03-28 175 2165019 12372 3145 9226 above monthly AOV
2017-03-29 165 98929 600 3145 -2546 below monthly AOV
2017-03-30 167 73915 443 3145 -2703 below monthly AOV

Considering the margin data from Table 1, it can be seen at Table 2 that 40% of the days had revenue above of the the monthly AOV ($3145).

Var1 Freq Prop
above monthly AOV 12 0.4
below monthly AOV 18 0.6

Daily AOV graphic shows that only 4 days in March had daily AOV above $7500 or 2x monthly AOV; one of the days had a daily AOV outlier of $12500 or almost 4x monthly AOV.

dailyAOV<- TAB_montly_AOV %>% 
  ggplot(aes(x=created_date, y=daily_AOV))+
  geom_point(col='#014d64')+
  labs(x='', y='daily AOV')+
  theme_economist()+
  labs(title = 'Daily AOV',
       subtitle= "Average Order Value",
       caption='Reference line: monthly AOV'
  )+
  geom_hline(yintercept=AOV_30d,color='#e5001f',lwd=0.4)+
  theme(plot.title = element_text(family = 'Avenir Next Condensed', hjust=0,size=18,margin=margin(-7,0,10,0)),
        plot.subtitle = element_text(family = 'Avenir Next Condensed',size=12, margin=margin(-5,-1,3,0)),
        plot.caption = element_text(family = 'Avenir Next Condensed', size=9, hjust = 0,margin=margin(34,0,-45,0)),  
        axis.text = element_text(face='bold'),
        axis.title.x = element_text(family = 'Avenir Next Condensed',face = 'bold', margin=margin(12,0,-12,0)),
        axis.title.y = element_text(family = 'Avenir Next Condensed',face = 'bold', margin=margin(0,12,0,0)),
        plot.margin = margin(1,1.5,2,0.5, 'cm'))
dailyAOV

Comparing Daily AOV - Monthly AOV, the maximum daily margin gain was $9226 and the maximum daily margin lost was $-2867.

##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   -2867   -2574   -1801     -50    2004    9226
daily_margin<- TAB_montly_AOV %>% 
  ggplot(aes(x=created_date,y=delta))+
  geom_bar(stat="identity", position="identity", aes(fill=margin)) +
  labs(x='', y="delta")+
  theme_economist()+
  labs(title = 'AOV Margins',
       subtitle= "Daily AOV - Monthly AOV",
       caption='Source: Shopify'
  )+
  
  scale_fill_manual(values=c("steelblue","firebrick1")) +
  
  theme(plot.title = element_text(family = 'Avenir Next Condensed', hjust=0,size=18,margin=margin(-7,0,10,0)),
        plot.subtitle = element_text(family = 'Avenir Next Condensed',size=12, margin=margin(-5,-1,3,0)),
        plot.caption = element_text(family = 'Avenir Next Condensed', size=9, hjust = 0,margin=margin(34,0,-45,0)),  
        axis.text = element_text(face='bold'),
        axis.title.x = element_text(family = 'Avenir Next Condensed',face = 'bold', margin=margin(12,0,-12,0)),
        axis.title.y = element_text(family = 'Avenir Next Condensed',face = 'bold', margin=margin(0,12,0,0)),
        plot.margin = margin(1,1.5,2,0.5, 'cm')) + 
  ylim(-5000,10000)
daily_margin

Loyalty Analysis - Metric to report

Calculation of the Purchase Frequency indicated that a customer purchased 16.6 times in march (see Table 3:number of orders = 5000, number of unique user=301).

month orders_count user_count Purchase_Freq
Mar 5000 301 17

Looking at the data, I was curious to know if customers were loyal to a particular store. Table 4 shows the number of times a user ordered at the same store and AOV per user per store.

Customers shopped in multiple stores (except for user 607, who only shopped at store 42 for 17 times).

Considering that each user shopped in multiple locations, Table 5 shows that 99% AOV user per store (4551 stores) were classified as class C: less than monthly AOV, and the 1% (46 stores) were class A: Above 2x Monthly AOV.

Var1 Freq Prop
A: Above 2x Monthly AOV 46 0.01
C: less than monthly AOV 4551 0.99

Table 6 displays the number of stores that each user ordered.

Considering the 301 unique users, Table 7 indicates that the majority of individual customers (92%) have been being classified as C: less than monthly AOV, followed by 7% who spent between the monthly AOV and 2x monthly AOV. Only 1% of the customers were A: above 2x monthly AOV; they should be treated as VIP.

Var1 Freq Prop
A: Above 2x Monthly AOV 4 0.013
B: >= monthly AOV <= 2x Monthly AOV 20 0.066
C: less than monthly AOV 277 0.920

Question 2 - SQL

1) How many orders were shipped by Speedy Express in total?

SELECT sh.ShipperName , COUNT(sh.ShipperName) AS ShipperCount
FROM Orders o
INNER JOIN Shippers sh ON
sh.ShipperID= o.ShipperID
WHERE sh.ShipperName=‘Speedy Express’;

ShipperName ShipperCount
Speedy Express 54

2) What is the last name of the employee with the most orders?

SELECT e.LastName, COUNT(od.OrderID) AS OrderCount
FROM Orders o
INNER JOIN Employees e ON
e.EmployeeID= o.EmployeeID
INNER JOIN OrderDetails od ON
od.OrderID= o.OrderID
GROUP BY e.LastName
ORDER BY OrderCount DESC
LIMIT 3;

LastName OrderCount
Peacock 123
Davolio 78
Leverling 74

3) What product was ordered the most by customers in Germany?

SELECT p.ProductName, cus.Country, od.Quantity
FROM Orders o
INNER JOIN OrderDetails od ON
od.OrderID= o.OrderID
INNER JOIN Products p ON
p.ProductID= od.ProductID
INNER JOIN Customers cus ON
cus.CustomerID= o.CustomerID
WHERE Country = ‘Germany’
GROUP BY p.ProductName
order by Quantity DESC
LIMIT 3;

ProductName Country Quantity
Steeleye Stout Germany 100
Teatime Chocolate Biscuits Germany 80
Raclette Courdavault Germany 70