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

# Codebeispiel fuer R - Website Boosting Ausgabe 69
# Aus der Serie "R Leuchtungen", Teil 8
# Dieses Skript macht aus Google Search Console Daten Grafiken und Tabellen
# 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 Librarys einmalig für R installieren.
# Dazu entfernen Sie die beiden Doppelkreuze vor "instal....".
# 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("tidyverse")
# install.packages("janitor")
# install.packages("cowplot")
# install.packages("santoku")
# install.packages("glue")
# install.packages("zoo")

# Nachfolgended werden die installierten Librarys in den Speicher von R geladen:

library(tidyverse)
library(janitor)
library(cowplot)
library(santoku)
library(glue)
library(zoo)


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

# Damit man zwischen Suchtreffern des Domainnamens/der Marke
# unterscheiden kann, nachfolgend eigene Begriffe und typische
# Schreibweisen eintragen:

# TODO: Schreibweisen der Brand-Suchanfragen eintragen
BRAND_QUERIES <- c("website boosting",
                   "websiteboosting.com",
                   "websiteboosting",
                   "websitebooosting")

# Die nachfolgenden Angaben erscheinen bei den Grafiken
# als Legende, damit man weiß, welcher Zeitraum betroffen ist

# TODO: Abfragezeitraum eintragen
START_DATE <- "2020-07-06"
END_DATE <- "2021-07-06"



# Ab hier laufen die Berechnungen und die Erzeugung der
# Grafiken und Tabellen
# Laden der Daten aus dem Arbeitsverzeichnis ------------------------------

gsc_pages <- read_csv("Seiten.csv") %>% 
  clean_names() %>% 
  mutate(ctr = parse_number(ctr) / 100) %>% 
  filter(!str_detect(die_haufigsten_seiten, "#")) # Sitelink- / Sprungmarken-URLs entfernen


gsc_queries <- read_csv("Suchanfragen.csv") %>% 
  clean_names() %>% 
  mutate(
    ctr = parse_number(ctr) / 100,
    brand = case_when(str_detect(haufigste_suchanfragen, paste0("(", paste(BRAND_QUERIES, collapse = "|"), ")")) ~ "Brand",
                      TRUE ~ "Non-Brand"))


gsc_countries <- read_csv("Länder.csv") %>% 
  clean_names() %>% 
  mutate(ctr = parse_number(ctr) / 100)


gsc_timeseries <- read_csv("Zeiträume.csv") %>% 
  clean_names() %>% 
  mutate(ctr = parse_number(ctr) / 100)



# Berechnung der Ranking-Verteilung --------------------------------

# Anreicherung der Daten mit Position-Bins und SERP
gsc_pages_ranking_distribution <- gsc_pages %>%
  mutate(
    position_bin = chop_width(position, width = 1, start = 1),
    serp = as_factor(ceiling(position / 10))
  )

gsc_queries_ranking_distribution <- gsc_queries %>% 
  mutate(
    position_bin = chop_width(position, width = 1, start = 1),
    serp = as_factor(ceiling(position / 10))
  )


# Seiten ********************************************************
## Position 1 - 10

#Erklären!!!! HIer mehr via API, da kommt alles

gsc_pages_ranking_distribution %>% 
  filter(position < 11) %>%
  ggplot(aes(position_bin)) +
  geom_bar(fill = "blue", alpha = .8) +
  theme_minimal_hgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Seiten: Rankingverteilung der ersten SERP",
       subtitle = "Auf welchen Positionen ranken wie viele Seiten",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "Position",
       y = "# Seiten")

ggsave("seiten_ranking_verteilung_position_1_10.jpg", width = 12, height = 8)


## SERPs
gsc_pages_ranking_distribution %>% 
  ggplot(aes(serp)) +
  geom_bar(fill = "blue", alpha = .8) +
  theme_minimal_hgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Seiten: Rankingverteilung der SERPs",
       subtitle = "Auf welchen SERPs ranken wie viele Seiten",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "SERP",
       y = "# Seiten")

