Visualizing Foursquare Check-In Data using Power BI


proximity1The Foursquare API can be used for displaying interesting information for retailers that can include location data of their properties, proximity to competition as well proximity to public transportation, access to parking and a lot more. I have been using the Yelp API in the past for getting information but switched to the Foursquare API after the Yelp 1.0 API was deprecated. This blog post is in response to several requests that I received to share out the steps for visualizing the data from the Foursquare API using Power BI. So here is how I use it:

Setting up for using the Foursquare API

If you do not have a Foursquare account, you would need to create one via: http://foursquare.com. Before you start using the API, you should create an app on Foursquare. This will give you a client ID and client secret which are needed for using the API.

To create an App go to: https://foursquare.com/developers/apps and fill in the details. Once you do this you will have a Client ID and Client Secret that you can use in your Power BI Queries.

Using the Foursquare API

A quick way to get started is to Search for Places in an Area using the Search API. See the documentation for the API here: https://developer.foursquare.com/start/searchYou’ll need your client ID and client secret to make a userless venue search and is the simplest way to interact with the Foursquare API—there’s no need to use OAuth. This is the sample http request:

https://api.foursquare.com/v2/venues/search
  ?client_id=CLIENT_ID
  &client_secret=CLIENT_SECRET
  &v=20130815
  &ll=40.7,-74
  &query=sushi

Replace the CLIENT_ID and CLIENT_SECRET with your client ID and client secret. You can replace the location around which you want to search by using a different latitude and longitude coordinate. To search for locations around Seattle, I would need to find the co-ordinates for Seattle. An easy way to do this, is to type in lat long Seattle, WA in Bing Search.

If you haven't downloaded Power BI Desktop, you can download it from https://powerbi.microsoft.com/en-us/desktop. Launch Power BI Desktop and use the Get Data option. Past the http request using the Web option under Get Data.

image

Select Response Record.

image

 

Select Venues List

image

Click on Convert to Table

image

Expand the Columns and unselect Use Original Column Name as Prefix

image

Expand the Location Field and select the sub fields that you want in your analysis. I typically pull in Lat, Lng, postalcode, address, city, state and country.

image

Expand Stats and select CheckInsCount and usersCount:

image

Delete any fields that you wont be using in your visualization. Click Close and Appy and you now have the data in a table:

image

Here is the code that was generated. You can copy and paste this into your Advanced Editor and use it to reduce the time taken to pull data from Foursquare:

let
    Source = Json.Document(Web.Contents("
https://api.foursquare.com/v2/venues/search?client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET&v=20130815&ll=40.7,-74&query=sushi")),
    response = Source[response],
    venues = response[venues],
    #"Converted to Table" = Table.FromList(venues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "contact", "location", "categories", "verified", "stats", "url", "hasMenu", "delivery", "menu", "allowMenuUrlEdit", "specials", "hereNow", "referralId", "venueChains", "reservations", "venuePage", "storeId"}, {"id", "name", "contact", "location", "categories", "verified", "stats", "url", "hasMenu", "delivery", "menu", "allowMenuUrlEdit", "specials", "hereNow", "referralId", "venueChains", "reservations", "venuePage", "storeId"}),
    #"Expanded location" = Table.ExpandRecordColumn(#"Expanded Column1", "location", {"address", "lat", "lng", "postalCode", "city", "state", "country", "formattedAddress"}, {"address", "lat", "lng", "postalCode", "city", "state", "country", "formattedAddress"}),
    #"Expanded formattedAddress" = Table.ExpandListColumn(#"Expanded location", "formattedAddress"),
    #"Added Custom" = Table.AddColumn(#"Expanded formattedAddress", "FullAddress", each [address]&", "&[city]&", "&[state]&", "&[country]),
    #"Expanded categories" = Table.ExpandListColumn(#"Added Custom", "categories"),
    #"Expanded categories1" = Table.ExpandRecordColumn(#"Expanded categories", "categories", {"shortName"}, {"shortName"}),
    #"Expanded stats" = Table.ExpandRecordColumn(#"Expanded categories1", "stats", {"checkinsCount", "usersCount"}, {"checkinsCount", "usersCount"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded stats",{"contact", "formattedAddress", "verified", "hasMenu", "delivery", "menu", "allowMenuUrlEdit", "specials", "hereNow", "referralId", "venueChains", "reservations", "venuePage", "storeId"})
in
    #"Removed Columns"

I also pull in Demographics data by Zip Code and correlate it to the data from Foursquare. This gives me additional information for each location that includes Population, Median Income, Average age and so on. Here are some examples:

PB3

PB4

 

 

 


Comments (0)

Skip to main content