Google Webmaster Console Keyword Position Report Program in Java

Google Webmaster Console Keyword Position Report Program in Java: This post will be very helpful for one who has website and already configured in google webmaster console. Google webmaster console is a place whee you can add your sites sitemap and check the keyword position, total impressions and clicks of each keyword details.

 

This java program will compare current month and previous month google webmaster console report and give you

1. Keywords which started appearing newly
2. Keywords which stopped appearing
3. Keyword trend sheet (which shares the details about your keyword, whether it is bad/better/same comparing to last month and the number of positions it reduced or increased)

 

3 Reports will be generated when you run this java program with the proper input google webmaster CSV file:

1. disappeared-keywords.xlsx
2. new-keywords.xlsx
3. keyword-trends.xlsx

 

 

Prerequisite:

• Need two reports downloaded in CSV format (one for last month and one for this month) from your google webmaster console portal.
• Need to include all the required jars to generate Excel report. (refer below post for the jars)

 

How to Read Excel File xlsx in Java using Apache Poi?

See more

 

 

You can take the report by setting custom date range for the required months.

 

Input sheet sample format:

 

Export the reports from google webmaster console by selecting all the columns (clicks, impressions, ctr and position).

 

Google Webmaster Pojo Class [to set and get all the values]

[java]

package in.javadomain;

public class GWCPojo {

String query;

String position;

String ctr;

String clicks;

String impressions;

String trend;
// posible values – same | better | bad

Double positionUpDownValue;

public Double getPositionUpDownValue() {
return positionUpDownValue;
}

public void setPositionUpDownValue(Double positionUpDownValue) {
this.positionUpDownValue = positionUpDownValue;
}

public String getQuery() {
return query;
}

public String getTrend() {
return trend;
}

public void setTrend(String trend) {
this.trend = trend;
}

public void setQuery(String query) {
this.query = query;
}

public String getPosition() {
return position;
}

public void setPosition(String position) {
this.position = position;
}

public String getCtr() {
return ctr;
}

public void setCtr(String ctr) {
this.ctr = ctr;
}

public String getClicks() {
return clicks;
}

public void setClicks(String clicks) {
this.clicks = clicks;
}

public String getImpressions() {
return impressions;
}

public void setImpressions(String impressions) {
this.impressions = impressions;
}

}

[/java]

 

 

 

Java Program to Generate the Reports:

[java]

