Why Are Unique IDs Important When Importing Transaction Data?
If you use a CRM or Accounting system, each record already has a unique ID. This article only applies to customers whose data source does NOT already provide a unique ID.
Let's say you upload a CSV file with records like this:
When you upload this CSV file, we create one sales transaction per record.
Let's say you want to change this amount from $5.44 to $5.90 because it was incorrect:
If you edit your CSV file and upload it again, you will end up with duplicates!
One sales transaction will have an amount of $5.44
Another sales transaction will have an amount of $5.90
Why? Because you did not provide a unique ID in your data. Therefore, we don't know which sales transaction to update. Your row with an updated amount of $5.90 looks like new data. There is no way for us to know which one of your previously uploaded rows needs to be updated.
This is why you should provide a unique ID for each record. Most CRM and accounting systems provide a built-in unique ID for each record (ex: opportunity ID, deal ID, etc.). Using a unique ID, updates and corrections become easy.
If you have a unique ID for each record, you can upload the same spreadsheet with corrections, and we'll know whether we need to a/ update a record vs. b/ create a new record.
If you provide a unique ID in your data, make sure that:
- It is truly unique
- Two different records cannot have the same unique ID
- It is stable
- Your unique ID does not change when properties change (ex: amount, product, etc.)
If you cannot provide a unique ID, we can generate one for you using a combination of fields. We will concatenate your chosen fields, and generate a unique checksum, which will serve as a unique ID. Make sure that your chosen fields, when combined, respect the 2 criteria above.
Using the example above, using {Product + Customer + Quantity} as a combination is NOT a good choice. Indeed, we can see that a customer can have 2 different transactions for the same product and quantity (ex: purchase then refund). Think carefully about the 2 criteria above!