Analyze market data for Berlin

Enrich Berlin postcodes

To find an attractive test market for Fahrräder für Familien kiosks, you will look for postcodes in Berlin that have concentrations of family households and households with low-middle to middle levels of income as well as relatively high spending on recreational equipment. This data can be added to polygon layers, like postcodes, using the Data Enrichment tool.

  1. Open the Fahrräder für Familien Lesson Map 2019 and sign in with an ArcGIS Online organizational account.
    Note:

    If you don't have an organizational account, you can sign up for an ArcGIS free trial.

    A color-coded map of the Berlin area shows population by postcode. In the Details pane on the left, you can see information about the map and options for viewing it. The population by postcode layer shows the general distribution of people in Berlin. You will use this to extract and analyze additional demographic data at the postcode level.

  2. On the ribbon, click Analysis. Expand Data Enrichment and click Enrich Layer.

    Enrich data layer tool

    The Enrich Layer menu appears in the Analysis pane.

  3. For Choose layer to enrich with new data, choose Households by Postcode.
  4. Click Select Variables to open the data browser.
  5. In the Data Browser window, make sure Germany is selected as the data source, and then click Income.

    Income variables

  6. Under Keep Browsing, click Households by Income. Select the 2017 Households in 2nd Income Quintile and 2017 Households in 3rd Income Quintile variables.

    Select second and third income quintiles.

    These two categories contain the lower-middle-income households you want to target. Note that the two variables are added to the Selected Variables list.

  7. Return to the main Data Browser window and click Households.
  8. Under Keep Browsing, scroll until you see Households by Type and click it. Choose 2017 Households by Type: Multi-person Households with Children and return to the main Data Browser window.
  9. Click Spending, and under Keep Browsing, click Spending.
  10. Expand 2017 Recreational & Cultural Service Expenditures and choose 2017 Recreational & Cultural Service Expenditures: Total.

    There should be four variables in the Selected Variables list.

  11. Click Apply.

    Enrich Layer variables

    The attributes are added to the Selected Variables list in the Enrich Layer menu.

  12. For Result layer name, type Enriched Berlin Postcodes FML (where the letters FML are the initials of your first, middle, and last names, which results in a layer name unique to you).
  13. Uncheck Use current map extent and click Run Analysis.

    Save the enriched layer.

    When the analysis is completed, the new layer is added to the map with the same symbology as the postcode layer from which it was derived. To optimize its value, you will calculate the target market attribute you require, then configure the layer's pop-up and adjust its symbology.

  14. In the Contents pane, point to Enriched Berlin Postcodes and click Show table.

    Show the attribute table.

  15. In the table, click Options and choose Add field.

    Add Field

    The Add Field window appears.

  16. For Field Name, type LMIHHS. For Display name, type Low-middle Income Households.

    Field Name refers to the actual name of the attribute, a shortened version that the table stores and reads. Display Name is a longer alias that is more descriptive for map users to read and appears in tables and pop-ups and provides a clearer sense of the content of the attribute.

  17. For Type, choose Double and click Add New Field to create a new field and add it to the table.

    Add a field to the table.

    Double is a data type that allows you to store decimal numbers.

  18. Scroll to the end of the table to find the new field.

    Because you just added the field, the Low-middle Income Households column is blank. To add data, you'll use the Calculate Field tool to add the two income attributes together.

  19. Click the Low-middle Income Households title cell and click Calculate.

    Calculate the Low-middle Income Households field.

  20. In the Calculate Field window, click SQL and click the income attributes in the Fields column to create the expression HINC02_CY + HINC03_CY.

    Field calculator expression

  21. Click the green check mark to validate the expression. When the expression is validated, click Calculate.

    The Low-middle Income Households attribute is now populated with data and can be mapped, used in further calculations, and more.

  22. Close the attribute table.

Configure pop-ups

