Advanced SQL: MySQL for Ecommerce Data Analysis

Engage with an advanced level MySQL course for ecommerce data analysis by mastering select statements, aggregate functions, and multi-table joins, with a quick review and a skills quiz.

Clarifies prerequisites for the advanced sql course, covering the big six, group by with aggregates, and joins and unions, and advises a beginner course if unsure.

Install the MySQL community server and MySQL workbench, then run code to create the ecommerce analytics database. If you've taken the beginners course, skip to the database creation step.

Install and connect MySQL community server and MySQL Workbench, explore the interface, run a single SQL script to create the database, and follow troubleshooting tips to stay on track.

Download the mac version of MySQL community server from dev.mysql.com, install with the DMG package, and set the root password. Then verify in system preferences before connecting with MySQL Workbench.

Install the Mac version of MySQL Workbench from dev.mysql.com/downloads/workbench, download the DMG archive, move it to the Applications folder, and get ready to create a connection to a community server.

Learn how to install the PC version of the MySQL community server and MySQL workbench on Windows, including download steps, permission prompts, server and client setup, and root password configuration.

Connect MySQL Workbench to the server by creating a new connection with root user, host, port, and a name, then enter the root password.

Explore the MySQL Workbench interface across Mac and PC, identify the management tab, query editor, and results grid, review schemas, run queries, and use the action output for troubleshooting.

Connect MySQL Workbench to the community server, open the provided sequel script to create the Maven Fuzzy Factory database, and adjust timeouts and preferences to handle the large dataset.

Explore the Maven Fuzzy Factory e-commerce database, including orders, order items, refunds, products, website sessions, and paid traffic via utm parameters, to analyze revenue drivers and marketing effectiveness.

Use the use statement to select the Maven fuzzy factory schema, prevent no database selected errors, and run queries like select star from website sessions with the correct default schema.

Analyze traffic sources and performance using SQL to measure volume and conversion rates, and learn bid adjustments to optimize marketing budgets.

Analyze traffic sources to identify which channels drive quality traffic and conversions. Examine website sessions, page views, and orders; use UTM parameters and group by to size campaigns and optimize.

Learn to identify top traffic sources by breaking down session volumes by utm source, utm campaign, and referring domain, using data up to April 12, 2012.

Analyze top traffic sources by session volumes from the website sessions table, grouped by utm_source, utm_campaign, and http_referrer, with a createdat before 2012-04-12, and rank by sessions.

Analyze the non-brand paid search campaign's traffic source conversion rates and the session-to-order conversion rate, determining eligibility against a 4% threshold for sessions prior to April 14, 2012.

Analyze traffic source conversion rates for G search non-brand by linking website sessions with orders, up to April 14, 2012, using utm source and campaign to compute the conversion rate.

Master bid optimization across traffic segments and trend analysis using MySQL date functions, group by, and case pivoting to reveal revenue per click, conversions, and mobile versus desktop performance.

Analyze traffic source trends by using date functions to compare weekly sessions from Google search non-brand before May 10, 2012, assessing the impact of bid changes on overall site sessions.

Analyze traffic source trending for ecommerce, focusing on search non-brand campaigns and bid changes. Group by year and week to count sessions and assess volume shifts from bid changes.

Pull conversion rates from session to order by device type, distinguishing mobile and desktop to improve bid optimization for paid traffic.

Analyze device type session-to-order conversion rates by comparing sessions and orders for desktop and mobile traffic, using a left join and calculations to optimize bids for paid traffic.

Pull weekly desktop and mobile traffic trends for search non-brand since April 15 and assess impact of the May 19 bid change using the case and count pivoting method.

Analyze weekly traffic source trends in ecommerce data using a case pivot method to split desktop and mobile sessions, focusing on g search non-brand traffic from April 15, 2012 onward.

Improve ecommerce performance by analyzing traffic patterns on the Fuzzy Factory site, identifying where users land and how they move through the conversion funnel toward checkout.

Analyze top website pages and entry pages using temporary tables and multi-step SQL queries to identify high-traffic pages and optimize landing page performance.

Identify the most viewed website pages by listing page view URLs with session counts, using a date filter before June 9, 2012 for the new website manager Morgan Rockwell.

Identify top website pages by session volume using the website page views table, highlighting the home page, products page, and Mr. Fuzzy Page, and plan follow-up analyses.

Identify top entry (landing) pages by counting first sessions per page with a created date filter in MySQL, reinforcing SQL techniques for ecommerce data analysis.

