Skip to main content
All CollectionsAutomations
How to Import Prospect Click/Open/Email Sent Data into Google Sheets and Add a Follow-up Sequence for Engaged Prospects
How to Import Prospect Click/Open/Email Sent Data into Google Sheets and Add a Follow-up Sequence for Engaged Prospects

How to Automate your Sequence based on Prospect Activity

Naitik Chavda avatar
Written by Naitik Chavda
Updated this week

Hello there, Saleshandy users! πŸ‘‹
​
This guide will help you set up a Google Sheets script to automatically import click data from Saleshandy into a sheet, filter the data based on specific sequences, and then export the filtered data back to Saleshandy in the follow up sequence automatically.

Track Clicks & Auto-Follow in Saleshandy

Importance and Benefits of Automating Cold Email Follow-ups

In the world of cold emailing, timing and relevance are critical. By automatically importing click data from Saleshandy into Google Sheets and filtering this data to identify engaged prospects, you can ensure that your follow-up emails are sent to those who have shown interest in your initial email.

This increases the chances of engagement and conversion, as follow-up emails sent to engaged prospects are more likely to be opened and responded to compared to generic follow-ups.

Personalization is key to successful cold emailing. With detailed prospect data imported and filtered, you can tailor your follow-up emails to address specific actions taken by the prospect, such as clicking a particular link.

Automating this process using Google Sheets and Saleshandy’s webhook and API integration allows you to scale your outreach efforts without sacrificing quality, which is especially important for large campaigns.

As soon as a prospect clicks on a link, their action is recorded, and they are promptly added to your follow-up sequence. This immediacy is crucial in keeping your prospects engaged and moving them efficiently down the sales funnel.

By setting up an automated system to follow up with these engaged prospects, you can strike while the iron is hot, significantly increasing your chances of conversion.

This streamlined workflow reduces manual effort, minimizes the risk of missing follow-ups, and allows you to focus on crafting compelling email content and strategy.

Prerequisites

  • A Google Sheets document with two sheets named SaleshandyWebhookData and FollowupBasedonClick.

  • Saleshandy Webhook is configured to send click data to your Google Sheets script URL

  • Saleshandy API key for exporting prospects.

Step 1: Create a Google Sheets Document

  1. Create a new Google Sheets document.

  2. Rename the first sheet to SaleshandyWebhookData

  3. Rename the second sheet to FollowupBasedonClick.

  4. Download and open the sample file to see the structure of the data.

Step 2: Set Up Google Apps Script

  1. Open your Google Sheets document.

  2. Click on Extensions in the top menu, then select Apps Script.

  3. Delete any code in the script editor and replace it with the following script

Script 1: Import Click Data from Saleshandy

