Create a powerful Shopify CRM with Airtable

So, you are looking to use a CRM to better manage your Shopify store and increase sales? And you are thinking of using Airtable for the job? Well, you've come to the right place. We've written this article to share with you some of the best ways we've seen our clients use their Airtable-powered CRMs to grow their Shopify stores. We'll tell you how to set things up, and what you can achieve. Let's dive in.

What is a CRM

A Customer Relationship Management (CRM) system helps you manage your customer data, and use this data to grow your business. A CRM system helps businesses keep customer information up to date, track customer interactions, share this data with colleagues, and learn from customer data. A CRM is designed to help businesses improve customer relationships and also Customer Lifetime Value (CLV) - the total amount of money that a customer spends with your store across all of their orders.

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

Benefits of a CRM system

1. Understanding your customers

A CRM system is a fantastic tool for storing details on your customers, and their purchase history. The more you understand your customers, the more you can uniquely target their interests, leading to better targeted products, leading to more sales.

2. Increasing accountability

A CRM allows you to see all your customer data in one place. All the core customer information (name, address, etc.), their order history, any tasks you have for them, any other third-party information, etc.

3. Better internal communications

A CRM is a great tool to keep yourself and your team accountable and literally on the same page. When everyone on your team has the same information, they are better at communicating with customers, and communicating amongst themselves. This also ensures that anyone in your business can take over a client relationship at the drop of a hat.

4.Automation of admin tasks

Having you and your team focus on sales is the most important aspect of your business. A CRM can help you automate those small admin and data entry tasks that can sap away vital time during the day, and that can lead to context switching and loss of productivity.

5. Cost savings

The initial cost and time to set up a CRM is an investment, but is one where the return on this investment can be astounding over time. It all results in growing your topline, from better targeting and service to new customers, and upsells/cross-sells to existing customers. This being said, having a CRM that you can set up quickly, like you can with Airtable, is great too!

Why use Airtable as a Shopify CRM

The CRM software landscape is vast. There are hundreds (thousands?) of products that compete to be the best CRM out there. Some of them even integrate with Shopify directly, although oftentimes rather crudely. Although not a CRM-specific tool, Airtable is a fantastic candidate for the job. A CRM is fundamentally a customer database, task tracker, and analytics tool; in all of these dimensions Airtable not only shines above the rest, but it's customizability allows you to quickly create the right CRM for your business, vs. having to fit your business into how an existing CRM software product works. It's no surprise that Airtable is frequently used as a CRM system, task management system, and an analytics tool. No overhead and full customizability - what can be better?

How to set up and use Airtable as a Shopify CRM

Let's get into the nitty gritty. Many AirPower clients use the Airtable-Shopify integration we provide to build such a Shopify CRM. We've learned a lot from working and listening to how our clients have approached the CRM build-out, and what they are looking to achieve. In this article we'll share the best practices of how to set up and use 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 in. 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 AirPower app in your store, and your base set up, we need to make sure that the necessary data for a CRM is set up to sync. For this we need Customer, Order, Order Line Item, Products, and Variants data at least. This is all very easy to set up with AirPower, but if you need a hand just send us a note.

Using Airtable as your Shopify CRM

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

Calculate your Customer Lifetime Value (CLV)

Airtable's table linking functionality makes calculating Customer Lifetime Value almost trivial, and if you used one of our base templates then it's already done for you. All you have to do is ensure that the table storing your Customer data is linked to the table storing your Order Data. With this link active go your Customer table and create a rollup column to SUM up the total dollar amounts of the orders associated with the given customer.

Segment your customers and find CLV per segment

Once your customer data is in Airtable we can start segmenting your customers and to determine the CLV for each customer segment. The way to accomplish this is with Airtable's powerful grouping functionality. Simply find the column that you want to segment by and group your data by this value. Airtable will automatically calculate the total amount purchased column for each group. Keep in mind that you can also filter the grouped data, by removing customers with less than two orders, for example.

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

Identify your best/least selling products

To do this we need to set up our Airtable CRM so that the table storing our product data is linked to the table storing our order data. Then, create a rollup column in the products table that sums up all the order amounts for all the orders that are associated with the given product. This is very similar to what we did for your customers above. This column will now show us the total amount we've sold of each product. Now sort this column in ascending or descending order, and this will show you the best/least selling products respectively.

Identify your best/least selling vendors

To do this we need to set up our Airtable CRM so we have set up AirPower to sync the "vendor" value of each order line item into the table that stores our 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 tell you the total sales by vendor
  • If you've set up AirPower such that when the data sync happens 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 will pull 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

Ensure that you've set up AirPower to sync the "fulfillment_status" order field to your Airtable base. Now, go to the table that stores your orders and filter the column that has your fulfillment status to be one of "unfulfilled" or "partial".

Generate customer mailing lists based on purchase history

With all your customer data in one place you can easily extract the information necessary to create a mailing campaign. Use Airtable's filtering capability to narrow down the list of customers to match the criteria you need. For example, you may want to email customers that haven't placed an order in the last six months.

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

Get a monthly transaction report for reconciliation

To do this we'll need to make use of Airtable's nifty "DATETIME_FORMAT" formula (see here for all the details). Add a new column to the table holding your orders data and use the DATETIME_FORMAT formula to extract just the month name into the new column (format "MMMM"). This new column will now contain the name of the month that the order took place, which you can then filter on/group by as needed to produce a monthly sales report.

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 the fact that it will also be cheaper). Here is how to add team tasks to your CRM.

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

Now link the "Tasks" column to your "Orders" or "Customers" tables as needed. Now, to add a task to an order or customer just 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 an ever-growing piece of 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 are missing some way in which Airtable can help Shopify stores as a CRM please let us know by dropping us a line!

© 2019 Intelaria Corporation