With Screenshots, Using Google Sheets, Zapier and Burst SMS – Time to setup: 30mins
In this tutorial, I am going to solve an issue a client had when they wanted to remind a customer that their boat outboard engine was due for a service exactly 1 year from the last service date. Using Google Sheets, Zapier and Burst SMS I will show you how this can be done.
What you will need for this:
Overview: Send a previous customer a text message 1 month prior to the last time they visited (had their outboard serviced) to remind them they are due for a service in 4 weeks. This setup can work for a variety of different industries including car dealerships, appointments in just about any industry with just about any date or time range.
Step 1: First you need to create a Google Sheet where the non-sensitive customer information will be stored, now most ERP’s, CRM’s etc will have a feature where you can mass export your customers data, before you begin I suggest heading over to Google to find out how you do that or if you want to start fresh read on. I have stripped down a template for Google sheets that you can download for free here. Remember you must use Google Sheets for this to work as this gives us the ability to push and pull data where we need it. I highly recommend using your own information across row 2 for testing purposes until live.
In the spreadsheet above we do have some formatting like when you double click in the Last Serviced Date column it will bring up a date picker, also the Next Service Date column uses a formula to add 335 days to the last date, now this has been stripped out of the template so that you can set your own timeframe. To add the formula simply click row K line 2 and paste the following formula = J2 + 335 (J2 will be the column/row with the original date and the + 335 is 335 days added onto the date from the J column. To add this formula to the whole column grab the bottom right-hand corner of the blue box and drag down the column.
Step 2: Now we want to head over to Zapier and create a new Zap. The trigger for your Zap is going to be Google Sheets following the prompts to connected Google Sheets to your Zapier your account so that the data can be pulled from your sheet to Zapier.
Step 3: In the next step, once you’ve connected your account, you want to select New or Updated Spreadsheet Row as the second part of your trigger, see below screenshot.
Step 4: In this step we want to select our actual spreadsheet, if you used my free template it will be named “Servicing Reminders Template Free Download” select that as the spreadsheet and then the worksheet will be called “Annual Service Reminders” as for the trigger we want this to be any_column (for advanced users you can select a specific column to only trigger the zap) otherwise read on.
Step 5: Pull in sample data from your spreadsheet, note as I mentioned back in step 1 I highly recommend you fill out row 2 with your own information first so that you can test the functionality.
Step 6: For this section, we connect our Burst SMS account to Zapier using their API key, if you don’t know how to do this refer to Step 10 in this blog post and then come back here.
Step 7: Our action for Burst SMS is to “Send SMS” as pictured below, select Send SMS and click continue.
Step 8: In the message box, you can select data from your spreadsheet by hitting the + button on top right of this box and select elements like First Name, Service date etc and prepopulate into this box which is what will be sent in the text message.
(required) click the + sign in the right corner and add the customer’s phone number that is pulled from your Google Sheets.
Step 9: In the final parts of the configuration you will need to select Format number in this case we are sending from Australia this allows the SMS provider to add the country code to the number to ensure delivery. Next is caller ID, with this you can purchase/rent your ownunique mobile number from Burst SMS or use a word as the number like MediaBooth, the word must be 11 characters or less to function properly. Finally, we select Send At this is the date from the spreadsheet titled Next Service Due but we also want to add at 9am next to this date to ensure customers don’t receive the text message at midnight when the date comes into play. Zapier can distinguish human-readable terms like tomorrow at 9am or today at midday, note for this you must have your timezone set correctly in your Zapier profile i.e. AEST time zone.
Need some help? let our team of automation experts help you by contacting