Skip to content
September 5, 2013 / Erik Svensen

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

Advertisements

One Comment

Leave a Comment
  1. josepht88 / Jun 29 2015 10:50 pm

    If you want to get any other Foursquare JSON feeds into tabular format, you can paste the JSON into https://json-csv.com – then you can open the data up in Excel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: