105 lines
4.1 KiB
Python
105 lines
4.1 KiB
Python
|
"""Market trends feed for kant-en-klaar / halal ready meals."""
|
||
|
|
from __future__ import annotations
|
||
|
|
|
||
|
|
from typing import Any
|
||
|
|
|
||
|
|
from app.db import execute_returning, fetch_all, json_param
|
||
|
|
|
||
|
|
|
||
|
|
TREND_SEEDS = [
|
||
|
|
{
|
||
|
|
"category": "kant-en-klaar",
|
||
|
|
"trend_name": "Halal ready-meals groei stedelijk",
|
||
|
|
"description": "Stedelijke gebieden met hoge niet-westerse bevolking tonen vraag naar halal kant-en-klaar zonder voldoende schap-aanbod.",
|
||
|
|
"source": "CBS + retail intelligence",
|
||
|
|
"confidence_score": 0.82,
|
||
|
|
"opportunity_score": 0.88,
|
||
|
|
"related_products": ["halal maaltijden", "microwave meals", "salades"],
|
||
|
|
"action_items": ["Target Plus/Jumbo regio's met halal-gap score >60", "Pilot schap bij 3 filialen"],
|
||
|
|
},
|
||
|
|
{
|
||
|
|
"category": "halal",
|
||
|
|
"trend_name": "Certificering als vertrouwen-driver",
|
||
|
|
"description": "Filialen met halal-certificering maar beperkt ready-meal assortiment = upsell kans voor Foodlinkk.",
|
||
|
|
"source": "halal_registry + CRM",
|
||
|
|
"confidence_score": 0.75,
|
||
|
|
"opportunity_score": 0.80,
|
||
|
|
"related_products": ["HQC gecertificeerde maaltijden"],
|
||
|
|
"action_items": ["Match HQC stores met CRM pipeline", "Cross-sell bestaande klanten"],
|
||
|
|
},
|
||
|
|
{
|
||
|
|
"category": "kant-en-klaar",
|
||
|
|
"trend_name": "Convenience trend post-COVID",
|
||
|
|
"description": "Gemiddeld inkomen en eenpersoonshuishoudens correleren met groei kant-en-klaar segment.",
|
||
|
|
"source": "CBS kerncijfers",
|
||
|
|
"confidence_score": 0.70,
|
||
|
|
"opportunity_score": 0.72,
|
||
|
|
"related_products": ["single-serve", "meal kits"],
|
||
|
|
"action_items": ["Filter winkels op huishoudens + inkomen >€35k"],
|
||
|
|
},
|
||
|
|
]
|
||
|
|
|
||
|
|
|
||
|
|
def refresh_trends_from_social() -> dict[str, Any]:
|
||
|
|
"""Derive trend signals from social mentions keywords."""
|
||
|
|
mentions = fetch_all(
|
||
|
|
"""SELECT platform, text, sentiment_score, created_at FROM social_mentions
|
||
|
|
WHERE created_at > NOW() - interval '30 days'
|
||
|
|
ORDER BY created_at DESC LIMIT 100"""
|
||
|
|
)
|
||
|
|
keywords = {
|
||
|
|
"halal": 0, "kant-en-klaar": 0, "ready meal": 0, "meal prep": 0,
|
||
|
|
"supermarkt": 0, "schap": 0, "afhalen": 0,
|
||
|
|
}
|
||
|
|
for m in mentions:
|
||
|
|
text = (m.get("text") or "").lower()
|
||
|
|
for kw in keywords:
|
||
|
|
if kw in text:
|
||
|
|
keywords[kw] += 1
|
||
|
|
|
||
|
|
created = 0
|
||
|
|
for kw, count in keywords.items():
|
||
|
|
if count < 1:
|
||
|
|
continue
|
||
|
|
execute_returning(
|
||
|
|
"""INSERT INTO market_trends (category, trend_name, description, source,
|
||
|
|
confidence_score, opportunity_score, related_products, data_source)
|
||
|
|
VALUES (%s, %s, %s, 'social_mentions', %s, %s, %s, 'live_feed')
|
||
|
|
RETURNING id""",
|
||
|
|
(
|
||
|
|
"kant-en-klaar" if "meal" in kw or "kant" in kw else "halal",
|
||
|
|
f"Social buzz: {kw} ({count} mentions)",
|
||
|
|
f"{count} vermeldingen afgelopen 30 dagen rond '{kw}'.",
|
||
|
|
min(0.95, 0.5 + count * 0.05),
|
||
|
|
min(0.95, 0.4 + count * 0.06),
|
||
|
|
[kw],
|
||
|
|
),
|
||
|
|
)
|
||
|
|
created += 1
|
||
|
|
|
||
|
|
for seed in TREND_SEEDS:
|
||
|
|
exists = fetch_all(
|
||
|
|
"SELECT id FROM market_trends WHERE trend_name = %s LIMIT 1",
|
||
|
|
(seed["trend_name"],),
|
||
|
|
)
|
||
|
|
if not exists:
|
||
|
|
execute_returning(
|
||
|
|
"""INSERT INTO market_trends (category, trend_name, description, source,
|
||
|
|
confidence_score, opportunity_score, related_products, action_items, data_source)
|
||
|
|
VALUES (%s,%s,%s,%s,%s,%s,%s,%s,'seed') RETURNING id""",
|
||
|
|
(
|
||
|
|
seed["category"], seed["trend_name"], seed["description"], seed["source"],
|
||
|
|
seed["confidence_score"], seed["opportunity_score"],
|
||
|
|
seed["related_products"], seed["action_items"],
|
||
|
|
),
|
||
|
|
)
|
||
|
|
created += 1
|
||
|
|
return {"trends_created": created, "keyword_hits": keywords}
|
||
|
|
|
||
|
|
|
||
|
|
def list_live_trends(limit: int = 20) -> list[dict[str, Any]]:
|
||
|
|
return fetch_all(
|
||
|
|
"""SELECT * FROM market_trends ORDER BY updated_at DESC NULLS LAST, created_at DESC LIMIT %s""",
|
||
|
|
(limit,),
|
||
|
|
)
|