# www.websiteboosting.com / Patrik Luerwer & Mario Fischer

# Codebeispiel fuer R - Website Boosting Ausgabe 68
# Mehrfachrankings / Keyword-Kannibalisierung
# Aus der Serie "R Leuchtungen", Teil 7 V2
# Alle Zeilen, die mit # beginnen, dienen nur der Erklaerung
# und werden von R ignoriert.

# Hinweis. Die sechs nachfolgenden Librarys muessen einmalig installiert
# werden. Wie das geht, steht im Heft! Danach kann man die beiden Zeilen loeschen.

# Achtung: Wenn Sie die vorherigen Teile unserer Serie noch nicht
# durchgespielt haben, müssen Sie die/einige Librarys einmalig für R installieren.
# Dazu entfernen Sie die Doppelkreuze vor "install.packages....".
# R wird dann ggf. nochmal neu starten wollen. Ist halt so. 
# Anschließend können Sie die # wieder vorne anstellen, sonst werden die immer
# wieder installiert. Das ist lästig. Es geht auch einfacher, mehr dazu im Heft!

# install.packages ("httr")
# install.packages ("rvest")
# install.packages ("janitor")
# install.packages ("tidyverse")
# install.packages ("googleAuthR")
# install.packages ("searchConsoleR")

# Auch wenn die Librarys bereits installiert wurden, muessen sie beim
# jedem Neustart von R auch aufgerufen bzw. in den Speicher von R geladen werden:

library(httr)
library(rvest)
library(janitor)
library(tidyverse)
library(googleAuthR)
library(searchConsoleR)


# Konfiguration  -------------------------------------------------

# TODO: Pfad zum Authentifizierungsfiles *.JSON eintragen
PATH_TO_KEY_FILE <- "hier-geben-Sie-den-Pfad zur Datei an, die in etwa so aussieht: /name-cf8346ad9w18.json"

# TODO: Berichtszeitraum definieren
START_DATE <- "2021-01-01"
END_DATE <- "2021-04-30"

# TODO: Property eintragen
GSC_PROP <- "https://www.Ihre-Domain.de/"

# TODO: Brand-Queries eintragen, die ausgeschlossen werden sollen
# Hier als Beispiel für Website Boosting drei Schreibweisen
# Die überschreiben Sie einfach, ggf. mittlere Zeile kopieren
# und neu einfügen, wenn Sie mehr Zeilen brauchen.
BRAND_QUERIES <- c("websiteboosting",
                   "website boosting",
                   "websiteboosting.com")


# Rowlimit der GSC-Abfrage ggf. hochsetzen, wenn mehr Daten vorhanden sind
ROW_LIMIT <- 100000

# Schwellwert der Impressions definieren, die eine
# Query-Gruppe in Summe aufweisen muss, um beibehalten zu werden.
# Dient dazu, das "Rauschen" durch wenig nachgefragte Queries zu minimieren
THRESHOLD_IMPRESSIONS <- 100

# Konfiguration Ende ----------------------------------------------
# Ab hier kann alles komplett ohne Benutzeränderungen ablaufen

# GSC-Authentifizierung & Abfrage -------------------------------

options(googleAuthR.scopes.selected = "https://www.googleapis.com/auth/webmasters.readonly")

gar_auth_service(PATH_TO_KEY_FILE)


# GSC-Daten abfragen
gsc_data_raw <- search_analytics(siteURL = GSC_PROP,
                                 startDate = START_DATE,
                                 endDate = END_DATE,
                                 dimensions = c("query", "page"),
                                 searchType = "web",
                                 rowLimit = ROW_LIMIT) %>%
  as_tibble()


