bigrquery

Author

Affiliation

LB

TPS

Link Utili

Supporto R

• https://bigrquery.r-dbi.org/reference/api-table.html

Librerie

library("DBI")
library("bigrquery")
library("googleAnalyticsR")
library("tidyverse")

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.3     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.5.1     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Autenticazione

Credenziali GA4

Inserisco la mail collegata ad analytics.

ga_auth(email = "triplesense.torino@gmail.com" )
ga_id_4         <- 252648400
ga_id_corporate <- 211109179

Proseguo l’autenticazione su Browser flaggando tutti i campi.

Credenziali BigQuery

getwd()

[1] "/Users/lucabongiorno/Il mio Drive/R"

bq_auth(path = "~/fpt-big-query-for-analytics-0f48d5b7ab13.json")

! Using an auto-discovered, cached token.

  To suppress this message, modify your code or options to clearly consent to
  the use of a cached token.

  See gargle's "Non-interactive auth" vignette for more details:

  <https://gargle.r-lib.org/articles/non-interactive-auth.html>

ℹ The bigrquery package is using a cached token for
  'triplesense.torino@gmail.com'.

Set Up

project_id <- "fpt-big-query-for-analytics"
dataset_id <- "ga4_backfill_data" 

Connessione

connection_ga4_backfill <- dbConnect( bigrquery::bigquery(),
                                      project = "fpt-big-query-for-analytics",
                                      dataset = "ga4_backfill_data",
                                      billing = "817536772965"  # billing lo trovi in IAM e admin > settings > project identifier
)

Analisi + Query

# read data from BQ - LOW LEVEL
dbListTables(connection_ga4_backfill)

[1] "df_ga4_acquisition"     "df_ga4_cr"              "df_ga4_events"         
[4] "df_ga4_events_sessions" "df_ga4_overview"        "df_ga4_site_sections"  
[7] "df_ga4_sociodem"        "df_ga4_users"           "ga4_sociodem"          

# read data from BQ - DPLYR
df_overview <- tbl(connection_ga4_backfill, "df_ga4_overview")

Warning: <BigQueryConnection> uses an old dbplyr interface
ℹ Please install a newer version of the package or contact the maintainer
This warning is displayed once every 8 hours.

# ex. query 
df_overview %>% 
  filter(date > "2023-02-27", 
         country == "Italy") %>% 
  group_by(session_default_channel_group) %>% 
  summarise(sessions = sum(sessions, na.rm = T)) %>% 
  arrange(desc(sessions)) %>% 
  show_query()

<SQL>
SELECT `session_default_channel_group`, SUM(`sessions`) AS `sessions`
FROM `df_ga4_overview`
WHERE (`date` > '2023-02-27') AND (`country` = 'Italy')
GROUP BY `session_default_channel_group`
ORDER BY `sessions` DESC

#BigQueryConnection DB
df_BQ <- df_overview %>% 
  filter(date > "2023-02-27", 
         country == "Italy") %>% 
  group_by(session_default_channel_group) %>% 
  summarise(sessions = sum(sessions, na.rm = T)) %>% 
  arrange(desc(sessions))

df_R <- df_BQ %>% 
  collect()

Upload Data

# per caricare nuovi dati da locale
tbl_ga3 <- bq_table( "fpt-big-query-for-analytics",      # nome progetto
                     "ga3_data",                         # nome connessione
                     "ga3_goals_sessions")               # nome tabella

bq_table_ga3 <- bq_table_create( tbl_ga3,
                                 fields = ga3_goals_sessions,
                                 friendly_name = "GA3_goals_FPT",
                                 description = "GA3 export about goals and sessions, only 2022-2023 - FPT",
                                 labels = list(category = "test")
)

bq_table_upload(tbl_ga3, ga3_goals_sessions, create_disposition='CREATE_IF_NEEDED', write_disposition='WRITE_APPEND')

---