function doPost(e) {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SaleshandyWebhookData");

try {

if (!e || !e.postData || !e.postData.contents) {

throw new Error("No postData found");

}

var params = JSON.parse(e.postData.contents);

// Log the parsed parameters to verify data structure

Logger.log(params);

// Extract necessary data from the webhook

var event = params.event || "";

var linkClickedAt = params.linkClickedAt || "";

var clickCount = params.clickCount || "";

var link = params.link || "";

var prospect = params.prospect || {};

var sequence = params.sequence || {};

var user = params.user || {};

var rowData = [

event,

linkClickedAt,

clickCount,

link,

prospect.prospectCreatedAt || "",

prospect.firstName || "",

prospect.lastName || "",

prospect.email || "",

prospect.phoneNumber || "",

prospect.jobTitle || "",

prospect.department || "",

prospect.industry || "",

prospect.experience || "",

prospect.linkedIn || "",

prospect.twitter || "",

prospect.facebook || "",

prospect.website || "",

prospect.city || "",

prospect.state || "",

prospect.country || "",

prospect.company || "",

prospect.companyDomain || "",

prospect.companyWebsite || "",

prospect.companyIndustry || "",

prospect.companySize || "",

prospect.companyRevenue || "",

prospect.companyFoundedYear || "",

prospect.companyLinkedIn || "",

prospect.companyPhone || "",

prospect.plan_name || "",

prospect.offer || "",

prospect.opening_line || "",

prospect.latestoffer || "",

prospect.welcome_line || "",

(prospect.tag || []).join(", "), // Join tags with comma

prospect.latestStatus || "",

sequence.id || "",

sequence.sequenceName || "",

sequence.stepId || "",

sequence.stepNumber || "",

sequence.variant || "",

sequence.isSentAsNewThread || "",

sequence.emailSubject || "",

sequence.emailBody || "",

sequence.containsLink || "",

sequence.containsAttachment || "",

sequence.senderEmail || "",

sequence.latestOutcome || "",

sequence.dealValue || "",

sequence.importedAt || "",

user.firstName || "",

user.lastName || "",

user.email || "",

user.jobRole || "",

user.userRole || "",

user.shAccountId || ""

];

// Append the data to the sheet

sheet.appendRow(rowData);

return ContentService.createTextOutput(JSON.stringify({result: "success"})).setMimeType(ContentService.MimeType.JSON);

} catch (error) {

Logger.log("Error: " + error.message);

return ContentService.createTextOutput(JSON.stringify({result: "error", message: error.message})).setMimeType(ContentService.MimeType.JSON);

}

}

function setup() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SaleshandyWebhookData");

sheet.clear();

sheet.appendRow([

"event",

"linkClickedAt",

"clickCount",

"link",

"prospectCreatedAt",

"firstName",

"lastName",

"email",

"phoneNumber",

"jobTitle",

"department",

"industry",

"experience",

"linkedIn",

"twitter",

"facebook",

"website",

"city",

"state",

"country",

"company",

"companyDomain",

"companyWebsite",

"companyIndustry",

"companySize",

"companyRevenue",

"companyFoundedYear",

"companyLinkedIn",

"companyPhone",

"plan_name",

"offer",

"opening_line",

"latestoffer",

"welcome_line",

"tag",

"latestStatus",

"sequenceId",

"sequenceName",

"stepId",

"stepNumber",

"variant",

"isSentAsNewThread",

"emailSubject",

"emailBody",

"containsLink",

"containsAttachment",

"senderEmail",

"latestOutcome",

"dealValue",

"importedAt",

"userFirstName",

"userLastName",

"userEmail",

"userJobRole",

"userRole",

"shAccountId"

]);

}

Step 3: Deploy the Script as a Web App

  1. In the Apps Script editor, click on Deploy > New deployment.

  2. Select Web app.

  3. Under Description, add a description like "SaleshandyClickDataWebhook."

  4. Under Execute as, select Me.

  5. Under Who has access, select Anyone.

  6. Click Deploy.

  7. Authorise the script by clicking Review Permissions And allowing the necessary permissions.

  8. Copy the Web app URL Provided. This will be your webhook URL.

Step 4: Configure Webhook in Saleshandy

  1. Log in to your Saleshandy account.

  2. Navigate to the settings or Webhook Section.

  3. Click on Add Webhook.

  4. Enter the webhook URL you copied from the Google Apps Script deployment.

  5. Select the events you want to track (e.g., link clicks, When an email is opened). For example, we used it when an event link was clicked.

  6. Save the webhook configuration and click on Test. If Webhook is successful, it will show on the right.

Step 5: Filtering Data in Google Sheets

  1. In the FollowupBasedonClick Sheet, use the following formula to filter prospects based on a specific sequence ID:

=FILTER(SaleshandyWebhookData!A:BD, SaleshandyWebhookData!AK:AK = "Sequence ID") 

Replace SequenceID With the actual sequence ID you want to filter by.
​
To find the SequenceID, open the desired sequence in Saleshandy. The SequenceID is located in the URL of the sequence. Copy this SequenceID and paste it into the appropriate field in the formula where it is required.
​

