Google BigQuery - Google Analytics 4 (GA4) - Users by Devices - LCP Issue

Intro:

I have been optimizing the website performance of my sister's website (Crafts-Women.com) this week. I received a notification from Google Search Console about the decrease in performance for Largest Contentful Paint (LCP) on mobile devices. LCP is one of the core web vitals metrics that Google uses to measure website performance and usability.

Background:

We use a no-code platform Bubble.io to host and develop the website. We already knew about some performance issues when traffic gets heavier due to heavy customization and the quota from Bubble.io based on our subscription level. So how can we improve the UX without spending more money and time to restructure? I would have to focus on fixing the pages where we get the most traffic and optimize load time to core content. It means reducing the size of the images, creating priorities on which contents to load first, and more.

Simple Analysis - Bigquery and Tableau

First, I wanted to fix the pages where we get the most traffic. It wasn't hard to find it. I used the historical GA4 data on Google Bigquery. I wrote a post about how to connect GA4 to Bigquery here. The website gets most of its traffic from an organic source - Google Search (more than 70%). 

We wanted to first fix any issues based on the device type. Here is a snippet from a Tableau dashboard.

Surprisingly, we have many Chromebook users. I'm glad the LCP issue did not impact most website users. Based on the dashboard, I see that mobile users are predominantly Apple iPhone users. We have a considerable amount of Safari users, so I would have to make sure to improve performance there. 

Google Biquery - Query

Here is the query that I used to pull the data from Bigquery:

with cte_tbl_base as (
select
    distinct
    base.stream_id,
    base.event_date,
    base.user_pseudo_id,
    base.device.category as device_category,
    base.device.mobile_brand_name as device_brand_name,
    base.device.mobile_model_name as device_model_name,
    base.device.web_info.browser as browser
from `***.analytics_322701071.events_*` base #, unnest(event_params) as event
where base.event_name is not null
),

cte_tbl_events as (
select
    distinct
    base.stream_id,
    base.event_date,
    base.user_pseudo_id,
    base.event_name,
    (select value.string_value from unnest(event_params) where key='page_location') as page_location,
from `***.analytics_322701071.events_*` base, unnest(event_params) as event
where (select value.string_value from unnest(event_params) where key='page_location') is not null
)

select *
from (
        select
            base.stream_id,
            base.event_date,
            base.user_pseudo_id,
            base.device_category,
            base.device_brand_name,
            base.device_model_name,
            base.browser,
            events_.event_name,
        case
        when events_.page_location like '%.com/' then 'home'
        when events_.page_location like '%/post%' then 'post'
        when events_.page_location like '%/hobbies%' then 'hobbies'
        when events_.page_location like '%/interviews%' then 'interviews'
        end root,
from cte_tbl_base base
left join cte_tbl_events events_
on base.event_date = events_.event_date
and base.user_pseudo_id = events_.user_pseudo_id
and base.stream_id = events_.stream_id
where events_.page_location is not null
)
pivot
(
    count(*)
    for event_name in ('first_visit','session_start','page_view','first_visitors','user_engagement','click','first_visit_30_days')
)

In my next post, I will query to see more user behaviors to compare pre and post-website changes.