Message Sender Sequence

1. Find New Connections

This workflow begins by importing the Session Cookie Rotation Sheet and using an Insert Column to add Common with "X" as the default value. It then uses a Row Filter to remove any rows that don't have a Username and a Network Booster Sheet. Then we will use two API Enrichment steps to get the Network Booster Sheet CSV and Contacts Extractor CSV. Then we use a List Contains step to find people who were on both lists.

Any contacts who are on both the Network Booster and Contacts Extractor have 1. been sent an automated connection request 2. accepted the connection request. They need to have accepted the connection request for us to send them a follow up message. By checking the Contacts Extractor table for contacts that are also in the Network Booster table, we filter out new connections that we connected with in some other way other than automation. This ensures people who we went to college with, are colleagues with, or who have sent us connection requests won't get a sales message after connecting because they weren't sent a request by the Network Booster.

We use an Approximate Match Join in the List Contains step to compare full names that match at least 90%. If table 1 has "Patrick Hoban" and table 2 has "Patrick J Hoban," then requiring a complete match would remove the contact since the "J" makes for a slight variation. The "Approximate Match" lets it recognize it as the same person, despite the middle initial. This also creates an Approximately Matched With column. We use Text Casing steps to make both tables "Title case" and then Join the tables by Approximately Matched With and Full Name.

This allows us to add the Linkedin (written) column to the main table, when that was a value not originally provided by the Network Booster Sheet CSV with the Join from the Contacts Extractor table.

We then use a Rename step to change the headers to match the Headers Standardization and use an Insert Column step to create the columns Current Step with a default value of "1," Replied, and Done with Sequence.

When then use a List Contains step to remove contacts that already exist in the Parvenu Linkedin Message Follow Up Rotation, ensuring this step only reaches out to new connections that we have not yet messaged since they accepted our connection request.

2. Follow up with Existing Contacts

This part of the workflow begins by importing the Parvenu Linkedin Message Follow Up Rotation using a Sheets Import. Then we will use the Filter for Today / Yesterday workflow to keep contacts where the Next Action Date is equal to the Timestamp Date.

Then we use a Column Filter to remove the extra columns in the table and use a De-Dupe step by Full Name.

3. Filter for Replies

We use a Stack Tables to merge the new and existing Linkedin contacts and De-Dupe by Full Name. The De-Dupe step merges duplicates in the Replied column.

Then we use an API Enrichment step to search Hubspot by Approximately Matched With.

https://api.hubapi.com/contacts/v1/search/query?q={Approximately Matched With}&hapikey={API KEY}

Then we use an Insert Column step to ensure the columns api.contacts, api.total, api.contacts.properties.lifecyclestage.value, and Common are created in the event Hubspot returns zero results.

Then we use a JSON Flattener step to expand the result of the API Enrichment from Hubspot. Then we De-Dupe again by Full Name.

Next we use Row Filters to separate contacts that exist in Hubspot from contacts that don't exist in Hubspot.

The path where contacts exists in Hubspot uses another Row Filter to remove contacts where Replied contains "X" and Done with Sequence does not contain "X." Currently, the other path has the same filters. Then we use a Stack Tables to merge the tables back together.

4. Filter by Title, Website, and Company

Next we use a Sheets Import on the "Filters" tab of the Session Cookie Rotation Sheet. We use an Insert Columns to add the Common column with a default value of "X" and do a Join by Filter ID. This takes the contact data from Table 1 and the filters from Table 2 and puts them into a single table.

Next we apply the Dynamic Filters workflow to be able to control our filters from a Google Sheet.

5. Add Existing Contacts Already in Message Sender Sheets

We will now add the third place contacts can come from - contacts that were already in queue to be sent a message.

We will use a Sheets Import to import the Message Sender Sheets and then use a Stack Tables step.

Next we will set up the Current Step. In the next steps we're going to add 1 to the Current Step to indicate step of the workflow. New connections move from step 1 to step 2 and people at step 2 will change to step 3. Since these contacts are being sent the same message, as they were already in the queue, we want to offset this using a Math step to take "{Current Step} -1." This way someone at step 2 changes to step 1 so it can later be changed back to step 2 and be cohesive with the rest of the workflow, while keeping the step the same. The result of the Math step is named 1. Then we use a Column Filter to remove the old Current Step column and a Rename step to change 1 to the new Current Step column.

6. Add Messaging

Then we Join the main table with the "Linkedin Messaging" tab of the Session Cookie Rotation Sheet. Then we De-Dupe by Full Name and use Stack Tables with the table that contains new connections and follow ups.

Next we use an If/Else step to assign a Message based on whether they're in A/B. For example, i

If Current Step is equal to "1" AND A/B is equal to "A," then create a column called Message and set the default value as {Message2a}. If instead the A/B were "B," then it would set the value of Message to {Message2b}

Next we will use the Dynamic Text Replacements workflow to add First Name and Company.

Then we will add the Step column.

Step = Message{Current Step}{A/B}

Next we will use the Maintaining the Format of One Table, While Needing the Opposite Table to be Stacked First workflow to keep the structure of Parvenu Linkedin Message Follow Up Rotation on the table.

7. Adjust Current Step

Next we will increase the Current Step of the workflow by 1 so that the next message in the sequence will be set up.

We use Row Filters to split by the Current Step down each path. Then we use a Math step to create a column called 1 with the value "{Current Step} + 1."

Then we use an Insert Date and Time step to add a column called 2 that is +3 days and a column called 4 that is today's date.

Then we use a Text Extract step to find :space: in 2 and create a new column called 3 with all text before the space.

Then we use another Text Extract step to find :space: in 4 and create a new column called 5 with all text before the space. This gives us today's date without the hour or seconds. IE 2020-11-13.

Then we use a Column Filter step to remove Message{N}, Message{N} ID, Message {N} Date, Message{N +1} Date, Next Action Date, Current Step, 2, and 4.

Then we add a Rename step to rename 1 to Current Step, 3 to Message{N +1} Date, and Message{N} Date.

After that we use an Insert Column step to add Message{N} ID and set the value as "{Filter ID}Message{Current Step}{A/B}," add Next Action Date with a value of "{Message{N +1} Date}," and Message with a value of "{Message{N}}". Then we use a Stack Tables to merge all the different Current Step paths back together.

Finally we use a Column Filter to remove Done with Sequence, use an If/Else to re-create the Done with Sequence column so that it has an "X" as a default value if the Current Step is greater than or equal to Steps in Sequence, and then De-Dupe by Full Name.

8. Send Messages to Parvenu Linkedin Message Follow Up Rotation

We will send the results of the workflow back to the Parvenu Linkedin Message Follow Up Rotation. We will use a Sheets Import step to import the Parvenu Linkedin Message Sender, Stack Tables on the main table with the imported Sheet, De-Dupe by Full Name and a Sheets Export back to the same document.

9. Send Messages to Message Senders

We will use Row Filters to split the table by Row that identifiers which sales person is sending the messages to the contact.

We previously imported the Message Sender Sheets to add existing contacts into the workflow with new messaging, so we will use a Stack Tables on the main table and each of the Message Sender Sheets.

We will then De-Dupe by Full Name, use a Row Filter to remove contacts that don't have a Message associated with them or who have the Company "Parvenu," and then use an Add Row steps to create a row for the sales person. The Add Rows step prevents errors from occurring in Phantombuster when no contacts are in the Sheet and adding yourself prevents the error, without sending unwanted messages.

Finally we export the results to the Message Sender Sheets for each person so that Phantombuster can send them messages.