System Guide: Real-Time Competitor Intelligence Aggregation and Alerting

CREATE TABLE competitor_data (
    id SERIAL PRIMARY KEY,
    competitor_domain VARCHAR(255) NOT NULL,
    product_sku VARCHAR(255) NOT NULL,
    data_point_key VARCHAR(255) NOT NULL, /* e.g., 'price', 'feature_list', 'cpu_cores' */
    scraped_value TEXT,
    scraped_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    source_url TEXT NOT NULL,
    UNIQUE (product_sku, data_point_key, scraped_at)
);
CREATE INDEX idx_product_key_timestamp ON competitor_data (product_sku, data_point_key, scraped_at DESC);
The index on `product_sku`, `data_point_key`, and `scraped_at` is critical for efficiently retrieving the most recent record for a given data point during the comparison logic in the next step.
 
#### Step 4: Develop the Change Detection Logic
 
We will develop an AWS Lambda function in Python. A crucial setup detail is that the function's deployment package must include the `psycopg2` library, specifically compiled for the Amazon Linux 2 environment used by Lambda. I recommend using a pre-compiled layer for this, such as the one found [here](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQEYDArX1YmhXYOU_wyhy2EhWxQyLNXuMXI1Sc8E8TwF1LxwIpYQoNgK_qrRD9vOJbOVGUQIJFpUSIUZ8Q8qHzfSS4o5Yr9rhZFrNwOFRMmVLaIxkaDBrio-LKMvcGsRfMeVMZgz-FqPjrf5bHNnVxd7KEsooaRwKHWpsdsNKkrHx8GMUR_6gIrjFwnqYB8=).
 
The function's logic will execute the following sequence:
1.  Parse the incoming Apify webhook payload to extract the newly scraped data points.
2.  Retrieve the database credentials from [AWS Secrets Manager](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQE0JOnDT7H5RqQotx54kQZ0xpNi6UWafIe3FVyO3eDdfXOk0KjazD7QnsRVuKPMWHutK3qGgmvL1RI4mjUCmtl9036lEkDEJFKG-pVKVF7A5J5VwTwET5G5TMPpdn1fd9Ii1cf6Ce9kTn_Rg04jAxZySNNdfv-uhahw_NZzEx1SwmDSR5fr7Ski2EEALFlXQGDG9390bPQLTlahG7Q4fjkkEV3ZMS5jrYktfgNpP3qTg9i25U8q7TICm3VN5KRk-TA=).
3.  For each new data point, query the `competitor_data` table for the most recent entry with a matching `product_sku` and `data_point_key`.
4.  Compare the new `scraped_value` against the previous one. This is not a simple string comparison. We must normalize the data first. For example, a competitor might change "16 GB" to "16GB". Our logic must see these as identical.
5.  If a meaningful change is detected after normalization, the function proceeds to the alerting step. A simple text change is noise; a change in a numerical value or the addition or removal of a keyword from a feature list is a signal.
 
#### Step 5: Integrate the Alerting Mechanism
 
If a change is confirmed, our Lambda function formats a message. For Slack, we will use the [Block Kit](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQFNQiPeCsEhUgQ4Q5qhroLbi4UKycy1933XaDneZOtpbkgZ9eTIm6eVn1iML53oNDrdEVDgoDYA3kuhj8aV-43F4SO8yYZBVdjQjVArBXkIWfk0_qtKRz6FJZHGyDe2FUuzGjueKxZ5KIgNTzadyPpcWghe43iI4QTjSeR7k0wTYt22-GUvMzlajgtyQ-kuvxTlr1OVyc8SPxY54wZ-ojIK) framework to create rich, readable messages, not just a line of text. The function then sends this JSON payload to the pre-configured Slack webhook URL.
 
For critical changes, such as a competitor removing a high-value feature from a plan, a separate function call can send a payload to the [PagerDuty Events API v2](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQFWtSNSw0T_ZbdULtpObNVcnD8Z_qi7zZorXkmDCprbWveRg-rlOFQau7-V_9LBHqtOdsWpbBWEjjxvBfxOLHUZ5e2ZcAlKtF-hgwTtJCMK7p4tAHCmMSwMZjoAigMzGq7JvwlL17RtcmfCUpK3EEQfsflirEWoSHwhfR_u6bmxjWQIiA==) to trigger a formal incident.
 
