book

Data Analysis Using SQL and Excel

Name: Data Analysis Using SQL and Excel
Author: Gordon S. Linoff
ISBN: 9780470099513

by Gordon S. Linoff

October 2007

Beginner to intermediate

645 pages

16h 34m

English

Wiley

Read now

Unlock full access

Copyright
Dedication
About the Author
Foreword
Acknowledgments
Introduction
1. A Data Miner Looks at SQL
Picturing the Structure of the DataWhat Is a Data Model?What Is a Table?Allowing NULL ValuesColumn TypesWhat Is an Entity‐Relationship Diagram?The Zip Code TablesSubscription DatasetPurchases DatasetPicturing Data Analysis Using DataflowsWhat Is a Dataflow?Dataflow Nodes (Operators)READ: Reading a Database TableOUTPUT: Outputting a Table (or Chart)SELECT: Selecting Various Columns in the TableFILTER: Filtering Rows Based on a ConditionAPPEND: Appending New Calculated ColumnsUNION: Combining Multiple Datasets into OneAGGREGATE: Aggregating ValuesLOOKUP: Looking Up Values in One Table in AnotherCROSSJOIN: General Join of Two TablesJOIN: Join Two Tables Together Using a Key ColumnSORT: Ordering the Results of a DatasetDataflows, SQL, and Relational AlgebraSQL QueriesWhat to Do, Not How to Do ItA Basic SQL QueryA Basic Summary SQL QueryWhat it Means to Join TablesCross‐Joins: The Most General JoinsLookup: A Useful JoinEquijoinsNonequijoinsOuter JoinsOther Important Capabilities in SQLUNION ALLCASEINSubqueries Are Our FriendSubqueries for Naming VariablesSubqueries for Handling SummariesSubqueries and INRewriting the “IN” as a JOINCorrelated SubqueriesThe NOT IN OperatorSubqueries for UNION ALLLessons Learned
2. What's In a Table? Getting Started with Data Exploration
What Is Data Exploration?Excel for ChartingA Basic Chart: Column ChartsInserting the DataCreating the Column ChartFormatting the Column ChartResize the Chart in the Chart WindowFormat the LegendChange the FontsChange Chart ColorsAdjust the Grid LinesAdjust the Horizontal ScaleUseful Variations on the Column ChartA New QuerySide‐by‐Side ColumnsStacked ColumnsStacked and Normalized ColumnsNumber of Orders and RevenueOther Types of ChartsLine ChartsArea ChartsX‐Y Charts (Scatter Plots)What Values Are in the Columns?HistogramsHistograms of CountsCumulative Histograms of CountsHistograms (Frequencies) for Numeric ValuesRanges Based on the Number of Digits, Using Numeric TechniquesRanges Based on the Number of Digits, Using String TechniquesMore Refined Ranges: First Digit Plus Number of DigitsBreaking Numerics into Equal‐Sized GroupsMore Values to Explore — Min, Max, and ModeMinimum and Maximum ValuesThe Most Common Value (Mode)Calculating Mode Using Standard SQLCalculating Mode Using SQL ExtensionsCalculating Mode Using String OperationsExploring String ValuesHistogram of LengthStrings Starting or Ending with SpacesHandling Upper‐ and LowercaseWhat Characters Are in a String?Exploring Values in Two ColumnsWhat Are Average Sales By State?How Often Are Products Repeated within a Single Order?Direct Counting ApproachComparison of Distinct Counts to Overall CountsWhich State Has the Most American Express Users?From Summarizing One Column to Summarizing All ColumnsGood Summary for One ColumnQuery to Get All Columns in a TableUsing SQL to Generate Summary CodeLessons Learned
3. How Different Is Different?
Basic Statistical ConceptsThe Null HypothesisConfidence and ProbabilityNormal DistributionHow Different Are the Averages?The ApproachStandard Deviation for Subset AveragesThree ApproachesEstimation Based on Two SamplesEstimation Based on DifferenceCounting PossibilitiesHow Many Men?How Many Californians?Null Hypothesis and ConfidenceHow 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 StatisticsStandard Error of a ProportionConfidence Interval on ProportionsDifference of ProportionsConservative Lower BoundsChi‐SquareExpected ValuesChi‐Square CalculationChi‐Square DistributionChi‐Square in SQLWhat States Have Unusual Affinities for Which Types of Products?Data InvestigationSQL to Calculate Chi‐Square ValuesAffinity ResultsLessons Learned
4. Where Is It All Happening? Location, Location, Location
Latitude and LongitudeDefinition of Latitude and LongitudeDegrees, Minutes, Seconds, and All ThatDistance between Two LocationsEuclidian MethodAccurate MethodFinding All Zip Codes within a Given DistanceFinding Nearest Zip Code in ExcelPictures with Zip CodesThe Scatter Plot MapWho Uses Solar Power for Heating?Where Are the Customers?Census DemographicsThe Extremes: Richest and PoorestMedian IncomeProportion of Wealthy and PoorIncome Similarity and Dissimilarity Using Chi‐SquareComparison of Zip Codes with and without OrdersZip Codes Not in Census FileProfiles of Zip Codes with and without OrdersClassifying and Comparing Zip CodesGeographic HierarchiesWealthiest Zip Code in a State?Zip Code with the Most Orders in Each StateInteresting Hierarchies in Geographic DataCountiesDesignated Marketing Areas (DMAs)Census HierarchiesOther Geographic SubdivisionsZip+2 and Zip+4Electoral DistrictsSchool DistrictsCatchment AreasCalculating County WealthIdentifying CountiesMeasuring WealthDistribution of Values of WealthWhich Zip Code Is Wealthiest Relative to Its County?County with Highest Relative Order PenetrationMapping in ExcelWhy Create Maps?It Can't Be DoneMapping on the WebState Boundaries on Scatter Plots of Zip CodesPlotting State BoundariesPictures of State BoundariesLessons Learned

