Why Are Unique IDs Important When Importing Transaction Data?

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:
  1. It is truly unique
    1. Two different records cannot have the same unique ID
  2. It is stable
    1. 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!

You can learn more about unique IDs here.

    • Related Articles

    • Why Are Transaction Dates Important When Importing Transaction Data?

      Let's say you upload a CSV file with records like this: As you can see, there is no date column. So, if you wanted to calculate January commissions, we would not know if those sales would apply since there is no effective date column we can use. In ...
    • How Can I Duplicate A Transaction?

      In most cases, you should update your source data (ex: CRM system, accounting system, CSV file, etc.) to include missing records. However, in some rare cases, you may need to add another transaction within Sales Cookie only. To do this: Select ...
    • How Can I Adjust An Individual Transaction's Commission

      This article only applies to per-transaction rewards. Many commissions are paid in aggregate (ex: $1000 payout for reaching a milestone), and so there isn't a per-transaction commission amount. The same applies is you use the portion method to ...
    • How Can I Credit Multiple Users For One Transaction?

      You may want to issue commissions to multiple users over the same sales transaction. This may be necessary when multiple users contribute to a sale, or because you have a multi-level commission structure (ex: override commissions). There are two ways ...
    • I Have Accidentally Deleted Data - Can It Be Recovered?

      We provide a warning confirmation for all deletion actions to make sure it was intended. It is important that all confirmation messages be reviewed very carefully to avoid problems. If you deleted the data recently (48 hours ago or less), we might be ...