From Excel to Airtable: Building a Threats & Opportunities Dashboard for Public Affairs

Paul Shotton, PhD

Co-Founder Advocacy Academy, Advocacy Strategy and Owner at Paul Shotton Consulting

April 15, 2025

Over the years, I’ve built a number of tools in Excel to help public affairs teams manage stakeholder mapping, policy prioritization, and campaign planning. While these tools were powerful, they often hit a wall when it came to wider team adoption. Why? Because Excel requires a level of familiarity — with formulas, with Gantt charts, with data integrity — that not every team member has, especially when juggling multiple issues, markets, or timelines.

In short: the tools worked, but not everyone could work with the tools.

Why Airtable? Why Now?

I’ve tried other platforms too. Microsoft's Business Insights dashboards, for example, have been useful for tracking engagement and visualizing priorities in the policy landscape. There are also solid campaign management tools like Monday.com, Smartsheet, or even Trello, depending on the team setup.

But in this recent project, the client already had a license for Airtable, and it was approved for internal use. That made the choice easy. What wasn’t so easy was realizing that I couldn’t just copy-paste the Excel logic into Airtable and call it a day.

Even with some experience, I underestimated how much relearning and redesigning would be needed. Airtable operates more like a relational database than a spreadsheet, and the real value comes from tailoring it to how people actually work.

So, I had to stop, take stock, go through Airtable’s training materials, and rebuild the system from the ground up.

Start with the Workflow, Not the Tool

The biggest lesson? Don’t start with the tool. Start with the workflow.

Before building anything, I worked with the client to map:

• How threats and opportunities are identified,

• Who owns what part of the process,

• How information is updated and communicated internally,

• What their ideal workflow might look like.

That gave us the foundation to structure the Airtable base in a way that reflected the team’s reality — not just a theoretical best practice.

The MVP: Keep It Simple, Keep It Useful

Once we had the workflow, we co-designed a minimum viable product (MVP) that would be functional without overwhelming the team. Here’s what made it into the first version:

• Issue title

• Description

• Region

• Country

• Owner/team

• (Optional) policy cycle stage

• Threat score (1–5) + description

• Opportunity score (1–5) + description

• Links to relevant documents

We debated a few extras — but ultimately decided to keep it focused. The goal wasn’t to capture everything; it was to make starting easy and consistent.

Tailoring Views to Roles

Airtable’s flexibility let us design different interfaces for different types of users:

• Regional team members use forms or filtered views to submit and update issues.

• Managers have dashboards for visibility across regions.

• Product-specific departments can access data relevant to their own files.

• A designated database manager maintains the overall structure and quality control.

This approach meant that people didn’t have to learn the whole system — just their part.

It Took Longer Than Expected

This process took more time than I expected. We had to reschedule parts of the project. It forced me to reflect — and to adjust. Even if you have experience with tools and workflows, building something that sticks inside an organization takes time.

This made me appreciate the value of potentially bringing in external support — someone who can help map out internal processes and align technical design with the team’s actual ways of working.

Next Up: Onboarding and Adoption

We’re now moving into the most critical phase: onboarding.

Our goal is to make the tool part of everyday routines, not just another “nice idea” parked on a shared drive. That means:

• Introducing it gradually, through weekly meetings and check-ins,

• Providing bite-sized support to contributors,

• Assigning ownership, and

• Using Interfaces and Forms to simplify input.

The good news? Most people don’t need to understand the full backend. They just need to know how to do their part, and Airtable’s customization options make that easier than ever.

Final Thoughts

If you’re considering moving from Excel to a more collaborative platform like Airtable, my advice is simple:

• Don’t start with templates.

• Start by asking: how do we actually work today?

• Then, build from there — slowly and simply.

This project is still ongoing, and I’ll be sharing more lessons as we move through the process. If you’ve built something similar — or are thinking about it — I’d love to compare notes.