Itgenoda035 on Active Campaign with DealActivities

[smw66]

I keep getting an error message relating to an invalid JSON for the OData. But when I look into the error message through Bridge Online Monitoring I get a different error:
The remote server returned an error: (500) Internal Server Error.

Error: itgenoda035
Exception:
De verbinding met de website is onverwacht en met geweld verbroken om 06/24/2024 14:05:33 voor redenen zoals onvoldoende rechten, het vastlopen van de website, het opnieuw opstarten van het systeem, het hard afsluiten van een externe host of firewallactie.
Het ophalen van gegevens is mislukt voor alle 3 pogingen gedurende 67 seconden.
Neem contact op met de API-ondersteuning van ActiveCampaign. (https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900).
De ActiveCampaign-server heeft een interne fout geretourneerd, meestal is dat een applicatiefout binnen ActiveCampaign.
Neem contact op met de ondersteuning van ActiveCampaign met de verzamelde oproepgegevens van het native platform of stel een vraag op de Invantive-forums met deze oproepgegevens. (https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900).
The remote server returned an error: (500) Internal Server Error.

I did contact ActiveCampaign, but they do not have enough information as of right now. And gave the option of including someone from ActiveCampaign in the email thread to resolve the issue.

[forums]

The reason for the itgenoda035 error is that ActiveCampaign repeatedly rejects a connection with a HTTP 500 Internal Server Error on the Active Campaign API server.

In the past, ActiveCampaign server problems have frequently been seen often during night hours in the United States, typically between 12:00 and 16:00 CET. As a workaround you might want to schedule a refresh using PowerBI.com during business hours of ActiveCampaign HQ.

It is not clear what data would be needed, but assistance on internal server errors on ActiveCampaign and other connected platforms can be acquired in the form of a short consult using the chatbot.

As an alternative you might want to try increasing the number of tries by extending the connection string on the data container of the database to include download-error-500-internal-server-error-max-tries with a higher value. Please note however that when the error is consistent, the download can run for significant longer time without making any progress. More documentation on this is available at SQL Driver for ActiveCampaign API - ActiveCampaign API Data Model.

When the problem reproduces also on Invantive Query Tool, you might also want to try to collect the data yourself using the tips on What API calls did Invantive UniversalSQL actually perform?.

[smw66]

Despite running the refresh during US working hours the JSON error persists.

[forums]

Thanks for the feedback.

It is recommended to use the alternatives given in the previous reply.

The Internal Server Error of ActiveCampaign API server is not considered a bug in the Invantive-software and requires corrective action by ActiveCampaign. In case ActiveCampaign needs reproduction information, this can be collected by following the steps in the last hyperlink in the previous reply.

[smw66]

It seems to be a problem when the data is pivoted. Loading the data works, renaming and merging the data with another table works fine too.
When I go to the pivot step the error message pops up. Any idea on how that can happen?

[forums]

No, it is highly unlikely that both events relate.

To go for safe by hindering query folding, you might want to add a Table.Buffer step.

[smw66]

Analyzed with consultant.

All pages of data can be retrieved, but the 100 deals starting at 1900 can not be retrieved and consistently reports an error. Tested using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900'
)

By reducing the page size the cause could further be zoomed into to be the deal at offset 1964 and 1966. Query used:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=1&offset=1964'
)

When queried one-by-one, the queries worked. The JSON payload for a deal is typically 10-20 KB, but for 1964 (deal ID 732) it is 16 MB and for 1966 (deal ID 734) it is 25 MB of JSON.

The payload of deal with ID 732 (offset 1964) was analyzed using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=1&offset=1964'
)

select j.*
from   nativeplatformscalarrequests@acn t
join   jsontable 
       ( 'dealActivities[*]' 
         passing t.result_text
         columns d_id varchar2 path 'd_id'
         , id varchar2 path 'id'
         , dataType varchar2 path 'dataType'
         , d_stageid varchar2 path 'd_stageid'
         ) j
where    t.transaction_id = 23 /* Taken from query above. */

This revealed that there were 20.064 activities on that deal with data type connection_email:

dataType	Count
connection_email	20064
note	3
userid	2
subscriberid	2
	1
d_stageid	1
task	1

There are many emails on https://site.eu/app/deals/732, but it does not seem realistic that there are over 20.000 in ActiveCampaign.

