Financial and marketing systems typically place a different focus on data quality. Salesforce is the only notable exception I know which pairs excellent business rule handling with CRM applications.

The design of processes aligning data between financial and marketing systems requires preparing for these focus differences and making the best of it.

This note provides a sample how to use the Invantive SQL synchronize statement to keep marketing data as aligned as possible to financial data despite API problems.

Source Data

The financial source data is taken from an arbitrary backoffice system and the marketing system has been chosen to be ActiveCampaign. A query collects the intended situation:

create or replace table Contacts_soll@inmemorystorage
as
select distinct
       soll.email
,      soll.date_created
       cdate
,      soll.work_phone
       phone
,      soll.given_name
       firstName
,      soll.family_name
       lastName
,      gbrmap.contactId
       id
       label 'Active Campaign Contact ID'
,      actmap.accountId
       organization
       label 'Active Campaign Account ID'
,      actmap.accountId
       orgId
       label 'Active Campaign Account ID'
,      soll.id
,      soll.org_country
,      soll.language_code
,      soll.mobile_phone
from   contacts@inmemorystorage soll
left
outer
join   cttmap@inmemorystorage cttmap
on     cttmap.bubs_ctt_id = soll.ctt_id
left
outer
join   actmap@inmemorystorage actmap
on     actmap.bubs_act_id = soll.act_id
CODE

This query provides a flattened structure of contact person plus references to a possibly existing registration on contact and account level in ActiveCampaign.

Please remember that ActiveCampaign, similar to many other marketing systems, splits the storage of standard and environment-specific information between a base table and values for custom fields. The standard information of ActiveCampaign contacts is available in Invantive SQL through “Contacts“, whereas the custom fields and their values are stored in, respectively, “CustomFields“ and “CustomFieldValues“.

Please remember also that specifically ActiveCampaign has two unique keys on contacts: “email” as a natural key and “id” as a technical key, with the id being assigned a value on insert. In this sample, the SQL will maintain a correlation between the back office key and the ActiveCampaign technical key using a custom field “BUBSCTTID“ on the contact in ActiveCampaign.

Existing registrations on contacts and accounts in ActiveCampaign have been established by a query on ActiveCampaign such as:

select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       cfe.contact
       contactid
,      cast(cfe.value as decimal)
       bubs_ctt_id
,      'cfd'
       src
from   CustomFields@ac cfd
join   CustomFieldValues@ac cfe
on     cfe.Field = cfd.id
where  cfd.perstag = 'BUBSCTTID'
--
-- Resort to email address matching when custom field
-- values are not yet present.
--
union distinct on contactid
select /*+ http_disk_cache(false) http_memory_cache(false) */ 
       cttac.id
       contactid
,      cttbubs.ctt_id
       bubs_ctt_id
,      'ctt'
       src
from   contacts@ac cttac
join   contacts@inmemorystorage cttbubs
on     cttbubs.gbr_email_adres = cttac.email
CODE

Note the use of “union distinct“ to remove duplicates, giving preference to the first occurrence of “contactid” on the left side of the union.

Steps

The alignment of the backoffice data towards the contact registration in ActiveCampaign consists of several steps:

  • First update base data for previously registered contacts, for which the mapping containing the technical key.

  • Then add new base data for new contacts, based upon email address as natural key.

  • Then refresh the mapping (as above) to add the new contacts and their associated IDs.

  • Then add or update the custom field values.

Error Handling

Due to areas in the APIs with space for improvement, the data upload typically will fail for a small percentage of the data synchronized. However, since ActiveCampaign is a marketing solution this is acceptable for period of time till the data analyst checks and improve the data and/or offers the same set again.

The “continue on first … errors“ clause can be added to the synchronize statement to give the business some slack for correcting errors without havocking the timelines of the marketing process.

Update Registered Contacts

The previously registered contacts can be updated using a directional synchronize on the technical key “id“, which is maintained in ActiveCampaign custom fields for inclusion in the contacts mapping:

--
-- Update known Contacts by ID.
--
synchronize Contacts@ac
from        Contacts_soll@inmemorystorage
with        update all except cdate
            ignore changes to cdate, udate
identified
by          id ignore nulls
continue on first 500 errors
CODE

The “all except … ignores changes to…” is ensure that the creation date is uploaded on initial use, but never changed since ActiveCampaign further maintains itself the audit information. The same holds for the update date.

Add New Contacts

New contacts can be loaded using the “email“ address for identification:

--
-- Register new Contacts by email.
--
synchronize Contacts@ac
from        Contacts_soll@inmemorystorage
with        insert
identified
by          email
continue on first 500 errors
CODE

Add Custom Field Values

In the last step a synchronize is executed on custom field values per contact to ensure the reference back to the backoffice system is available for filling the contacts mapping plus as a sample the country:

create or replace table ContactCustomFieldValues_soll@inmemorystorage
as
select cttmap.contactid contact
,      ccd.id field
,      cast(ctt.ctt_id as varchar2) 
       value
,      ccd.perstag
       src
from   Contacts_soll@inmemorystorage ctt
join   customfields@ac ccd
on     ccd.perstag = 'BUBSCTTID'
join   cttmap@inmemorystorage gbrmap
on     cttmap.bubs_ctt_id = ctt.ctt_id
where  ctt.ctt_id is not null
union all
select cttmap.contactid contact
,      ccd.id field
,      ctt.org_country
       value
,      ccd.perstag
       src
from   Contacts_soll@inmemorystorage gbr
join   customfields@ac ccd
on     ccd.perstag = 'LAND'
join   cttmap@inmemorystorage cttmap
on     cttmap.bubs_ctt_id = ctt.ctt_id
where  ctt.account_country is not null

synchronize CustomFieldValues@ac
from        ContactCustomFieldValues_soll@inmemorystorage
with        insert or update
identified
by          contact
,           field
continue on first 500 errors
CODE