Articles in this section

Magento

NOTE: Certain information in this article may be missing or out of date. We are in the process of updating the content.

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