Amazon CSV Import Duplicates - Metadata Issue Found

Google Sheets
1

Is anyone else seeing an issue with duplicates when importing amazon csv purchases? The Metadata column is supposed to prevent duplicates… what I am seeing is a variation in the Metadata with these duplicates which is causing the issue. I am getting to this by selecting year to date with the orders/refunds report so nothing is ever missed.

@randy
In the examples below, notice the original imported line item does not include shipping date, the tracking or the category-hint… should the Tiller script filter out those that do not have these missing data points to fix this issue? OR at least give the user the option in the import tool to filter these out?

Here is an example of the differences in Metadata.

Original imported line item:
{“amazon”:{“id”:“114-**",“quantity”:1,“item-price”:14.99,“account”:"@gmail.com”,“total”:15.92,“shipDate”:"",“isbn”:“B09D8T11YS”,“unspsc”:“52140000”,“seller”:“Zulay Deals”,“category-hint”:“MILK_FROTHER”,“payment-type”:“American Express - *”,“type”:“purchase”}}

Duplicate imported line item:
{“amazon”:{“id”:“114-**",“quantity”:1,“item-price”:14.99,“account”:"@gmail.com”,“total”:15.92,“shipDate”:“01/23/2022”,“tracking”:“AMZN_US(TBA166955229204)”,“isbn”:“B09D8T11YS”,“unspsc”:“52140000”,“seller”:“Zulay Deals”,“category-hint”:“SMALL_HOME_APPLIANCES”,“payment-type”:“American Express - *”,“type”:“purchase”}}

2

@randy bump… any input on this finding? How are most doing these imports? Are they selecting the day after the last import through day or is my finding this issue not working correctly?

Thanks!

3

Hey, @al1! Thanks for the reminder. I’ve been taking a little time off in Europe. Just returned. Will look into this in the next few days. Thanks for your patience.

4

Finally had a chance to peek at this, @al1. Thanks for your patience and for providing some sample data.

When I unwrap, your “original imported line” item looks like this:

{
"amazon": {
"id": "114-**",
"quantity": 1,
"item-price": 14.99,
"account": "@gmail.com",
"total": 15.92,
"shipDate": "",
"isbn": "B09D8T11YS",
"unspsc": "52140000",
"seller": "Zulay Deals",
"category-hint": "MILK_FROTHER",
"payment-type": "American Express - *",
"type": "purchase"
}
}

And your “duplicate imported line” item looks like this:

{
"amazon": {
"id": "114-**",
"quantity": 1,
"item-price": 14.99,
"account": "@gmail.com",
"total": 15.92,
"shipDate": "01/23/2022",
"tracking": "AMZN_US(TBA166955229204)",
"isbn": "B09D8T11YS",
"unspsc": "52140000",
"seller": "Zulay Deals",
"category-hint": "SMALL_HOME_APPLIANCES",
"payment-type": "American Express - *",
"type": "purchase"
}
}

To avoid duplicates, the scripts create a concatenated string using the following fields:

  • id
  • isbn
  • quantity
  • tracking

When I scan the data, the missing piece is that the tracking key is not populated in the “original” metadata. I’m guessing this is why the script isn’t identifying it as a duplicate. Is there any chance you have the original CSV to see if that data was present?

Or, is it possible that you pulled in the transaction before it was shipped? And the tracking information wasn’t yet available? (I run the script every quarter or so… so this isn’t an issue for me.)