Business Intelligence & Data analysis | Advanced MySQL

Analyze ecommerce data in MySQL to compare traffic sources and campaigns, compute conversions from sessions to orders, and pivot results to track device-based trends for optimizing marketing spend.

Analyze traffic sources and their conversion into sales from email marketing, normal search, and social networks, using utm sources and campaign naming to track website sessions and conversion rate.

Break down top traffic by UTM source and campaign, filter by date, and compare sessions, orders, and conversion rate to reveal which search and brand content drive results.

Limit data to g search and brand, filter by utm source and campaign, count distinct sessions, join orders, and compute conversion rate to reveal 3% of sessions turning into orders.

Analyze bid optimization across multichannel campaigns by evaluating conversion rates and revenue per click to reallocate budgets toward high-performing channels.

Analyze conversion rates from sessions to orders by device type to identify whether desktop or mobile drives more volume, using date and utm filters (source and campaign).

Develop a weekly trend analysis of website sessions by device type, pivoting the data for desktop and mobile within the April 15, 2012 to June 9, 2012 window.

Analyze website page views to identify the most viewed pages, calculate bounce rates and landing page trends, and perform multi-step analysis with a temporary table and competitive analysis using Workbench.

Analyze website pageviews to identify top entry pages using temporary tables, session-based landing pages, and aggregation techniques like count and group by.

Explore bounce rate and conversion rate across landing pages a and b, testing performance and using multi-step analysis with temporary tables to improve the preferred page.

Analyze landing page performance by comparing Lander One with the home page using bounce sessions and page views, guided by a split test and G search numbers.

Analyze weekly trends for the home and landing pages, examining page search numbers, brand traffic, and bounce rate, using multi-step analysis and session-first page joins.

Explore building and analyzing conversion funnels in MySQL, tracking paths from home to product to cart to sales, measuring click-through and conversion rates, identifying drop-offs, and optimizing the customer journey.

Analyze each marketing channel—email, social, direct or search—by sessions, orders, and conversion rate to measure performance and guide focus; compare new channels with existing ones and analyze direct-to-brand traffic.

Analyze regional channel performance across direct, social, search, and email marketing to optimize bidding and budget allocation. Compare sessions, orders, and conversions by channel, audience type, and device.

Analyze weekly trend of the channel portfolio by comparing G Search and the new B Search, tracking total sessions and G Search vs B Search sessions to assess business impact.

Compare b search and g search channels by calculating total sessions, mobile sessions, and the mobile traffic percentage within the date range for the announcement utm campaign.

Calculate conversion rates by device for g search and b search from Aug 22 to Sep 19, left join website sessions with orders, and group by utm source and device.

analyze weekly session volumes by device for g search and bid changes, calculating b search as a percentage of g search across desktop and mobile.

Analyze direct and branded traffic versus organic visits using utm source and campaign data to gauge brand strength and revenue without paid campaigns.

Analyze brand strength by comparing organic search traffic to paid brand sessions over time, using utm sources, campaigns, and monthly trends to quantify channel performance.

Analyze seasonality and business patterns using date functions to forecast trends, optimize resources, and plan for future periods by examining sessions and orders over time.

Analyze average website sessions per hour by weekday to optimize live chat staffing from September 15 to November 15, 2012, using aggregation and date functions in MySQL.

Analyze products and sales, explore launching new items, and perform sales and trend analysis. Examine cross selling, website pathing, product portfolio expansion, and refund credit analysis.

Analyze product sales to understand each product's contribution to revenue and margin, assess the impact of new product launches on the portfolio, and track trend analysis to gauge business health.

Analyze trend data for the new product from April 2012 to April 2013. Compute monthly order volume, conversion rates, and revenue per session, plus sales breakdown by product.

Analyze the website at the product level to understand purchase behavior, comparing pageviews, sessions, orders, and conversion rate for Origin of the Year and Forever Love Year.

Analyze the click-through rate and next-page behavior from the product page, comparing three months before and after launch. Break down sessions by product ids to reveal pre vs post-launch insights.

Analyze product conversion funnels for each product page, build session-level funnels from landing to thank you, and compare two products including the Jan 6 launch.

Analyze cross-selling patterns by examining orders and order items to identify primary and cross-sell products, then test and optimize strategies while measuring conversion rate, revenue impact, and customer purchase behavior.

Analyze pre-post effects of a new product launch by comparing sessions, orders, conversion rate, revenue per session, and average order value using SQL-driven metrics.

Analyze product refunds to monitor refund rates, assess quality and supplier performance, and calculate amounts refunded and timing across price points using join operations on refunds, orders, and order items.

Analyze user behavior, including repeat visits, time to repeat, and repeat channel behavior, to build conversion channels and improve conversion rates for returning customers.

Analyze repeat behavior to identify valuable customers and track return visits by channel, using cookies, unique IDs, and date-based metrics to optimize marketing channels.

Identify repeat visitors by analyzing new and repeated sessions from 2014 website sessions data, using user IDs and timestamps to guide marketing targeting.

Analyze repeat behavior by identifying user sessions, computing the time between the first and second session, and reporting the min, max, and average return intervals using 2014 as the baseline.

Analyze new versus repeat sessions by channel using UTM source and campaign data, distinguishing direct, organic, and paid channels (paid social, brand search) and evaluating conversion rates.

The lecture compares conversion rates and revenue per session for repeat versus new sessions using 2014 as baseline, showing 6% and 4.3 per session for new vs ~8% for repeats.