Deletion of CDO orphan records in Eloqua

Version 3
Visibility: Open to anyone

    We often come across a situation when a contact gets deleted from Eloqua the linked record in the Custom Object Records becomes unmapped until we manually map them to different contact or mostly keep them as it is. As the CDO record count goes on increasing these records becomes burden on the Instance and not useful anymore, so what next?

     

    Delete unmapped CDO records from Eloqua: We can achieve this by using API or Program builder, both these option have some pros and cons, let’s see in details

     

    a)Deletion Using OOTB Program Builder:

    Step 1:

    Identify the CDO where the orphan records need to be deleted in Eloqua, create a PB using the specific CDO as default member

    Step 2:

    Pass these records through a decision step and provide a filter to check if contact linked to CDO without any condition(NA)

    1.bmp2.bmp

     

    Step 3:

    The expected records in the “Yes” path will the mapped records which should be removed from the PB and the “No” path records need to be processed (targeted orphan records)

    Step 4:

    Update the “No” path records with some meaningful status say “Y”

    3.bmp

    Step 4:

    After update rule step move out of Program, keep the Program enabled as on required.

    Step 5:

    Create a CDO program for the desired CDO by selecting it while creating this asset and pull a listener, Has Linked Contact step, for additional validation pull a Compare Custom Object Record and finally the Delete Custom Object record step. All together it should look like as below

    4.bmp

    Save this and we are almost done.

    Step 6:

    Create modified data service for the desired CDO, so as to watch all the modified records by the Program Builder and pass only the Orphan records (status as Y) to the deletion program canvas (created in the last step), by adding the action “Add to step in Program”

    5.bmp

    Idea is to watch for the modified CDO records and pass it to CDO Program canvas where the deletion status set to “Y”, this key value can be set as per the user requirement. Next choose the program canvas listener step and provide a filter for the CDO records as below

    6.bmp

    Do remember to enable the modified data service, to make it as a feeder.

    Overall process is as simple as:

    Modify CDO Records ->Pick required records using record service -> Pass it Program Canvas-> Delete Records

    Pros:

    • OOTB functionality, no custom development is required

    Cons:

    • Dependent on specific CDO, can’t be reused for others

     

    b)Deletion Using API:

    Using Eloqua API also we can delete the required CDO records, the below steps may be helpful to achieve this

    1. Read CDO Data:

         The objective is to read the CDO data row by row, here filter condition can be applied like date range or any specific column filter to retrieve data. Otherwise directly the all CDO records can be pulled.

         To use filter we can make use export uri.

      

         URL : URL :https://secure.p04.eloqua.com/API/REST/1.0/data/customObject/CDO_ID/?limit=1000&offset=1000

         Method : GET

         Authentication : Basic

        2. Read Row ID

        Form the response retrieve the Row ID, here row id denotes the each row of CDO data, so it will be similar as shown in red font

        Response:

    {

        "elements": [

            {

                "type": "CustomObjectData",

                "id": "174308668",

                "contactId": "61107",

                "fieldValues": [

                    {

                        "type": "FieldValue",

                        "id": "271",

                        "value": "5022084281029822019-03-19"

                    },

                    {

                        "type": "FieldValue",

                        "id": "272",

                        "value": "3003701"

                    }

     

        3. Delete CDO Records

         After getting the Row ID in step 3 loop through the records and pass each row id to the below URL and make a delete request

         URL :https://secure.p0X.eloqua.com/API/REST/2.0/data/customObject/CDO_ID/instance/RowID

         Method : Delete

         Authentication : Basic

         4. Conclusion:

         The above process mostly taking 1 hr to delete 5K records which I did a year ago, which I felt slow in nature.....

     

    Please let me know if you have any better way to do so!!!!

     

    Thanks for reading.