Data Analysis Using SQL and Excel, 2nd Edition

✍ Scribed by Gordon S. Linoff

• Publisher: Wiley
• Year: 2015
• Tongue: English
• Leaves: 863
• Edition: 2
• Category: Library

✦ Synopsis

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.

✦ Table of Contents

Foreword
Introduction
Overview of the Book and Technology
How This Book Is Organized
Who Should Read This Book
Tools You Will Need
What’s on the Website
Summary
Chapter 1: A Data Miner Looks at SQL
Databases, SQL, and Big Data
Picturing the Structure of the Data
Picturing Data Analysis Using Dataflows
SQL Queries
Subqueries and Common Table Expressions Are Our Friends
Lessons Learned
Chapter 2: What’s in a Table? Getting Started with Data Exploration
What Is Data Exploration?
Excel for Charting
Sparklines
What Values Are in the Columns?
More Values to Explore—Min, Max, and Mode
Exploring String Values
Exploring Values in Two Columns
From Summarizing One Column to Summarizing All Columns
Lessons Learned
Chapter 3: How Different Is Different?
Basic Statistical Concepts
How Different Are the Averages?
Sampling from a Table
Counting Possibilities
Ratios and Their Statistics
Chi-Square
What Months and Payment Types Have Unusual Affinities for Which Types of Products?
Lessons Learned
Chapter 4: Where Is It All Happening? Location, Location, Location
Latitude and Longitude
Census Demographics
Geographic Hierarchies
Mapping in Excel
Lessons Learned
Chapter 5: It’s a Matter of Time
Dates and Times in Databases
Starting to Investigate Dates
How Long Between Two Dates?
Year-over-Year Comparisons
Counting Active Customers by Day
Simple Chart Animation in Excel
Lessons Learned
Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Background on Survival Analysis
The Hazard Calculation
Survival and Retention
Comparing Different Groups of Customers
Comparing Survival over Time
Important Measures Derived from Survival
Using Survival for Customer Value Calculations
Forecasting
Lessons Learned
Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
Which Factors Are Important and When
Left Truncation
Time Windowing
Competing Risks
Before and After
Lessons Learned
Chapter 8: Customer Purchases and Other Repeated Events
Identifying Customers
RFM Analysis
Which Households Are Increasing Purchase Amounts Over Time?
Time to Next Event
Lessons Learned
Chapter 9: What’s in a Shopping Cart? Market Basket Analysis
Exploring the Products
Products and Customer Worth
Product Geographic Distribution
Which Customers Have Particular Products?
Lessons Learned
Chapter 10: Association Rules and Beyond
Item Sets
The Simplest Association Rules
One-Way Association Rules
Two-Way Associations
Extending Association Rules
Lessons Learned
Chapter 11: Data Mining Models in SQL
Introduction to Directed Data Mining
Look-Alike Models
Lookup Model for Most Popular Product
Lookup Model for Order Size
Lookup Model for Probability of Response
Naïve Bayesian Models (Evidence Models)
Lessons Learned
Chapter 12: The Best-Fit Line: Linear Regression Models
The Best-Fit Line
Measuring Goodness of Fit Using R2
Direct Calculation of Best-Fit Line Coefficients
Weighted Linear Regression
More Than One Input Variable
Lessons Learned
Chapter 13: Building Customer Signatures for Further Analysis
What Is a Customer Signature?
Designing Customer Signatures
Operations to Build Customer Signatures
Extracting Features
Summarizing Customer Behaviors
Lessons Learned
Chapter 14: Performance Is the Issue: Using SQL Effectively
Query Engines and Performance
Using Indexes Effectively
When OR Is a Bad Thing
Pros and Cons: Different Ways of Expressing the Same Thing
Window Functions
Lessons Learned
Appendix Equivalent Constructs Among Databases
String Functions
Date/Time Functions
Mathematical Functions
Other Functions and Features
EULA