ggsave("seiten_ranking_verteilung_serps.jpg", width = 12, height = 8)


# Suchanfragen **************************************************
## Position 1 - 10
### Total
gsc_queries_ranking_distribution %>% 
  filter(position < 11) %>%
  ggplot(aes(position_bin)) +
  geom_bar(fill = "blue", alpha = .8) +
  theme_minimal_hgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Suchanfragen: Rankingverteilung der ersten SERP",
       subtitle = "Auf welchen Positionen ranken wie viele Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "Position",
       y = "# Suchanfragen")

ggsave("suchanfragen_ranking_verteilung_position_1_10.jpg", width = 12, height = 8)


### Brand / Non-Brand
gsc_queries_ranking_distribution %>% 
  filter(position < 11) %>%
  ggplot(aes(position_bin, fill = brand)) +
  geom_bar(position = position_dodge2(preserve = "single"), alpha = .8) +
  theme_minimal_hgrid() +
  theme(legend.position = "top") +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  scale_fill_manual(values = c("Brand"  = "blue",
                               "Non-Brand" = "orange")) +
  labs(title = "Suchanfragen: Rankingverteilung der ersten SERP - Brand / Non-Brand",
       subtitle = "Auf welchen Positionen ranken wie viele Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "Position",
       y = "# Suchanfragen",
       fill = NULL)

ggsave("suchanfragen_brand_nonbrand_ranking_verteilung_position_1_10.jpg", width = 12, height = 8)


## SERPs
### Total
gsc_queries_ranking_distribution %>% 
  ggplot(aes(serp)) +
  geom_bar(fill = "blue", alpha = .8) +
  theme_minimal_hgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Suchanfragen: Rankingverteilung der SERPs",
       subtitle = "Auf welchen SERPs ranken wie viele Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "SERP",
       y = "# Suchanfragen")

ggsave("suchanfragen_ranking_verteilung_serps.jpg", width = 12, height = 8)


### Brand / Non-Brand
gsc_queries_ranking_distribution %>% 
  ggplot(aes(serp, fill = brand)) +
  geom_bar(position = position_dodge2(preserve = "single"), alpha = .8) +
  theme_minimal_hgrid() +
  theme(legend.position = "top") +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  scale_fill_manual(values = c("Brand"  = "blue",
                               "Non-Brand" = "orange")) +
  labs(title = "Suchanfragen: Rankingverteilung der SERPs - Brand / Non-Brand",
       subtitle = "Auf welchen SERPs ranken wie viele Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "SERP",
       y = "# Suchanfragen",
       fill = NULL)

ggsave("suchanfragen_brand_nonbrand_ranking_verteilung_serps.jpg", width = 12, height = 8)



# Anzahl Wörter in Suchanfrage ----------------------------------

gsc_queries_n_tokens <- gsc_queries_ranking_distribution %>% 
  mutate(n_tokens = str_count(haufigste_suchanfragen, "\\S+"))


# Anzahl Wörter
gsc_queries_n_tokens %>% 
  count(n_tokens) %>% 
  mutate(n_tokens = as_factor(n_tokens)) %>% 
  ggplot(aes(n_tokens, n)) +
  geom_col(fill = "blue", alpha = .8) +
  theme_minimal_hgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Anzahl der Suchanfragen mit # Wörtern",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "# Wörter",
       y = "# Suchanfragen")

ggsave("suchanfragen_anzahl_der_wörter.jpg", width = 12, height = 8)


# Anteil der Suchanfragen mit x Wörtern je Position auf SERP 1
gsc_queries_n_tokens %>% 
  filter(position < 11) %>% 
  count(n_tokens, position_bin) %>% 
  mutate(n_tokens = as_factor(n_tokens)) %>% 
  ggplot(aes(position_bin, n, fill = n_tokens)) +
  geom_col(color = "black", alpha = .8) +
  theme_minimal_hgrid() +
  theme(legend.position = "top") +
  scale_y_continuous(expand = expansion(mult = c(0, .1))) +
  labs(title = "Suchanfragen: Rankingverteilung der ersten SERP nach Anzahl der Wörter der Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = "Position",
       y = "# Suchanfragen",
       fill = "# Wörter")

