Skip to main content

Let’s say you want to send someone a coupon code when they sign up for your mailing list. You’ve generated a list of coupon codes, downloaded a CSV...but now what? How do you find out if a coupon code has been sent out before? And how do we send a brand new one every time?

Well, with a couple of Google Sheets steps, we can both get the first unused code, and mark it as used so it never comes up again!

The first thing to do is to get those coupon codes into a Google Sheet, and make sure it’s set up properly. We only need two columns: Coupon, and Used. Coupon will track the coupon code, and then Used will track whether or not it has been used before. Make sure to put “no” into the Used column, so it looks like this:

O_ppmCSlXgwqoLUPs9lftExrRUhYglu-bj8E3qRzkpzrE4-J-xBh1nTONCZwyTkxLpXch5txfV04SwtFM7TCsw3EBs9V8fCcKtVDt8gBBX6Ijt1xrz_QuJf6zzxfciF_MdBYNJed

Now that your Sheet is set up, let’s take a look at an outline for the Zap that will use these:

Step 1 Trigger: Anything you like. Maybe someone signs up for a list, or they write into a contact form - it’s up to you!

Step 2 Action: Lookup Spreadsheet Row in Google Sheets. Search for “no” in the Used column, and this will pull up the very first result. It should look like this:

 

b-fx2uirXTVvYd0BpTDOWEENBQhDvnOfIWpXAm3WbijNGFUzLH6FQ5GTY4zOhe_GVRW1fBbPcnfL5vkSR_PzKStj9joto0XSgkfT9RRkswEVCI0cDoPX-ncBPI1doosxTP90ETyK

Step 3 Action: Send Coupon Code to customer - you can use whatever system you like here as well. Just use the coupon code from the previous step.

Step 4 Action: Update Spreadsheet Row in Google Sheets. Using the Row ID from Step 2, change the “Used” column to “yes.” This will ensure that this coupon code is never used again. Here’s what that looks like:

0JAassi2cJfseYM-9ZNFgYVI0hGMkzGL98qOdqrdPFTt2YA3dMM4KmLQKoTD3LodC2QfhGT4P2Tzjctr2rOyzvRCXN0ttfqH3VSys9I_inw5j0McQ0nVGJkPc5s2mzC0KjIDJD3i

So, every time this Zap runs, it will (a) get the first unused coupon code, then (b) mark that coupon code as used. It would look like this after running 3 times:

 

MffFiFmkPNVarQvFJs8gcjkXls1FlxXxkKEogIRefTYuKeClu2dQnU2mO__ZJoY5ROLtndbDWa88D7hgfRTNZY2ApBx8KjA_9hUVxzsvDIVmXMjuilj6AflhKICEnyJoHuwL5OAI

That way, the next time this runs, it will pull the coupon from Row 5, skipping the first three! You can also use the “Update Spreadsheet Row” step to insert other information if you like, including who you sent it to, their name, the date it was issued, etc. Just add more columns to your sheet, and fill it in with information from your trigger.

And you’re all set! Hopefully this helps. :)

Hey @MarijnVerdult and @Currently Jason!

Thanks for offering your solutions as well. There are lots of ways to tackle this one 🙂 I like the Airtable one because it can use the record ID from directly in the app, but the code option is great too (especially when you have that example to go by).

FUN FACT: that link to https://zapiertutorials.wordpress.com/, Regan who created that site/tutorial, ended up getting hired at Zapier 1 week after publishing that :grinning:


Great solution! I had the same issue and we use WooCommerce. We wanted to create a referral program users could sign up to. My approach was to:

  1. Trigger by a form
  2. Create a random string using the code-step (in case you need help), I also would parse the string into an url.
  3. Create the coupon via the WooCommerce Zapier step
  4. Find and update the contact with both the string (now a coupon-code) and the url in our CRM 
  5. Trigger a sequence in the CRM to send the code and follow-up with some tips & tricks (via webhook)

Hi Everyone,

I have just had to do something similar here but I used Airtable and then used digits from the recordID of the customer record in the database 

Just used the formula RIGHT(RECORD_ID(),6) to create a 6 digit code straight from the record itself

Seems to work well.

 

 


Hi there, 

I’m working with SendinBlue and sheets..Step 3 above…..how do I ‘just use the coupon code’ from the Google Sheet and send it in a new transactional email? How to insert it? Thanks!

Best, 

Paul


Hi @Paul22! 

You would use the SendinBlue action Send Transactional Email. In the body of the email you can add information from previous steps of the Zap - in this case, you would add the coupon code from Google Sheets. 

You can learn more about building Zaps in this guide: How to Create a Zap - getting started

 

If you need any more help, you can either ask a question in the Community or contact the Zapier Support Team using the Get Help form 🙂


Would it also be possible to search horizontally on a row, instead of vertical (in a column)? @ClintEmsley @Danvers 

I’m looking for a way to automatically scan a row and replace the first cell on that row that matches my criteria. For example, in order to put a name in the first empty cell on that particular row


Hi there, 

I’m working with Mailerlite and would like this ZAP to start with a “SUBMIT FORM” option (but Mailerlite in Zapier only shows Triggered Automation or Added to a Group etc) but can work with the “triggered automation” process...BUT how do I ‘just use the coupon code’ from the Google Sheet and send it in a FIRST transactional/automated email? How to insert it? 

 

Thanks!

Best, 

Jan


What will happend if two users sign up at same time? So one coupone will be used twice ? How can we insert delay here? 


What will happend if two users sign up at same time? So one coupone will be used twice ? How can we insert delay here? 

 


Hi sir my name is Naveed you can have work you contact me


Yes I no I am hear same time 

 


Reply