Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

SEA Camp Logo

Liebe SEA-Camp Hannover Teilnehmer,

Danke fürs Zuhören von unserem Vortrag "Tools für bessere SEA-SEO Verzahnung". Wir hoffen, ihr habt einige gute Ideen mitnehmen können. Diese github-gist Seite hat zum Ziel, alle relevanten und angesprochenen Inhalte in einer Dokumentation zusammenzuführen.

Die Folien findet ihr hier: slideshare.com/norisk

Bei weiteren Fragen, gerne unten kommentieren oder eine Mail schreiben an: cgutknecht@noriskshop.de oder dkonszack@noriskshop.de

Viele Grüße Domenic & Christopher


Einleitende Artikel (Folien 6-12)


DataStudio Setup und Daten zusammenführen (Folie 18)

  • Zunächst bindet ihr die Datenquellen Google Ads und Search Console ein
  • Im Anschluss nutzt ihr die Funktion Daten zusammenführen und wählt Euren Verknüpfungsschlüssel und die zu verbindenden Metriken aus. Je spezifischer der Schlüssel, desto stärker werden die Daten segmentiert.

bildschirmfoto 2018-10-31 um 22 18 59

Es sind auch mehrere parallele Verknüpfungen mit verschiedenen Schlüsseln denkbar, um alle Aggregationslevels in einem Report parallel zu nutzen.

Darüberhinaus ist es sinnvoll, das unten genannte SEA-SEO Reportsheet mit den obigen Data Studio Blends zu verknüpfen, um ein umfassenden Datenbild zu erhalten.

EXKURS: Die Nutzung des PaidOrganic-Query Reports kann auch mittels des Ads Data Transfer Services in BigQuery, und dann in DataStudio geladen werden, allerdings sind - wie in Folie 15 erwähnt - die SEO Daten FALSCH (!) bzw unterrepräsentiert, da nur SERP Impressions herangezogen werden, wenn mind eine SEA Impression stattfand. Nach Einrichtung des Data Transfers kann man in der neuen BigQuery UI die Option "Im DataStudio erkunden" nutzen und kommt in die Labs UI von DataStudio.

bildschirmfoto 2018-10-28 um 10 19 10

  • Ab diesem Punkt liegen Euch die Daten in der Labs UI vor und ihr habt freie Hand zum Reports bauen. Über die Funktion rechts obem "In Bericht exportieren" könnt ihr die Ansicht in ein reguläres DataStudio Dashboard umbauen und und andere Datenquellen hinzufügen.

SEA-SEO Reportskript in Google Sheets (Slide 19)

Zunächst KOPIERT ihr das folgende DEMO-SHEET, welches auch in der Session gezeigt wurde: https://docs.google.com/spreadsheets/d/1jIMsEheJvpzpnTJZSkkVPcZL5_azD3NcWjrLY-LXtZ0/edit?usp=sharing

Im Anschluss kopiert ihr das folgende Ads-Skript in den Account Eures Interesses: https://gist.github.com/norisk-marketing/23f7c5ef58a1357bad2c5cde3b9c9f3d#file-seaseoreport-js

VORAUSSETZUNGEN:

  • Euer Google Ads Account ist mit dem Google Search Console Account verknüpft, sonst gibt es KEINE Daten.

  • Trotz GSC Anbindung werden die GSC Daten MANUELL per Plugin in das Sheet gezogen, damit die SEO Daten korrekt sind, siehe Problem Folie 15.

  • Euer Google Ads User hat Zugriff auf Google Analytics und kann API-Calls aus dem Skript ausführen.

  • WICHTIG: Ihr müsst im Sheet in den Erweiterte APIs Google Analytics aktivieren, sonst erhaltet ihr keinen SEO-Umsatz.

    bildschirmfoto 2018-10-28 um 12 05 46
  • Nach Aktivierung der API müsst ihr in der Skriptkonfiguration Eure GA-Profile ID angeben, diese erhaltet ihr in GA unter Verwaltung > Datenansicht > Einstellungen. Nach Authorisierung wird beim nächsten Skriptdurchlauf ein Fehler geworfen:

Project xy is not found and cannot be used for API calls. 
If it is recently created, enable Google Analytics API by visiting the following URL...

WICHTIG: Dieser URL folgen und die API in der Cloud Console aktivieren.

bildschirmfoto 2018-10-31 um 23 41 02

Im Anschluss sollte das Skript die Analytics Daten ziehen können

MANUELLER (!) Datenabzug über Search Analytics Addon:

  • Ihr habt die Sheet-Extension "Search Analytics for Sheets" installiert, um die GSC-Daten zu ziehen: https://chrome.google.com/webstore/detail/search-analytics-for-shee/ieciiohbljgdndgfhgmdjhjgganlbncj

  • Mit dem Search Analytics Sheet zieht ihr Euch wie folgt die Daten der letzten 30 Tage:

  • In das "(SEO_I)" Tab mit den Dimensionen Query, Page, sowie zB einen Non-Brand Filter

  • In das "(SEO_II_Page-Only)" Tab mit der Dimensionen Page, sowie zB einen Non-Brand Filter. Dies dient der Berechnung des Query-Weights pro URL

    SEO_I Call >> SEO_URL Only

CODE ERNEUT HIER: https://gist.github.com/norisk-marketing/23f7c5ef58a1357bad2c5cde3b9c9f3d#file-seaseoreport-js


Keyword Expansion Script (Folie 23-24)

CODE HIER: https://gist.github.com/norisk-marketing/23f7c5ef58a1357bad2c5cde3b9c9f3d#file-keywordexpansion-js

Unser Keyword Expansion ist mit dem obigen SEA-SEO REport-Sheet verdrahtet und nutzt Inputwerte auf dem Tab "Expansion_KW-Input".

Alle Einträge in diesem Tab werden wie folgt verarbeitet:

`

  • Uses the Google Suggest API and an adaptation of a DerekMartin script to scrape related searches. Reference: MixedMarketingArtist.com
  • Calculates a common denominator of related searches to highlight multiple word occurrences
  • Fetches the search volume via a internal KW Planner API and prints the result to a spreadsheet `

Alle neuen Einträge werden versucht, zum Sheet hinzuzufügen, falls das baseKeyword noch nicht im Tab "Expansion_KWData" vorhanden ist. Bereits vorhandene Einträge werden einfach ignoriert. Im Anschluss können die Einträge im "Expansion_KW-Input" wieder entfernt oder neue hinzugefügt werden.

Den Skript Code findet ihr weiter unten: https://gist.github.com/norisk-marketing/23f7c5ef58a1357bad2c5cde3b9c9f3d#file-keywordexpansion-js


Eigene Keyword Planner API bauen (Folie 25)

Im Keyword Expansion Skript nutzen wir eine PHP-Implementierung zur Abfrage des Keyword Planners und können per HTTP-POST im Payload ein Array an Keywords mitgeben. Gehostet wird das PHP-Skript auf unserem Digitaldrang-Server. Aktuell sind im Basiszugriff bis zu 10.000 / Tag möglich, also ausreichend für mehrere Tester gleichzeitig.

Schritte zur Erstellung einer eigenen KW Planner API

  • Zunächst muss API Zugriff beantragt werden. Der Vorgang ist hier ausführlich beschrieben: https://developers.google.com/adwords/api/docs/guides/signup?hl=de

  • Ausgangspunkt ist der Menüpunkt API-Center in der Ads UI zur Befüllung der Kontaktdaten

  • Im Anschluss muss ein detailliertes Formular zur genauen Beschreibung der Anwenungsfälle der spezifisch genutzen Services ausgefüllt werden, worauf typischerweise Rückfragen vom Ads API Team kommen.

  • Wenn der Antrag genehmigt wird, steht nun im API-Center das Entwickler-Token bereit, mit welchem echte Daten zurückgegeben werden (vorher nur Testdaten)

  • Nun folgt der eigentlich schwerste Schritt: Die Authentifizierung der Applikation mittels oAuth2. Der Prozess ist von Google ausführlich mit Videos (Xerxes wird ein enger Bekannter werden) dokumentiert, aber trotzdem ziemichh kompliziert: https://developers.google.com/adwords/api/docs/guides/first-api-call?hl=de

Folgende Infos müssen zur Authentifizierung vorliegen, welche über ein Cloud Console Projekt generiert werden. Die Werte unten wurden angepasst bzw anonymisiert:

clientCustomerId (abgeändert)

123-456-7890

developerToken (abgeändert)

oD23gWwevFVWchhpO-7wvVg

clientId (abgeändert)

123475188799-mgdfj3tpv48rh6brnvaho12u6dvj3tmi.apps.googleusercontent.com

clientSecret (abgeändert)

v7tHiUkAAnlAAA1f9Hjj1Ls

