magento2-sales-order-statistics

magento2-sales-order-statistics.sql

-- Sets the timezone for the current session.
SET time_zone = '+02:00';

-- Orders Per Year --
SELECT period_date as year, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y') AS period_date
         FROM sales_order o
         WHERE o.created_at > '2000-01-01'
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date ORDER BY period_date DESC;

-- Orders Per Day for Last Month --
SELECT period_date as day, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d') AS period_date
         FROM sales_order o
         WHERE o.created_at > date_sub(CURDATE(), INTERVAL 1 MONTH)
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date ORDER BY period_date DESC;

-- Orders Per Hour for Last 24 Hrs --
SELECT period_date as hour, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d %H:00') AS period_date
         FROM sales_order o
         WHERE o.created_at > date_sub(CURDATE(), INTERVAL 1 DAY)
         GROUP BY period_date
     ) t GROUP BY period_date ORDER BY period_date DESC;

-- peak orders per minute --
SELECT period_date as peak_minute, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i') AS period_date
         FROM sales_order o
         WHERE o.created_at > '2000-01-01'
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date
         ORDER BY order_count DESC, period_date DESC LIMIT 10;

-- peak orders per hour --
SELECT period_date as peak_hour, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d %H:00') AS period_date
         FROM sales_order o
         WHERE o.created_at > '2000-01-01'
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date
ORDER BY order_count DESC, period_date DESC LIMIT 10;

-- peak orders per day --
SELECT period_date as peak_day, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d') AS period_date
         FROM sales_order o
         WHERE o.created_at > '2000-01-01'
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date
ORDER BY order_count DESC, period_date DESC LIMIT 10;

-- peak orders per week --
SELECT period_date as year_week, order_count, gross_revenue, ROUND(gross_revenue / order_count, 2) AS gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%u') AS period_date
         FROM sales_order o
         WHERE o.created_at > '2000-01-01'
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t GROUP BY period_date
ORDER BY order_count DESC, period_date DESC LIMIT 10;

-- avg orders per day of week past year--
SELECT period_date as day_of_week,
       order_count,
       ROUND(AVG(order_count), 1) AS avg_orders,
       MAX(order_count) AS max_orders,
       ROUND(AVG(gross_revenue), 2) AS avg_gross_revenue,
       MAX(gross_revenue) AS max_gross_revenue,
       ROUND(AVG(gross_revenue) / AVG(order_count), 2) AS avg_gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%W') AS period_date,
             DATE_FORMAT(o.created_at, '%w') AS period_num
         FROM sales_order o
         WHERE o.created_at > date_sub(CURDATE(), INTERVAL 1 YEAR)
           AND o.created_at <= CURDATE()
         GROUP BY period_date, period_num
     ) t
GROUP BY period_date, period_num
ORDER BY period_num ASC;

-- avg and max orders per hourly period --
SELECT period_date as hour,
       order_count,
       ROUND(AVG(order_count), 1) AS avg_orders,
       MAX(order_count) AS max_orders,
       ROUND(AVG(gross_revenue), 2) AS avg_gross_revenue,
       MAX(gross_revenue) AS max_gross_revenue,
       ROUND(AVG(gross_revenue) / AVG(order_count), 2) AS avg_gross_aov
FROM (
         SELECT
             COUNT(*) AS order_count,
             ROUND(SUM(base_grand_total), 2) AS gross_revenue,
             DATE_FORMAT(o.created_at, '%Y-%m-%d %H:00') AS period_date
         FROM sales_order o
         WHERE o.created_at > date_sub(CURDATE(), INTERVAL 1 YEAR)
           AND o.created_at <= CURDATE()
         GROUP BY period_date
     ) t
GROUP BY period_date
ORDER BY period_date ASC;