SEA CAMP Jena 2018 Adwords Script, Big Query & Data Studio

Setup Common Pitfall

Big Query Einrichten

Transfer Service Einrichten

AdWords Script einrichten

  • In Adwords Scripts die erweiterte API für die Big Query aktivieren


  • Die Big Query in der Google Developer Console Aktiveren


  • Das AdWords Script in AdWords unter Scripte einfügen.
  • Projekt-Id und Dataset-Id aus der Big Query im Script eintragen.
  • Script autorisieren und einmalig ausführen. Der Account in dem das Script ausgeführt wird, muss der gleiche sein wie der der im Transfer-Service hinterlegt ist.

 

Data Studio Verknüpfen

  • Datenquellen in der Reihenfolge wie im Bild ersetzen
  • Neue Datenquelle erstellen.
  • Als Connector die Big Query auswählen.
  • Als Datenquelle die rot markierten Quellen auswählen



Adwords Script Code

// --------- SETTINGS ----------------------
var BIGQUERY = {
  // project id in bigquery
  PROJECT_ID : 'enter you project id here',
  // Id of the dataset in bigquery which contains tables and views created by AdWords Transfer Service.
  DATASET_ID : 'enter you dataset id here',
};
// This script creates three Views in the same dataset which is used by AdWords Transfer Service.
// - 2duplicate_conversion_tracker
// - 2replaceable_negative_keywords
// - 2broad_modifier_errors
// Set REPLACE_EXISTING_VIEWS to true to recreate the three views each time this script is executed.
// If it is set to false then exisiting views are not replaced.
var REPLACE_EXISTING_VIEWS = true;

// AdWords Transfer Service creates about 200 Views and Tables. 
// This prefix helps to group our three views and position them at the top of the alphabetically ordered list of Views/Tables.
var VIEW_PREFIX = '2';


// -------- CONSTANTS ----------------------
var SCRIPT_NAME = 'BigqueryViewsCreator';

var ACCOUNT_ID = AdWordsApp.currentAccount().getCustomerId().split( '-' ).join( '' );

