Create a Highly Effective Activity Dashboard for Your Communication Agency

You are a digital strategy consultant specialized in steering communication agencies. Your mission: design an activity dashboard for agency directors, project managers, sales and marketing managers. This dashboard must enable real-time monitoring of agency performance, project profitability, team productivity, and client satisfaction.

Dashboard Structure

1. Executive Summary

• Overall revenue ([PERIOD]): amount, change vs. previous period
• Overall gross margin: margin rate, change
• Number of projects in progress / completed / pending
• Resource utilization rate: percentage of team usage
• Net Promoter Score (NPS) or average client satisfaction score

2. Project Profitability

• List of [NUMBER] most/least profitable projects
• Margin per project: cost vs. budget
• Time spent vs. budgeted time per project
• Drift indicator (budget/time) per project

3. Team Productivity

• Task completion rate by [DEPARTMENT]: creative, strategy, production
• Billable vs. non-billable hours per employee
• Deadline adherence: % of projects delivered late
• Number of revisions / client feedback per project

4. Sales Pipeline

• Number of leads inbound/outbound
• Conversion rate (signed quotes / sent quotes)
• Average contract value
• Average sales cycle length

5. Client Satisfaction & Reputation

• Average project rating (1-5)
• Recommendation rate (% of clients willing to recommend)
• Verbatim analysis: positive/negative feedback excerpts
• Recurring vs. new business revenue

6. Visibility & Acquisition

• Agency website traffic
• Bounce rate and average session duration
• Number of backlinks obtained in [PERIOD]
• Newsletter open rate

Expected Format

• A dashboard with 6 blocks (one per section) each containing: key indicator, value, change (green/red arrow), and alert threshold.
• Data should be automatically extractable from a CRM ([CRM_NAME]) and a project management tool ([PM_TOOL_NAME]).
• Use standard color coding: green (good), orange (average), red (critical).

Industry Constraints

• A communication agency sells time and creativity: unbilled hours (sales, admin) must be visible but separated.
• High team turnover: add a mental load indicator (e.g., number of simultaneous tasks per employee).
• Client satisfaction is key: integrate a 'dissatisfied client alert' widget if rating < 3.

Customization

• Adapt metrics according to agency type: [AGENCY_TYPE] (creative, digital, PR, events).
• Allow filtering by [PERIOD] (month, quarter, year) and by [DEPARTMENT].

This prompt must be actionable: indicate calculation formulas for each KPI (e.g., margin = (revenue - cost) / revenue) and recommended data sources.