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

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!

Best Answer

Answers

  • 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
    Bulloko Domo Employee

    @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
    kshah008 Contributor

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

  • gyosh
    gyosh Member

    @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
    kshah008 Contributor

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

  • 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
    Bulloko Domo Employee

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

  • @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
    Bulloko Domo Employee

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

  • @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
    Bulloko Domo Employee

    @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
    Togna_Bologna Domo Employee

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

  • Togna_Bologna
    Togna_Bologna Domo Employee

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

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

  • @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
    Togna_Bologna Domo Employee

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

  • 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
    Togna_Bologna Domo Employee

    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.

  • This worked well. I had to add a "column type" transform but the reports are working as expected!
  • Togna_Bologna
    Togna_Bologna Domo Employee
    Answer ✓
    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
  • Also see this thread on the same topic.

  • Kurbz
    Kurbz Member

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

     

    domo-adword-issue.png

     

    Any help? or screenshots to assist?

    Many Thanks.

  • Kurbz
    Kurbz Member

    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.