Jaaromzet uit Exact Online naar ActiveCampaign contacten synchroniseren

Wiki (nl)
, ,
1

In campagnes of automations binnen ActiveCampaign kan het gewenst zijn om data uit Exact Online te verwerken. In dit voorbeeld is te zien hoe de omzet van het huidige kalenderjaar en het vorige kalenderjaar in een custom field van een ActiveCampaign contact geladen kan worden.

De onderstaande code voor de ActiveCampaign-koppeling laadt waar nodig actuele omzetcijfers uit Exact Online van klanten als custom velden in ActiveCampaign. Aanroepen via de Invantive-driver voor ActiveCampaign worden automatisch en transparant omgezet in aanroepen van de API (“application programming interface”).

Voorbereidingen ActiveCampaign

In dit voorbeeld wordt er van uit gegaan dat een contactpersoon in ActiveCampaign de volgende custom fields heeft:

  • DEBITEURNUMMER: de relatiecode uit Exact Online,
  • ANNUAL_REVENUE: de omzet in het lopende kalenderjaar,
  • PREVIOUS_ANNUAL_REVENUE: de omzet in het vorige kalenderjaar.

Alle drie de custom fields zijn tekstvelden; DEBITEURNUMMER wordt uitgelezen via de koppeling en de andere twee worden geladen via de Exact Online-ActiveCampaign koppeling.

Daarnaast wordt er van uit gegaan dat er een Invantive Cloud-database is met daarin twee datacontainers:

  • Exact Online met de alias eol,
  • ActiveCampaign met de alias acn.

Deze database met twee datacontainers kan gemaakt worden zoals beschreven voor Exact Online op Verbind Power BI met Exact Online, 180 dagen gratis en daarna te kiezen voor Datacontainers → Nieuw → ActiveCampaign. De database is niet alleen voor Power BI te gebruiken, maar ook voor lezen, schrijven en synchroniseren.

Module

De volgende code kan in een Invantive Cloud-applicatiemodule gebruikt worden. Deze module toont bij uitvoering een formulier met een groene knop waarmee de synchronisatie gestart kan worden. De synchronisatie duurt voor 1.000 wijzigingen typisch circa 5 minuten en is incrementeel.

Het laden kan handmatig via de webmodule, maar kan ook automatisch door via Invantive App Online elke x uur de App Online-URL aan te roepen. Zo valt de integratie verder te automatiseren.

Meer voorbeelden zijn te vinden op Hoe maak ik een ActiveCampaign API koppeling?.

declare
  p_run           boolean;
  --
  l_workbook      excel_workbook;
  l_worksheet     excel_worksheet;
  l_xlsx_filename varchar2;
  l_binary        blob;
