Add Phone Calls to existing script

[Media 33]

Hi,

I'm trying to add a column for 'PhoneCalls' to the below script but I don't know why it's not working.  

Our client has 30 conversions and 66 phone calls but the data coming through below is only capturing the 30 conversions.

Is there a way to add in a separate column to show 66 phone calls?

Thanks

/****************************************************/

 

var SPREADSHEET_URL = 'INSERT_SPREADSHEET_URL_HERE';

var SHEET_NAME = 'Source';

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

var sheet = spreadsheet.getSheetByName(SHEET_NAME);

 

 

function main() {

 

 

 sheet.getRange("A:H").clearContent();

 

var last5WeeksMonday = new Date();

var lastweekSunday = new Date();

 

// set to Monday of this week

 

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - (last5WeeksMonday.getDay() + 6) % 7);

lastweekSunday.setDate(lastweekSunday.getDate() - (lastweekSunday.getDay() + 6) % 7);

 

 

 

// set to previous Monday

 

last5WeeksMonday.setDate(last5WeeksMonday.getDate() - 35);

 

lastweekSunday.setDate(lastweekSunday.getDate() - 1);

 

var Last5WeeksMonday = Utilities.formatDate(last5WeeksMonday, 'PST', 'yyyyMMdd');

var LastweekSunday = Utilities.formatDate(lastweekSunday, 'PST', 'yyyyMMdd');

 

var d = new Date(); d.getDate(); var today = Utilities.formatDate(d, 'PST', 'yyyyMMdd');

 

sheet.getRange("A1").setValue("Start Date");

sheet.getRange("A2").setValue("End Date");

sheet.getRange("B1").setValue(Last5WeeksMonday);

sheet.getRange("B2").setValue(LastweekSunday);

sheet.getRange("A3").setValue("Frequency");

sheet.getRange("B3").setValue("Last Five Weeks");

sheet.getRange("A4").setValue("Last Execution");

sheet.getRange("A5").setValue("Copyright");

sheet.getRange("B5").setValue("https://optimizationup.com/");

sheet.getRange("B4").setValue(today);

sheet.getRange("A8").setValue("Week");

sheet.getRange("B8").setValue("Impressions");

sheet.getRange("C8").setValue("Clicks");

sheet.getRange("D8").setValue("Cost");

sheet.getRange("E8").setValue("Conversions");

sheet.getRange("F8").setValue("Revenue");

sheet.getRange("G8").setValue("Network");

sheet.getRange("H8").setValue("Device");

sheet.getRange("A8").setValue("Week");

 

 

var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, ConversionValue " +

 "FROM ACCOUNT_PERFORMANCE_REPORT " +

 "WHERE Impressions > 0 " +

 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 

 

 var rows = report.rows();

 while (rows.hasNext()) {

 var row = rows.next();

 

 

sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'], "All" ]);

 

 }

 

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, ConversionValue " +

 "FROM ACCOUNT_PERFORMANCE_REPORT " +

 "WHERE Impressions > 0 " +

 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 

 var rows = report.rows();

 while (rows.hasNext()) {

 var row = rows.next();

 

 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , "All" ]);

 

}

 

 var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, AdNetworkType1, Device, ConversionValue " +

 "FROM ACCOUNT_PERFORMANCE_REPORT " +

 "WHERE Impressions > 0 " +

 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 

 var rows = report.rows();

 while (rows.hasNext()) {

 var row = rows.next();

 

 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], row['AdNetworkType1'] , row['Device'] ]);

 

}

var report = AdWordsApp.report("SELECT Week, Clicks, Impressions, Ctr, AverageCpc, Cost, Conversions, Device, ConversionValue " +

 "FROM ACCOUNT_PERFORMANCE_REPORT " +

 "WHERE Impressions > 0 " +

 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 

 var rows = report.rows();

 while (rows.hasNext()) {

 var row = rows.next();

 

 sheet.appendRow([ row['Week'], row['Impressions'], row['Clicks'] , row['Cost'] , row['Conversions'],row['ConversionValue'], "All" , row['Device'] ]);

 

}

 

var report = AdWordsApp.report("SELECT Week, Clicks " +

 "FROM ACCOUNT_PERFORMANCE_REPORT " +

 "WHERE Impressions > 0 " +

 "DURING " + Last5WeeksMonday + ", " + LastweekSunday);

 

var rows = report.rows()

for (var row = 1; rows.hasNext(); row ++) {

 var rowx = rows.next();

 

 sheet.getRange("D1").setValue("The 5 Weeks Sorted ASC");

 sheet.getRange("E" + row).setValue(rowx['Week']);

 

 

}

 

 

var range = sheet.getRange("E1:F6");

range.sort(5);

}

[Hiroyuki Miyauchi (AdWords Scripts Team)]

Hello,

Upon checking the script you provided, you are using the Account Performance Report. As you can see in the documentation, phone call is not an available field. If you want to fetch phone call data, you may utilize Call Metrics Call Details Report. However, since the data is per call data level, you would need to consider how to accumulate the data to be the same level with Account Performance Report.

Please let me know if you have further clarifications.

Regards,

Hiroyuki

AdWords Scripts Team

[Syed Ahmed]

I trust this message finds you well. I am writing to seek your valuable expertise in creating a Google Ads Script that can efficiently fetch phone call data, encompassing metrics such as clicks, phone calls, and other pertinent conversion data.

Our team is focused on leveraging phone call metrics to better understand campaign performance and optimize our strategies accordingly. However, we lack the necessary knowledge and skills to script a solution that accurately retrieves this specific data.

Could you kindly provide guidance or support in developing a Google Ads Script tailored to fetch phone call-related metrics, including but not limited to clicks, phone call duration, conversion rates, and other relevant data points? Our aim is to generate a comprehensive report that consolidates these metrics for thorough analysis.

Additionally, I would greatly appreciate any assistance in understanding and utilizing the call_view to customize reports related to phone call data. If there are specific methods or best practices for accessing and interpreting data through call_view using Google Ads Script, your insights would be immensely beneficial to our efforts.

Thank you in advance for considering my request. Your expertise in this matter would significantly aid our team in optimizing our campaigns and deriving meaningful insights from phone call data.