Step 6: Automating the Export of Filtered Clicked Prospect Data Back to Saleshandy Sequence

  1. Open the Script Editor again (Extensions > Apps Script).

  2. Create a new script and paste the Script 2 from below and importProspectsToSaleshandy Function

Script 2: Export Filtered Prospects to New Saleshandy Sequence

function importProspectsToSaleshandy() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FollowupBasedonClick');

var data = sheet.getDataRange().getValues();

var headers = data[0];

var prospects = [];

// Assuming the first row is the header and actual data starts from the second row

for (var i = 1; i < data.length; i++) {

var fields = [];

for (var j = 0; j < headers.length; j++) {

var fieldId = getFieldIdFromHeader(headers[j]);

if (fieldId && data[i][j] !== "") {

fields.push({

"id": fieldId,

"value": data[i][j]

});

}

}

if (fields.length > 0) {

prospects.push({ "fields": fields });

}

}

var payload = {

"prospectList": prospects,

"stepId": "68Pv0jDbz7", // You will replace this with your actual stepId, which you can find under your sequence and they will also be automatically added to the main prospects tab

"verifyProspects": false, // If you also want the prospect to verify, write true instead of false

"conflictAction": "overwrite"

};

Logger.log("Payload: " + JSON.stringify(payload)); // Log the payload to check the structure

var options = {

"method": "post",

"contentType": "application/json",

"headers": {

"x-api-key": "91f2185726f60302a3a3ded43854f804" // Replace with your actual API key

},

"payload": JSON.stringify(payload),

"muteHttpExceptions": true

};

var response = UrlFetchApp.fetch(url, options);

Logger.log("Response: " + response.getContentText());

}

function getFieldIdFromHeader(header) {

// You have to replace the example IDs below with the actual field IDs from Saleshandy that matches with your sheets column header and you may add all the headers that you want to map with the Saleshandy prospect fields just make sure you don't add a comma for the last field. You can add more as well if you want but make sure Field Name is correct and ID you copied from Saleshandy is also correct.
​

var fieldMap = {

"firstName": "9KwOvlREz6", // you will get this field if from settings>prospect fields>system fields

"lastName": "BVaD1Aojzo", // you will get this field if from settings>prospect fields>system fields

"email": "1qPB19kGwD" // you will get this field if from settings>prospect fields>system fields. If you want more fields ids add it below with the same formatting and it will be imported as well. Make sure you don't add a comma after the last field.

};

return fieldMap[header];

}

Summary and Best Practices

Keeping Your Follow-up Sequence Active

To ensure that your follow-up sequence is effective, keep your follow-up sequence schedule open 24 hours and in active mode. This way, the follow-up emails will be sent automatically whenever a prospect is added to the sequence. Ensure your sequence parameters (such as timing and content) are correctly set to maximize engagement.

Continuous Monitoring and Optimization

Review the performance of your follow-up sequences regularly and make necessary adjustments. Monitor open, click, and response rates to identify what works best for your audience. Use this data to optimize your email content and scheduling.

Conclusion

Integrating Google Sheets with Saleshandy for automated data import and export is a powerful way to enhance your cold emailing strategy. It ensures timely, relevant, and personalized follow-ups, leading to higher engagement and conversion rates. Keeping your follow-up sequences active and continuously optimizing your approach can streamline your workflow and achieve better results in your cold email campaigns.
​
​Mission accomplished; you've successfully Automated your prospect import. πŸ‘

Wishing you a delightful experience! ❀️

Happy Selling. 🀝

Warmest regards,

Saleshandy Team
​
β€‹πŸ‘‰ Next Steps

Check out the below article If you want to know more about


πŸ’‘ Tip

Don't hesitate to reach out if you have any questions. We're available on chat and ready to provide you with prompt assistance. πŸ€—

Did this answer your question?