begin
  --
  -- Get parameters.
  --
  p_run := cast(cloud_http. get_request_form_value('p_run') as boolean);
  --
  if coalesce(p_run, false) = false
  then
    --
    -- Enter parameters manually (currently solely a button to start synchronization).
    --
    cloud_http.set_use_template(true);
    cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
    cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
    cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
    cloud_http.append_line_to_response_body_text('<ul>');
    cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Synchroniseer"/></li>');
    cloud_http.append_line_to_response_body_text('</ul>');
    cloud_http.append_line_to_response_body_text('</form>');
  else
    --
    -- Synchronize revenues to ActiveCampaign.
    --
    create or replace table cttac@inmemorystorage
    as
    select cttac.*
    from   contacts@acn cttac
    ;
    create or replace table ContactWithCustomFieldValues@inmemorystorage
    as
    select contact
    ,      field
    ,      value
    from   ContactWithCustomFieldValues@acn
    ;
    create or replace table ACContact2EOLCompany@InMemoryStorage
    as
    select cfe.contact
           contactid
    ,      cast(cfe.value as decimal)
           eol_account_code
    --
    ,      cttac.email
           contactemail
    ,      cttac.firstName
           contactfirstname
    ,      cttac.lastName
           contactlastname
    from   CustomFields@acn cfd
    join   ContactWithCustomFieldValues@inmemorystorage cfe
    on     cfe.field   = cfd.id
    and    cfe.value is not null
    join   cttac@inmemorystorage cttac
    on     cttac.id    = cfe.contact
    where  cfd.perstag = 'DEBITEURNUMMER'
    ;
    create or replace table RevenuesPerYear@InMemoryStorage
    as
    select act.Code AccountCode
    ,      act.Name AccountName
    ,      sum
           ( case
             when sie.InvoiceDate >= trunc(sysdateutc, -2)
             then sie.AmountDC
             else 0
             end
           ) 
           AnnualRevenue
    ,      sum
           ( case
             when sie.InvoiceDate between add_months(trunc(sysdateutc, -2), -12) and trunc(sysdateutc, -2)
             then sie.AmountDC
             else 0
             end
           ) 
           PreviousAnnualRevenue
    from   SalesInvoicesIncremental@eol sie
    join   AccountsIncremental@eol act
    on     act.Division = sie.Division
    and    act.Id       = sie.InvoiceTo
    group
    by     act.Code
    ,      act.Name
    ;
    create or replace table ContactCustomFieldValues_soll@InMemoryStorage
    as
    select cfd.id field
    ,      act.contactid contact
    ,      to_char(round(rve.AnnualRevenue)) value
    from   ACContact2EOLCompany@InMemoryStorage act
    join   CustomFields@acn cfd
    on     cfd.perstag = 'ANNUAL_REVENUE'
    left
    outer
    join   RevenuesPerYear@InMemoryStorage rve
    on     rve.AccountCode = act.eol_account_code
    union all
    select cfd.id field
    ,      act.contactid contact
    ,      to_char(round(rve.PreviousAnnualRevenue)) value
    from   ACContact2EOLCompany@InMemoryStorage act
    join   CustomFields@acn cfd
    on     cfd.perstag = 'PREVIOUS_ANNUAL_REVENUE'
    left
    outer
    join   RevenuesPerYear@InMemoryStorage rve
    on     rve.AccountCode = act.eol_account_code
    ;
    --
    -- Update/insert Custom Field values.
    --
    insert into customfieldvalues@acn
    ( contact
    , field
    , value
    )
    select contact
    ,      field
    ,      value
    from   ContactCustomFieldValues_soll@inmemorystorage
    minus
    select contact
    ,      field
    ,      value
    from   ContactWithCustomFieldValues@inmemorystorage
    ;
    --
    -- Retrieve results as an Excel workbook.
    --
    create or replace table output@inmemorystorage
    as
    select *
    from   RevenuesPerYear@InMemoryStorage
    ;
    l_xlsx_filename := 'sync-results-' || to_char(sysdateutc, 'YYYYMMDDHH24MISS') || '.xlsx';
    l_workbook := excel.new();
    --
    -- Add a worksheet with a title.
    --
    l_worksheet := excel.add_worksheet
    ( l_workbook
    , 'Parameters'
    );
    excel.set_cell_contents(l_worksheet, 'A1', 'ActiveCampaign to Exact Online load revenues using Invantive Cloud');
    excel.set_cell_contents(l_worksheet, 'A3', 'Created (UTC)');
    excel.set_cell_contents(l_worksheet, 'B3', sysdateutc);
    --
    l_worksheet := excel.add_worksheet
    ( l_workbook
    , 'Logs'
    );
    excel.fill_using_query(l_worksheet, 'select txt from output@inmemorystorage');
    --
    -- Retrieve the resulting XLSX-file.
    --
    l_binary := excel.export_to_xlsx(l_workbook);
    --
    -- Return the XLSX file to the user.
    --
    cloud_http.set_response_body_binary(l_binary);
    cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="' || l_xlsx_filename || '"');
  end if;
end;