Data visualisation in SQL

sql_data_viz.sql

-- this query outputs a data visualisation to explore how many unique products people
-- see on your website, and the conversion rate

-- it uses Big Query SQL on a GA4Dataform events table - check https://github.com/superformlabs/ga4dataform-community

with products_per_user as (
  SELECT
    user_pseudo_id,
    count( distinct if(event_name = 'view_item', i.item_name, NULL) ) as n_items_viewed,
    max( if(event_name = 'purchase', 1, 0)) as purchaser 
  FROM
    `superform_outputs_31337.ga4_events`, unnest(items) i
  WHERE
    event_name in ('view_item', 'purchase')
    and event_date between date_sub(current_date(), interval 28 day) and current_date()
  group by all
  having n_items_viewed > 0
),

agg as (
  select
    n_items_viewed,
    count(*) users,
    sum(purchaser) as purchasers,
  from products_per_user
  group by all
)

, calculations as (
  select
    *,
    purchasers / users as cvr,
    users / sum(users) over()  as pct_of_users,
    purchasers / sum(purchasers) over() as pct_of_buyers,
  from agg
)

-- make pretty
select
  n_items_viewed as `Unique items viewed`,
  users,
  repeat('\u2588', cast(100 * cvr as int64))           || ' ' || round(100 * cvr, 1)           || '%' as `Conversion Rate`,
  repeat('\u2588', cast(60  * pct_of_users as int64))  || ' ' || round(100 * pct_of_users, 1)  || '%' as `Percentage of Users`,
  repeat('\u2588', cast(80  * pct_of_buyers as int64)) || ' ' || round(100 * pct_of_buyers, 1) || '%' as `Percentage of Buyers`

from calculations
order by n_items_viewed

Ugh.. spot the typo!

od

od

🍰