The Metadata Column
When using Tiller’s Amazon Line Item Importer, you might have wondered what’s this Metadata column.
The documentation says it’s to help uniquely identify purchases. But there’s a whole lot more it can do.
The Metadata column is not meant to be viewed. It contains a lot of useful information that can be extracted if desired. The Amazon Line Item Importer sheet is our first experiment in Tiller Labs using this Metadata column. But we hope to use this concept is many more future integrations.
There are 8 unique pieces of information in the Metadata column in the Amazon Line Item Importer. Rather than create 8 additional columns in the transaction sheet, the Metadata column packs all the data into one column using JSON. This helps keep the number of columns in the Transactions sheet from getting overwhelming.
How To Extract The Data
Great, so there’s all that information in the Metadata field, but how do you get that data out so it’s easy to see?
You use a function that searches for the key word and returns its value. If a value is found, it is displayed in the new column. If nothing is found, the column will be blank.
For example, if you put this formula in the top row of a new column, it will display the Shipping Date for any Amazon purchase:
There are only 2 things you need to change to extract different metadata:
Change Ship Date to the new name you want for the column.
Change shipDate to the new value you want to show. In the Amazon Metadata, the options are "id, account, shipDate, tracking (the shipping carrier and tracking number), isbn and type.
The above formula extracts the text strings. The number strings for the quantity and total data would require a different REGEX. (Does anyone in the community that’s a REGEX expert want to give that a try?)
How The Formula Works
If you are curious how the formula works, let’s review each part.
ARRAYFORMULA function wraps around the whole formula and makes it work for every row in the new column.
IFERROR function checks if the formula finds a match or else makes an error. If there is an error or no match, the last part of the formula
IFERROR(1/0) is used, which displays a NULL cell.
IF(ROW(A:A)=1 part puts the name of the column, in this case “Ship Date”, into ROW 1, or the top row.
REGEXEXTRACT formula looks for the text string “shipDate” (or whatever term you search for) followed by a : and then surrounded by quote. The first part of the formula,
INDIRECT(CHAR(64+MATCH("Metadata",1:1,0))&":"&CHAR(64+MATCH("Metadata",1:1,0))), determines which column is named Metadata. It uses that column as an array in the REGEXEXTRACT search.