Analysis of 7-Eleven to Chains of Interest

[AmandaGIN]

Initial request was for 10 mile proximity:

1/9 We have a two subsets of BOE stores of interest: n= 36 key stores (they are all 7-11s), let's call these "key 7-11 stores" Stores from specific corporate chains, let's call these "chains of interest" Need to to identify all stores that are from the "chains of interest" list that are within 10 miles of the n=36 "key 7-11 stores". For the 10 mile definition: roadway distance is ideal, but will take Euclidean if necessary to meet deadline.

1/17 update: I spoke with Lisa, and she would like to take the n=88 stores with mapping from 80%-90% as they are now. Using their current lat/long coordinates, and including in our list of mappable stores. For the identification of stores within 10 miles of a corporate 7-11, I have attached an Excel file. This file is the December 22, 2017 BOE list (all stores) with some additional variables. Of interest to you are: "Corp7_11"; coded: 0 = No, 1 = Yes. This variable identifies the n=37 corporate owned 7-11s. These are the stores that we want to use as our reference point, and then identify all chain of interest stores within 10 miles. "ChainOfInterest"; coded 0 = No, 1 = Yes. This variables identifies the n=3801 stores that are owned by a chain of interest. Some of these stores are franchises under the banner of a corporation/chain of interest. Thus you will find 7-11 franchises with "ChainOfInterest" = 1, but "Corp7_11" = 0.

To summarize, we want to identify all stores with "Chain Of Interest" = 1 that are within 10 miles of at least one of the n=37 corporate owned 7-11s (as per the variable "Corp7_11"). Ideally, 10 miles would be roadway, but should using Euclidean distance allow us to have the list by say tomorrow or Wednesday, we would gladly switch to Euclidean.

[AmandaGIN]

10 mile results:
ChainsOfInterest_within10milesOfCorp7_11.xlsx 15 mile results:
ChainsOfInterest_within15miles_Of_7ElevenCorp.xlsx (includes the full BOE list)

[AmandaGIN]

Amanda, Telephone verification of our sample stores derived from the 15 mile buffer indicate we need to cast an even wider net. Would you please replicate our request using a 20 mile buffer? Thanks so much, Nina S

[AmandaGIN]

Documentation of Method used: Use the ArcMap tool: Analysis Tools - Proximity - Generate Near Table

Settings: Input Features - P:\proj_p_s\Stanford_PRC\BOE_2017\data\Corp_7_Eleven\Corporate_7_11_stores_Albers83 Near Features - P:\proj_p_s\Stanford_PRC\BOE_2017\data\Corp_7_Eleven\ChainOfInterest_stores_Albers83 Output Table - [optional location] P:\proj_p_s\Stanford_PRC\BOE_2017\data\Corp_7_Eleven\Buffer_20mile\ChainsOfInterest_within20miles_Of_7ElevenCorp.dbf Sear Radius - 20 miles DO NOT CHECK: Location, Angle, or Find only closest feature Maximum number of closest matches - leave blank Method - PLANAR

[AmandaGIN]

The output is a dbf table with the fields:

OID - new internal Arc identification

IN_FID - FID from the input file (Corporate_7_11_stores_Albers83.shp) test point - the list should include all of the IDs from the input features that have a feature within the distance, and will repeat for each feature within the distance defined

NEAR_FID - FID of the output file (ChainOfInterest_stores_Albers83.shp)

NEAR_DIST - Distance (in the unit of the input data - in this case meters) to from the record input to the output feature

NEAR_RANK - Rank of the input location (Corporate_7_11_stores_Albers83.shp) related stores (ChainOfInterest_stores_Albers83.shp); with 1 being the closest chain, followed by 2, 3, etc. It continues through numbering each store within the defined distance.

[AmandaGIN]

Post processing: 1)Join the License number of the corporate 7-11 store Nearness table: IN_FID join the corporate 7-11 store FID 2) Add a new field Corp711Lic long integer and populate with [Corporate_7_11_stores_Albers83.LicenseNum] 3) Remove join 4) Join the LIcense number of the near Chains of Interest Nearness table: NEAR_FID join the chainofinterest FID 5) Add a new field COI_Lic long integer and populate with [ChainOfInterest_stores_Albers83.LicenseNum] 6) Remove join & test that values look to have properly populated. 7) Create a new field NEAR_MILES (double) for distance in miles (NEAR_DIST * 0.000621371)

[AmandaGIN]

Test results: 1) Run a 20 mile (32,186.9 meter) buffer of Corporate_7_11_stores_Albers83, do not dissolve buffers barriers between features. 2) Spot check that a few of the buffers have the expected number for Chain Of Interest points within.

[AmandaGIN]

Open the dbf to use in Excel to join attributes and finalize results for Stanford. See file for tabs and formatting.

• Use the VBA code to insert lines at change of 7-Eleven corp store in column A https://www.extendoffice.com/documents/excel/4046-excel-add-border-when-value-changes.html

In this case:

• Alt + F11
• Insert - Module
• Paste:

Sub AddBorderLineWhenValueChanges() 'UpdatebyExtendoffice20160918 Application.ScreenUpdating = False Dim LastRow As Long Dim xrg As Range LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For Each xrg In Range("A2:A" & LastRow) If xrg <> xrg.Offset(1, 0) Then Range("A" & xrg.Row & ":S" & xrg.Row).Borders(xlEdgeBottom).LineStyle = xlContinuous End If Next xrg Application.ScreenUpdating = True End Sub column notation of what columns to work with

• Run (F5)
• Save Excel file w/o module
• If results are not as desired, clear manually and redo.

[AmandaGIN]

P:\proj_p_s\Stanford_PRC\BOE_2017\data\Corp_7_Eleven\Buffer_20mile\ChainsOfInterest_within20miles_Of_7ElevenCorp.xlsx ChainsOfInterest_within20miles_Of_7ElevenCorp.xlsx

[AmandaGIN]

Client update: Amanda, We have hit yet another roadblock with our sample of stores near Corporate 7-11s. The field of retailers has changed since our grant was written, and as such we are learning and adapting as per the data you are creating. We now need to identify convenience stores within 20 Euclidean miles of our target n=37 Corporate 7-11s. To accomplish this, a new variable appears in the attached updated BOE file: "ConvenienceOfInterest." The new variable ("ConvenienceOfInterst") is coded 0 = "No", 1 ="Yes", and replaces the previously used "ChainOfInterest." The list of convenience stores flagged by the variable "ConvenienceOfInterest" is not comprehensive, but does capture the chains of convenience stores in which we are interested.

Attached to this email is the updated BOE file that includes the BOE data associated with each record, indicator variable for Corporate 7-11s ("Corp7_11"), and indicator variable for "ConveneinceOfInterest." As per our previous requests, having the distance in Euclidean miles between each Corporate 7-11 and the "nearby" (within 20 miles Euclidean) convenience store of interest, and county in which convenience store is located are needed, and the BOE information for that store is very helpful.

We know there has been a lot of repetition with this task, it has been a learning experience how much the retail landscape has changed in a relatively short period of time.

Thanks, Nina S

See issue #4 for Convenience products