I have an interesting edge case with importing Amazon Items CSV data. When there is only a single item in the CSV export, the import utility correctly detects 1 new purchase, but the filters see 0 purchases, thus I cannot add the purchase to my Transactions sheet.
The date of the single transaction in the attempted import is 1/2/2021. I’m wondering if there is a bounding issue with the filter logic when there is only a single date, or possible when there is a date range of only one date. I do notice on my successful imports that the # of New Purchase and Filters Purchases always differ by 1, so it may be a counting issue. Does anyone have any thoughts on why a single transaction import might not be picked up?
To update, here is the importer dialog when there are two transactions in the CSV file. Note that the top states 2 new purchases, but the Filters section says 1 purchase.
I could imagine the bug you were talking about with a single line item not falling within a date range filter with the same start & end dates… but I’m not seeing that. I deleted all but one rows from one of my CSVs and the result looks good:
As for your Jan 8 example, I think that can happen if you have two purchases under a single order. That should register as 2 purchases with just one offset. Do you think that is what happened?
I’m happy to dig deeper if these problems persist. Right now I just don’t have enough info to reproduce them.
I’m not super sure what is going on but the most likely issue is that the importer thinks you have already added those refund line-items into your spreadsheet.
The importer rules are here. I don’t really have documentation to share but go to the amazon-refund key for the Amazon Refunds rules object. There is a key value pair that looks like this:
"uniqueId": [ "id", "isbn", "quantity" ]
The importer attempts to use the the combination of Amazon Order ID, product ISBN # and order quantity as a unique identifier for an order line item. If this combination of refund metadata are already in your spreadsheet (i.e. concatenated together), then the importer will not reimport the line item.
You can see Metadata column is populated with these (and other) metadata from the CSV:
I’d start by searching your Transactions sheet for the order number to see what is already in your spreadsheet. From there, we can try to figure out why the workflow isn’t allowing the rows to be imported.
Searching for the order ID only finds 6 entries: 2 for the original order offset, and 2 for each original item purchase. None have metadata specifically pertaining to refunds. I can send or post the purchase transactions as well as the refund export if you like.
I’m a little bit surprised to see that the Metadata key isn’t different for refunds— though they do seem to have “type” as “refund”.
My refund metadata looks like: {"amazon":{"id":"113-9705727-26XXXXX","quantity":1,"isbn":"B078T2XXXX","seller":"XXXX","category-hint":"PRECISION_MEASURING","Order Date":"03/08/2021","Refund Reason":"Missed Fulfillment Promise","type":"refund"}}
If you want to DM me the item & refund CSV (you can strip out all the other rows), I can see if I can reproduce the problem, @cculber2.
I guess it MAY be possible that the uniqueId from the purchase is working as a blocker to the uniqueId for the refund.
The uniqueId for a line-item (purchase) is: "uniqueId": [ "id", "isbn", "quantity", "tracking" ]
I can imagine that a line item without a tracking number could appear as the same uniqueId as a refund. The uniqueId for a refund should probably include the type field.
I ran this down, @cculber2. It appears that the first/last line-items can be clipped during the CSV import process if the browser OS does not match the Tiller Money Labs add-on’s “script timezone” (PST).
In the case where all line items fall on a single day (like your Amazon returns example), a browser in the EST timezone can filter out all results.
It’s a bit of a hack but I just published a change to expand the filter start and end date windows by 6 hours. This should address the issue for US-based users at least.
