Learn more
Guides

Dotdigital Exporter

v0.0.0

Suggest Edits

📘

The Dotdigital Contacts API documentation can be accessed here

🚧

Important Note on Data Management

  • We recommend testing this connector using credentials from a staging environment to ensure data flows as intended. Given that the reports can overwrite existing data or create new entries, please proceed with caution when executing them to prevent any unintended changes.

Set up


To get set up with the DotDigital connector you will need the following:

  • Username - Your DotDigital username
  • Password - Your DotDigital password
  • Region - The Dotdigital region id your account belongs to e.g. r1, r2 or r3

Features

FeatureSupportNotes
API reliability🟢Reliable

Reports detail

⬇️ Report🔑 Incremental key🔑 Primary key📄 Link to API endpoint
Create ContactsN/AN/ACreate Contacts
Update ContactsN/AN/AUpdate Contacts
Replace ContactsN/AN/AReplace Contacts
Delete ContactsN/AN/ADelete Contacts
Import Contacts CollectionN/AN/AImport Contacts Collections
Bulk Import Insight DataN/AN/ABulk Import Insight Data
Check Import Job Status (Contacts Data or Insight Data )N/AN/ARetrieve the status of a Insight data import request and Retrieve the status of a contacts import request

Source Table Creation

  • For the Create Contacts report, the minimum required keys for the object to post are shown below, using the contact email as the identifier. Additional key options that can be added are available via the docs.

    CREATE OR REPLACE TABLE CREATE_CONTACTS_DATA (
    DATA VARCHAR NOT NULL  
);

INSERT INTO CREATE_CONTACTS_DATA (DATA)
VALUES (
    '{
        "identifiers": {
            "email": "kleenetestprod5@gmail.com"
        },
        "preferences": [
            {
                "isOptedIn": true,
                "id": 123
            },
            {
                "isOptedIn": false,
                "id": 435
            }
        ],
        "consentRecords": [
            {
                "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
                "text": "Yes, I agree to the terms.",
                "dateTimeConsented": "2024-02-05T15:30:00Z",
                "ipAddress": "192.168.2.20",
                "url": "https://example.com/resource/7f3a9c2b-1234-4d56-89ab-5e6d7f8a9b0c"
            }
        ]
    }'
);

  • For the Update Contacts report, you need to include both request_identifier and request_value, in addition to the data fields you want to update for the contacts. See the example below. Additional key options that can be added are available via the docs.

    CREATE OR REPLACE TABLE UPDATE_CONTACTS_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO UPDATE_CONTACTS_DATA (DATA)
VALUES (
    '{
        "request_identifier": "email",
        "request_value": "kleenetestprod1@gmail.com",
        "identifiers": {
            "email": "kleenetestprod1@gmail.com"
        },
        "dataFields": {
            "FIRSTNAME": "userOne",
            "LASTSUBSCRIBED": "2024-09-11T15:30:00Z"
        },
        "preferences": [
            {
                "isOptedIn": false,
                "id": 907
            }
        ],
        "consentRecords": [
            {
                "userAgent": "Mozilla/5.0 (X11; OpenBSD i386)...",
                "text": "Yes, I would like to receive emails",
                "dateTimeConsented": "2024-02-05T15:30:00Z",
                "ipAddress": "129.168.0.2",
                "url": "https://example.com/resource/7f3a9c2b-1234-4d56-89ab-5e6d7f8a9b0c"
            },
            {
                "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
                "text": "Yes, I agree to the terms.",
                "dateTimeConsented": "2024-02-05T15:30:00Z",
                "ipAddress": "192.168.2.20",
                "url": "https://example.com/resource/7f3a9c2b-1234-4d56-89ab-5e6d7f8a9b0c"
            }
        ]
    }'
);

  • For the Replace Contacts report, you need to include request_identifier, request_value, and mergeOption, in addition to the contacts data. Note: This will replace the entire contacts data. Use the Update Contacts report for specific field updates. However, consentRecords cannot be replaced. See here for all additional key options.

    CREATE OR REPLACE TABLE REPLACE_CONTACTS_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO REPLACE_CONTACTS_DATA (DATA)
VALUES (
    '{
        "request_identifier": "email",
        "request_value": "kleenetestprod1@gmail.com",
        "mergeOption": "overwrite",
        "identifiers": {
            "email": "kleenetestprod1@gmail.com"
        },
        "preferences": [
            {
                "isOptedIn": false,
                "id": 1445
            },
            {
                "isOptedIn": true,
                "id": 8893
            }
        ]
    }'
);

  • For the Delete Contacts report, you need to include request_identifier and request_value. The example below creates a table with two rows of contacts to be deleted, identified via their email addresses. See here for more details. 

    CREATE OR REPLACE TABLE DELETE_CONTACTS_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO DELETE_CONTACTS_DATA (DATA)
VALUES 
    (
        '{
            "request_identifier": "email",
            "request_value": "kleenetestprod1@gmail.com"
        }'
    ),
    (
        '{
            "request_identifier": "email",
            "request_value": "kleenetestprod2@gmail.com"
        }'
    );

  • For the Import Contacts Collection report, the example shown below uses the contact email as the identifier. In case of a size error, split the contacts into multiple rows, see here for more details.

    Note: This update is processed asynchronously by the API. Use the Check Import Job Status report with the import ID in the source table to check the status of the contact data import.

    CREATE OR REPLACE TABLE CONTACTS_COLLECTIONS_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO CONTACTS_COLLECTIONS_DATA (DATA)
