Skript, který porovnává organické dotazy z GA s dotazy z Adwords a hledá příležitosti

1 dubna, 2016 od Jakub Kašparů 24 Comments

Co skript dělá:

Načítá aktuální dotazy z Google Adwords, na které se zobrazujete.
Zároveň načítá organické vyhledávací dotazy, které porovnává s reportem z Google Anaylytics.
Dotazy, které nemáte v kampaní, Vám zapíše do sešitu, včetně vstupní stránky dotazu.

Jak nasadit:

Na řádek 2 vložte ID Vašeho profilu v Google Analytics, které najdete v sekci v Google Analytics > Správce > Nastavení výběru dat > ID výběru dat – příkladem 1223542
Na řádek 3 zkopírujte URL nového sešitu na Google Disku – příkladem „url sešitu“
Na řádek 4 vložte do uvozovek kořen domény – příkladem „http://mujweb.cz“
Jakmile skript vložíte do Adwords, je nutné povolit pokročilá rozhraní a API Google Analytics.
Následně ještě povolit Google Analytics API v rámci Developer Console.

//VARIABLESS//
var ANALYTICS_ID = 12456; // Vložte GA ID profilu
var SPREADSHEET_URL = ""; // Vložte URL sešitu
var YourDomain = ""; // Vložte URL domény

function main () {
  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  spreadsheet.setSpreadsheetLocale("en");
  var dataSheet = createOrGetSheet(spreadsheet,"Data",0);
  var lPreport = createOrGetSheet(spreadsheet,"Vstupní stránky",1);
  if(dataSheet.getRange("A1").isBlank() == true) {
    dataSheet.getRange("A1:C1").setValues([["Klíčové slovo","Vstupní stránka","Návštěvy GA"]]);
    dataSheet.deleteColumns(4,20);
  } else {
    dataSheet.getRange(2,1,dataSheet.getLastRow(),3).clear();
  }
  var forTotalResultNum = runReport(ANALYTICS_ID,1).totalResults;
  var adwordsData = adwReportObject();
  for(i=1;i< forTotalResultNum;i = i + 1000) {
    var data = runReport(ANALYTICS_ID,i);
    for(a=0; a < data['rows'].length; a++) { var kw = data['rows'][a][1]; if(kw == "(not provided)") { continue; } var sessions = data['rows'][a][3]; var landingPage = YourDomain + data['rows'][a][2]; if(adwordsData[kw] == undefined && adwordsData[kw] != "(not provided)") { dataSheet.appendRow([kw,landingPage,sessions]); } } } var formula = lPreport.getRange("A10"); formula.setFormula('=QUERY(Data!A:C,"select B,count(A),sum(C) group by B order by sum(C)desc")'); } function adwReportObject() { var AdwordsData = {}; var report = AdWordsApp.report( " SELECT Query, KeywordTextMatchingQuery, CampaignName, AdGroupName, Impressions, Clicks, AverageCpc, Ctr, Cost, AllConversions, CostPerAllConversion, AllConversionValue" + " FROM SEARCH_QUERY_PERFORMANCE_REPORT"+ " WHERE Impressions > 10"+
  " AND AdGroupStatus = ENABLED" + 
  " AND CampaignStatus = ENABLED" + 
  " DURING LAST_30_DAYS");
  var data = report.rows();
  while(data.hasNext()) {
    var row = data.next();
    var query = row.Query;
    AdwordsData[query] = row;
  }
  return AdwordsData;
}

function runReport(profileId,paging) {
  var today = new Date();
  var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

  var startDate = Utilities.formatDate(oneWeekAgo, "GTM - 1",
      'yyyy-MM-dd');
  var endDate = Utilities.formatDate(today, "GTM - 1",
      'yyyy-MM-dd');

  var tableId  = 'ga:' + profileId;
  var metric = 'ga:visits';
  var options = {
    'dimensions': 'ga:source,ga:keyword,ga:landingPagePath',
    'sort': '-ga:visits,ga:source',
    'filters': 'ga:medium==organic',
    'start-index': paging
  };
  var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,
      options);

  return report;

}

function createOrGetSheet(reportSpreadsheet, name, position) {
  var sheet = reportSpreadsheet.getSheetByName(name);
  if(sheet == null) {
    return reportSpreadsheet.insertSheet(name,position);
  } else {
    return sheet;
  }
}

//VARIABLESS//

var ANALYTICS_ID = 12456; // Vložte GA ID profilu

var SPREADSHEET_URL = ""; // Vložte URL sešitu

var YourDomain = ""; // Vložte URL domény

