Eloqua Bulk API – Export filter with EXISTS() segment to only allow system updated or created fields as additional criterion (May 2019)

Version 2

    Overview

     

    With the arrival of the 19B Eloqua release (May 2019), Bulk API contact export definitions with an EXISTS() Segment, e.g. EXISTS('{{ContactSegment[<id>]}}'), will only allow up to two system updated or created date criterion.

     

    What’s changing?

    With 19B, when Bulk API contact export definitions have an EXISTS() Segment, e.g. EXISTS('{{ContactSegment[<id>]}}'), validation will be added to ensure the following:

    • Only the following system date field statements can be used in the filter:

    {{Contact.UpdatedAt}}

    {{Contact.CreatedAt}}

    {{Contact.Field(C_DateModified)}}

    {{Contact.Field(C_DateCreated)}}

    • Only the following logic can be used in the filter with a maximum of two date criterion:

    segmentCriterion AND dateCriterion

    segmentCriterion AND dateCriterion1 AND dateCriterion2

    segmentCriterion AND (dateCriterion1 OR dateCriterion2)

     

    Notes:

    • If using a field or logic not listed above when creatingupdating
    • If syncing an existing export definition using a field or logic not listed above, the sync will end in error with a status code of “ELQ-00122”

     

    Examples

     

    After 19B:

     

    A validation error will be returned if including any non-system date field with an
    EXISTS() Segment:

     

    Request:

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "Filter on Segment AND Non-system Date Field",

      "fields": {

                "id": "{{Contact.Id}}"

      },

      "filter": "EXISTS('{{ContactSegment[6]}}') AND '{{Contact.Field(C_FirstName)}}'='John'"

    }

     

    Response: 400 There was a validation error.

     

    {

        "failures": [

            {

                "field": "filter",

                "value": "EXISTS('{{ContactSegment[6]}}') AND '{{Contact.Field(C_FirstName)}}'='John'",

                "constraint": "Must contain an expression that filters by EXISTS() Segment and system updated or created date criterion only. Found other criterion."

            }

        ]

    }

     

     

    A validation error will be returned if including more than
    two date criterion / unsupported logic:

     

    Request:

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "Filter on Segment with 3 Date Criterion",

      "fields": {

                "id": "{{Contact.Id}}"

      },

      "filter": "EXISTS('{{ContactSegment[6]}}') AND '{{Contact.Field(C_DateModified)}}'>'2019-01-01' AND ('{{Contact.Field(C_DateModified)}}'<'2019-02-01' AND '{{Contact.Field(C_DateCreated)}}'<'2019-01-01')"

    }

     

    Response: 400 There was a validation error.

     

    {

        "failures": [

            {

                "field": "filter",

                "value": "EXISTS('{{ContactSegment[6]}}') AND '{{Contact.Field(C_DateModified)}}'>'2019-01-01' AND ('{{Contact.Field(C_DateModified)}}'<'2019-02-01' AND '{{Contact.Field(C_DateCreated)}}'<'2019-01-01')",

                "constraint": "Must contain an expression that filters by EXISTS() Segment and system updated or created date criterion with supported logic. Found invalid logic. Supported logic includes the following: segmentCriterion AND dateCriterion, segmentCriterion AND dateCriterion1 AND dateCriterion2, segmentCriterion AND (dateCriterion1 OR dateCriterion2)"

            }

        ]

    }

     

    Example Valid Export Definitions Before and After 19B:

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "BulkAPI_Segment_Filter-Segment+UpdatedAt>",

      "fields": {

        "C_EmailAddress": "{{Contact.Field(C_EmailAddress)}}",

        "ContactIdExt": "{{Contact.Field(ContactIDExt)}}",

        "C_DateCreated": "{{Contact.Field(C_DateCreated)}}",

        "C_DateModified": "{{Contact.Field(C_DateModified)}}"

      },

      "filter": "EXISTS('{{ContactSegment[2708]}}') AND '{{Contact.UpdatedAt}}'>'2018-06-17'"

    }

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "BulkAPI_Segment_Filter-Segment+CreatedAt<",

      "fields": {

        "C_EmailAddress": "{{Contact.Field(C_EmailAddress)}}",

        "ContactIdExt": "{{Contact.Field(ContactIDExt)}}",

        "C_DateCreated": "{{Contact.Field(C_DateCreated)}}",

        "C_DateModified": "{{Contact.Field(C_DateModified)}}"

      },

      "filter": "EXISTS('{{ContactSegment[2708]}}') AND '{{Contact.CreatedAt}}'<'2016-01-06'"

    }

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "BulkAPI_Segment_Filter-Segment+C_DateCreated<=",

      "fields": {

        "C_EmailAddress": "{{Contact.Field(C_EmailAddress)}}",

        "ContactIdExt": "{{Contact.Field(ContactIDExt)}}",

        "C_DateCreated": "{{Contact.Field(C_DateCreated)}}",

        "C_DateModified": "{{Contact.Field(C_DateModified)}}"

      },

      "filter": "EXISTS('{{ContactSegment[2708]}}') AND '{{Contact.Field(C_DateCreated)}}'<='2015-11-08 23:41:07.813'"

    }

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "BulkAPI_Segment_Filter-Segment+2CriterionC_DateModifiedC_DateCreated",

      "fields": {

        "C_EmailAddress": "{{Contact.Field(C_EmailAddress)}}",

        "ContactIdExt": "{{Contact.Field(ContactIDExt)}}",

        "C_DateCreated": "{{Contact.Field(C_DateCreated)}}",

        "C_DateModified": "{{Contact.Field(C_DateModified)}}"

      },

      "filter": "EXISTS('{{ContactSegment[2708]}}') AND '{{Contact.Field(C_DateModified)}}'>'2018-06-01' AND '{{Contact.Field(C_DateCreated)}}'<='2016-01-05'"

    }

     

    POST /api/Bulk/2.0/contacts/exports

     

    {

      "name": "BulkAPI_Segment_Filter-Segment+2CriterionCreatedAtOR",

      "fields": {

        "C_EmailAddress": "{{Contact.Field(C_EmailAddress)}}",

        "ContactIdExt": "{{Contact.Field(ContactIDExt)}}",

        "C_DateCreated": "{{Contact.Field(C_DateCreated)}}",

        "C_DateModified": "{{Contact.Field(C_DateModified)}}"

      },

      "filter": "EXISTS('{{ContactSegment[2708]}}') AND ('{{Contact.CreatedAt}}'>'2016-01-05' OR '{{Contact.CreatedAt}}'<'2015-11-05')"

    }

     

    Timeline

    With the arrival of the 19B Eloqua release (May 2019), Bulk API contact export definitions with an EXISTS() Segment, e.g. EXISTS('{{ContactSegment[<id>]}}'), will only allow up to two system updated or created date criterion. Check the Eloqua Release Center for specific dates and times.

     

    Next Steps

    Review any existing Bulk API contact export definitions with an EXISTS() Segment, and processes creating new Bulk API contact export definitions with an EXISTS() Segment to confirm the following:

    • Only the following system date field statements are used in the filter:

    {{Contact.UpdatedAt}}

    {{Contact.CreatedAt}}

    {{Contact.Field(C_DateModified)}}

    {{Contact.Field(C_DateCreated)}}

    • Only the following logic is used in the filter with a maximum of two date criterion:

    segmentCriterion AND dateCriterion

    segmentCriterion AND dateCriterion1 AND dateCriterion2

    segmentCriterion AND (dateCriterion1 OR dateCriterion2)

     

    If using Bulk API contact export definitions with an EXISTS() Segment and non-system date criterion, these should be updated to include this criterion directly in the Segment instead of in the Bulk API export definition filter.

     

    Additional Resources

    View changes for Eloqua's APIs including, new features, significant recent changes, and platform notices, on the Eloqua Developer Changelog.

     

    If you have questions, post a discussion on Code It!

     

    FAQ

     

    Q: What endpoints are directly impacted by this change?

    A: The following endpoints are directly impacted:

     

    Q: What happens if I sync an export definition created prior to 19B that includes now invalid fields or logic?

    A: The sync will end in error with a status code of “ELQ-00122”

     

    Q: Why is this change being implemented?

    A: We can provide a more performant export by including non-system date fields directly in the Segment.

     

    Q: Are there any API endpoints to interact with Segments?

    A: Yes, see the Contact Segment endpoints.