Data Analysis Using SQL and Excel

Description

A practical guide to data mining using SQL and Excel Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most popular tools for data query and analysis SQL and Excel to perform sophisticated data analysis without the need for complex and expensive data mining tools. Written by a leading expert on business data mining, this book shows you how to extract useful business information from relational databases. You′ll learn the fundamental techniques before moving into the "where" and "why" of each analysis, and then learn how to design and perform these analyses using SQL and Excel. Examples include SQL and Excel code, and the appendix shows how non–standard constructs are implemented in other major databases, including Oracle and IBM DB2/UDB. The companion website includes datasets and Excel spreadsheets, and the book provides hints, warnings, and technical asides to help you every step of the way. Data Analysis Using SQL and Excel, 2nd Edition shows you how to perform a wide range of sophisticated analyses using these simple tools, sparing you the significant expense of proprietary data mining tools like SAS. Understand core analytic techniques that work with SQL and Excel Ensure your analytic approach gets you the results you need Design and perform your analysis using SQL and Excel Data Analysis Using SQL and Excel, 2nd Edition shows you how to best use the tools you already know to achieve expert results. Data Analysis Using SQL and Excel® About the Author Credits Acknowledgments Contents at a Glance Contents Foreword Introduction Chapter 1 A Data Miner Looks at SQL Databases, SQL, and Big Data What Is Big Data? Relational Databases Hadoop and Hive NoSQL and Other Types of Databases SQL Picturing the Structure of the Data What Is a Data Model? What Is a Table? Allowing NULL Values Column Types What Is an Entity-Relationship Diagram? The Zip Code Tables Subscription Dataset Purchases Dataset Tips on Naming Things Picturing Data Analysis Using Dataflows What Is a Dataflow? READ: Reading a Database Table OUTPUT: Outputting a Table (or Chart) SELECT: Selecting Various Columns in the Table FILTER: Filtering Rows Based on a Condition APPEND: Appending New Calculated Columns UNION: Combining Multiple Datasets into One AGGREGATE: Aggregating Values LOOKUP: Looking Up Values in One Table in Another CROSSJOIN: Generating the Cartesian Product of Two Tables JOIN: Combining Two Tables Using a Key Column SORT: Ordering the Results of a Dataset Dataflows, SQL, and Relational Algebra SQL Queries What to Do, Not How to Do It The SELECT Statement A Basic SQL Query A Basic Summary SQL Query What It Means to Join Tables Cross-Joins: The Most General Joins Lookup: A Useful Join Equijoins Nonequijoins Outer Joins Other Important Capabilities in SQL UNION ALL CASE IN Window Functions Subqueries and Common Table Expressions Are Our Friends Subqueries for Naming Variables Subqueries for Handling Summaries Subqueries and IN Rewriting the “IN” as a JOIN Correlated Subqueries NOT IN Operator EXISTS and NOT EXISTS Operators Subqueries for UNION ALL Lessons Learned Chapter 2 What’s in a Table? Getting Started with Data Exploration What Is Data Exploration? Excel for Charting A Basic Chart: Column Charts Inserting the Data Creating the Column Chart Formatting the Column Chart Bar Charts in Cells Character-Based Bar Charts Conditional Formatting-Based Bar Charts Useful Variations on the Column Chart A New Query Side-by-Side Columns Stacked Columns Stacked and Normalized Columns Number of Orders and Revenue Other Types of Charts Line Charts Area Charts X-Y Charts (Scatter Plots) Sparklines What Values Are in the Columns? Histograms Histograms of Counts Cumulative Histograms of Counts Histograms (Frequencies) for Numeric Values Ranges Based on the Number of Digits, Using Numeric Techniques Ranges Based on the Number of Digits, Using String Techniques More Refined Ranges: First Digit Plus Number of Digits Breaking Numeric Values into Equal-Sized Groups More Values to Explore—Min, Max, and Mode Minimum and Maximum Values The Most Common Value (Mode) Calculating Mode Using Basic SQL Calculating Mode Using Window Functions Exploring String Values Histogram of Length Strings Starting or Ending with Spaces Handling Upper- and Lowercase What Characters Are in a String? Exploring Values in Two Columns What Are Average Sales by State? How Often Are Products Repeated within a Single Order? Direct Counting Approach Comparison of Distinct Counts to Overall Counts Which State Has the Most American Express Users? From Summarizing One Column to Summarizing All Columns Good Summary for One Column Query to Get All Columns in a Table Using SQL to Generate Summary Code Lessons Learned Chapter 3 How Different Is Different? Basic Statistical Concepts The Null Hypothesis Confidence and Probability Normal Distribution How Different Are the Averages? The Approach Standard Deviation for Subset Averages Three Approaches Estimation Based on Two Samples Estimation Based on Difference Sampling from a Table Random Sample Repeatable Random Sample Proportional Stratified Sample Balanced Sample Counting Possibilities How Many Men? How Many Californians? Null Hypothesis and Confidence How Many Customers Are Still Active? Given the Count, What Is the Probability? Given the Probability, What Is the Number of Stops? The Rate or the Number? Ratios and Their Statistics Standard Error of a Proportion Confidence Interval on Proportions Difference of Proportions Conservative Lower Bounds Chi-Square Expected Values Chi-Square Calculation Chi-Square Distribution Chi-Square in SQL What States Have Unusual Affinities for Which Types of Products? Data Investigation SQL to Calculate Chi-Square Values Affinity Results What Months and Payment Types Have Unusual Affinities for Which Types of Products? Multidimensional Chi-Square Using a SQL Query The Results Lessons Learned Chapter 4 Where Is It All Happening? Location, Location, Location Latitude and Longitude Definition of Latitude and Longitude Degrees, Minutes, Seconds, and All That Distance between Two Locations Euclidian Method Accurate Method Finding All Zip Codes within a Given Distance Finding Nearest Zip Code in Excel Pictures with Zip Codes The Scatter Plot Map Who Uses Solar Power for Heating? Where Are the Customers? Census Demographics The Extremes: Richest and Poorest Median Income Proportion of Wealthy and Poor Income Similarity and Dissimilarity Using Chi-Square Comparison of Zip Codes with and without Orders Zip Codes Not in Census File Profiles of Zip Codes with and without Orders Classifying and Comparing Zip Codes Geographic Hierarchies Wealthiest Zip Code in a State? Zip Code with the Most Orders in Each State Interesting Hierarchies in Geographic Data Counties Designated Marketing Areas Census Hierarchies Other Geographic Subdivisions Geography on the Web Calculating County Wealth Identifying Counties Measuring Wealth Distribution of Values of Wealth Which Zip Code Is Wealthiest Relative to Its County? County with Highest Relative Order Penetration Mapping in Excel Why Create Maps? It Can’t Be Mapped Mapping on the Web State Boundaries on Scatter Plots of Zip Codes Plotting State Boundaries Pictures of State Boundaries Lessons Learned Chapter 5 It’s a Matter of Time Dates and Times in Databases Some Fundamentals of Dates and Times in Databases Extracting Components of Dates and Times Converting to Standard Formats Intervals (Durations) Time Zones Calendar Table Starting to Investigate Dates Verifying That Dates Have No Times Comparing Counts by Date Order Lines Shipped and Billed Customers Shipped and Billed Number of Different Bill and Ship Dates per Order Counts of Orders and Order Sizes Items as Measured by Number of Units Items as Measured by Distinct Products Size as Measured by Dollars Days of the Week Billing Date by Day of the Week Changes in Day of the Week by Year Comparison of Days of the Week for Two Dates How Long Between Two Dates? Duration in Days Duration in Weeks Duration in Months How Many Mondays? A Business Problem about Days of the Week Outline of a Solution Solving It in SQL Using a Calendar Table Instead When Is the Next Anniversary (or Birthday)? First Year Anniversary This Month First Year Anniversary Next Month Manipulating Dates to Calculate the Next Anniversary Year-over-Year Comparisons Comparisons by Day Adding a Moving Average Trend Line Comparisons by Week Comparisons by Month Month-to-Date Comparison Extrapolation by Days in Month Estimation Based on Day of Week Estimation Based on Previous Year Counting Active Customers by Day How Many Customers on a Given Day? How Many Customers Every Day? How Many Customers of Different Types? How Many Customers by Tenure Segment? Calculating Actives Entirely Using SQL Simple Chart Animation in Excel Order Date to Ship Date Order Date to Ship Date by Year Querying the Data Creating the One-Year Excel Table Creating and Customizing the Chart Lessons Learned Chapter 6 How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value Background on Survival Analysis Life Expectancy Medical Research Examples of Hazards The Hazard Calculation Data Investigation Stop Flag Tenure Hazard Probability Visualizing Customers: Time versus Tenure Censoring Survival and Retention Point Estimate for Survival Calculating Survival for All Tenures Calculating Survival in SQL Calculating the Product of Column Values Adding in More Dimensions A Simple Customer Retention Calculation Comparison between Retention and Survival Simple Example of Hazard and Survival Constant Hazard What Happens to a Mixture? Constant Hazard Corresponding to Survival Comparing Different Groups of Customers Summarizing the Markets Stratifying by Market Survival Ratio Conditional Survival Comparing Survival over Time How Has a Particular Hazard Changed over Time? What Is Customer Survival by Year of Start? What Did Survival Look Like in the Past? Important Measures Derived from Survival Point Estimate of Survival Median Customer Tenure Average Customer Lifetime Confidence in the Hazards Using Survival for Customer Value Calculations Estimated Revenue Estimating Future Revenue for One Future Start Value in the First Year SQL Day-by-Day Approach Estimated Revenue for a Group of Existing Customers Estimated Second Year Revenue for a Homogenous Group Estimated Future Revenue for All Customers Forecasting Existing Base Forecast Existing Base Calculation Calculating Survival on July 1st Calculating the Number of Existing Customers on July 1st How Good Is It? Estimating the Long-Term Hazard New Start Forecast Lessons Learned Chapter 7 Factors Affecting Survival: The What and Why of Customer Tenure Which Factors Are Important and When Explanation of the Approach Using Averages to Compare Numeric Variables The Answer Answering the Question in SQL and Excel Answering the Question Entirely in SQL Extension to Include Confidence Bounds Hazard Ratios Interpreting Hazard Ratios Calculating Hazard Ratios Using SQL and Excel Calculating Hazard Ratios in SQL Why the Hazard Ratio? Left Truncation Recognizing Left Truncation Effect of Left Truncation How to Fix Left Truncation, Conceptually Estimating Hazard Probability for One Tenure Estimating Hazard Probabilities for All Tenures Doing the Calculation in SQL Time Windowing A Business Problem Time Windows = Left Truncation + Right Censoring Calculating One Hazard Probability Using a Time Window All Hazard Probabilities for a Time Window Comparison of Hazards by Stops in Year in Excel Comparison of Hazards by Stops in Year in SQL Competing Risks Examples of Competing Risks I=Involuntary Churn V=Voluntary Churn M=Migration Other Competing Risk “Hazard Probability” Competing Risk “Survival” What Happens to Customers over Time Example A Cohort-Based Approach The Survival Analysis Approach Before and After Three Scenarios A Billing Mistake A Loyalty Program Raising Prices Using Survival Forecasts to Understand One-Time Events Forecasting Identified Customers Who Stopped Estimating Excess Stops Before and After Comparison Cohort-Based Approach Cohort-Based Approach: Full Cohorts Direct Estimation of Event Effect Approach to the Calculation Time-Dependent Covariate Survival Using SQL and Excel Doing the Calculation in SQL Lessons Learned Chapter 8 Customer Purchases and Other Repeated Events Identifying Customers Who Is the Customer? How Many? How Many Genders in a Household? Investigating First Names Other Customer Information First and Last Names Addresses Email Addresses Other Identifying Information How Many New Customers Appear Each Year? Counting Customers Span of Time Making Purchases Average Time between Orders Purchase Intervals How Many Days in a Row Do Customers Make Purchases? RFM Analysis The Dimensions Recency Frequency Monetary Calculating the RFM Cell How Is RFM Useful? A Methodology for Marketing Experiments Customer Migration RFM Limits Which Households Are Increasing Purchase Amounts Over Time? Comparison of Earliest and Latest Values Calculating the Earliest and Latest Values Comparing the First and Last Values Comparison of First Year Values and Last Year Values Trend from the Best Fit Line Using the Slope Calculating the Slope Time to Next Event Idea behind the Calculation Calculating Next Purchase Date Using SQL From Next Purchase Date to Time-to-Event Stratifying Time-to-Event Lessons Learned Chapter 9 What’s in a Shopping Cart? Market Basket Analysis Exploring the Products Scatter Plot of Products Which Product Groups Are Shipped in Which Years? Duplicate Products in Orders Are Duplicates Explained by the Product? Are Duplicates Explained by the Product Group? Are Duplicates Explained by Timing? Are Duplicates Explained by Multiple Ship Dates or Prices? Histogram of Number of Units Which Products Tend to be Sold Multiple Times Within an Order? Changes in Price Products and Customer Worth Consistency of Order Size Products Associated with One-Time Customers Products Associated with the Best Customer Residual Value Product Geographic Distribution Most Common Product by State Which Products Have Broad Appeal Versus Local Appeal Which Customers Have Particular Products? Which Customers Have the Most Popular Products? Which Products Does a Customer Have? Lists Using Conditional Aggregation Aggregate String Concatenation in SQL Server Lists Using String Aggregation in SQL Server Which Customers Have Three Particular Products? Three Products Using Joins Three Products Using Exists Using Conditional Aggregation and Filtering Generalized Set-Within-a-Set Queries Lessons Learned Chapter 10 Association Rules and Beyond Item Sets Combinations of Two Products Number of Two-Way Combinations Generating All Two-Way Combinations Examples of Item Sets More General Item Sets Combinations of Product Groups Larger Item Sets All Item Sets Up to a Given Size Households Not Orders Combinations within a Household Investigating Products within Households but Not within Orders Multiple Purchases of the Same Product The Simplest Association Rules Associations and Rules Zero-Way Association Rules What Is the Distribution of Probabilities? What Do Zero-Way Associations Tell Us? One-Way Association Rules Evaluating a One-Way Association Rule Generating All One-Way Rules One-Way Rules with Evaluation Information One-Way Rules on Product Groups Two-Way Associations Calculating Two-Way Associations Using Chi-Square to Find the Best Rules Applying Chi-Square to Rules Applying Chi-Square to Rules in SQL Comparing Chi-Square Rules to Lift Chi-Square for Negative Rules Heterogeneous Associations Rules of the Form “State Plus Product” Rules Mixing Different Types of Products Extending Association Rules Multi-Way Associations Multi-Way Associations in One Query Rules Using Attributes of Products Rules with Different Left- and Right-Hand Sides Before and After: Sequential Associations Lessons Learned Chapter 11 Data Mining Models in SQL Introduction to Directed Data Mining Directed Models The Data in Modeling Model Set Score Set Prediction Model Sets versus Profiling Model Sets Examples of Modeling Tasks Similarity Models Yes-or-No Models (Binary Response Classification) Yes-or-No Models with Propensity Scores Multiple Categories Estimating Numeric Values Model Evaluation Look-Alike Models What Is the Model? What Is the Best Zip Code? A Basic Look-Alike Model Look-Alike Using Z-Scores Example of Nearest-Neighbor Model Lookup Model for Most Popular Product Most Popular Product Calculating Most Popular Product Group Evaluating the Lookup Model Using a Profiling Lookup Model for Prediction Using Binary Classification Instead Lookup Model for Order Size Most Basic Example: No Dimensions Adding One Dimension Adding More Dimensions Examining Nonstationarity Evaluating the Model Using an Average Value Chart Lookup Model for Probability of Response The Overall Probability as a Model Exploring Different Dimensions How Accurate Are the Models? ROC Charts and AUC Creating an ROC Chart Calculating Area under the Curve (AUC) Adding More Dimensions Naïve Bayesian Models (Evidence Models) Some Ideas in Probability Probabilities and Conditional Probabilities Odds Likelihood Calculating the Naïve Bayesian Model An Intriguing Observation Bayesian Model of One Variable Bayesian Model of One Variable in SQL The “Naïve” Generalization Naïve Bayesian Model: Scoring and Lift Scoring with More Attributes Creating a Cumulative Gains Chart Comparison of Naïve Bayesian and Lookup Models Lessons Learned Chapter 12 The Best-Fit Line: Linear Regression Models The Best-Fit Line Tenure and Amount Paid Properties of the Best-fit Line What Does Best-Fit Mean? Formula for Line Expected Value Error (Residuals) Preserving Averages Inverse Model Beware of the Data Trend Lines in Charts Best-Fit Line in Scatter Plots Logarithmic, Power, and Exponential Trend Curves Polynomial Trend Curves Moving Average Best-Fit Using the LINEST() Function Returning Values in Multiple Cells Calculating Expected Values LINEST() for Logarithmic, Exponential, and Power Curves Measuring Goodness of Fit Using R2 The R2 Value Limitations of R2 What R2 Really Means Direct Calculation of Best-Fit Line Coefficients Calculating the Coefficients Calculating the Best-Fit Line in SQL Price Elasticity Price Frequency Price Frequency for $20 Books Price Elasticity Model in SQL Price Elasticity Average Value Chart Weighted Linear Regression Customer Stops during the First Year Weighted Best Fit Weighted Best-Fit Line in a Chart Weighted Best-Fit in SQL Weighted Best-Fit Using Solver The Weighted Best-Fit Line Solver Is More Accurate Than a Guessing Game More Than One Input Variable Multiple Regression in Excel Getting the Data Investigating Each Variable Separately Building a Model with Three Input Variables Using Solver for Multiple Regression Choosing Input Variables One-By-One Multiple Regression in SQL Lessons Learned Chapter 13 Building Customer Signatures for Further Analysis What Is a Customer Signature? What Is a Customer? Sources of Data for the Customer Signature Current Customer Snapshot Initial Customer Information Self-Reported Information External Data (Demographic and So On) About Their Neighbors Transaction Summaries and Behavioral Data Using Customer Signatures Data Mining Modeling Scoring Models Ad Hoc Analysis Repository of Customer-Centric Business Metrics Designing Customer Signatures Profiling versus Prediction Column Roles Identification Columns Input Columns Target Columns Foreign Key Columns Cutoff Date Time Frames Naming of Columns Eliminating Seasonality Adding Seasonality Back In Multiple Time Frames Operations to Build Customer Signatures Driving Table Using an Existing Table as the Driving Table Derived Table as the Driving Table Looking Up Data Fixed Lookup Tables Customer Dimension Lookup Tables Initial Transaction Pivoting Payment Type Pivot Channel Pivot Year Pivot Order Line Information Pivot Summarizing Basic Summaries More Complex Summaries Extracting Features Geographic Location Information Date Time Columns Patterns in Strings Email Addresses Addresses Product Descriptions Credit Card Numbers Summarizing Customer Behaviors Calculating Slope for Time Series Calculating Slope from Pivoted Time Series Calculating Slope for a Regular Time Series Calculating Slope for an Irregular Time Series Weekend Shoppers Declining Usage Behavior Lessons Learned Chapter 14 Performance Is the Issue: Using SQL Effectively Query Engines and Performance Order Notation for Understanding Performance A Simple Example Full Table Scan Parallel Full Table Scan Index Lookup Performance of the Query Considerations When Thinking About Performance Storage Management (Memory and Disk) Indexes Processing Engine and Parallel Processing Performance: Its Meaning and Measurement Performance Improvement 101 Ensure that Types are Consistent Reference Only the Columns and Tables That Are Needed by the Query Use DISTINCT Only When Necessary UNION ALL: 1, UNION: 0 Put Conditions in WHERE Rather Than HAVING Use OUTER JOINs Only When Needed Using Indexes Effectively What Are Indexes? B-Trees Hash Indexes Spatial Indexes (R-Trees) Full Text Indexes (Inverted Indexes) Variations on B-Tree Indexes Simple Examples of Indexes Equality in a Where Clause Variations on the Theme of Equality Inequality in a WHERE Clause ORDER BY Aggregation Limitations on Indexes Effectively Using Composite Indexes Composite Indexes for a Query with One Table Composite Indexes for a Query with Joins When OR Is a Bad Thing Sometimes UNION ALL Is Better Than OR Sometimes LEFT OUTER JOIN Is Better Than OR Sometimes Multiple Conditional Expressions Are Better Pros and Cons: Different Ways of Expressing the Same Thing What States Are Not Recognized in Orders? The Most Obvious Query A Simple Modification A Better Version An Alternative Using LEFT JOIN How Big Would That Intermediate Table Be? A GROUP BY Conundrum A Basic Query Pre-aggregation Fixes the Performance Problem Correlated Subqueries Can Be a Reasonable Alternative Be Careful With COUNT(*) = 0 A Naïve Approach NOT EXISTS Is Better Using Aggregation and Joins A Slight Variation Window Functions Where Window Functions Are Appropriate Clever Use of Window Functions Number of Active Subscribers Number of Active Households with a Twist Number of Maximum Prices Most Recent Holiday Lessons Learned Appendix Equivalent Constructs Among Databases Index EULA