use a query with the bigquery connector

gjbankos1 — Thu, 14 Mar 2024 13:38:17 GMT

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;

topic use a query with the bigquery connector in Integration, Extension & APIs

use a query with the bigquery connector