Email connector for Google Ads Report

Ainash

Hi All! I tried to make Email connector for Google Ads Youtube report. The problem is that email that is coming from Google Ads doesn't have a straight link to report. It has button 'View Report' and when you click on it, it goes to google ads report and starts downloading report. So, I guess Email connector can't recognize this button. Is there any way to solve this problem? Or should I look for other options how to automate pulling this report to DOMO?

Thanks for your advices!

Ainash

Decided to share my way to choose this problem here. If somebody will have a problem with pulling data from Google Ads Youtube. I ended up writing a script in Google Ads that pushes data to the GoogleSheets and connected that sheet to DOMO.

Here is my script:

var TIME_PERIOD = "LAST_30_DAYS" // THIS_MONTH, LAST_MONTH, LAST_30_DAYS

//var EMAIL_ADDRESS = "paste_email_here" // uncomment if you want to send emails when scripting will end

function main(){

  //Step 1: Connect Google Ads to the Google Sheet

    var spreadsheetUrl = 'paste_url_address_here';

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  var ss = spreadsheet.getSheetByName('Sheet1');

  ss.clear()

  //Step 2: Collect all column names needed. You can find all available columns here https://developers.google.com/adwords/api/docs/appendix/reports/video-performance-report

  columns = ["Date", "AccountDescriptiveName", "ExternalCustomerId", "CampaignId", "CampaignName", "AdGroupId", "AdGroupName", "CreativeId", "VideoId", "VideoTitle", "AccountCurrencyCode", "Clicks", "Impressions", "Cost", "Conversions", "ViewThroughConversions", "VideoQuartile25Rate", "VideoQuartile50Rate", "VideoQuartile75Rate", "VideoQuartile100Rate", "VideoViews"];

  //Step 3: Create an array to store the data

  var sheetarray = [columns];

   

  //query to extract report

  columns_string = columns.join(", ") // CampaignName, AdGroupName

  var awql = "SELECT " + columns_string + " FROM VIDEO_PERFORMANCE_REPORT DURING " + TIME_PERIOD;

  

  //creating report

  var report = AdsApp.report(awql).rows();

  //add report into an array

  var report_data = [];

  while(report.hasNext()){

   row = report.next(); 

   metrics = [];

   for(i = 0; i < columns.length; i++){

    metrics.push(row[columns[i]]);

   } 

   report_data.push(metrics);

  }

  //sort the data

  report_data.sort(function(a, b) {

    return b[4] - a[4];

  });

  // Step 5: push all data to sheetarray

  for (i = 0; i < report_data.length; i++){

   sheetarray.push(report_data[i]);

  }

   

  //Step 6: Display the contents of the array

  Logger.log(sheetarray);

  if (sheetarray.length > 0) {

   

   // Step 6: Send the array's data to the Google Sheet

   ss.getRange(1, 1, sheetarray.length, sheetarray[0].length).setValues(sheetarray);

    

   // Step 7: Send email with link to Google Sheet. Uncomment if you want to send emails.

   //MailApp.sendEmail(EMAIL_ADDRESS, "Video Performance Report", "Here's the link: "+spreadsheetUrl);

  }

  

   

 }

amehdad

Hi @Ainash, on your 2nd question, have a read of this knowledge article: https://domohelp.domo.com/hc/en-us/articles/360060270674-Google-Ads-Connector#4.0.1.

Ainash

@amehdad yes, thank you! I was thinking for my next step to export google ads data to google sheets using scripting. Just thought, maybe there is a way how to solve the button problem in Email Connector.

GrantSmith

Hi @Ainash

I've tried using the Google Sheets method but it's hard to know if / when the data is finished importing into google sheets to then have Domo pick up the correct data. I ended up utilizing the pydomo SDK and the Google Ads python SDK to query Google Ads, process my data and then upload it into Domo via their API / SDK. It's a bit more of a technical solution but one that I've found works well to make sure the entire pipeline flows properly.

Ainash

@GrantSmith I wish I knew how to do that😁 but let me try the google ads script first, if that's not going to work, then I'll go with Python route. I tried to set up Pydomo on my computer, but I had some issues, but didn't have a chance to dig into the problem yet.

Thanks!

Ainash

Decided to share my way to choose this problem here. If somebody will have a problem with pulling data from Google Ads Youtube. I ended up writing a script in Google Ads that pushes data to the GoogleSheets and connected that sheet to DOMO.

Here is my script:

var TIME_PERIOD = "LAST_30_DAYS" // THIS_MONTH, LAST_MONTH, LAST_30_DAYS

//var EMAIL_ADDRESS = "paste_email_here" // uncomment if you want to send emails when scripting will end

function main(){

  //Step 1: Connect Google Ads to the Google Sheet

    var spreadsheetUrl = 'paste_url_address_here';

  var spreadsheet = SpreadsheetApp.openByUrl(spreadsheetUrl);

  var ss = spreadsheet.getSheetByName('Sheet1');

  ss.clear()

  //Step 2: Collect all column names needed. You can find all available columns here https://developers.google.com/adwords/api/docs/appendix/reports/video-performance-report

  columns = ["Date", "AccountDescriptiveName", "ExternalCustomerId", "CampaignId", "CampaignName", "AdGroupId", "AdGroupName", "CreativeId", "VideoId", "VideoTitle", "AccountCurrencyCode", "Clicks", "Impressions", "Cost", "Conversions", "ViewThroughConversions", "VideoQuartile25Rate", "VideoQuartile50Rate", "VideoQuartile75Rate", "VideoQuartile100Rate", "VideoViews"];

  //Step 3: Create an array to store the data

  var sheetarray = [columns];

   

  //query to extract report

  columns_string = columns.join(", ") // CampaignName, AdGroupName

  var awql = "SELECT " + columns_string + " FROM VIDEO_PERFORMANCE_REPORT DURING " + TIME_PERIOD;

  

  //creating report

  var report = AdsApp.report(awql).rows();

  //add report into an array

  var report_data = [];

  while(report.hasNext()){

   row = report.next(); 

   metrics = [];

   for(i = 0; i < columns.length; i++){

    metrics.push(row[columns[i]]);

   } 

   report_data.push(metrics);

  }

  //sort the data

  report_data.sort(function(a, b) {

    return b[4] - a[4];

  });

  // Step 5: push all data to sheetarray

  for (i = 0; i < report_data.length; i++){

   sheetarray.push(report_data[i]);

  }

   

  //Step 6: Display the contents of the array

  Logger.log(sheetarray);

  if (sheetarray.length > 0) {

   

   // Step 6: Send the array's data to the Google Sheet

   ss.getRange(1, 1, sheetarray.length, sheetarray[0].length).setValues(sheetarray);

    

   // Step 7: Send email with link to Google Sheet. Uncomment if you want to send emails.

   //MailApp.sendEmail(EMAIL_ADDRESS, "Video Performance Report", "Here's the link: "+spreadsheetUrl);

  }

  

   

 }