WordPress MySQL server using Query Streams, hosted on Siteground

---

Query Streams enables direct MySQL database access to WordPress sites hosted on SiteGround, allowing technical users to efficiently create and share live SQL queries through Excel and Google Sheets without the need for VPN or complex firewall configuration. This comprehensive guide covers the essential steps for connecting the Query Streams agent to SiteGround’s MySQL environment, configuring necessary database credentials, and building effective WordPress-specific queries for common data extraction scenarios including wp_posts, wp_users, and custom field tables. Once properly configured, these queries can be securely shared with non-technical users who can easily access real-time WordPress data through various spreadsheet add-ons. This process eliminates the need for cumbersome database exports or sensitive credential sharing while maintaining robust read-only security measures.

1. Login and goto your siteground.com account.

Once logged in goto Site Tools option. Goto Site -> MySQL

2. Click the Remote tab, you will need your external IP.

You’ll need the external IP address of the network where your Query Streams agent is running. To find this, visit websites like WhatIsMyIP.com, IPChicken.com, or ipinfo.io from the same network/location where the agent is installed. Make sure you’re checking the IP address from the correct network location, not from a different office, home network, or remote connection.

3. Click the users tab and create a new user

Edit that user, be sure to add a database to the new user.

I recommend read-only

Make note of your username and password and database!. You will need it.

4. Open your network agent https://:1823

Click Data Sources –> Connections –> + Add Connection

Save your configuration.

5. Open your Google Sheets or Excel Addon

In this example, we will use Google Sheets

You will need to get your database prefix, as most WordPress installs have random prefix.

SELECT 
    SUBSTRING_INDEX(table_name, '_', 1) as detected_prefix,
    COUNT(*) as table_count,
    GROUP_CONCAT(
        CASE 
            WHEN table_name LIKE '%_posts' THEN 'posts'
            WHEN table_name LIKE '%_users' THEN 'users'
            WHEN table_name LIKE '%_options' THEN 'options'
            WHEN table_name LIKE '%_comments' THEN 'comments'
            WHEN table_name LIKE '%_postmeta' THEN 'postmeta'
            WHEN table_name LIKE '%_usermeta' THEN 'usermeta'
        END
    ) as wordpress_core_tables,
    CASE 
        WHEN COUNT(*) >= 10 THEN 'Primary WordPress Installation'
        WHEN COUNT(*) >= 5 THEN 'Possible WordPress Installation' 
        ELSE 'Plugin/Custom Tables'
    END as installation_type
FROM information_schema.tables 
WHERE table_schema = DATABASE()
AND table_name LIKE '%_%'
GROUP BY SUBSTRING_INDEX(table_name, '_', 1)
HAVING wordpress_core_tables IS NOT NULL
ORDER BY table_count DESC;

Now you have your prefix.

5. Here are some sample queries to run. Besure to Find/Replace prefix with the one for your site.

.

It is important that you Find and Replace your prefix

Daily Traffic Overview (Last 30 Days)

SELECT 
    DATE(last_counter) as visit_date,
    COUNT(DISTINCT ip) as unique_visitors,
    COUNT(*) as total_visits,
    SUM(hits) as page_views,
    ROUND(SUM(hits) / COUNT(DISTINCT ip), 2) as pages_per_visitor,
    DAYNAME(last_counter) as day_of_week
FROM nxj_statistics_visitor 
WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(last_counter)
ORDER BY visit_date DESC;

Visitor Geographic Distribution

SELECT 
    CASE 
        WHEN location IS NULL OR location = '' THEN 'Unknown Location'
        ELSE location 
    END as visitor_location,
    COUNT(DISTINCT ip) as unique_visitors,
    COUNT(*) as total_visits,
    SUM(hits) as page_views,
    ROUND(COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM nxj_statistics_visitor 
        WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ), 2) as percentage_of_traffic,
    ROUND(SUM(hits) / COUNT(*), 2) as avg_pages_per_visit
FROM nxj_statistics_visitor
WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY visitor_location
ORDER BY unique_visitors DESC
LIMIT 15;

User Behavior & Session Analysis

SELECT 
    CASE 
        WHEN hits = 1 THEN 'Bounce (1 page)'
        WHEN hits BETWEEN 2 AND 3 THEN 'Quick Browse (2-3 pages)'
        WHEN hits BETWEEN 4 AND 7 THEN 'Engaged (4-7 pages)'
        WHEN hits BETWEEN 8 AND 15 THEN 'Very Engaged (8-15 pages)'
        WHEN hits > 15 THEN 'Power User (15+ pages)'
    END as engagement_level,
    COUNT(*) as visitor_count,
    ROUND(COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM nxj_statistics_visitor 
        WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ), 2) as percentage,
    ROUND(AVG(hits), 2) as avg_pages_in_group,
    MIN(hits) as min_pages,
    MAX(hits) as max_pages
FROM nxj_statistics_visitor
WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY engagement_level
ORDER BY avg_pages_in_group;

Device & Technology Usage Patterns

SELECT 
    CASE 
        WHEN agent LIKE '%Mobile%' OR agent LIKE '%Android%' OR agent LIKE '%iPhone%' OR agent LIKE '%BlackBerry%' THEN 'Mobile'
        WHEN agent LIKE '%Tablet%' OR agent LIKE '%iPad%' THEN 'Tablet'
        WHEN agent LIKE '%Windows%' OR agent LIKE '%Macintosh%' OR agent LIKE '%Linux%' OR agent LIKE '%Ubuntu%' THEN 'Desktop'
        WHEN agent LIKE '%bot%' OR agent LIKE '%Bot%' OR agent LIKE '%spider%' THEN 'Bot/Crawler'
        ELSE 'Unknown Device'
    END as device_type,
    
    CASE 
        WHEN agent LIKE '%Chrome%' AND agent NOT LIKE '%Chromium%' THEN 'Chrome'
        WHEN agent LIKE '%Firefox%' THEN 'Firefox'
        WHEN agent LIKE '%Safari%' AND agent NOT LIKE '%Chrome%' THEN 'Safari'
        WHEN agent LIKE '%Edge%' THEN 'Edge'
        WHEN agent LIKE '%Opera%' THEN 'Opera'
        WHEN agent LIKE '%bot%' OR agent LIKE '%Bot%' THEN 'Bot'
        ELSE 'Other Browser'
    END as browser_type,
    
    COUNT(*) as visits,
    COUNT(DISTINCT ip) as unique_visitors,
    SUM(hits) as page_views,
    ROUND(AVG(hits), 2) as avg_pages_per_visit,
    ROUND(COUNT(*) * 100.0 / (
        SELECT COUNT(*) FROM nxj_statistics_visitor 
        WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ), 2) as percentage_of_traffic
FROM nxj_statistics_visitor
WHERE last_counter >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND agent IS NOT NULL
GROUP BY device_type, browser_type
ORDER BY visits DESC
LIMIT 20;