You have GOAT customer and prospect data and you are receiving your data via API. But your data is in these weird files called JSON. How exactly do you wrangle your GOAT data in the fastest and easiest way possible so that you can get what you need from it? Would it be better if we told you it was also low to no cost? Yes, the light at the end of the tunnel is here. JSON data is easy to use and download, if you know how…
Regardless of business size, you likely have access to Excel. If you don’t, sorry, this article may not be for you. If you do, you are in luck! There is no need to purchase huge data storage, download tools that are not approved by your company, nor bang your head against the wall (Seriously, that just hurts!). There is an easy way to figure out how to get inside these JSON data files so that you can organize your GOAT data and get a sense of what your ROI really is. Google, Microsoft, Amazon and many others offer data storage space today that is cheaper than ever before, and sometimes even free. And, if you are a small business or a freelancer, your laptop storage will likely due nicely. So, how do we get to JSON and make it work?!?!?
And, the answer is… Excel Power Query
This tool within your friendly Excel neighbor has got to be one of the most powerful, and least marketed, tool out there. So, use it safely and widely. It will help you with not only JSON data files, but with any standard TXT, CSV, and other file types out there.
Here are the instructions of how to create a simple task in Excel Power Query in order to automatically upload multiple JSON data files at once into your Excel workbook. The screenshots below are from Excel Office 365 and should work similarly to older versions. But you will get the gist.
But first, some definitions…
JSON, API, Power Query – Oh My!
Application Program Interface is a way in which your systems can talk to other systems in a safe and productive manner. APIs allow anyone outside (with permission, of course. No mean bots!) a specific system to interface with it in order to augment programming efficiencies or download data. FUN FACT: Most APIs output data in JSON format.
By far, one of the coolest Excel tools invented since Excel came into the market in the 1990s. No need to understand macros or VBA (Visual Basic for Applications programming language). All you need to do is to click on the right places on your screen. BONUS – We will show you how!
15 steps to Power Quering your JSON data:
Please note: column headings and data are shaded out due client privacy and compliance.
1. Click on the Data tab of your Excel screen (2016, 365, or later). Then click on Get Data / From File / From Folder:
2. Select the folder where the data exists. You can also pull data directly from different databases, online services, or other sources. Then click on “OK”:
3. Power Query will pull all the files as per below. Click on “Edit”:
4. Right click on the header of the column with the binary information (listed here in green). Select “Transform”, then “JSON”:
5. Each row from step 4 has been transformed from a multilevel JSON data into a flat file. Each file is called a “Record”:
6. To see the information in each Record, or file, click on the double arrow on the “Content” column header. This will allow you to see the data in each file:
7. The JSON data now expands into each column below. Any column with double arrows can be expanded further:
8. Each double arrow column can be expanded in 2 ways:
- Expand to New Rows – this will add rows for every value within the record for this specific column. The information on all other columns will repeat across the newly inserted rows
- Extract Values – this option will put all values within the same cell with a delimiter (see step 13)
9. By using step 8.a, we now have multiple rows created for the same file:
10. Expand the rows further by clicking on the double arrow as needed:
11. The Content.priv4 column has been expanded into several columns
12. Expand data values within a column by adding delimiters. Here, we select the “Extract Values” option and use a delimiter to include all values within the cell:
13. A double pipe ( || ) is used to separate the values:
14. All values within Content.priv4.D6 are now double pipe delimited, and remain within the same column without adding new rows:
15. Click on “Close & Load”. This will export the data into your Excel spreadsheet:
DONE! It’s simple if you know where to click.
Need Help Wrangling your Marketing Data – We can help!
If you have a GOAT or just about anything to sell but don’t have the time to organize your Marketing Data, let Key Paragon help you. We are a Marketing Solutions and Advisory firm. We partner with our clients to address their most important marketing challenges, whether it be branding, strategy, process automation, social media, analytics, or marketing technology. Happy GOAT marketing!
Want to know more about How to Market Your Goat? Take a look at our other blogs here.
Erika is a data and targeting expert. She has developed many segment analysis processes and target populations in order to support business initiatives. Her passion in chaos theory and fractal analysis helps her view data in a very unique way.
Erika enjoys also reading about the future of work and how we can bring it closer to the present. “How we work today will be so different in 10/20 years from now. It’s exciting to see what will happen given all the economic, human, and technical evolution we are currently experiencing.”
…and Han shot first…