1  #!/usr/bin/env node
  2  
  3  /**
  4   * Expand keywords for countries with <50 final keywords
  5   *
  6   * Target: Get 150-200 raw keywords per country to achieve 50+ after filtering
  7   *
  8   * Countries to expand:
  9   * - IN (India): 17 final
 10   * - NO (Norway): 30 final
 11   * - SG (Singapore): 30 final
 12   * - CH (Switzerland): 42 final
 13   * - NL (Netherlands): 43 final
 14   * - ID (Indonesia): 44 final
 15   * - DK (Denmark): 45 final
 16   * - CN (China): 48 final
 17   * - FR (France): 49 final
 18   */
 19  
 20  import 'dotenv/config';
 21  import { generateSearchVolumeCSVOptimized } from '../src/utils/keyword-validator-optimized.js';
 22  import { filterKeywordsPreOverview } from '../src/utils/keyword-filter.js';
 23  import { stringify } from 'csv-stringify/sync';
 24  import { parse } from 'csv-parse/sync';
 25  import fs from 'fs';
 26  import Logger from '../src/utils/logger.js';
 27  
 28  const logger = new Logger('ExpandKeywords');
 29  
 30  // Countries that need expansion (less than 50 final keywords)
 31  const COUNTRIES_TO_EXPAND = [
 32    { code: 'IN', name: 'India', current: 17, target: 150 },
 33    { code: 'NO', name: 'Norway', current: 30, target: 100 },
 34    { code: 'SG', name: 'Singapore', current: 30, target: 100 },
 35    { code: 'CH', name: 'Switzerland', current: 42, target: 120 },
 36    { code: 'NL', name: 'Netherlands', current: 43, target: 120 },
 37    { code: 'ID', name: 'Indonesia', current: 44, target: 150 },
 38    { code: 'DK', name: 'Denmark', current: 45, target: 100 },
 39    { code: 'CN', name: 'China', current: 48, target: 80 },
 40    { code: 'FR', name: 'France', current: 49, target: 150 },
 41  ];
 42  
 43  // Core seed keywords to expand from
 44  const CORE_SEEDS = [
 45    'plumber',
 46    'electrician',
 47    'carpenter',
 48    'painter',
 49    'pest control',
 50    'locksmith',
 51    'handyman',
 52    'landscaper',
 53    'lawn care',
 54    'tree service',
 55    'HVAC repair',
 56    'heating repair',
 57    'air conditioning repair',
 58    'garage door repair',
 59    'appliance repair',
 60    'roofer',
 61    'gutter cleaning',
 62    'pressure washing',
 63    'junk removal',
 64    'concrete contractor',
 65    'driveway paving',
 66    'fencing',
 67    'deck builder',
 68    'bathroom remodeling',
 69    'kitchen remodeling',
 70    'flooring contractor',
 71    'waterproofing',
 72    'insulation contractor',
 73    'siding contractor',
 74    'drywall contractor',
 75    'masonry contractor',
 76    'pool service',
 77    'pool cleaning',
 78    'solar panel installation',
 79    'chimney sweep',
 80  ];
 81  
 82  /**
 83   * Expand keywords for a single country
 84   */
 85  async function expandCountry(country) {
 86    const countryCode = country.code;
 87    const countryDir = `./data/${countryCode.toLowerCase()}`;
 88  
 89    logger.info(`\n${'='.repeat(60)}`);
 90    logger.info(
 91      `${country.name} (${countryCode}) - Current: ${country.current}, Target: ${country.target}`
 92    );
 93    logger.info('='.repeat(60));
 94  
 95    // Create seed file
 96    const seedFile = `${countryDir}/businesses-expanded-seeds.txt`;
 97    fs.writeFileSync(seedFile, CORE_SEEDS.join('\n'), 'utf-8');
 98    logger.success(`Created seed file with ${CORE_SEEDS.length} seeds`);
 99  
100    // Generate expanded keywords using DataForSEO Labs API
101    const csvPath = `${countryDir}/businesses-search-volume-optimized.csv`;
102  
103    try {
104      logger.info(`Generating keywords for ${countryCode}...`);
105      const stats = await generateSearchVolumeCSVOptimized(seedFile, countryCode, csvPath);
106  
107      logger.success(`Generated ${stats.totalKeywords} keywords`);
108      logger.info(`  - Seeds: ${stats.seedCount}`);
109      logger.info(`  - Related found: ${stats.totalRelated}`);
110      logger.info(`  - Already have volume: ${stats.haveVolume}`);
111      logger.info(`  - Needed Overview: ${stats.needOverview}`);
112  
113      // Apply filtering
114      const csvContent = fs.readFileSync(csvPath, 'utf-8');
115      const keywords = parse(csvContent, { columns: true, skip_empty_lines: true });
116  
117      const kwList = keywords.map(k => k.keyword);
118      const filtered = await filterKeywordsPreOverview(
119        kwList,
120        'en',
121        150, // Allow more keywords through
122        countryCode
123      );
124  
125      logger.success(
126        `Filtered: ${filtered.filtered_keywords.length} kept, ${filtered.removed.length} removed`
127      );
128  
129      // Save filtered results
130      const finalPath = `${countryDir}/businesses-final-filtered.csv`;
131      const finalData = keywords
132        .filter(k => filtered.filtered_keywords.includes(k.keyword))
133        .sort((a, b) => parseInt(b.search_volume) - parseInt(a.search_volume));
134  
135      const csvData = stringify(finalData, {
136        header: true,
137        columns: [
138          { key: 'keyword', header: 'keyword' },
139          { key: 'search_volume', header: 'search_volume' },
140          { key: 'competition', header: 'competition' },
141          { key: 'cpc_low', header: 'cpc_low' },
142          { key: 'cpc_high', header: 'cpc_high' },
143          { key: 'related_to', header: 'related_to' },
144          { key: 'country_code', header: 'country_code' },
145        ],
146      });
147  
148      fs.writeFileSync(finalPath, csvData, 'utf-8');
149  
150      const finalCount = finalData.length;
151      const status = finalCount >= 50 ? '✓' : '✗';
152      logger.success(
153        `${status} Final count: ${finalCount} keywords ${finalCount >= 50 ? '(TARGET MET)' : '(BELOW TARGET)'}`
154      );
155  
156      // Clean up seed file
157      fs.unlinkSync(seedFile);
158  
159      return {
160        country: countryCode,
161        before: country.current,
162        after: finalCount,
163        generated: stats.totalKeywords,
164        removed: filtered.removed.length,
165        success: finalCount >= 50,
166      };
167    } catch (error) {
168      logger.error(`Error expanding ${countryCode}: ${error.message}`);
169      return {
170        country: countryCode,
171        error: error.message,
172        success: false,
173      };
174    }
175  }
176  
177  /**
178   * Main function
179   */
180  async function main() {
181    console.log('Expanding keywords for countries with <50 final keywords\n');
182    console.log(`Processing ${COUNTRIES_TO_EXPAND.length} countries...\n`);
183  
184    const results = [];
185  
186    for (const country of COUNTRIES_TO_EXPAND) {
187      const result = await expandCountry(country);
188      results.push(result);
189    }
190  
191    // Summary
192    console.log(`\n${'='.repeat(60)}`);
193    console.log('SUMMARY');
194    console.log('='.repeat(60));
195  
196    const successful = results.filter(r => r.success).length;
197    const failed = results.filter(r => !r.success).length;
198  
199    console.log(`\n✓ Successful: ${successful}/${results.length}`);
200    console.log(`✗ Below target: ${failed}/${results.length}\n`);
201  
202    console.log('Per-country results:');
203    for (const result of results) {
204      if (result.error) {
205        console.log(`  ${result.country}: ERROR - ${result.error}`);
206      } else {
207        const status = result.success ? '✓' : '✗';
208        console.log(
209          `  ${status} ${result.country}: ${result.before} → ${result.after} (${result.generated} generated, ${result.removed} removed)`
210        );
211      }
212    }
213  
214    console.log('\nExpansion complete!');
215  }
216  
217  main().catch(error => {
218    logger.error(`Fatal error: ${error.message}`);
219    process.exit(1);
220  });