Automate and Streamline Your Newsletter Sponsorships

⌛ Read time: 12 minutes

💰 Potential savings: 20+ hours a month (min.)

A few questions I often receive about automation and building systems are, "Why do I like Airtable? Isn't it just an overpowered spreadsheet?"

Spreadsheets have their place. But, when it comes to automating systems and relying on clean data, that’s where a tool like Airtable (or Notion) are the ideal choice.

To illustrate this, I’ll share a tangible use case: managing newsletter sponsorships and cross-promotions.

Even if you don’t run a newsletter or handle sponsorships, I believe this read will still prove valuable.

Why is Airtable the ideal choice?

Airtable’s real power lies in its ability to serve as a true relational database.

Here is a side-by-side comparison of a relational database and spreadsheets.

Managing Sponsorships – Before State

Here's an example of what the typical exchange looks like if someone wants to sponsor your newsletter:

1. A brand emails you, or you email them about a potential sponsorship.

2. You exchange a few emails back and forth, discussing pricing, availability, terms, etc.

3. You finally agree on something and then discuss ad details; you mark it on your calendar so you don’t forget.

4. Once the ad goes live, you notify them.

5. You follow up with them a week later and update them on ad performance since, ideally, you’d want to keep the relationship going.

"Five steps, big deal, Mike," I hear you say.

Yes, it might seem simple. But this is just for one sponsor.

And,

• You’d have to sift through ten emails to reference any info in the future.

• Then there's the context switching and the potential for errors since we're human and make mistakes.

• Delegating this process becomes difficult and nearly impossible, meaning less time for what you enjoy.

• All of this leads to increased ‘attention residue’.

Newsletter Sponsorship Booking Workflow – After State

1. A potential sponsor (brand) visits your site. Since you’d have a Sponsor page, they can obtain the information they need and book an ad directly from your website, eliminating the need to email you for a link. (Click here to view mine)

2. The sponsor purchases an ad slot in your newsletter.

3. They fill out an Airtable form with their information.

4. 🤖 An automated Slack message alerts you when someone has booked an ad.

5. Manual step: Decide if it’s a good fit. If yes, mark it as approved. If not, you mark it as 'not approved'.

6. Manual step: Apply a 'template' (don’t worry if you don’t know what this is — it’ll be covered). All it takes is two mouse clicks.

What are the benefits?

• You only have to complete two steps, which will take you just 20 seconds.

• No back-and-forth emails or setting reminders so you don’t forget to include the ad in your email.

• Scalability: This system is now repeatable, allowing you to delegate it eventually and focus on high-leverage tasks.

• Professionalism: The way you communicate with brands and sponsors is important. This system makes it easy to stay on top of things — sponsors are more likely to continue working with you if you can effectively deliver and communicate the ROI you're generating. It can be challenging to do this effectively if your data (or 🧠 brain) are scattered.

  

  Newsletter Sponsorship System – Ad Bookings Table GIF

Reminder: Do this before you continue:

Step 1: Duplicate the Airtable Sponsorship Booking Template

Step 2: Duplicate this Checklist I’ve created – If you’re familiar with Airtable then I’d start with this – you can do Step 3 if you’d like to get into the nitty-gritty

Sponsor Alert

If you’re a freelancer, agency, or consultant and need help setting up your systems, check out Work Hubs. Ask for Sara, and tell her Mike sent you – she’ll hook you up!

"We build & manage powerful, digital workspaces that inspire your team, produce better client results, and scale your business."

All for a simple, fixed monthly rate.

Let’s go over the Newsletter Sponsorship Booking Workflow – The After State once more, but this time in more detail!

1. Sponsor pays and completes form submission

After the sponsor pays, they’re directed to an Airtable Form

Every submission is then added to your database allowing you to manage all your bookings in single location with minimal oversight.

2. Slack notification

Once the sponsor completes the form, you're notified immediately via Slack:

3. Approving Ads

Approving Ads: When a new sponsorship is booked through your paid sponsorship page (e.g. Hidden Levers AI Sponsorships), simply decide whether or not they’re a good fit. By default, any new submissions are set to “⏸️ Under review”

