EZ Entry — Boost Your Business by Automating Your Data
Written in collaboration with Mateo Montero and Oscar Wronski. Project for ENTI 559.7 at the University of Calgary.
We are EZ Entry, a group aiming to automate the data-entry process in order to make life easier for the e-commerce sales sector. Who likes making spreadsheets? Very few people answer “yes” to this question. Unfortunately, spreadsheets and the data that they contain are both necessary and important in any business venture. Finances are crucial to business success, but data entry gets boring, and it’s easy to make a mistake. Our goal is to make life easier for small businesses and entrepreneurs, who can’t afford expensive accountants, to track and record customers, sales, and invoices. In this blog post, we outline how you can create cloud-based tool using four platforms in order to automate data-entry on the e-commerce side of business.
The Tools
We will be utilizing four tools in order to streamline and automate the process of data entry for sales and invoicing. The tools and their purposes are as follows:
- Airtable: Airtable is a cloud-based service that combines the functionalities of a database and a spreadsheet. It allows you to add any content you want and view it in any manner that you choose from your mobile or desktop for total ease. Airtable is linkable to over 100 apps and websites, including Zapier — which we will be using. Essentially, Airtable gives you the capabilities of a database but with the visual simplicity of a spreadsheet. Due to flexibility of Airtable, this could be used to create accounting, sales, or other processes as you need them. Airtable is used for our interactive spreadsheet. The service has some great interactive spreadsheets, or “base” templates, that can be used to get started. Airtable also allows users to customize and upload their own versions of base templates. Get Airtable here.
- Gmail: Gmail is, of course, an email platform. Gmail is what will allow us to link Airtable and Wave to Zapier. Ensure that you are using the same email address for every step of the process so that all of your features properly link. We recommend using a distinct email for the process you are looking to automate, in our case the sales process, so that you never have to disable your Zap due to personal emails or end up with incorrect information in your Airtable. Get Gmail here.
- Zapier: Zapier is another open-source service that allows users to connect different applications through the use of “Zaps.” It has over 1,000 app and website integrations and allows users to connect these sites so they can be used in conjunction and information can be passed between apps. By using Zapier, we can automate many services and improve our efficiency. Zapier is the linking tool that we used. Think of Zapier as the middle-man. Through it, you link your Airtable and Gmail, as well as your Airtable and Wave. Without Zapier, information could not be shared between the different services we are utilizing. Get Zapier here.
- Wave: Wave provides financial services through its desktop and mobile application. It is a suite of products that allows businesses to do their accounting, invoicing, payroll services, and payments from one simple dashboard. Wave is a fantastic tool for small businesses as it is highly affordable, easier than coordinating with an accountant, and makes it simple to keep track of your finances. Wave’s simplicity makes it feel like you are filling out a form rather than completing invoicing services and ensures that you do not forget any essential information. Wave is the financial hub on which you will be creating the invoices that keep track of your sales. Wave has many other financial services, however for the purposes of this exercise, we will only be using its invoicing services. Get Wave here.
What Will You Make?
The following process will allow you to create two seperate Zaps through Zapier with two distinct but related functions. Note: if you are using a free Zapier account, you can only have one Zap active at a time. We recommend that you either upgrade your account or manually turn each Zap on and off depending on the work that you are doing.
Zap 1 connects Gmail and Airtable through Zapier. It allows you to send cold-call emails to potential customers without having to manually enter their data in a spreadsheet. This Zap automatically fills out an Airtable sheet dedicated to collecting, storing, and organizing the information of potential customers with data from new emails that Zapier finds in the “Sent” mailbox of the connected Gmail account. This is useful for anyone that interacts with multiple individual clients through emails. For example, a sales rep that has to contact many individual companies, with tailored emails to each one, would have a high volume of manual data entry to complete for each company that they reach out to. Our tool would be useful for them because as soon as they reach out to the company via email, information about that communication, such as the date that they first reached out, name of client, subject line of email, etc., is collected and sent to the Airtable spreadsheet that you have customized to receive and store this data.
Zap 2 connects Wave and Airtable through Zapier. It uses a different Airtable sheet than the first but the same table. Similar to a browser having multiple tabs open, Airtable can have more than one sheet on the same table. This second sheet is used to automatically collect accounting information, specifically focused on tracking sales from invoices. This tool is useful for anyone that sends electronic invoices or receipts directly to their customers for two reasons:
- Wave offers free online accounting services for e-commerce use or startups that can’t afford a fancy accountant. You can quickly create and send email invoices directly to your customer as a proof of transaction receipt or as a request for payment invoice.
- When you send an invoice and receive payment, collecting that sales information also requires manual data entry. This is how Zap 1 and Zap 2 are related — both aim to eliminate the manual data process for e-commerce sales transactions. This tool automatically collects data from these documents generated on Wave and uploads it to a tailored Airtable sheet.
The sheets for both Zaps are kept on the same Airtable table. It’s valuable to have all of that information in one place because it allows you to:
- Reduce data entry
- Summarize key sales metrics
- Build customer profiles by combining information collected from Zap 1 and Zap 2, such as knowing what a customer buys regularly
What Do I Do?
Step 1: Create your Airtable form
We’re going to use Airtable to create a customized spreadsheet so that we can record cold-call email information and a second sheet for sales information from customers.
- Record what information your sales calls will require from customers. What information is required will differ between companies. Some need email addresses, phone numbers, names, or a variety of other variables. Based on how your company works, determine what fields you will need to create.
2. On Airtable, create your columns based on the required information that you identified in the previous step. To do this, click the plus sign on the top row in your Airtable to add a field. This will create a column. From there, click the drop down menu on any of your columns to pull up the customization options. Press “Customize field type” in order to set what type of information will go in that column, such as a phone number or a date. Repeat these steps for each column that you anticipate needing. As you can see, we created a sales spreadsheet with columns including “Name”, “Email”, and “Initial Contact”.
3. Repeat this process for both of the required Airtable sheets. You will need one for Zap 1, the cold-call process recording potential customer information, and one for Zap 2, the invoice tracking sheet.
Step 2: Connect Gmail to Airtable through Zapier
We’re going to use Zapier to connect our customized Airtable form to our Gmail account in order to complete the process for Zap 1. This will allow Zapier to scrape emails in our Gmail Sent emails mailbox for the information that will fill in our Airtable form. Our goal is to reduce the abundant data entry that burdens e-commerce teams, online sales reps, and any outbound email marketing to a large audience. This Zap will detect when a new email is sent and automatically populate fields of our Airtable spreadsheets from above. If you’re confused about how to make Zaps, this is a great resource.
- Create a trigger step. Our trigger step will allow Zapier to detect when a new email is sent from our sales account. It’s best to use a separate email account specifically for sales, otherwise the Zap will need to be disabled whenever you are sending non sales-related emails. We have set our Zap to refresh every 15 minutes. First, choose a trigger app. In this case, the trigger app will be Gmail. Next, set “New Email” as your trigger. This will cause your Zap to flag any new email in your Sent outbox. Add your Gmail account and select the Sent mailbox to be monitored. Run a test, and your Zap is ready to use.
2. Create an action step. The action step is going to take our emails and filter and scrape the information in order to fill out our Airtable. First, choose an action app. We will be choosing Airtable here as this is where the table we want filled is located. Select an action, in this case “Create Record.” What this will do is create and fill a new row in your spreadsheet for every email that Zapier detects.
3. The result will be that every time Zapier detects a new email in your Gmail Sent mailbox, in this case these emails would be your cold-calls for new customers, it will scrape those emails for data and automatically fill your Airtable sheet.
Step 3: Connect Wave and Airtable
For Zap 2, we’re going to use Wave for invoicing as we are demonstrating the process for a sales organization. Using Wave, you can create invoices directly from the platform. From there, invoices can be emailed using your linked Gmail account. Our step here is to create and send invoices from Wave using Gmail. Once Zapier detects these new sent emails, it will scrape the emails and feed the information into our customized Airtable. This completes the loop by automating our data-entry process for our sales information. Ensure that you are using the same email for Wave as you are using for the trigger step in the Gmail to Airtable connection step.
- Log on to Wave and create an invoice. The invoice page can be reached from the toolbar along the left side of the screen. Invoices is found when you click on the sales icon, and then click on “Invoices.” Click on “Create an Invoice” in the top right corner.
2. Fill the invoice. To start with, add a customer. While Wave only requires filling in the customer name on the invoice, we recommend that you also include their email address so there is an account to send the invoice to. Next, add the item that you are sending them the invoice for. Whether they are purchasing a product or a service, describe what the service is and how much it costs.
3. Send the invoice. The next step is to send the customer the invoice through the email we connected to Zapier earlier. By doing this, Zapier will automatically detect the invoice as a new item in your “Sent” mailbox and scrape the data to add to your Airtable.
Step 4: Connect Wave to Airtable through Zapier
- Create a trigger step. Choose Wave as your trigger app and select an invoice as a sample test. Select “New Invoice” as your trigger. This means that when you create a new invoice in Wave, Zapier will recognize it. Fill in your Gmail in the next step. Once again, ensure that you are using a consistent email throughout. If you’re still confused about Zapier, here is another good article explaining the basics.
The yellow arrows were added onto the following screenshot to indicate that this textbox is filled with information from the invoice you created. As you can see, this is the same invoice that we created in our Wave section.
2. Create an action step. Airtable is your action app in this case. Select “Create Record” for this step so that every invoice will lead to the creation of a new row in Airtable. In Airtable, create a new sheet for sales tracking. Make sure to fill the fields on this page with the options underneath the Wave icon from the trigger step creation.
Video Tutorial
This post is meant to explain what the tools are, how to use them, and how to create the data auto-fill process for invoicing and sales. If you want a video walk through of how the tools were made on Zapier, check this out.