5. It's a Matter of Time
Dates and Times in DatabasesSome Fundamentals of Dates and Times in DatabasesExtracting Components of Dates and TimesConverting to Standard FormatsIntervals (Durations)Time ZonesCalendar TableStarting to Investigate DatesVerifying that Dates Have No TimesComparing Counts by DateOrderlines Shipped and BilledCustomers Shipped and BilledNumber of Different Bill and Ship Dates per OrderCounts of Orders and Order SizesItems as Measured by Number of UnitsItems as Measured by Distinct ProductsSize as Measured by DollarsDays of the WeekBilling Date by Day of the WeekChanges in Day of the Week by YearComparison of Days of the Week for Two DatesHow Long between Two Dates?Duration in DaysDuration in WeeksDuration in MonthsHow Many Mondays?A Business Problem about Days of the WeekOutline of a SolutionSolving It in SQLUsing a Calendar Table InsteadYear‐over‐Year ComparisonsComparisons by DayAdding a Moving Average Trend LineComparisons by WeekComparisons by MonthMonth‐to‐Date ComparisonExtrapolation by Days in MonthEstimation Based on Day of WeekEstimation Based on Previous YearCounting Active Customers by DayHow Many Customers on a Given Day?How Many Customers Every Day?How Many Customers of Different Types?How Many Customers by Tenure Segment?Simple Chart Animation in ExcelOrder Date to Ship DateOrder Date to Ship Date by YearQuerying the DataCreating the One‐Year Excel TableCreating and Customizing the ChartLessons Learned
6. How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Background on Survival AnalysisLife ExpectancyMedical ResearchExamples of HazardsThe Hazard CalculationData InvestigationStop FlagTenureHazard ProbabilityVisualizing Customers: Time versus TenureCensoringSurvival and RetentionPoint Estimate for SurvivalCalculating Survival for All TenuresCalculating Survival in SQLStep 1. Create the Survival TableStep 2: Load POPT and STOPTStep 3: Calculate Cumulative PopulationStep 4: Calculate the HazardStep 5: Calculate the SurvivalStep 6: Fix ENDTENURE and NUMDAYS in Last RowGeneralizing the SQLA Simple Customer Retention CalculationComparison between Retention and SurvivalSimple Example of Hazard and SurvivalConstant HazardWhat Happens to a MixtureConstant Hazard Corresponding to SurvivalComparing Different Groups of CustomersSummarizing the MarketsStratifying by MarketSurvival RatioConditional SurvivalComparing Survival over TimeHow 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 SurvivalPoint Estimate of SurvivalMedian Customer TenureAverage Customer LifetimeConfidence in the HazardsUsing Survival for Customer Value CalculationsEstimated RevenueEstimating Future Revenue for One Future StartSQL Day‐by‐Day ApproachSQL Summary ApproachEstimated Revenue for a Simple Group of Existing CustomersEstimated Second Year Revenue for a Homogenous GroupPre‐calculating Yearly Revenue by TenureEstimated Future Revenue for All CustomersLessons Learned
7. Factors Affecting Survival: The What and Why of Customer Tenure
What Factors Are Important and WhenExplanation of the ApproachUsing Averages to Compare Numeric VariablesThe AnswerAnswering the Question in SQLExtension to Include Confidence BoundsHazard RatiosInterpreting Hazard RatiosCalculating Hazard RatiosWhy the Hazard RatioLeft TruncationRecognizing Left TruncationEffect of Left TruncationHow to Fix Left Truncation, ConceptuallyEstimating Hazard Probability for One TenureEstimating Hazard Probabilities for All TenuresTime WindowingA Business ProblemTime Windows = Left Truncation + Right CensoringCalculating One Hazard Probability Using a Time WindowAll Hazard Probabilities for a Time WindowComparison of Hazards by Stops in YearCompeting RisksExamples of Competing RisksI=Involuntary ChurnV=Voluntary ChurnM=MigrationOtherCompeting Risk “Hazard Probability”Competing Risk “Survival”What Happens to Customers over TimeExampleA Cohort‐Based ApproachThe Survival Analysis ApproachBefore and AfterThree ScenariosA Billing MistakeA Loyalty ProgramRaising PricesUsing Survival ForecastsForecasting Identified Customers Who StoppedEstimating Excess StopsBefore and After ComparisonCohort‐Based ApproachDirect Estimation of Event EffectApproach to the CalculationTime‐Varying Covariate Survival Using SQL and ExcelLessons Learned
8. Customer Purchases and Other Repeated Events
Identifying CustomersWho Is the Customer?How Many?How Many Genders in a HouseholdInvestigating First NamesOther Customer InformationFirst and Last NamesAddressesOther Identifying InformationHow Many New Customers Appear Each Year?Counting CustomersSpan of Time Making PurchasesAverage Time between OrdersPurchase IntervalsRFM AnalysisThe DimensionsRecencyFrequencyMonetaryCalculating the RFM CellUtility of RFMA Methodology for Marketing ExperimentsCustomer MigrationRFM LimitsWhich Households Are Increasing Purchase Amounts Over Time?Comparison of Earliest and Latest ValuesCalculating the Earliest and Latest Values“Find‐the‐Transaction” (Standard SQL Approach)Clever AggregationComparing the First and Last ValuesWhat Happens as Customer Span IncreasesWhat Happens as Customer Order Amounts VaryComparison of First Year Values and Last Year ValuesTrend from the Best Fit LineUsing the SlopeCalculating the SlopeTime to Next EventIdea behind the CalculationCalculating Next Purchase Date Using SQLFrom Next Purchase Date to Time‐to‐EventStratifying Time‐to‐EventLessons Learned
9. What's in a Shopping Cart? Market Basket Analysis and Association Rules
Exploratory Market Basket AnalysisScatter Plot of ProductsDuplicate Products in OrdersHistogram of Number of UnitsProducts Associated with One‐Time CustomersProducts Associated with the Best CustomersChanges in PriceCombinations (Item Sets)Combinations of Two ProductsNumber of Two‐Way CombinationsGenerating All Two‐Way CombinationsExamples of CombinationsVariations on CombinationsCombinations of Product GroupsMulti‐Way CombinationsHouseholds Not OrdersCombinations within a HouseholdInvestigating Products within Households but Not within OrdersMultiple Purchases of the Same ProductThe Simplest Association RulesAssociations and RulesZero‐Way Association RulesWhat Is the Distribution of Probabilities?What Do Zero‐Way Associations Tell Us?One‐Way Association RulesExample of One‐Way Association RulesGenerating All One‐Way RulesOne‐Way Rules with Evaluation InformationOne‐Way Rules on Product GroupsCalculating Product Group Rules Using an Intermediate TableCalculating Product Group Rules Using Window FunctionsTwo‐Way AssociationsCalculating Two‐Way AssociationsUsing Chi‐Square to Find the Best RulesApplying Chi‐Square to RulesApplying Chi‐Square to Rules in SQLComparing Chi‐Square Rules to LiftChi‐Square for Negative RulesHeterogeneous AssociationsRules of the Form “State Plus Product”Rules Mixing Different Types of ProductsExtending Association RulesMulti‐Way AssociationsRules Using Attributes of ProductsRules with Different Left‐ and Right‐Hand SidesBefore and After: Sequential AssociationsLessons Learned
10. Data Mining Models in SQL
Introduction to Directed Data MiningDirected ModelsThe Data in ModelingModel SetScore SetPrediction Model Sets versus Profiling Model SetsExamples of Modeling TasksSimilarity ModelsYes‐or‐No Models (Binary Response Classification)Yes‐or‐No Models with Propensity ScoresMultiple CategoriesEstimating Numeric ValuesModel EvaluationLook‐Alike ModelsWhat Is the Model?What Is the Best Zip Code?A Basic Look‐Alike ModelLook‐Alike Using Z‐ScoresExample of Nearest Neighbor ModelLookup Model for Most Popular ProductMost Popular ProductCalculating Most Popular Product GroupEvaluating the Lookup ModelUsing a Profiling Lookup Model for PredictionUsing Binary Classification InsteadLookup Model for Order SizeMost Basic Example: No DimensionsAdding One DimensionAdding More DimensionsExamining NonstationarityEvaluating the Model Using an Average Value ChartLookup Model for Probability of ResponseThe Overall Probability as a ModelExploring Different DimensionsHow Accurate Are the Models?Adding More DimensionsNaïve Bayesian Models (Evidence Models)Some Ideas in ProbabilityProbabilitiesOddsLikelihoodCalculating the Naïve Bayesian ModelAn Intriguing ObservationBayesian Model of One VariableBayesian Model of One Variable in SQLThe “Naïve” GeneralizationNaïve Bayesian Model: Scoring and LiftScoring with More AttributesCreating a Cumulative Gains ChartComparison of Naïve Bayesian and Lookup ModelsLessons Learned
11. The Best‐Fit Line: Linear Regression Models
The Best‐Fit LineTenure and Amount PaidProperties of the Best‐fit LineWhat Does Best‐Fit Mean?Formula for LineExpected ValueError (Residuals)Preserving the AveragesInverse ModelBeware of the DataTrend Lines in ChartsBest‐fit Line in Scatter PlotsLogarithmic, Power, and Exponential Trend CurvesPolynomial Trend CurvesMoving AverageBest‐fit Using LINEST() FunctionReturning Values in Multiple CellsCalculating Expected ValuesLINEST() for Logarithmic, Exponential, and Power CurvesMeasuring Goodness of Fit Using R2The R2 ValueLimitations of R2What R2 Really MeansDirect Calculation of Best‐Fit Line CoefficientsDoing the CalculationCalculating the Best‐Fit Line in SQLPrice ElasticityPrice FrequencyPrice Frequency for $20 BooksPrice Elasticity Model in SQLPrice Elasticity Average Value ChartWeighted Linear RegressionCustomer Stops during the First YearWeighted Best FitWeighted Best‐Fit Line in a ChartWeighted Best‐Fit in SQLWeighted Best‐Fit Using SolverThe Weighted Best‐Fit LineSolver Is Better Than GuessingMore Than One Input VariableMultiple Regression in ExcelGetting the DataInvestigating Each Variable SeparatelyBuilding a Model with Three Input VariablesUsing Solver for Multiple RegressionChoosing Input Variables One‐By‐OneMultiple Regression in SQLLessons Learned
12. Building Customer Signatures for Further Analysis
What Is a Customer Signature?What Is a Customer?Sources of Data for the Customer SignatureCurrent Customer SnapshotInitial Customer InformationSelf‐Reported InformationExternal Data (Demographic and So On)About Their NeighborsTransaction SummariesUsing Customer SignaturesPredictive and Profile ModelingAd Hoc AnalysisRepository of Customer‐Centric Business MetricsDesigning Customer SignaturesColumn RolesIdentification ColumnsInput ColumnsTarget ColumnsForeign Key ColumnsCutoff DateProfiling versus PredictionTime FramesNaming of ColumnsEliminating SeasonalityAdding Seasonality Back InMultiple Time FramesOperations to Build a Customer SignatureDriving TableUsing an Existing Table as the Driving TableDerived Table as the Driving TableLooking Up DataFixed Lookup TablesCustomer Dimension Lookup TablesInitial TransactionWithout Window FunctionsWith Window FunctionsPivotingPayment Type PivotChannel PivotYear PivotOrder Line Information PivotSummarizingBasic SummariesMore Complex SummariesExtracting FeaturesGeographic Location InformationDate Time ColumnsPatterns in StringsEmail AddressesAddressesProduct DescriptionsCredit Card NumbersSummarizing Customer BehaviorsCalculating Slope for Time SeriesCalculating Slope from Pivoted Time SeriesCalculating Slope for a Regular Time SeriesCalculating Slope for an Irregular Time SeriesWeekend ShoppersDeclining Usage BehaviorLessons Learned
1. Appendix: Equivalent Constructs Among Databases
String FunctionsSearching for Position of One String within AnotherIBMMicrosoftmysqlOracleSAS proc sqlString ConcatenationIBMMicrosoftmysqlOracleSAS proc sqlString Length FunctionIBMMicrosoftmysqlOracleSAS proc sqlSubstring FunctionIBMMicrosoftmysqlOracleSAS proc sqlReplace One Substring with AnotherIBMMicrosoftmysqlOracleSAS proc sqlRemove Leading and Trailing BlanksIBMMicrosoftmysqlOracleSAS proc sqlRIGHT FunctionIBMMicrosoftmysqlOracleSAS proc sqlLEFT FunctionIBMMicrosoftmysqlOracleSAS proc sqlASCII FunctionIBMMicrosoftmysqlOracleSAS proc sqlDate Time FunctionsDate ConstantIBMMicrosoftmysqlOracleSAS proc sqlCurrent Date and TimeIBMMicrosoftmysqlOracleSAS proc sqlConvert to YYYYMMDD StringIBMMicrosoftmysqlOracleSAS proc sqlYear, Month, and Day of MonthIBMMicrosoftmysqlOracleSAS proc sqlDay of Week (Integer and String)IBMMicrosoftmysqlOracleSAS proc sqlAdding (or Subtracting) Days from a DateIBMMicrosoftmysqlOracleSAS proc sqlAdding (or Subtracting) Months from a DateIBMMicrosoftmysqlOracleSAS proc sqlDifference between Two Dates in DaysIBMMicrosoftmysqlOracleSAS proc sqlDifference between Two Dates in MonthsIBMMicrosoftmysqlOracleSAS proc sqlExtracting Date from Date TimeIBMMicrosoftmysqlOracleSAS proc sqlMathematical FunctionsRemainder/ModuloIBMMicrosoftmysqlOracleSAS proc sqlPowerIBMMicrosoftmysqlOracleSAS proc SQLFloorIBMMicrosoftmysqlOracleSAS proc sql“Random” NumbersIBMMicrosoftmysqlOracleSAS proc sqlLeft Padding an Integer with ZerosIBMMicrosoftmysqlOracleSAS proc sqlConversion from Number to StringIBMMicrosoftmysqlOracleSAS proc sqlOther Functions and FeaturesLeast and GreatestIBMMicrosoftmysqlOracleSAS proc sqlReturn Result with One RowIBMMicrosoftmysqlOracleSAS proc sqlReturn a Handful of RowsIBMMicrosoftmysqlOracleSAS proc sqlGet List of Columns in a TableIBMMicrosoftmysqlOracleSAS proc sqlORDER BY in SubqueriesIBMMicrosoftmysqlOracleSAS proc sqlWindow FunctionsIBMMicrosoftmysqlOracleSAS proc sqlAverage of IntegersIBMMicrosoftmysqlOracleSAS proc sql

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Data Analysis Using SQL and Excel, 2nd Edition

Publisher Resources

ISBN: 9780470099513Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills