Lab 23 - Google Analytics & BigQuery (SQL) - Conversion Funnel Analysis

The holy grail for Data Scientists in Digital Marketing is understanding the Conversion Funnel - Learn which steps are the most important for transactions, then optimize for Conversion.

Google Analytics is a powerful tool that marketing organizations use to mine for web trends. In this presentation, we show how to mine the Google Merchandise Store for trends using BigQuery, a petabyte-scale database that integrates with Google Analytics 360. We perform a Conversion Funnel analysis to find which Page-Paths (steps in conversion) are most important to successful transactions.

Transcript

1. Conversion Funnel Analysis (with SQL & R) Matt Dancho & David Curry Business Science Learning Lab Google Analytics + BigQuery
2. Learning Lab Structure • Presentation (20 min) • Demo’s (30 min) • Pro-Tips (15 mins)
3. Success Story Susan Welch - Chemist/Data Analyst - Marathon Runner! - Took Business Science Courses - App to Track Marathon Performance “Your courses gave me the skills to make this dashboard easily.” #Business Science Success
4. Agenda • Business Case Study ◦ Google Merchandise Store ◦ Conversion Funnel • Google Analytics ◦ Terminology • Big Query ◦ 80/20 Data Concepts • 30-Min Demo ◦ BigQuery SQL • Pro-Tips & Learning Guide ◦ Recap + Pro-Tips ◦ Learning Plan
5. Learning Labs PRO Every 2-Weeks 1-Hour Course Recordings + Code + Slack $19/month university.business-science.io Lab 22 SQL for Time Series Lab 21 SQL for Data Science Lab 20 Explainable Machine Learning Lab 19 Using Customer Credit Card History for Networks Analysis Lab 18 Time Series Anomaly Detection with anomalize Lab 17 Anomaly Detection with H2O Machine Learning Lab 16 R’s Optimization Toolchain, Part 2 Continuous Learning Jet Fuel for your Brain
6. Google Merchandise Store Business Case
7. Google Merchandise Store Google Analytics Data Customers can purchase t-shirts, gear, etc Google Analytics tracks every event on the website. We can use this for Conversion Funnel Analysis. https://shop.googlemerchandisestore.com/
8. Google Analytics Data For Google Merchandise Store 1. Google made their data public to help Google Analytics 360 customers leverage BigQuery 2. BigQuery can store BILLIONS of GA Records 3. Super granular - Search Paths, Transactions, Hits… for every session 4. Goal: Analyze the Conversion Funnel
9. Conversion Funnel Analysis Understanding the Transaction Funnel is the most important concept for a Digital Marketer. But which step(s) is/are most important? Key Concept - The hit number is the sequence of events. • Hit No. 1 is the first interaction. • Hit No. 20 is the 20th interaction. • Median Hit No. helps us identify the event position in the sequence.
10. Conversion Funnel Analysis Understanding the Transaction Funnel is the most important concept for a Digital Marketer. But which step(s) is/are most important? Adding items to the basket is 4X more frequent in successful transactions than visiting home; 6X more than the store.
11. Google Analytics Terminology
12. Terminology Session Record for all user interactions with a website from entry to exit. Session Record Stores: • Hits • Transactions • And more
13. Terminology Hits Tracks events that happen during the session. • Pages the User Navigates To • Social Interactions • Ecommerce Hit
14. Terminology Page Paths Each page that an event occurs on has a pagePath associated with it. GA tracks these page paths.
15. BigQuery 80/20 Data Concepts
16. BigQuery + Google Analytics 360 Integration Designed for Petabyte Scale Can store Billions of Google Analytics Records Integrates with Google Analytics 360
17. Table Structure ga_sessions_20170103 ga_sessions_20170102 ga_sessions_20170101 Tables is time-based by each day Comes in handy with Wildcard Search to combine tables (more on this later)
18. Table Structure Data can be Nested • Different than Redshift & Azure, which are built on PostgreSQL • Stored as JSON, similar to mongoDB NoSQL BigQuery uses SQL Dialect with special features: • NEST() & UNNEST()
19. Table Structure Data can be Nested • Different than Redshift & Azure, which are built on PostgreSQL • Stored as JSON, similar to mongoDB NoSQL BigQuery uses SQL Dialect with special features: • NEST() & UNNEST()
20. 30-Min Demo Home Loan Applications
21. PRO-TIPS Yeahhhhhh!
22. Pro-Tips #1. Use dplyr if you don’t need to UNNEST dbplyr backend connects to BigQuery #2. If UNNEST is required, break up complex joins into smaller queries Test sub-queries on data # 3. Combine with str_glue() Use str_glue() to form complex joins
23. What We Just Did And how WE did it!
24. Conversion Funnel Workflow Step-By-Step Start Finish 1 2 3 bigrquery Connect to BigQuery database containing Google Analytics sessions data dbplyr & SQL Query Aggregations & Joins dplyr & ggplot2 Visualize Conversion Funnel Detect Most Important Step(s)
25. bigrquery
26. dbplyr & SQL
27. dplyr & ggplot2 Adding items to the basket is 4X more frequent in successful transactions than visiting home; 6X more than the store.
28. A lot more to learn… Advanced ML 201
29. A lot more to learn… Web Apps 102 & 202A
30. Business Science University Learn Data Science for Business in 6-Months
31. The program that will deliver YOUR Transformation
32. Advanced Visualization Advanced Data Wrangling Advanced Functional Programming & Modeling Advanced Data Science Visualization Data Cleaning & Manipulation Functional Programming & Modeling Business Reporting Business Analysis with R (DS4B 101-R) Data Science For Business with R (DS4B 201-R) Web Apps & Shiny Developer (DS4B 102-R + DS4B 202A-R) Web Apps Data Science Foundations 7 Weeks Machine Learning & Business Consulting 10 Weeks Web Application Development 12 Weeks -TRACK Project-Based Courses with Business Application Business Science University R-Track 4-Course R-Track System
33. Key Benefits - Fundamentals - Weeks 1-5 (25 hours of Video Lessons) - Data Manipulation (dplyr) - Time series (lubridate) - Text (stringr) - Categorical (forcats) - Visualization (ggplot2) - Programming & Iteration (purrr) - 3 Challenges - Machine Learning - Week 6 (8 hours of Video Lessons) - Clustering (3 hours) - Regression (5 hours) - 2 Challenges - Learn Business Reporting - Week 7 - RMarkdown & plotly - 2 Project Reports: 1. Product Pricing Algo 2. Customer Segmentation Visualization Data Cleaning & Manipulation Functional Programming & Modeling Business Reporting Business Analysis with R (DS4B 101-R) Data Science Foundations 7 Weeks
34. Key Benefits Understanding the Problem & Preparing Data - Weeks 1-4 - Project Setup & Framework - Business Understanding / Sizing Problem - Tidy Evaluation - rlang - EDA - Exploring Data -GGally, skimr - Data Preparation - recipes - Correlation Analysis - 3 Challenges Machine Learning - Weeks 5, 6, 7 - H2O AutoML - Modeling Churn - ML Performance - LIME Feature Explanation Return-On-Investment - Weeks 7, 8, 9 - Expected Value Framework - Threshold Optimization - Sensitivity Analysis - Recommendation Algorithm Data Science For Business (DS4B 201-R) Machine Learning & Business Consulting 10 Weeks Advanced Visualization Advanced Data Wrangling Advanced Functional Programming & Modeling Advanced Data Science End-to-End Churn Project
35. Key Benefits Learn Shiny & Flexdashboard - Build Applications - Learn Reactive Programming - Integrate Machine Learning App #1: Predictive Pricing App - Model Product Portfolio - XGBoost Pricing Prediction - Generate new products instantly App #2: Sales Dashboard with Demand Forecasting - Model Demand History - Segment Forecasts by Product & Customer - XGBoost Time Series Forecast - Generate new forecasts instantly Shiny Apps for Business (DS4B 102-R) Web Application Development 4 Weeks Web Apps Machine Learning
36. Key Benefits Frontend + Backend + Production Deployment Frontend for Shiny - Bootstrap Backend for Shiny - MongoDB - Dynamic UI - User Authentication - Store & Write User Data Production Deployment - AWS - EC2 Server - VPC Connection - URL Routing Shiny Apps for Business (DS4B 202A-R) Web Application Development 6 Weeks
37. -TRACK BUNDLE 15% OFF PROMO Code: learninglabs $127/mo Limited Time
38. Begin Learning Today university.business-science.io