The free Brevo-connector for Invantive SQL allows both data extraction as well as loading to support marketing automation, email campaigns and transactional emails.
Brevo (formerly “sendinblue”) currently does not support labels to be assigned to contacts, but membership of a contact list is an alternative approach for labels. Each contact can be member of one out of hundreds of lists.
An especial performance feature of Brevo, is that custom fields are integrated into mass contact downloads as well on uploads. This saves dramatically on the number of API-calls required to synchronize contacts with Brevo without any additional effort by the application developer using the Invantive SQL synchronize
statement.
However, for list membership, things are more complicated. Brevo provides a list of lists a contact is member of with mass contact downloads as well as with uploads, but for intelligent comparison and uploading it is needed to integrate these two lists.
The following code provides a sample for exchanging contact data including custom fields and lists membership from Invantive Estate to Brevo. Note that Brevo is one of the supported connectors on the Free Plan, together with for instance Microsoft SQL Server.
First, the current and intended list memberships are taken from, respectively, Invantive Estate and Brevo, and combined to the intended value of the listIds
column for comparing and uploading:
create or replace table MergedListIds@InMemoryStorage
as
--
-- Take lists registered in Invantive Estate as person classifications.
--
select gbr_id
, lst.id
from bubs_gbr_kle_v@ora gke
join ( select id
, name kle_code
from lists@sib lst
) lst
on lst.kle_code = gke.kle_code
union
--
-- Add lists registered only in Brevo.
--
select gbr.gbr_id
, cte.id
from Contacts@sib ctt
join bubs_gebruikers_v@ora gbr
on gbr.gbr_email_adres = ctt.email
--
-- Split lists like "1, 2, 3" into three rows.
--
join csvtable
( passing ctt.listids
row delimiter ','
column delimiter '######'
columns id int position next
) cte
where ctt.listids is not null
create or replace table ContactListsSoll@InMemoryStorage
as
select gbr_id
--
-- Sorted by number, separated by comma plus space to match data format retrieved
-- from SendInBlue.
--
, listagg(id, ', ') listids
from MergedListIds@InMemoryStorage
group
by gbr_id
In the next step, the intended values for the contact, it’s custom fields as well as the list memberships are constructed:
create or replace table ContactsSoll@InMemoryStorage
as
select gbr.gbr_mobiel_tel
mobile_number
, gbr.gbr_email_adres
email
, gbr.gbr_voornaam
firstname
, gbr.gbr_id
bubs_gbr_id
, gbr.gbr_achternaam
lastname
, gbr.lge_code
language_code
, gbr.lvr_land
country
, gbr.gbr_functie
jobtitle
, gbr.gbr_werk_tel
phone_number
, gbr.lvr_naam
company_name
, glt.listIds
from bubs_gebruikers_v@ora gbr
left
outer
join ContactListsSoll@InMemoryStorage glt
on glt.gbr_id = gbr.gbr_id
where gbr.gbr_email_adres is not null
and gbr.gbr_klassificatie_reeks_c is not null
order
by gbr.gbr_id desc
The actual loading is then very simple. The following statement compares the Brevo-contacts with the intended contact information. Where necessary, an update or insert is executed:
synchronize ContactsSoll@InMemoryStorage
to contacts@sib
identified
by email ignore nulls