如何修改SQL查询获取无订单且一年未活跃的WooCommerce用户

你可以通过添加针对last_update元数据的筛选条件，过滤出超过12个月没有活动的客户。以下是修改后的完整SQL查询：

SELECT DISTINCT `user_id`
FROM `wp_usermeta`
WHERE
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities' AND `meta_value` = 'a:1:{s:8:"customer";b:1;}') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'first_name' AND `meta_value` = '') AND
`user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_name' AND `meta_value` = '') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_first_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'billing_last_name') AND
`user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = '_order_count' AND meta_value > 0) AND
-- 新增：筛选超过12个月无活动的客户
(
    -- 情况1：存在last_update记录且超过12个月未更新
    `user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_update' AND STR_TO_DATE(`meta_value`, '%Y-%m-%d %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL 12 MONTH))
    OR
    -- 情况2：无last_update记录（视为从未有过活动）
    `user_id` NOT IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_update')
);

关键说明： #

• 如果你的last_update存储的是Unix时间戳而非日期字符串，把对应的条件替换为：

  `user_id` IN (SELECT DISTINCT `user_id` FROM `wp_usermeta` WHERE `meta_key` = 'last_update' AND CAST(`meta_value` AS UNSIGNED) <= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)))
  

• 注意替换表前缀wp_为你实际使用的WordPress数据库前缀。
• 新增条件同时覆盖两类客户：有活动记录但超过12个月未活跃的，以及从未产生过任何活动记录的客户。

内容的提问来源于stack exchange，提问作者Zed0121