How to check last login date and purchase item date of customer

How to check last login date and purchase item date of customer

October 4, 2026 · Illustrated author avatarBy Stefan Bogdanescu
How to check last login date and purchase item date of customer

Checking Customer Login Dates and Purchase History in Magento

As a senior developer specializing in e-commerce platforms like Magento, I frequently encounter requests for customer analytics to maintain effective customer relations. In your case, you need to identify customers who haven’t logged in within the last three months or made any purchases despite recent logins.

To accomplish this, we’ll need to query the database and apply some logical filters. Let’s break down the requirements:

1. Customers Not Logged In for 3 Months

This involves checking when a customer was last active on your website. Magento stores this information in the customer_entity table. We can write a SQL query to find customers who haven’t logged in within the past three months.

Here’s an example using PHP and PDO:

$threeMonthsAgo = date('Y-m-d H:i:s', strtotime('-3 month'));
$query = "SELECT entity_id, created_at FROM customer_entity WHERE created_at > ? AND last_login_at <= ?";
$stmt = $pdo->prepare($query);
$stmt->execute([$threeMonthsAgo, $threeMonthsAgo]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

In this query:

  • created_at represents the customer’s registration date.
  • last_login_at is when they last logged in.

Only customers registered after three months ago and whose last login was also within that period are returned.

2. Customers with Recent Logins but No Purchases

To find customers who have recently logged in but haven’t made any purchases, we need to join data from the sales_order table (for purchases) with customer_entity.

Here’s a more complex query for this:

$threeMonthsAgo = date('Y-m-d H:i:s', strtotime('-3 month'));
$query = "SELECT ce.entity_id, ce.last_login_at FROM customer_entity AS ce LEFT JOIN sales_order AS so ON ce.entity_id = so.customer_id AND so.created_at > ? WHERE so.entity_id IS NULL";
$stmt = $pdo->prepare($query);
$stmt->execute([$threeMonthsAgo]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

This query uses a LEFT JOIN to match customers with their orders. The sales_order table is filtered for purchases made within the last three months. Customers without matching rows in this subquery (i.e., those who haven’t purchased) are returned.

Conclusion

To maintain effective customer relations, regularly identifying inactive or non-purchasing customers is crucial. By using SQL queries to filter your Magento database based on relevant dates and actions, you can generate the reports you need.

Remember that working with databases directly requires careful handling of permissions, security, and performance optimizations. Always ensure you have a backup strategy in place before making substantial changes.

For more advanced techniques in Magento customer management, consider exploring the Customer Segmentation module from our catalog. It provides enhanced tools for segmenting customers based on their behavior and purchase history.

Happy coding! Let me know if you have any further questions.

Database Customers

Our network