I have this query that I want to use with the bigquery connector. It looks like with the base functionality, you just pick tables and it pull the tables in. I would like to run this query and pull the results back. Can I do that with the bigquery connector?
with prep as ( select user_pseudo_id, (select value.int_value from unnest(event_params) where key = 'ga_session_id') as session_id, array_agg((select value.string_value from unnest(event_params) where key = 'source') ignore nulls order by event_timestamp)[safe_offset(0)] as source, array_agg((select value.string_value from unnest(event_params) where key = 'medium') ignore nulls order by event_timestamp)[safe_offset(0)] as medium, array_agg((select value.string_value from unnest(event_params) where key = 'campaign') ignore nulls order by event_timestamp)[safe_offset(0)] as campaign, max((select value.string_value from unnest(event_params) where key = 'gclid')) as gclid, countif(event_name = 'lead_completed') as lead_completed_count, countif(event_name = 'phone_link_click') as phone_link_click_count, countif(event_name = 'osc_chat_started') as osc_chat_started_count, countif(event_name = 'osc_text_requested') as osc_text_requested_count, countif(event_name = 'scheduled_appointment') as scheduled_appointment_count, countif(event_name = 'virtual_scheduled_appointment') as virtual_scheduled_appointment_count from `ga4-tb-data.analytics_270728985.events_20240304` group by user_pseudo_id, session_id )
select -- session default channel grouping (dimension | the channel group associated with a session) case when source = '(direct)' and (medium in ('(not set)','(none)')) then 'Direct' when gclid is not null then 'Paid Search' -- Categorizing as Paid Search when gclid is not null when regexp_contains(source,'Facebook|Instagram|Linkedin|Pinterest|Tiktok|Twitter|X') and regexp_contains(medium,'Social') then 'Paid Social' when regexp_contains(medium,'Display|DisplayNGDN|Display_NGDN') then 'Display' when regexp_contains(source,'facebook|instagram|linkedin|pinterest|tiktok|twitter|whatsapp|linkin.bio') or medium = 'Social' then 'Organic Social' when regexp_contains(source,'baidu|bing|duckduckgo|ecosia|google|yahoo') or medium = 'organic' then 'Organic Search' when source = 'Tbmail' then 'Email' when regexp_contains(source,'(newhomesource|realtor|trulia|zillow|livabl|redfin|newhomesourceprofessional|casasneuvasaqui|homes)') then 'Affiliates' when medium = 'referral' then 'Referral' when regexp_contains(source, 'yext|Yext') then 'Location Listings' when regexp_contains(medium,'Offline Media|Print|Direct Mail|TBQR') then 'Offline Media' else 'Unassigned' end as channel_grouping_session, count(distinct concat(user_pseudo_id,session_id)) as sessions, sum(lead_completed_count) as lead_completed, sum(phone_link_click_count) as phone_link_click, sum(osc_chat_started_count) as osc_chat_started, sum(osc_text_requested_count) as osc_text_requested, sum(scheduled_appointment_count) as scheduled_appointment, sum(virtual_scheduled_appointment_count) as virtual_scheduled_appointment from prep group by channel_grouping_session order by sessions desc;