var VIEWS = {
  DUPLICATE_CONVERSION_TRACKER : [
    '#StandardSQL',
    'SELECT',
    '  AccountName,',
    '  STRING_AGG( ConversionTypeName , \', \' ) as conversion_tracker_ids,',
    '  count(*) as count,',
    '  conversions,',
    '  value',
    'FROM (',
    '  SELECT',
    '    ifnull( CustomerDescriptiveName,  AccountDescriptiveName ) as AccountName,',
    '    ConversionTypeName,',
    '    sum( Conversions ) as conversions,',
    '    sum( ConversionValue ) as value',
    '  FROM `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.CriteriaConversionStats_' + ACCOUNT_ID + '` as k',
    '  JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Customer_' + ACCOUNT_ID + '` as customer ON customer.ExternalCustomerId = k.ExternalCustomerId',
    '  WHERE TRUE',
    '    AND DATE_DIFF( CURRENT_DATE(), k.Date, DAY ) <= 30',
    '	AND customer._LATEST_DATE = customer._DATA_DATE',
    //'	AND k._LATEST_DATE = k._DATA_DATE',
    '  GROUP BY AccountName, ConversionTypeName',
    '  ORDER BY AccountName, ConversionTypeName',
    ')',
    'GROUP BY AccountName, conversions, value',
    'HAVING count > 1',
    'AND conversions > 4'
  ].join( '\n' ),
  REPLACEABLE_NEGATIVE_KEYWORDS : [
    '#StandardSQL',
    'SELECT',
    '	AccountName,',
    '	negKeywords.CampaignName,',
    '	Criteria as negative_keyword,',
    '	KeywordMatchType as match_type,',
    '	countAdgroups as count',
    'FROM (',
    '	SELECT',
    '	  ifnull( CustomerDescriptiveName,  AccountDescriptiveName ) as AccountName,',
    '	  campaign.CampaignId,',
    '	  CampaignName,',
    '	  Criteria,',
    '	  keyword.KeywordMatchType',
    '	  ,count( DISTINCT AdGroupName ) as countAdgroups',
    '	FROM `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Keyword_' + ACCOUNT_ID + '` as keyword',
    '	JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Campaign_' + ACCOUNT_ID + '` as campaign ON campaign.ExternalCustomerId = keyword.ExternalCustomerId AND campaign.CampaignId = keyword.CampaignId',
    '	JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Customer_' + ACCOUNT_ID + '` as customer ON customer.ExternalCustomerId = keyword.ExternalCustomerId',
    '	JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.AdGroup_' + ACCOUNT_ID + '` as adgroup ON adgroup.ExternalCustomerId = keyword.ExternalCustomerId AND adgroup.AdGroupId = keyword.AdGroupId',
    '	WHERE TRUE',
    '	  AND keyword.IsNegative',
    '	  AND keyword.Status = \'Status_Active\'',
    '	  AND campaign.CampaignStatus = \'Status_Active\'',
    '	  AND adgroup.AdGroupStatus = \'ENABLED\'',
    '	  AND keyword._LATEST_DATE = keyword._DATA_DATE',
    '	  AND campaign._LATEST_DATE = campaign._DATA_DATE',
    '	  AND customer._LATEST_DATE = customer._DATA_DATE',
    '	  AND adgroup._LATEST_DATE = adgroup._DATA_DATE',
    '	group by AccountName, campaign.CampaignId, CampaignName, Criteria, KeywordMatchType',
    ') as negKeywords',
    'JOIN (',
    '	SELECT',
    '	  campaign.CampaignId,',
    '	  count( AdGroupName ) as countAdgroupsInCampaign',
    '	FROM `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Campaign_' + ACCOUNT_ID + '` as campaign',
    '	JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.AdGroup_' + ACCOUNT_ID + '` as adgroup ON adgroup.ExternalCustomerId = campaign.ExternalCustomerId AND campaign.CampaignId = adgroup.CampaignId',
    '	WHERE TRUE',
    '	  AND campaign.CampaignStatus = \'Status_Active\'',
    '	  AND adgroup.AdGroupStatus = \'ENABLED\'',
    '	  AND campaign._LATEST_DATE = campaign._DATA_DATE',
    '	  AND adgroup._LATEST_DATE = adgroup._DATA_DATE',
    '	group by CampaignId, CampaignName',
    ') as campaigns',
    'ON negKeywords.CampaignId = campaigns.CampaignId',
    'WHERE countAdgroups = countAdgroupsInCampaign',
    '  AND countAdgroups > 1',
  ].join( '\n' ),
  BROAD_MODIFIER_ERRORS : [
    '#StandardSQL',
    'SELECT *',
    'FROM unnest( [',
    '    \'wrongly_modified_broad\',',
    '    \'partly_modified_broad\',',
    '    \'unmodified_broad\'',
    '    ] ) as problem',
    'JOIN (',
    'SELECT *',
    'FROM (',
    '  SELECT',
    '	ifnull( CustomerDescriptiveName,  AccountDescriptiveName ) as AccountName,',
    '    CampaignName,',
    '    AdGroupName,',
    '    Criteria,',
    '    KeywordMatchType,',
    '    FinalUrls,',
    '    cast( ( not IsNegative AND ( KeywordMatchType = \'BROAD\' AND ( REGEXP_CONTAINS( Criteria, \'(\\\\S\\\\+)|(\\\\+(\\\\s+|$))\' ) ))) as int64 ) as wrongly_modified_broad,',
    '    cast( ( not IsNegative AND KeywordMatchType = \'BROAD\' AND REGEXP_CONTAINS( Criteria, \'((^|\\\\s)\\\\+\\\\S.*\\\\s[^\\\\+])|((^|\\\\s)[^\\\\+].*(\\\\s)\\\\+\\\\S)\' )) as int64 ) as partly_modified_broad,',
    '    cast( ( not IsNegative AND KeywordMatchType = \'BROAD\' AND NOT REGEXP_CONTAINS( Criteria, \'\\\\+\' )) as int64 ) as unmodified_broad',
    '  FROM `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Keyword_' + ACCOUNT_ID + '` as keyword',
    '  JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Campaign_' + ACCOUNT_ID + '` as campaign ON campaign.ExternalCustomerId = keyword.ExternalCustomerId AND keyword.CampaignId = campaign.CampaignId',
    '  JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.Customer_' + ACCOUNT_ID + '` as customer ON customer.ExternalCustomerId = keyword.ExternalCustomerId',
    '  JOIN `' + BIGQUERY.PROJECT_ID + '.' + BIGQUERY.DATASET_ID + '.AdGroup_' + ACCOUNT_ID + '` as adgroup ON adgroup.ExternalCustomerId = keyword.ExternalCustomerId AND keyword.CampaignId = adgroup.CampaignId AND keyword.AdGroupId = adgroup.AdGroupId',
    '  WHERE TRUE',
    '    AND Status = \'Status_Active\'',
    '    AND ApprovalStatus = \'APPROVED\'',
    '    AND campaign.CampaignStatus = \'Status_Active\'',
    '    AND adgroup.AdGroupStatus = \'ENABLED\'',
    '    AND keyword._LATEST_DATE = keyword._DATA_DATE',
    '    AND campaign._LATEST_DATE = campaign._DATA_DATE',
    '    AND customer._LATEST_DATE = customer._DATA_DATE',
    '    AND adgroup._LATEST_DATE = adgroup._DATA_DATE',
    '  GROUP BY AccountName, CampaignName, AdGroupName, Criteria, KeywordMatchType, FinalUrls, IsNegative',
    ')',
    'WHERE TRUE',
    '  AND ( FALSE',
    '    OR wrongly_modified_broad = 1',
    '    OR partly_modified_broad = 1',
    '    OR unmodified_broad = 1',
    '  )',
    ') ON FALSE',
    '  OR ( problem = \'wrongly_modified_broad\' AND wrongly_modified_broad = 1 )',
    '  OR ( problem = \'partly_modified_broad\' AND partly_modified_broad = 1 )',
    '  OR ( problem = \'unmodified_broad\' AND unmodified_broad = 1 )',
  ].join( '\n' ),
};

