Email connector for Google Ads Report

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!

Tagged:

Best Answer

  • Ainash
    Ainash Member
    Answer ✓

    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);

      }

      


       

     }

Answers

  • @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.

  • 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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @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
    Ainash Member
    Answer ✓

    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);

      }

      


       

     }