CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Riccardo Lamera
Riccardo Lamera

Reputation: 105

BigQuery, How to replicate the 'Conversions | (Enhanced) Ecommerce | checkout behavior' funnel report

I built a query that allows me to replicate the google analytics shopping behavior funnel report in bigquery:

#standardSQL
select
  case when totals.newvisits = 1 then 'New visitor' else 'Returning visitor' end as user_type,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) as all_sessions,
  count(distinct case when hits.ecommerceaction.action_type = '2' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as sessions_with_product_views,
  count(distinct case when hits.ecommerceaction.action_type = '3' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as sessions_with_add_to_card,
  count(distinct case when hits.ecommerceaction.action_type = '5' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as sessions_with_check_out,
  count(distinct case when hits.ecommerceaction.action_type = '6' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as sessions_with_transactions
from
  `dataset.ga.ga_sessions_20210410`,
  unnest(hits) as hits,
  unnest(product) as product
where
  totals.visits = 1
group by
  user_type
order by
  all_sessions desc

Here the results:

enter image description here

This is for sessions, I have a similar one for abandonments. I can't find any field giving checkout stages so that I could replicate the checkout report funnel as well. These stages are 'current order', 'shipping billing method', 'order confirmation' and relative dropoffs. The field giving shopping stages is 'hits.ecommerceaction.action_type', is there one for checkouts?

Upvotes: 0

Views: 648

Answers (1)

Kumanan
Kumanan

Reputation: 26

You could use hits.eCommerceAction.step to get the steps on checkout process.

This field is populated when a checkout step is specified with the hit.

Upvotes: 1

Related Questions