Weekly Ranking Summary

Hello,

Here's your weekly ranking summary for your RankWatch account:

\n \n

Overview

Keywords Checked: ${summary.total_keywords}
Currently Ranking: ${summary.ranking_keywords}
Top 10 Positions: ${summary.top_10}
Top 20 Positions: ${summary.top_20}

\n \n ${summary.improvements.length > 0 ? `\n

🎉 Improvements

${item.keyword}: Moved up ${item.change} positions to #${item.position}

\n ` : ''}\n \n ${summary.declines.length > 0 ? `\n

📉 Declines

${item.keyword}: Moved down ${Math.abs(item.change)} positions to #${item.position}

\n ` : ''}\n \n

Best regards,
The RankWatch Team

\n \n

This is an automated weekly summary. To manage your alerts, log in to your RankWatch account.

\n `;\n\n await email({\n to: userEmail,\n subject: subject,\n html: htmlContent,\n });\n}\n\n// Main cron function - runs every Monday at 9 AM UTC\nexport default async function cronWeekly() {\n console.log(\"🕒 Starting weekly ranking check for freemium accounts...\");\n \n try {\n // Initialize database\n await initializeDatabase();\n const db = getDatabase();\n \n // Get all freemium users with active keywords\n const freemiumUsers = await db.execute({\n sql: `SELECT DISTINCT u.id, u.email \n FROM users u \n JOIN projects p ON u.id = p.user_id \n JOIN keywords k ON p.id = k.project_id \n WHERE u.plan = 'freemium' \n AND u.deleted_at IS NULL \n AND p.deleted_at IS NULL \n AND k.deleted_at IS NULL \n AND k.active = 1`,\n args: [],\n });\n\n console.log(`Found ${freemiumUsers.rows.length} freemium users with active keywords`);\n\n for (const userRow of freemiumUsers.rows) {\n const userId = userRow[0] as number;\n const userEmail = userRow[1] as string;\n \n try {\n console.log(`Processing user ${userId} (${userEmail})`);\n \n // Get all active keywords for this user\n const userKeywords = await db.execute({\n sql: `SELECT k.id, k.keyword, p.domain, k.target_url, p.location, p.device\n FROM keywords k \n JOIN projects p ON k.project_id = p.id \n WHERE p.user_id = ? \n AND k.deleted_at IS NULL \n AND p.deleted_at IS NULL \n AND k.active = 1`,\n args: [userId],\n });\n\n if (userKeywords.rows.length === 0) {\n console.log(`No active keywords found for user ${userId}`);\n continue;\n }\n\n console.log(`Checking ${userKeywords.rows.length} keywords for user ${userId}`);\n\n // Prepare keywords for batch checking\n const keywordsToCheck = userKeywords.rows.map(row => ({\n keywordId: row[0] as number,\n keyword: row[1] as string,\n domain: row[2] as string,\n target_url: row[3] as string | null,\n location: row[4] as string,\n device: row[5] as string,\n }));\n\n // Check rankings in batches to avoid overwhelming Serper API\n const batchSize = 5;\n const summaryData = {\n total_keywords: keywordsToCheck.length,\n ranking_keywords: 0,\n top_10: 0,\n top_20: 0,\n improvements: [] as any[],\n declines: [] as any[],\n };\n\n for (let i = 0; i < keywordsToCheck.length; i += batchSize) {\n const batch = keywordsToCheck.slice(i, i + batchSize);\n \n console.log(`Processing batch ${Math.floor(i / batchSize) + 1}/${Math.ceil(keywordsToCheck.length / batchSize)} for user ${userId}`);\n \n // Check rankings for this batch\n const batchResults = await batchCheckKeywords(\n batch.map(k => ({\n keyword: k.keyword,\n domain: k.domain,\n target_url: k.target_url || undefined,\n location: k.location,\n device: k.device,\n })),\n 2000 // 2 second delay between requests\n );\n\n // Process results\n for (let j = 0; j < batchResults.length; j++) {\n const result = batchResults[j];\n const keywordData = batch[j];\n \n try {\n // Save ranking result\n await saveRankingResult(keywordData.keywordId, result);\n \n // Update summary statistics\n if (result.found && result.position) {\n summaryData.ranking_keywords++;\n \n if (result.position <= 10) {\n summaryData.top_10++;\n }\n if (result.position <= 20) {\n summaryData.top_20++;\n }\n }\n\n // Check for improvements/declines\n const historyResult = await db.execute({\n sql: `SELECT change_value \n FROM rank_history \n WHERE keyword_id = ? \n ORDER BY checked_at DESC \n LIMIT 1`,\n args: [keywordData.keywordId],\n });\n\n if (historyResult.rows.length > 0) {\n const change = historyResult.rows[0][0] as number;\n if (change > 0) {\n summaryData.improvements.push({\n keyword: result.keyword,\n position: result.position,\n change: change,\n });\n } else if (change < 0) {\n summaryData.declines.push({\n keyword: result.keyword,\n position: result.position,\n change: change,\n });\n }\n }\n\n console.log(`✓ Processed keyword \"${result.keyword}\" for user ${userId}: ${result.found ? `#${result.position}` : 'Not ranking'}`);\n \n } catch (error) {\n console.error(`Error saving ranking for keyword ${keywordData.keywordId}:`, error);\n }\n }\n\n // Add delay between batches\n if (i + batchSize < keywordsToCheck.length) {\n await new Promise(resolve => setTimeout(resolve, 5000)); // 5 second delay between batches\n }\n }\n\n // Send weekly summary email\n await sendWeeklySummary(userEmail, summaryData);\n console.log(`✓ Weekly summary sent to ${userEmail}`);\n\n } catch (error) {\n console.error(`Error processing user ${userId}:`, error);\n \n // Send error notification email\n try {\n await email({\n to: userEmail,\n subject: \"RankWatch Weekly Check - Issue Occurred\",\n html: `\n

Hello,

We encountered an issue while checking your keyword rankings this week. Our team has been notified and will resolve this shortly.

Best regards,
The RankWatch Team

\n `,\n });\n } catch (emailError) {\n console.error(`Failed to send error notification to ${userEmail}:`, emailError);\n }\n }\n }\n\n console.log(\"✅ Weekly ranking check completed for all freemium accounts\");\n\n } catch (error) {\n console.error(\"❌ Weekly cron job failed:\", error);\n \n // Send admin notification about cron failure\n try {\n await email({\n to: \"admin@rankwatch.dev\", // Replace with actual admin email\n subject: \"RankWatch Weekly Cron Job Failed\",\n html: `\n

Weekly Cron Job Failure

Time: ${new Date().toISOString()}

Error: ${error.message}

${error.stack}

\n `,\n });\n } catch (emailError) {\n console.error(\"Failed to send admin notification:\", emailError);\n }\n }\n}","deleted","label",[98],"permalink","https://www.val.town/x/diegoivo/denoCrudTask/code/cronWeekly.ts","extension","actionData","errors"]