How to replicate the 'Conversions | (Enhanced) Ecommerce | Shopping behavior' funnel report (UA)
2 min read

How to replicate the 'Conversions | (Enhanced) Ecommerce | Shopping behavior' funnel report (UA)

A query to generate the Google Analytics Shopping behavior funnel report in BigQuery. In this report you'll find completion and abandonment data about your enhanced ecommerce funnel performance, segmented by user type.
How to replicate the 'Conversions | (Enhanced) Ecommerce | Shopping behavior' funnel report (UA)

As a Google Analytics user you are probably quite attached to the default reports in the user interface of Universal Analytics. It can be hard to make sense of the data in the BigQuery export tables.

Let me enable you to replicate the reports you're familiar with. I'll try to keep it basic here.

Shopping behavior report

In the Conversions | (Enhanced) Ecommerce | Shopping behavior report you'll find data about the performance of your enhanced ecommerce activities, segmented by user type by default, but basically you can add any dimension you need.

Note that these queries will only calculate the absolute visits or abandonments per step in the funnel. You'll still have to add the completion rates or abandonments rates if you need that.

Sessions

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
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits,
  unnest(product) as product
where
  totals.visits = 1
group by
  user_type
order by
  all_sessions desc

Abandonments

select
  case when totals.newvisits = 1 then 'New visitor' else 'Returning visitor' end as user_type,
  count(distinct concat(fullvisitorid, cast(visitstarttime as string))) - count(distinct case when hits.ecommerceaction.action_type = '2' then concat(fullvisitorid, cast(visitstarttime as string)) else null end)as no_shopping_activity,
  count(distinct case when hits.ecommerceaction.action_type = '2' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) - count(distinct case when hits.ecommerceaction.action_type = '3' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as no_cart_addition,
  count(distinct case when hits.ecommerceaction.action_type = '3' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) - count(distinct case when hits.ecommerceaction.action_type = '5' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as cart_abandonment,
  count(distinct case when hits.ecommerceaction.action_type = '5' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) - count(distinct case when hits.ecommerceaction.action_type = '6' then concat(fullvisitorid, cast(visitstarttime as string)) else null end) as checkout_abandonment
from
  `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`,
  unnest(hits) as hits,
  unnest(product) as product
where
  totals.visits = 1
group by
  user_type
order by
  no_shopping_activity desc

Enjoying this post? Subscribe to the GA4BigQuery newsletter!

You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.