Book description
One of the leading experts on business data mining shows managers how to leverage SQL and Excel to perform sophisticated types of business analysis without the expense of data mining tools and consultants
Explains how to use the relatively simple tools of SQL and Excel to extract useful business information from relational databases
Each chapter discusses why and when to perform a particular type of business analysis to obtain a useful business result, how to design and perform the analysis using SQL and Excel, and what the results look like in SQL and Excel
Presents hints, warnings, and technical asides about Excel, SQL, and data analysis/mining
Table of contents
 Copyright
 Dedication
 About the Author
 Foreword
 Acknowledgments
 Introduction

1. A Data Miner Looks at SQL
 Picturing the Structure of the Data

Picturing Data Analysis Using Dataflows
 What Is a Dataflow?

Dataflow Nodes (Operators)
 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: General Join of Two Tables
 JOIN: Join Two Tables Together Using a Key Column
 SORT: Ordering the Results of a Dataset
 Dataflows, SQL, and Relational Algebra
 SQL Queries
 Subqueries Are Our Friend
 Lessons Learned

2. What's In a Table? Getting Started with Data Exploration
 What Is Data Exploration?
 Excel for Charting
 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
 3. How Different Is Different?

4. Where Is It All Happening? Location, Location, Location
 Latitude and Longitude
 Census Demographics
 Geographic Hierarchies
 Mapping in Excel
 Lessons Learned

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

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
 Lessons Learned
 7. Factors Affecting Survival: The What and Why of Customer Tenure

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

9. What's in a Shopping Cart? Market Basket Analysis and Association Rules
 Exploratory Market Basket Analysis
 Combinations (Item Sets)
 The Simplest Association Rules
 OneâWay Association Rules
 TwoâWay Associations
 Extending Association Rules
 Lessons Learned

10. 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

11. 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

12. Building Customer Signatures for Further Analysis
 What Is a Customer Signature?
 Designing Customer Signatures
 Operations to Build a Customer Signature
 Extracting Features
 Summarizing Customer Behaviors
 Lessons Learned

1. Appendix: Equivalent Constructs Among Databases
 String Functions

Date Time Functions
 Date Constant
 Current Date and Time
 Convert to YYYYMMDD String
 Year, Month, and Day of Month
 Day of Week (Integer and String)
 Adding (or Subtracting) Days from a Date
 Adding (or Subtracting) Months from a Date
 Difference between Two Dates in Days
 Difference between Two Dates in Months
 Extracting Date from Date Time
 Mathematical Functions
 Other Functions and Features
Product information
 Title: Data Analysis Using SQL and Excel
 Author(s):
 Release date: October 2007
 Publisher(s): Wiley
 ISBN: 9780470099513
You might also like
book
Data Analysis Using SQL and Excel, 2nd Edition
A practical guide to data mining using SQL and Excel Data Analysis Using SQL and Excel, …
book
Microsoft Excel 2016 Data Analysis and Business Modeling
Master business modeling and analysis techniques with Microsoft Excel 2016, and transform data into bottomline results. …
book
Practical Data Analysis Using Jupyter Notebook
Understand data analysis concepts to make accurate decisions based on data using Python programming and Jupyter …
video
Data Science Fundamentals Part 2: Machine Learning and Statistical Analysis
21+ Hours of Video Instruction Data Science Fundamentals Part II teaches you the foundational concepts, theory, …