For quite a while, I have been looking for data sources that enable me to import Retail Store locations for any retailer. A recent blog post (Thank you Microsoft BI Team) gave me the idea of using data from Yelp for importing Retail Store data for several chains around an area and analyzing data including proximity and demographics of the surrounding area. Retailers could include the other dimension of Retail Store Performance to further enrich the data and gain insights using sales data, demographics and social data. For testing this out, I decided to experiment with data about the Safeway & QFC Stores in the Seattle area.
To analyze the information, I used Excel 2013 with PowerPivot and Power View. I also used Microsoft "Data Explorer" Preview for Excel for data acquisition, filtering and shaping.
For 3D Geospatial Visualization of the Data, I used Project codename “GeoFlow” Preview for Excel 2013.
Here is the final visualization that I built with Power View:
The visualization shows the QFC& Safeway stores in the Seattle area. The bubbles indicate yelp ratings for the stores. In addition, I also correlated demographic data for the store locations with each store. The demographic data was access from Windows Azure Marketplace and is data provided by Esri.
Here is the Geoflow visualization that I put together:
In addition, I also got myself a Yelp account and a developer key for accessing the API.
To Query the list of stores near an area I used the following API Query:
I used Data Explorer to query the Yelp API, convert the JSON response to an Excel table. I used the following query:
I used Data Explorer to import the QFC Store data for the Seattle area using the following query:
The query returns data in JSON format.
I then converted this to a record using the Transform option in Data Explorer. Right Click on the column name and choose Transform and JSON.
Click on the top right corner of the table and select businesses
Once again, click on the top right corner of the column title to expand the records.
Once again, expand the columns using the top right corner of the column title:
Select the columns that you want to import. I selected:
City, Zip, State, Address1, Name, Country, Avg_Rating
After importing the data, I used Data Explorer to Merge the Address1 Field with City, State and Zip (using commas)
Once this was done, I renamed the columns to a more user-friendly list:
I also wanted to ensure that the average ratings were imported as a number field. I used Data Explorer to change the type to Number:
I also filtered the data to show only the QFC data and remove Fresh Fare from the list.
I then rearranged the fields with drag-and-drop and renamed the Query to QFC (right click)
This process enabled me to list out the QFC stores and their Yelp ratings in an Excel Spreadsheet.
You can repeat this process for each retailer that you can information about. I repeated the process for Safeway.
The next step was to append the QFC & Safeway data to create a single table of store listings.
The other layer of insights that I wanted to add to this was demographic information about the people that lived around each store. For this I looked at the Windows Azure Marketplace for a good source of data.
Esri has an excellent data source with US Demographics by ZIP Code for 14 key demographic variables. This includes information like population, unemployment rate, per capita income, median age and so on.
I decided to import the data through Data Explorer into Excel:
Select ZIP Code for Geography type in the Query Editor:
Once the Demographics data is imported into the Excel spreadsheet, I wanted to merge the Store data with the demographic data. To do this, I used the Merge feature in Data Explorer:
I use the Zip Code and GeographyID as the columns to match in the two tables:
This process gave me a table with Store information for QFC and Safeway as well as demographic data for the population in the Zip codes that the stores were located in.
The next step was to create the visualizations using Power View and Geoflow to get insights into the stores, their locations, their yelp ratings and demographic data comparisons around the stores.
I would love to hear of any other data sources that can be combined to this to get further insights.