**Sample Slack Block Kit JSON for a Feature Change:**
```json
{
  "blocks": [
    {
      "type": "header",
      "text": {
        "type": "plain_text",
        "text": "Competitor Feature Change Detected"
      }
    },
    {
      "type": "section",
      "text": {
        "type": "mrkdwn",
        "text": "Competitor *Acmecorp* has modified their *Enterprise Plan* for the *Quantum CRM* product."
      }
    },
    {
      "type": "divider"
    },
    {
      "type": "section",
      "fields": [
        { "type": "mrkdwn", "text": "*Data Point:* \n Feature List" },
        { "type": "mrkdwn", "text": "*Change Type:* \n Feature Added" },
        { "type": "mrkdwn", "text": "*New Feature:* \n SSO Integration" },
        { "type": "mrkdwn", "text": "*Source URL:* \n <https://acmecorp.com/pricing>" }
      ]
    }
  ]
}
#### Step 6: Schedule and Deploy the System
 
With all components defined, we set a recurring schedule within the Apify console for each Actor. A 15-minute interval is aggressive and appropriate for high-priority targets. For less critical pages, an hourly or daily check is sufficient.
 
Finally, we deploy our AWS Lambda function and its related IAM roles and API Gateway trigger using the [AWS SAM CLI](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQGUR3qzXqvwc4XGXibb1PluwPlPjnsOJ3pBj6EQl6ocqJJq1yf-apC0TtBun64FF7pWV0f5LQVEG5iiKDGPai4hAKE6vPY0BRBZBAvnmAAEbAklsIQk4s23BA_82xyQ1HYaqvhqelNrG2uvFUW7Ljk2AWg=). A single command, `sam deploy --guided`, will package the function, create the necessary cloud infrastructure as defined in our `template.yaml` file, and deploy it. The system is now active and monitoring autonomously.
 
## Troubleshooting Common Issues
 
Even a well-designed system will encounter issues when interacting with external websites. Here is my guidance on addressing the most common problems.
 
*   **Scraper Failures:** The primary cause of failure is 'selector fragility'. Competitors will redesign their websites, which breaks our CSS selectors. My team mitigates this by writing robust selectors that avoid brittle, auto-generated class names. If a scraper consistently returns null values for a field we have designated as critical, the Lambda function's error handling must send a high-priority PagerDuty alert to the engineering team. The alert must contain the target URL and the failed selector, allowing for immediate investigation and updates.
 
*   **False Positive Alerts:** Minor website changes, like adding a trademark symbol or rewording a sentence, can trigger unwanted alerts. We must refine our data cleaning and normalization logic within the Lambda function. For example, before comparing a price, extract only the numerical value. A simple Python regular expression like `re.search(r'[\d\.]+', price_string).group(0)` can isolate "149.00" from strings like "From $149.00/mo", preventing alerts based on text changes. The goal is to compare the substance, not the presentation.
 
*   **Rate Limiting and IP Blocks:** Aggressive, repeated requests from a single IP address are a clear signal of automated scraping and will result in a block. We must use Apify's [proxy management features](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQHlcII11oIdF_YFTEEpcgy7782QOhhKGW_fOr19vMmYtmeqAJ__-AGsGP2VArwu7Y29r6h-XXJ85AHH-jnVyF5cMwcihS9-n7GT-482vzuGc11QEccm-AlZrsDHlzqH2zHCzwiwG0yYOv7g3g3lyNs_2K5bAloNrlv0TrEkEEWDBDE=) extensively. I recommend using a mix of datacenter and residential proxies to mask our footprint. We will also set realistic user-agent headers and ensure our scraping frequency is reasonable for any single domain. Scraping every 15 minutes is for a handful of critical pages, not their entire website. Responsible, considerate scraping is not only ethical but also more effective long-term.
 
## Expected Results and System Maintenance
 
Upon successful deployment, our product and marketing teams will receive immediate, well-formatted Slack notifications detailing relevant competitor activities. This information provides a direct, tactical input for our strategic decision-making process, from pricing adjustments to feature roadmap planning.
 
We will measure success by tracking two key metrics: 'detection-to-alert latency' and 'scraper success rate'. Latency is the time difference between the `scraped_at` timestamp in our database and the timestamp of the corresponding Slack alert. The scraper success rate is monitored by programmatically checking Actor run statuses via the [Apify API](https://vertexaisearch.cloud.google.com/grounding-api-redirect/AUZIYQHKgZ-kst5YND53TaReBDjfy8BO02Cxx8dPkjr3c5KNOV43asLajTZ5PEc2lZTFRKcOr8lI2FlHg0p03u5Ph_aMwVhfEcOp41PHC7Km9h6xvDikS2HZBBa80tSXq-M_W6cTs2oqOkRqrKVcPUFL1zd7dzfVRzlAEekF1X8wfTVp7qMR4Y7Y5N8ef_cY2f2Tgy_ars7t-dXtZ1hy06KLIdpbpBwoJg==). Our objective is to maintain a latency under one minute and a success rate above 98%.
 
This system requires active maintenance. It is not a "set and forget" solution. My team schedules a mandatory quarterly review of all selectors and targets. The competitive landscape changes constantly, and our monitoring system must evolve with it. All changes to selectors, target lists, and the Lambda function logic are tracked through our Git repository, providing a full audit trail and enabling rapid rollbacks if a change introduces errors.