GA4 × BigQuery、SQLあんまり分からんくてもChatGPTを使ってどうにかこうにかやる方法

いまからBigQueryでGA4のデータ分析やるためのSQLを書いてもらうんだけど、データ構造は下記のような内容になっているんで、その想定でよろしく頼むぜ

| フィールド名 | 種類 | モード |
|--------------|------|-------|
| event_date | STRING | NULLABLE |
| event_timestamp | INTEGER | NULLABLE |
| event_name | STRING | NULLABLE |
| event_params | RECORD | REPEATED |
| - key | STRING | NULLABLE |
| - value | RECORD | NULLABLE |
| -- string_value | STRING | NULLABLE |
| -- int_value | INTEGER | NULLABLE |
| -- float_value | FLOAT | NULLABLE |
| -- double_value | FLOAT | NULLABLE |
| event_previous_timestamp | INTEGER | NULLABLE |
| event_value_in_usd | FLOAT | NULLABLE |
| event_bundle_sequence_id | INTEGER | NULLABLE |
| event_server_timestamp_offset | INTEGER | NULLABLE |
| user_id | STRING | NULLABLE |
| user_pseudo_id | STRING | NULLABLE |
| privacy_info | RECORD | NULLABLE |
| - analytics_storage | STRING | NULLABLE |
| - ads_storage | STRING | NULLABLE |
| - uses_transient_token | STRING | NULLABLE |
| user_properties | RECORD | REPEATED |
| - key | STRING | NULLABLE |
| - value | RECORD | NULLABLE |
| -- string_value | STRING | NULLABLE |
| -- int_value | INTEGER | NULLABLE |
| -- float_value | FLOAT | NULLABLE |
| -- double_value | FLOAT | NULLABLE |
| set_timestamp_micros | INTEGER | NULLABLE |
| user_first_touch_timestamp | INTEGER | NULLABLE |
| user_ltv | RECORD | NULLABLE |
| - revenue | FLOAT | NULLABLE |
| - currency | STRING | NULLABLE |
| device | RECORD | NULLABLE |
| - category | STRING | NULLABLE |
| - mobile_brand_name | STRING | NULLABLE |
| - mobile_model_name | STRING | NULLABLE |
| - mobile_marketing_name | STRING | NULLABLE |
| - mobile_os_hardware_model | STRING | NULLABLE |
| - operating_system | STRING | NULLABLE |
| - operating_system_version | STRING | NULLABLE |
| - vendor_id | STRING | NULLABLE |
| - advertising_id | STRING | NULLABLE |
| - language | STRING | NULLABLE |
| - is_limited_ad_tracking | STRING | NULLABLE |
| time_zone_offset_seconds | INTEGER | NULLABLE |
| browser | STRING | NULLABLE |
| browser_version | STRING | NULLABLE |
| web_info | RECORD | NULLABLE |
| - browser | STRING | NULLABLE |
| - browser_version | STRING | NULLABLE |
| - hostname | STRING | NULLABLE |
| geo | RECORD | NULLABLE |
| - continent | STRING | NULLABLE |
| - country | STRING | NULLABLE |
| - region | STRING | NULLABLE |

DECLARE start_date STRING DEFAULT "20220701";
DECLARE end_date STRING DEFAULT "20220731";
DECLARE segment_url_regex STRING DEFAULT r'^https://oshii\.net/?([?].*)?$'; 
DECLARE conversion_url_regex STRING DEFAULT r'^https://oshii\.net/family/.*$'; 
DECLARE segment_label STRING DEFAULT "Visited Specified Page"; 
DECLARE non_segment_label STRING DEFAULT "Did Not Visit Specified Page";

WITH UserActions AS (
    SELECT user_pseudo_id,
           MAX(IF(REGEXP_CONTAINS(param.value.string_value, segment_url_regex), 1, 0)) AS IsSegmentUser,
           MAX(IF(REGEXP_CONTAINS(param.value.string_value, conversion_url_regex), 1, 0)) AS IsCVUser
    FROM `oshiinet-ga4.analytics_295827956.events_*`, UNNEST(event_params) AS param
    WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date AND event_name = 'page_view'
    GROUP BY user_pseudo_id
)

SELECT 
    CASE WHEN IsSegmentUser = 1 THEN segment_label ELSE non_segment_label END AS Segment,
    COUNT(user_pseudo_id) AS UserCount,
    SUM(IsCVUser) AS ConversionCount,
    CONCAT(ROUND(SAFE_DIVIDE(SUM(IsCVUser), COUNT(user_pseudo_id)) * 100, 2), "%") AS CVR
FROM UserActions
GROUP BY IsSegmentUser
ORDER BY IsSegmentUser DESC;

DECLARE start_date STRING DEFAULT "20220701";
DECLARE end_date STRING DEFAULT "20220731";
DECLARE target_page STRING DEFAULT r'^https://oshii\.net/?([?].*)?$'; 

WITH PageNavigation AS (
  SELECT 
    user_pseudo_id,
    LAG(param.value.string_value) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) as previous_page,
    param.value.string_value as current_page,
    LEAD(param.value.string_value) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) as next_page
  FROM `oshiinet-ga4.analytics_295827956.events_*`, UNNEST(event_params) AS param
  WHERE _TABLE_SUFFIX BETWEEN start_date AND end_date
  AND event_name = 'page_view'
  AND param.key = 'page_location'
)

SELECT 
  CASE
    WHEN REGEXP_CONTAINS(current_page, target_page) THEN "Previous Page"
    ELSE "Next Page"
  END AS NavigationType,
  CASE
    WHEN REGEXP_CONTAINS(current_page, target_page) THEN previous_page
    ELSE next_page
  END AS PageURL,
  COUNT(DISTINCT user_pseudo_id) as Users
FROM PageNavigation
WHERE REGEXP_CONTAINS(current_page, target_page) OR REGEXP_CONTAINS(previous_page, target_page)
GROUP BY NavigationType, PageURL
ORDER BY Users DESC;