UPDATE/UPSERT - Continuous Duplicate Feed for Account Records

  • 57Views
  • Last Post 26 December 2017
0
votes
Danart Ros posted this 19 December 2017

Hello,

I have a question about UPDATE/UPSERT.   I have a new import task set up to Update/Upsert but every couple of scheduled feeds that occur, I get a feed of over 3,000 records. Most of the Records are coming from my ACCOUNT task.   When I examine each of the records, it shows that all the records are coming from Accounts and have already been subscribed. Could it be that my set up incorrect and this keeps happening? Should’t it not be sent over if the email has already been subscribed or feeding over duplicates?   As you can see with the image attached, every 4th schedule I have is more than 3,000 but the other schedules have expected average amount of records being feed over.  

Order By: Standard | Newest | Votes
0
votes
Simon Bubnov posted this 22 December 2017

We have studied your package. You have 3 tasks with the state filter "Updated" and the upsert operation. "Updated" state filter means that package will process only those records which were changed in Salesforce since the previous package run. And if 3000 records were updated since the previous package run, the package will process 3000 records.

When performing the upsert operation, the target primary key must be mapped. The UPSERT is performed in the following way: if a non-null value is provided for your target Id column, the update operation is performed. When a NULL value is provided for the target Id column, the record will be inserted. For more information, please refer to

https://skyvia.com/resources/docs/index.html?performing_upsert_operation.htm

Thus, even if a record is present in ListMembers, it will still be processed, and update operation is performed for it. If there is no such subscriber in ListMembers, the insert operation is performed.

We have also found that in the first two tasks (importing data from Contact and Lead) you use a correct lookup mapping for the id field. But the third task, for the Account object, uses just a column mapping for the Id field. This is not correct. Id values in Salesforce Account are always different from Id values in MailChimp ListMembers. Since a non-null Id is provided for each account, the update operation is performed. But since there is no such Id value in MailChimp, each record will fail.

0
votes
Danart Ros posted this 22 December 2017

Hello Simon,

Thank you for your response. 

To your first point. We can confirm that we did not modify any of our records from Salesforce that would cause multiple scheduled feeds to go over with over 3,000 records. Could it be the way I configured my UPDATE/UPSERT - ACCOUNT task that would cause duplicate records to be sent over again and again?

To your next point ("When a NULL value is provided for the target Id column, the record will be inserted."), I am a little bit confused. If I already have the INSERT task activated/scheduled which essentially imports all new records that have not been subscribed yet, do I still need have the UPSERT task activated for new subscriptions? Please review my INSERT intergrations.

The only issue I am ecountering is only having records that have beed modified in Salesforce to feed over to MailChimp and update existing subscribers. Would ("if a non-null value is provided for your target Id column, the update operation is performed") be the way to do it?

Lastly to your final point, If the UPDATE/UPSERT - ACCOUNT is set up incorrectly, What needs to be changed to only target modified records from salesforce and to have imported into MailChimp and update existing subscribers? Right now, Email Field and List Field is filled in. What do I need to do in the ID field to make it work and not have 3,000 records feed over everytime when the emails have already been subscribed?

 

 

 

0
votes
Mariia Zaharova posted this 26 December 2017

To your next point ("When a NULL value is provided for the target Id column, the record will be inserted."), I am a little bit confused. If I already have the INSERT task activated/scheduled which essentially imports all new records that have not been subscribed yet, do I still need have the UPSERT task activated for new subscriptions?

You have one package with the UPSERT operation that is scheduled to run every 3 hours. Most likely, 3000 records were inserted since the last package run (during 3 hours). As we can see, this behaviour is non-persistent.

Also, you have one package with the INSERT operation and state filter = Inserted. If the package with UPSERT operation runs first, it will insert all new records. Then, the package with the INSERT operation will also insert these new records which may lead to duplicates, errors, etc.

Please note, UPSERT operation always performs two operations - INSERT or UPDATE. In your scenario, you should use one package with the UPSERT operation or two packages with the INSERT and UPDATE operations.

 

 The only issue I am encountering is only having records that have been modified in Salesforce to feed over to MailChimp and update existing subscribers. Would ("if a non-null value is provided for your target Id column, the update operation is erformed") be the way to do it?

The UPSERT operation updates a record if it exists or inserts a new record. This allows you to avoid inserting duplicate data. You need to map the target ID/Primary key columns for performing UPSERT.

In Skyvia, UPSERT determines what action to perform in the following way: if a Null value is specified for the ID or primary key, UPSERT operation inserts the record, and if a non-null value is specified, UPSERT operation tries to update the record with the specified ID or primary key. Skyvia does not actually checks if such record exists, and providing invalid ID/PK values results in failed records.

 

Lastly to your final point, If the UPDATE/UPSERT - ACCOUNT is set up incorrectly, What needs to be changed to only target modified records from salesforce and to have imported into MailChimp and update existing subscribers?

The first two tasks in your package (importing data from Contact and Lead) have correct lookup mapping for the id field. But the third task, for the Account object, uses just column mapping for the Id field. This is not correct. Id values in Salesforce Account are always different from Id values in MailChimp ListMembers. Since a non-null Id is provided for each account, the update operation is performed. But since there is no such Id value in MailChimp, each record will fail. Thus, you need to setup mapping for the Id field like it is made for the first two tasks.

Close