Jun 7, 2017 11:53 AM
Sometimes you have an offer for known customers that would require they utilize a unique coupon code. Though this is possible through some of our integration partners, you can accomplish this in our native system, with just a few steps.
Requirements: HubSpot Marketing Pro or Enterprise, Microsoft Excel or Google Sheets
Hope you all like this trick!
Jun 8, 2017 8:52 AM
Thanks for the GIF I am totally going to use that with my teammates. I have worked with a few customers to whom I provided this solution. I wouldn't be able to disclose the specific nature of the campaign apart from the fact these unique codes were to be used to identify specific contacts if redeemed either for the customer's product/offer or an associated vendor. I'd love to tell you more but I am sure you understand.
Jun 8, 2017 9:07 AM
For approaches like this, it's a good idea to keep a running list of previously-used strings, if uniqueness across campaigns is required. While you can always add more digits or characters to a randomly-generated string to decrease the chance of duplicates, there's always a nonzero chance of a duplicate. It may be worthwhile to keep a master list, and check any new codes against it, to preserve uniqueness.
Jun 8, 2017 9:35 AM - edited Jun 8, 2017 9:36 AM
You can also trick this out a little more, if random letters are desired, in addition to numbers. Every character in a cell contains an ASCII code. There are two native Excel functions you can use to leverage this: CHAR() and CODE().
CODE([value representing a character]) will return a value from 1 to 255. =CODE("G"), for example, returns a value of 71. CHAR([number from 1 to 255]) returns the matching character from the ASCII table. =CODE(71) returns a value of G.
This chart shows you the values for all ASCII characters. Capital letters are from 65-90. Let's say those are the ones we want to create random strings for. How would we randomly assign a letter?
To solve for that, you're going to start at code 65, then use the RAND() function to create some sort of step. Since the capital letters go from 65 to a number 25 higher than that, multiply the RAND() value by 25, round it, and add it to your initial 65.
That gives us a number value. And since CHAR() decodes numbers as the ASCII equivalent, a letter gets returned:
You can create as long a string as you like, by concatenating that function ad nauseum. Here's the function to create a random string of three capital letters:
As with numbers, keep a master list of previously-used codes, to avoid duplication issues.
[Pedantic edit: I just tested this with 10,000 values in a workbook. The number of A and Z values are approximately half of all B-Y values. I'm sure that's just due to the hastiness of this solution, and I haven't properly accounted for rounding. The TRUNC() function might work better than ROUND() to deal with the RAND()*25 value.]
Aug 4, 2017 11:58 AM
I LOVE this solution.
Right now, we're using multiple programs to process a transaction, and our CRM from program to program don't have the same information, which is not the greatest... We use HubSpot, Bling, Recurly, and Intercom. Do any of those integrate with HubSpot? After some preliminary research, I can't seem to find anything on the above apps.