Paid Organic rapportage

November 5, 2023
Dashboard

Met de rapportage van Paid en Organic kunnen gebruikers hun zoekwoorden analyseren en verbeteren, omdat zowel betaalde als organische zoekopdrachten in één en hetzelfde rapport worden gepresenteerd, waardoor ze naast elkaar kunnen worden vergeleken.

Het rapport stelt de gebruiker ook in staat om de effectiviteit van zijn advertenties te bekijken waar de gebruiker combineert met een betaalde advertentie en een organische vermelding op dezelfde webpagina.

 


Het rapport is een combinatie van twee type scripts, namelijk 1) Google Ads script en 2) Google App script. De Google Ads script haalt de data op van alle zoekwoorden waarop wordt geboden. De impressies, clicks, conversies en spend.

De Google App script heeft een koppeling met de Google Search Console API en kan de organische impressies, clicks en posities ophalen. Binnen 15 minuten heb je dit rapportage geautomatiseerd, omdat het mogelijk is om beide scripts elke maand te laten runnen. En zo kun je dit dashboard ook in Looker Studio plaatsen door de spreadsheet te koppelen als bron in Looker Studio.
 

Hoe pas je het script toe?

Je hebt de volgende properties nodig:

  1. Google Search Console
  2. Google Ads
  3. Google Console Cloud
     

Stap 1: Maak een project aan in Google Console Cloud


Maak een project aan in Google Console Cloud. Het is een goed idee om het project de naam van een domein mee te geven. Zoek naar: Search Console API
 


Als je de API nog niet hebt ingeschakeld, doe dat dan eerst. Klik door en ga naar ‘Credentials’. Klik op ‘+ CREATE CREDENTIALS’ en klik op ‘API key:
 


Je kunt de API key nu kopiëren. Die heb je nodig voor het script. Voeg de API key toe in Google App script. Maak een kopie van deze sheet. Navigeer naar Uitbreidingen > Apps script
 


 

