Conversion Analysis

Keep two things in mind when analyzing your Data Export data:

1. Rows are individual events created during sessions
2. Individual sessions can be found by grouping on a compound key of sessionid + userid

More information about how Fullstory defines sessions can be found here.

All SQL examples are written in Postgres SQL.

Conversion drop-off

You can find individual sessions where users completed one action in a conversion funnel, but not a subsequent action. In this example, we're finding users who clicked a "Checkout" button but not a "Purchase" button on an ecommerce site.

This example also demonstrates how you can construct Fullstory session replay URLs. You can find your org id by going to the JavaScript snippet on the settings page in the Fullstory application: window['_fs_org'] = 'your org id';

Sessions where users checked-out but did not purchase

select fs1.eventtargettext as "checkout event",
fs2.eventtargettext as "purchase event",
fs1.useremail as "email",
'https://app.fullstory.com/ui/'||'your org id'||'/session/'||fs1.userid||':'||fs1.sessionid as "session replay URL"
from fsexport fs1
left outer join
	(select userid, sessionid, eventtargettext
	from fsexport
	where eventtargettext = 'Purchase') fs2
on fs1.userid = fs2.userid and fs1.sessionid = fs2.sessionid
where "checkout event" = 'Checkout' and "purchase event" is null

Conversion time

You can discover the length of time between two events in a conversion funnel and watch the session replays for each conversion. In this example, we've found how long it takes users to click on a "Purchase" button after clicking on a "Checkout" button on an ecommerce site.

Amount of time between checkout and purchase

select
trunc(extract(second from (Max(fs2.eventstart) - Min(fs1.eventstart)))/60, 2) as "conversion time (minutes)",
'https://app.fullstory.com/ui/'||'your org id'||'/session/'||fs1.userid||':'||fs1.sessionid as "session replay URL"
from fsexport fs1
inner join fsexport fs2
	on fs1.sessionid = fs2.sessionid and fs1.userid = fs2.userid
	where fs1.eventtargettext = 'Checkout' and fs2.eventtargettext = 'Purchase'
group by fs1.sessionid, fs1.userid 
order by "conversion time (minutes)" desc