TitleFarmers Insurance Business Locations and Demographic Analysis |
|||
AuthorGeorge Nicholas |
|||
AbstractGIS is clearly entering the business world. Information is visually being brought to the forefront from tables and spreadsheets. Patterns and trends can be seen and mapped; better decisions can be made. Being tasked with finding locations and surrounding demographics is the trend in business analysis. Farmers Insurance has ordered just that. I have geocoded their business addresses in Sacramento county and mapped out census data that provides population and housing statistics. With this data, they will be able to recognize potential policy sales and need for involvment in community outreach programs |
|||
IntroductionAn employee of Farmers Insurance has approached me about viewing the spatial location of all Farmer's Insurance agents located in Sacramento County. He was wondering if better decisions can be made based on area demographics and business locations. I plan on starting my project with an excel spreadsheet submitted by him and then relying on ArcGIS to display and query many different data types I acquire. This project should include initial data quality control, geocoding, location analysis, and map production. I plan on delivering my findings to Farmers in the form of hard copy and digital maps and data using ESRI's ArcExplorer. In the end, Farmers business decisions may be made based on my analysis. |
|||
BackgroundFarmers insurance is based in Los Angeles, California, and provides coverage of all kinds across 41 states servicing more than 15 million customers. There is no doubt a portion of those customers belong in Sacramento county. The company has deeply rooted itslelf in the area possessing 77 business locations totaling 242 agents. While providing auto, life, business, homeowners, and specialty insurances products, Farmers goal is to provide customer security and satisfaction while committing to the improvement of the diverse communities of there customers. At Farmers Communities, customers can experience programs directed towards education, culture, and diversity. According to American FactFinder, Sacramento County's population in 2000 was 1,223,499 people with no one race exceeding more than 65% of the population. Also, the 2000 census reported 474,814 housing units where 263,819 were owner occupied and 189,783 were renter occupied.
|
|||
MethodThis whole project started with an excel spreadsheet which was provided to me by an employee with Farmers Insurance. This spreadsheet contained agent data from all over Northern California. Of course, all I needed was agent data in Sacramento County so there was some considerable "scrubbing" that needed to be performed. Fortunately, excel spreadsheets can now be viewed directly in ESRI's ArcGIS 9.2 software, so I was able to query and extract just the data I needed using ArcMap. After I found all Sacramento County addresses, I cleaned up any addresses that had incorrect street suffixes and suite numbers by performing simple field calculations and manual attribute editing. Lastly, I converted the .xls file to a .dbf for familiarity and use. The scrubbing process was done initially in order to prepare for the major step of geocoding the addresses against the Sacramento County Regional Centerline shape file to find the offices spatial location in the county. The geocoding process is done in 3 steps. First, I setup an address locator based on US Streets accessed in ArcCatalog. The key to setting up the locator is refering the locator to the correct reference data. Second, I added the .dbf file containing Farmers addresses to ArcMap and geocoded the table against the Address locator. This produces the initial results showing how many matched and did not match. Lastly, I chose to match unmatched addresses interactively in order to get a higher match rate. Once I was happy with my match rate, I ended the geocoding process and it produced a point shapefile. This point shape file shows the location of the addressed records in the Farmers agent address table.
The next step in my project was to find demographic data that I could use for analysis. Initially, I started my search at the US Census Bureau. I was able to get a lot of information their but not in the format that I needed. That proved to be a big deterent in my project. Instead, I went back to the data source of the centerline file at the county. There I found census data shape files that contained census block spatial data and demographic attributes. After I had gathered the data I needed, I decided to take a sample from the geocoded addresses rather than do the analysis on all 77 locations in the county. I chose the top 7 addresses that contained the most agents. This was done by summarizing on the Business_A field in the .dbf to find out how many times an address was entered in the table. Then, I deleted all the addresses from the geocoded point file to have only points from the top 7 farmers agents locations. Next, I used the buffer tool in ArcToolbox to create a mile buffer from the 7 points. Then I selected by location in ArcMap the census block groups that intersected the mile buffer ring. From there, I dissolved the census block groups tied to each point and combined the attributes
|
|||
ResultsDue to the amount of data possible, I decided early to narrow my analysis to the top 7 Farmers Insurance business locations, and to analyize 8 demographic attributes around them. Then I turned it into two location studies. The first study outputed the spatial location of the address with the combined census block group population data. This population study counted the total population of the combined block groups and then of that number, how many of each race category: White, Black, Asian, and Hispanic. The second study outputed the spatial location of the address with the combined census block group housing unit data. This housing unit study counted the total number of housing units of the combined block groups and then of that number, how many were owner occupied and renter occupied.
|
|||
AnalysisIn my analysis of this data I hope I provided to Farmers Insurance the ability to visually see community outreach opportunity areas per the 7 office locations and the potential to provide homeowners or renters insurance to many more citizens in these areas. Also with this sample, I hope that they itellectually see the wonders that GIS technology can provide. Products such as ArcMap could be invaluable especially coupled with the Business Analyst extension. Although I have never been directly exposed to it, I have read that this project has a definite need for that software. The Geocoding process was very important in this project. I started with a "dirty" excel spreadsheet that was nowhere near ready to geocode. Yet, after scrubbing the data I was able to get a 97% address match rate. Due to this, I was able to get precise locations stemming from the very reliable Sacramento county centerline file. I definitely ran into difficulty when working with census data. It is very hard to display on maps due to the immense amount of attributes. It is good to be specific and to the point rather than try to show or work with too much. After I finished I found a great resource that may have made me rethink my methods, but I chose not to change due to the complexity. Of course and in most cases, analyst techniques can be done more than one way |
|||
ConclusionsThis was just a sample of many kinds of location analysis studies that can be done on a location. If I were to do this on all 77 addresses it would have taken considerable more time and effort in the form of decisions and output. Many times GIS Analysts are assigned with doing these kinds of tasks but are not asked to make better business decisions. That would be the job of the Business Analyst. However, as GIS expands into the business world, the role or position of GIS Business Analyst may evolve. |
|||
ReferencesMitchell, Andy. 1999. The ESRI Guide to GIS Analysis. V. 1: Geographic
Patterns and Relationships Sears, Bryan, 2004. Directions Magazine Online: http://www.directionsmag.com/article.php?article_id=558&trv=1 Geocoding Challenges: Why Accuracy Matters |