# GSC-Daten aufbereiten
gsc_data <- gsc_data_raw %>%
  filter(
    # Brand-Queries herausfiltern
    !str_detect(query, paste0("(", paste(BRAND_QUERIES, collapse = "|"), ")")),
    # Rankings von URLs mit Fragments und GA-Tracking-Parametern herausfiltern
    !str_detect(page, "(#|utm_)")
  ) %>%
  group_by(query) %>%
  filter(
    # Nur doppelte Queries behalten
    n() > 2,
    # Nur Query-Gruppen behalten, die in Summe den definierten Schwellwert überschreiten
    sum(impressions) >= THRESHOLD_IMPRESSIONS
  ) %>%
  ungroup()



# Status Code und T&Ds abfragen ---------------------------------

# Funktion zur Abfrage des Status Codes / der T&Ds / der H1 definieren
get_status_td_h1 <- function(url) {

  # Fallback-Dataframe, wenn URL nicht antwortet / nicht geparst werden kann
  df_fallback <- tibble(page = url,
                        status_code = NA_integer_,
                        title =  NA_character_,
                        description = NA_character_,
                        h1 = NA_character_)


  # Funktion zur sicheren Abfrage der URL
  safe_get <- safely(GET, otherwise = NULL)

  # Funktion zur Abfrage des Status Codes / der T&Ds / der H1
  get_df <- function(r) {

    status_code <- r %>%
      status_code()

    title <- r %>%
      content() %>%
      html_node(xpath = "//title") %>%
      html_text()

    description <- r %>%
      content() %>%
      html_node(xpath = "//meta[@name='description']") %>%
      html_attr("content")

    h1 <- r %>%
      content() %>%
      html_node(xpath = "//h1") %>%
      html_text()

    df <- tibble(page = url,
                 status_code = status_code,
                 title = title,
                 description = description,
                 h1 = h1)

    return(df)

  }


  # Abfrage der URL
  message(paste0("Requesting: ", url, "\n"))

  r <- safe_get(url)$result

  if (is.null(r)) {

    # Wenn Response leer, Fallback-Dateframe verwenden
    df <- df_fallback

    return(df)

  } else {

    # Versuch, Status Code / T&Ds / H1 zu ermitteln. Wenn er
    # scheitert, Fallback-Dataframe verwenden
    df <- tryCatch(
      {

        get_df(r)

      },
      error = function(cond) {

        message(paste0("\nAn ERROR occured for: ", url, " =====\nHere's the original error message:\n", cond, "\n"))

        return(df_fallback)

      },
      warning = function(cond) {

        message(paste0("\nA WARNING occured for: ", url, " =====\nHere's the original error message:\n", cond, "\n"))

        return(df_fallback)
      }
    )

    return(df)
  }

}


# Abfrage des Status Codes / der T&Ds / H1
titles_descriptions <- map_dfr(gsc_data %>%
                                 distinct(page) %>%
                                 pull(page),
                               get_status_td_h1)



# Daten zusammenführen und filtern ------------------------------

# GSC-Daten und Status Code / T&Ds / H1 zusammenführen und aufbereiten
gsc_title_desctiption_h1 <- gsc_data %>%
  left_join(titles_descriptions, by = "page") %>%
  arrange(query, position) %>%
  mutate(across(where(is.numeric), ~round(.x, 3))) %>%
  # Entfernen aller Query-Gruppen, bei denen ein Ranking auf Position 1 liegt
  group_by(query) %>%
  filter(!any(position < 2)) %>%
  ungroup() %>%
  select(query, page, position, clicks, impressions, ctr, title, description, h1, status_code)


# Visuelle Trenner zwischen den Query-Gruppen erzeugen, um sie
# einfacher in Excel unterscheiden zu können
l_export <- list()
unique_queries <- unique(gsc_title_desctiption_h1$query)

for (i in 1:length(unique_queries)) {

  q <- unique_queries[i]

  df <- gsc_title_desctiption_h1 %>%
    filter(query == q) %>%
    add_row(query = str_dup("*", 10))

  l_export[[i]] <- df
}

export <- bind_rows(l_export)


# Daten in CSV schreiben
write_excel_csv2(export, "keyword_kannibalisierung.csv", na = "")

### Ende des des Skrips
### Für weitere Infos siehe Beitrag im Heft