Go to your 🏠 Ad Bookings & Cross-Promos Table and select the ‘Grouped ⏸️ Under Review – Paid/CP Ads’ View then update the status field ‘🫰Is Ad approved?’:

• 🚀 Approved

• 🚫 Not approved

4. Linking Approved Ads

Let’s assume the ad is approved.

• Navigate to your Approved but NOT Live – Grouped View (still within the 🏠 Ad Bookings & Cross-Promos table)

• Right-click the same record, hit "Apply Template" and select “Link UTM”.

  

  Step 1: Apply Template

  

  Step 2: Link UTM

Note: You need an Airtable Pro account to use Record Templates.

If you don’t have a Pro Airtable account, then you can link it yourself with only a few extra clicks. Here is how (assuming we’ve approved it)

Navigate to your Approved but NOT Live – Grouped View (still within the 🏠 Ad Bookings & Cross-Promos table)

1. Click the + under the ‘🔗Linked to UTM Generator’ field

2. Click ‘Create a new record’

3. Select the campaign type: paid or cross-promo.

   

What are record templates? Click here

And, that’s it on your end. Now, you’ll get the deets of how this database is set up.

The Details: Ad Bookings & Cross-Promos Table

The Ad Bookings & Cross-Promos table is the heart of the Airtable sponsorship booking system. It's where all the information about your ad bookings and cross-promotions is stored and managed.

The rest of this tutorial goes into detail about each field.

If you’re familiar with Airtable then you probably don’t need to go through this!

Inside Your 📆 Slots – Dates Promos Fields

• 🚫 Title - Date - Slot: This field combines the title of the ad, the date it's scheduled for, and the slot it's booked in. It's automatically generated when a new booking is made.

• 🗒️Ad Promo Tagline: This field contains the tagline for the ad, as provided by the sponsor.

• 🗒️Promo Link: This field contains the link to the sponsor's website or the specific page they want to promote.

• 🔗 Linked to 📆 Slots – Dates Promos: This field links the ad booking to the corresponding slot in the Slots – Dates Promos table. This allows you to easily track which slots are booked and which are still available.

• 🗒️ promo_type: This field indicates whether the booking is a paid ad or a cross-promotion.

• 🔗Linked to UTM Generator: This field links the ad booking to the corresponding record in the UTM Link Generator table. This is where the UTM link for the ad is generated.

• 🔍 UTM Link (lookup): This field automatically pulls in the UTM link from the UTM Link Generator table.

• 🗒️Title: This field contains the title of the ad, as provided by the sponsor.

• 🗒️ Email: This field contains the sponsor's email address.

• 🗒️ Email Notification (Live Ad): This field indicates whether an email notification has been sent to the sponsor to let them know their ad is live.

• 🚫📆 Date Created: This field automatically records the date the booking was made.

• 🫰Is Ad Live?: This field indicates whether the ad is live or not.

• 🗒️First Name: This field contains the sponsor's first name.

• 🗒️Last Name: This field contains the sponsor's last name.

• 🫰Is Ad approved?: This field indicates whether the ad has been approved. You can manually change this status after reviewing the ad.

• 🗒️ Target audience: This field contains information about the target audience for the ad, as provided by the sponsor.

• 🗄️ Archived: This field allows you to archive old bookings to keep your table clean and organized.

The Ad Bookings & Cross-Promos Views

In addition to the fields, the Ad Bookings & Cross-Promos table also includes several views. These views allow you to filter and sort your records in different ways, making it easier to manage your bookings.

Here's a breakdown of each view:

• ✉️ Send Email When Ad Goes Live: This view shows all ads that are live and for which an email notification needs to be sent to the sponsor.

• Empty UTM Link: This view shows all ads for which a UTM link has not yet been generated.

• Master (All) Bookings: This is the default view that shows all bookings, regardless of their status or type.

• 💰 Paid Sponsors (Live and Non-Live): This view shows all paid sponsorships, whether they're currently live or not.

• 🚀 Live Ads Only (Paid): This view shows all paid ads that are currently live.

• ⏸️ Under Review – Paid Ads: This view shows all paid ads that are currently under review.

• 🗄️ Not Approved & Archived: This view shows all ads that were not approved or have been archived.

