How To Pull Amazon Metadata Into A Standalone Column

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:

=ARRAYFORMULA(IFERROR(IF(ROW(A:A)=1,"Ship Date",REGEXEXTRACT(INDIRECT(CHAR(64+MATCH("Metadata",1:1,0))&":"&CHAR(64+MATCH("Metadata",1:1,0))),"shipDate"":""([^""]+)")),IFERROR(1/0)))

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(IFERROR(IF(ROW(A:A)=1,"Ship Date",REGEXEXTRACT(INDIRECT(CHAR(64+MATCH("Metadata",1:1,0))&":"&CHAR(64+MATCH("Metadata",1:1,0))),"shipDate"":""([^""]+)")),IFERROR(1/0)))

The ARRAYFORMULA function wraps around the whole formula and makes it work for every row in the new column.

The first 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.

The IF(ROW(A:A)=1 part puts the name of the column, in this case “Ship Date”, into ROW 1, or the top row.

The 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.

1 Like

This will be really helpful for some workflows I have seen the community ask about— like filtering on the ordering account.

Thanks for posting this, @jonorlin!
Randy