package in.javadomain;

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadGWC {

public static void main(String[] args) {
ReadGWC gwc = new ReadGWC();
gwc.genKeywordsRelatedSheets();
Set<GWCPojo> lstMntSet = gwc.getGWCVo(“C:\\last_month.csv”);
Set<GWCPojo> tisMntSet = gwc.getGWCVo(“C:\\this_month.csv”);

Set<GWCPojo> posGWCPojos = gwc.genPositionTrendReport(tisMntSet,lstMntSet);
gwc.writePosTrendExcel(posGWCPojos);
}

private void writePosTrendExcel(Set<GWCPojo> posGWCPojos){
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(“New Keywords”);

int rowCount = 0;
Row row = sheet.createRow(rowCount++);
Cell cell = row.createCell(0);
cell.setCellValue(“Search Keyword”);
cell = row.createCell(1);
cell.setCellValue(“Comments”);
cell = row.createCell(2);
cell.setCellValue(“Result Down / Up by”);
for (GWCPojo gwcPojo : posGWCPojos) {
row = sheet.createRow(rowCount++);

int columnCount = 0;
cell = row.createCell(columnCount++);
if (gwcPojo.getQuery() instanceof String) {
cell.setCellValue((String) gwcPojo.getQuery());
}

cell = row.createCell(columnCount++);
if (gwcPojo.getTrend() instanceof String) {
cell.setCellValue((String) gwcPojo.getTrend());
}

cell = row.createCell(columnCount++);
if (gwcPojo.getPositionUpDownValue() instanceof Double) {
cell.setCellValue((Double) gwcPojo.getPositionUpDownValue());
}

}

try (FileOutputStream outputStream = new FileOutputStream(
“C:\\keyword-trends.xlsx”)) {
workbook.write(outputStream);
System.out.println(“Keyword Trend Sheet Written!”);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

private Set<GWCPojo> getGWCVo(String fileLoc) {
String splitBy = “,”;
String[] csvContents = null;
Set<GWCPojo> gwcVoSet = new HashSet<GWCPojo>();
String eachLine = “”;
try {
BufferedReader br = new BufferedReader(new FileReader(fileLoc));
int i = 0;
while ((eachLine = br.readLine()) != null) {
if (i == 0) {
eachLine = br.readLine();
i++;
}
csvContents = eachLine.split(splitBy);
GWCPojo gwcPojo = new GWCPojo();
if (csvContents.length == 5) {
gwcPojo.setQuery(csvContents[0].trim());
gwcPojo.setClicks(csvContents[1].trim());
gwcPojo.setPosition(csvContents[4].trim());
gwcPojo.setImpressions(csvContents[2].trim());
gwcPojo.setCtr(csvContents[3].trim());
gwcVoSet.add(gwcPojo);
gwcPojo = new GWCPojo();
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return gwcVoSet;
}

private Set<String> getKeywordsLstFromCSV(String fileLoc) {
String splitBy = “,”;
String[] csvContents = null;
Set<String> keyWordsLst = new HashSet<String>();
String eachLine = “”;
try {
BufferedReader br = new BufferedReader(new FileReader(fileLoc));
int i = 0;
while ((eachLine = br.readLine()) != null) {
if (i == 0) {
eachLine = br.readLine();
i++;
}
csvContents = eachLine.split(splitBy);
if (csvContents.length == 5) {
keyWordsLst.add(csvContents[0].trim());
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return keyWordsLst;
}

private Set<GWCPojo> genPositionTrendReport(Set<GWCPojo> tisMntSet,Set<GWCPojo> lstMntSet){
Set<GWCPojo> excelWriteSet = new HashSet<GWCPojo>();
for (GWCPojo thisMnth : tisMntSet) {
GWCPojo exclPojo = new GWCPojo();
for (GWCPojo lstMnth : lstMntSet) {
if (thisMnth.getQuery().equalsIgnoreCase(lstMnth.getQuery())) {
if(null!=(thisMnth.getPosition()) && null!=(lstMnth.getPosition())){
if (Double.parseDouble(thisMnth.getPosition()) > Double.parseDouble(lstMnth.getPosition())) {
exclPojo.setQuery(thisMnth.getQuery());
exclPojo.setClicks(thisMnth.getClicks());
exclPojo.setCtr(thisMnth.getCtr());
exclPojo.setImpressions(thisMnth.getImpressions());
exclPojo.setPosition(thisMnth.getPosition());
exclPojo.setTrend(“Better”);
exclPojo.setPositionUpDownValue(Double.parseDouble(lstMnth.getPosition())-Double.parseDouble(thisMnth.getPosition()));
excelWriteSet.add(exclPojo);
exclPojo = new GWCPojo();
}else if (Double.parseDouble(thisMnth.getPosition()) == Double.parseDouble(lstMnth.getPosition())) {
exclPojo.setQuery(thisMnth.getQuery());
exclPojo.setClicks(thisMnth.getClicks());
exclPojo.setCtr(thisMnth.getCtr());
exclPojo.setImpressions(thisMnth.getImpressions());
exclPojo.setPosition(thisMnth.getPosition());
exclPojo.setTrend(“Same”);
exclPojo.setPositionUpDownValue(Double.parseDouble(lstMnth.getPosition())-Double.parseDouble(thisMnth.getPosition()));
excelWriteSet.add(exclPojo);
exclPojo = new GWCPojo();
} else {
exclPojo.setQuery(thisMnth.getQuery());
exclPojo.setClicks(thisMnth.getClicks());
exclPojo.setCtr(thisMnth.getCtr());
exclPojo.setImpressions(thisMnth.getImpressions());
exclPojo.setPosition(thisMnth.getPosition());
exclPojo.setTrend(“Bad”);
excelWriteSet.add(exclPojo);
exclPojo.setPositionUpDownValue(Double.parseDouble(lstMnth.getPosition())-Double.parseDouble(thisMnth.getPosition()));
exclPojo = new GWCPojo();
}
}
}
}
}
return excelWriteSet;
}

private void genKeywordsRelatedSheets() {
System.out.println(“Keyword List Preparation started!”);
String thisMonthFile = “C:\\last_month.csv”;
String lastMonthFile = “C:\\this_month.csv”;
// Delimiter of CSV file
ReadGWC gwc = new ReadGWC();
Set<String> thisMonthKeywordsLst = gwc.getKeywordsLstFromCSV(thisMonthFile);
Set<String> lastMonthKeywordsLst = gwc.getKeywordsLstFromCSV(lastMonthFile);
Set<String> commonKeywordsLst = new HashSet<String>();
Set<String> keywordsStartedAppearningNewly = new HashSet<String>();
Set<String> keywordsDisappeard = new HashSet<String>();

for (String thisMonthKeyword : thisMonthKeywordsLst) {
for (String lastMontKeyword : lastMonthKeywordsLst) {
if (lastMontKeyword.equalsIgnoreCase(thisMonthKeyword)) {
commonKeywordsLst.add(thisMonthKeyword);
}
}
}
// These keywords started appearning
keywordsStartedAppearningNewly.addAll(thisMonthKeywordsLst);
keywordsStartedAppearningNewly.removeAll(commonKeywordsLst);
gwc.writeNewKeywords(keywordsStartedAppearningNewly);
// These keywords stopped Appearning
keywordsDisappeard.addAll(lastMonthKeywordsLst);
keywordsDisappeard.removeAll(commonKeywordsLst);
gwc.writeDisppearedKeywords(keywordsDisappeard);
// cross checking
for (String newKW : keywordsStartedAppearningNewly) {
for (String remKW : keywordsDisappeard) {
if (newKW.equalsIgnoreCase(remKW)) {
System.out.println(“something went wrong!”);
}
}
}
System.out.println(“Keyword List Prepared!”);
}

private void writeNewKeywords(Set<String> newKeywordsLst) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(“New Keywords”);

int rowCount = 1;
for (String newKeyword : newKeywordsLst) {
Row row = sheet.createRow(rowCount++);

int columnCount = 0;
Cell cell = row.createCell(columnCount++);
if (newKeyword instanceof String) {
cell.setCellValue((String) newKeyword);
}
}

try (FileOutputStream outputStream = new FileOutputStream(
“C:\\new-keywords.xlsx”)) {
workbook.write(outputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

private void writeDisppearedKeywords(Set<String> disappearedCoupons) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(“Disappeared Keywords”);

int rowCount = 1;
for (String newKeyword : disappearedCoupons) {
Row row = sheet.createRow(rowCount++);

int columnCount = 0;
Cell cell = row.createCell(columnCount++);
if (newKeyword instanceof String) {
cell.setCellValue((String) newKeyword);
}
}

try (FileOutputStream outputStream = new FileOutputStream(
“C:\\disappeared-keywords.xlsx”)) {
workbook.write(outputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

}

[/java]

 

Generated Output Sheet Snippets:

keyword-trend sheet.xlsx:

 

New keywords.xlsx:

 

 

Disappeared Keywords.xlsx:

 

 

 

Feel free to share your comments below.