Fast loading of contacts with lists in Brevo (formerly sendinblue)

Wiki (en)
, ,
1

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