We’re using Tableau to visualise Snowplow web data, and I want to create a simple visualisation of bounce rate.
Does anyone know how to create a calculated metric for the number of bounced sessions i.e. sessions with just a single event? I’ve read Yali’s article on SQL queries for Snowplow data, and the methodology does make sense, however I am not sure how to calculate the number of bounces. Once I have that, I think the calc will be very straight forward:
Where Sessions is a calculated metrics like the following:
Have you created a persistent sessions table similar to what the Snowplow team does using SQL Runner? If so, there should be a column for event_count that you can use for counting how many sessions only have an event_count=1
If not, you’d need to use a SQL query on the events table that aggregates (counts) the number of events (grouped by domain_userid and session_idx) and then you could calculate bounce rate off of that view with Tableau.
The Snowplow team has a basic SQL recipe that creates a daily bounce rate view (off the atomic.events table) which looks like this:
-- Bounce rate by day
CREATE VIEW recipes_basic.bounce_rate_by_day AS
DATE_TRUNC('day', time_first_touch) AS "Date",
SUM(bounces)::REAL/COUNT(*) as "Bounce rate"
MIN(collector_tstamp) as "time_first_touch",
COUNT(*) as "number_of_events",
CASE WHEN count(*) = 1 THEN 1 ELSE 0 END AS bounces
WHERE collector_tstamp > current_date - integer '31'
GROUP BY 1,2
GROUP BY 1
ORDER BY 1;
First of all, bounce is usually calculated through page views (google analytics), and not through total events. So I don’t agree with the count(*).
You can create a view like this:
create view atomic.events_w_bounce as
select e.*, case when a.domain_sessionid is not null and e.event = 'page_view' then 1 else 0 end as bounce
from atomic.events e
left join (
select domain_sessionid, count(distinct event_id) as page_views
where event = 'page_view'
group by 1
having count(distinct event_id) = 1
on e.domain_sessionid = a.domain_sessionid;
It still says “single-page session”. You may interpret it as you like, it’s fine. Just think about things like scrolling- if a user went into to the page, scrolled and closed the browser, would you consider it as bounce? The user did perform an action (scrolled).
If you wouldn’t count it as bounce, I would recommend you adding a tag to the events of “Interaction/ Non Interaction”, and count only the interaction events when calculating bouncing.
And another thing, you should still go for count(distinct event_id) instead of count(). We see sometimes events are written twice. You can check it by running:
select event_id, count() as n
where collector_tstamp>=getdate()::date - 30
group by 1
order by 2 desc
And see if you have any results. If not, then forget about it
Depending on your definition of bounce rate, you may want to exclude page_ping events from your SQL query.
OP is asking specifically about “the number of bounced sessions i.e. sessions with just a single event”. In that case you’ll need to add event != 'page_ping' to the WHERE clause of the query. Otherwise, the first ping on a page will increase the event count and that user will not be counted as bounced.
Of course, a visitor who only visits one page but stays on it for 5 mins could be seen as highly engaged, depending on what you’re looking to measure. Also, the time between pings is something Snowplow users set by themselves, so keep that in mind when deciding whether to exclude page_pings or not.