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:
- Overview before installing Magento
- Install Magento onto MySQL
- Configure a MYSQL database instance for Magento
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.