Identify top entry pages by analyzing first page views per session with a two-step approach using temporary tables to count sessions by landing page URL.

Leverage SQL to analyze landing page performance, run AB tests, and optimize bounce rates and session-to-cart conversion to boost ecommerce outcomes.

pull bounce rates for traffic landing on the home page, reporting total sessions, bounce sessions, and bounce rate for records created before June 14, 2012.

Learn to calculate bounce rates in MySQL for ecommerce traffic by breaking analysis into steps, using temporary tables and left joins to identify one-page home page sessions.

Isolate the 50/50 ab test period for Lander One, compare home page to the search non-brand landing page, and compute total sessions, bounce sessions, and bounce rate to assess improvement.

Analyze a 50/50 landing page test comparing lander one and the home page for g search non-brand traffic, using a fair date range; lander reduces bounce rate.

Analyze landing page trend analysis by comparing lander one and homepage bounce rates, and track weekly page search bounce rates and sessions for paid traffic rerouted to lander one.

Analyze landing page trend analysis for paid search non-brand traffic, confirming routing to /lander-1 with weekly trends since June 1st and a consolidated multi-step query workflow.

Analyze conversion funnels and testing conversion paths by tracing website page views from home to product, Mr. Fuzzy, and cart, culminating in a sale, using flags, subqueries, and temporary tables.

Analyze conversion funnels from lander one to order; track sessions August 5 to September 5, 2012. Show sessions and click rates to products, Mr. Fuzzy, cart, shipping, and thank you.

Learn to build and analyze a conversion funnel from lander to thank you page using SQL, tracing steps like products, Mr. Fuzzy, cart, shipping, and billing for session-level insights.

Analyze two-version billing page A/B test (billing page vs billing dash two) to compute sessions, orders, and conversion rate within the fair test window, and present a versioned results table.

Demonstrate a 50/50 split-test analysis of billing two versus billing, calculating sessions and orders to show conversion rate improvement from 45% to 62% and plan rollout.

Apply midcourse project skills to craft a data-backed recap of initial performance for the board, using the database to tell a great story.

Extract and analyze website traffic and performance data from the Maven Fuzzy Factory database to quantify growth and revenue impact, and communicate the story to key stakeholders with SQL-driven insights.

Analyze G search sessions and orders with a MySQL ecommerce data analysis mid-course project solution, covering brand versus non-brand trends, device type, conversion rate, and landing page impact.

Analyze ecommerce data by channel mix, comparing paid and free traffic, and slice data by device type to contrast mobile and desktop performance. Apply time series analysis to identify trends.

Analyze a portfolio of marketing channels to bid efficiently and maximize your budget using sessions, orders, and conversion rates, with UTM parameters guiding traffic and sales insights.

Analyze a growing channel portfolio by comparing weekly G search non-brand sessions with B search sessions since August, using week start dates to assess channel impact on ecommerce performance.

Analyze channel portfolios by comparing weekly non-brand search sessions from G and B search in a MySQL ecommerce dataset, using year-week grouping and case-based counts.

Compare be search non-brand and g search non-brand channels, analyzing mobile session counts and the mobile percentage within the date range from August 22 to November 30.

This solution video demonstrates calculating total sessions, mobile sessions, and percent mobile for UTM sources G search and B search, within the August 22, 2012 to November 30, 2012 window.

Analyze cross-channel bid optimization by comparing non-brand sessions and orders by device type and UTM source for G search and B search, August 20 to September 18, revealing conversion rates.

Analyze channel portfolio trends by pulling session volumes for G search and b search non-brand, break them down by device, and show b search as a percent of G search.

Analyze weekly channel portfolio trends by comparing G search and B search sessions (non-brand) across desktop and mobile, using a percent-of-G metric to isolate bid impact.

Analyze direct traffic to measure brand traction and revenue, using null UTM sources to identify direct type in and organic search, and assess the halo effect of paid traffic.

Analyze organic search, direct type-in, and paid brand search sessions by month, then express free-channel traffic as a percent of non-brand traffic to assess brand momentum.

Label channels with a case statement to compute year-month trends and brand as a percent of non-brand, using non-brand sessions to reveal direct, organic, and brand growth.

Explore seasonality and business patterns using MySQL date functions to perform dayparting and hourly, daily, weekly, and quarterly analyses, enabling proactive staffing and trend insights.

Analyze ecommerce seasonality by measuring monthly and weekly patterns in sessions and orders. Build two MySQL queries: one by year and month, the other by week with a week-start label.