• 🤝 Cross-Promos: This view shows all cross-promotions.

• Book Your Sponsorship: This view is designed for sponsors and shows available slots for booking.

• Cross Promo Form: This view is designed for cross-promotions and shows available slots for booking.

The Slots – Dates Promos Table

The Slots – Dates Promos table is where you manage your available ad slots and track which ones are booked and which are still open. 

Here's a breakdown of each field in this table:

• 🚫Date - Slot: This field combines the date and the slot number. It's automatically generated when you add a new date and slot to the table.

• 🔗Ad Bookings (from Track Ad Bookings & Cross-Promos): This field links the slot to the corresponding ad booking in the Ad Bookings & Cross-Promos table. This allows you to easily see which ads are booked in each slot.

• 🚫🤖Total Booked: This field automatically calculates the total number of bookings for each slot. It's a great way to quickly see how many bookings you have for each date and slot.

• 🤖🫰Slot Number: This field contains the slot number. This is useful for organizing your slots and making it easy to identify them.

• 🤖🫰Status: This field indicates the status of the slot. It can be "Available", "Booked", or "Archived".

• 🫰Date: This field contains the date of the slot. This is useful for planning your bookings and ensuring you don't double-book a slot.

• 🤖Archived: This field allows you to archive old slots to keep your table clean and organized.

• 🫰 Book Your Ad: This field is a button that directs sponsors to the booking form when they want to book a slot.

• Add New Dates: This field is a button that allows you to quickly add new dates and slots to the table.

The Slots – Dates Promos Views

The Slots – Dates Promos table also includes several views that allow you to filter and sort your slots in different ways.

Here's a breakdown of each view:

• Available Sponsorship [Public]: This view shows all available slots that are open for booking.

• Paid PUBLIC Available Sponsorships: This view shows all available slots that are open for paid sponsorships.

• Cross-Promo Public Available Sponsorships: This view shows all available slots that are open for cross-promotions.

• Booked Calendar View: This view shows all slots that have been booked, displayed in a calendar format.

• 🏠 Master Sponsorships: This is the default view that shows all slots, regardless of their status.

• Grouped by Slot Number Master View: This view groups all slots by their slot number, making it easy to see how many slots you have for each number.

• Available ONLY Slots: This view shows all slots that are currently available and not yet booked.

• Archived View: This view shows all slots that have been archived.

The UTM Link Generator Table

The UTM Link Generator table is where the UTM links for your ads and cross-promo campaigns are generated.

Not familiar with UTM codes? Read the article below.

UTM Links are important for tracking the performance of a campaign.

Example:

Here's a breakdown of each field in this table:

• 🚫 Title - Date: This field combines the title of the ad and the date it's scheduled for. It's automatically generated when a new record is created in this table.

• 🔗 Track Ad Bookings & Cross-Promos: This field links the UTM link to the corresponding ad booking in the Ad Bookings & Cross-Promos table. This allows you to easily track which UTM link corresponds to which ad.

• Campaign Name: This field contains the name of the campaign for the campaign.

• Source (required): This is useful for understanding where your traffic is coming from. “Use utm_source to identify a search engine, newsletter name, or other source.”

• Medium (required): Use utm_medium to identify a medium such as email or cost-per-click.

• Content: This field contains the content of the ad. This is useful for tracking which specific content is driving traffic. Any call-to-action or headline, e.g. buy-now.

• Keyword (or Campaign Term): Hide this field unless you're running paid ads.

The rest of the UTM Links Generator fields:

• 🔍 Promo Link from Track Ad Bookings: This field automatically pulls in the promo link from the Ad Bookings & Cross-Promos table. This ensures that the correct link is used in the UTM link.

• 🚫 URL to String: This field automatically converts the UTM link to a string. This is necessary for the link to work correctly.

• Last Modified: This field automatically records the last time the record was modified. This is useful for tracking changes and updates to your ads.

• Created: This field automatically records the date the record was created. This is useful for tracking when each ad was booked.

How the UTM Link formula works

🚫fx UTM Link: This field automatically generates the UTM link for the ad. This link is crucial for tracking the performance of your ad and understanding how your audience interacts with it.

