Book Image

Data Analysis Using SQL and Excel - Second Edition

By : Gordon S. S. Linoff
Book Image

Data Analysis Using SQL and Excel - Second Edition

By: Gordon S. S. Linoff

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Data Analysis Using SQL and Excel - Second Edition
Gordon S. S. Linoff
Dec, 2015 | 800 pages
No titles found
Table of Contents (18 chapters)
Free Chapter
1
Foreword
Foreword
2
Introduction
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
3
Chapter 1: A Data Miner Looks at SQL
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
4
Chapter 2: What’s in a Table? Getting Started with Data Exploration
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
5
Chapter 3: How Different Is Different?
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
6
Chapter 4: Where Is It All Happening? Location, Location, Location
Chapter 4: Where Is It All Happening? Location, Location, Location
Latitude and Longitude
Census Demographics
Geographic Hierarchies
Mapping in Excel
Lessons Learned
7
Chapter 5: It’s a Matter of Time
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
8
Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
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
9
Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
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
10
Chapter 8: Customer Purchases and Other Repeated Events
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
11
Chapter 9: What’s in a Shopping Cart? Market Basket Analysis
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
12
Chapter 10: Association Rules and Beyond
Chapter 10: Association Rules and Beyond
Item Sets
The Simplest Association Rules
One-Way Association Rules
Two-Way Associations
Extending Association Rules
Lessons Learned
13
Chapter 11: Data Mining Models in SQL
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
14
Chapter 12: The Best-Fit Line: Linear Regression Models
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
15
Chapter 13: Building Customer Signatures for Further Analysis
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
16
Chapter 14: Performance Is the Issue: Using SQL EffectivelyChapter 14
Chapter 14: Performance Is the Issue: Using SQL EffectivelyChapter 14
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
17
EULA
EULA
Appendix Equivalent Constructs Among Databases
Appendix Equivalent Constructs Among Databases
String Functions
Date/Time Functions
Mathematical Functions
Other Functions and Features

Year-over-Year Comparisons

The previous year usually provides the best comparison for what is happening the following year. This section talks about such comparisons, with particular emphasis on one of the big challenges. This year’s data is usually not complete, so how can we make a valid comparison?

Comparisons by Day

The place to start is with day-by-day comparisons from one year to the next. Here is a method where much of the work is done in Excel:

  1. Query the database and aggregate by date.
  2. Load the data into Excel, with all the dates in one column.
  3. Pivot the data into 366 rows (for each day in the year) and a separate column for each year.

This is actually more work than necessary. An easier way is to use the MONTH(), DAY(), and YEAR() functions in SQL to create the resulting table directly, as in the following example using starts from Subscribers:

SELECT MONTH(StartDate) as mon, DAY(StartDate) as dom,
       SUM(CASE WHEN YEAR(StartDate) = 2004 THEN 1 ELSE 0 END) as n2004...
Previous Section
End of Section 5
Next Section