Articles in this section

Magento

Overview

Magento’s highly flexible commerce platform helps businesses of any size provide more frequent high-quality customer buying experiences. Here are a few things you can expect from the Magento/Grow integration.

The connection requires you to write SQL, so you will want to involve a developer or IT person. When you have the queries to bring in the data you want to use, we would recommend that you create a few datasets in Grow to have consistent reports.

How to Connect

It is best to connect your Magento data to Grow through a MySQL database since Magento works with MySQL natively. Here is the help article from Grow on connecting to MySQL

Here are some other articles from Magento's developer documentation:

Endpoints

To help you get started getting Magento data through MySQL, here are some common queries you can use.

Orders over the last 30 days:

SELECT SUBSTRING((created_at - INTERVAL 8 HOUR),1,10) as date, 
round(sum(base_subtotal+base_discount_amount+base_shipping_amount),2) as total_sales, 
count(*) as total_orders
FROM sales_flat_order o
WHERE (created_at - INTERVAL 8 HOUR) >= DATE(NOW() - INTERVAL 8 HOUR - INTERVAL 29 DAY)
AND state <> 'canceled'
GROUP BY date

Average Order Value over the last 30 days:

SELECT date, total_sales, total_orders, 
ROUND(total_sales / total_orders,2) as average_order_value

FROM

(SELECT SUBSTRING((created_at - INTERVAL 8 HOUR),1,10) as date, 
round(sum(base_subtotal+base_discount_amount+base_shipping_amount),2) as total_sales, 
count(*) as total_orders
FROM sales_flat_order o
WHERE (created_at - INTERVAL 8 HOUR) >= DATE(NOW() - INTERVAL 8 HOUR - INTERVAL 29 DAY)
AND state <> 'canceled'
GROUP BY date) t1

Top Products:

SELECT 
 product_id, 
 name,
 (SUM(qty_invoiced) - 
 (SUM(qty_canceled)+SUM(qty_refunded))) 
 as orderQty,
 (SUM(price)-SUM(amount_refunded)) 
 as totalRevenue

FROM sales_flat_order_item
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-%d') - INTERVAL 30 DAY
GROUP BY 1,2
ORDER BY 3 DESC

Orders by Hour (Today):

SELECT SUBSTRING((created_at - INTERVAL 8 HOUR),12,2) as hour, 
round(sum(base_subtotal+base_discount_amount+base_shipping_amount),2) as total_sales, 
count(*) as total_orders
FROM sales_flat_order
WHERE (created_at - INTERVAL 8 HOUR) >= CURDATE() 
AND state <> 'canceled'
GROUP BY hour

Orders by Hour (Yesterday):

SELECT SUBSTRING((created_at - INTERVAL 8 HOUR),12,2) as hour, 
round(sum(base_subtotal+base_discount_amount+base_shipping_amount),2) as total_sales, 
count(*) as total_orders
FROM sales_flat_order
WHERE (created_at - INTERVAL 8 HOUR) >= DATE_SUB(CURDATE(), INTERVAL 2 DAY)
AND (created_at - INTERVAL 8 HOUR) < CURDATE()
AND state <> 'canceled'
GROUP BY hour

FAQs + Tips and Tricks

Here is a help article from Magento about optimizing your SQL queries.

Related articles