FREE Big Query + GA4 File Download Looker Studio Dashboard

If you’ve embedded downloadable files on your website, this intuitive Looker Studio dashboard template will help you with granular insights on user behaviour regarding file downloads.

DumbData

Intuitive Dashboards That Give Actionable Insights On File Download Events Happening On The Website.

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

 

Video on how to use the Dashboard

Related Dashboards

GA4 - Redirection Monitoring Dashboard

After a website redirection, how do you measure traffic and conversions pulse before and after the redirection?

GA4 - 404 Error Monitoring Dashboard

A data studio template that will make it easy and seamless to monitor errors using Google Analytics 4 (GA4) data.

GA4 - Landing Page Snapshot Report

With this data studio template, you can quickly get meaningful insights about a particular landing page using GA4 data.

You can access this Dashboard for free

we make you smarter with data

credit By

The DumbData Team