Let’s zoom in on what this formula does, step-by-step

1. Check if the URL already starts with 'https://'.

- If not, add 'https://' to the start.

2. Append UTM parameters (Medium, Source, Campaign Name, Content, Keyword) to the URL, if they exist.

• For each parameter, if it exists, it gets added to the URL like so: '&utm_parameter=' followed by the parameter value.

3. After appending all UTM parameters, replace the first '&' character with '?' to start the query string in the URL.

4. Convert the entire URL to lowercase using the LOWER() function.

5. Replace any spaces in the URL with '%20' (the URL-encoded value for a space) using the SUBSTITUTE() function.

The final result is a well-formatted, lowercase URL with UTM parameters.

Sponsorship Booking Airtable Automations

I’ve created this Notion template where I cover what the six automations do in detail.

Click the button below:

The Automations

One of the key features of the Airtable sponsorship booking system is its automations. These automations are designed to streamline the booking process and save you time. You will need all 6 if you’d like to replicate the system I’ve described.

Here's a breakdown of the main automations in the system:

1. Update STATUS to BOOKED When Total Booked = 1:
   

   

   Update STATUS to BOOKED When 🤖Total Booked = 1

   
   
   This automation gets triggered when the 'Total Booked' field in the Slots – Dates Promos table equals 1, indicating that a slot has been booked. The automation then updates the 'Status' field of the corresponding record to "Booked". This helps keep your slots table up-to-date and accurately reflects the availability of each slot.
   

2. Update STATUS to AVAILABLE When Total Booked = 0:
   

   

   
   Similar to the previous automation, this one gets activated when the 'Total Booked' field in the Slots – Dates Promos table equals 0, indicating that a slot is available. The automation then updates the 'Status' field of the corresponding record to "Available". This ensures that your slots table accurately reflects the availability of each slot.

3. [OPTIONAL] Send Slack Message When Paid Booking Form Is Submitted:

   

   • This optional automation sends a message to a specified Slack channel when a new paid booking form is submitted.

   • The message includes details of the booking, allowing you to quickly review new bookings without having to check Airtable.

   • This automation uses Slack but you can choose email too.
     

4. [OPTIONAL] Archive Past-Due Records:
   
   This automation archives records in the Ad Bookings & Cross-Promos table that are past their live date. The automation checks if the date of the ad is more than a day in the past and if so, flags the record as archived and moves it to the Archive view. This helps keep your table clean and organized, making it easier to manage your current and upcoming bookings.

5. [OPTIONAL] When Ad Is Live = ✅ Send Sponsor an Email:

   

   Airtable Automation: When Ad Is Live = ✅ Send Sponsor an Email

   • This automation is triggered when the 'Is Ad Live?' field in the Ad Bookings & Cross-Promos table is checked, indicating that the ad is live.

   • The automation then sends an email to the sponsor to inform them that their ad is now live.

   • View the right side of the photo for the conditions

   The email is sent using a specified Gmail account, and the body of the email includes a friendly message informing the sponsor that their ad is live. This automation helps keep sponsors informed and saves you the time of manually sending the email. You can view the details of this automation here.

   • Email Notification When Ad Goes Live: This automation sends an email to the sponsor when their ad goes live. This keeps the sponsor informed and saves you the time of manually sending the email.
     

6. [OPTIONAL] Send an email when ad is approved:

This automation is triggered when the 'Is Ad approved?' field in the Ad Bookings & Cross-Promos table is set to "Approved".

Here's a breakdown of each element:

Trigger Details

This automation gets activated when a record in the Ad Bookings & Cross-Promos table starts matching the following conditions:

1. Is Ad approved? is set to "Approved".

2. Email is not empty.

3. UTM Link (lookup) is not empty.

4. promo_type is set to "paid".

Similar to the previous automation, this trigger does not fire for records that already satisfy these conditions; it only gets triggered when a record starts to match these conditions.

I’ve got some video walkthroughs of this system to help you set it up. They’ll be on the site soon.

I went a bit deeper with this workflow. I’d love to know if this is something you’d like to see or if it is too much. Take a vote below.

Feedback is always appreciated 🙏