How to set recipient name and email based on Google Sheets scenarios
SOLVE
I'm looking to build a rather complex automation, and was wondering if it was possible. I have a Google Sheets worksheet with some "scenarios." It contains scenarios 1-20 under a Scenarios column.
Each scenario is determined by the availability, and category of different types of contact information (contact type, contact seniority, job title, interal/external, etc...)
There is then an Action for each scenario. So for example, if Scenario 1, then Action is to email a specific contact.
If Scenario 2, then email different contact.
Let's say I'm reading a report, and the report mentions multiple contacts, their emails, company name and other information. I will extract this information. Then, emails should be sent to different recipients, based on which type of information is available (or not) in the report.
I want to automate this. My thinking is that this can be done with custom properties and workflows, but I'm a little lost. My guess is that somehow custom properties can be matched to columns in Google Sheets.
I hope this makes sense. Basically I want to automatically set who will recieve a campaign based on pre-set parameters from a Google spreadsheet.
How to set recipient name and email based on Google Sheets scenarios
SOLVE
Setting recipient names and emails from Google Sheets can be streamlined with scripts. Using Apps Script to automate this process based on scenarios is super efficient.
So, if I understand this correctly, you import the excel sheet, then map each "scenario" in the sheet to a multiple selection dropdown property?
I think what I'm struggling to visualize is how do I match the options in the dropdown property to the different parameters in the spreadsheet?
The flow:
Report is analyed, data is extracted
Extracted data is added to the spreadsheet with automation (Zapier maybe?)
Extracted data is analyzed and matched to a scenario.
Each scenario has an action, so if the extracted data is assigned "scenario 1" the action for that scenario is to only contact the brand contact from the report, and not the media contact.
Automatically set the recipient(s) name and email in Hubspot campaign based on the scenario that was assigned.
I really hope all of this makes sense. Thanks for your help!
I recommend having the data within HubSpot, as this simplifies management and process automation. If importing the data directly isn’t possible, you can explore options like HubSpot Marketplace integrations or developing a custom integration to transfer data from your spreadsheet to HubSpot.
Once the data is in HubSpot, you can use a multiple-selection property to assign more than one value to each record based on the defined scenarios. This allows you to set up workflows that trigger automatically depending on the assigned values. For example:
Multiple-Selection Property: Each "scenario" can be mapped as an option within the property. This lets you assign multiple scenarios to a contact, deal, or other HubSpot object based on the analyzed information.
Automated Workflow: Set up a workflow that triggers based on the selected values in the property. For instance, if a contact is assigned "Scenario 1," the workflow can:
Filter the recipients to include only the brand contact from the report and exclude the media contact.
Automatically set the recipient's name and email in the HubSpot campaign.
I’d like to invite our experts to join this conversation so they can share if they’ve done something similar and how they do, it to give you some inspiration.