function main() {
    const sheet_url = "https://docs.google.com/spreadsheets/d/1ZjwOxSR4_th7egQoc3FjOAfn9xiGg6g321AUgPx__yU/copy"
    const tab_name = "Data"
    const days = 7
    const start_day = 3

    const sheet = SpreadsheetApp.openByUrl(sheet_url).getSheetByName(tab_name)
    let arr = sheet.getDataRange().getValues()
    let data = {}
    arr.filter((v, i) => i != 0).map((v, i) => (data[v[0]] = i + 2))
    const end = Utilities.formatDate(new Date(Date.now() - start_day * 86400000), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")
    const start = Utilities.formatDate(new Date(Date.now() - (start_day + days - 1) * 86400000), AdsApp.currentAccount().getTimeZone(), "yyyy-MM-dd")
    let obj = {}
    const result = []

    const searchResults = AdsApp.search(`SELECT ad_group_criterion.keyword.match_type,
                                                ad_group_criterion.keyword.text,
                                                metrics.impressions,
                                                metrics.clicks,
                                                metrics.cost_micros,
                                                metrics.conversions
                                         from keyword_view
                                         WHERE metrics.impressions > 0
                                           AND segments.date >= '${start}'
                                           AND segments.date <= '${end}'`)
    for (let row of searchResults) {
        id = row.adGroupCriterion.resourceName
        const info = [id,
            getKw(row.adGroupCriterion.keyword.text, row.adGroupCriterion.keyword.matchType),
            row.metrics.impressions,
            row.metrics.clicks,
            row.metrics.impressions == 0 ? 0 : (parseInt(row.metrics.clicks) / parseInt(row.metrics.impressions)).toFixed(2),
            row.metrics.clicks == 0 ? 0 : (costMicros(row.metrics.costMicros) / parseInt(row.metrics.clicks)).toFixed(2),
            costMicros(row.metrics.costMicros).toFixed(2),
            row.metrics.conversions
        ]


        if (data[id] != null) {
            sheet.getRange(data[id], 1, 1, info.length).setValues([info])
            const pos = parseFloat(arr[data[id] - 1][10])
            delete data[id]
        } else {
            result.push(info)
        }
    }

    Object.values(data).map(i => sheet.getRange(i, 3, 1, 6).setValues([[0, 0, 0, 0, 0, 0]]))

    if (result.length > 0)
        sheet.getRange(arr.length + 1, 1, result.length, result[0].length).setValues(result)

    arr = sheet.getDataRange().getValues()

    for (let i = 1; i < arr.length; i++) {
        if (arr[i][10] == "" || arr[i][10] == 0) continue
        const pos = parseFloat(arr[i][10])
        Logger.log(`The keyword ${arr[i][1]} has position in organic search ${pos}`)
        if (pos <= 2) {
            applyLabel(arr[i][0], "pos 1-2")
            continue
        }
        if (pos <= 5) {
            applyLabel(arr[i][0], "pos 3-5")
        }
    }


}

function getKw(text, match) {
    if (match === "EXACT") return `[${text}]`
    if (match === "PHRASE") return `"${text}"`
    return text
}

function costMicros(cost) {
    return parseInt(cost || 0) / 1000000
}

function applyLabel(resource, label) {
    let kwIterator = AdsApp.keywords().withResourceNames([resource]).get()
    if (!kwIterator.hasNext()) {
        Logger.log(`The script didnt find a keyword with resource name ${resource}`)
        return
    }
    const keyword = kwIterator.next()
    labelCheck(label)
    keyword.applyLabel(label)
}


function labelCheck(Labelname) {
    const labelIterator = AdsApp.labels()
        .withCondition(`label.name = "${Labelname}"`)
        .get();
    if (!labelIterator.hasNext()) {
        AdsApp.createLabel(Labelname)
    }
} 


Klik op ‘getpropertieforuser’ en voer het script uit. Dan komen alle properties naar voren die gekoppeld zijn aan jouw e-mailadres. Overal waar je toegang hebt tot Google Search Console:

 


Selecteer het domein volledig. Dus als je domein bestaat uit: ‘sc-domain:domeinnaam.nl’, kopieer dit volledig en voeg die toe aan regel 2: const url = {hier plaatsen}. Sla het script op en geef het een naam.

Ga nu terug naar je Google sheet. En klik op: Script. Nu gaat het script runnen:
 


Stap 2: Google Ads script

Ga nu naar Google Ads. Voeg onderstaande script toe als Google Ads script:
 

// const url = "XXX"
const url = "XXX"
const days = 30
const start = 3
const timezone = "GMT+1"
const tab_name= "Data"
GSC_APIKEY = 'AIzaSyBHWjcEZTBqGaNCj-hF13JpGUChXXXXXXc'


function getpropertiesforuser() {
 const oauthToken = ScriptApp.getOAuthToken();
 const url = 'https://searchconsole.googleapis.com/webmasters/v3/sites?key=' + GSC_APIKEY


 const headers = {
   'Authorization': 'Bearer ' + oauthToken,
   'Content-Type': 'application/json'
 }
 const options = {
   headers: headers,
   method: 'GET',
 }
 const response = UrlFetchApp.fetch(url, options).getContentText()
 const json = JSON.parse(response)


 for(i = 0; i < json.siteEntry.length; i++){
     Logger.log(json.siteEntry[i]['siteUrl'])
 }
}




function getConsoleInfo() {
const endDate = Utilities.formatDate(new Date(Date.now() - start*86400000), timezone, "yyyy-MM-dd")
const startDate = Utilities.formatDate(new Date(Date.now() - (days+start-1)*86400000), timezone, "yyyy-MM-dd")
Logger.log(startDate)
Logger.log(endDate)
const url_query = `https://www.googleapis.com/webmasters/v3/sites/${encodeURIComponent(url)}/searchAnalytics/query`
const token = ScriptApp.getOAuthToken();
Logger.log(token)
const headers = { Authorization: 'Bearer ' + token };
const payload = {
endDate,
startDate,
"dimensions": ["query"]
};
const options = {
"headers": headers,
"method": "POST",
"muteHttpExceptions": true,
"contentType": "application/json",
"payload": JSON.stringify(payload),
};




const response = UrlFetchApp.fetch(url_query, options);
Logger.log(response)
const result = JSON.parse(response.getContentText());
Logger.log(result.rows)
const map = new Map()
result.rows.map(i=>map.set(i.keys[0],[i.clicks,i.impressions, (parseFloat(i.position)).toFixed(2)]))
Logger.log([...map.entries()])
const sh=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tab_name)
const arr=sh.getDataRange().getValues().map((v,i)=>({index:i+1,kw:clean(v[1].toString())}))
arr.shift()
for (let row of arr){
  if (map.has(row.kw)){
   sh.getRange(row.index,9,1,3).setValues([map.get(row.kw)])
   continue
  }
  sh.getRange(row.index,9,1,3).setValues([[0,0,0]])
}
Logger.log(arr)
}




const clean = (str) => str.replace(/\[|\]|\"/g, "");


Voeg de sheet URL toe in regel 2: const sheet_url. Zet de frequentie op de eerste dag van de maand tussen 09:00 - 10:00. 

Proces van beide scripts

Je hebt nu alles klaar. Het doel is, dat als Google Ads script runt, daarna je Google Apps script laat runnen. Op de eerste van de maand. En het Google Apps script run je twee uur later. Met een trigger is dat eenvoudig in te stellen.
 


 Maak een trigger aan. Zorg dat deze 2 uur later dan de Google Ads script wordt gerund:
 


 

jermaya leijen
Jermaya Leijen arrow icon

In de loop der jaren heb ik de liefde ontwikkeld voor automation, AI en data(analyse). Hoewel ik me altijd heb gericht op Google Ads, ben ik al geruime tijd betrokken bij uiteenlopende SEO-projecten. Ik heb mezelf Python en JavaScript aangeleerd om automation en data(verwerking) te kunnen integreren in zowel mijn eigen projecten als die van klanten, met als doel hen te ondersteunen bij het behalen van hun bedrijfs- en omzetdoelstellingen.

Cases Blogs Audit Tooling