Overview

This query compares performance metrics across devices and countries, helping you evaluate how users from different regions engage with your site on mobile, desktop, or tablet. It surfaces insights like traffic volume, new visitor share, and conversion rate by device-country combination, supporting device-specific optimization and geo-targeting decisions.

SQL Query

SELECT
  wa.device as device,
  wa.country as country,
  sum(wa.sessions) as sessions,
  sum(wa.session_page_views) as session_page_views,
  sum(wa.unique_visitors) as unique_visitors,
  sum(wa.new_visitors) as new_visitors,
  new_visitors / unique_visitors as new_visitor_percent,
  sum(wa.orders_quantity) as orders_quantity,
  sum(wa.order_revenue) as order_revenue,
  orders_quantity / sessions as conversion_rate
FROM
  web_analytics_table wa
WHERE
  event_date BETWEEN @start_date AND @end_date
GROUP BY
  grouping sets ((device, country), ())
ORDER BY unique_visitors DESC

Tables and Metrics Used

Table

• web_analytics_table (Web Analytics Table)

Dimensions

• device
• country
• sessions
• session_page_views
• unique_visitors

Measures

• orders_quantity

Derived

• conversion_rate

Customization Options

This query helps evaluate traffic quality and conversion performance by device and country. You can customize it in the following ways:

Filter specific countries or devices

• Add AND country IN ('US', 'CA') or AND device = 'mobile' to the WHERE clause to focus on a particular segment.

Add bounce rate or session duration

• Include fields like avg_session_duration or bounce_rate if available to evaluate engagement quality.

Break out by landing page

• Add landing_page to the SELECT and GROUP BY clauses to analyze performance by entry point.

Compare with previous periods

• Wrap the current query in a WITH clause and join to a version filtered on an earlier date range (e.g. @start_date - 7) to compare performance over time.