frontier_analytics.md

First Order Count BY Month (change to stat_user_last_cart for last)

select count(id) first_cart_count,
    SUM(order_count) first_order_count,
    QUARTER(created) quarter_segment,
    MONTH(created) month_segment,
    YEAR(created) year_segment,
    SUM(subtotal) subtotal,
    SUM(total) total,
    DATE_FORMAT(created,'%Y-%m-01') segment
FROM stat_user_first_cart
GROUP BY segment
ORDER BY segment desc;

First Order Count BY Quarter (change to stat_user_last_cart for last)

select count(id) first_cart_count,
    SUM(order_count) first_order_count,
    QUARTER(created) quarter_segment,
    MONTH(created) month_segment,
    YEAR(created) year_segment,
    SUM(subtotal) subtotal,
    SUM(total) total,
    DATE_FORMAT(created,'%Y-%m-01') segment
from stat_user_first_cart
group by quarter_segment, year_segment
order by year_segment desc, quarter_segment desc;

Expired Customers (Adjust INTERVAL 11 MONTH)

select user_id,
users.username,
users.company_id,
IF(users.company_id, companies.name, users.company) user_company,
QUARTER(stat_user_last_cart.created) quarter_segment,
MONTH(stat_user_last_cart.created) month_segment,
YEAR(stat_user_last_cart.created) year_segment,
stat_user_last_cart.total,
stat_user_last_cart.subtotal,
stat_user_last_cart.order_count,
DATE_FORMAT(stat_user_last_cart.created,'%Y-%m-01') segment
from stat_user_last_cart
    left join users ON users.id = stat_user_last_cart.user_id
    left join companies ON users.company_id = companies.id
where DATE_FORMAT(stat_user_last_cart.created, '%Y-%m-01') =  DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 11 MONTH), '%Y-%m-01') 
order by year_segment desc, quarter_segment desc;

New Customers (adjust interval)

select user_id,
users.username,
users.company_id,
IF(users.company_id, companies.name, users.company) user_company,
QUARTER(stat_user_first_cart.created) quarter_segment,
MONTH(stat_user_first_cart.created) month_segment,
YEAR(stat_user_first_cart.created) year_segment,
stat_user_first_cart.total,
stat_user_first_cart.subtotal,
stat_user_first_cart.order_count,
DATE_FORMAT(stat_user_first_cart.created,'%Y-%m-01') segment
from stat_user_first_cart
    left join users ON users.id = stat_user_first_cart.user_id
    left join companies ON users.company_id = companies.id
where DATE_FORMAT(stat_user_first_cart.created, '%Y-%m-01') =  DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 0 MONTH), '%Y-%m-01') 
order by year_segment desc, quarter_segment desc;

Active Customers (Adjust INTERVAL 12 MONTH)

select count(id) active_cart_count,
    SUM(total) active_cart_total,
    SUM(subtotal) active_cart_subtotal,
    SUM(order_count) active_cart_order_count,
    DATE_FORMAT(created,'%Y-%m-01') segment
from stat_user_last_cart
where DATE_FORMAT(created, '%Y-%m-01') >=  DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 12 MONTH), '%Y-%m-01') ;

Unique User Carts

Unique carts. Multiple carts placed by a single user/company count as 1

select count(segmented_carts.id) unique_user_count, segmented_carts.user_id, segment from (
    select carts.id, carts.user_id, DATE_FORMAT(carts.created, '%Y-%m') segment
    from carts
        left join users on users.id = carts.user_id
    where carts.status_id = 20 and (  users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
    group by segment, CASE WHEN users.company_id IS NULL THEN user_id ELSE users.company_id END
    order by segment desc) segmented_carts
group by segment
order by segment desc;

Unique User Carts (Quarter)

Unique Carts. Grouped by quarter instead of by month

select count(segmented_carts.id) unique_user_count, 
    segmented_carts.user_id, 
    quarter_segment, 
    year_segment, 
    month_segment 
from (
    select carts.id, carts.user_id, 
    QUARTER(carts.created) quarter_segment,
    YEAR(carts.created) year_segment,
    DATE_FORMAT(carts.created, '%Y-%m') month_segment
    from carts
        left join users on users.id = carts.user_id
    where carts.status_id = 20 and ( users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
    group by quarter_segment, year_segment, CASE WHEN users.company_id IS NULL THEN user_id ELSE users.company_id END
    order by year_segment DESC, quarter_segment desc) segmented_carts
group by quarter_segment, year_segment
order by year_segment desc, quarter_segment DESC;

Total Carts

select count(carts.id), DATE_FORMAT(carts.created, '%Y-%m') segment
from carts
left join users on users.id = carts.user_id
where carts.status_id = 20 and (users.company_id NOT IN (19,1,2) OR users.company_id IS NULL)
group by segment
order by segment desc;

Packages by size, Month, Year

select count(sp.id) total, width, height, depth, MONTH(sp.created), YEAR(sp.created)
	from shipment_packages sp
	left join shipments on shipments.id = sp.shipment_id
	where shipments.status_id = 3
	group by YEAR(sp.created), MONTH(sp.created), width, height, depth;