tablero-correo.qmd

---
title: |
  Dashboard de Metricas de Mailchimp <a href="https://metadocencia.github.io/reporte-mailchimp/" class="btn btn-primary btn-sm" style="margin-left: 10px;"><i class="bi bi-link-45deg"></i> Volver al principio</a>
format: 
  dashboard:
    orientation: rows
    nav-buttons: 
      - icon: images/md-logo.png
        href: https://metadocencia.github.io/reporte-mailchimp/
embed-resources: true
logo: images/md-logo.png
include-in-header:
  - text: |
      <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.7.2/font/bootstrap-icons.css">
---

```{r}
#| label: load-packages
#| message: false
library(dplyr)
library(tidyr)
library(ggplot2)
library(stringr)
library(scales)
library(googlesheets4)
library(forcats)
library(bsicons)
library(bslib)
library(plotly)

```

```{r}
#| label: cargar-datos
#| message: false


gs4_auth(path = Sys.getenv('GOOGLE_APPLICATION_CREDENTIALS'))

mailchimp <- read_sheet("1grpIQWXQfVT82vS-mU2JjkKzZ0zwrGes3CFEnWdjYf8")[c(1,4:7, 13:19)]

```

```{r}
#| label: prep-datos

names(mailchimp) <- c("titulo", "envio_fecha", "envio_dia", "total_destinatarios", "entregas_exitosas", "aperturas_unicas", "aperturas_tasa", "aperturas_totales", "clics_unicos", "clics_tasa", "clics_totales", "desuscriptores")

mailchimp <- mailchimp |>
  mutate(envio_fecha = format(envio_fecha, "%Y-%m"),
         envio_dia = fct_recode(envio_dia, 
                                "Lunes" = "Monday",
                                "Martes" = "Tuesday",
                                "Miércoles" = "Wednesday",
                                "Jueves" = "Thursday",
                                "Viernes" = "Friday",
                                "Sábado" = "Saturday",
                                "Domingo" = "Sunday"),
         envio_dia = fct_relevel(envio_dia, "Lunes", "Martes", "Miércoles", "Jueves", "Viernes"))



```

# 📰 Newsletter

```{r}
#| label: prep-apuntes-datos

apuntes <- mailchimp |>
  filter(str_detect(titulo, pattern = "Apuntes [0-9]+")) |>
  mutate(titulo = as.numeric(str_extract(titulo, "[0-9]+")))


```

## Fila 1 - Totales de ultimo envio {height=""}

```{r}
#| label: calcular-totales-apuntes

ultimo_envio <- apuntes |>
  arrange(desc(envio_fecha)) |>
  slice_head(n = 1)

```


```{r}
# Crear value boxes
value_box(
  title = "Total de vistas",
  value = scales::number(ultimo_envio$total_destinatarios, big.mark = ","),
  showcase = bsicons::bs_icon("people-fill"),
  theme = "primary",
  full_width = TRUE,
)

```


```{r}

value_box(
  title = "Desuscripciones",
  value = scales::number(ultimo_envio$desuscriptores, big.mark = ","),
  showcase = bsicons::bs_icon("person-fill-dash"),
  theme = "info",
  full_width = TRUE,
)
```


```{r}
value_box(
  title = "Aperturas unicas",
  value = scales::number(ultimo_envio$aperturas_unicas, big.mark = ","),
  showcase = bsicons::bs_icon("envelope-paper"),
  theme = "success",
  full_width = TRUE,
)


```


```{r}

value_box(
  title = "Tasa de apertura",
  value = scales::number(ultimo_envio$aperturas_tasa, big.mark = ","),
  showcase = bsicons::bs_icon("envelope-paper-fill"),
  theme = "warning",
  full_width = TRUE,
)




```


```{r}
value_box(
  title = "Tasa de clicks",
  value = scales::number(ultimo_envio$clics_tasa, big.mark = ","),
  showcase = bsicons::bs_icon("cursor"),
  theme = "info",
  full_width = TRUE,
)


```


## Fila 2 {height=""}

### Columna - Métricas por newsletter{width="60%"}

```{r}
#| title: Métricas por newsletter
#| fig-width: 8
#| fig-height: 2

p1 <- apuntes |>
  pivot_longer(cols = c(total_destinatarios, entregas_exitosas, aperturas_unicas),
               names_to = "variables",
               values_to = "valores") |>
  ggplot(aes(x = titulo, y = valores, color = variables, group = variables)) +
  geom_line(linewidth = 1) +
  scale_color_manual(
    values = c("total_destinatarios" = "#C83737", "entregas_exitosas" = "#004F70", "aperturas_unicas" = "#F67A21"), 
    labels = c("total_destinatarios" = "Destinatarios totales", "entregas_exitosas" = "Entregas exitosas",
               "aperturas_unicas" = "Aperturas únicas")  
  ) +
  scale_x_continuous(breaks = seq(from = min(apuntes$titulo, na.rm = TRUE), 
                                    to = max(apuntes$titulo, na.rm = TRUE), 
                                    by = 1)) +
  theme_minimal() +
  labs(x = "Newsletter", y = "Cantidad", color = "") 


ggplotly(p1) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)

```

### Columna - Tasa de apertura por día {width="40%"}

```{r}
#| title: Tasa de apertura promedio por día de la semana
#| fig-width: 4
#| fig-height: 2

p2 <- apuntes |>
  group_by(envio_dia) %>% 
  summarise(media = mean(aperturas_tasa, na.rm = TRUE)) %>% 
  ggplot(aes(x = envio_dia, y = media, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +  
  labs(x = "", y = "Tasa de apertura %")

ggplotly(p2) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```
## Fila 3 {height=""}

### Columna - Tasa de apertura por newsletter {width="50%"}