ggsave("suchanfragen_ranking_verteilung_anzahl_der_wörter.jpg", width = 12, height = 8)



# Top 10 Seiten -------------------------------------------------

gsc_pages %>%
  mutate(pct_klicks = round(klicks / sum(klicks) * 100, 1)) %>% 
  slice_max(order_by = klicks, n = 10) %>%
  select(die_haufigsten_seiten, Klicks = klicks, `% an Gesamt-Klick` = pct_klicks) %>% 
  pivot_longer(-die_haufigsten_seiten) %>%
  mutate(name = fct_relevel(name, "Klicks")) %>% 
  ggplot(aes(reorder(die_haufigsten_seiten, value), value)) +
  geom_point(size = 3) +
  geom_segment(aes(x = die_haufigsten_seiten, xend = die_haufigsten_seiten, y = 0, yend = value)) +
  coord_flip() +
  facet_wrap(~name, ncol = 2, scales = "free_x") + 
  theme_minimal_vgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .2)),labels=scales::label_number_si()) +
  labs(title= "Top 10 Seiten",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = NULL,
       y = NULL)

ggsave("top_10_seiten.jpg", width = 12, height = 8)


# Top 10 Suchanfragen -------------------------------------------

gsc_queries %>%
  mutate(pct_klicks = round(klicks / sum(klicks) * 100, 1)) %>% 
  slice_max(order_by = klicks, n = 10) %>%
  select(haufigste_suchanfragen, Klicks = klicks, `% an Gesamt-Klick` = pct_klicks) %>% 
  pivot_longer(-haufigste_suchanfragen) %>%
  mutate(name = fct_relevel(name, "Klicks")) %>% 
  ggplot(aes(reorder(haufigste_suchanfragen, value), value)) +
  geom_point(size = 3) +
  geom_segment(aes(x = haufigste_suchanfragen, xend = haufigste_suchanfragen, y = 0, yend = value)) +
  coord_flip() +
  facet_wrap(~name, ncol = 2, scales = "free_x") + 
  theme_minimal_vgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .2))) +
  labs(title= "Top 10 Suchanfragen",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = NULL,
       y = NULL)

ggsave("top_10_suchanfragen.jpg", width = 12, height = 8)



# Lageparameter -------------------------------------------------

summary_stats <- function(df) {
  
  df %>% 
    summarise(
      across(c(klicks, impressionen, ctr, position),
             list(
               min = ~min(.x, na.rm = TRUE),
               q25 = ~quantile(.x, .25, na.rm = TRUE),
               median = ~quantile(.x, .5, na.rm = TRUE),
               mean = ~mean(.x, na.rm = TRUE),
               q75 = ~quantile(.x, .75, na.rm = TRUE),
               max = ~max(.x, na.rm = TRUE)
             )
      )
    )
}

# Lageparameter: Seiten *****************************************
gsc_pages_summary_stats <- gsc_pages %>% 
  summary_stats() %>% 
  pivot_longer(everything()) %>%
  separate(name, into = c("metric", "lageparameter")) %>%
  pivot_wider(names_from = metric, values_from = value) %>% 
  mutate(
    ctr = ctr * 100,
    across(where(is.numeric), ~round(.x, 2))
  )

write_excel_csv2(gsc_pages_summary_stats, "seiten_lageparameter.csv")


# Lageparameter: Suchanfragen ***********************************
## Total
gsc_queries_summary_stats <- gsc_queries %>% 
  summary_stats() %>% 
  pivot_longer(everything()) %>%
  separate(name, into = c("metric", "lageparameter")) %>%
  pivot_wider(names_from = metric, values_from = value) %>% 
  mutate(
    ctr = ctr * 100,
    across(where(is.numeric), ~round(.x, 2))
  )

