In the previous lesson, you created a map of salons and potential new customers.
In this lesson, you'll enrich the customer data by adding household income data to help determine who can afford the salon's services. You'll also analyze the map to identify which of these potential customers are within a reasonable distance from the new salon—say, a 15-minute driving distance. Finally, you'll use your analysis results to select customers who meet these requirements, so you can create a customized mailing list for your promotional package.
Enrich customer data
You can enhance your data by adding demographic data from ArcGIS as new columns to the spreadsheet. Then you can use standard Excel features to further refine your data and your map. Because your high-end salons are aimed at more affluent customers, you'll add income data and filter the table to find customers whose annual income exceeds $90,000.
Because data enrichment adds new columns to your spreadsheet, the data that you used to create the map layer must be in an Excel table. Otherwise, to enrich a layer that was added from a cell range, you'd need to overwrite an existing column. To learn more, see Enrich your data.
- If necessary, open the lesson spreadsheet (salonCustomersOttawaArea.xlsx) in Excel and sign in to ArcGIS.
- On the customer layer card, hover over the right side of the card to display the layer tools and click the Layer settings button.
- On the layer settings pane, in the Data group, click Enrich Data.
This tool enriches your data by getting facts about the people, places, and businesses that surround your data locations. You can choose from a list of countries to access the available demographic categories. You can also search for specific variables.
- In the Enrich layer window, make sure Canada is listed.
- Click Income.
There are several available income variables for Canada; the window displays the most popular variables.
- Check the box next to the 2013 Median Household Income (Constant Year 2005$) variable.
Demographic data is updated periodically, so the available variables and values may differ from those specified in the lesson. If necessary, use the most recent data.
- Click Next.
- Set the study area to a 1-mile radius from each feature. In the Sample distance for features field, type 1. Ensure that the Sample area for features value is Ring and the distance is in miles.
- Choose <Create new column> from the menu and click Add data to worksheet.
The demographic data will be added to a new column in the sheet. This may take a few minutes. A message will indicate that the data has been added successfully.
- Click OK. Move the Map window so you can see the new column in Excel.
ArcGIS Maps for Office created a new column in the table and populated it with the income variable that you chose. Your customer data now includes median household income within a 1-mile radius of each customer location.
Filter the spreadsheet
Next, you'll use standard Excel features to filter only those records that fit within your target income range. To identify customers with a household income above $90,000, you'll filter the new column to display only rows with a value greater than 89999.
- In the Excel table, click the arrow in the 2013 Med Hh Inc column to display the context menu. Point to Number Filters, and click Greater Than.
- In the Custom AutoFilter window, next to the is greater than value field, type 89999.
- Click OK.
The map automatically updates to show only the customers in areas where the household income is $90,000 or higher.
If you minimized the Map window, to open it, click the ArcGIS Maps tab, click Go To Map, and choose your map.
- Click one of the features on the map to view its pop-up.
The pop-up now includes the 2013 median household income for the area surrounding that feature.
- Close the pop-up.
Find nearby customers
You want to specifically promote the new salon in the East end of the city. To identify potential customers within a 15-minute drive time of this location, you'll perform analysis using the Find Nearby tool.
- On the salons layer, display the layer tools and click the Layer settings button.
- On the layer tools pane, in the Analyze group, click Find Nearby.
The Find Nearby pane opens. You can use this analysis tool to find locations in a layer that are within a specified distance or drive time of the features that you select on the map.
- Click the rectangle selection tool on the right side of the map and click the smallest salon symbol on the map.
If you click the wrong feature on the map, click anywhere on the map to deselect it. Zoom in the map (if necessary), and click the correct salon.
The symbol is highlighted on the map.
- Click Next.
- For Choose a search area and distance, choose Drive time. Specify a 15-minute duration.
- Click Next.
- For Choose target layer, make sure customers is selected.
- Click Next.
The summary page describes the parameters you set the Find Nearby operation. This will find features in the customer layer that are located within a 15-minute drive of the selected salon feature.
- Click Run analysis.
The map updates, highlighting all the potential customers who could drive to the new salon in 15 minutes or less. A message also appears in the Find Nearby pane indicating that 43 potential customers were found within the 15-minute drive time area.
Next, you'll create a new table for these selected customer records so you can add them to the map as a new layer.
Export selected features
Because you enriched the data and filtered it to show only customers in high-income areas, you know that the selected customers fit within the target demographic for your promotion. Now that you've identified potential customers near the new salon, you can export the selected features to a new worksheet to isolate only the customers that you want to contact directly.
- In the Find Nearby pane, click
Export selected rows.
A window opens that prompts you to choose whether to save the selection to a separate file or to a new worksheet in the current workbook. For this lesson, you'll create a new worksheet in the current workbook. You can also specify a spatial reference for the coordinates that define the location of the features on the map. You'll just accept the current spatial reference (4326 World Geodetic Survey (WGS) 1984), which is the spatial reference for the current basemap, Dark Grey Canvas.
- In Export selection to, choose Worksheet and click OK.
A new worksheet is created; this new worksheet contains an Excel table that lists the 43 potential customers within a 15-minute drive to the new salon. The default table name is Sheet1 8_customers. Because the customer locations were already geocoded, the new table also includes longitude and latitude coordinates.
Now you'll rename the Excel table.
- On the Formulas tab, in the Defined Names group, click Name Manager.
- Click the customers_Sheet1_8_customers entry and click Edit.
The default name for your new table may have a different number in it.
- In the Edit Name window, change the name to salonPromo and click OK.
Table names in Excel cannot contain spaces, and the first character of the name must be a letter.
- In the Name Manager window, click Close. (You may need to click the Excel application in the task bar to bring this window to the front.)
- In the Map window, close the Find Nearby pane.
You now have a list of all the potential customers within a 15-minute driving distance of the new salon. You can use this list to send those customers a personalized promotional kit to entice them to try the salon.
View analysis results on the map
Now you'll use your new Excel table to add a layer to the map that shows only the customers that you identified for the promotion. Because the new table includes longitude and latitude coordinates, you can use these columns to define the location type to avoid using ArcGIS service credits for geocoding.
- On the Map ribbon, click Excel data.
- In the Add data from Excel window, for Dataset, choose salonPromo.
- Specify the location information for the salonPromo customers:
- For Location Type, choose Coordinates. The Match columns with location types pane opens.
- From the Longitude (X) and Latitude (Y) drop-down menus, choose corresponding columns in your data.
- Accept the default World Geodetic Survey (WGS) 1984 spatial reference.
- You only want to show the customers' proximity to a salon, so you'll style the layer by location only. For Map style by column, choose <None>. Accept the default theme, Location (single symbol).
- Click Add data. Confirm the map options and click Add data to add the layer to the map.
A new layer appears on the map, showing the locations of the filtered customer list from your spreadsheet. The name of the layer is based on the Excel table name, salonPromo.
Next, you'll change the symbol to differentiate it from the other customer features.
- On the salonPromo layer card, display the layer tools and click Layer settings, then click Layer style.
- In the Symbol style section, click Advanced to display the advanced symbol settings.
- In the advanced symbol pane, confirm that the category is set to Shapes and choose the yellow circle. Set the symbol size to 15 pixels (px) and click OK.
The map updates to show the new symbols.
- Close the Layer style pane.
- On your own, configure pop-ups for the new salonPromo layer (click a feature on the layer to display a pop-up and click the pop-up settings button):
- Set the Name column as the Header.
- Exclude the Name, City, Province, Longitude (X), and Latitude (Y) columns.
- Change the Alias for Address to Home Address.
- Change the Alias for 2013 Median Household Income (constant $) to Median household income.
- In Excel, click Save.
You’ve identified potential customers for the new salon, and you're confident that household incomes for these customers are well within the acceptable range. Now you're ready to show these findings to your boss and get final approval for your promotion. In the next lesson, you'll add a dynamic map slide to PowerPoint to present your results.