In this interview, I’m roasting my friend Lee Jackson on how he is using Airtable to manage the day-to-day business in his agency.
Below, you can find the transcript:
Jan: Hey friends, I’m here with Lee Jackson from Agency Trailblazer and Agency Transformation. And I’m really happy that he took the time to dive into his Airtable account because Airtable is something that I’m playing around with for task management, CRM and all sorts of database, data management stuff. And Lee is a wizard with Airtable. So thanks for taking the time Lee, to walk us through it.
Lee Jackson: Hey mate, so absolutely my please. You happy for me to dive in?
Jan: Yeah, let’s do it.
Lee Jackson: All right, so you should be able to see my screen right now?
Lee Jackson: Epic. All right, so you can see we’ve got quite a lot of bases. So the idea here is
we’re using Airtables as a kind of spreadsheet on steroids for our business. And
that gives us the ability to track a whole load of data but also to report on that
data and also to be able to create documentation. And we’ve got quite a few
different use cases for it. The biggest thing for us is the fact that this acts almost
like an access database online because it’s inter relational. You can use
spreadsheets, which is great on say Google or on Microsoft 365, but that ability
to be able to relate data and to create reports et cetera, although possible, is
just not as easy, there’s a lot of training with a lot of Googling you’ve got to do.
So for us, we really like using Airtable for a lot of this.
So what I’ll do is mate, is I’ll show you through a few of the different types of
bases that we’ve got. You can see here we’ve got quite a lot. We’ve got a little
naming convention going on so that we can group things together. And you’ll
also see on the left hand side that we have a lot of workspaces. One of them is
the pro workspace, which means we get access to the blocks, et cetera. And
then the other ones are free work spaces that we’ve got, which don’t give us
access to the blocks and some of the extra colorization features but still allow us
to track and manage data and share stuff with our clients, et cetera. A lot of
companies to be honest, will get away with the free version of Airtable because
they’re really generous.
Jan: Yeah, I’m on that free version as well right now.
Lee Jackson: Yeah. It’s very impressive. For us, we wanted to be able to use the blocks and I’ll
show you why. And we also wanted to be able to use the colorization and just
some of those extra features. And equally, I’m one of those people as well that
if a product is adding tons and tons of value and it’s free, I feel like I have to find
a tier to pay for so that I feel better. I feel like I’m supporting the application.
So like I said, I can’t scroll too far down because there are a few companies in
there that I have NDAs with. But you can see here we have a whole load of
different types of content including strategy, et cetera.
And the first thing I’ll show you is just what we use for the up and coming
agency transformation live 2020 you can see here that we’re already building
our entire agenda.
So we have our agenda here. It’s grouped, we can create multiple views. So I can
just see day one. So day one’s web view essentially gives us what is currently
showing on the website. And I can actually embed this if I pop that out, which I
won’t because it will show you a private link, but I can actually show that and
embed that on my website or I can send people a direct link in.
What I can also do as well as send forms out to all of these speakers through
this for them to submit all of their speaker information as well. So that might be
their PowerPoint presentation, et cetera. And that’s exactly what we did last
time in 2019 so we set up the agenda, we sent up all of who was doing what
within the event, every single … in fact I’ll show you the run sheet. We set up
this run sheet so we knew exactly what slide was happening. So this is 2019
down to the minute. We had five seconds [crosstalk 00:03:36] we knew that this
was going to go up and on, this particular slide would be up for the last five
minutes, et cetera, et cetera. So that we had an entire build. And because
everyone had it on their apps as well, we had certain people who were
responsible for checking off each and every section as we went through the
What that also did for us, which is why we’ve got the premium features is,
obviously it gave us things like the colorization so we could see when things
were completed, et cetera and we could filter things, but also it gave us the
ability to create these sorts of documents. So this is the older related records,
but if I actually click and expand on the document itself, obviously I’ve deleted
the logo because we’ve redesigned it, but you can see here a quite a nice clean
document which gives us everything that somebody needs to use.
So for us it was a complete no brainer to just go ahead and use this and print it
out on a decent printer so that everybody had a program. So the data was there
and we could export it. Alternatively you can put it through the API as well and
send it to a printer, export it, do whatever you want. But for us, because things
were changing right up until a couple of days beforehand, we just had
everything in here and then we just simply printed it all off on a nice high quality
high grade laser printer so that everybody had a copy of what was going on. We
also had like a run sheet version of this as well, so that we had not only the app
version of the run sheet for people who are using the app, so we were using the
computer, but also we had printed versions of the run sheet down here as well.
We were also able to create other things that I can’t obviously show you, which
include things like email templates that would go out to the speakers, exhibitors
and sponsors, et cetera for information. So it was a request for an information,
et cetera. And also you can see here the finance as well.
So what we did was when people would buy a ticket through our
WooCommerce shopping cart, because WooCommerce and Airtables integrate
really well with Zapier, that allowed us to have a sale coming through
WooCommerce that would then be picked up by Zapier or Zapier, however you
pronounce it. And it would actually put that into here, into us sales pipeline
here. So it would record it, it would create a ticket for them inside of here. So
we knew what ticket we had, what ID number they had, what invoice number
they had used to pay, et cetera.
But also what it went ahead and did was then put it into QuickBooks as well,
which is our accounts package. And it would also then record the payment
against the Stripe account so it automatically re ensiled. So it gave us an entire
flow, one of which we’ve exactly mirrored for 2020s events. So that happens in
the other Airtable that we have, the 2020. So every time someone buys a ticket
and it goes and we’ve got it all set up and ready and we can instantly then click
on the finance tab to do financial reporting. We can see, what’s our costs, how
much sponsorship have we had in, are we still in the red? You know how events
are, you’re in the red for a very long time until eventually it then hopefully
creeps above and starts to go green. So I can see what’s going on, et cetera. So
that’s been particularly useful for us to manage events or manage things with a
lot of data and get information in from other people.
Another thing that we’ve been able to use Airtable for which is really good fun,
is actually for discovery. So for going through that discovery process with a
client and helping them understand how each one of those sections work. So if
you see here, we’ve got this journey breakdown. Let me just talk to you a little
bit about our discovery process. What we will do is, and this is our checklist, is
we will go through with a client and say, all right, you want a new website
building? So first of all we’re going to meet up and we’re going to define all the
terms that your industry uses. So you see this, we’ve got our defined terms.
Then we’re going to establish the users of the platform that they want creating.
We’re then going to list all of the possible journeys that that user is going to go
on. Perhaps they’re going to buy something, perhaps are going to make an
inquiry. Perhaps the admin is going to pick and pack or whatever all those
Then what we’ll do is then start to match those journeys out, et cetera. So you
can see here that what we’ve done throughout the Airtable is we’ve given
ourselves a checklist at the top to ensure that we can go through the process
and then we work our way through here to go through all of those elements. So
we might define first of all what all the terms are. Then we will establish all of the users who will be using the system, what they will be doing, what their
position is, what terms might be related as well to those users.
Then we’ll start to list out the journeys. So perhaps one might register as a
candidate. This is obviously an example, so there’s not actually that much in it.
Their journey might be they’ll register as a candidate. And then what we can do
is we can relay all of the different actions that will be needed for that person to
then register as a candidate. So they’ll fill in the registration form, receive an
email, blah de blah de blah. And we can also see who has touched or what users
are effected and what system that is that’s going to be applied to.
Now the cool thing about doing it this way is it’s super easy for us to then work
through our entire discovery process with a client and it automatically, as you
can see here, creates a document for us. So we now have a document by the
end of the meeting that we’ve not really had to write because we’ve just been
filling in elements of Airtable and then it’s built up the entire document.
Now I mentioned earlier that you could use it as a CRM as well. And I’ll shut up
in a minute so you can ask me questions, but I just wanted to show you these
core ones. And with any opportunity you might want to take that through a
process. Maybe it’s an inquiry, you’ve got needs analysis, you’ve got negotiation
review, et cetera and you want to take it through those stages. So here you can
see an example of a … in fact there’s an entire video on this, if I remember, I’ll
send you the link to it so they can watch it on YouTube. And you can actually
clone this template that I have here. So what this is is essentially a starter pack
for an agency to be able to manage their entire business. So you can manage all
of the opportunities that come in and take them through those processes. You
can also manage the projects that you have and your deliverables with regards
to that project. You’ve got all the task management that would happen, so your
priority tasks, et cetera. That can then all be filtered by your different users,
your different collaborators.
You also have the ability to create your own invoices, obviously manage your
contacts, you’ve got your company management, which is all the companies and
the contacts that are related. And then also we have our tax brackets because here in the UK the VAT sometimes changes. So this is our current VAT, it might
change in the near future.
So this is an entire basic system that replaces the need for any software out
there. If you already have your processes nailed and you just need to create
something that works to match your processes, then Airtable again is just that
perfect delivery system for creating something that works for you.
Downsides would be that often when you click on say Elmer Fudd here, it’s not
Lee Jackson: There’s a lot of information here. I think you can hide certain things, I haven’t
really bothered. So it’s not necessarily user-friendly sometimes, but for a lot of
us, we’re quite technical. We get what our processes are, et cetera. So for us all
we need to do is be able to see the data and then obviously be able to create all
of these different reports. Here’s an example of an opportunity report here that
we’ve got based on this data here so we can see that we’ve won … or what have
we won? What’s on, all right, yeah, we can see here we’ve got 10 K in the
pipeline, et cetera. If we go to, what is it, documentation? Yeah, this is an
example of a logo that … Nope, sorry, I think, example of-
Jan: An invoice.
Lee Jackson: A invoice that you could send. Let’s just take a look at that, actually, how easy
that was to design. So there’s your logo, there’s your data that’s being brought
through. These are the line items they’re the deliverables against the project.
And then obviously you can see your total with the addition of VAT and all that
sort of stuff as well and you can throw in your bank term details and all that sort
of stuff. So it’s ridiculously easy to add these blocks in.
The other thing as well that you can do is do things like for the premium you can
do things like track time as well. So I can actually track time on a particular
record, do the work and then log that. And also there’s a cool little search.
There’s tons of blocks in here as well that take it beyond so you can do an
organization chart if you’re doing hierarchical. We’ve done the organization
chart very often in our discovery because what we will do is design the database
almost inside of Airtable where we will relate records to each other and then
that will give us a nice hierarchical chart, which essentially shows us the
structure of the database or of the modules, et cetera. Obviously we use the
Gantt chart a lot so that we can see what our upcoming schedule is, but there
are all sorts of different ways of visualizing your data.
You’ve got charts, maps, add extra lists and all that sort of stuff. A lot of which
we’ve not really used because we’ve not needed to because the basics are pretty damn good anyway. But for us it’s, we’ve used for definite Gantt charts a
lot. Summaries are really useful for us to be able to see things at a glance,
organization, charts, et cetera. I am coming into land, mate.
So you can see as well to create all of this is ridiculously easy. I can right click
and I can customize any of these fields or I can add a new field. So let’s just get
rid of the blocks and I just want to show you as an example. If I go over to say
the invoice, you can see that these have a weird kind of icon on them, that
means that they are a rollup field. What that means is they are a calculation of
all of these related deliverables.
So if I look at these deliverables, all of these deliverables have got a sum, what
their rate is and what the total price is, et cetera. So you can see here there are
two widgets at £100 each. Therefore, the total is 100. If we then see that our tax
type is of 5% because this particular type, if I hit customized field, you can see
that there is just a very simple Excel style calculation there to then give us our
total with the tax, et cetera. So we’re applying tax to that and then that will go
out into our own invoice. The rollup field therefore is essentially all of those
related records added together. And then we have our subtotal, which is before
tax and then our total, which is with that extra 5% on that one line item. So that
gives you the ability to get really, really techie.
Equally though, it’s just point and click, I can right click or I can just add a new
field, do basic Excel calculations if I need to. And then obviously you can see that
this data is grouped so you can add tons of grouping to give yourself a cleaner
look and feel. So for example, I might want to clone this particular view and
change it so that it’s only showing me companies in the UK and it’s showing me
less fields so that that’s the view I could then share with other people. I might
have the bigger complicated view as the accountant, but then I might give a
simplified view to the rest of my team, et cetera. So you can create multiple
views. And I feel like I’m not even showing you the tip of the iceberg mate, of
what this could do.
So that’s kind of what we’re using it for. You want to hit me with questions?
Jan: Absolutely. That’s a very powerful tool and actually that agency management
system is exactly what I was trying to replicate in Airtable. I have a somewhat
similar approach to you, didn’t know about that template yet, so I’ll definitely
give that a try.
One thing that made me go back to a regular project management tool over to
managing tasks in Airtable was the ability to being reminded when tasks are
due. So is there something that I’m missing when it comes to executing tasks to
see what task I have scheduled for today, tomorrow, the upcoming week and
stuff like that?
Lee Jackson: So there’s a few things you can do there. So we’ve managed tasks in here for
quite a while. Can you still hear me, I just knocked the microphone? Cool. So
you can see here, this is an example task. You can set a date due. You can also
set a date time due. And what you can do is you can create a calendar view of
your tasks. So we’re going to use say the date field here. And what you can then
do is actually have people subscribe. So you can subscribe to this calendar to
have this calendar visible inside of your Outlook or your Gmail or whatever. So if
you add this calendar, you then have a view outside of here and you can set
notifications that way then to tell you if a task is due, because you can set a start
and a finish date and time. So you could actually plan out the particular tasks
that you need to start, what time and then be notified of them, et cetera.
And you can create this particular view based on you, your. So you could say
that this will only show anything that is assigned to Jan, which means that when
you then subscribe to this view, you will only get your tasks up in your calendar
view on your computer or whatever kind of software you’re using.
You can also use Zapier and do triggered notifications, which you can then set to
send you an email. So when a record changes its status to whatever or when a
date is now, then trigger an email and it’ll send you an email, et cetera. So
obviously that’s not, it’s not clean, is it like say ClickUp, which is automatic
because that’s a project management system. But at the same time Airtables
isn’t meant to be
Jan: Project management.
Lee Jackson: The finished system. It’s meant to be the tools to allow you to create the
finished system that you need. So perhaps an enterprise or a company that’s got
very specific needs would want to use something like Airtable to manage their
business as opposed to a done for you system.
Jan: I agree on that. I think that we always to keep in mind that Airtable is like a spreadsheet on steriods. It is not meant to be a full task management system.
Lee Jackson: Mm-hmm (affirmative). Although it can be quite, [crosstalk 00:18:10], actually.
You can’t have collaboration though. I mean that is a downside. So we can
collaborate internally. So say you’re in my workspace, we can collaborate
internally, but if I’ve got the pro account and I want clients to come in and
collaborate on particular tasks, they can’t, unless they have paid accounts and
that means it could get very expensive. There are ways around that where you
can at least create forms for them to submit feedback, which will push data into
the system, but there’s no ability without paying to get them in and get them
collaborating, et cetera. But again, for us that’s not a problem because we don’t
really want our clients in our project management system. We want to give
them the views that only we want them to see, which is the Gantt chart, which they can see publicly and a few other pieces of information so that they are
having a regular update but they’re not actually messing around inside of our
Jan: Yeah. Is there anything that you found wasn’t possible to replicate with
Airtable? Any roadblocks you faced?
Lee Jackson: Anything that wasn’t possible? I mean there are a few things probably that you
… The one thing I haven’t worked out is things like reoccurring tasks, but I do
believe that somebody has put a video on YouTube to show you how to do that.
Jan: I saw a video like that on Youtube.
Lee Jackson: So there are ways of doing things like recurring tasks. So I’ve not yet worked it
out and not really needed it, necessarily. But there are certain things that are
probably more complicated to do. However, this one … What I forgot to show
you was that this particular element here, this is something that we’ve
integrated with Zapier, which allows us to paste in and hit as published our up
and coming podcast episodes, et cetera, and using Zapier, it’ll automatically post
it out across all social media. I actually did a video on that as well. I should
probably share that with you. So because I wanted to … I thought it wouldn’t be
able to do this. So I was just using this initially as this is where we’re going to
store all of our content for the up and coming posts and then we’ll feed it,
maybe export it as a CSV and feed it into buffer.
But then when we looked through Zapier, Zapier has got this really cool ability
to take the data but then trigger the action in the future at a set time. So as long
as I’d set what time or wanted that Tweet to go out, the Tweet would
automatically go into Zapier straight away. But then Zapier would delay it until
that date and that time and then release the Tweet.
Jan: I need to check out that video.
Lee Jackson: It’s really cool.
Jan: I need to build this, yeah.
Lee Jackson: So yeah, it just made it easier. So obviously Airtable on its own is great for our
data management, yada, yada, yada. But the fact that they integrate so well
with Zapier means that there is lots of extra power in there as well. And we’re
using Airtable with WordPress and with QuickBooks so that we’ve got that
whole kind of combined element going on.
And we’re also receiving a lot of data in from our clients nowadays to Airtable
because it’s easier just to get it all straight in. And we’re also displaying lots of data to our clients this way with private links, with passwords associated so that
no one can see it other than the client so that they can just see what’s going on
with their project or access data quickly. So yeah, big lover of it, mate.
Jan: Yeah, seems like that. I think that that’s a good way to wrap it up though. I don’t want to take up much more of your time here and I think-
Lee Jackson: No worries, thanks.
Jan: We could probably talk for the next five hours about Airtable because it’s so
powerful. So for everybody watching this, I recommend you
just get your hands on Airtable. Think about what you can do to start with an
easy base structure, like a very simple thing to replicate in Airtable, check out
Zapier and just take it from there, I guess.
Lee Jackson: Absolutely. Is it Norman something? There’s a guy on YouTube who I would
recommend people follow him as well. He does a lot of Airtable videos if you
want to go down the rabbit hole of learning how to use Airtable in more detail.
So there’s some amazing things that have been built, but for me, data
management and for useful tools, Airtable is a winner, winner, winner, chicken
Jan: One last question: where do people connect with you and find out more about you?
Lee Jackson: Awesome. Just come along to agencytrailblazer.com and you’ll find my podcast and you’ll find links to the YouTube channel and you’ll find links as well to our lovely Facebook group that you can come and hang out with us in. And also they can find us both, can’t they? On the CloudWays Mavericks call every second Tuesday of every month.