eCommerce SQL Project - Part 2. Product Sales Analysis
Seasonality & Business Pattern / Analyze Product Launch / Product Conversion Rate
Introduction
Following the eCommerce SQL Project - Part 1. Web Traffic Analysis, here comes the SQL Project - Part 2. I will use the database to dive more deeply into the channel mix, explore paid/free traffic, and utilize time series analysis to understand product sales trends and seasonality to illustrates the company’s growth.
Project Background
The SITUATION: Now, our company Maven Fuzzy Factory has been in the market for 3 years and has generated enough growth to raise a much larger round of venture capital funding. The CEO is close to securing the company’s next round of funding, and she needs my help to tell a compelling story to investors. I will need to pull the relevant data and help the CEO craft a story about a data-driven company that has been producing rapid growth.
The OBJECTIVE: Use SQL to extract and analyze traffic and website performance data to craft a growth story that the CEO can sell. I will dive into the marketing channel activities, the website improvements, sales activities that have contributed to the company’s success to date, and use the opportunity to flex my analytical skills for the investors.
Important Events
- 01/2013: 2nd Product (love bear) was launched, targeting couples for Valentine’s day.
- 12/12/2013: 3rd Product (birthday bear) was launched, targeting the birthday gift market.
- 02/05/2014: 4th Product (bear accessory) was launched as a cross-sell item.
- 12/05/2014: 4th Product (bear accessory) was made available as a primary product - not just a cross-sell item anymore.
7 Analyses Requests From the CEO
1. First, I’d like to show our volume growth. Can you pull overall session and order volume, trended by quarter for the life of the business? Since the most recent quarter is incomplete, you can decide how to handle it.
Now we’re at the end of a 3 year run of this business, we can see some pretty dramatic growth. At the very beginning, there were only have 60 orders, now we’re at about 100 times that many orders. Similar large growth in session volume. It’s been pretty impressive.
2. Next, let’s showcase all of our efficiency improvements. I would love to show quarterly figures since we launched, for session-to-order conversion rate, revenue per order, and revenue per session.
Similar story to above session growth. We’ve gone from session-to-order conversion rates around 3% to up over 8% in the most current quarter. Revenue per order has gone from a flat $49.99 back when the company only sold one product to starting to do some cross-sell and optimization to now receive revenue per order up above $60. Revenue per session was initially around $1.6, now has risen all the way up to over $5 in the most recent quarter.
** That metrics revenue per session is very important because it impacts how much your marketing director will be able to spend to acquire traffic. The higher he can bid, the more traffic you’re going to win because you will be higher in the auctions and people will see your ads more.
3. I’d like to show how we’ve grown specific channels. Could you pull a quarterly view of orders from Gsearch nonbrand, Bsearch nonbrand, brand search overall, organic search, and direct type-in?
One thing in particular that the potential investors would be excited about is the brand search, organic search, direct type-in really picking up. Back in 2012 Q2, we have Google Search nonbrand: brand+free ratio being almost 6:1. In 2015 Q1, the ratio is about 2:1. The business has become much less dependent on these paid nonbrand campaigns and is starting to build its own brand, organic and direct type-in traffic, which has a better margin and takes you out of the dependency of the search engine.
4. Next, let’s show the overall session-to-order conversion rate trends for those same channels, by quarter. Please also make a note of any periods where we made major improvements or optimizations.
Google Search non-brand campaign CVR has increased from 3.2% to well over 8% in the most recent quarter, more than doubling conversion. Similar story with Bing Search, which we were not running a non-brand campaign originally, but we have been able to increase that.
Excitingly, our direct channels - Brand Search, Organic Search, Direct Type-In, have all seen substantial improvements from where they initially were to where they are now. These efficiency improvements will impress investors and it shows that we know what we are doing and striving to improve the business all the time.
5. We’ve come a long way since the days of selling a single product. Let’s pull monthly trending for revenue and margin by product, along with total sales and revenue. Note anything you notice about seasonality.
The mrfuzzy revenue was originally around $3000/month and now we have raised it up to $5500 in February 2015 (March is a partial month). There were even some high months around November and December (Thanksgiving, Christmas) in the $72000 and $79000 range. Apparently, there is a rush leading up to the holiday season at the end of the year, and that is repeated each year.
If we look at love bear, there is a major pop in February every year. This makes sense because this bear was targeted to couples for giving to one another as a gift on Valentine’s Day.
With the birthday bear and mini bear, we may see some similar trends with a spike at the end of the year. It’s a little hard to tell because we don’t have as much data to understand seasonality.
6. Let’s dive deeper into the impact of introducing new products. Please pull monthly sessions to the /products page, and show how the % of those sessions clicking through another page has changed over time, along with a view of how conversion from /products to placing an order has improved.
The main thing to call out other than just the general growth in the sessions making it to the product page is the clickthrough rate going up from 71% at the beginning of the business to 85% in the most recent month. Similarly, the rate of people seeing the product page and then converting to a full paying order has gone up from 7% all the way up to 14% in the most recent month.
All of these improvements that the business has made (i.e. adding additional products that may appeal better to other people, bringing in a product at a lower price point) have positively helped to contribute to the health of the business.
7. We made our 4th product available as a primary product on December 05, 2014 ( it was previously only a cross-sell item). Could you please pull sales data since then, and show how well each product cross-sells from one another?
- Product 1 is still the heavy hitter, followed by product 2, 3. Product 4 is the least likely to be the primary product.
- Product 4 looks like it cross-sold pretty well to product 1, 2, 3. That’s understandable because product 4 was at the lower price point so it’s more of a snackable add-on purchase for the customers. Above 20% of the orders for primary product 1, 2, 3 end up also purchasing product 4.
- One interesting thing is that Product 3 seems to cross-sells pretty well for product 1.
Conclusion
Acting as an analyst, seeing the evolution of the business and how the business took turns in directions based on the analyses results, gives me a concrete way of viewing the value that an analyst can bring to a business. Not only did I improve my SQL data program skills, but I also developed some eCommerce business acumen. When we optimize a business, it makes everything more competitive. Our website gets better, we are selling products in a better way, we then can go to the market and compete better in the ads auction, which eventually will result in more traffic volume.
