How do you get Google Adwords data into Domo to use with the Google Adwords Keywords Performance App

gyosh

I want to test out the Google Adwords Keywords Performance App, but I can't figure out how to pull in the Google Adwords data that Domo outlines here: https://developer.domo.com/docs/installation-guides/google-adwords-keywords-performance

 

I don't see any connector for it, and I don't see it in my Google Analytics connector. Has anyone successfully used this app or pulled in Adwords data?

 

Thank you in advance!

Togna_Bologna

In the Knowledge Base you can now find the new and improved guide to getting AdWords data in Domo: http://knowledge.domo.com?cid=googleadwords

gwsv-it

We pull in Google AdWords data using a short Google Apps script to export the numbers to a Google Sheet daily, then share that sheet with a Google Sheets user on our Domo instance. 

 

I was on the clock when I wrote the Google Apps script, otherwise I would just share the source code with you. However, if you're familiar with JavaScript, then Google's documentation should be more than adequate to figure it out. Here's the gist of how ours works:

 

1) Create a campaign iterator (AdWordsApp.campaigns function)

2) Loop through each campaign and use the campaign.getStatsFor function to grab the stats for the date range you're interested in (yesterday, in our case)

3) Store these stats in an object or array

4) Open the shared spreadsheet using SpreadsheetApp.openById

5) Append each of the stats you stored in steps 2-3 to the spreadsheet 

 

 

Note that we don't pull in keyword performance stats, just general campaign performance stats. You should be able to expand upon the script outline above to request keyword stats to throw into your spreadsheet.

 

Hope that helps!

Bulloko

@gwsv-it - is correct. Going through and using a script to push said data into Google Sheets and then pulling it into Domo is how we bring the data in currently. It does require a little scripting knowledge / skill, but it isn't that bad.

 

 

 

Here is a "base" script I have found much success with - 

 

 

function main() {
var REPORT_NAME = '[report name]';
var REPORT_TYPE = '[report type value]';
var FILTER = '[filter condition]';
var DATE_RANGE = '[predefined date range]';
var COLUMN_NAMES = [
[column names]
];
var COLUMNS = COLUMN_NAMES.join(',');  
var REPORT = AdWordsApp.report(
'SELECT ' + COLUMNS +
' FROM ' + REPORT_TYPE +
' WHERE Status IN [[status values]]' +
' AND ' + FILTER + 
' DURING ' + DATE_RANGE
);
if(DriveApp.getFilesByName(REPORT_NAME).hasNext()){
Logger.log("Overwriting the existing report");
var file = DriveApp.getFilesByName(REPORT_NAME).next();
var spreadsheet = SpreadsheetApp.openByUrl(file.getUrl());
} else {
Logger.log("Creating a new report");
var spreadsheet = SpreadsheetApp.create(REPORT_NAME);
}
var sheet = spreadsheet.getActiveSheet();
REPORT.exportToSheet(sheet);
}

 

 

I tried to highlight the parts in RED you are going to need to change.

 

I also want to point out a couple other points -

 

 

var REPORT = AdWordsApp.report(
'SELECT ' + COLUMNS +
' FROM ' + REPORT_TYPE +
' WHERE Status IN [[status values]]' +
' AND ' + FILTER + 
' DURING ' + DATE_RANGE
);

 This is a common QL statement that you can edit. Perhaps you want want that WHERE clause and you want to remove the filter you are using, etc.

 

This script will generate said report and push it into Google Sheets for you. You will find it there under the name you gave for the REPORT_NAME.

 

This is also going to overwrite the file that is in there the next time it runs. Why? So your Drive doesn't get filled up by a new report each day.

 

 

NOTE - This is for Google Adwords accounts, some changes may need to be made to use this with an MCC account as there are different functions there.

 

NOTE 2 - Your mileage may very. This is just something I have found success with.

kshah008

@gyosh, did any of the above replies help you out? 

gyosh

@kshah008 - yes it did. I haven't tried the solutions yet, it might be beyond my expertise, but I'll try. However, the replies did help me reallize I'm not missing something easy. I might have to wait for a connector to be built if I can't get the Google Sheets method to work. 

 

Thanks all!

kshah008

@gyosh, great! Please mark the post that best helped you out by clicking "Accept as solution." 

Zach_Shearer

Where are you appending this data in Domo? I have my AdWords script all ready to go but I'm not sure how to aggregate the data in Domo. 

Bulloko

@Zach_Shearer - The script I posted pushes the data to Google Sheets. Sheets has a 2million cell limit - so appending there would quickly cause issues.

 

I would then use the Google Sheets connector in Domo and set that to APPEND. Now - take note on the Date range you're using. If your script is pulling the past 7 days and you APPEND that daily in Domo - you will have some duplicates to compensate for.

Zach_Shearer

