657 lines
15 KiB
TypeScript
657 lines
15 KiB
TypeScript
/**
|
|
* Queries de Telemetria
|
|
* Sistema completo de tracking e analytics
|
|
*/
|
|
|
|
import { query } from "../index";
|
|
|
|
export type TelemetryEventType =
|
|
| "ad_view"
|
|
| "ad_click"
|
|
| "ad_exit"
|
|
| "search"
|
|
| "filter"
|
|
| "favorite"
|
|
| "unfavorite"
|
|
| "purchase"
|
|
| "inquiry"
|
|
| "page_view"
|
|
| "price_change";
|
|
|
|
export interface TelemetryEvent {
|
|
event_id: number;
|
|
event_type: TelemetryEventType;
|
|
ad_id: number | null;
|
|
user_id: number | null;
|
|
session_id: string | null;
|
|
metadata: Record<string, any>;
|
|
timestamp: Date;
|
|
}
|
|
|
|
export interface TrackEventData {
|
|
event_type: TelemetryEventType;
|
|
ad_id?: number;
|
|
user_id?: number;
|
|
session_id?: string;
|
|
metadata?: Record<string, any>;
|
|
}
|
|
|
|
export interface AdAnalytics {
|
|
ad_id: number;
|
|
impressions: number;
|
|
clicks: number;
|
|
ctr: number;
|
|
avg_view_duration: number;
|
|
bounce_rate: number;
|
|
inquiries: number;
|
|
favorites: number;
|
|
conversion_rate: number;
|
|
time_to_sell: number | null;
|
|
last_updated: Date;
|
|
}
|
|
|
|
export interface UserBehavior {
|
|
user_id: number;
|
|
search_queries: string[];
|
|
filters_applied: Record<string, any>;
|
|
items_viewed: Array<{
|
|
item_id: number;
|
|
duration: number;
|
|
timestamp: Date;
|
|
}>;
|
|
items_favorited: number[];
|
|
purchases: Array<{
|
|
ad_id: number;
|
|
price: number;
|
|
timestamp: Date;
|
|
}>;
|
|
active_hours: Array<{
|
|
hour: number;
|
|
activity_count: number;
|
|
}>;
|
|
}
|
|
|
|
export interface MarketplaceStats {
|
|
total_transactions: number;
|
|
avg_price_by_category: Record<string, number>;
|
|
conversion_rate: number;
|
|
most_searched_items: Array<{
|
|
item_id: number;
|
|
item_name: string;
|
|
search_count: number;
|
|
}>;
|
|
most_sold_items: Array<{
|
|
item_id: number;
|
|
item_name: string;
|
|
sales_count: number;
|
|
}>;
|
|
peak_hours: Array<{
|
|
hour: number;
|
|
activity_count: number;
|
|
}>;
|
|
avg_time_to_sell: number;
|
|
}
|
|
|
|
/**
|
|
* Registra um evento de telemetria
|
|
*/
|
|
export async function trackEvent(
|
|
data: TrackEventData,
|
|
): Promise<TelemetryEvent> {
|
|
const result = await query<TelemetryEvent>(
|
|
`
|
|
INSERT INTO telemetry_events (
|
|
event_type, ad_id, user_id, session_id, metadata
|
|
)
|
|
VALUES ($1, $2, $3, $4, $5)
|
|
RETURNING *
|
|
`,
|
|
[
|
|
data.event_type,
|
|
data.ad_id || null,
|
|
data.user_id || null,
|
|
data.session_id || null,
|
|
JSON.stringify(data.metadata || {}),
|
|
],
|
|
);
|
|
|
|
return result.rows[0];
|
|
}
|
|
|
|
/**
|
|
* Registra múltiplos eventos em batch
|
|
*/
|
|
export async function trackEventsBatch(
|
|
events: TrackEventData[],
|
|
): Promise<TelemetryEvent[]> {
|
|
if (events.length === 0) return [];
|
|
|
|
const values: any[] = [];
|
|
const placeholders: string[] = [];
|
|
let paramIndex = 1;
|
|
|
|
for (const event of events) {
|
|
placeholders.push(
|
|
`($${paramIndex}, $${paramIndex + 1}, $${paramIndex + 2}, $${paramIndex + 3}, $${paramIndex + 4})`,
|
|
);
|
|
values.push(
|
|
event.event_type,
|
|
event.ad_id || null,
|
|
event.user_id || null,
|
|
event.session_id || null,
|
|
JSON.stringify(event.metadata || {}),
|
|
);
|
|
paramIndex += 5;
|
|
}
|
|
|
|
const result = await query<TelemetryEvent>(
|
|
`
|
|
INSERT INTO telemetry_events (
|
|
event_type, ad_id, user_id, session_id, metadata
|
|
)
|
|
VALUES ${placeholders.join(", ")}
|
|
RETURNING *
|
|
`,
|
|
values,
|
|
);
|
|
|
|
return result.rows;
|
|
}
|
|
|
|
/**
|
|
* Busca analytics de um anúncio específico
|
|
*/
|
|
export async function getAdAnalytics(
|
|
adId: number,
|
|
): Promise<AdAnalytics | null> {
|
|
const result = await query<AdAnalytics>(
|
|
`
|
|
SELECT * FROM ad_analytics
|
|
WHERE ad_id = $1
|
|
`,
|
|
[adId],
|
|
);
|
|
|
|
return result.rows[0] || null;
|
|
}
|
|
|
|
/**
|
|
* Busca comportamento do usuário (comprador)
|
|
*/
|
|
export async function getUserBehavior(
|
|
userId: number,
|
|
startDate?: Date,
|
|
endDate?: Date,
|
|
): Promise<UserBehavior | null> {
|
|
// Verificar se usuário existe
|
|
const userCheck = await query<{ user_id: number }>(
|
|
"SELECT user_id FROM users WHERE user_id = $1",
|
|
[userId],
|
|
);
|
|
|
|
if (userCheck.rows.length === 0) {
|
|
return null;
|
|
}
|
|
|
|
const dateFilter =
|
|
startDate && endDate ? "AND timestamp BETWEEN $2 AND $3" : "";
|
|
|
|
const params: any[] = [userId];
|
|
if (startDate && endDate) {
|
|
params.push(startDate.getTime(), endDate.getTime());
|
|
}
|
|
|
|
// Buscar queries de busca
|
|
const searchQueries = await query<{ metadata: { query?: string } }>(
|
|
`
|
|
SELECT DISTINCT metadata->>'query' as query
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 AND event_type = 'search' ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar filtros aplicados
|
|
const filters = await query<{ metadata: Record<string, any> }>(
|
|
`
|
|
SELECT DISTINCT metadata
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 AND event_type = 'filter' ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar itens visualizados
|
|
const itemsViewed = await query<{
|
|
ad_id: number;
|
|
metadata: { duration?: number };
|
|
timestamp: Date;
|
|
}>(
|
|
`
|
|
SELECT ad_id, metadata, timestamp
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 AND event_type = 'ad_view' AND ad_id IS NOT NULL ${dateFilter}
|
|
ORDER BY timestamp DESC
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar favoritos
|
|
const favorites = await query<{ ad_id: number }>(
|
|
`
|
|
SELECT DISTINCT ad_id
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 AND event_type = 'favorite' AND ad_id IS NOT NULL ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar compras
|
|
const purchases = await query<{
|
|
ad_id: number;
|
|
metadata: { price?: number };
|
|
timestamp: Date;
|
|
}>(
|
|
`
|
|
SELECT ad_id, metadata, timestamp
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 AND event_type = 'purchase' AND ad_id IS NOT NULL ${dateFilter}
|
|
ORDER BY timestamp DESC
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar horas ativas
|
|
const activeHours = await query<{
|
|
hour: number;
|
|
count: string;
|
|
}>(
|
|
`
|
|
SELECT EXTRACT(HOUR FROM timestamp)::INTEGER as hour, COUNT(*)::TEXT as count
|
|
FROM telemetry_events
|
|
WHERE user_id = $1 ${dateFilter}
|
|
GROUP BY hour
|
|
ORDER BY hour
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Buscar item_id dos anúncios visualizados
|
|
const _itemIds = await query<{ item_id: number }>(
|
|
`
|
|
SELECT DISTINCT l.item_id
|
|
FROM telemetry_events te
|
|
JOIN listings l ON l.listing_id = te.ad_id
|
|
WHERE te.user_id = $1 AND te.event_type = 'ad_view' AND te.ad_id IS NOT NULL ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
return {
|
|
user_id: userId,
|
|
search_queries: searchQueries.rows
|
|
.map((r) => r.metadata?.query)
|
|
.filter((q): q is string => !!q),
|
|
filters_applied: filters.rows.reduce((acc, r) => {
|
|
return { ...acc, ...r.metadata };
|
|
}, {}),
|
|
items_viewed: itemsViewed.rows.map((r) => ({
|
|
item_id: r.ad_id,
|
|
duration: r.metadata?.duration || 0,
|
|
timestamp: r.timestamp,
|
|
})),
|
|
items_favorited: favorites.rows.map((r) => r.ad_id),
|
|
purchases: purchases.rows.map((r) => ({
|
|
ad_id: r.ad_id,
|
|
price: r.metadata?.price || 0,
|
|
timestamp: r.timestamp,
|
|
})),
|
|
active_hours: activeHours.rows.map((r) => ({
|
|
hour: r.hour,
|
|
activity_count: parseInt(r.count, 10),
|
|
})),
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Busca métricas agregadas do marketplace
|
|
*/
|
|
export async function getMarketplaceStats(
|
|
startDate?: Date,
|
|
endDate?: Date,
|
|
): Promise<MarketplaceStats> {
|
|
const dateFilter =
|
|
startDate && endDate ? "WHERE timestamp BETWEEN $1 AND $2" : "";
|
|
const params = startDate && endDate ? [startDate, endDate] : [];
|
|
|
|
// Total de transações
|
|
const transactions = await query<{ count: string }>(
|
|
`
|
|
SELECT COUNT(*)::TEXT as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'purchase' ${dateFilter.replace("timestamp", "timestamp")}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Preço médio por categoria (requer join com listings)
|
|
const avgPriceByCategory = await query<{
|
|
category: string;
|
|
avg_price: string;
|
|
}>(
|
|
`
|
|
SELECT
|
|
l.item_name,
|
|
AVG(COALESCE(l.price_pix, l.price_tibia_coins, l.price_gold_coins))::TEXT as avg_price
|
|
FROM listings l
|
|
JOIN telemetry_events te ON te.ad_id = l.listing_id
|
|
WHERE te.event_type = 'purchase' ${dateFilter.replace("timestamp", "te.timestamp")}
|
|
GROUP BY l.item_name
|
|
LIMIT 10
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Taxa de conversão
|
|
const views = await query<{ count: string }>(
|
|
`
|
|
SELECT COUNT(*)::TEXT as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'ad_view' ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
const purchases = await query<{ count: string }>(
|
|
`
|
|
SELECT COUNT(*)::TEXT as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'purchase' ${dateFilter}
|
|
`,
|
|
params,
|
|
);
|
|
|
|
const viewsCount = parseInt(views.rows[0]?.count || "0", 10);
|
|
const purchasesCount = parseInt(purchases.rows[0]?.count || "0", 10);
|
|
const conversionRate =
|
|
viewsCount > 0 ? (purchasesCount / viewsCount) * 100 : 0;
|
|
|
|
// Itens mais procurados
|
|
const mostSearched = await query<{
|
|
item_name: string;
|
|
search_count: string;
|
|
}>(
|
|
`
|
|
SELECT
|
|
metadata->>'query' as item_name,
|
|
COUNT(*)::TEXT as search_count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'search' AND metadata->>'query' IS NOT NULL ${dateFilter}
|
|
GROUP BY metadata->>'query'
|
|
ORDER BY COUNT(*) DESC
|
|
LIMIT 10
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Itens mais vendidos
|
|
const mostSold = await query<{
|
|
item_id: number;
|
|
item_name: string;
|
|
sales_count: string;
|
|
}>(
|
|
`
|
|
SELECT
|
|
l.item_id,
|
|
l.item_name,
|
|
COUNT(*)::TEXT as sales_count
|
|
FROM telemetry_events te
|
|
JOIN listings l ON l.listing_id = te.ad_id
|
|
WHERE te.event_type = 'purchase' ${dateFilter.replace("timestamp", "te.timestamp")}
|
|
GROUP BY l.item_id, l.item_name
|
|
ORDER BY COUNT(*) DESC
|
|
LIMIT 10
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Horários de pico
|
|
const peakHours = await query<{
|
|
hour: number;
|
|
activity_count: string;
|
|
}>(
|
|
`
|
|
SELECT
|
|
EXTRACT(HOUR FROM timestamp)::INTEGER as hour,
|
|
COUNT(*)::TEXT as activity_count
|
|
FROM telemetry_events
|
|
${dateFilter}
|
|
GROUP BY hour
|
|
ORDER BY COUNT(*) DESC
|
|
LIMIT 10
|
|
`,
|
|
params,
|
|
);
|
|
|
|
// Tempo médio para venda
|
|
const avgTimeToSell = await query<{
|
|
avg_hours: string;
|
|
}>(
|
|
`
|
|
SELECT AVG(aa.time_to_sell)::TEXT as avg_hours
|
|
FROM ad_analytics aa
|
|
WHERE aa.time_to_sell IS NOT NULL
|
|
`,
|
|
[],
|
|
);
|
|
|
|
return {
|
|
total_transactions: parseInt(transactions.rows[0]?.count || "0", 10),
|
|
avg_price_by_category: avgPriceByCategory.rows.reduce(
|
|
(acc, r) => {
|
|
acc[r.category] = parseFloat(r.avg_price);
|
|
return acc;
|
|
},
|
|
{} as Record<string, number>,
|
|
),
|
|
conversion_rate: conversionRate,
|
|
most_searched_items: mostSearched.rows.map((r) => ({
|
|
item_id: 0, // Não temos item_id em searches, apenas query string
|
|
item_name: r.item_name || "",
|
|
search_count: parseInt(r.search_count, 10),
|
|
})),
|
|
most_sold_items: mostSold.rows.map((r) => ({
|
|
item_id: r.item_id,
|
|
item_name: r.item_name,
|
|
sales_count: parseInt(r.sales_count, 10),
|
|
})),
|
|
peak_hours: peakHours.rows.map((r) => ({
|
|
hour: r.hour,
|
|
activity_count: parseInt(r.activity_count, 10),
|
|
})),
|
|
avg_time_to_sell: parseFloat(avgTimeToSell.rows[0]?.avg_hours || "0"),
|
|
};
|
|
}
|
|
|
|
/**
|
|
* Agrega eventos de telemetria em métricas de analytics
|
|
* Executar periodicamente (ex: a cada hora)
|
|
*/
|
|
export async function aggregateHourlyMetrics(): Promise<void> {
|
|
// Agregar impressões (ad_view events)
|
|
await query(
|
|
`
|
|
INSERT INTO ad_analytics (ad_id, impressions, last_updated)
|
|
SELECT
|
|
ad_id,
|
|
COUNT(*) as impressions,
|
|
CURRENT_TIMESTAMP
|
|
FROM telemetry_events
|
|
WHERE event_type = 'ad_view'
|
|
AND ad_id IS NOT NULL
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
GROUP BY ad_id
|
|
ON CONFLICT (ad_id) DO UPDATE SET
|
|
impressions = ad_analytics.impressions + EXCLUDED.impressions,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
`,
|
|
);
|
|
|
|
// Agregar cliques
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET clicks = clicks + sub.count,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
FROM (
|
|
SELECT ad_id, COUNT(*) as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'ad_click'
|
|
AND ad_id IS NOT NULL
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
GROUP BY ad_id
|
|
) sub
|
|
WHERE aa.ad_id = sub.ad_id
|
|
`,
|
|
);
|
|
|
|
// Atualizar CTR (Click-Through Rate)
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics
|
|
SET ctr = CASE
|
|
WHEN impressions > 0 THEN (clicks::DECIMAL / impressions::DECIMAL) * 100
|
|
ELSE 0
|
|
END,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
WHERE impressions > 0
|
|
`,
|
|
);
|
|
|
|
// Agregar duração média de visualização
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET
|
|
avg_view_duration = COALESCE(
|
|
(
|
|
SELECT AVG((metadata->>'duration')::DECIMAL)
|
|
FROM telemetry_events
|
|
WHERE event_type = 'ad_exit'
|
|
AND ad_id = aa.ad_id
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
AND metadata->>'duration' IS NOT NULL
|
|
),
|
|
avg_view_duration
|
|
),
|
|
last_updated = CURRENT_TIMESTAMP
|
|
WHERE EXISTS (
|
|
SELECT 1 FROM telemetry_events
|
|
WHERE event_type = 'ad_exit'
|
|
AND ad_id = aa.ad_id
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
)
|
|
`,
|
|
);
|
|
|
|
// Calcular bounce rate (views com duração < 3 segundos)
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET
|
|
bounce_rate = CASE
|
|
WHEN aa.impressions > 0 THEN
|
|
(
|
|
SELECT (COUNT(*)::DECIMAL / aa.impressions::DECIMAL) * 100
|
|
FROM telemetry_events
|
|
WHERE event_type = 'ad_exit'
|
|
AND ad_id = aa.ad_id
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
AND (metadata->>'duration')::DECIMAL < 3
|
|
)
|
|
ELSE 0
|
|
END,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
WHERE aa.impressions > 0
|
|
`,
|
|
);
|
|
|
|
// Agregar inquiries
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET inquiries = inquiries + sub.count,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
FROM (
|
|
SELECT ad_id, COUNT(*) as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'inquiry'
|
|
AND ad_id IS NOT NULL
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
GROUP BY ad_id
|
|
) sub
|
|
WHERE aa.ad_id = sub.ad_id
|
|
`,
|
|
);
|
|
|
|
// Agregar favoritos
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET favorites = favorites + sub.count,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
FROM (
|
|
SELECT ad_id, COUNT(*) as count
|
|
FROM telemetry_events
|
|
WHERE event_type = 'favorite'
|
|
AND ad_id IS NOT NULL
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
GROUP BY ad_id
|
|
) sub
|
|
WHERE aa.ad_id = sub.ad_id
|
|
`,
|
|
);
|
|
|
|
// Calcular taxa de conversão
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET
|
|
conversion_rate = CASE
|
|
WHEN aa.impressions > 0 THEN
|
|
(
|
|
SELECT (COUNT(*)::DECIMAL / aa.impressions::DECIMAL) * 100
|
|
FROM telemetry_events
|
|
WHERE event_type = 'purchase'
|
|
AND ad_id = aa.ad_id
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
)
|
|
ELSE 0
|
|
END,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
WHERE EXISTS (
|
|
SELECT 1 FROM telemetry_events
|
|
WHERE event_type = 'purchase'
|
|
AND ad_id = aa.ad_id
|
|
AND timestamp >= NOW() - INTERVAL '1 hour'
|
|
)
|
|
`,
|
|
);
|
|
|
|
// Calcular time_to_sell para anúncios vendidos
|
|
await query(
|
|
`
|
|
UPDATE ad_analytics aa
|
|
SET
|
|
time_to_sell = EXTRACT(EPOCH FROM (l.sold_at - l.created_at)) / 3600,
|
|
last_updated = CURRENT_TIMESTAMP
|
|
FROM listings l
|
|
WHERE aa.ad_id = l.listing_id
|
|
AND l.listing_status = 'sold'
|
|
AND l.sold_at IS NOT NULL
|
|
AND aa.time_to_sell IS NULL
|
|
AND l.sold_at >= NOW() - INTERVAL '1 hour'
|
|
`,
|
|
);
|
|
}
|