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

# Codebeispiel für R - Website Boosting Ausgabe 66
# Aus der Serie "R Leuchtungen", Teil 5
# Alle Zeilen, die mit # beginnen, dienen nur der Erklaerung
# und werden von R ignoriert.

### Teil 2 des Skrips

# Hinweis: Ohne den ersten Teil des Skrips macht ein Start dieses Teils keinen Sinn

# SF-Export wieder einlesen
crawl <- read_csv("internal_all.csv") %>%
  clean_names() %>% # Spaltennamen normalisieren
  select(address, status_code, title_1, meta_description_1, h1_1) # Nur die fuer uns interessanten Spalten behalten


# GSC-Daten und Crawl-Daten des Screaming Frogs verbinden und pruefen, ob das Keyword (query) in
# T&Ds / H1 enthalten ist
abgleich <- top_query_page %>%
  inner_join(crawl, by = c("page" = "address")) %>% # DataFrames zusammenfuehren
  filter(status_code == 200) %>% # Da wir historische Daten einbeziehen, kann sich der Status Code von URLs mittlerweile geaendert haben
  select(-status_code) %>% # Spalte mit dem Status Code wird nicht mehr benoetigt
  mutate(across(c(title_1, meta_description_1, h1_1), tolower), # Zu Kleinschreibung normalisieren
         across(c(title_1, meta_description_1, h1_1), ~str_replace_all(.x, "[:punct:]", " ")), # Symbole durch Leerzeichen ersetzen
         across(c(title_1, meta_description_1, h1_1), str_squish), # Mehrfache Leerzeichen, die durch Ersetzung der Symbole entstanden sind, zu einem Leerzeichen reduzieren
         
         across(c(title_1, meta_description_1, h1_1), ~case_when(str_detect(.x, query) ~ "enthalten", TRUE ~ "nicht enthalten"), .names = "query_in_{.col}")) %>% # Pruefen, ob Query in T&D/h1 vorhanden ist
  
  mutate(across(c(ctr, position), ~round(.x, 3))) %>% # CTR und Position auf drei Nachkommastellen runden
  
  arrange(query_in_title_1, query_in_meta_description_1, query_in_h1_1)

# Schnelles Plotting: Wie schauen die Verhaeltnisse jeweils aus?
abgleich %>%
  select(starts_with("query_in_")) %>%
  pivot_longer(everything()) %>%
  ggplot(aes(name, fill = value)) +
  geom_bar(position = "fill") +
  labs(title = "Anteile der enthaltenen Queries",
       x = NULL,
       y = NULL,
       fill = "Query enthalten?") +
  scale_y_continuous(labels = scales::percent_format())

# CSV-Datei als Export erzeugen
write_excel_csv2(abgleich, "abgleich.csv", na = "")

# Ende--------------------------------------------------------------