function main () {

var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

spreadsheet.setSpreadsheetLocale("en");

var dataSheet = createOrGetSheet(spreadsheet,"Data",0);

var lPreport = createOrGetSheet(spreadsheet,"Vstupní stránky",1);

if(dataSheet.getRange("A1").isBlank() == true) {

dataSheet.getRange("A1:C1").setValues([["Klíčové slovo","Vstupní stránka","Návštěvy GA"]]);

dataSheet.deleteColumns(4,20);

} else {

dataSheet.getRange(2,1,dataSheet.getLastRow(),3).clear();

}

var forTotalResultNum = runReport(ANALYTICS_ID,1).totalResults;

var adwordsData = adwReportObject();

for(i=1;i< forTotalResultNum;i = i + 1000) {

var data = runReport(ANALYTICS_ID,i);

for(a=0; a < data['rows'].length; a++) { var kw = data['rows'][a][1]; if(kw == "(not provided)") { continue; } var sessions = data['rows'][a][3]; var landingPage = YourDomain + data['rows'][a][2]; if(adwordsData[kw] == undefined && adwordsData[kw] != "(not provided)") { dataSheet.appendRow([kw,landingPage,sessions]); } } } var formula = lPreport.getRange("A10"); formula.setFormula('=QUERY(Data!A:C,"select B,count(A),sum(C) group by B order by sum(C)desc")'); } function adwReportObject() { var AdwordsData = {}; var report = AdWordsApp.report( " SELECT Query, KeywordTextMatchingQuery, CampaignName, AdGroupName, Impressions, Clicks, AverageCpc, Ctr, Cost, AllConversions, CostPerAllConversion, AllConversionValue" + " FROM SEARCH_QUERY_PERFORMANCE_REPORT"+ " WHERE Impressions > 10"+

" AND AdGroupStatus = ENABLED" +

" AND CampaignStatus = ENABLED" +

" DURING LAST_30_DAYS");

var data = report.rows();

while(data.hasNext()) {

var row = data.next();

var query = row.Query;

AdwordsData[query] = row;

}

return AdwordsData;

}

function runReport(profileId,paging) {

var today = new Date();

var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

var startDate = Utilities.formatDate(oneWeekAgo, "GTM - 1",

'yyyy-MM-dd');

var endDate = Utilities.formatDate(today, "GTM - 1",

'yyyy-MM-dd');

var tableId = 'ga:' + profileId;

var metric = 'ga:visits';

var options = {

'dimensions': 'ga:source,ga:keyword,ga:landingPagePath',

'sort': '-ga:visits,ga:source',

'filters': 'ga:medium==organic',

'start-index': paging

};

var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,

options);

return report;

}

