讲解 MASY1-GC 1240-104 Information Technology and data analytics Spring Semester 2024调试Python程序

Information Technology and data analytics

MASY1-GC 1240-104

Spring Semester 2024

Individual Project

The objective

You have just been hired as a consultant for a new and emerging home improvement store called “Tool Time.”  The year is 2012 and Tool Time has 15 stores and 1500 employees. Tool Time’s flagship store is located in Essex county Massachusetts and they have a corporate headquarters in downtown Essex. They have also recently expanded their store footprint. Tool Time now has stores in Rhode Island and New Hampshire. Each of its 15 stores are doing very well in revenue and profit. They have hired you to consult them on potentially expanding and becoming a franchise in the larger New England Region.  

Tool Time specializes in home improvement and DIY (Do-it-Yourself) supplies. Its store inventory and customers are similar to that of Home Depot and Lowes. The store footprint of Tool Time is not that of Home Depot and Lowes but they believe they have a competitive advantage in the small communities of the New England area.

You have been provided with a vast amount of data on Lowes and Home Depot including store locations by county, Census 2000 and 2010 data, highway counts per county and property tax data by county. A data scientist working for Tool Time has already provided data visualizations, built machine learning models on projected store sites and done an immense amount of research on its competitors (Home Depot and Lowes).

Deliverables and Specifications for your report

-     Less than 10 pages total or 2,750 words double spaced (including charts, graphs, data visualizations, etc)

-     Purpose of the Strategy and CRM solution (1 page)

- The Strategy (3 pages)

- The CRM Solution (3 pages)

- Choose an analytical solution for CRM to analyze, prescribe or predict for management (1/2 page - 1 page)

- New technology innovations, data sources or data pipelines to consider for the business and the CRM solution (1/2 page - 1 page)

1. Strategic

Your objective is to provide a strategic plan to identify how Home Depot and Lowes have selected their store locations in the New England Region. Once you have reverse engineered their strategy, you must develop one for Tool Time and its potential areas to expand its stores and become a franchise. You must select the next 3 store locations in the North East for Tool Time. You are to select the next 3 Tool Time stores where there is NO presence of Home Depot or Lowes in the county. Tool Time believes that they can differentiate themselves in small communities with superior customer service and they can do so without a presence from Home Depot or Lowes in the county. 

2. CRM

To effectively compete against Home Depot and Lowes, Tool Time must intelligently manage its customer relationships to ensure its processes, people and strategy are aligned and fully consistent with its goals of expanding in the New England region. While expanding they must also be fully consistent with its goals of increasing revenues and net profits while enhancing the customer experience. Develop a plan for Tool Time’s CRM implementation, application to its customers, integration with its IT department and success criteria or KPI’s for its use in the corporate headquarters of Tool Time.

CRM Process considerations

1. Identify New Prospects

2. Track and Monitor Sales Opportunities

3. Organize and Engage Current Customers

4. Establish a Relationship with Current Customers

5. Manage Customer Life Cycle

v Components for consideration of your report:

v Phased implementation of the Strategy and the CRM solution

v Strategic Planning

v Performance Standards or KPIs of the CRM solution

v Custom Reporting and Analytic Solutions

v Data migration, data pipelines and data architecture

v Maintenance and Support

v Data Privacy and Responsible AI considerations

v Social Media Integration

v Change Management

v A Data warehouse and data analytics

v Security

v A customer loyalty program

v Cloud or on-prem considerations

v Risk Assessment

Competitor Background

Home Improvement and DIY store history

Home Depot and Lowes have similar store sizes, inventory and slight variations in their customer base. Typically, the stores are 120,000 square feet with a 20,000-square foot joining garden center. Inventory between the stores is quite similar with minor varieties here and there. Through your research and insight from your new colleagues at Tool Time, they have determined that customer demographics, property taxes and a store location with a close proximity to highways are the most important in making strategic decisions on store locations.  