Analyze seasonality by tracing monthly and weekly trends of sessions and orders in 2012, using year, month, and week start labels to guide 2013 planning, noting peaks around Black Friday.

Analyze hourly and day-of-week averages of website sessions to populate a grid for live chat staffing, using Sept 15–Nov 15, 2013 and excluding holidays.

Analyze ecommerce website sessions by date, weekday, and hour in MySQL, producing a day-of-week pivoted grid and revealing live chat staffing and daypart trends for business insights.

Analyze product level sales and conversion rate trends, explore cross selling patterns, and monitor product refund rates to keep an eye on quality.

Analyze product sales to reveal each product's contribution to revenue and margin. Track revenue, margin, and average order value by primary product; assess cannibalization and the impact of new launches.

Analyze monthly product level sales by pulling total sales, total revenue, and total margin (revenue minus cost of goods sold) using year and month dimensions starting from 2013-01.

Analyze product-level sales trends by year and month, computing number of sales, total revenue, and total margin for a single product to establish baseline metrics for ecommerce data analysis.

Analyze the impact of a second product launch by computing monthly order volume, conversion rate, revenue per session, and product one and product two sales breakdown since April 1, 2012.

Analyze the impact of the new product launch by examining monthly orders, conversion rates, and revenue per session since April 1, with product one and product two orders.

Analyze product level website interactions to measure how each product converts visitors. Examine the impact of adding new products, cannibalization, click-through rates, and product-specific conversion funnels.

Analyze product-level website pathing to measure sessions, next-page click-through rates, and product-page transitions for pre-launch and post-launch periods, focusing on Mr. Fuzzy and The Love Bear.

Analyze product-level website pathing with MySQL, comparing three months pre- and post-launch to measure product showcase sessions, next-page views, and destinations like Mr. Fuzzy and Love Bear pages.

Assignment on building product-level conversion funnels compares two products from page to sale, analyzing sessions through product page, cart, shipping, billing, and order confirmation using advanced SQL.

Walk through building and comparing product-level conversion funnels for two ecommerce pages, using session flags to track cart, shipping, billing, and order confirmation and convert counts to click-through rates.

Learn SQL-based cross-selling analysis in ecommerce by analyzing orders and order items to identify best product pairings, measure revenue and conversion impacts, and optimize the sales funnel.

Evaluate the impact of adding cross-sell on cart metrics by comparing pre and post periods, measuring click-through rate, products per order, average order value, and revenue per cart session.

Analyze the cross-sell impact on the cart page by comparing pre and post periods, measuring sessions, click-through rate, orders, items per order, and revenue per cart session.

Analyze impact of adding a third product via a pre-post analysis around Dec 12 2013 launch; measure session-to-order conversion rate, average order value, products per order, and revenue per session.

Analyze the impact of launching a third product using a pre-post comparison of website sessions, conversion rate, average order value, products per order, and revenue per session.

Analyze product refund rates to assess supplier quality and pricing effects, using the order item refunds table to measure refunds and impact on revenue.

Analyze monthly refund rates by product to assess quality issues and margins, tracing trends for four products and comparing data with Cindy's story to confirm fixes.

Analyze monthly product refund rates by product using a left join of order items with refunds, grouping by year and month to reveal trends and supplier changes.

Explore customer behavior and repeat sessions to better understand customer value, helping ecommerce optimize and grow profits.

Analyze repeat visits and segments to understand how often customers return and which channels drive return, using the is repeat session flag and browser cookies.

Learn to count users by their repeat sessions to reveal true customer value beyond the first visit, framing the analysis, outlining steps, and building the MySQL query.

Analyze repeat visitors by counting new sessions and their repeat sessions in website data; reveal user counts by repeat sessions to inform customer value and bidding.

Analyze repeat visitor behavior by computing the average, minimum, and maximum days between first and second sessions for customers who return, using date functions and aggregate analysis.

Analyze repeat session timing by computing the min, max, and average days between a user's first and second website sessions using date diff, joins, and aggregate summaries.

Analyze new versus repeat channel patterns to identify which channels drive repeat customer visits and compare new versus repeat sessions by channel, using 2014 to date data.

Analyze new versus repeat channel patterns using UTM source, campaign, and HTTP referrer to reveal direct, organic, and paid brand channels, and compare new and repeat session counts.

Analyze new versus repeat customer performance in sql by calculating conversion rates and revenue per session, using the is repeat session flag for 2014 data up to November 8, 2014.

Analyze new versus repeat sessions to compare conversion rates and revenue per session, using MySQL queries to compute sessions, orders, and revenue, and apply results to paid traffic bidding.