#tabtQS 2: Google location history in Tableau

In case you missed post #1 check it out here, however this post is about Google location history. On the 19th June I gave a short talk on the Quantified Self and the iterative process by which I explore the data I collect and improve the way in which I handle it. In the talk I showed in 5 minutes how you could get hold of the location data that Google has in a JSON format and  then very quickly visualise it in Tableau. This blog post will summarise the steps you need to take and what you need to do in Tableau. Since version 10.1 of Tableau, the addition of son support has meant that there is a world of data now readily available to analyse in minutes using tableau.

Step 1: Request your data

Download your data from Google. You can visit that link and request your Google data. In this post I’m only focussing on location history but you can grab more if you like. Be warned downloading everything can generate a monster sized set of files as it will include copies of files from google drive, photos and videos form google photos and your tube along with reams of metadata that can easily go into the millions of rows.

Step 2: Get your data

Once you have your download from Google and in it, your location history, inspect the size. Tableau has a limitation with JSON files where it can’t open extremely large JSON files, hoping this gets ironed out as if you get this error, you’ll need to convert you JSON into maybe a CSV or parse it with another tool. Here’s a link mot my Alteryx workflow that parses the location history (it takes a while to run).  In my case, the file is 80mb so we can proceed. Place the JSON file in a suitable location and fire up Tableau.

 

Step 3: Connect to JSON

No its over to Tableau. Fire up tableau and select JSON as the data source and locate the JSON file. Once selected, Tableau will show you the schema within the JSON file. Select the first two sections of the heirachy. Later on Tableau recognise new elements within the schema and offer you the choice to bring those in. Look out for the exclamation mark next to the data source and follow the instructions to bring in the new fields.

 

 

Step 4: Lets Viz

Once connected, we can see we have mapping data, but before we can use the latitude and longitude, we need to adapt them as they are stored as whole numbers. To fix this we divide them by 10,000,000 as in the calculation below. Once done, a few clicks and we have a map.

Step 5: Design tweaks

Once you’re done, tweak the map. I like to use a dark map and make the points white, the contrast tells a story and white space in this design brings your location history to life. Its also great if you want to play around with transparency to try and figure out where you’re spending the most time.

 

6 Comments

  • acotgreave
    Hi Tim - this looks great. I tried but got an error in Tableau saying "Tableau encountered a single object larger than 128MB in in the JSON file" Any ideas? I don't seem to be able to control the size of the JSON download via Google's download service.
    • The 128mb is a limit within Tableau and JSON file support at the moment. I've suggested to those who have it to use Alteryx to parse it out, I have a workflow that does that here (https://drive.google.com/file/d/0B5GCcmjpXCNEOEo4TTVvMGcxSFE/view?usp=sharing ) otherwise a JSON to CSV converter also works and you can the drop the CSV into Tableau and carry on as its easier to do that than change the structure of the JSON file which is the supported suggestion (https://onlinehelp.tableau.com/current/pro/desktop/en-us/examples_json.html#Tips ).
      • Stefan Pawlik
        Hi Tim, thanks for the tutorial, the result looks amazing. I am currently trying to replicate the steps, however, since my JSON file is almost 300mb big I'm having troubles importing it into Tableau. All online to CSV converters fail at that size and I'm not having any luck with the CLI-tools. Would it be possible to share the Alteryx workflow again, your link is currently broken (I'm working with the 14-day trial)? Thanks in advance. Any other tips are also welcome! Best, Stef
  • PGB
    Thanks for this Tim! This worked great for me (just a shame it's only my 6 months of Android use). Thought I'd share my approach to parsing the datestamp field in case it is of use to anyone else: DATEADD("second", INT(INT([timestampMs (locations)])/1000), MAKEDATETIME(MAKEDATE(1970,01,01),MAKETIME(00, 00, 00)) )
    • I should have included this logic! Thanks for sharing.
      • Rosie Haworth
        Hi - I just went to a workshop on Tableau where we were shown this google maps exercise! Would you mind explaining how to fix the time/date please, I'm new to tableau so don't really understand :)

Leave a comment