@Bulloko Thanks for your help. I think I am almost there. I have my scripts setup in AdWords. The Google Sheets are updated each morning at 7AM with data from the prior day. I am building a dataflow in AdWords to combine the cost from several AdWords accounts into one dataflow. It ran successfully both today and yesterday but when it ran today, the data from yesterday was replaced.  What is my next step? See attached for my ETL setup. 

 

 

 

 

Bulloko

@Zach_Shearer - The three inputs - are they all set to "APPEND"? So outside of the ETL - on the DataSet itself.

Zach_Shearer

@Bulloko

 

Not as far as I can tell. I've checked the Google Sheets data source in datacenter and I don't see any option for append.

Bulloko

@Zach_Shearer - You are absolutley right. The Google Sheets connector does not append.

 

We need to make the ETL Append. To do this - we need to make it a recrusive ETL. Or in other words the ETL has to call itself and append itself.

 

That is usually done but creating the ETL, running it once, and then editing the ETL to add in the output as an input.

 

@Togna_Bologna - I believe you have some experience in this, right?

Togna_Bologna

@Zach_Shearer, I am working on a guide/walk-through for this. I will send it your way shortly.

Zach_Shearer

Thanks!

 

@Togna_Bologna and @Bulloko

Togna_Bologna

@Zach_Shearer,

Your DataFlow looks great. Let me explain at a high-level what you want to do now:

1. Add another input dataset tile.
2. Pick the output dataset from this same DataFlow to be the new input dataset.
3. Delete the rows/records from the recurisve (DataFlow output data) dataset where the dates match those of the new input sheets.
   Let me explain: If your sheets are pulling in data for the last week and we append that data every day, on the second day, the last six days of data will be duplicated. Eventually every row of data would be there 7x. So, we use a join to compare the dates, then delete the overlapping data from the data already in Domo, giving preference to the newly incoming data--in case AdWords made any retroactive corrections.
4. Combine the new data with the data already in Domo.

You can see how this is done on steps 8 and 9 of this guide: https://domo.box.com/s/yafm7j1df8v29wed78dbt7ch7abokjw2 

 

The guide is still being perfected, so let me know if it is at all confusing. If you find that this is a little to convoluted over text, open a support ticket and ask for Tony. Then we can do it together over a screen-share.

Zach_Shearer

 @Togna_Bologna

 

Thanks for taking a look at this for me. I'll step through your instructions and try to get it updated. One note, your link to the Box.com document sent me to a 404. MAybe the link has expired or something? 

Zach

Zach_Shearer

@Togna_Bologna

 

I'm really sorry about all of the trouble. I'm afraid that, without the document in the Box.com folder, I'm finding this setup a little confusing. How does one open a support ticket?

 

Zach Shearer

Togna_Bologna

@Zach_Shearer, it's no problem at all. The link seems to work only if you copy and paste it, not click it. I can't see why, but that's what I'm finding.

 

To contact support:

Use @DomoSupport in Buzz or email support@domo.com

Just mention this post, that I said to submit a ticket for me (Tony), and provide a link to your DataFlow.

Zach_Shearer

I have this setup and working. Thanks for working through this with me. The next thing I need to figure out how to do is create calculated attributes, e.g. =Year(day), =Month(day), etc. I'll start looking into that. If you have a recommendation, please send it my way via PM.

You guys have been a great help!

Togna_Bologna

Zach_Shearer,

 

The DOJO doesn't have a PM option, that I know of. You should be able to use the Date Format tile in the ETL tool to create new Year and Month columns. If you want to skip that step, the card builder also contains optionss to group the data by Month or Year.

Zach_Shearer

This worked well. I had to add a "column type" transform but the reports are working as expected!

Togna_Bologna

In the Knowledge Base you can now find the new and improved guide to getting AdWords data in Domo: http://knowledge.domo.com?cid=googleadwords

[Deleted User]

Also see this thread on the same topic.

Kurbz

Hi folks,

The 'Google AdWords Keywords Performance' App for Domo is requiring 2 columns that appear to be depriciated.

 

Converted Clicks (is now Conversions)

Click Conversion Rate (is now Conversion Rate)

 

Additionally, the app requires a different column called 'Conversions'.

 

I'm confused, when I complete the 'How to get data into Domo' for this app, these 2 columns are missing.

Is the process for the data or the app using depriciated columns?

For instance, I can run the report for historic data in AdWords and select Converted Clicks, however when attempting to bring this column into the Recent Data report the column dosnt exist...

 

 

Any help? or screenshots to assist?

Many Thanks.

Kurbz

Hi,

Yes the date range.

We are scheduling the daily report every day, yet the data range is 7 days.

Should we be adding a remove duplicate ETL to the flow?

 

its ok i figure this out.

 

Rgds,

K.