Hello Friend's,
Here some usefully mysql query.
========================================================================
Number Orders - Sales - Avg Per Order
*********************************************************************************
AVG Order Value Per Day
SELECT DATE(`created_at`) AS 'Date', AVG(`grand_total`) AS 'AVG Order Value Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC
--------------------------------------------------------------------------------------
AVG Order Value Per Week
SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC
--------------------------------------------------------------------------------------
AVG Order Value Per Month
SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`)
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC
--------------------------------------------------------------------------------------
AVG Order Value Per year
SELECT YEAR(`created_at`) AS 'Year', AVG(`grand_total`) AS 'AVG Order Value Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Sale Per Day
SELECT DATE(`created_at`) AS 'Date',SUM(`grand_total`) AS 'Total Sale Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Sale Per Week
SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC
--------------------------------------------------------------------------------------
Total sales per month
SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`), MONTH(`created_at`)
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Sale Per year
SELECT YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total Sale Per year' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC
--------------------------------------------------------------------------------------
Specific Month of year sale
EX.
" MONTH(`created_at`) = '01' " = Number of month(05)
" YEAR(`created_at`) = '2016' " = Number of year(2013)
SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`grand_total`) AS 'Total sale',`status` FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND MONTH(`created_at`) = '01' AND YEAR(`created_at`) = '2016'
--------------------------------------------------------------------------------------
Specific Month wise yearly sale
EX.
" YEAR(`created_at`) = '2015' " = Number of year(2013)
SELECT MONTHNAME(`created_at`) AS 'Month', SUM(`grand_total`) AS 'Total Sale Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `status` = 'complete' AND YEAR(`created_at`) = '2015' GROUP BY MONTH(`created_at`) ORDER BY MONTH(`created_at`) ASC
--------------------------------------------------------------------------------------
Total Order Per Day
SELECT DATE(`created_at`) AS 'Date', COUNT(*) AS 'Total Order Per Day' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY DATE(`created_at`) ORDER BY DATE(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Order Per Week
SELECT WEEK(`created_at`) AS 'Week',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Week' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY WEEK(`created_at`),YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC, WEEK(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Order Per Month
SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`), MONTH(`created_at`) ORDER BY YEAR(`created_at`) DESC,MONTH(`created_at`) DESC
--------------------------------------------------------------------------------------
Total Order Per Year
SELECT YEAR(`created_at`) AS 'Year', COUNT(*) AS 'Total Order Per Year' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY YEAR(`created_at`) ORDER BY YEAR(`created_at`) DESC
*******************************************
Sales by New Customers (How many new and sales)
Consider created customers in current month customers as new customer
SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) = MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) = YEAR(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`
*********************************************************************************
Sales by Existing Customers
Consider not created customers in current month as existing customer
SELECT `sales_flat_order`.`customer_email`,`sales_flat_order`.`customer_id`,`customer_entity`.`created_at` AS 'Customer Created Date',`sales_flat_order`.`created_at` AS 'Order Created Date',COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` JOIN `customer_entity` ON `customer_entity`.`entity_id` = `sales_flat_order`.`customer_id` WHERE MONTH(`customer_entity`.`created_at`) != MONTH(NOW()) AND YEAR(`customer_entity`.`created_at`) != YEAR(NOW()) AND MONTH(`sales_flat_order`.`created_at`) = MONTH(NOW()) AND YEAR(`sales_flat_order`.`created_at`) = YEAR(NOW()) AND `sales_flat_order`.`store_id` = '2' GROUP BY `sales_flat_order`.`customer_email`
*********************************************************************************
Sales by Customers By Date
SELECT `customer_firstname`,`customer_lastname`,`customer_email`,`customer_id`, COUNT(*) AS 'Total Order Per Customer', DATE(`created_at`) AS 'Date' FROM `sales_flat_order` where `store_id` = 2 GROUP BY DATE(`created_at`), `customer_email` ORDER BY DATE(`created_at`) DESC
*********************************************************************************
Sales By State
SELECT `sales_flat_order_address`.`region`,count(*) FROM `sales_flat_order` JOIN `sales_flat_order_address` ON `sales_flat_order`.`entity_id` = `sales_flat_order_address`.`parent_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_address`.`address_type` = 'shipping' GROUP BY `sales_flat_order_address`.`region`
*********************************************************************************
Sales By Product
--------------------------------------------------------------------------------------
Total Sale for all product
SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order`.`status` = 'complete' GROUP BY `sales_flat_order_item`.`sku`
--------------------------------------------------------------------------------------
Total Sale for specific Product
SELECT `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete'
--------------------------------------------------------------------------------------
Total Sale for specific Product with specific Day
SELECT DATE(`sales_flat_order`.`created_at`) AS 'Date', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND DATE(`sales_flat_order`.`created_at`) = '2015-12-15'
--------------------------------------------------------------------------------------
Total Sale for specific Product with specific month period
SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', MONTHNAME(`sales_flat_order`.`created_at`) AS 'Month', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND MONTH(`sales_flat_order`.`created_at`) = '10' AND YEAR(`sales_flat_order`.`created_at`) = '2014'
--------------------------------------------------------------------------------------
Total Sale for specific Product with specific year period
SELECT YEAR(`sales_flat_order`.`created_at`) AS 'Year', `sales_flat_order_item`.`sku` AS 'Product SKU', ROUND(SUM(`sales_flat_order_item`.`qty_ordered`)) AS 'Total Product Sale' FROM `sales_flat_order` JOIN `sales_flat_order_item` ON `sales_flat_order`.`entity_id` = `sales_flat_order_item`.`order_id` WHERE `sales_flat_order`.`store_id` = 2 AND `sales_flat_order_item`.`sku` = 'JRGEL-60' AND `sales_flat_order`.`status` = 'complete' AND YEAR(`sales_flat_order`.`created_at`) = '2015'
*********************************************************************************
Sales by type of Customer
SELECT `customer_group`.`customer_group_code` AS 'Customer Role', COUNT(`sales_flat_order`.`customer_id`) AS 'Total Customer' FROM `sales_flat_order` JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id` where `sales_flat_order`.`store_id` = 2 GROUP BY `customer_group`.`customer_group_code`
--------------------------------------------------------------------------------------
Sales by specific type of Customer
EX. - 'Wholesale'
SELECT `sales_flat_order`.`entity_id`, `sales_flat_order`.`customer_id`,`sales_flat_order`.`customer_email`,`customer_group`.`customer_group_code` FROM `sales_flat_order`
JOIN `customer_group` ON `customer_group`.`customer_group_id` = `sales_flat_order`.`customer_group_id`
where `sales_flat_order`.`store_id` = 2 AND `customer_group`.`customer_group_code` = 'Wholesale'
*********************************************************************************
Sales by customer on specific period (Report that show how many times during year)
SELECT YEAR(`created_at`) AS 'Year',`customer_firstname`,`customer_lastname`,`customer_email` AS 'Customer Email',`customer_id`,COUNT(*) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2 AND YEAR(`created_at`) = '2015' GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC
*********************************************************************************
Birthdays per month (List Customers)
SELECT `customer_entity`.`email`,`customer_entity_varchar`.`value` FROM `customer_entity` JOIN `customer_entity_varchar` ON `customer_entity`.`entity_id` = `customer_entity_varchar`.`entity_id` WHERE `customer_entity_varchar`.`attribute_id` = 11 AND MONTH(`customer_entity_varchar`.`value`) = MONTH(NOW())
*********************************************************************************
Number of orders per customers
SELECT `customer_email` AS 'Customer Email',COUNT(`customer_email`) AS 'Total Order By Customer' FROM `sales_flat_order` WHERE `store_id` = 2 GROUP BY `customer_email` ORDER BY COUNT(`customer_email`) DESC
*********************************************************************************
Show all customer attribute data from Magento database
SELECT ce.*, ea.attribute_code,
CASE ea.backend_type
WHEN 'varchar' THEN ce_varchar.value
WHEN 'int' THEN ce_int.value
WHEN 'text' THEN ce_text.value
WHEN 'decimal' THEN ce_decimal.value
WHEN 'datetime' THEN ce_datetime.value
ELSE NULL
END AS value
FROM customer_entity AS ce
LEFT JOIN eav_attribute AS ea ON ce.entity_type_id = ea.entity_type_id
LEFT JOIN customer_entity_varchar AS ce_varchar ON ce.entity_id = ce_varchar.entity_id AND ea.attribute_id = ce_varchar.attribute_id AND ea.backend_type = 'varchar'
LEFT JOIN customer_entity_int AS ce_int ON ce.entity_id = ce_int.entity_id AND ea.attribute_id = ce_int.attribute_id AND ea.backend_type = 'int'
LEFT JOIN customer_entity_text AS ce_text ON ce.entity_id = ce_text.entity_id AND ea.attribute_id = ce_text.attribute_id AND ea.backend_type = 'text'
LEFT JOIN customer_entity_decimal AS ce_decimal ON ce.entity_id = ce_decimal.entity_id AND ea.attribute_id = ce_decimal.attribute_id AND ea.backend_type = 'decimal'
LEFT JOIN customer_entity_datetime AS ce_datetime ON ce.entity_id = ce_datetime.entity_id AND ea.attribute_id = ce_datetime.attribute_id AND ea.backend_type = 'datetime'
*********************************************************************************
Update status form "fulfillment" to "complete" in sales order table
SELECT * FROM `sales_flat_order` where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'
UPDATE `sales_flat_order` SET `status` = 'complete' where `store_id` = 2 AND `state` = 'complete' AND `status` = 'fulfillment'
----------------------------------------------------------------------------------------
Update status form "fulfillment" to "complete" in sales order grid table
SELECT sg.* FROM `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'
UPDATE `sales_flat_order_grid` as sg JOIN `sales_flat_order` as s ON s.`entity_id` = sg.`entity_id` SET sg.`status` = 'complete' WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment'
----------------------------------------------------------------------------------------
Delete status "fulfillment" form sales order status history table
SELECT sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'
DELETE sh.* FROM `sales_flat_order_status_history` as sh JOIN `sales_flat_order` as s ON s.`entity_id` = sh.`parent_id` WHERE s.`store_id` = 2 AND s.`state` = 'complete' AND s.`status` = 'fulfillment' AND sh.`status` = 'fulfillment' AND sh.`entity_name` = 'order'
*********************************************************************************
Update order table date (day light saving)
UPDATE `sales_flat_order`
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE)
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');
----------------------------------------------------------------------------------------
Update order grid table date (day light saving)
UPDATE `sales_flat_order_grid`
SET `created_at`= ADDDATE(`created_at`, INTERVAL 60 MINUTE)
WHERE `store_id`= 3 AND (created_at BETWEEN '2011-11-06 02:00:00' AND '2012-03-11 02:00:00'
OR created_at BETWEEN '2012-11-04 02:00:00' AND '2013-03-10 02:00:00'
OR created_at BETWEEN '2013-11-03 02:00:00' AND '2014-03-09 02:00:00'
OR created_at BETWEEN '2014-11-02 02:00:00' AND '2015-03-08 02:00:00'
OR created_at BETWEEN '2015-11-01 02:00:00' AND '2016-03-13 02:00:00');
*********************************************************************************
Get all refund
SELECT MONTHNAME(`created_at`) AS 'Month',YEAR(`created_at`) AS 'Year', SUM(`total_refunded`) AS 'Total Refund Per Month' FROM `sales_flat_order` WHERE `store_id` = 2 AND `total_refunded` != '' GROUP BY YEAR(`created_at`), MONTH(`created_at`)
ORDER BY YEAR(`created_at`) DESC, MONTH(`created_at`) DESC
This comment has been removed by the author.
ReplyDelete