Build a Shopify CRM in Airtable with AirPower

In Shopify, customer and order data lives in the admin’s customer list, segments, and order pages. Building a true CRM workflow on top of that data, tracking interactions, segmenting buyers, calculating lifetime value, usually means exporting CSVs or paying for a dedicated CRM product.

Airtable is a database, task tracker, and reporting tool in one. With the right tables and links, it works well as a Shopify CRM shaped to how your store actually operates.

AirPower syncs Shopify customers, orders, line items, products, and variants into Airtable. From there, table links, rollups, grouping, and filters become the building blocks of a CRM you can tailor. This guide covers the setup and the patterns we see most often.

What is a CRM

A Customer Relationship Management (CRM) system helps you manage your customer data and use it to grow your business. A CRM keeps customer information up to date, tracks customer interactions, shares data with colleagues, and helps you learn from customer behavior. It’s designed to improve customer relationships and increase Customer Lifetime Value (CLV): the total amount of money a customer spends with your store across all their orders.

By understanding customers better, you get better at cross-selling and up-selling, giving you a much better chance to win new sales from your existing Shopify customers.

Benefits of a CRM system

Understanding your customers
A CRM stores customer details and purchase history in one place. The more you understand your customers, the better you can target their interests, leading to more sales.

Increasing accountability
A CRM lets you see all your customer data in one place: core customer information (name, address, etc.), order history, tasks, and any third-party data.

Better internal communications
A CRM keeps you and your team literally on the same page. When everyone has the same information, they communicate better with customers and amongst themselves. This also ensures that anyone on your team can take over a client relationship at a moment’s notice.

Automation of admin tasks
Having you and your team focus on sales is the most important aspect of your business. A CRM helps automate small admin and data entry tasks that can sap vital time and lead to context switching and lost productivity.

Cost savings
Setting up a CRM takes time, and it pays back over time. It grows your top line through better targeting and service to new customers, and upsells and cross-sells to existing customers. Having a CRM you can set up quickly, like you can with Airtable, makes it even better.

Why use Airtable as a Shopify CRM

The CRM software landscape is vast, with hundreds of products competing to be the best. Some even integrate with Shopify directly, though often crudely. While not a CRM-specific tool, Airtable is a good fit for the job.

A CRM is fundamentally a customer database, task tracker, and analytics tool. Airtable works well across these areas. Its customizability lets you quickly create the right CRM for your business, rather than fitting your business into how existing CRM software works. It’s no surprise that Airtable is frequently used as a CRM system, task management system, and an analytics tool.

How to set up and use Airtable as a Shopify CRM

Let’s get into the details. Many AirPower clients use our Shopify-Airtable integration to build a Shopify CRM. We’ve learned a lot from working with and listening to our clients about their CRM buildouts and goals. In this guide we’ll share best practices for setting up and using Airtable as a Shopify CRM.

Getting the necessary Shopify data to sync to Airtable

The most important CRM ingredient is customer and sales data, so let’s get that flowing. First, add the AirPower app to your Shopify store. Next, set up the Airtable base you want to sync data to. If you don’t have a base already set up, take a look at our pre-made template bases.

With the app in your store and your base set up, add the data a CRM needs to your Shopify to Airtable sync: Customers, Orders, Order line items, Products, and Variants.

1. On the dashboard, click Edit on your Shopify to Airtable sync
2. Click Add data, then the data type, such as Customers
3. Click Create a Customers table, or pick one of your tables from Use a table
4. Repeat steps 2 and 3 for each data type the sync doesn’t have yet
5. Click Save in the save bar
6. In Save changes, keep the data types ticked and click Save changes

AirPower links the tables to each other, so each order links to its customer and its line items, and each line item to its product and variant. See Bulk edit customers and Bulk edit orders. If you need help, send us a note.

Using Airtable as your Shopify CRM

Now comes the fun part. With data flowing, we can focus on using your new Airtable CRM. Below are the core CRM use cases we see our clients using and how to get them up and running.