Now that you have all the population data you need on the map, you want to give it more context. In addition to symbology, which you'll change later, you can configure the layer's pop-ups. When you click a postcode, a pop-up will show relevant attributes of your choice. While you may be familiar with the data, this helps anyone else looking at the map understand what they're looking at, and what information on the map you think is important.

  1. In the Contents pane, point to the Enriched Berlin Postcodes layer and click More Options. Choose Configure Pop-up.

    Configure the pop-up for the layer.

  2. For Pop-up Title, type Berlin Postcode Data.

    Now, you can choose which attributes to list on each pop-up.

  3. Under Pop-up Contents, click Configure Attributes.
  4. In the Configure Attributes window, check the box next to the Display header to select all attributes, and then click it again to deselect all.
  5. Select the following attributes and rename their field aliases as listed below:

    AttributeField aliasDecimal places

    {ID}

    Postcode

    {HTYP03_CY}

    Households with Children

    0

    {CS16_CY}

    Total Recreational Service Spending

    2

    LMIHHS

    Low-middle Income Households

    0

  6. Click OK in the Configure Attributes window, and then click OK in the Configure Pop-up pane to apply the changes.
  7. In the Contents pane, uncheck Households by Postcode to turn it off.
  8. With the data table open, click any postcode on the map to open its pop-up.

    Configured pop-up

    When you click a postcode, it is selected and will be highlighted in the attribute table as its pop-up is shown on the map.

    With these operations, you have designed a map layer that contains the relevant segmenting dimensions (household type, income, recreational service spending) for this marketing decision. You have configured the data table and pop-up to clearly display that data. You are ready to use other map tools to explore the distribution of these relevant attributes across the market area.

  9. On the ribbon, click Save and choose Save As.
  10. In the Save Map window, name the map Fahrräder fur Familien FML, substituting your initials.

    Tags and a summary have already been set by the original map, but you can edit them if you want.

  11. Click Save Map.

    This version of the map is saved to your Content gallery.

    Note:

    This map would be useful to other users wanting to explore the market area and can also function as a communication tool in presentations to potential financial backers or other stakeholders. Sharing the map with relevant users, creating a web app based on the map, and designing a presentation from map content are methods of achieving those objectives.

Symbolize target postcodes

With your data preparation complete, you'll start looking for areas in Berlin that have concentrations of households with children, moderate levels of income, and relatively high spending on recreational services. To perform a preliminary visual analysis, you'll change how the symbols on the map portray the data.

  1. In the Contents pane, point to Enriched Berlin Postcodes and click Change Style.

    Change the layer style.

    The Change Style pane appears.

  2. For Choose an attribute to show, choose Low-middle Income Households.

    Four drawing styles that are relevant to your data are listed. Counts and Amounts (Color) will color the polygons lighter or darker based on the number of low-middle income households. However, this can create a misleading map if the values are not first divided by the area of each polygon. Instead, you'll use Counts and Amounts (Size) to create a map of symbols with graduated sizes.

  3. For Counts and Amounts (Size), click Select, and then click Options.

    Change the style options.

    The first step is to understand the data you want to show. Because you're trying to identify areas with many low-middle income households, you want to make these easy to visually identify. To do this, you'll use the mean, or average, value of the data to emphasize postcodes with above-average numbers of low-middle income households.

  4. On the histogram, click the x-bar symbol (the mean) and copy the value.

    Click the x-bar symbol to see the mean value of the data.

  5. Click the value of the bottom slider, 0, and paste the average value (3,893). Press Enter.

    Average symbology

    The bottom slider is now set to the average value of the data, and the symbols on the map readjust. Now, all postcodes with values below the mean are shown in a single class, while values above the mean are symbolized proportionally to their value in relation to the other data values.

    Note:

    This may seem misleading, since it has skewed the distribution of the symbols somewhat, but it is a useful way to get a quick picture of the patterns that you are looking for. You will use this symbology for your own visual analysis, rather than a finished map to be shared.

  6. Click Symbols and click the Fill tab. Choose a dark blue color and click OK.

    Low-middle income households by postcode

    The map displays the distribution of low-middle income households across the market area. Larger circles indicate larger numbers of low-middle income households. West Berlin appears to have the most low-middle income households per postcode. Now, you'll symbolize recreational spending in a similar way to understand the patterns.

  7. Click Cancel. In the Change Style Pane, for Choose an attribute to show, choose Total Recreational Service Spending.
  8. For Counts and Amounts (Size), click Select and click Options.
  9. Move the bottom slider to the mean value of the data and change the symbol color to a dark purple color.

    Total recreational spending by postcode

    Again, the most desirable postcodes seem to be concentrated in western Berlin. This was only a visual analysis, though; in order to make a final selection, you'll use the Find New Locations tool to identify postcodes with above-average values for all three attributes. You also want to factor in proximity to safe biking areas, parks, and existing bike stores that may be competition.

  10. In the Change Style pane, click Cancel and click Done.
  11. Save the map.

