Long Runtimes - Keywords Performance Report takes too long

[Itay Meir]

I'm having trouble with long runtimes with my Keywords Performance Reports...
I'm running 5 queries for each campaign in my account.
And sometimes my first query (query3) is taking around one minute to complete.

This is strange because sometimes it takes less than a second.
It seems to take longer on the same campaigns but I can't get as for why.
The campaigns that take longer has similar amount of keywords to the campaigns that take less than a second...
And also, the other queries (query7, query14, query28, query56) of the campaigns that took longer with query3, takes less then a second again like nothing is wrong.
So I don't think amount of keywords is the reason for the runtime...

var query3 = "SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = " + campaign.getId() + " DURING " + dateRange3days;
var result3 = AdWordsApp.report(query3); //////////////////////////////////////////////////////////////////////// !!!TOO MUCH TIME!!! ////////////////////////////////////////////////////////////////////////////////////////////

var query7 = "SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = " + campaign.getId() + " DURING " + dateRange7days;
var result7 = AdWordsApp.report(query7);

var query14 = "SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = " + campaign.getId() + " DURING " + dateRange14days;
var result14 = AdWordsApp.report(query14);

var query28 = "SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = " + campaign.getId() + " DURING " + dateRange28days;
var result28 = AdWordsApp.report(query28);

var query56 = "SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = " + campaign.getId() + " DURING " + dateRange56days;
var result56 = AdWordsApp.report(query56);

Does anyone else came across a problem like this?

Also, I've tries to give those queries a list of campaign IDs to retrieve data from (So I could run the 5 queries once for all campaigns instead of once for each campaign).
But I couldn't make it work with a list.
Does anyone know how to make Keywords Performance Reports to work with campaignIds List?

Thanks in advance,

Itay.

[Google Ads Scripts Forum Advisor]

Hi Itay,

Can you please provide your CID and script name so we can investigate directly in your account?

Thanks,
Matt 
Google Ads Scripts Team

ref:_00D1U1174p._5001USwscf:ref

[Itay Meir]

Hi Matt,

Just sent all of the information you asked for in a DM.

I'm also having trouble checking in my queries for script label on the keyword's parent adGroup.

I've put a label on all of the adGroups the script should run on but I can't find a way to make the keywords performance report check for label on the parent adGroup..

Thanks,
Itay.

[Google Ads Scripts Forum Advisor]

Hi Itay,

Thanks for sending the additional information.

Since these four queries are the same except for the date range variable, first I would ensure that the proper date range is getting pulled each time. You can use log statements to print out that variable before/after the query is run. Perhaps it is taking a bit longer because it is the first report call. 

If you have specific dates and times that the script was run where you saw this discrepancy, this can help us take a closer look.

Regards,

[Itay Meir]

Hi Matt,

I didn't run the script yet. I am running it on preview because I don't want it to change anything until I know it works fine.

But when previewing, it happens everytime, to the same campaigns, with the same query (query3).

That means that it doesn't happen only at specific dates or times. and doesn't happen only on the first report call.

The first campaign it happens on is usually the third one. Then the next one is the fifth one. It seems like starting from the third campaign, every second campaign is taking longer with query3..

I can give you a list of the campaigns that the query3 is getting stuck on if you want me to.

Below are the queries in a non problematic campaign and in a problematic campaign (The first five are in a non problematic campaign and the other five are in a problematic one):

