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.

clip_image002

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.

image

 

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:

clip_image006

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:

clip_image008

In addition, I also got myself a Yelp account and a developer key for accessing the API.

clip_image010

To Query the list of stores near an area I used the following API Query:

http://api.yelp.com/business_review_search?term=[RetailerName]&location=[City]&ywsid=[YourAPIKey]

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:

http://api.yelp.com/business_review_search?term=QFC&location=Seattle&ywsid=YourAPIKey

clip_image012

The query returns data in JSON format.

clip_image014

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.

clip_image016

Click on the top right corner of the table and select businesses

clip_image018

Once again, click on the top right corner of the column title to expand the records.

 

clip_image020

Once again, expand the columns using the top right corner of the column title:

clip_image022 

Select the columns that you want to import. I selected:

City, Zip, State, Address1, Name, Country, Avg_Rating

clip_image024

After importing the data, I used Data Explorer to Merge the Address1 Field with City, State and Zip (using commas)

clip_image026

Once this was done, I renamed the columns to a more user-friendly list:

clip_image028

clip_image030

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:

clip_image032

I also filtered the data to show only the QFC data and remove Fresh Fare from the list.

clip_image034

 

I then rearranged the fields with drag-and-drop and renamed the Query to QFC (right click)

clip_image036

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.

clip_image038

clip_image040

clip_image042

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.

clip_image044

I decided to import the data through Data Explorer into Excel:

clip_image046

clip_image048

Select ZIP Code for Geography type in the Query Editor:

clip_image050

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:

clip_image052

I use the Zip Code and GeographyID as the columns to match in the two tables:

clip_image054

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.

clip_image056

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.