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
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
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
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
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