write_excel_csv2(gsc_queries_summary_stats, "suchanfragen_lageparameter.csv")


## Brand
gsc_queries_brand_summary_stats <- gsc_queries %>%
  filter(brand == "Brand") %>% 
  summary_stats() %>% 
  pivot_longer(everything()) %>%
  separate(name, into = c("metric", "lageparameter")) %>%
  pivot_wider(names_from = metric, values_from = value) %>% 
  mutate(
    ctr = ctr * 100,
    across(where(is.numeric), ~round(.x, 2))
  )

write_excel_csv2(gsc_queries_brand_summary_stats, "suchanfragen_lageparameter_brand.csv")


## Non-Brand
gsc_queries_nonbrand_summary_stats <- gsc_queries %>%
  filter(brand == "Non-Brand") %>% 
  summary_stats() %>% 
  pivot_longer(everything()) %>%
  separate(name, into = c("metric", "lageparameter")) %>%
  pivot_wider(names_from = metric, values_from = value) %>% 
  mutate(
    ctr = ctr * 100,
    across(where(is.numeric), ~round(.x, 2))
  )

write_excel_csv2(gsc_queries_nonbrand_summary_stats, "suchanfragen_lageparameter_nonbrand.csv")



# Länder --------------------------------------------------------

gsc_countries %>%
  slice_max(order_by = klicks, n = 10) %>% 
  select(land, Klicks = klicks, Impressionen = impressionen) %>% 
  pivot_longer(-land) %>% 
  mutate(name = fct_relevel(name, "Klicks")) %>% 
  ggplot(aes(reorder(land, value), value)) +
  geom_point(size = 3) +
  geom_segment(aes(x = land, xend = land, y = 0, yend = value)) +
  coord_flip() +
  facet_wrap(~name, ncol = 2, scales = "free_x") +
  theme_minimal_vgrid() +
  scale_y_continuous(expand = expansion(mult = c(0, .2)), labels = scales::label_number_si()) +
  labs(title= "Top 10 Länder",
       x = NULL,
       y = NULL)

ggsave("top_10_länder.jpg", width = 12, height = 8)



# Geglättete Zeitreihen -----------------------------------------

gsc_timeseries %>%
  mutate(across(where(is.numeric), ~rollmean(.x, k = 7, fill = NA, align = "center"), .names = "{col}_rollmean")) %>%
  select(everything(), klicks_day = klicks, impressionen_day = impressionen, ctr_day = ctr, position_day = position) %>%
  pivot_longer(-datum) %>% 
  separate(name, sep = "_", into = c("name", "temp_col")) %>%
  pivot_wider(names_from = "temp_col", names_prefix = "value_", values_from = "value") %>%
  mutate(name = fct_relevel(name, "klicks", "impressionen", "ctr", "position")) %>% 
  ggplot(aes(datum)) +
  geom_line(aes(y = value_day), color = "grey") +
  geom_line(aes(y = value_rollmean), size = 1) +
  facet_wrap(~name, scales = "free_y") +
  theme_half_open() +
  background_grid() +
  panel_border() +
  theme(legend.position = "top") +
  scale_x_date(labels = scales::label_date_short()) +
  scale_y_continuous(labels = scales::label_number_auto()) +
  labs(title = "Tägliche Werte & Rollender Durschnitt",
       subtitle = "Zeitfenster des Durchschnitts: 7 Tage",
       caption = glue("Berichtszeitraum:\n{ START_DATE } – { END_DATE }"),
       x = NULL,
       y = NULL)

ggsave("zeitreihen_geglättet.jpg", width = 12, height = 8)

### Ende des des Skrips
### Für weitere Infos siehe Beitrag in Website Boosting, Ausgabe 69