UXWizz
WebsitePricingDemoTwitter (X)
  • Introduction
  • 🛠️ Installation
    • Requirements
      • Limitations
      • Server specs (CPU, RAM)
    • Installation guide
      • Uploading the script
      • Creating a MySQL database
      • Running the installer
    • Install on a new server
      • Ubuntu 20.04 (or higher)
      • DigitalOcean
    • Docker
      • Via Docker Compose
      • Standalone Docker image
    • Adding the tracking code
      • Automatic SPA pageview tracking
      • Add tracker to Next.js App
      • Add tracker to React Router App
    • Optimization tips
      • MySQL/MariaDB
      • Auto-delete old data (cron jobs)
      • Apache
    • Frequently Asked Questions
  • 🔧JavaScript API
    • Tags
    • Events
    • Hooks/callbacks
    • Other API functions
    • Session Recording
      • Ignore specific elements
  • 📖Guides and features
    • Goals (NEW!)
    • Ask AI (NEW!)
    • Basic usage
    • A/B testing
    • Usage tips
    • Dashboard user access level
    • Resetting the admin password
    • Database querying
    • Technical details
      • IP Geolocation
    • Troubleshooting
      • Agency
        • MultiDB
      • Dashboard
        • Refreshing dashboard sub-page leads to 404 error
        • Website iframe not loading (x-frame-options)
        • License says "invalid"
        • Updating Fails
      • Tracking
        • No data is being recorded
        • Can't include tracker via Google Tag Manager
        • The A/B test JS file is missing
      • WordPress
        • Cloudways 403 Forbidden screen on WordPress
        • NGINX 403 Forbidden screen on WordPress
    • Extending the dashboard
    • Support
    • Migrating to a new server
  • 🎓Useful Examples
    • Feedback form (polls)
    • Tracking 404 Pages
    • Tracking UTM parameters
    • Tracking Google Ads GCLID
    • Storing user device types
    • Track video playback
  • 📜About
    • Changelog
    • Personal Data Information
    • Privacy Policy (uxwizz.com)
    • Licenses and pricing
    • [Deprecated] License Subscriptions
Powered by GitBook
On this page
  • Database structure
  • Remarks
  • Examples

Was this helpful?

  1. Guides and features

Database querying

PreviousResetting the admin passwordNextTechnical details

Last updated 1 year ago

Was this helpful?

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

For improved performance, there are multiple indexes already created. Those indexes are specifically created for the queries ran for the UXWizz dashboard, but they should also lead to very fast query performance in most cases.

Examples

You can find many example queries in the PHP source code of UXWizz. Usually each PHP file only runs a single query.

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"}]
📖