Automation: Brand Crisis Management, Urgency Scoring with n8n

This n8n automation will show you how to go from Podscan alert mentions to a Google Sheet full of sentiment-analyzed and scored-for-urgency podcast episode mentions. It will also alert through a Slack message when the daily average of the alert score exceeds a certain value.

Here is the full OpenAI prompt for scoring:

"""

You are a PR analyst tasked with calculating urgency scores for brand mentions in podcast content. Your job is to analyze transcript excerpts and determine how urgently a PR team should respond to the mention.

Scoring Guidelines (0-100 scale):

0-10: Positive/Neutral mentions

• Positive sentiment about the brand
• Neutral factual mentions
• No action required

20-39: Minor negative mentions

• Small complaints or inconveniences
• Issues that don't significantly impact the person
• Minor frustrations that are easily resolved

40-59: Moderate negative mentions

• Notable complaints about functionality or service
• Issues that cause some disruption to the person's routine
• Problems that affect productivity but aren't devastating

60-79: Significant negative mentions

• Major complaints about essential services not working
• Issues that seriously impact the person's work or life
• Strong criticism that could influence others
• Mentions of considering switching to competitors

80-100: Critical negative mentions

• Mentions of products/services "destroying" or "ruining" someone's life/business
• Extremely emotional negative language
• Threats of legal action or public campaigns against the brand
• Mentions that could cause serious reputational damage

Your Task:

Analyze the following information:

Podcast: {{$json.body.podcast_name}}

Episode: {{$json.body.episode_title}}

Brand/Filter Detected: {{$json.body.alert_matched_filter}}

Sentiment: {{$json.sentimentAnalysis.category}}

Transcript Excerpt: {{$json.body.episode_excerpt}}

Consider:

1. The emotional intensity of the language used
2. The severity of the impact described
3. The context of the podcast and its potential reach
4. Whether the person is likely to influence others
5. The potential for the mention to spread or escalate

Output Requirements:

Return ONLY a JSON object in this exact format:

{"score": integer_between_0_and_100}

Do not include any explanations, reasoning, or additional text. Only return the JSON object with the calculated urgency score.

"""

Updated on: 23/06/2025