In this exercise, you are a business analyst for a consortium of colleges that wants to run a marketing campaign in states with high-value colleges. It's up to you to find states with colleges that have a good return on investment (ROI) for students. You'll use Insights for ArcGIS to analyze United States Department of Education College Scorecard data in the form of a feature layer to find relationships between the cost of college and earnings by graduates. In 45 minutes or less, you will do the following:
- Create interactive maps, charts, and tables.
- Apply an advanced filter to your data.
- Use spatial and nonspatial analysis techniques to solve a problem.
To complete this exercise, your account must have the following specifications:
- Creator or GIS Professional user type
- Publisher or Administrator role, or equivalent custom role
- Insights license
For more information, see Administer Insights.
Add data to your organization
The data for this analysis has been provided publicly on the ArcGIS website, where it can be downloaded to your machine. Follow these steps to access and load the data to your ArcGIS Online organization:
If you completed the Create your first workbook lesson, you can skip to the next section. Reopen your workbook, and drag the CollegeScorecard.Table1 dataset to the New page tab then create a map on the new page.
- Follow the link to the CollegeScorecard item.
- Click the Download button to download the item to your machine.
- Unzip the folder and save the Microsoft Excel file on your computer in a location that you can find easily.
- Sign in to your ArcGIS Online account.
- Open Insights and sign in to your account if necessary.
You can access Insights through the gallery of apps in ArcGIS Online.
- On the Workbooks page, click New workbook.
- In the Add To Page window, click Excel or CSV.
- Click Browse my computer and open the Excel file or drag the file onto the Add To Page window. Click Add.
- Click Dataset options for the Excel file and choose Enable Location from the menu.
- Click Run.
- Expand the dataset in the data pane.
- Drag the dataset onto your page and drop it on the Map drop zone to create a map of United States colleges.
- Click Untitled Workbook and replace it with a unique and useful title, such as US Colleges - Your Name. Including your name in the title will make your workbook easier to find if you share your work. Click Save.
If you prefer to use buttons, you can select a field from the dataset and click the Map button above the data pane.
Calculate return on investment
The return on investment (ROI) for colleges is calculated using cost and earnings after graduation. In this section, you will begin your analysis by calculating ROI for all United States colleges.
- Click the Action button on the map to open the Analytics pane.
- Click on the Find Answers tab and How is it related? to display spatial and nonspatial analysis capabilities.
- Open Calculate Ratio. For the numerator, choose EARNINGS, and for the denominator, choose COST. Name the field ROI and click Run.
- Close the data table.
- Save your workbook.
Find states with above average ROI
Now that your dataset has a field for return on investment, you can start to find more information about which areas have a high ROI. In this section you will filter your data and determine which states have the highest ROI.
- On the map card, click the Card filter button . The New Filter pane opens.
- Click Advanced to open the Expression Filter pane.
- Enter the expression ROI>AVG(ROI) to query only the colleges with a greater than average return on investment. Click Apply.
- Close the Card Filters pane.
- Hover over the result dataset and click the Rename dataset button .
- Rename the dataset Colleges_ROI and hit Enter on the keyboard to set the changes.
- Expand Colleges_ROI to display the fields. Select STATE and TYPE, drag them to the Chart drop zone, and drop them on Stacked Bar Chart.
- Drag the bottom edge of the chart card down to make it easier to see all of the states.
- Click the Sort button on the card toolbar and choose Sort Descending .
- Click More and Edit Labels , then click the axis to rename it. Name the x-axis (horizontal) Count of colleges with above average ROI and rename the y-axis (vertical) State and college type.
Using a card filter instead of a dataset filter will allow you to work with both the filtered and unfiltered data throughout your analysis.
Analyze the ROI spatially
- Click Add to open the Add To Page pane.
- Click Boundaries to display the available boundary layers.
- Select the USA States (Generalized) dataset. Use the Search bar if necessary to reduce the number of datasets being displayed. Click Add.
- On the map of US states, click More then Delete .
- Drag the state dataset from the data pane onto the map of Colleges_ROI and drop it on Spatial aggregation.
- Click Run.
- Drag the original College_Scorecard.Table1 dataset to the map and drop it in the Spatial aggregation drop zone. Click Run to calculate the count of colleges within each state.
- Expand the Spatial Aggregation 2 dataset. There are two count fields: Count of Colleges_ROI, which includes the count of colleges with above average ROI in each state, and Count of CollegeScorecard.Table1, which includes the total count of colleges in each state.
- Click Rename dataset next to Spatial Aggregation 2 and name the dataset College counts.
- Open the Dataset options menu and click View Data Table.
- Click the + Field button to add a new field to the data table.
- Click New Field and update the field name to PercAboveAvgROI (Percent above average ROI).
- Click the Enter calculate function box and enter the equation (Count of Colleges_ROI/Count of CollegeScorecard.Table1)*100. Click Run and close the data table.
- Click the number field icon next to the PercAboveAvgROI field and choose Rate/Ratio to change the field role.
- Drag PercAboveAvgROI to the map to update the style.
- Resize the map so that it fits onto your page and all of the states are visible.
- Save your workbook.
The count is calculated by default.
A second Spatial Aggregation dataset is added to the data pane and the map updates to show the new Count of CollegeScorecard.Table1 field.
Change the classification on your map
Now that you have your map, you can start making conclusions about return on investment in different states. However, it's important to remember that your conclusions are going to be affected by the classification on the map. Therefore, you should check what classification is being used before you finish your analysis.
- Open the Layer options pane by expanding the layer name on the map.
- Click the Options tab .
- Click Natural Breaks to expand the Classification type menu.
- In the Classification type menu, click Equal Interval.
- Change the number of classes to 8.
- Click the first slider and change the value from 16 to 10. Hit Enter on your keyboard.
- Change the other dividers to 20, 30, 40, 50, 60, and 70.
- Click on the legend tab to see the values. Click on values in the legend to select the features in each class.
- Save your workbook.
There are 6 classification options available: Natural Breaks, Equal Interval, Quantile, Standard Deviation, Unclassed, and Manual. An unclassed classification gives the map a continuous color ramp, rather than discrete classes. In this case it is probably best to have discrete classes so that the states can be analyzed in groups. An equal interval classification is useful for data with a known range, like percentages, because it allows you to group your data into set ranges (for example, a dataset with percentages could be divided into 5 intervals with ranges of 20 percent). A quantile classification divides the data into groups with an equal number of features, which makes it good for data that you want to display by rank. In this case, a ranked classification could be useful. A standard deviation classification is useful when you want to focus on the distance from the mean. While knowing the average could be useful in this scenario, it doesn't need to be the focus of your analysis. Finally, a manual classification can be used to create a custom classification scheme. Manually changing the classification can be useful for data with specific values that need to be taken into account or for standardizing the classification between multiple maps.
On the surface, quantile and equal interval seem like the best options. However, this dataset has 51 features (50 states plus the District of Columbia), which makes it difficult to divide the data equally into a quantile classification. It may be best to try equal interval.
Change the Display field for the Location field to STATE so that the state names will be displayed when you hover over them.
Now that you have finished your analysis, it is time to share your results with your colleagues. Use the quick exercise Share your analysis to continue the college scorecard scenario and share the results as a model and an interactive page.
Try more scenario-based exercises from Learn ArcGIS and the Applied Analysis team:
- Get Started with Insights for ArcGIS - Explore where construction permits are being used to find trends in growth around Montgomery County, Maryland.
- Investigate Prescribed Drugs - Fight drug abuse by identifying suspicious prescription trends.
- Finding a new home using Insights for ArcGIS - Follow along as Mark and Lisa use Insights to investigate whether or not to sell their home.