I'm working on bigquery api, and using postman.
So, I'm passing this query:
{
"query": "select
count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end) as engaged_sessions,
count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) as sessions,
count(distinct user_pseudo_id) as users,
count(distinct case when (select value.int_value from unnest(event_params) where key = 'ga_session_number') = 1 then user_pseudo_id else null end) as new_users,
safe_divide(count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as engagement_rate,
safe_divide(count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id'))) - count(distinct case when (select value.string_value from unnest(event_params) where key = 'session_engaged') = '1' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end),count(distinct concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')))) as bounce_rate,
countif(event_name = 'page_view') as page_views,
count(distinct case when event_name = 'page_view' then concat(user_pseudo_id,(select value.int_value from unnest(event_params) where key = 'ga_session_id')) end) as unique_pageviews
from
-- change this to your google analytics 4 export location in bigquery
`big-query-broadridge-api.analytics_339758665.events_*`
where
-- define static and/or dynamic start and end date
_table_suffix between '20231003' and '20231003'"
}
and im getting this error :
{
"error": {
"code": 400,
"message": "1.600 - 1.945: Unrecognized function safe_divide\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"errors": [
{
"message": "1.600 - 1.945: Unrecognized function safe_divide\n[Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]",
"domain": "global",
"reason": "invalidQuery",
"location": "q",
"locationType": "parameter"
}
],
"status": "INVALID_ARGUMENT"
}
}
i tried to run this query on google console for bigquery and its working fine. but its showing error when i use it in postman using bigquery api.