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