Calculate your Customer Lifetime Value (CLV)

AirPower links each order to its customer, so a rollup column on your Customers table can add up every customer’s orders.

1. In your Customers table, click + at the end of the column headers
2. Find Rollup and click it
3. Name the column, such as Total Order Amount
4. Under Select rollup source, pick Orders
5. Under Orders field you want to roll up, pick Total
6. Keep SUM(values) and click Create

Each customer’s row now shows the total of their orders. Shopify’s own Total Spent can come into Airtable too, but a rollup lets you choose which orders count, with Only include linked records from the Orders table that meet certain conditions.

Segment your customers and find CLV per segment

Once your customer data is in Airtable, you can start segmenting customers to determine the CLV for each segment. Click Group in the view’s toolbar and pick the column to segment by, such as Tags. Each group shows how many customers it has and, for number columns such as Total Order Amount, their sum. You can also filter the grouped data, for example, removing customers with fewer than two orders.

With grouping and filtering, Airtable gives you two tools to slice and dice your customer data to find the customer segments that are most or least profitable, have the largest basket size, place the most orders, and more.

Identify your best and least selling products

Set up your Airtable CRM so the table storing your product data is linked to the table storing your order data. Then, create a rollup column in the products table that sums all the order amounts for orders associated with each product. This is similar to what you did for customers above. This column will show the total amount sold of each product. Sort this column in ascending or descending order to see your least or best selling products respectively.

Identify your best and least selling vendors

Set up AirPower to sync the “vendor” value of each order line item into the table that stores your order line items. Now, do one of the following:

• Group the data on the “vendor” column. The sub-totals for the total price column for each vendor grouping will show total sales by vendor
• If you’ve set up AirPower so your vendor column is linked to a table listing your vendors, you can get a bit fancy. Go to the table storing your vendors and create a rollup column that pulls the total dollars sold via the relationship to your order line items table. Now sort based on this total.

Track orders that are pending fulfillment

On the Orders tab of your sync, click Sync to Airtable under Fulfillment status. Then, go to the table that stores your orders and filter the column that has your fulfillment status to “unfulfilled” or “partial”.

Generate customer mailing lists based on purchase history

With all your customer data in one place, you can pull out the information a mailing campaign needs. Use Airtable’s filtering capability to narrow down your customer list to match the criteria you need. For example, you may want to email customers who haven’t placed an order in the last six months.

Once you’ve filtered your customer data, export it to CSV and import the CSV file into your email marketing system of choice (like Mailchimp).

Get a monthly transaction report for reconciliation

Use Airtable’s “DATETIME_FORMAT” formula (see here for details). Add a new column to the table holding your orders data and use the DATETIME_FORMAT formula to extract the month name into the new column (format “MMMM”). This new column will now contain the name of the month the order took place, which you can then filter or group by as needed to produce a monthly sales report.

Manage customer-related tasks

Expanding your Airtable-based Shopify CRM to suit your needs is one of the key advantages to using Airtable over a traditional CRM product (aside from being more affordable). Here’s how to add team tasks to your CRM.

Add a “Tasks” table to your Airtable base. Each row can be a task, and you can set it up to contain all the data your tasks need. Also add a column of the “Collaborator” type. If you add a fellow Airtable user to this column, they’ll be notified by email of new tasks assigned to them.

Now link the “Tasks” table to your “Orders” or “Customers” tables as needed. To add a task to an order or customer, add a new linked task. You can do this manually, or use a tool like Zapier to automatically add tasks when, for example, a new order row is added to the orders table.

Did we miss something?

This guide is ever-growing content. We add new CRM use cases as we learn about them from our clients. Keep checking in from time to time to see what’s new, and if we’re missing some way Airtable can help Shopify stores as a CRM, please let us know by dropping us a line!

Related pages

• Set Up Shopify Customers in Airtable
• Set Up Shopify Orders and Line Items in Airtable