Last Month Metrics or Data
You can get data for any number of past months by changing -1 month in the SQL query {"(INTERVAL '-1 month')"} to the number of months you need it for. Data will be grouped by month.
Getting last month sales for an instance
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , sum ( "public" . "spree_orders" . "total" ) AS "value" | FROM "public" . "spree_orders" WHERE ( "public" . "spree_orders" . "state" = 'complete' AND "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp ))) GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC
Getting last month's completed orders for an instance
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , count ( * ) AS "value" FROM "public" . "spree_orders" WHERE ( "public" . "spree_orders" . "state" = 'complete' AND "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp ))) GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC
Getting last month's new enterprise signups for an instance
Copy SELECT date_trunc( 'month' , "public" . "enterprises" . "created_at" ) AS "created_at" , count ( distinct "public" . "enterprises" . "id" ) AS "value" | FROM "public" . "enterprises" WHERE ( "public" . "enterprises" . "created_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "enterprises" . "created_at" < date_trunc( 'month' , CAST ( now () AS timestamp ))) GROUP BY date_trunc( 'month' , "public" . "enterprises" . "created_at" ) ORDER BY date_trunc( 'month' , "public" . "enterprises" . "created_at" ) ASC
Getting number of shoppers with a completed order in last month for an instance
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , count ( distinct "public" . "spree_orders" . "email" ) AS "value" | FROM "public" . "spree_orders" WHERE ( "public" . "spree_orders" . "state" = 'complete' AND "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp ))) GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC
Getting number of Active Shopfronts in last month for an instance
Number of shopfronts/shops with at least 1 completed order (can incl. test orders) from their shop
Copy SELECT date_trunc( 'month' , "Spree Orders" . "completed_at" ) AS "completed_at" , count ( distinct "public" . "enterprises" . "id" ) AS "value" | FROM "public" . "enterprises" LEFT JOIN "public" . "spree_orders" "Spree Orders" ON "public" . "enterprises" . "id" = "Spree Orders" . "distributor_id" WHERE ( "Spree Orders" . "state" = 'complete' AND "Spree Orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "Spree Orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp ))) GROUP BY date_trunc( 'month' , "Spree Orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "Spree Orders" . "completed_at" ) ASC
Getting number of Active Producers in last month for an instance
Number of producers who has an item sold in the last month via any shop
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , count ( distinct "Enterprises" . "id" ) AS "value" | FROM "public" . "spree_orders" LEFT JOIN "public" . "spree_line_items" "Spree Line Items" ON "public" . "spree_orders" . "id" = "Spree Line Items" . "order_id" LEFT JOIN "public" . "spree_variants" "Spree Variants" ON "Spree Line Items" . "variant_id" = "Spree Variants" . "id" LEFT JOIN "public" . "spree_products" "Spree Products" ON "Spree Variants" . "product_id" = "Spree Products" . "id" LEFT JOIN "public" . "enterprises" "Enterprises" ON "Spree Products" . "supplier_id" = "Enterprises" . "id" WHERE ( "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp )) AND "public" . "spree_orders" . "state" = 'complete' ) GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC
Getting last month's sale by individual shopfronts for an instance
Shop filter: Sells = own
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , sum ( "public" . "spree_orders" . "total" ) AS "value"
FROM "public" . "spree_orders"
LEFT JOIN "public" . "enterprises" "enterprises__via__distributor_" ON "public" . "spree_orders" . "distributor_id" = "enterprises__via__distributor_" . "id"
WHERE ( "public" . "spree_orders" . "state" = 'complete'
AND "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp )) AND "enterprises__via__distributor_" . "sells" = 'own' )
GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" )
ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC
Getting last month's sale by hub shopfronts for an instance
Shop filter: Sells = any
Copy SELECT date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) AS "completed_at" , sum ( "public" . "spree_orders" . "total" ) AS "value" | FROM "public" . "spree_orders" LEFT JOIN "public" . "enterprises" "enterprises__via__distributor_" ON "public" . "spree_orders" . "distributor_id" = "enterprises__via__distributor_" . "id" WHERE ( "public" . "spree_orders" . "state" = 'complete' AND "public" . "spree_orders" . "completed_at" >= date_trunc( 'month' , CAST (( CAST ( now () AS timestamp ) + (INTERVAL '-1 month' )) AS timestamp )) AND "public" . "spree_orders" . "completed_at" < date_trunc( 'month' , CAST ( now () AS timestamp )) AND "enterprises__via__distributor_" . "sells" = 'any' ) GROUP BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ORDER BY date_trunc( 'month' , "public" . "spree_orders" . "completed_at" ) ASC