How to categorize Google Analytics data into channels based on entrance URL when dealing with large datasets in BigQuery?

I am working with Google Analytics data stored in BigQuery and I need to categorize visits into channels (organic, newsletter, and paid) based on the entrance URL. However, the dataset is quite large, spanning several terabytes, and I am unsure about the correct approach to efficiently handle this task. Currently, my code retrieves the entrance URL for each visit, but I need help expanding it to categorize visits into channels. Here's my existing code:

SELECT
  clientid,
  visitid,
  visitnumber,
  (SELECT h.page.pagepath FROM UNNEST(hits) h WHERE h.isentrance = true) AS entrance_url
FROM
  `test.test.ga_sessions_*`
WHERE
  _table_suffix BETWEEN '20230301' AND '20230628'

Could someone please guide me on the correct approach to categorize visits into channels based on the entrance URL while efficiently handling the large dataset in BigQuery? Thank you!

Example entrance URLs (modified for privacy):

/ca/ca/shop/parcel-tracking?order=&zip=&country=CA
/ca/ca/shop/faqs
/ca/ca/shop/newsletter/unsubscribe?shop=CA&lang=en&uid=&cid=&llid=&emaid=&sc_src=email_&sc_customer=&sc_llid=&sc_lid=&sc_uid=&emst=**********_
/us/us/shop
/us/us/shop/swimwear
/ca/ca/shop/women
/us/us/shop/pyjama-trousers-**********/1

Note: The country codes (e.g., "ca", "us") and product names have been randomly generated to protect privacy while preserving the essence of the data.

Thank you in advance!!

To categorize visits into channels based on the entrance URL while efficiently handling the large dataset in BigQuery,<br><br>

WITH
  channel_mapping AS (
  SELECT
    'organic' AS channel,
    '/google/' AS url_pattern
  UNION ALL
  SELECT
    'newsletter' AS channel,
    '/newsletter/' AS url_pattern
  UNION ALL
  SELECT
    'paid' AS channel,
    '/cpc/' AS url_pattern )
SELECT
  clientid,
  visitid,
  visitnumber,
  entrance_url,
  (SELECT channel FROM channel_mapping WHERE entrance_url LIKE CONCAT('%', url_pattern, '%')) AS channel
FROM (
  SELECT
    clientid,
    visitid,
    visitnumber,
    (SELECT h.page.pagepath FROM UNNEST(hits) h WHERE h.isentrance = true) AS entrance_url
  FROM
    `test.test.ga_sessions_*`
  WHERE
    _table_suffix BETWEEN '20230301' AND '20230628')

You can modify the channel_mapping CTE to add or remove channels as needed. You can also modify the URL patterns to match your specific use case<br>