Home Depot’s About Us (https://corporate.homedepot.com/about)

“Our founders’ vision of one-stop shopping for the do-it-yourselfer came to fruition when they opened the first two Home Depot stores on June 22, 1979, in Atlanta, Georgia. The first stores, at around 60,000 square feet each, were cavernous warehouses that dwarfed the competition and stocked 25,000 products, much more than the average hardware store at that time.

Today, The Home Depot is the world’s largest home improvement retailer with nearly 400,000 orange-blooded associates and more than 2,200 stores in the U.S., Canada and Mexico. The typical store today averages 105,000 square feet of indoor retail space, interconnected with an e-commerce business that offers more than one million products for the DIY customer, professional contractors, and the industry’s largest installation business for the Do-It-For-Me customer.”

Lowe’s About Us (http://careers.lowes.com/media/39421/loweshistory.pdf)

Founded in 1946, Lowe’s has grown from a small hardware store to the 2nd largest home improvement retailer worldwide, the 8th largest retailer in the U.S. and 19th largest in the world.

Anticipating DIY needs while still accommodating contractors, Lowe’s began to enlarge its stores and expanded its merchandise offerings. The modern Lowe’s began in 1994, when its new store expansion consisted only of stores greater than 85,000 square feet of selling space. Expansion continues in North America as Lowe's plans to open 10 stores in 2012, with 103,000-square-foot stores in large markets and 94,000-square-foot stores in small to mid-size markets.

Customer Demographics

The customers for Home Depot and Lowes are split into the following categories:

I. Contractors involved in the construction and repairs of homes. This group comes to the store to buy home construction supplies, raw materials and tools

II. DIY consumers are homeowners or renters that live in a dwelling and come to the stores to buy materials and request consulting on their projects from the store staff

III. Finished product consumers –are those that come to the store to purchase goods that have been completed by the manufacturer but may need installation assistance like refrigerators, light fixtures, washer and dryers, etc.

The Data

Files

Home_Depot_Lowes_Data.csv

RDC_MarketHotness_Monthly.csv

state_region.csv

highways.csv

HDLData.csv

Property_Tax.csv

Data Considerations

The New England Region encompasses the following states – Maine, New Hampshire, Connecticut, Vermont, Rhode Island and Massachusetts

Unfortunately, you do not have raw data on the above 3 customer demographic categories. However, the next best data points which relate to these customers are free and available to you – Census Data, Real Estate Data, Property Tax data and US & State Highway data.

The census data provides a broad but telling story of demographics for each county in the United States. Many businesses use this data for market research. The types of customers that Home Depot and Lowes are targeting can be gleaned from this census and store location data. A data dictionary is referenced in the appendix section below.

The property tax data is a dataset from smartasset.com and it lists property taxes by each county. The DIY stores have a large footprint with estimates of 103,000-square-feet in each store’s size. A larger property tax would imply a larger cost of operations per store given the county it is located in. Tool Times store size is similar in square footage to Home Depot and Lowes.

Subjective Insight from Tool Time corporate

The Vice President of Marketing at Tool Time has mentioned  that she noticed Lowes and Home Depot stores are usually located near major highways. The data scientist has collected a count of US Highway, State Highways and Tool Roads per county in the North East Region. The VP is not certain if her theory is to related to the success with Home Depot and Lowes supply chain management or if vehicle traffic off of highway ramps is high and therefore leading to more customers stopping at their stores.

Tool Time executive management also asked the data scientist to research real estate and population data. The data scientist has performed some correlation research and theorized his findings. It is up to you, the consultant, to use or not use this research in your strategy and CRM planning.

Tool Time prides itself on its relationship with its small community locations. Their in-store employees are highly knowledgeable in DIY, tools and getting the job done. Tool Time has received many customer service awards. In addition to the success of their in-store employees, the IT and marketing teams collect survey data and conduct email marketing campaigns to continue to improve their customer loyalty. These considerations should be included in the CRM solution.

Data Research Notes from the Tool Time’s Data Scientist:

1. Data Transformation

a. Performed an inner join on the HDLo and the Region data

b. Isolated data frame. to the New England Region

c. Created 3 new columns

i. Lowes present in county

ii. Home Depot present in county

iii. Home Depot and Lowes not present in county

d. Performed an left  join merging HDLo data set and the highway data

i. Merged by both county name and state abbreviation

e. Performed a left join  HDLo and property tax data data setMerge by both county name and state abbreviation 

2. Performed Exploratory Data Analysis on the stores

a. Corrections to variables  

i. Watch out for missing values. Its ok to remove them or use median values.

ii. Use the Boolean values of present or missing Home Depot or Lowes. There should be a correlation against Lowes and one correlation against Home Depot for each of the independent variables

iii. There is collinearity among the census variables. Subtract the year _2010 values from the year _2000 to devise a unique variable free from collinearity.  Take the absolute value of the subtracted values.

1. Remove columns with _2000 and _2010 once after you have created the normalized field 

3. Exploratory Data Analysis  

a. Total store counts of Home Depot and Lowes in the New England Region

b. Created one dummy variable for Home Depot and one dummy variable for Lowes that identifies if the store is located in a county

4. Data Visualizations

a. Used a United States map with FIPS locations to plot the count of store locations of both Lowes and Home Depot by county

*NOTE: The FIPS numbers are in the “county” column and should be used in conjunction with the map packages

R map packages: maps, maptools, blscrapeR, tmap, ggcorrplot

Python packages: importlib, plotly, plotly.figure_factory, basemap, mpl_toolkits.basemap

An example of populations by county from plot.ly can be seen here - https://plot.ly/python/county-choropleth/#single-state

Created the following visualizations to assist with data understanding:

North East Maps of the following:

1. North East Lowes store presence by count

2. North East Home Depot Presence by county

3. Census 2010 Population in North East

4. Highway county by County

5. Median Annual Property Tax Payment by county

Through a  scatter plot  visualization prove or disprove the following theories:

Note - Use the seaborn package with scatterplot

1. Determined if their is a relationship of income and population with store_present by county

x="income", y="population",

hue="county", size="Store_present"

2. Determined if their is a relationship of highway count and population with store_present by county

x="highway_count", y="population",

hue="county", size="Store_present"

3.  Determined if their is a relationship of Median Annual Property Tax Payment and population with store_present by county

x="highway_count", y="population",

hue="county", size="Store_present" 

5. Through a pandas_profiling report and a spearmen correlation, I found the strength of the correlation for the variables to identify the following

a. Found the variables that are most import to Home Depot

b. Found the variables that are most import to both Home Depot and Lowes

c. Found how are the home improvement store chains similar in their decision making

d. Found how they are different 

6. Found the top 3 counties that can be predicted as potential candidates for new locations for both Lowes and Home Depot?

a. Used a logistic regression, RandomForestClassifier and a Decision Tree algorithms

b. Leveraged a confusion matrix and precision, recall and F1 for evaluations

• Data Dictionary for Census Data

• Areaname – city / town  name

• County – zipcode

• State

• Lcount – count of Lowes stores in the town/city

• Hdcount – count of Home Depot in the town/city

• Pop_2000 – population in 2000

• Pop_2010 – population in 2010

• Income_2000 – avg income in 2000

• Income_2010 – avg income in 2010

• pct_U18_2000 – percent under 18 in 2000

• pct_U18_2010 – percent under 18 in 2010

• Pct_college_2000 – percent in college per town in 2000

• Pct_college_2010 – percent in college per town in 2010

• Ownhome_2000 – percent owned home in 2000

• Ownhome_2010 – percent owned home in 2010

• Density_2000 – percent density per town in 2000

• Density_2010 – percent density per town in 2010

• Pct_white_2000 – percent of Caucasian in town in 2000

• Pct_white_2010 – percent of Caucasian in town in 2010

• Pct_black_2000 – percent African American in 2000

• Pct_black_2010 – percent African American in 2010

Data Dictionary for realtor.com data