VALUES (
    '{
        "mergeOption": "overwrite",
        "contacts": [
            {
                "matchIdentifier": "email",
                "identifiers": {
                    "email": "kleenetestprod11@gmail.com"
                },
                "dataFields": {
                    "CPAAS_PROFILE_ID": "userFive",
                    "FIRSTNAME": "Ethan",
                    "LASTSUBSCRIBED": "2024-10-10T08:00:00Z",
                    "FUNDRAISER": "MedicalAid"
                },
                "preferences": [
                    {
                        "isOptedIn": true,
                        "id": 8632
                    }
                ],
                "consentRecords": [
                    {
                        "userAgent": "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
                        "text": "Yes, I agree to receive promotional emails.",
                        "dateTimeConsented": "2025-02-01T17:55:00Z",
                        "ipAddress": "10.1.2.50",
                        "url": "https://example.com/resource/12de34fa-56bc-78cd-90ef-3456789012ab"
                    }
                ]
            },
            {
                "matchIdentifier": "email",
                "identifiers": {
                    "email": "kleenetestprod12@gmail.com"
                },
                "preferences": [
                    {
                        "isOptedIn": true,
                        "id": 6624
                    },
                    {
                        "isOptedIn": false,
                        "id": 6632
                    }
                ],
                "consentRecords": [
                    {
                        "userAgent": "Mozilla/5.0 (iPhone; CPU iPhone OS 16_1 like Mac OS X) AppleWebKit/537.36 (KHTML, like Gecko) Version/16.0 Mobile/15E148 Safari/537.36",
                        "text": "No, I do not want notifications.",
                        "dateTimeConsented": "2025-01-28T13:40:00Z",
                        "ipAddress": "172.16.1.44",
                        "url": "https://example.com/resource/90ab12cd-34ef-56fa-78bc-1234567890de"
                    }
                ]
            },
            {
                "matchIdentifier": "email",
                "identifiers": {
                    "email": "kleenetestprod14@gmail.com"
                },
                "preferences": [
                    {
                        "isOptedIn": false,
                        "id": 60132
                    }
                ],
                "consentRecords": [
                    {
                        "userAgent": "Mozilla/5.0 (iPhone; CPU iPhone OS 16_1 like Mac OS X) AppleWebKit/537.36 (KHTML, like Gecko) Version/16.0 Mobile/15E148 Safari/537.36",
                        "text": "No, I do not want notifications.",
                        "dateTimeConsented": "2025-01-28T13:40:00Z",
                        "ipAddress": "172.16.1.44",
                        "url": "https://example.com/resource/90ab12cd-34ef-56fa-78bc-1234567890de"
                    }
                ]
            }
        ]
    }'
);

  • For the Bulk Import Insight Data report, creates new records or updates already existing records for the contact email added. In case of a size error, split the contacts into multiple rows, see here for more details.

    Note: This update is processed asynchronously by the API. Use the Check Import Job Status report with the import ID in the source table to check the status of the insight data import.

    CREATE OR REPLACE TABLE CONTACTS_INSIGHT_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO CONTACTS_INSIGHT_DATA (DATA)
VALUES 
    (
        '{
            "collectionName": "Kleene_livetest2",
            "collectionScope": "contact",
            "collectionType": "custom",
            "records": [
                {
                    "contactIdentity": {
                        "identifier": "email",
                        "value": "kleenetestprod13@gmail.com"
                    },
                    "key": "5566778",
                    "json": {
                        "datetimestamp": "2025-02-18T12:14:33.753",
                        "data_source": "",
                        "professional": "chef",
                        "avaliablitity": "occasionally"
                    }
                },
                {
                    "contactIdentity": {
                        "identifier": "email",
                        "value": "kleenetestprod10@gmail.com"
                    },
                    "key": "210987654321",
                    "json": {
                        "data_source": "Tablet",
                        "avaliablitity": "twice a week"
                    }
                },
                {
                    "contactIdentity": {
                        "identifier": "email",
                        "value": "kleenetestprod4@gmail.com"
                    },
                    "key": "1100998877",
                    "json": {
                        "data_source": "Tablet",
                        "avaliablitity": "twice a week"
                    }
                }
            ]
        }'
    );

  • For the Check Import Job Status report, you can check the results of asynchronous import reports, such as Bulk Import Insight Data and Import Contacts Collection. Create the source table using the importId returned as shown below:

    CREATE OR REPLACE TABLE JOB_STATUS_DATA (
    DATA VARCHAR NOT NULL
);

INSERT INTO JOB_STATUS_DATA (DATA)
VALUES
    ('{"importId": "74dc3c02-cb5d-4e5b-a314-ac5a03f1a1bc"}'),
    ('{"importId": "1094c97c-cc8b-4117-b316-15a4583f1afc"}'),
    ('{"importId": "9a7f5efc-63c1-407a-9708-e9ac5e68b2be"}');

📘

Behaviour of exporter (and additional table for logging)

Whenever you run these reports the connector will store the requests in a table in the same schema with the same name as the destination table + _responses

so if you export little_pond.big_fish, it’ll be little_pond.big_fish_responses

The rows will contain the first level of the request map flattened (like url, method, headers, etc) as columns, and then a response and error columns with the response or error that came from that request.

In subsequent runs, the new rows will be appended to that same table.

Limitations

🚧

Specifying null for a fields value clears the field

Regarding the Import Contacts Collection and Update Contacts reports, only pass the fields you require updating on a contact. Dotdigital interpret a field passed with a null value as an instruction to clear the value for that field. See here for more details.

Updated 1 day ago