function createOrGetSheet(reportSpreadsheet, name, position) {

var sheet = reportSpreadsheet.getSheetByName(name);

if(sheet == null) {

return reportSpreadsheet.insertSheet(name,position);

} else {

return sheet;

}

Comments

Klaudie says

29 dubna, 2016 at 2:35 pm

Ahoj, moc ráda bych script zkusila, ale vůbec si nevím rady s body „Jakmile skript vložíte do Adwords, je nutné povolit pokročilá rozhraní a API Google Analytics.“ a „Následně ještě povolit Google Analytics API v rámci Developer Console.“
Myslíš, že bys mohl popsat, kde tyto možnosti najít a co všechno a jak nastavit?
Díky moc za opověď.

Odpovědět
- Jakub Kašparů says
  
  29 dubna, 2016 at 4:12 pm
  
  Přidal jsem youtube video s návodem 🙂
  
  Odpovědět
  - Klaudie says
    
    29 dubna, 2016 at 4:12 pm
    
    Boží! Díky
    
    Odpovědět
Tomáš Kapler says

29 dubna, 2016 at 5:20 pm

v titulku by místo jednoho z těch tří „z“ mělo být asi jedno „s“ 🙂

Odpovědět
- Jakub Kašparů says
  
  29 dubna, 2016 at 5:30 pm
  
  Pravda, děkuji za komentář 🙂
  
  Odpovědět
Honza says

27 května, 2016 at 12:32 pm

Dobrý den,
udělal jsem vše podle videa, v adwords mi ale vyjela chyba :
Náhled byl dokončen s chybami. Podrobnosti naleznete v protokolech. (00:21)

Odpovědět
- Klaudie says
  
  27 května, 2016 at 1:15 pm
  
  Jakou chybu protokoly ukazují? Často to nechce projít, pokud je script spouštěn z jiného účtu, než na kterém se nachází sheet.
  
  Odpovědět
  - Honza says
    
    27 května, 2016 at 1:32 pm
    
    Díky za pomoc. Stav: Nedokončeno: Chyba uživatele
    
    Odpovědět
  - Honza says
    
    29 srpna, 2016 at 11:45 am
    
    Stejný problém – adwords účet i sešit jsou na jednom účtu.
    
    Odpovědět
Michal Královič says

16 června, 2016 at 12:55 pm

Ahojte,

@Jakub – nebolo by výhodnejšie načítať data o kľúčových slovách, vyhľadávaniach a vstupnej stránke zo Search Console? Nakoľko v Google Analytics sa zobrazuje len malé percento týchto slov a väčšina spadá do not provided?

vďaka

Odpovědět
- Jakub Kašparů says
  
  4 srpna, 2016 at 9:02 am
  
  Ahoj, bylo ale API Search Console není integrované do appscriptů a bylo by to o dost náročnější 🙂
  
  Odpovědět
- Danča says
  
  5 září, 2018 at 8:54 am
  
  jj přesně, to by bylo rozhodně lepší pracovat se Search consolí, takhle u mě 99,11 % klíčových slov se to vůbec netýká, kvůli tomu 1 % to nemá moc smysl ani nastavovat.. Nepřemýšlíš Jakube přeci jen o té náročnější verzi? to by bylo super, protože nápad je to víc než zajímavý. díky Danča
  
  Odpovědět
Ondřej says

17 července, 2016 at 4:46 pm

Ahojte, hází mně to takový problém: Access Not Configured. Google Analytics API has not been used in project 424855998226 before or it is disabled.
I když jsem povolil Analytics API, tak žádná změna. Nevíte co s tím?

Odpovědět
- Vláďa Smitka says
  
  18 července, 2016 at 10:15 pm
  
  Zkuste ještě povolit Analytics Reporting API, jestli to pomůže – https://console.developers.google.com/apis/api/analyticsreporting.googleapis.com/overview
  
  Odpovědět
Robert says

18 července, 2016 at 3:04 pm

Ahoj, prosím o pomoc. Mám stejný problém jako Honza. Píše mi to:

Náhled byl dokončen s chybami. Podrobnosti naleznete v protokolech.

v protokolech je napsáno:

User does not have sufficient permissions for this profile. (line 51)

Jsem přihlášen přes MCC na účtě, na kterém script jede. Nevíte, co je špatně?

Díky moc.

Odpovědět
- Jakub Kašparů says
  
  18 července, 2016 at 10:37 pm
  
  Podle mě Google Analytics účet provozujete na jiném emailu než Google Adwords, díky tomu, se tam skript nemůže dostat. Adwords i Analytics musí být na stejném účtu.
  
  Odpovědět
  - Robert says
    
    19 července, 2016 at 9:15 am
    
    Děkuji za odpověď, nasdílel jsem si GA na svůj Adwords účet a už to jede.
    
    Odpovědět
Robert says

19 července, 2016 at 2:58 pm

Ještě jeden dotaz: Pokud nastavím spouštění skriptu např. na každý den, budou se mi předešlé vyhledávací dotazy smazávat, tzn. budu tam mít pouze vyhledávací dotazy, na které byly reklamy spuštěny předešlých 24 hodin, nebo tam staré vyhledávací dotazy zůstávají? Jen přemýšlím, jak často je nejlepší nechat skript spouštět.

Odpovědět
- Ondra says
  
  22 července, 2016 at 12:33 pm
  
  Sheet se vždy přemaže (if na řádcích 11 – 16), a bere data za posledních 30 dní (řádek 24), nejvhodnější tedy bude spouštět ho každý měsíc. Bylo by možné spouštět ho týdně (přepsat řádek 24 na LAST_7_DAYS), případně i denně a exportovat každý den do nového listu, případně do několika a točit dokola (pondělí – neděle). Otázkou je velikost účtu a jestli se taková frekvence vyplatí.
  
  Odpovědět
  - Robert says
    
    22 července, 2016 at 12:48 pm
    
    Supr, díky za odpověď. Nejlepší bude asi to týdenní spouštění, pokud chci účet aktivně optimalizovat.
    
    Odpovědět
Pavel Trojánek says

26 září, 2017 at 2:46 pm

Invalid reporting query: SEARCH_QUERY_PERFORMANCE_REPORT. (line 21)

Kde může být chyba v tomto případě? (Vše nastaveno podle YouTube návodu a vše běží na stejných emailových účtech. Díky. 🙂

Odpovědět
- Jan says
  
  23 července, 2018 at 12:02 pm
  
  Pavle, jak jste to vyřešil?
  
  Odpovědět
Robert Macků says

21 března, 2018 at 2:19 pm

Ahoj,
změnili teď přístup do Analytics API, už nelze prokliknout na enable, je zde možnost pouze try this API, dále pod tím píší: IAM: you have insufficient permissions to enable or disable services and APIs for this project. Contact a project owner to request permissions.

Nevíš, co s tím? Kde mohu povolit nyní Analytics API? Nebo jsou scripty potřebující Analytics API nyní nepoužitelné bez vyšších oprávnění.

Díky,

Robert

Odpovědět
Jan says

23 července, 2018 at 12:02 pm

Invalid reporting query: SEARCH_QUERY_PERFORMANCE_REPORT. (file Code.gs, line 21)

Stejný problém. 🙁

Odpovědět

Reader Interactions

Comments

Napsat komentář Zrušit odpověď na komentář