1/23/2020 9:20:51 AM     [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 202095195 DURING 20200119,20200121

1/23/2020 9:20:52 AM    [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 202095195 DURING 20200115,20200121

1/23/2020 9:20:53 AM    [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 202095195 DURING 20200108,20200121

1/23/2020 9:20:54 AM    [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 202095195 DURING 20191225,20200121

1/23/2020 9:20:54 AM    [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 202095195 DURING 20191127,20200121

1/23/2020 9:26:41 AM [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 246986475 DURING 20200119,20200121

1/23/2020 9:27:01 AM [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 246986475 DURING 20200115,20200121

1/23/2020 9:27:01 AM [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 246986475 DURING 20200108,20200121

1/23/2020 9:27:02 AM [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 246986475 DURING 20191225,20200121

1/23/2020 9:27:02 AM [NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 246986475 DURING 20191127,20200121

As you can see in the timestamps, for some reason the problematic campaigns took less time when I added the log lines for the queries (around 15-20 second). But they are still taking too long and bumping up the runtime.

I'm adding Below my testing logs to show the amount of time it took for the queries without the log lines for the queries text (Again, the first script quete is for a non problematic campaign and the second one is for a problematic campaign):

1/23/2020 9:50:27 AM	[INFO]: Start of Query3
1/23/2020 9:50:27 AM	[NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 203754435 DURING 20200119,20200121
1/23/2020 9:50:27 AM	[INFO]: Start of Rows3
1/23/2020 9:50:28 AM	[INFO]: Start of Query7
1/23/2020 9:50:28 AM	[INFO]: Start of Query14
1/23/2020 9:50:29 AM	[INFO]: Start of Query28
1/23/2020 9:50:32 AM	[INFO]: Start of Query56

1/23/2020 9:50:36 AM	[INFO]: Start of Query3
1/23/2020 9:50:36 AM	[NOTICE]: SELECT Id, AdGroupId, SearchImpressionShare, AllConversionValue, Cost, Clicks, Impressions FROM KEYWORDS_PERFORMANCE_REPORT WHERE Status = ENABLED AND AdGroupStatus = ENABLED AND CampaignId = 203754675 DURING 20200119,20200121
1/23/2020 9:51:24 AM	[INFO]: Start of Rows3
1/23/2020 9:51:24 AM	[INFO]: Start of Query7
1/23/2020 9:51:25 AM	[INFO]: Start of Query14
1/23/2020 9:51:26 AM	[INFO]: Start of Query28
1/23/2020 9:51:26 AM	[INFO]: Start of Query56

Thanks,

Itay.

[Google Ads Scripts Forum Advisor]

Hello,

If you re-order the queries, I wonder if the delays occur on the same queries, or if the delays occur at the same number query. For example, if you swap queries 2 and 3, would the delay still happen on the 3rd query to be issued to scripts, or would it happen on the 2nd query now, because that date range is problematic for some reason.

My guess would be that in that case, the 3rd query to be issued (formerly the 2nd query) would still be the one that is delayed, because this is probably some sort of rate limiting on the back end to keep you from issuing too many report requests in too quick succession.

Also, the results probably wouldn't be perfectly consistent in the case of rate limiting, as server load is taken into account when these kinds of situations arise.

Regards,
Mike, Google Ads Scripts Team

ref:_00D1U1174p._5001USwscf:ref

[Itay Meir]

Hi Mike,

I've tried swapping query3 and query7. Now instead of query3 taking almost one minute, query7 does...
It seems to me that every first query for some campaigns is taking longer.

I also noticed it's not really like I said in my previous post:

It seems like starting from the third campaign, every second campaign is taking longer with query3..

It's actually not every second campaign. Sometimes it's two campaigns in a row and sometimes there's two campaigns between the problematic campaigns. But it looks to be happening to the same campaigns each time.

Itay.

[Itay Meir]

Hi Mike and Matt,

Do you by any chance have a new insight regarding my problems?

Itay.

[Google Ads Scripts Forum Advisor]

Hello,

Thanks for investigating. Before I escalate internally for someone to investigate in more detail, I want to make sure that I have the problem clearly defined so that we can target our troubleshooting accurately. Can you provide 2 specific queries that always take longer than you expect, and 2 specific queries that always execute on par with your expectations? By having you provide these explicitly we avoid any potential confusion that might arise if I try to extract them from your script based on our contextual discussion.

[Itay Meir]

Hello,

After farther investigation I've noticed there is no specific queries that thake longer.

It randomly happens once every 10-15 queries.

Do you have any idea as of why?

Itay.

[Google Ads Scripts Forum Advisor]

Hello,

If that's the case, then it's likely just rate limiting. You can only fetch data so quickly from the backend. If you are going too fast, the backend will return a rate exceeded error, and the client will have to wait an allotted amount of time before retrying. Scripts handles all of this automatically for you, with the side effect that sometimes queries will appear to take much longer for no apparent reason. In reality, the reason is that you are making many queries in succession and the backend is enforcing a wait to ensure that service is not interrupted for any other clients using it at the same time.