Monthly total user retention numbers in BigQuery with google analytics

retention.sql

SELECT 
    last as last_month,
    DATETIME_DIFF(last, first, MONTH) as retained_months,
    COUNT(*) as users
FROM (
    SELECT
        DATE(DATE_TRUNC(TIMESTAMP_MICROS(min(event_timestamp)), MONTH)) as first,
        DATE(DATE_TRUNC(TIMESTAMP_MICROS(max(event_timestamp)), MONTH)) as last
    FROM `<your-project>.events_*`
    WHERE event_name = 'view_item'
    GROUP BY user_pseudo_id
)
WHERE DATETIME_DIFF(last, first, MONTH) > 0
GROUP BY last_month, retained_months
ORDER BY last_month, retained_months