Analyzing foursquare data using Excel Power Query and Power View

I have been on foursquare for a couple of years now, and have also created venue for my workplace where I am the administrator. This is required for making the report in this blog post but there are many API end points where you can access your own data to create reports on.

You can read a lot about the different possibilities her – https://developer.foursquare.com/docs/ .

I wanted to analyze the stats on my Venue – Knowledge Cube A/S – and used this endpoint to retrieve the data from Foursquare – link

You need to find the Venue_ID to retrieve the stats and you can do that by Search API – or by checking in on the venue and use https://developer.foursquare.com/docs/explore#req=users/self/checkins to find the venue id in the response.

So let’s get the data using Power Query – Switch to Excel and on the Power Query tab choose the From Web in the Get External Data group.

This will open up the Power Query window and you can construct/design the query.

The Foursquare api will be return as a JSON document and we can browse through the response using the navigator.

But first start by naming your query to something meaningful instead of Query1 – do that by double clicking the name and type the new one.

When we navigate through the response we can see the different elements we get returned for Foursquare.

And could choose to see the checkins by agebreakdown by simply clicking on “list” it will return a list of records and in order to extract those you convert it into a table.

Choose None as delimiter and choose ok

Expand column1

And you get a table with the Age groups.

In my example I want the top visitors so I use the navigator to go back in the reponse and Power Query will want me about this – but its ok in this case.

So to get the topvisitors I choose that from the stats and convert it into a Table.

Expand the Column1 to get the users

And then expand the user to get the meta data about the users.

In this case I didnt pick all but just a few informations that I wanted to visualize – Name, Photo, home city and number of check ins.

When I click ok in Power Query the data is returned to Excel as a table containing the top 10 visitors of my venue.

I then add this Power Query to the data model by click “Load to data model”

As I would like to have a picture of the person I construct a url to the profile picture – BE AWARE that you need to add “Original” between the prefix and suffix.

Be sure to check that the datacategory for the calculated column is set for Image URL

For the HomeCity column you should specify City as Data category.

Finally add a measure to SUM the Checkins.

Switch to Excel and insert a Power View Report

And after a few clicks you could have this reports showing the Top Foursquare visitors of your Venue.

PS – Haven’t spent time on renaming the column names but this should offcourse be done