function pp_generate_insights(){
global $wpdb;
$prices = $wpdb->prefix . 'pp_prices';
$products = $wpdb->prefix . 'pp_products';
$stores = $wpdb->prefix . 'pp_stores';
$clicks = $wpdb->prefix . 'pp_clicks';
$insights = [];
// Total clicks per store
$store_clicks = $wpdb->get_results("
SELECT s.store_name, COUNT(c.id) as total
FROM $clicks c
JOIN $stores s ON c.store_id = s.id
GROUP BY c.store_id
ORDER BY total DESC
");
if($store_clicks){
$top = $store_clicks[0];
$insights[] = "🔥 {$top->store_name} is your top performing store.";
}
// Products with zero clicks
$dead_products = $wpdb->get_results("
SELECT p.name
FROM $products p
LEFT JOIN $clicks c ON p.id = c.product_id
WHERE c.id IS NULL
");
foreach($dead_products as $dp){
$insights[] = "⚠ {$dp->name} has zero clicks.";
}
// Products with no price
$no_price = $wpdb->get_results("
SELECT pr.name
FROM $products pr
LEFT JOIN $prices p ON pr.id = p.product_id
WHERE p.price IS NULL OR p.price = 0
GROUP BY pr.id
");
foreach($no_price as $np){
$insights[] = "💸 {$np->name} missing price data.";
}
return $insights;
}
Plastic – pricepair.in
