Database querying
In some cases it can be useful to query the UXWizz database directly. This page explains the database structure and how the data is stored in it.
Database structure
NOTE: This was the database structure for version 3.5.0. A newerer version might include new tables.
Here is a diagram created using PHPMyAdmin->Designer
for an existing UXWizz database:
Remarks
ust_clients - Stores unique sessions/visits. An unique user can have multiple entries. The
ip
can be used to link all sessions from the user.ust_clientpage - Stores unique pageviews. Each session has multiple pageviews.
ust_records/ust_partials - Store the record playback information for a specific pageview.
ust_movements/ust_clicks - Heatmap data, stored per pageview.
ust_client_tag - Session level tag/label/event data.
ust_client_event - Events stored linked to a specific pageview, session and user.
ust_ab - Stores AB tests
ust_users (internal) - Stores list of UXWizz dashboard users
ust_access (internal) - Stores which domain data can be accessed by which users
Examples
Basic query
SELECT * FROM `ust_clients` WHERE `domain`="domain.com"
Domain change (move all users tracked from one domain to another)
UPDATE ust_clients SET `domain`='new.com' WHERE `domain`='old.com';
Get the path each visitor took before first reaching the pricing page:
SELECT
t1.clientid as clientID,
GROUP_CONCAT(t1.page ORDER BY t1.id ASC SEPARATOR ' -> ') as path
FROM
ust_clientpage t1
WHERE
t1.id <= (
SELECT MIN(t2.id) as min_id FROM ust_clientpage t2
WHERE t2.page LIKE '%pricing%' AND t2.clientid = t1.clientid
)
AND t1.clientid IN (SELECT id FROM ust_clients WHERE domain = 'uxwizz.com')
GROUP BY t1.clientid
ORDER BY t1.clientid
DESC LIMIT 10;
# Example output:
# [{"clientID":126951,"path":"/hotjar-alternative -> /pricing"}]
Last updated
Was this helpful?