Sinds september 2020 zijn niet meer alle zoektermen zichtbaar in Google Ads, zogenaamd omwille van privacy van de gebruikers volgens Google. Dit biedt een flinke uitdaging voor SEA specialisten, omdat je moet sturen op ontbrekende cijfers. Helaas kunnen we ook niet met scripts dit probleem oplossen, maar onderstaand script biedt wel inzicht in de impact van deze ontbrekende zoektermen in accounts.
Onderstaand script is gebaseerd op het Search Query Illuminator script van Smarter Ecommerce. Dit script kan je toepassen op MCC-niveau. Hiermee krijg je snel per account te zien welk percentage van de vertoningen, klikken, kosten, conversies en conversiewaarde toegeschreven kan worden aan onbekende zoektermen. Hoewel je hiermee niet beter kan sturen, kan je dit wel gebruiken ter onderbouwing richting (interne) stakeholders of bij een eerste analyse van een account.
Je vind de resultaten van het script in de log.
Instellingen
DATE_RANGE: Geef aan over hoeveel dagen terug het script de statistieken moet verzamelen.
ACCOUNT_LABEL: Gebruik accountlabels om maximaal 50 accounts te selecteren.
ADNETWORKTYPE: Je kan de rapportage aanpassen op Ad Network. De mogelijkheden zijn SEARCH, CONTENT (Display), MIXED, YOUTUBE_SEARCH, YOUTUBE_WATCH.
The script
// Copyright 2021. Increase BV. All Rights Reserved. License: MIT
//
// Original script by Smarter Ecommerce GmbH. All rights reserved.
// Link: https://smarter-ecommerce.com/en/ppc-scripts/search-query-illuminator/
// Changes by: Tibbe van Asten
//
// Created: 20-05-2021
// Last update: 03-06-2021
//
// ABOUT THE SCRIPT
// This script gives you insights on the impact of hidden search queries
// in your account. See what percentage of impressions, clicks, cost
// and conversion is generated by unknows search queries.
//
////////////////////////////////////////////////////////////////////
var config = {
DATE_RANGE : last_n_days(30),
// Use accountlabels to select specific account. Leave empty to select all account
// A script can loopt through 50 account maximum
ACCOUNT_LABEL : 'QS - 1',
// Optionally you may adjust the Ad Network Type used. The default is "SEARCH".
// Other possible options are "CONTENT" (for Display Network), "YOUTUBE_SEARCH", "YOUTUBE_WATCH", "MIXED"
ADNETWORKTYPE : "SEARCH"
}
////////////////////////////////////////////////////////////////////
function main() {
var accountIterator = AdsManagerApp
.accounts()
.withLimit(50);
if(config.ACCOUNT_LABEL != ""){
accountIterator = accountIterator.withCondition("LabelNames CONTAINS '" + config.ACCOUNT_LABEL + "'");
}
accountIterator = accountIterator.get();
while(accountIterator.hasNext()){
var account = accountIterator.next();
AdsManagerApp.select(account);
processClientAccount(account);
} // accountIterator
afterProcessAllClientAccounts();
} // function main
////////////////////////////////////////////////////////////////////
function last_n_days(n) {
var from = new Date(), to = new Date();
to.setUTCDate(from.getUTCDate() - n);
return googleDateRange(from, to);
} // function last_n_days()
////////////////////////////////////////////////////////////////////
function googleDateRange(from, to) {
function googleFormat(date) {
var date_array = [date.getUTCFullYear(), date.getUTCMonth() + 1, date.getUTCDate()];
if (date_array[1] < 10) date_array[1] = '0' + date_array[1];
if (date_array[2] < 10) date_array[2] = '0' + date_array[2];
return date_array.join('');
}
var inverse = (from > to);
from = googleFormat(from);
to = googleFormat(to);
var result = [from, to];
if (inverse) {
result = [to, from];
}
return result;
} // function googleDateRange()
////////////////////////////////////////////////////////////////////
function processClientAccount(account) {
var aggregatedData = initializeEmptyAggregatedDataObject();
// Collect data
var query1 = "SELECT Clicks, Impressions, Cost, Conversions, ConversionValue " +
"FROM ACCOUNT_PERFORMANCE_REPORT " +
"WHERE AdNetworkType1 = " + config.ADNETWORKTYPE +
" DURING " + config.DATE_RANGE;
aggregateDataFromTotals(AdsApp.report(query1).rows(), "DATA");
var query2 = "SELECT Month, Clicks, Impressions, Cost, Conversions, ConversionValue " +
"FROM SEARCH_QUERY_PERFORMANCE_REPORT " +
"WHERE Query!='' AND AdNetworkType1 = " + config.ADNETWORKTYPE +
" DURING " + config.DATE_RANGE;
aggregateDataFromSearchQueries(AdsApp.report(query2).rows(), "DATA");
Logger.log("Account: " + account.getName());
Logger.log("Report for Ad Network Type " + config.ADNETWORKTYPE);
Logger.log("--------------------------------");
Logger.log("All impressions: " + (aggregatedData["DATA"].totalImpressions) + " | Impressions from unknown search terms: " + (aggregatedData["DATA"].totalImpressions - aggregatedData["DATA"].impressionsFromKnownQueries).toFixed(0) + " => " + (((aggregatedData["DATA"].totalImpressions - aggregatedData["DATA"].impressionsFromKnownQueries) / aggregatedData["DATA"].totalImpressions)*100).toFixed(2) + "%");
Logger.log("All clicks: " + (aggregatedData["DATA"].totalClicks) + " | Clicks from unknown search terms: " + (aggregatedData["DATA"].totalClicks - aggregatedData["DATA"].clicksFromKnownQueries).toFixed(0) + " => " + (((aggregatedData["DATA"].totalClicks - aggregatedData["DATA"].clicksFromKnownQueries) / aggregatedData["DATA"].totalClicks)*100).toFixed(2) + "%");
Logger.log("All costs: " + (aggregatedData["DATA"].totalCost.toFixed(2)) + " | Cost from unknown search terms: " + (aggregatedData["DATA"].totalCost - aggregatedData["DATA"].costFromKnownQueries).toFixed(2) + " => " + (((aggregatedData["DATA"].totalCost - aggregatedData["DATA"].costFromKnownQueries) / aggregatedData["DATA"].totalCost)*100).toFixed(2) + "%");
Logger.log("All conversions: " + (aggregatedData["DATA"].totalConversions.toFixed(2)) + " | Conversions from unknown search terms: " + (aggregatedData["DATA"].totalConversions - aggregatedData["DATA"].conversionsFromKnownQueries).toFixed(2) + " => " + (((aggregatedData["DATA"].totalConversions - aggregatedData["DATA"].conversionsFromKnownQueries) / aggregatedData["DATA"].totalConversions)*100).toFixed(2) + "%");
Logger.log("All conversionvalue: " + (aggregatedData["DATA"].totalConversionValue.toFixed(2)) + " | Conversionvalue from unknown search terms: " + (aggregatedData["DATA"].totalConversionValue - aggregatedData["DATA"].conversionValueFromKnownQueries).toFixed(2) + " => " + (((aggregatedData["DATA"].totalConversionValue - aggregatedData["DATA"].conversionValueFromKnownQueries) / aggregatedData["DATA"].totalConversionValue)*100).toFixed(2) + "%");
Logger.log("--------------------------------");
Logger.log(" ");
////////////////////////////////////////////////////////////////////
function aggregateDataFromTotals(rowIterator, period){
while(rowIterator.hasNext()) {
var row = rowIterator.next();
aggregatedData[period].totalClicks += parseFloat(row["Clicks"].replace(",",""));
aggregatedData[period].totalImpressions += parseFloat(row["Impressions"].replace(",",""));
aggregatedData[period].totalCost += parseFloat(row["Cost"].replace(",",""));
aggregatedData[period].totalConversions += parseFloat(row["Conversions"].replace(",",""));
aggregatedData[period].totalConversionValue += parseFloat(row["ConversionValue"].replace(",",""));
}
} // function aggregateDataFromTotals
////////////////////////////////////////////////////////////////////
function aggregateDataFromSearchQueries(rowIterator, period){
while(rowIterator.hasNext()) {
var row = rowIterator.next();
aggregatedData[period].clicksFromKnownQueries += parseFloat(row["Clicks"].replace(",",""));
aggregatedData[period].impressionsFromKnownQueries += parseFloat(row["Impressions"].replace(",",""));
aggregatedData[period].costFromKnownQueries += parseFloat((row["Cost"].replace(",","")));
aggregatedData[period].conversionsFromKnownQueries += parseFloat(row["Conversions"].replace(",",""));
aggregatedData[period].conversionValueFromKnownQueries += parseFloat(row["ConversionValue"].replace(",",""));
}
} // function aggregateDataFromSearchQueries
////////////////////////////////////////////////////////////////////
function initializeEmptyAggregatedDataObject() {
var aggregatedData = {};
["DATA"].forEach(function(period){
aggregatedData[period] = {
"totalClicks": 0,
"clicksFromKnownQueries": 0,
"totalImpressions": 0,
"impressionsFromKnownQueries": 0,
"totalCost": 0,
"costFromKnownQueries": 0,
"totalConversions": 0,
"conversionsFromKnownQueries": 0,
"totalConversionValue": 0,
"conversionValueFromKnownQueries": 0
};
});
return aggregatedData;
} // function initializeEmptyAggregatedDataObject()
} // function processClientAccount()
////////////////////////////////////////////////////////////////////
function afterProcessAllClientAccounts(){
Logger.log("Thanks for using this script by Tibbe van Asten. Winning!");
} // function afterProcessAllClientAccounts
Show whole script!
Loading Comments
The Experts
Tibbe van AstenTeam Lead Performance Marketing
Nils RooijmansWater Cooler Topics
Martijn KraanFreelance PPC Specialist
Bas BaudoinTeamlead SEA @ Happy Leads
Jermaya LeijenDigital Marketing Strategist
Krzysztof BycinaPPC Specialist from Poland
How about you?JOIN US!
Caring
Adsscripts.com staat voor het delen van kennis. In de huidige markt houden SEA-specialisten de kennis en ervaring graag voor zich. Wij zijn er van overtuigd dat het delen van kennis ervoor kan zorgen dat iedereen beter wordt in haar of zijn werk. Daarom lopen wij hier graag in voorop, door onze kennis over scripts te delen met iedereen.
Wil jij ook graag een bijdrage leveren? Wij staan open voor nieuwe ideeën en feedback op alles wat je op Adsscripts.com vindt.