Filtering manually on the dataType is possible using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900&filters[data_type]=d_stageid'
)

However, even with filter applied, the ActiveCampaign API server does not respond within 60 seconds.

The timeout was increased using:

set http-get-timeout-max-ms@acn 360000

set http-get-timeout-ms@acn 360000

but still the ActiveCampaign API server returned HTTP 500.

Besides these two deals, there are no other deals with extreme large numbers of activities.

Suggested solutions are:

• Reach out to ActiveCampaign Support to request a bug fix on there server to better handle deals with these numbers of connection emails.
• Reach out to ActiveCampaign Support to remove all unnecessary connection_email activities from these two tasks.
• Archive and delete the two deals.

[smw66]

Removing the two deals has not worked.
When refreshing the data, the powerbi error keeps existing. Bridge Online Monitoring does not show an error at the moment.

[forums]

It is recommended to execute the other suggested solutions.

[smw66]

The filter on emails when loading the data from the source is ignored when loading the data. This causes a data refresh to stop since it is too much data.
Active Campaign has provided an update on why this might be ignored.
Can someone help with checking the API URL?

This was their message:

The team provided suggestions on how to use the “DealActivities” endpoint, it it’s used following the suggestions below you shouldn’t encounter errors.
The Query Params described on the page only work with the URL path “api/3/dealActivities”:

These params won’t work with the URL path “api/3/deals/{Deal_Id}/dealActivities.” The latter one can still be used without any query params.
The param “exclude” only accepts “email”, “connection_email”:

, or “LinkData”, and no additional values can be passed to it. For example, you might use it in this API URL “api/3/dealActivities?exclude=email”. In this case, it will exclude some types from the results.
The param “filters[data_type]” is used to get only activities of a given type:

For example, filters[data_type]=note to get only Notes.

I understand that you aren’t making API calls directly. Can you check the API URL taking into consideration the recommendations above with the third party?
Let me know if there’s something unclear about that.

[forums]

There are currently no plans to further look into this issue given the outcome of the analysis.

Suggested solutions are:

• Reach out to ActiveCampaign Support to request a bug fix on there server to better handle deals with these numbers of connection emails.
• Reach out to ActiveCampaign Support to remove all unnecessary connection_email activities from these two tasks.
• Archive and delete the two deals.

The third one provided no resolution, so it is recommended to try to first two suggested solutions.

[forums]

This question was automatically closed after at least 1 week of inactivity after a possible solution was provided. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.

[forums]

It was found that there are more tasks with over 40,000 activities each of type “connection_email” using the following script:

declare
  l_txt varchar2;
begin
  for i in 2000..2099
  loop
    insert into NativePlatformScalarRequests@acn
    ( url
    , orig_system_reference
    ) 
    values 
    ( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=1&offset=' || i
    , to_char(i)
    )
    ;
    select listagg('| #' || i || ' | ' || d || '|' || c || ' |', chr(13))
    into   l_txt
    from   ( select dataType d
             ,      count(*) c
             from   ( select j.*
                      from   nativeplatformscalarrequests@acn t
                      join   jsontable
                             ( 'dealActivities[*]' 
                               passing t.result_text
                               columns d_id      varchar2 path 'd_id'
                               ,       id        varchar2 path 'id'
                               ,       dataType  varchar2 path 'dataType'
                               ,       d_stageid varchar2 path 'd_stageid'
                             ) j
                      where  t.orig_system_reference = to_char(i)
                    ) 
             group
             by     datatype
           )
    where  c > 2500
    ;
    dbms_output.put_line(l_txt);
  end loop;
end;

It seems approximately around the first time the problem occurred a new API was added “dealActivities”. This will be added since the smaller page size in terms of activities might resolve the issue.

[forums]

The next release of Invantive Cloud will include a new view DealActivitiesNonEmail which uses a new API of ActiveCampaign.

This view is able to handle tens of thousands of activity registrations for an individual deal, while still displaying acceptable performance. The average performance is above 10 times slower than the table DealWithActivities.

DealWithActivities remains available for backward compatibility.

[smw66]

When will the next release be available?

[forums]

Please refer to Release frequency Invantive Cloud for release information.

[smw66]

That doesn’t seem to answer my question. I’d like to know how I can check which version of the data I’m using, and whether that is the data from the new release.

[forums]

Please read the linked article carefully. It contains answers to both questions.