function main(){
  if( BIGQUERY.PROJECT_ID == 'enter you project id here' ){
    Logger.log( 'Unfinished configuration: pls enter your project_id' );
  }
  if( BIGQUERY.DATASET_ID == 'enter you dataset id here' ){
    Logger.log( 'Unfinished configuration: pls enter your dataset_id' );
  }
  
  Object.keys( VIEWS ).forEach( function( view ){
    var viewName = VIEW_PREFIX + view.toLowerCase();
    var query = VIEWS[ view ];
    //Logger.log( query );
    createView( viewName, query );
  });
}

/**
 * Checks if table already exists in dataset.
 *
 * @param {string} tableId The table id to check existence.
 *
 * @return {boolean}  Returns true if table already exists.
 */
function tableExists( tableId ){
  var pageToken = ''; // start with empty pageToken
  var resultsPerPage = 150;
  var finished = false;
  
  while( ! finished ){
    // Get a list of a part of all tables in the dataset.
    var tables = BigQuery.Tables.list(
      BIGQUERY.PROJECT_ID,
      BIGQUERY.DATASET_ID,
      {
        pageToken  : pageToken,
        maxResults : resultsPerPage
      }
    );
    pageToken = tables.nextPageToken;
      
    if( ! pageToken ){
      finished = true;
    }
    // Iterate through each table and check for an id match.
    if ( tables.tables != null ){
      for( var i = 0; i < tables.tables.length; i++ ){
        var table = tables.tables[ i ];
        if( table.tableReference.tableId == tableId ){
          return true;
        }
      }
    }
  }
  return false;
}

function createView( viewName, query ){
  if ( tableExists( viewName ) ){
    if( REPLACE_EXISTING_VIEWS ){
      BigQuery.Tables.remove( BIGQUERY.PROJECT_ID, BIGQUERY.DATASET_ID, viewName );
    }else{
      Logger.log('View %s already exists. Skip.', viewName );
      return;
    }
  }

  var table = BigQuery.newTable();
  
  table.friendlyName = viewName;

  table.tableReference = BigQuery.newTableReference();
  table.tableReference.datasetId = BIGQUERY.DATASET_ID;
  table.tableReference.projectId = BIGQUERY.PROJECT_ID;
  table.tableReference.tableId = viewName;
  
  table.view = {
    query : query,
    useLegacySql : false
  };
  
  try{
    BigQuery.Tables.insert(
      table,
      BIGQUERY.PROJECT_ID,
      BIGQUERY.DATASET_ID
    );
    Logger.log( 'View ' + viewName + ' created.' );
  }catch( error ){
    Logger.log( '----------------------> ' + error + ' - ' + viewName );
  }
}