```{r}
#| title: Tasa de apertura por newsletter
#| fig-width: 6
#| fig-height: 2

p3 <- apuntes |>
  ggplot(aes(x = titulo, y = aperturas_tasa, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_x_continuous(breaks = seq(from = min(apuntes$titulo, na.rm = TRUE), 
                                    to = max(apuntes$titulo, na.rm = TRUE), 
                                    by = 1)) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +  
  labs(x = "Newsletter", y = "Cantidad")

ggplotly(p3) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```

### Columna - Desuscriptores por newsletters {width="50%"}

```{r}
#| title: Desuscriptores por newsletter
#| fig-width: 6
#| fig-height: 2

p4 <- apuntes |>
  ggplot(aes(x = titulo, y = desuscriptores, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_x_continuous(breaks = seq(from = min(apuntes$titulo, na.rm = TRUE), 
                                    to = max(apuntes$titulo, na.rm = TRUE), 
                                    by = 1)) +
  scale_y_continuous(breaks = seq(from = 0, to = 15, by = 2), limits = c(0, 15)) +
  labs(x = "Newsletter", y = "Cantidad")

ggplotly(p4) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```


# 🚀 ALTa Ciencia Abierta

```{r}
#| label: prep-alta-datos

regex <- "Mensaje semana(l)? (\\d+|[A-Za-z]\\d+) ALTa[123]2024"

alta <- mailchimp |>
  filter(str_detect(titulo, pattern = regex)) |>
  mutate(titulo = paste0("C", str_match(titulo, "\\d+.*?(\\d)")[,2], "-E", str_match(titulo, "[A-Za-z]?(\\d+)")[,2]))


```

## Fila 1 - Totales de ultimo envio {height=""}

```{r}
#| label: calcular-totales-alta

ultimo_envio <- alta |>
  arrange(desc(envio_fecha)) |>
  slice_head(n = 1)

```


```{r}

value_box(
  title = "Destinatarios totales",
  value = scales::number(ultimo_envio$total_destinatarios, big.mark = ","),
  showcase = bsicons::bs_icon("people-fill"),
  theme = "primary",
  full_width = TRUE,
)


```


```{r}

value_box(
  title = "Desuscripciones",
  value = scales::number(ultimo_envio$desuscriptores, big.mark = ","),
  showcase = bsicons::bs_icon("person-fill-dash"),
  theme = "info",
  full_width = TRUE,
)

```

```{r}
value_box(
  title = "Aperturas unicas",
  value = scales::number(ultimo_envio$aperturas_unicas, big.mark = ","),
  showcase = bsicons::bs_icon("envelope-paper"),
  theme = "success",
  full_width = TRUE,
)

```

```{r}
value_box(
  title = "Tasa de apertura",
  value = scales::number(ultimo_envio$aperturas_tasa, big.mark = ","),
  showcase = bsicons::bs_icon("envelope-paper-fill"),
  theme = "info",
  full_width = TRUE,
)

```

```{r}
value_box(
  title = "Tasa de clicks",
  value = scales::number(ultimo_envio$clics_tasa, big.mark = ","),
  showcase = bsicons::bs_icon("cursor"),
  theme = "warning",
  full_width = TRUE,
)

```


## Fila 2 {height=""}

### Columna - Métricas por envío{width="60%"}

```{r}
#| title: Métricas por newsletter
#| fig-width: 8
#| fig-height: 2

p5 <- alta |>
 pivot_longer(cols = c(total_destinatarios, entregas_exitosas, aperturas_unicas),
               names_to = "variables",
               values_to = "valores") |>
  ggplot(aes(x = titulo, y = valores, color = variables, group = variables)) +
  geom_line(linewidth = 1) +
  scale_color_manual(
    values = c("total_destinatarios" = "#C83737", "entregas_exitosas" = "#004F70", "aperturas_unicas" = "#F67A21"), 
    labels = c("total_destinatarios" = "Destinatarios totales", "entregas_exitosas" = "Entregas exitosas",
               "aperturas_unicas" = "Aperturas únicas")  
  ) +
  theme_minimal() +
  labs(x = "Newsletter", y = "Cantidad", color = "")

ggplotly(p5) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)

```

### Columna - Tasa de apertura por día {width="40%"}

```{r}
#| title: Tasa de apertura promedio por día de la semana
#| fig-width: 4
#| fig-height: 2

p6 <- alta |>
  group_by(envio_dia) %>% 
  summarise(media = mean(aperturas_tasa, na.rm = TRUE)) %>% 
  ggplot(aes(x = envio_dia, y = media, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +  
  labs(x = "", y = "Tasa de apertura %")
ggplotly(p6) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```
## Fila 3 {height="35%"}

### Columna - Tasa de apertura por envío{width="50%"}

```{r}
#| title: Tasa de apertura por newsletter
#| fig-width: 6
#| fig-height: 2

p7 <- alta |>
  ggplot(aes(x = titulo, y = aperturas_tasa, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1)) +  
  labs(x = "Newsletter", y = "Cantidad")
ggplotly(p7) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```

### Columna - Desuscriptores por envío {width="50%"}

```{r}
#| title: Desuscriptores por newsletter
#| fig-width: 6
#| fig-height: 2

p8 <- alta |>
  ggplot(aes(x = titulo, y = desuscriptores, group = 1)) +
  geom_line(linewidth = 1, color = "#C83737") +
  theme_minimal() +
  scale_y_continuous(breaks = seq(from = 0, to = 15, by = 2), limits = c(0, 15)) +
  labs(x = "Newsletter", y = "Cantidad")
ggplotly(p8) %>%
  config(displayModeBar = FALSE, showLink = FALSE, displaylogo = FALSE)
```