We give away free stickers to anyone who asks at the rate donations and merch sales permit. We add requests to a list and fulfill First in, first out. Since we get way more requests than donations, our list is growing. We think that marketing to this list will help us generate more donations/sales and in so doing, accelerate our distribution. So we're trying to automate a monthly newsletter that gives everyone who's not received a sticker an update on where they are in line. "There are 4053 people in front of you" <- that number would change every time we send stickers/newsletters.
Initial thoughts to do this are $place_in_line = $total_requests - $total_sent
The question is how do we get those values out of HubSpot?
Initial thoughts:
$total_requests is the number of contacts in the db
Each contact has a property "status" set to "Sent" when stickers are sent.
so count of contacts with status property='Sent" = $total sent
But how do I get those numbers and where would I store them in Hubspot when generating newsletters (which we'll send through SendinBlue)
I'd love to be able to have line position number as a field in each record so that I could just send out monthly newsletters with that field as a mail merge variable rather than having to calculate it all the time.
Brain hurts from trying to solve this. Feeling stumped and hoping Hubspot geeks can help a dogooder out.
Thanks for the offer! I'd be happy to touch base with you from my personal email (I shouldn't really use my HubSpot email for non-business related purposes). One other details that's related to this issue; according to the following Ideas Forum post, calculated properties are currently in planning:
While I can't say for sure when that functionality will be ready, it's very possible that it'll help out with the use case you're describing! I'd recommend throwing some support behind that idea, and keeping your eyes/ears peeled for this functionality going forward.
That's an interesting question; I think the solution will depend heavily on your current implementation. Does $total_requests correspond to the total number of contacts in your HubSpot portal? Or some external DB? If the former, then it's not necessarily equal to the total number of requests received, since contacts might be created in HubSpot by other processes.
The first step I would recommend is to create an active lists of contacts who submitted a request but have not been sent a sticker. This list would contain all of the contacts that are awaiting stickers, and the size of this list would be the number you're looking for ($total_requests - $total_sent)
That solves a portion of the problem, but actually ordering those contacts is a bit trickier. If there are 100 people waiting on stickers, you can't just send them an email saying "There are 100 people in front of you" because that's only true for the last person. You'd need to assign each contact a 'placement' and populate a contact property with that placement for each contact in the list (and theoretically, update that contact property each time a sticker is sent to a new person).
As you can see, this is a fairly complicated problem without a straightforward solution. There's definitely no out-of-the-box HubSpot functionality to do this. I'll include some general information on some relevant APIs below, to help with the brainstorming process. I'll also open this up to other folks in the community who might have dealt with similar issues; any ideas?
Thank you so much for some of your brain, Derek! This has been a vexing problem. It seems so simple! And yet....
TBH, We don't have anything really set up in Hubspot yet. We've been collecting requests in a Google Sheet (which can easily handle the "place in line" problem, but not the "send them an email" problem. We were thinking SendinBlue and Hubspot might be a solution if we can figure out this dynamic variable business. in the meantime, we're doing it all "by hand" - emails and everything. Our hypothesis is that automating this process and being able to market to our list will accelerate sticker distribution.
.png "HubSpot Alumni"){kind=icon}
