Data Exploration Using Publicly Available Data

Derived from an assignment published by: Nitin Kal, University of Southern California Nancy Jones, San Diego State University

OBJECTIVE Self-service data exploration of publicly available data from the U.S. Census.

ACTIVITIES Explore data with charts and tables. Filter, sort, and rank data to gain insights.

SOFTWARE PREREQUISITES Web browser MS Excel 2016 or newer

Scenario You are a new Global Bike intern assigned to the sales department. Global Bike Incorporated (GBI) is multinational manufacturing company based in the US and Germany that sells bikes and accessories to specialized dealers throughout the world. On your first day at work, you are tasked with analyzing U.S. census data to understand the demographics for your home and university metro areas. The demographic data will be used to help the sales team devise a targeted sales and marketing plan.

Analysis Instructions

a. The assignment requires you to identify/compare the latest census.gov demographics data for your university and home (e.g., current residence) metro areas to answer the questions listed below. Everyone should use St. Louis, MO as their university metro area. If your current residence is also houston, Tx, then pick another metro location (e.g., birth) as your home metro area for comparison purposes. If you were not born in a metro area, choose the closest metro area to your home address where you can find comparable data to St. Louis (i.e., +/- 1 million in population). Note: Metro areas are delineated in census.gov as Metropolitan Statistical Areas, as described in this link: https://en.wikipedia.org/wiki/Metropolitan_statistical_area.

b. Access the U.S. Census data at this url: https://www.census.gov

c. Familiarize yourself with the Census website. Start by clicking View QuickFacts on the home page to explore data for some common questions. If you cannot find answers to a question from the "QuickFacts" data, click Visit data.census.gov to use the Advance Search function. You may also use the 2020 Census Demographic Data Map Viewer to answer some of the map related question(s).

d. For some questions, you will need to import a census.gov data table into Excel and do some formatting and simple calculations.

e. Include screen captures of the Census/Excel data visualizations and tables to support your answers. For each question include a short sentence that clearly answers the question, and

for question 9 write a short paragraph based on your insight from a review of the data in the other questions. Question 1: What is the average travel (commute) time to work for individuals in your university and home state metro areas? (It's OK to use county data of the principal cities to approximate the metro areas for this question.) Question 2: What is the median and mean household incomes within your university and home state metro areas during the past year? Question 3: Create an Excel chart that compares the median and mean incomes for your university and home state metro areas. Question 4: Create maps that show the population density of your university and home state metro areas. (It's OK to use county data of the principal cities to approximate the metro areas for this question.) Question 5: Has the population in your university and home state metro areas grown or shrunk over the past 10 years? By how much (% change and raw numbers)? Question 6: What percent of the population within your university and home state metro areas is between the ages of 18 and 64? What is an estimated population for this age group? Question 7: How many commuters (>16 years old) in your university and home state metro areas currently ride bicycles or walk to work? How many drive alone? Question 8: Create a dashboard by combining three or more maps/charts/tables that you accessed on the census website and paste a screen shot here. Comment on how the dashboard could be used by GBI to help develop a targeted sales and marketing plan. Question 9: Comment and provide rationale on which metro area (your university or home) you would recommend for a GBI targeted sales and marketing plan