Pmax data script

[Lucia Brítez]

Hello!!

I was following this tutorial to obtain Pmax information but the information in the spreadsheet does not completely impact me. Could you help me? I send you the step by step and the script.

What should I change?

Import script into account.
Make a copy of the spreadsheet (column names and tabs are most important)
Copy the new spreadsheet URL in the script
Adjust N=14 (number of days) if you like.
Run the script.
Note: you will see a difference between campaign level data and what the script can fetch. This is because until now it is not possible to retrieve keyword/search and other data, such as Sponsored Gmail, Discovery, etc.

SCRIPT

/****************************
 * Created By: Jermaya Leijen (https://partout.nl/)
 ****************************/

function main() {
  const count_days=14  
  const sheet_url="https://docs.google.com/spreadsheets/d/1wuWPouMr0bc7LLscEB44sCea7h5G-WBH0Yvg_awlvNQ/edit#gid=0"

  const end=new Date(Date.now() - 86400000)    
  const start=new Date(Date.now() - count_days*86400000)  
 
  let sh
  let searchResults

  const ss=SpreadsheetApp.openByUrl(sheet_url)
 
  let data=[]  
  searchResults = AdsApp.search( `SELECT  segments.day_of_week, segments.hour, metrics.conversions, metrics.conversions_value, metrics.clicks, campaign.id,
  metrics.cost_micros, metrics.impressions  FROM campaign
  WHERE campaign.advertising_channel_type = PERFORMANCE_MAX AND segments.date>='${date_format(start)}' AND segments.date <='${date_format(end)}' `)
  // ORDER BY segments.day_of_week ASC`
  for (const row of searchResults) {
    //Logger.log(row)
    data.push([row.segments.dayOfWeek,row.segments.hour, row.campaign.id, row.metrics.impressions,row.metrics.clicks,row.metrics.conversions,row.metrics.conversionsValue,(parseInt(row.metrics.costMicros)/1000000).toFixed(2)])
  }
  sh=ss.getSheetByName("day of the week - hour")
  if (sh.getMaxRows()>1) sh.getRange(2,1,sh.getMaxRows(),data[0].length).clear()
  sh.getRange(2,1,data.length, data[0].length).setValues(data)
 
  data=[]
  searchResults = AdsApp.search( `SELECT  segments.product_item_id, campaign.advertising_channel_type, metrics.conversions, metrics.conversions_value, metrics.clicks,
  metrics.cost_micros, metrics.impressions  FROM shopping_performance_view
  WHERE campaign.advertising_channel_type = PERFORMANCE_MAX AND segments.date>='${date_format(start)}' AND segments.date <='${date_format(end)}' `)
  // ORDER BY segments.day_of_week DESC, segments.hour ASC

  for (const row of searchResults) {
    //Logger.log(row)
    data.push([row.segments.productItemId,row.metrics.impressions,row.metrics.clicks,row.metrics.conversions,row.metrics.conversionsValue,(parseInt(row.metrics.costMicros)/1000000).toFixed(2)])
  }
  sh=ss.getSheetByName("Products ID")
  if (sh.getMaxRows()>1) sh.getRange(2,1,sh.getMaxRows(),data[0].length).clear()
  sh.getRange(2,1,data.length, data[0].length).setValues(data);
 
  data=[]
  searchResults = AdsApp.search( `SELECT  asset_group.name,asset_group_listing_group_filter.case_value.product_type.level, asset_group.id,asset_group_listing_group_filter.case_value.product_item_id.value, campaign.advertising_channel_type, metrics.conversions, metrics.conversions_value, metrics.clicks,
  metrics.cost_micros, metrics.impressions,asset_group_listing_group_filter.parent_listing_group_filter, campaign.name  FROM asset_group_product_group_view
  WHERE  campaign.advertising_channel_type = PERFORMANCE_MAX AND segments.date>='${date_format(start)}' AND segments.date <='${date_format(end)}' `)
  // ORDER BY segments.day_of_week DESC, segments.hour ASC
 
  for (const row of searchResults) {
    //Logger.log(row)
    if (row.assetGroupListingGroupFilter.parentListingGroupFilter!=null) continue
   // if (row.assetGroupListingGroupFilter.caseValue&&row.assetGroupListingGroupFilter.caseValue.productItemId.value) pid=row.assetGroupListingGroupFilter.caseValue.productItemId.value; else pid=""
   // if (row.assetGroupListingGroupFilter.caseValue&&row.assetGroupListingGroupFilter.caseValue.productType) pid2=row.assetGroupListingGroupFilter.caseValue.productType.level; else pid2=""
   
   
    data.push([row.assetGroup.name,row.assetGroup.id,row.campaign.name,row.metrics.impressions,row.metrics.clicks,row.metrics.conversions,row.metrics.conversionsValue,(parseInt(row.metrics.costMicros)/1000000).toFixed(2)])
  }
  sh=ss.getSheetByName("Asset Group")
  if (sh.getMaxRows()>1) sh.getRange(2,1,sh.getMaxRows(),data[0].length).clear()
  sh.getRange(2,1,data.length, data[0].length).setValues(data);
 
}

function date_format(date){
return Utilities.formatDate(date, AdsApp.currentAccount().getTimeZone(), 'yyyy-MM-dd')
}

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for reaching out to the Google Ads Scripts support team.

By reviewing your query, I understand that you're encountered with an error while executing the scripts. Please provide the below details for further investigation.

• Google Ads account ID / CID
• Name of the script (Tools and Settings > Bulk Actions > Scripts)
• Screenshot of the issue

This message is in relation to case "ref:!00D1U01174p.!5004Q02qA6to:ref"

Thanks,
 

Google Ads Scripts Team

[Lucia Brítez]

Hi! Good.

ID: 500-294-1225

NAME:pmax data

SCREENSHOT: 

[Nils Rooijmans]

looks like the GAQL query that tries to get data from 'shopping_performance_view' did not return any results.

Could it be you are running the script in an account that does not have product feed based PMax campaigns ?

Hope this helps,

Nils Rooijmans
https://nilsrooijmans.com
See my Google Ads Scripts FAQ to avoid the same mistakes I made: https://nilsrooijmans.com/google-ads-scripts-faq/

[Google Ads Scripts Forum Advisor]

Hi,

Thank you for getting back to us.

After reviewing your concern, I understand that you are getting "cannot read properties error" that indicates your script is trying to access a property of a variable that doesn't have any values assigned to it. I would like to inform you that I have checked your account and I did not find any script with the name “pmax data”. Could you please confirm the account Id and the name of the script?