FREE Big Query + GA4 File Download Looker Studio Dashboard

This dashboard uses the native Looker Studio connector for Big Query to pull the data from the GA4 big Query view.

If you want to pull just event data, you can use this SQL Query in Big Query.

SELECT 
event_date
,event_name
,device.category as device_category
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_title") as page_title
,SPLIT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location"), '?')[offset(0)] as page_url
,REGEXP_REPLACE(SPLIT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location"), '?')[offset(0)], "http(s)?\\:.*\\.com", "") as page_path
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_extension") as file_extension
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_name") as file_name
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "link_url") as file_url
,user_pseudo_id as users
,COUNT(event_name) event_count
FROM `coffeetribega4.analytics_276347190.events_*` 
WHERE event_name = "file_download"
GROUP BY 1,2,3,4,5,6,7,8,9,10

But if you need to include the Session source and medium data in your analysis, you can use this SQL Query.

SELECT 
event_date
,source_session
,medium_session
,event_name
,device_category
,page_url
,REGEXP_REPLACE(page_url, "http(s)?\\:.*\\.com", "") as page_path
,file_extension
,file_url
,file_name
,users
,event_count

FROM
(
SELECT 
event_date
,event_name
,device.category as device_category
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_title") as page_title
,SPLIT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location"), '?')[offset(0)] as page_url
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_extension") as file_extension
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_name") as file_name
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "link_url") as file_url
,user_pseudo_id as users
,CONCAT(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) as stitched_session_id
,COUNT(event_name) as event_count
FROM `coffeetribega4.analytics_276347190.events_*` 
WHERE event_name = "file_download"
GROUP BY 1,2,3,4,5,6,7,8,9,10
) as ED

LEFT JOIN (
SELECT 
source as source_session
,medium as medium_session
,CONCAT(user_pseudo_id,session_id) as stitched_session_id
FROM(
SELECT
user_pseudo_id
,(select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id
,max(LOWER(coalesce((select value.string_value from unnest(event_params) where key = 'source'),'(direct)'))) as source
,max(LOWER(coalesce((select value.string_value from unnest(event_params) where key = 'medium'),'(none)'))) as medium
FROM `coffeetribega4.analytics_276347190.events_*` 
GROUP BY 
user_pseudo_id
,session_id)

GROUP BY
source_session
,medium_session
,stitched_session_id) as TSD
ON ED.stitched_session_id = TSD.stitched_session_id
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

While for Event source and medium, use this SQL query

SELECT 
event_date
,event_name
,device.category as device_category
,LOWER(coalesce((select value.string_value from unnest(event_params) where key = 'source'),'(direct)')) as source
,LOWER(coalesce((select value.string_value from unnest(event_params) where key = 'medium'),'(none)')) as medium
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_title") as page_title
,SPLIT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location"), '?')[offset(0)] as page_url
,REGEXP_REPLACE(SPLIT((SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "page_location"), '?')[offset(0)], "http(s)?\\:.*\\.com", "") as page_path
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_extension") as file_extension
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "file_name") as file_name
,(SELECT value.string_value FROM UNNEST(event_params) WHERE KEY = "link_url") as file_url
,user_pseudo_id as users
,COUNT(event_name) event_count
FROM `coffeetribega4.analytics_276347190.events_*` 
WHERE event_name = "file_download"
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12