Maven Fuzzy Factory's Digital Success

I played a role of an eCommerce Analyst for an online retailer Maven Fuzzy Factory which just launched their first product. Maven Fuzzy Factory has been live for 8 months, and your CEO is due to present company performance metrics to the board next week. You’ll be the one tasked with preparing relevant metrics to show the company’s promising growth. I used SQL to analyze and optimize marketing channels, measure and test website conversion performance, and understand the impact of new product launches.

These are the following questions I answered to help the stakeholders with my analysis:

1. Gsearch seems to be the biggest driver of our business. Could you pull monthly trends for gsearch sessions and orders so that we can showcase the growth there?
2. Next, it would be great to see a similar monthly trend for Gsearch, but this time splitting out nonbrand and brand campaigns separately. I am wondering if brand is picking up at all. If so, this is a good story to tell.
3. While we’re on Gsearch, could you dive into nonbrand, and pull monthly sessions and orders split by device type? I want to flex our analytical muscles a little and show the board we really know our traffic sources.
4. I’m worried that one of our more pessimistic board members may be concerned about the large % of traffic from Gsearch. Can you pull monthly trends for Gsearch, alongside monthly trends for each of our other channels?
5. I’d like to tell the story of our website performance improvements over the course of the first 8 months. 5 Could you pull session to order conversion rates by month?
6. For the gsearch lander test, please estimate the revenue that test earned us.
7. For the landing page test you analyzed previously, it would be great to show a full conversion funnel from each of the two pages to orders.
8. I’d love for you to quantify the impact of our billing test, as well. Please analyze the lift generated from the test (Sep 10 – Nov 10), in terms of revenue per billing page session, and then pull the number of billing page sessions for the past month to understand monthly impact.

This is the link for the analysis done in sql: https://github.com/ZainabTheAnalyst/SQLPortfolioProjects/blob/main/MavenFuzzyFactory_Portfolio_Project_1.sql

Insights:

1. Gsearch showed steady session to order conversion rate growth.
2. Non brand sessions are steadily growing while branded sessions showed inconsistencies with highest cvr 9.84% in April while dropping between 4 - 5 % towards the end of year. This could be due to different brands being promoted in different month or season.
3. Most of the session to orders were contributed by desktop users compared to mobile users. Consider moving bids to desktop to optimize traffic and marketing spend.
4. Conversion rate increased from 3.23% in March 2012 to 4.45% in November. Rates start to improve in September hitting 4% and stayed consistent.
5. The new lander page performed better than the previous homepage with 4.14 % cvr compared to 3.11% cvr for the previous homepage with increment in orders accounting for 50 additional orders each month.
6. A lift of $10,152 in revenue is seen after the introduction of new billing page.

SQL FUNCTIONS USED: Aggregate Functions, Joins, Case Statements, CTEs