Identify suitable test markets

Now that you have an idea of postcodes that may be suitable for test markets, you'll look at other criteria that Max and Renate want you to consider: parks and green space, and competitor locations. To find locations that meet all of the criteria, you'll use the Derive New Locations tool. The Derive New Locations tool takes two types of input queries: attribute and spatial. Attribute queries consider the value of a particular attribute within the data, such as the number of households with children in a postcode. Spatial queries consider location, such as the distance between features or whether they overlap on the map. Using a combination of these two types of queries, you'll write an expression that selects locations that meet all five of Max and Renate's requirements: high numbers of low-middle income households, households with children, and recreation spending, and locations within walking distance of parks and green space, but far enough away from competitor bike shops.

  1. In the Contents pane, point to Enriched Berlin Postcodes and choose Show Table.
  2. In the data table, click the Low-middle Income Households column title and choose Statistics.

    Statistics for the income attribute

    Summary statistics are calculated for this attribute, including minimum, maximum, and mean. These statistics are important to understanding the distribution of your data.

  3. Record the Average (mean) value. You'll use this value later in the filter query.

    Average value of households

  4. Find and record the averages for the Total Recreational Service Spending attribute and Households with Children attribute.

    Low-middle Income Households by Postcode

    3,893

    Total Recreational Service Spending

    9,674,326

    Households with Children

    2,297

    These average values will form the basis for your attribute queries. Now, you'll look at the data you'll use to build your spatial queries.

  5. In the Contents pane, turn off the Enriched Berlin Postcodes layer.
  6. Drag the Berlin Parks and Green Spaces and Berlin Bike Shops layers to the top of the pane and turn them on.

    Spatial query layers

    While you can now see both parks and bike shops on the map, it is not clear how their proximity to each other should be evaluated. You also can't make out a clear distribution pattern. Max and Renate believe that the most likely users of the new kiosks would be families living within 250 meters of a park or green space, and that these consumers would be willing to walk up to 250 meters to get to an existing bike shop to rent equipment. To be on the conservative side, they want you to limit potential Fahrräder für Familien kiosks to areas that are 500 meters from an existing bike shop and 250 meters from the nearest park or green space. Now that you have the data required for all the criteria Max and Renate want to consider, you'll select target locations.

  7. On the ribbon, click Analysis. Expand Find Locations and choose Derive New Locations.

    Derive New Locations tool

  8. For Derive new locations that match the following expression(s), click Add Expression.

    You'll build your attribute queries first.

  9. In the Add Expression window, use the menus to build the expression Enriched Berlin Postcodes where Households With Children is at least 2,297 and click Add.

    Select households with above-average number of children.

    The expression is added to the Derive New Locations pane.

  10. Click Add Expression and add the following expressions:

    • Enriched Berlin Postcodes where Total Recreational Service Spending is at least 9,674,326
    • Enriched Berlin Postcodes where Low-middle Income Households is at least 3,893

    Now that all your attribute expressions are in place, you can add spatial queries.

  11. Click Add Expression and build the following expressions:

    • Enriched Berlin Postcodes within a distance of 250 Meters from BerlinParksGreenSpaces
    • Enriched Berlin Postcodes not within a distance of 500 Meters from Berlin Bike Shops

    The resulting expression has five individual queries, each separated by the AND operator. This means that an area must meet all five criteria to be selected.

    Five selecting expressions

  12. For Result layer name, type Target Locations FML, replacing the letters FML with your first, middle, and last initials.
  13. Uncheck Use current map extent and click Run Analysis.

    Target locations within postcodes

    The result layer shows areas in the city that meet all the demographic and location criteria for an ideal test market. With these locations on the map, you can analyze the ground conditions of each area and decide where to site your bike kiosks.

  14. Save the map.

For more lessons on site selection and business, try Perform a Site Suitability Analysis for a New Wind Farm and Research Market Potential for an Uptown Theater.

You can find more lessons in the Learn ArcGIS Lesson Gallery.