refreshToken - mit getRefreshToken.php generiert (https://github.com/googleads/googleads-php-lib/blob/master/examples/Auth/GetRefreshToken.php) (abgeändert)

13vFgy3sivsdfjhs1lWMTD0-wGReRDSk52fgxsdAK4LfZ1s

Für die PHP Version werden alle Credentials in eine adsapi_php.ini gepackt, siehe weiter unten: https://gist.github.com/norisk-marketing/23f7c5ef58a1357bad2c5cde3b9c9f3d#file-kwplanner_demo__adsini_php-ini


Google Cloud Function über Ads Script ansprechen (Folie 26)

Die Integration einer Python Cloud Function über ein Ads Skript ist in diesem Gist zur HeroConf 2018 ausführlicher beschrieben. Im konkreten Anwendungsfall werden zwei Strings für einen "PartialString" Vergleich übergeben, welches mittels FuzzyWuzzy Package einen Ähnlichkeitswert zurückgibt.

https://gist.github.com/norisk-marketing/afb26844fa3b68f2de9440bf7e006f4b#python-get-partialratio-via-cloud-functions-slide-25

Weitere Beispiele für Anwendungsfälle wären: Nutzung der Bing Spell Checker API zur Erkennung von Verschreiberkeywords:


LandingPageFinder (Folie 27)

Unser LandingPageFinder Skript ist in diesem gist zur HeroConf 2018 ausführlicher beschrieben. https://gist.github.com/norisk-marketing/afb26844fa3b68f2de9440bf7e006f4b#ask-google-5-landinge-page-finder-script-slide-18

Falls ihr Fragen dazu habt, gerne dort als Kommentar posten!


FAZIT

Wir hoffen, es waren einige hilfreiche Idee für Eure eigene Arbeit dabei! Bei weiteren Fragen, einfach kommentieren oder Email schicken an: cgutknecht@noriskshop.de oder dkonszack@noriskshop.de

In diesem Sinne: Auf neue SEA-SEO Synergien und Happy Automating!

norisk Group logo

/**********************************************************************************************************************
* NORISK KEYWORD EXPANSION SCRIPT
*
* Related Searches Scraper
* By norisk Group (Chris Gutknecht & Alex Groß)
*
* Uses the Google Suggest API and an adaptation of a DerekMartin script to scrape related searches. Reference: MixedMarketingArtist.com
* Calculates a common denominator of related searches to highlight multiple word occurrences
* Fetches the search volume via a internal KW Planner API and prints the result to a spreadsheet
**********************************************************************************************************************/
// ************ START CONFIGURATION ************ //
// MAKE A COPY of this DEMO SHEET: https://docs.google.com/spreadsheets/d/1jIMsEheJvpzpnTJZSkkVPcZL5_azD3NcWjrLY-LXtZ0/edit#gid=2086633269
// Paste the URL below:
var SHEET_URL = "https://docs.google.com/spreadsheets/d/DEMO/edit#gid=2086633269";
// Choose target language here: https://developers.google.com/adwords/api/docs/appendix/codes-formats#languages
var LANG_ID = 1001; // German
// Choose target location here: https://fusiontables.google.com/DataSource?docid=1Jlxrqc1dU3a9rsNW2l5xxlmQEKUu0dIPusImi41B#rows:id=1
var LOC_ID = 2276; // Germany
// ************ END CONFIGURATION ************ //
var DEBUG_MODE = 0;
function main() {
// Get Expansion keywords from sheet, expected tab "Expansion_KW-Input"
var targetKeywords = getTargetKeywords();
// Iterate through expansion keywords, build related searches and fetch search volumes
for(var i=0; i< targetKeywords.length; i++) {
Logger.log("\nStarting to fetch suggest + related keywords for '" + targetKeywords[i] + "'");
var keywordSuggestions = {"baseKeyword" : targetKeywords[i], "synonyms": {}, "allRelatedAndSuggestKeywords": [], "keywordObjects" : [], "intersections" : {}};
keywordSuggestions = getSuggestKeywords(targetKeywords[i], keywordSuggestions);
keywordSuggestions = getRelatedKeywords(targetKeywords[i], keywordSuggestions);
keywordSuggestions.intersections = computeWordIntersection(keywordSuggestions.allRelatedAndSuggestKeywords);
keywordSuggestions.synonyms = getSynonyms(targetKeywords[i], keywordSuggestions);
if(DEBUG_MODE === 1) Logger.log("keywordSuggestions:\n" + JSON.stringify(keywordSuggestions));
var plannerResponses = getKwPlannerData(keywordSuggestions.allRelatedAndSuggestKeywords);
if(JSON.stringify(plannerResponses).indexOf("RateExceededError <rateName=RATE_LIMIT") != -1) {
Utilities.sleep(32000);
plannerResponses = getKwPlannerData(keywordSuggestions.allRelatedAndSuggestKeywords);
}
for(var j=0; j< keywordSuggestions.keywordObjects.length; j++) {
keywordSuggestions.keywordObjects[j].searchVolume = plannerResponses[keywordSuggestions.keywordObjects[j].term] == undefined ? 0 : plannerResponses[keywordSuggestions.keywordObjects[j].term];
}
writeToSheet(keywordSuggestions);
}
Logger.log("\n\nDone.");
}
/////// /////////////////////////
/// FUNCTION DEFINITIONS ////////
/////////////////////////////////
function getTargetKeywords() {
var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL);
var sheet = spreadsheet.getSheetByName("Expansion_KW-Input");
var keywordInput = sheet.getRange("A:A").getValues();
var targetKeywords = [];
for(var i=1; i<keywordInput.length; i++) {
if(targetKeywords.indexOf(keywordInput[i][0]) == -1 && keywordInput[i][0].length > 0) targetKeywords.push(keywordInput[i][0]);
}
targetKeywords = targetKeywords.slice(0,9);
Logger.log("targetKeywords : " + targetKeywords);
return targetKeywords;
}
function getSuggestKeywords(keyword, keywordSuggestions){
var keyword = keyword.replace(/ /g,"+").replace(/-/g,"+").replace(/_/g,"+");
var requestUrl = "https://suggestqueries.google.com/complete/search?output=chrome&hl=de&q=" + keyword;
var response = JSON.parse(UrlFetchApp.fetch(requestUrl));
if(typeof response[1][0] != "undefined") {
for(var j=0;j<10;j++) {
if(j === 0 || response[4]["google:suggestrelevance"][j-1] - response[4]["google:suggestrelevance"][j] < 10) {
var levenshteinDist = dziemba_levenshtein(response[0].toLowerCase(), response[1][j]);
var simMetric = (1-levenshteinDist/response[1][j].length).toFixed(2);
keywordSuggestions.allRelatedAndSuggestKeywords.push(response[1][j]);
keywordSuggestions.keywordObjects.push({
"term" : response[1][j],
"simValue" : simMetric,
"type" : (simMetric > 0.84 && levenshteinDist < 2) ? "typo/plural" : ""
});
}
} // END FOR Loop
}
return keywordSuggestions;
}
function getRelatedKeywords(keyword, keywordSuggestions){
buildKeywordList(keyword);
brandKeywordList.sort();
for(var i=0; i< brandKeywordList.length; i++){
if(keywordSuggestions.allRelatedAndSuggestKeywords.indexOf(brandKeywordList[i]) == -1 && brandKeywordList[i] !== keyword) {
var levenshteinDist = dziemba_levenshtein(keyword.split(" ").sort().join(","), brandKeywordList[i].split(" ").sort().join(","));
var wordDistance = (1-levenshteinDist/brandKeywordList[i].length).toFixed(2);
keywordSuggestions.allRelatedAndSuggestKeywords.push(brandKeywordList[i]);
keywordSuggestions.keywordObjects.push({
"term": brandKeywordList[i],
"simValue" : wordDistance,
"type" : ""
});
}
}
return keywordSuggestions;
}
/*
* @param {string} keyword
* @param {object} keywordSuggestions
* @return {array} synonyms
*/
function getSynonyms(keyword, keywordSuggestions){
var synonyms = [];
for(var i=0; i<Object.keys(keywordSuggestions.intersections.combinations).reverse().length; i++){
var combinationKey = Object.keys(keywordSuggestions.intersections.combinations)[i];
if(combinationKey.split(" ").length > 1 && keywordSuggestions.intersections.combinations[combinationKey].hit_count > 3 && keyword.indexOf(combinationKey) == -1) {
synonyms.push({"synonym" : combinationKey, "hitCount" : keywordSuggestions.intersections.combinations[combinationKey].hit_count});
}
}
return synonyms;
}
/*
* @param {object} keywordSuggestions
* @return {void}
*/
function writeToSheet(keywordSuggestions){
var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL);
var sheet = spreadsheet.getSheetByName("Expansion_KWData");
var headerRange = sheet.getRange(1, 1, 1, 6);
headerRange.setValues([["baseKeyword", "type", "term", "simValue", "detail", "volume"]]);
headerRange.setBackground("yellow").setFontWeight("bold").setHorizontalAlignment("center");
var suggestionsArray = convertSuggestionsToArray(keywordSuggestions);
var firstFreeRow = getLastReportRow(sheet);
var destinationRange = sheet.getRange(firstFreeRow, 1, suggestionsArray.length, suggestionsArray[0].length);
var firstColumnValues = sheet.getRange(2,1,firstFreeRow-1,1).getValues();
var suggestionsAlreadyCopied = false;
for(var i=0; i< firstColumnValues.length;i++){
if(firstColumnValues[i][0] === suggestionsArray[0][0]) suggestionsAlreadyCopied = true;
}
if(suggestionsAlreadyCopied === false) {
destinationRange.setValues(suggestionsArray);
Logger.log("suggestionsArray printed to sheet-URL: " + SHEET_URL);
} else Logger.log("suggestionsArray already contained in sheet-URL: " + SHEET_URL);
}
/*
* @param {object} keywordSuggestions
* @return {array} suggestionsArray
*/
function convertSuggestionsToArray(keywordSuggestions) {
var suggestionsArray = [];
suggestionsArray.push([keywordSuggestions.baseKeyword, "baseKeyword", keywordSuggestions.baseKeyword, "1", "", ""]);
for(var i=0; i< keywordSuggestions.synonyms.length; i++){
suggestionsArray.push([keywordSuggestions.baseKeyword, "synonym", keywordSuggestions.synonyms[i].synonym, "", "synHits:"+keywordSuggestions.synonyms[i].hitCount, ""]);
}
for(var i=0; i< keywordSuggestions.keywordObjects.length; i++){
suggestionsArray.push([keywordSuggestions.baseKeyword, "suggestion", keywordSuggestions.keywordObjects[i].term, keywordSuggestions.keywordObjects[i].simValue, keywordSuggestions.keywordObjects[i].type, keywordSuggestions.keywordObjects[i].searchVolume]);
}
if(DEBUG_MODE === 1) Logger.log("suggestionsArray : "+ suggestionsArray);
return suggestionsArray;
}
/*
* @param {object} singleSheet
* @return {int} lastReportRow
*/
function getLastReportRow(singleSheet) {
var column = singleSheet.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] !== "" ) {
ct++;
}
return (ct+1);
};
/*
* @param {array} keywords
* @return {object} responseObject
*/
function getKwPlannerData(keywords){
var formData = {
"keywords" : keywords,
"langId" : LANG_ID,
"locId" : LOC_ID
};
var options = {
"method" : 'post',
"contentType" : "application/json",
"payload" : JSON.stringify(formData),
"muteHttpExceptions" : true
};
var responseObject = JSON.parse(UrlFetchApp.fetch('http://testcos.digitaldrang.de/SearchVolume/SearchVolume.php', options));
return responseObject;
}
/*
* @param {string} a, first string
* @param {string} b, second string
* @return {int} res, the number of letter changes necessary
*/
function dziemba_levenshtein(a, b){
var tmp;
if (a.length === 0) { return b.length; }
if (b.length === 0) { return a.length; }
if (a.length > b.length) { tmp = a; a = b; b = tmp; }
var i, j, res, alen = a.length, blen = b.length, row = Array(alen);
for (i = 0; i <= alen; i++) { row[i] = i; }
for (i = 1; i <= blen; i++) {
res = i;
for (j = 1; j <= alen; j++) {
tmp = row[j - 1];
row[j - 1] = res;
res = b[i - 1] === a[j - 1] ? tmp : Math.min(tmp + 1, Math.min(res + 1, row[j] + 1));
}
}
return res;
}
/**
* computes the intersection of an array of strings
* @param {array} array array containing the strings to be computed
* @return {object}
* object = { total: { max_intersect_word: "", max_intersect_value: 0, word_count: 0 }, combinations: {}};
*/
function computeWordIntersection(array) {
var object = {
total: {
max_intersect_word: "",
max_intersect_value: 0,
word_count: 0
},
combinations: {}
};
for (var i = 0; i < array.length; i++) {
var value_array = array[i].split(" ");
value_array = getCombinations(value_array);
for (var j = 0; j < value_array.length; j++) {
var value = value_array[j];
if (object.combinations[value]) {
continue;
}
object.combinations[value] = {
hit_count: 0,
inbetween_count: 0,
inbeginning_count: 0,
atend_count: 0,
word_count: value.split(" ").length
};
for (var k = 0; k < array.length; k++) {
try{
if (array[k].match(new RegExp(".*" + value + ".*", "g"))) {
object.combinations[value].hit_count++;
}
if (array[k].match(new RegExp(".*\\s" + value + "\\s.*", "g"))) {
object.combinations[value].inbetween_count++;
}
if (array[k].match(new RegExp("^" + value + "\\s.*", "g"))) {
object.combinations[value].inbeginning_count++;
}
if (array[k].match(new RegExp(".*\\s" + value + "$", "g"))) {
object.combinations[value].atend_count++;
}
} catch(e){Logger.log(e);}
}
object.combinations[value].appearing_index = object.combinations[value].hit_count / array.length;
object.combinations[value].appearing_fullWord_index = (object.combinations[value].inbetween_count + object.combinations[value].inbeginning_count + object.combinations[value].atend_count) / array.length;
if (object.combinations[value].appearing_fullWord_index > object.total.max_intersect_value) {
object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
object.total.max_intersect_word = value;
object.total.word_count = object.combinations[value].word_count;
} else if (object.combinations[value].appearing_fullWord_index == object.total.max_intersect_value && object.combinations[value].word_count > object.total.word_count) {
object.total.max_intersect_value = object.combinations[value].appearing_fullWord_index;
object.total.max_intersect_word = value;
object.total.word_count = object.combinations[value].word_count;
}
}
}
return object;
};
/**
* generates combinations of characters
* @param {array} chars strings
* @return {array} result
*/
function getCombinations(chars) {
var result = [];
var f = function(prefix, chars) {
for (var i = 0; i < chars.length; i++) {
result.push(prefix + chars[i]);
f(prefix + chars[i] + " ", chars.slice(i + 1));
}
};
f('', chars);
return result;
};
var brandKeywordList = ["someValue"];
var hashMapResults = {};
var numOfKeywords = 0;
var doWork = false;
var keywordsToQuery = new Array();
var keywordsToQueryIndex = 0;
var queryflag = false;
/* Adaptation of a Derek Martin method
* @param {string} keyword
* @return {void} adds to the global variable brandKeywordList
*/
function buildKeywordList(keyword) {
// get the first set of keywords related to the term and add to list
brandKeywordList = queryKeyword(keyword);
// iterate through alphabet and build keyword list for initial keyword
for(var j = 0; j < 26; j++) {
var chr = String.fromCharCode(97 + j);
keywordVariation = keyword + ' '+ chr;
var alphaList = {};
alphaList = queryKeyword(keywordVariation);
for (var x = 0; x < alphaList.length; x++) {
if (x !== 0) { brandKeywordList.push(alphaList[x]); }
}
}
for(var n = 0; n <= 9; n++) {
keywordVariation = keyword + ' '+ n;
var numberList = {};
numberList = queryKeyword(keywordVariation);
for (var y = 0; y < numberList.length; y++) {
if (y !== 0) {brandKeywordList.push(numberList[y]);}
}
}
////////////////////////////////////
///// START CASE MULTIPLE WORDS ////
////////////////////////////////////
// Split keyword up if possible and look for different variations
var keywordPieces = _.str.words(keyword);
if (keywordPieces.length > 1) {
// iterate through alphabet and build keyword list for the variation: [keywordPiece1] + [a-z][0-9] + [keywordPiece2]
// Checking for the variation [keywordPiece1] + [a-z][0-9] + [keywordPiece2]...
// Variation: ' + keywordPieces[0] + ' '+ keywordPieces[1] + keywordPieces[2])
for(var j = 0; j < 26; j++) {
var chr = String.fromCharCode(97 + j);
keywordVariation = keywordPieces[0] + ' '+ chr + ' ' + keywordPieces[1]+ ' ' + keywordPieces[2];
var alphaList = {};
alphaList = queryKeyword(keywordVariation);
for (var x = 0; x < alphaList.length; x++) {
if (x !== 0) { brandKeywordList.push(alphaList[x]); }
}
}
for(var n = 0; n <= 9; n++) {
keywordVariation = keywordPieces[0] + ' '+ n + ' ' + keywordPieces[1] +' '+ keywordPieces[2];
var numberList = {};
numberList = queryKeyword(keywordVariation);
for (var y = 0; y < numberList.length; y++) {
if (y !== 0) { brandKeywordList.push(numberList[y]); }
}
}
Utilities.sleep(2000);
/* CHECK FOR THE VARIATION [keywordPiece1] + [a-z][0-9] + [keywordPiece0] */
// warn('now checking for the variation [keywordPiece2] + [a-z][0-9] + [keywordPiece1]...');
// warn('variation: ' + keywordPieces[1] + ' '+ keywordPieces[0]);
for(var j = 0; j < 26; j++) {
var chr = String.fromCharCode(97 + j);
keywordVariation = keywordPieces[1] + ' ' + keywordPieces[2] + ' '+ chr + ' ' + keywordPieces[0];
var alphaList = {};
alphaList = queryKeyword(keywordVariation);
for (var x = 0; x < alphaList.length; x++) {
if (x !== 0) {
info(alphaList[x]);
brandKeywordList.push(alphaList[x]);
}
}
}
for(var n = 0; n <= 9; n++) {
keywordVariation = keywordPieces[1] + ' ' + keywordPieces[2] + ' '+ n + ' ' + keywordPieces[0];
var numberList = {};
numberList = queryKeyword(keywordVariation);
for (var y = 0; y < numberList.length; y++) {
if (y !== 0) { brandKeywordList.push(numberList[y]); }
}
}
Utilities.sleep(2000);
/* last variation: [a-z][0-9] [keyword1] [keyword2] */
/* CHECK FOR THE VARIATION [keywordPiece1] + [a-z][0-9] + [keywordPiece0] */
// info('now checking for the variation [a-z][0-9] + [keywordPiece1] + [keywordPiece2]...');
// warn('variation: ' + keywordPieces[0] + ' '+ keywordPieces[1]);
for(var j = 0; j < 26; j++) {
var chr = String.fromCharCode(97 + j);
keywordVariation = chr + ' ' + keywordPieces[0] + ' ' + keywordPieces[1] + keywordPieces[2];
var alphaList = {};
alphaList = queryKeyword(keywordVariation);
for (var x = 0; x < alphaList.length; x++) {
if (x !== 0) { brandKeywordList.push(alphaList[x]); }
}
}
for(var n = 0; n <= 9; n++) {
keywordVariation = n + ' ' + keywordPieces[0] + ' ' + keywordPieces[1] + keywordPieces[2];
var numberList = {};
numberList = queryKeyword(keywordVariation);
for (var y = 0; y < numberList.length; y++) {
if (y !== 0) { brandKeywordList.push(numberList[y]); }
}
}
} // END IF Keyword Length > 1
////////////////////////////////////
///// END CASE MULTIPLE WORDS //////
////////////////////////////////////
}
/* Utility Functions */
function warn(msg) { Logger.log('WARNING: '+msg); }
function info(msg) { Logger.log(msg); }
function queryKeyword(keyword) {
var querykeyword = encodeURIComponent(keyword);
var queryresult = '';
queryflag = true;
Utilities.sleep(1000);
var response = UrlFetchApp.fetch("https://www.google.de/s?gs_rn=18&gs_ri=psy-ab&cp=7&gs_id=d7&xhr=t&q=" + querykeyword);
var retval = response.getContentText();
var test = _.str.stripTags(retval);
var retList = ScrapePage(retval, '["', '",');
queryflag = false;
return retList;
}
function ScrapePage(page, left, right) {
var i = 0;
var retVal = new Array();
var firstIndex = page.indexOf(left);
while (firstIndex != -1)
{
firstIndex += left.length;
var secondIndex = page.indexOf(right, firstIndex);
if (secondIndex != -1)
{
var val = page.substring(firstIndex, secondIndex);
val = val.replace("\\u003cb\\u003e", "");
val = val.replace("\\u003c\\/b\\u003e", "");
val = val.replace("\\u003c\\/b\\u003e", "");
val = val.replace("\\u003cb\\u003e", "");
val = val.replace("\\u003c\\/b\\u003e", "");
val = val.replace("\\u003cb\\u003e", "");
val = val.replace("\\u003cb\\u003e", "");
val = val.replace("\\u003c\\/b\\u003e", "");
val = val.replace("\\u0026amp;", "&");
val = val.replace("\\u003cb\\u003e", "");
val = val.replace("\\u0026", "");
val = val.replace("\\u0026#39;", "'");
val = val.replace("#39;", "'");
val = val.replace("\\u003c\\/b\\u003e", "");
val = val.replace("\\u2013", "2013");
retVal[i] = val;
i++;
firstIndex = page.indexOf(left, secondIndex);
}
else
{
return retVal;
}
}
return retVal;
}
!function(e,t){"use strict";var n=t.prototype.trim;var r=t.prototype.trimRight;var i=t.prototype.trimLeft;var s=function(e){return e*1||0};var o=function(e,t){if(t<1)return"";var n="";while(t>0){if(t&1)n+=e;t>>=1,e+=e}return n};var u=[].slice;var a=function(e){if(e==null)return"\\s";else if(e.source)return e.source;else return"["+p.escapeRegExp(e)+"]"};var f={lt:"<",gt:">",quot:'"',apos:"'",amp:"&"};var l={};for(var c in f){l[f[c]]=c}var h=function(){function e(e){return Object.prototype.toString.call(e).slice(8,-1).toLowerCase()}var n=o;var r=function(){if(!r.cache.hasOwnProperty(arguments[0])){r.cache[arguments[0]]=r.parse(arguments[0])}return r.format.call(null,r.cache[arguments[0]],arguments)};r.format=function(r,i){var s=1,o=r.length,u="",a,f=[],l,c,p,d,v,m;for(l=0;l<o;l++){u=e(r[l]);if(u==="string"){f.push(r[l])}else if(u==="array"){p=r[l];if(p[2]){a=i[s];for(c=0;c<p[2].length;c++){if(!a.hasOwnProperty(p[2][c])){throw new Error(h('[_.sprintf] property "%s" does not exist',p[2][c]))}a=a[p[2][c]]}}else if(p[1]){a=i[p[1]]}else{a=i[s++]}if(/[^s]/.test(p[8])&&e(a)!="number"){throw new Error(h("[_.sprintf] expecting number but found %s",e(a)))}switch(p[8]){case"b":a=a.toString(2);break;case"c":a=t.fromCharCode(a);break;case"d":a=parseInt(a,10);break;case"e":a=p[7]?a.toExponential(p[7]):a.toExponential();break;case"f":a=p[7]?parseFloat(a).toFixed(p[7]):parseFloat(a);break;case"o":a=a.toString(8);break;case"s":a=(a=t(a))&&p[7]?a.substring(0,p[7]):a;break;case"u":a=Math.abs(a);break;case"x":a=a.toString(16);break;case"X":a=a.toString(16).toUpperCase();break}a=/[def]/.test(p[8])&&p[3]&&a>=0?"+"+a:a;v=p[4]?p[4]=="0"?"0":p[4].charAt(1):" ";m=p[6]-t(a).length;d=p[6]?n(v,m):"";f.push(p[5]?a+d:d+a)}}return f.join("")};r.cache={};r.parse=function(e){var t=e,n=[],r=[],i=0;while(t){if((n=/^[^\x25]+/.exec(t))!==null){r.push(n[0])}else if((n=/^\x25{2}/.exec(t))!==null){r.push("%")}else if((n=/^\x25(?:([1-9]\d*)\$|\(([^\)]+)\))?(\+)?(0|'[^$])?(-)?(\d+)?(?:\.(\d+))?([b-fosuxX])/.exec(t))!==null){if(n[2]){i|=1;var s=[],o=n[2],u=[];if((u=/^([a-z_][a-z_\d]*)/i.exec(o))!==null){s.push(u[1]);while((o=o.substring(u[0].length))!==""){if((u=/^\.([a-z_][a-z_\d]*)/i.exec(o))!==null){s.push(u[1])}else if((u=/^\[(\d+)\]/.exec(o))!==null){s.push(u[1])}else{throw new Error("[_.sprintf] huh?")}}}else{throw new Error("[_.sprintf] huh?")}n[2]=s}else{i|=2}if(i===3){throw new Error("[_.sprintf] mixing positional and named placeholders is not (yet) supported")}r.push(n)}else{throw new Error("[_.sprintf] huh?")}t=t.substring(n[0].length)}return r};return r}();var p={VERSION:"2.3.0",isBlank:function(e){if(e==null)e="";return/^\s*$/.test(e)},stripTags:function(e){if(e==null)return"";return t(e).replace(/<\/?[^>]+>/g,"")},capitalize:function(e){e=e==null?"":t(e);return e.charAt(0).toUpperCase()+e.slice(1)},chop:function(e,n){if(e==null)return[];e=t(e);n=~~n;return n>0?e.match(new RegExp(".{1,"+n+"}","g")):[e]},clean:function(e){return p.strip(e).replace(/\s+/g," ")},count:function(e,n){if(e==null||n==null)return 0;return t(e).split(n).length-1},chars:function(e){if(e==null)return[];return t(e).split("")},swapCase:function(e){if(e==null)return"";return t(e).replace(/\S/g,function(e){return e===e.toUpperCase()?e.toLowerCase():e.toUpperCase()})},escapeHTML:function(e){if(e==null)return"";return t(e).replace(/[&<>"']/g,function(e){return"&"+l[e]+";"})},unescapeHTML:function(e){if(e==null)return"";return t(e).replace(/\&([^;]+);/g,function(e,n){var r;if(n in f){return f[n]}else if(r=n.match(/^#x([\da-fA-F]+)$/)){return t.fromCharCode(parseInt(r[1],16))}else if(r=n.match(/^#(\d+)$/)){return t.fromCharCode(~~r[1])}else{return e}})},escapeRegExp:function(e){if(e==null)return"";return t(e).replace(/([.*+?^=!:${}()|[\]\/\\])/g,"\\$1")},splice:function(e,t,n,r){var i=p.chars(e);i.splice(~~t,~~n,r);return i.join("")},insert:function(e,t,n){return p.splice(e,t,0,n)},include:function(e,n){if(n==="")return true;if(e==null)return false;return t(e).indexOf(n)!==-1},join:function(){var e=u.call(arguments),t=e.shift();if(t==null)t="";return e.join(t)},lines:function(e){if(e==null)return[];return t(e).split("\n")},reverse:function(e){return p.chars(e).reverse().join("")},startsWith:function(e,n){if(n==="")return true;if(e==null||n==null)return false;e=t(e);n=t(n);return e.length>=n.length&&e.slice(0,n.length)===n},endsWith:function(e,n){if(n==="")return true;if(e==null||n==null)return false;e=t(e);n=t(n);return e.length>=n.length&&e.slice(e.length-n.length)===n},succ:function(e){if(e==null)return"";e=t(e);return e.slice(0,-1)+t.fromCharCode(e.charCodeAt(e.length-1)+1)},titleize:function(e){if(e==null)return"";return t(e).replace(/(?:^|\s)\S/g,function(e){return e.toUpperCase()})},camelize:function(e){return p.trim(e).replace(/[-_\s]+(.)?/g,function(e,t){return t.toUpperCase()})},underscored:function(e){return p.trim(e).replace(/([a-z\d])([A-Z]+)/g,"$1_$2").replace(/[-\s]+/g,"_").toLowerCase()},dasherize:function(e){return p.trim(e).replace(/([A-Z])/g,"-$1").replace(/[-_\s]+/g,"-").toLowerCase()},classify:function(e){return p.titleize(t(e).replace(/_/g," ")).replace(/\s/g,"")},humanize:function(e){return p.capitalize(p.underscored(e).replace(/_id$/,"").replace(/_/g," "))},trim:function(e,r){if(e==null)return"";if(!r&&n)return n.call(e);r=a(r);return t(e).replace(new RegExp("^"+r+"+|"+r+"+$","g"),"")},ltrim:function(e,n){if(e==null)return"";if(!n&&i)return i.call(e);n=a(n);return t(e).replace(new RegExp("^"+n+"+"),"")},rtrim:function(e,n){if(e==null)return"";if(!n&&r)return r.call(e);n=a(n);return t(e).replace(new RegExp(n+"+$"),"")},truncate:function(e,n,r){if(e==null)return"";e=t(e);r=r||"...";n=~~n;return e.length>n?e.slice(0,n)+r:e},prune:function(e,n,r){if(e==null)return"";e=t(e);n=~~n;r=r!=null?t(r):"...";if(e.length<=n)return e;var i=function(e){return e.toUpperCase()!==e.toLowerCase()?"A":" "},s=e.slice(0,n+1).replace(/.(?=\W*\w*$)/g,i);if(s.slice(s.length-2).match(/\w\w/))s=s.replace(/\s*\S+$/,"");else s=p.rtrim(s.slice(0,s.length-1));return(s+r).length>e.length?e:e.slice(0,s.length)+r},words:function(e,t){if(p.isBlank(e))return[];return p.trim(e,t).split(t||/\s+/)},pad:function(e,n,r,i){e=e==null?"":t(e);n=~~n;var s=0;if(!r)r=" ";else if(r.length>1)r=r.charAt(0);switch(i){case"right":s=n-e.length;return e+o(r,s);case"both":s=n-e.length;return o(r,Math.ceil(s/2))+e+o(r,Math.floor(s/2));default:s=n-e.length;return o(r,s)+e}},lpad:function(e,t,n){return p.pad(e,t,n)},rpad:function(e,t,n){return p.pad(e,t,n,"right")},lrpad:function(e,t,n){return p.pad(e,t,n,"both")},sprintf:h,vsprintf:function(e,t){t.unshift(e);return h.apply(null,t)},toNumber:function(e,n){if(e==null||e=="")return 0;e=t(e);var r=s(s(e).toFixed(~~n));return r===0&&!e.match(/^0+$/)?Number.NaN:r},numberFormat:function(e,t,n,r){if(isNaN(e)||e==null)return"";e=e.toFixed(~~t);r=r||",";var i=e.split("."),s=i[0],o=i[1]?(n||".")+i[1]:"";return s.replace(/(\d)(?=(?:\d{3})+$)/g,"$1"+r)+o},strRight:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.indexOf(n);return~r?e.slice(r+n.length,e.length):e},strRightBack:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.lastIndexOf(n);return~r?e.slice(r+n.length,e.length):e},strLeft:function(e,n){if(e==null)return"";e=t(e);n=n!=null?t(n):n;var r=!n?-1:e.indexOf(n);return~r?e.slice(0,r):e},strLeftBack:function(e,t){if(e==null)return"";e+="";t=t!=null?""+t:t;var n=e.lastIndexOf(t);return~n?e.slice(0,n):e},toSentence:function(e,t,n,r){t=t||", ";n=n||" and ";var i=e.slice(),s=i.pop();if(e.length>2&&r)n=p.rtrim(t)+n;return i.length?i.join(t)+n+s:s},toSentenceSerial:function(){var e=u.call(arguments);e[3]=true;return p.toSentence.apply(p,e)},slugify:function(e){if(e==null)return"";var n="ąàáäâãåæćęèéëêìíïîłńòóöôõøùúüûñçżź",r="aaaaaaaaceeeeeiiiilnoooooouuuunczz",i=new RegExp(a(n),"g");e=t(e).toLowerCase().replace(i,function(e){var t=n.indexOf(e);return r.charAt(t)||"-"});return p.dasherize(e.replace(/[^\w\s-]/g,""))},surround:function(e,t){return[t,e,t].join("")},quote:function(e){return p.surround(e,'"')},exports:function(){var e={};for(var t in this){if(!this.hasOwnProperty(t)||t.match(/^(?:include|contains|reverse)$/))continue;e[t]=this[t]}return e},repeat:function(e,n,r){if(e==null)return"";n=~~n;if(r==null)return o(t(e),n);for(var i=[];n>0;i[--n]=e){}return i.join(r)},levenshtein:function(e,n){if(e==null&&n==null)return 0;if(e==null)return t(n).length;if(n==null)return t(e).length;e=t(e);n=t(n);var r=[],i,s;for(var o=0;o<=n.length;o++)for(var u=0;u<=e.length;u++){if(o&&u)if(e.charAt(u-1)===n.charAt(o-1))s=i;else s=Math.min(r[u],r[u-1],i)+1;else s=o+u;i=r[u];r[u]=s}return r.pop()}};p.strip=p.trim;p.lstrip=p.ltrim;p.rstrip=p.rtrim;p.center=p.lrpad;p.rjust=p.lpad;p.ljust=p.rpad;p.contains=p.include;p.q=p.quote;if(typeof exports!=="undefined"){if(typeof module!=="undefined"&&module.exports){module.exports=p}exports._s=p}else if(typeof define==="function"&&define.amd){define("underscore.string",[],function(){return p})}else{e._=e._||{};e._.string=e._.str=p}}(this,String)
[ADWORDS]
; NOTE ALL values changed and not useable !! ONLY for demonstration purposes
; Required AdWords API properties. Details can be found at:
; https://developers.google.com/adwords/api/docs/guides/basic-concepts#soap_and_xml
developerToken = "oD23gWwevFVWchhpO-7wvVg"
clientCustomerId = "123-456-7890"
; Optional. Set a friendly application name identifier.
; userAgent = "INSERT_USER_AGENT_HERE"
; Optional additional AdWords API settings.
; endpoint = "https://adwords.google.com/"
; isPartialFailure = false
; Optional setting for utility usage tracking in the user agent in requests.
; Defaults to true.
; includeUtilitiesInUserAgent = true
[ADWORDS_REPORTING]
; Optional reporting settings.
; isSkipReportHeader = false
; isSkipColumnHeader = false
; isSkipReportSummary = false
; isUseRawEnumValues = false
[OAUTH2]
; Required OAuth2 credentials. Uncomment and fill in the values for the
; appropriate flow based on your use case. See the README for guidance:
; https://github.com/googleads/googleads-php-lib/blob/master/README.md#getting-started
; For installed application or web application flow.
clientId = "123475188799-mgdfj3tpv48rh6brnvaho12u6dvj3tmi.apps.googleusercontent.com"
clientSecret = "v7tHiUkAAnlAAA1f9Hjj1Ls"
refreshToken = "13vFgy3sivsdfjhs1lWMTD0-wGReRDSk52fgxsdAK4LfZ1s"
; For service account flow.
;jsonKeyFilePath = "/var/www/html/EE_600/source/tests_nr/json_api_checker.json"
;scopes = "https://www.googleapis.com/auth/adwords"
;impersonatedEmail = "INSERT_EMAIL_OF_ACCOUNT_TO_IMPERSONATE_HERE"
[SOAP]
; Optional SOAP settings. See SoapSettingsBuilder.php for more information.
; compressionLevel = <COMPRESSION_LEVEL>
[CONNECTION]
; Optional proxy settings to be used by requests.
; If you don't have username and password, just specify host and port.
; proxy = "protocol://user:pass@host:port"
; Enable transparent HTTP gzip compression for all reporting requests.
; enableReportingGzip = false
[LOGGING]
; Optional logging settings.
; soapLogFilePath = "path/to/your/soap.log"
; soapLogLevel = "INFO"
; reportDownloaderLogFilePath = "path/to/your/report-downloader.log"
; reportDownloaderLogLevel = "INFO"
; batchJobsUtilLogFilePath = "path/to/your/bjutil.log"
; batchJobsUtilLogLevel = "INFO"
<?php
/**
* TargetingIdeaService wrapper, SearchVolume
* ---------------------------------------------------------------------------------------------------------------------
*
* @copyright ©2018 Noriskshop
*
* @author noriskshop.de
*/
/**
* Requirements:
* adsapi_php.ini must be in the same path
* requirements specified in composer.json with composer install
*/
use Google\AdsApi\AdWords\AdWordsServices;
use Google\AdsApi\AdWords\v201806\o\TargetingIdeaService;
//// bootstrap from the shop main folder
$sTestFileFolderName = __DIR__ . '/';
ini_set('display_errors', '0');
// before installation check the location of the vendor folder
require __DIR__ . '/vendor/autoload.php';
// default location and language constants
$langID = '1001';
$locID = '2276';
// handle requested parameters
$keywords = $_GET['keywords'];
if ($keywords) {
$delim = ' ';
if (strpos($keywords, ',')) {
$delim = ',';
}
$aKeywords = explode($delim, $keywords);
if ($_GET['langId']) {
$langID = $_GET['langId'];
}
if ($_GET['locId']) {
$locID = $_GET['locId'];
}
}
else {
if (empty($_POST['keywords'])) {
// handle application/json
$_POST = json_decode(file_get_contents('php://input'), true);
}
$aKeywords = $_POST['keywords'];
$keywords = $aKeywords;
if (!is_array($keywords)) {
$delim = ' ';
if (strpos($keywords, ',')) {
$delim = ',';
}
$aKeywords = explode($delim, $keywords);
}
if ($_POST['langId']) {
$langID = (int) $_POST['langId'];
}
if ($_POST['locId']) {
$locID = (int) $_POST['locId'];
}
}
// search volume
if (is_array($aKeywords) && count($aKeywords)) {
// credentials
$ideaResults = array();
try {
$oAuth2Credential = (new \Google\AdsApi\Common\OAuth2TokenBuilder())
->fromFile(__DIR__ . '/adsapi_php.ini')
->build();
$session = (new \Google\AdsApi\AdWords\AdWordsSessionBuilder())
->fromFile(__DIR__ . '/adsapi_php.ini')
->withOAuth2Credential($oAuth2Credential)
->build();
// create and setup service
$adWordsServices = new AdWordsServices();
$targetingService = $adWordsServices->get($session, TargetingIdeaService::class);
$selector = new \Google\AdsApi\AdWords\v201806\o\TargetingIdeaSelector();
$selector->setSearchParameters(array(
new \Google\AdsApi\AdWords\v201806\o\LanguageSearchParameter('LanguageSearchParameter', array(new \Google\AdsApi\AdWords\v201806\cm\Language($langID))),
new \Google\AdsApi\AdWords\v201806\o\LocationSearchParameter('LocationSearchParameter', array(new \Google\AdsApi\AdWords\v201806\cm\Location($locID))),
new \Google\AdsApi\AdWords\v201806\o\RelatedToQuerySearchParameter('RelatedToQuerySearchParameter', $aKeywords),
new \Google\AdsApi\AdWords\v201806\o\NetworkSearchParameter('NetworkSearchParameter', new \Google\AdsApi\AdWords\v201806\cm\NetworkSetting(true, false, false, false))
));
$selector->setIdeaType('KEYWORD');
$selector->setRequestType('STATS');
$selector->setRequestedAttributeTypes(array('KEYWORD_TEXT', 'SEARCH_VOLUME'));
$selector->setPaging(new \Google\AdsApi\AdWords\v201802\cm\Paging(0, 100));
// call the service up
/** @var \Google\AdsApi\AdWords\v201806\o\TargetingIdeaPage $page */
$page = $targetingService->get($selector);
// parse the response
/** @var \Google\AdsApi\AdWords\v201806\o\TargetingIdea $targetingIdea */
foreach ($page->getEntries() as $targetingIdea) {
/** @var \Google\AdsApi\AdWords\v201806\o\Type_AttributeMapEntry $mapEntry */
foreach ($targetingIdea->getData() as $mapEntry) {
if ($mapEntry->getKey() == 'KEYWORD_TEXT') {
$word = $mapEntry->getValue()->getValue();
}
if ($mapEntry->getKey() == 'SEARCH_VOLUME') {
$volume = $mapEntry->getValue()->getValue();
}
$ideaResults[$word] = $volume;
}
}
} catch (\Exception $e) {
http_response_code(200);
exit( json_encode('Exception:' . print_r($e->getMessage(), true)));
}
// return and exit
http_response_code(200);
exit(json_encode($ideaResults));
}
?><!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0">
<title>SearchVolume</title>
</head>
<style>
</style>
<body>
<div class="container">
<div >
<div class="col-md-12 col-md-offset-3" align="center">
<form class="section" name="frmSearchVolume" method="post" action="">
<span style="text-align: left;"> Wrapper for Google TargetingIdeaService API. Usage: test_SearchVolume.php?keywords=keyword1+keyword2..., or by using this form</span>
<p></p>
<h4>Introduce keywords (separate with ,):</h4>
<textarea style="height:100px; width: 700px" name="keywords"></textarea>
<br>
<input type="submit" value="enter">
</form>
</div>
</div>
</div>
</body>
</html>
/*
* SEA-SEO-Report 2018
* @author: Christopher Gutknecht
* norisk Group
* cgutknecht@noriskshop.de
*/
/************ Configuration Block ************/
// MAKE A COPY of this DEMO SHEET: https://docs.google.com/spreadsheets/d/1jIMsEheJvpzpnTJZSkkVPcZL5_azD3NcWjrLY-LXtZ0/edit#gid=2086633269
// Paste the URL below:
var URL = "https://docs.google.com/spreadsheets/d/DEMO/edit#gid=2086633269";
// Enter your Google Analytics view ID to match organic URL data
var ANALYTICS_VIEW_ID = 36365748;
// Add your domain URL to matvh search console and analytics URL data
var DOMAIN = "https://www.lodenfrey.com";
// Set the time span of report data
var TIMESPAN = "LAST_30_DAYS"; // Choose : "LAST_7_DAYS", "LAST_14_DAYS", "LAST_30_DAYS"
var MIN_COMBINED_CLICKS = 5;
var USE_SEA_CPC_FOR_SEO = 1;
var STATIC_SEO_CPC = 0.5; // Only used if "USE_SEA_CPC_FOR_SEO" is set to 0
var BRAND_CAMPAIGN = "'brand__Brand'"; // Brand Campaign Identifier
var BRAND_TERM = "'Brand'"; // Exclude this brand term from your report
/************ Configuration Block ************/
var DEBUG_MODE = 0;
var AGGREGATE_BY_QUERY = 1;
var ACCOUNT_NAME = ""; // will be filled by AccountName of Google Ads
function main() {
var spreadsheet = SpreadsheetApp.openByUrl(URL);
getSeaQueryData(spreadsheet);
getSeaSeoData(spreadsheet);
if(AdWordsApp.getExecutionInfo().isPreview() === false) sendNotificationEmail();
}
//////////////////////////
/// FUNCTION DEFINITIONS
//////////////////////////
function getSeaQueryData(spreadsheet){
var sQNATsheet = spreadsheet.getSheetByName("(SEA)");
var seaReport = AdWordsApp.report(
"SELECT CampaignName, AdGroupName, Query, KeywordTextMatchingQuery, FinalUrl, Cost, Conversions, ConversionValue, AveragePosition, Impressions, Clicks, Ctr, AverageCpc " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE Clicks > 1 AND Cost > 1 AND CampaignName DOES_NOT_CONTAIN " + BRAND_CAMPAIGN + " AND Query DOES_NOT_CONTAIN_IGNORE_CASE " + BRAND_TERM + " AND CampaignStatus != REMOVED "+
"DURING " + TIMESPAN + " ");
seaReport.exportToSheet(sQNATsheet);
// Add concat column for lookup
if(AGGREGATE_BY_QUERY === 0) {
var sQNATlastReportRow = getLastReportRow(sQNATsheet);
var sQNATrowLength = sQNATlastReportRow - 2;
sQNATsheet.insertColumnBefore(1);
sQNATsheet.getRange(1,1,1,1).setValue("ConcatLookupValue");
var cellRangeConcat = sQNATsheet.getRange(2,1,sQNATrowLength,1);
cellRangeConcat.setFormula('=B2&"|||"&C2&"|||"&D2');
}
Logger.log("Added SEA Data.");
}
function getSeaSeoData(spreadsheet){
var fieldsWithCampAdgroup = "SearchQuery, AccountDescriptiveName, CombinedAdsOrganicClicks, AveragePosition, Impressions, Clicks, Ctr, AverageCpc, OrganicAveragePosition, OrganicImpressions, OrganicClicks, CampaignName, AdGroupName,KeywordTextMatchingQuery";
var fieldsNoCampAdgroup = "SearchQuery, AccountDescriptiveName, CombinedAdsOrganicClicks, AveragePosition, Impressions, Clicks, Ctr, AverageCpc, OrganicAveragePosition, OrganicImpressions, OrganicClicks";
var queryFields = AGGREGATE_BY_QUERY === 1 ? fieldsNoCampAdgroup : fieldsWithCampAdgroup;
var pAGQreport = AdWordsApp.report(
"SELECT " + queryFields + " " +
"FROM PAID_ORGANIC_QUERY_REPORT " +
"WHERE CombinedAdsOrganicClicks > " + (MIN_COMBINED_CLICKS-1) + " AND CampaignName DOES_NOT_CONTAIN " + BRAND_CAMPAIGN + " AND SearchQuery DOES_NOT_CONTAIN_IGNORE_CASE " + BRAND_TERM + " AND CampaignStatus != REMOVED "+
"DURING " + TIMESPAN + " ");
var pAGQsheet = spreadsheet.getSheetByName("SEA-SEO-Data");
pAGQsheet.clear();
// Setting header rows
var headersWithCampAdgroup = [["SearchQuery", "AccountDescriptiveName", "CombinedAdsOrganicClicks", "AveragePosition", "Impressions", "Clicks", "Ctr", "AverageCpc", "OrganicAveragePosition", "OrganicImpressions", "OrganicClicks", "CampaignName", "AdGroupName","KeywordTextMatchingQuery"]];
var headersNoCampAdgroup = [["SearchQuery", "AccountDescriptiveName", "CombinedAdsOrganicClicks", "AveragePosition", "Impressions", "Clicks", "Ctr", "AverageCpc", "OrganicAveragePosition", "OrganicImpressions", "OrganicClicks"]];
var headerValues = AGGREGATE_BY_QUERY === 1 ? headersNoCampAdgroup : headersWithCampAdgroup;
var headerRows = AGGREGATE_BY_QUERY === 1 ? 11 : 14;
var headerRange = pAGQsheet.getRange(1,1,1,headerRows).setValues(headerValues);
// pAGQreport.exportToSheet(pAGQsheet);
var seaSeoReportData = [], allSeaSeoQueries = [];
// Iterating through all entries do avoid duplicates
var pAGQReportRows = pAGQreport.rows();
try{
while(pAGQReportRows.hasNext()){
var row = pAGQReportRows.next();
if(ACCOUNT_NAME === "") ACCOUNT_NAME = row["AccountDescriptiveName"];
if(allSeaSeoQueries.indexOf(row["SearchQuery"]) == -1) {
allSeaSeoQueries.push(row["SearchQuery"]);
seaSeoReportData.push([
row["SearchQuery"], row["AccountDescriptiveName"],
row["CombinedAdsOrganicClicks"], row["AveragePosition"], row["Impressions"], row["Clicks"], row["Ctr"],
row["AverageCpc"], row["OrganicAveragePosition"], row["OrganicImpressions"], row["OrganicClicks"]
]);
}
}
var cellRange = pAGQsheet.getRange(2,1, seaSeoReportData.length,seaSeoReportData[0].length);
cellRange.setValues(seaSeoReportData);
pAGQsheet.insertColumnsAfter(2,5);
pAGQsheet.insertColumnsAfter(13,1);
pAGQsheet.insertColumnsAfter(17,3);
var pAGQlastReportRow = getLastReportRow(pAGQsheet);
var pAGQrowLength = pAGQlastReportRow - 2;
// Adding SEO-only keywords
var seoOnlyKeywords = getSeoOnlyKeywords(spreadsheet, pAGQrowLength);
// Recalculating last row
pAGQlastReportRow = getLastReportRow(pAGQsheet);
pAGQrowLength = pAGQlastReportRow - 2;
// Adding columns: SEA-SEO ratio
var cellRange = pAGQsheet.getRange(2,7,pAGQrowLength,1);
cellRange.setFormula("=iferror(K2/Q2,0)").setNumberFormat("0.0");
// Adding columns: SEA Cost
var lookupFormula = AGGREGATE_BY_QUERY === 1 ? "=iferror(sumif('(SEA)'!C:C,A2,'(SEA)'!F:F),)" : '=iferror(vlookup(U2&"|||"&V2&"|||"&A2,\'(SEA)\'!A:G,7,false),)';
var cellRange = pAGQsheet.getRange(2,14,pAGQrowLength,1);
cellRange.setFormula(lookupFormula).setNumberFormat("0.0 €");
// Adding columns: SEA Clicks
var lookupFormula = AGGREGATE_BY_QUERY === 1 ? "=iferror(sumif('(SEA)'!C:C,A2,'(SEA)'!K:K),)" : '=iferror(vlookup(U2&"|||"&V2&"|||"&A2,\'(SEA)\'!A:L,11,false),)';
var cellRange = pAGQsheet.getRange(2,11,pAGQrowLength,1);
cellRange.setFormula(lookupFormula).setNumberFormat("0");
// Adding columns: SEA Impressions
var lookupFormula = AGGREGATE_BY_QUERY === 1 ? "=iferror(sumif('(SEA)'!C:C,A2,'(SEA)'!J:J),)" : '=iferror(vlookup(U2&"|||"&V2&"|||"&A2,\'(SEA)\'!A:K,10,false),)';
var cellRange = pAGQsheet.getRange(2,10,pAGQrowLength,1);
cellRange.setFormula(lookupFormula).setNumberFormat("0");
// Adding columns: SEA CTR
var cellRange = pAGQsheet.getRange(2,12,pAGQrowLength,1);
cellRange.setFormula('=iferror(K2/J2,"")').setNumberFormat("0.00%");
// Adding columns: SEA CPC
var cellRange = pAGQsheet.getRange(2,13,pAGQrowLength,1);
cellRange.setFormula('=iferror(N2/K2,"")').setNumberFormat("0.00 €");
// Adding columns: Total Clicks
var cellRange = pAGQsheet.getRange(2,8,pAGQrowLength,1);
cellRange.setFormula('=iferror(K2+Q2,"")').setNumberFormat("0");
// Adding columns: SEA ROAS
var cellRange = pAGQsheet.getRange(2,3,pAGQrowLength,1);
cellRange.setFormula('=iferror(D2/N2,)').setNumberFormat("0.0");
// Adding columns: SEA Revenue
var lookupFormula = AGGREGATE_BY_QUERY === 1 ? "=iferror(sumif('(SEA)'!C:C,A2,'(SEA)'!H:H),)" : '=iferror(vlookup(U2&"|||"&V2&"|||"&A2,\'(SEA)\'!A:I,9,false),)';
var cellRange = pAGQsheet.getRange(2,4,pAGQrowLength,1);
cellRange.setFormula(lookupFormula).setNumberFormat("0.0 €");
// Adding columns: SEA URL
var lookupFormula = AGGREGATE_BY_QUERY === 1 ? "=iferror(vlookup(A2,'(SEA)'!C:E,3,false),)" : '=iferror(vlookup(U2&"|||"&V2&"|||"&A2,\'(SEA)\'!A:F,6,false),)';
var cellRange = pAGQsheet.getRange(2,19,pAGQrowLength,1);
cellRange.setFormula(lookupFormula);
// Adding columns: SEO URL
var cellRange = pAGQsheet.getRange(2,20,pAGQrowLength,1);
cellRange.setFormula('=iferror(vlookup(A2,\'(SEO_I)\'!A:B,2,false),"(noUrl)")');
// Adding columns: SEO CTR
var cellRange = pAGQsheet.getRange(2,18,pAGQrowLength,1);
cellRange.setFormula("=iferror(Q2/P2,\"\")").setNumberFormat("0.00%");
// Adding columns: SEO Revenue
var seoRevenueByQuery = getSeoRevenueByQuery(spreadsheet, pAGQsheet, pAGQrowLength);
var cellRange = pAGQsheet.getRange(2,6,pAGQrowLength,1);
cellRange.setValues(seoRevenueByQuery).setNumberFormat("0.0 €");
// Adding columns: SEO ROAS
var cellRange = pAGQsheet.getRange(2,5,pAGQrowLength,1);
var seoRoasFormula = USE_SEA_CPC_FOR_SEO === 1 ? "=iferror(F2/(if(M2=0,"+ STATIC_SEO_CPC +",M2)*Q2),)" : '=iferror(F2/((vlookup(A2,\'(SEO_I)\'!A:C,3,false)*' + STATIC_SEO_CPC + ',"")';
cellRange.setFormula(seoRoasFormula).setNumberFormat("0.0");
// Resetting column values: SEO Position
var cellRange = pAGQsheet.getRange(2,15,pAGQrowLength,1);
cellRange.setFormula('=iferror(vlookup(A2,\'(SEO_I)\'!A:F,6,false),)').setNumberFormat("0.0");
// Resetting column values: SEO Impressions
var cellRange = pAGQsheet.getRange(2,16,pAGQrowLength,1);
cellRange.setFormula('=iferror(vlookup(A2,\'(SEO_I)\'!A:D,4,false),)').setNumberFormat("0");
// Resetting column values: SEO Clicks
var cellRange = pAGQsheet.getRange(2,17,pAGQrowLength,1);
cellRange.setFormula('=iferror(vlookup(A2,\'(SEO_I)\'!A:C,3,false),)').setNumberFormat("0");
setSeaSeoSheetFormatting(pAGQsheet, pAGQrowLength);
} catch (e) { Logger.log(e + ". stack : " + e.stack); }
Logger.log("Added SEA SEO Data");
}
/*
* @param spreadsheet {object}
* @return {integer}
*/
function getLastReportRow(spreadsheet) {
var column = spreadsheet.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct] && values[ct][0] != "" ) {
ct++;
}
return (ct+1);
}
function getSeoOnlyKeywords(spreadsheet, pAGQrowLength){
var seoSheet = spreadsheet.getSheetByName("(SEO_I)");
var clickValues = seoSheet.getRange("C:C").getValues();
var lastRow;
for(var i=1; i<clickValues.length; i++) {
if(parseInt(clickValues[i][0]) == parseInt(MIN_COMBINED_CLICKS -1)) {lastRow = i+1; break;}
}
Logger.log("lastRow of SEO keywords : " + lastRow + " for combined clicks > " + (MIN_COMBINED_CLICKS-1));
var seoValues = seoSheet.getRange(2,1,lastRow,5).getValues();
var seaSeoSheet = spreadsheet.getSheetByName("SEA-SEO-Data");
var seaSeoQueries_2Dim = seaSeoSheet.getRange(2,1,pAGQrowLength,1).getValues();
var seaSeoQueries = [];
for(var i=0; i<seaSeoQueries_2Dim.length;i++){
seaSeoQueries.push(seaSeoQueries_2Dim[i][0]);
}
var seoValuesForSeaSeo = [];
for(var i=0; i < seoValues.length;i++) {
if(seaSeoQueries.indexOf(seoValues[i][0]) == -1 && seoValuesForSeaSeo.indexOf(seoValues[i][0]) == -1) {
seoValuesForSeaSeo.push([seoValues[i][0], ACCOUNT_NAME, "","" ,"" ,"" ,"" ,seoValues[i][2] ,"" ,"" /*10*/ ,"" ,"" ,"", "", "", "", "", "", "", "" /*20*/]);
}
}
var newSeoDataRange = seaSeoSheet.getRange(pAGQrowLength, 1, seoValuesForSeaSeo.length, seoValuesForSeaSeo[0].length).setValues(seoValuesForSeaSeo);
}
function getSeoRevenueByQuery(spreadsheet, pAGQsheet, pAGQrowLength) {
var seoSheet = spreadsheet.getSheetByName("(SEO_I)");
var seolastReportRow = getLastReportRow(seoSheet);
var seoRowLength = seolastReportRow - 2;
seoSheet.getRange("G1").setValue("Overall_URLClicks").setBackground("#ffff00");
seoSheet.getRange(2,7,seoRowLength,1).setFormula("=vlookup(B2,'(SEO_II_Page-Only)'!A:B,2,false)").setNumberFormat("0");
var seoData = seoSheet.getRange(2,1,seoRowLength,7).getValues();
// Get Query click weights by URL from GSC data in "(SEO)" tab
var seoQueryWeightsByUrl = {};
for (var i=0;i<seoData.length;i++) {
if(typeof seoQueryWeightsByUrl[seoData[i][1]] == "undefined") seoQueryWeightsByUrl[seoData[i][1]] = {};
var jointKey = seoData[i][0] + "|||" + seoData[i][1];
seoQueryWeightsByUrl[seoData[i][1]][jointKey] = seoData[i][2] / seoData[i][6];
}
// Get GA Revenue data by URL
var seoUrlRevenueResponse = getGASeoUrlRevenue();
var seoUrlRevenueObject = {};
for (var i=0; i<seoUrlRevenueResponse.rows.length; i++){
var urlKey = DOMAIN + seoUrlRevenueResponse.rows[i][0];
seoUrlRevenueObject[urlKey] = parseFloat(seoUrlRevenueResponse.rows[i][1]);
}
var seoUrls = pAGQsheet.getRange(2,20,pAGQrowLength,1).getValues();
var queryLookupArray = pAGQsheet.getRange(2,1,pAGQrowLength,1).getValues();
// Build SEO Partial revenue list
var seoRevenueByQuery = [];
for(var i=0; i<seoUrls.length; i++){
if(seoUrls[i][0] === "(noUrl)") { seoRevenueByQuery.push([0]); continue; }
try{
if(typeof seoUrlRevenueObject[seoUrls[i][0]] == "undefined") { seoRevenueByQuery.push([0]); continue;}
var totalUrlRevenue = seoUrlRevenueObject[seoUrls[i][0]];
var queryUrlKey = queryLookupArray[i][0] + "|||" + seoUrls[i][0];
var queryWeight = seoQueryWeightsByUrl[seoUrls[i][0]][queryUrlKey];
var partialRevenue = totalUrlRevenue * queryWeight;
if(DEBUG_MODE === 1) {Logger.log("\ntotalUrlRevenue : " + totalUrlRevenue.toFixed(2) + " | " + "queryUrlKey : " + queryUrlKey + "\nqueryWeight : " + queryWeight.toFixed(2) + " | " + "partialRevenue : " + partialRevenue.toFixed(2));}
seoRevenueByQuery.push([partialRevenue]);
} catch (e) {Logger.log(e + ".stack : " + e.stack); seoRevenueByQuery.push([0]);}
}
return seoRevenueByQuery;
}
function getGASeoUrlRevenue() {
var profileId = ANALYTICS_VIEW_ID;
var tableId = 'ga:' + profileId;
var days = parseInt(TIMESPAN.replace("LAST_","").replace("_DAYS"));
var startDate = getLastNdays(days);
var endDate = getLastNdays(0); // Today.
var optArgs = {
'dimensions': 'ga:landingPagePath', // Comma separated list of dimensions.
'sort': '-ga:transactionRevenue,ga:sessions',
// Use segment if necessary 'segment': '',
'filters': 'ga:source==google;ga:medium==organic;ga:sessions>0;ga:transactionRevenue>0',
'start-index': '1',
'max-results': '50000'
};
// Make a request to the API.
var results = Analytics.Data.Ga.get(
tableId, // Table id (format ga:xxxxxx).
startDate, // Start-date (format yyyy-MM-dd).
endDate, // End-date (format yyyy-MM-dd).
'ga:transactionRevenue,ga:sessions', // Comma seperated list of metrics.
optArgs);
if (results.getRows()) {return results;} else {throw new Error('No views (profiles) found');}
}
function getLastNdays(nDaysAgo) {
var today = new Date();
var before = new Date();
before.setDate(today.getDate() - nDaysAgo);
return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}
function setSeaSeoSheetFormatting(pAGQsheet, pAGQrowLength){
// SHEET FORMATTING
pAGQsheet.getRange(2,8,pAGQrowLength,1).setNumberFormat("0");
pAGQsheet.getRange(2,9,pAGQrowLength,1).setNumberFormat("0.0");
pAGQsheet.getRange(2,10,pAGQrowLength,1).setNumberFormat("0");
pAGQsheet.getRange(2,11,pAGQrowLength,1).setNumberFormat("0");
pAGQsheet.getRange(2,13,pAGQrowLength,1).setNumberFormat("0.00 €");
pAGQsheet.getRange(2,15,pAGQrowLength,1).setNumberFormat("0.0");
pAGQsheet.getRange(2,16,pAGQrowLength,1).setNumberFormat("0.0");
pAGQsheet.getRange(2,16,pAGQrowLength,1).setNumberFormat("0");
pAGQsheet.getRange(2,17,pAGQrowLength,1).setNumberFormat("0");
var headersWithCampAdgroup = [["SearchQuery", "Account", "SEA_ROAS", "SEA_Revenue", "SEO_ROAS", "SEO_Revenue", "SEA-SEO_ClickRatio", "TOTAL_Clicks", "SEA_AvgPos" , "SEA_Imps" , "SEA_Clicks" , "SEA_Ctr" , "SEA_AvgCpc", "SEA_Cost" , "SEO_AvgPos" , "SEO_Imps", "SEO_Clicks" , "SEO_CTR" , "SEA_URL" , "SEO_URL", "SEA_Camp" , "SEA_Adgroup" , "SEA_Keyword"]];
var headersNoCampAdgroup = [["SearchQuery", "Account", "SEA_ROAS", "SEA_Revenue", "SEO_ROAS", "SEO_Revenue", "SEA-SEO_ClickRatio", "TOTAL_Clicks", "SEA_AvgPos" , "SEA_Imps" , "SEA_Clicks" , "SEA_Ctr" , "SEA_AvgCpc", "SEA_Cost" , "SEO_AvgPos" , "SEO_Imps", "SEO_Clicks" , "SEO_CTR" , "SEA_URL" , "SEO_URL"]];
var headerValues = AGGREGATE_BY_QUERY === 1 ? headersNoCampAdgroup : headersWithCampAdgroup;
var headerRows = AGGREGATE_BY_QUERY === 1 ? 20 : 23;
var headerRange = pAGQsheet.getRange(1,1,1,headerRows).setValues(headerValues);
// Setting header range background colors: GREY = #d9d9d9, GREEN = #d9ead3, YELLOW = #ffff00, BLUE = #c9daf8
pAGQsheet.getRange("A1:B1").setBackground("#d9d9d9").setHorizontalAlignment("center").setFontWeight("normal"); // GREY
pAGQsheet.getRange("C1:D1").setBackground("#c9daf8").setHorizontalAlignment("center").setFontWeight("normal"); // BLUE = SEA
pAGQsheet.getRange("E1:F1").setBackground("#ffff00").setHorizontalAlignment("center").setFontWeight("normal"); // YELLOW = SEO
pAGQsheet.getRange("G1:H1").setBackground("#d9ead3").setHorizontalAlignment("center").setFontWeight("normal"); // GREEN = Overall
pAGQsheet.getRange("I1:S1").setBackground("#c9daf8").setHorizontalAlignment("center").setFontWeight("normal"); // BLUE = SEA
pAGQsheet.getRange("O1:T1").setBackground("#ffff00").setHorizontalAlignment("center").setFontWeight("normal"); // YELLOW = SEO
if(AGGREGATE_BY_QUERY === 0) pAGQsheet.getRange("U1:W1").setBackground("#c9daf8").setHorizontalAlignment("center").setFontWeight("normal"); // YELLOW = SEO
var currentMaxColumns = pAGQsheet.getMaxColumns();
pAGQsheet.deleteColumns(24, currentMaxColumns-24);
}
function sendNotificationEmail(){
var htmlBody = '<html><body>Your Paid & Organic Query Report for ' + DOMAIN + ' is available at <br>' + SPREADSHEET_URL + '.</body></html>';
var date = new Date();
var subject = 'Paid & Organic Query Report for ' + DOMAIN + ' ' + date;
var body = subject;
var options = { htmlBody : htmlBody };
for(var i in NOTIFY) {
MailApp.sendEmail(NOTIFY[i], subject, body, options);
Logger.log("An Email has been sent.");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.