Member-only story
Making Tables from JSON data
My biggest challenge with Google Analytics data is that it has attributes containing JSON dictionaries. While this kind of data structure is not uncommon, it is inconvenient for analysts working with data stored as tables.
It is possible to use SQL to unpack JSON data into new tables. But part of applying best practices, and understanding data governance processes is realizing that this should be done in the ETL.
Storing JSON data in SQL tables limits its utility to situations where people have the time, skills and desire to write queries that will pull all that apart. It also inflates the size of those tables dramatically, which can limit performance of queries and BI tools.
Creating a new table from each JSON dictionary, and using keys to connect them back to what is left of the original table, distributes the size and processing load associated with the resulting tables. And it makes the data much easier to read and understand.
In this situation, I’m using Python to unpack the JSON dictionaries in some real Google Analytics data. This will allow me to see what is happening in the records associated with the JSON objects when I look at the table. It also makes querying that data using SQL or Pandas much easier.
Once the JSON has been flattened, I can create an Excel spreadsheet containing…
