Email connector for Google Ads Report

Ainash · 2021-10-27T17:56:50+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Connectors

Accepted answers

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

}

All comments

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

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

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

}