Wednesday, March 20, 2013

Store Account Performance Report in a Google Doc

Day 2 and I'm still playing around with the new reporting API. I figured I'd post something that people started asking a little about from my previous post about Storing Account Level Quality Score which is storing all account level performance in a spreadsheet.

All you need to do is create a brand new Google Doc to store your data and paste that url into the script. The first time the script runs, it will create column headings. You can add, remove, or rearrange columns as you like by moving the column names around. You should reset your spreadsheet after doing this though.

Thanks,
Russ

/**************************************
* Store Account Performance Report in a Google Doc
* Version 1.1
* Changelog v1.1 - Removed apiVersion, Removed get spreadsheet
* Created By: Russ Savage
* FreeAdWordsScripts.com
**************************************/
function main() {
  var spreadsheet_url = "Your Spreadsheet Url Goes Here";
  var date_range = 'YESTERDAY';
  var columns = ['Date',
                 'AccountCurrencyCode',
                 'AccountDescriptiveName',
                 'AccountId',
                 'AccountTimeZoneId',
                 'CustomerDescriptiveName',
                 'ExternalCustomerId',
                 'PrimaryCompanyName',
                 'PrimaryUserLogin',
                 'Device',
                 'AverageCpc',
                 'AverageCpm',
                 'AveragePosition',
                 'Clicks',
                 'ConversionRate',
                 'ConversionRateManyPerClick',
                 'Conversions',
                 'ConversionsManyPerClick',
                 'ConversionValue',
                 'Cost',
                 'CostPerConversion',
                 'CostPerConversionManyPerClick',
                 'Ctr',
                 'Impressions',
                 'SearchBudgetLostImpressionShare',
                 'SearchExactMatchImpressionShare',
                 'SearchImpressionShare',
                 'SearchRankLostImpressionShare',
                 'ValuePerConversion',
                 'ValuePerConversionManyPerClick',
                 'ViewThroughConversions'];
  var columns_str = columns.join(',') + " ";
  
  var sheet = SpreadsheetApp.openByUrl(spreadsheet_url).getActiveSheet();
  if(sheet.getRange('A1:A1').getValues()[0][0] == "") {
    sheet.clear();
    sheet.appendRow(columns);
  }
  
  var report_iter = AdWordsApp.report(
    'SELECT ' + columns_str +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' +
    'DURING ' +date_range).rows();
  
  while(report_iter.hasNext()) {
    var row = report_iter.next();
    var row_array = [];
    for(var i in columns) {
       row_array.push(row[columns[i]]);
    }
    sheet.appendRow(row_array); 
  }
}

12 comments:

  1. i see computer, mobile and table populate...

    any way to get each of those 3 in their own sheet?

    ps - any way to post this data into a sheet that is outside the adwords account?

    ReplyDelete
  2. Hi Russel,

    How to use "CONTAINS" (or equivalent) in "SELECT" statement?

    Thanks,
    Ren

    ReplyDelete
    Replies
    1. Hi Renjith, Sorry for not getting back to you. You can find more information about writing AWQL queries here: The AdWords Query Language (AWQL).

      The short answer to your question is to add a WHERE clause just before the DURING in the query above. Then you put your conditions. For example, "WHERE CampaignName IN ['Campaign 1','Campaign 2']".

      Thanks,
      Russ

      Delete
  3. Nice article. I have read the whole article.It is nicely explained. People who are stuck in storing account level quality score should read this to clear their doubts. If you have any problem regarding Norton, please visit Norton Setup

    ReplyDelete
  4. I would try to make a report for this page https://essaywriters.site/professional-thesis-writer as an example given. Thanks!

    ReplyDelete
  5. You can likewise disclose to Google how regularly your pages are refreshed and the date that each page was last adjusted. For instance your landing page may be refreshed each day, while a specific item page may just be refreshed on a yearly premise. finance

    ReplyDelete
  6. I gotta favorite this website it seems very helpful . clenbuterol achat

    ReplyDelete
  7. I am looking for and I love to post a comment that "The content of your post is awesome" Great work! Agentur für Google Adwords

    ReplyDelete
  8. Quickbooks support is a tech help assistance provider for the issues in quickbooks like installation error, setup fails, login id fails, etc and in order to fix them you need to contact quickbooks support and they will provide you quickbooks customer support number and from there you will get Quickbooks support

    ReplyDelete
  9. QuickBooks support is a reliable accounting software that makes the workflow super easy for the accountants. With numerous automated features, it allows the users to save precious time. Many firms use the QuickBooks suppoort program to manage their accounts effectively. There are some issues that such as the code H202 and issues related to QuickBooks desktop, which can be fixed by connecting with QuickBooks desktop support. Quickbooks Support | Quickbooks Desktop Support

    another link
    office.com/setup | www.office.com/setup | office setup | office com setup

    ReplyDelete
  10. office.com/setup includes applications like PowerPoint, Outlook, to install on your device. Everything you need is to install office.com/setup. Every application is key to reduce the work. This software is made with the latest features so you can easily install it. You can easily make a presentation with this software.
    It gives a complete set of tools that you need to get the work done.
    office.com/setup | www.office.com/setup | office setup | office com setup


    another link:

    Quickbooks Support | Quickbooks Desktop Support

    ReplyDelete