Google Sheets: Outreach with Email & Social Media
Previously, we talked about how web scraping works and how to use it effectively for gathering and organizing data. In this section, we will explain how to automate a complete Twitter and email followup system with the help of Google Sheets. We will begin by setting up an outreach tool for Twitter which automatically sends followup emails and tweets while keeping track of the lenght of your messages. With all the functions included, you will surely add some new tools to your repertoire which may come in handy in the future.
How to Set Up an Outreach System with Email and Social Media?
People commonly include their social network accounts in their CRM. In our previous articles about Forms and CRM, we showed you how to collect Twitter handles through data entry. Assuming that every respondent gives a correct handle of theirs (and of course that each of them is a Twitter user), you can set up an outreach tool for Twitter besides your CRM. The tool will be capable of the following:
- It will automatically address Tweets to the @TwitterHandle of every contact.
- A personal sales contact’s reference will be also included
- It can inform you when the customer last signed up
- Sending a Tweet to the desired user becomes as easy as 2 clicks
- The prospect receives an email if 3 days passed after your call
- First, import data to a Social Media sheet
You only need a certain set of data from your CRM sheet, not all of them. Let’s suppose we have a few named ranges to work with: name (customer’s name), date (date added), assigned (one of your guys who contacts), and twitter (user’s handle).
Each range needs to be targeted using a certain formula. Use ARRAYFORMULA() for this case to quickly pull the specific ranges. By creating a sheet named Social Media, you can start filling it with ranges from the other sheet:
Name: To import the Name range, type =ARRAYFORMULA(IF(twitter<>””,rangename,””)), where ARRAYFORMULA(IF(twitter<>”” searches for the certain Twitter handle inside your CRM, applying it to future rows. The ,rangename,””)) part gives the name of the contact, assuming the statement is true, otherwise it generates only an empty space.
Twitter: This is going to be a short one: =ARRAYFORMULA(twitter), which simply returns the range named “twitter”.
Assigned: Quite similar to the column labeled “Name”, just insert =ARRAYFORMULA(IF(twitter<>””,assigned,””)).
That’s pretty much all you need to know about importing columns.
- Combining CRM Data and Dynamic Tweets
Once you got imported the standard data from your CRM, you need to use that data as a part of a Tweet. Create a row for your Tweet where you break it into sections such as the imported name of the customer, the Twitter handle, a short punctuation and the team member who is assigned to the customer. The row should follow this pattern: (twitter) > Greeting Text > (customer name) > Core of the Text > (assigned to) > Closing Text.
There are many ways to structure its content; you can also add creative phrasings with hashtags, links and more. The next step is to combine these parts and put it into a single cell. Let’s create another column for this purpose and call it the column labeled “Tweet Copy”. Here is the formula that combines them: =ARRAYFORMULA(IF(twitter<>””,twitter…&name&…&assigned&…,””)), where =ARRAYFORMULA(IF(twitter<>”” searches for the customer’s Twitter handle, also applying the formula to future rows. The twitter…&name&…&assigned&… part joints together the Tweet by the chunks you created separately with each CRM data point with the & sign.
Next, choose between these two options:
- Open one of your Twitter clients, copy and paste the text and send Tweets manually, one at a time.
- Choose the easier route and use Google Sheets for sending your Tweets.
The latter option needs some explanation.
- Create a “Tweet This” Option for Tweeting
Your CRM is the center for managing the information of your customers, so if you want to interact with customers directly from your spreadsheet, all you need is an URL for Twitter which we call an “Intent” URL. This way, you can pre-fill a Tweet just by entering your text while typing in the URL. This is how it works: https://twitter.com/intent/tweet?text= + write your text after the equal sign.
This URL can be used anywhere to send tweets in a few seconds, and you don’t even need to jump to Twitter. By clicking the link in your browser, a pop-up window will appear, or it opens a tab where you can instantly show the Tweet to your audience. Try using this method to putting links into your blog pages that can be tweeted. People will see your “Tweet This” posts and it will help you in driving up your shares / engagement.
The next step is to insert your URL into another HYPERLINK() formula like this: =ARRAYFORMULA(IF(twitter<>””,HYPERLINK(“https://twitter.com/intent/tweet?text=”&F2:F,”Tweet It Now!”),””))
– =ARRAYFORMULA(IF(twitter<>”” – Like earlier, this one searches fort the particular customer’s Twitter account in your CRM
– HYPERLINK(“https://twitter.com/intent/tweet?text=”&F2:F – This part adds the dynamic Tweets from your Social Media sheet in the specified column (F), extending your Intent URL for Twitter.
– “Tweet It Now!”),””)) – This will be the text shown inside that cell, and it returns “” in case the contact row doesn’t contain a Twitter account
The character limit is one of the most frustrating things you can face while you try to automate a Twitter task. It’s especially true if you want to set up dynamically generated Tweets. Don’t forget to keep track of the character count whenever you want to vary the content for that Tweet.
- Monitor Your Tweet Length with Character Counter
Firstly, this tool lets you know the average length of the dynamic tweets you have, so you can adjust each part to be receptive for longer customer names and other pieces of text. Secondly, it helps you in verifying the Tweet before the system automatically sends it.
You need to make your column named Tweet Copy defined as “tweets” by choosing the Define Named Range option after right-clicking on it, and make sure to highlight the whole row first. Now they can be referenced just by entering “tweets” when you use your functions. The formula that counts how many letters your dynamic Tweets have looks like this: =ARRAYFORMULA(IF(twitter<>””,LEN(tweets),””)), where the LEN() function counts the amount of letters and symbols through the named column.
It’s important to note that Twitter handles the URLs as if they have 22 characters, even if it’s much longer. You can also use the LEN() function accordingly.
- Send Your Followup Emails Automatically
Although you can remind your contacts that you called them before with a Tweet, sending your message in email instead is still the best way to initiate an in-depth conversation. With a spreadsheet, you can automatically send emails to your contacts and also schedule them as you want. This can be done by integrating Zapier into Google Sheets. For example, the Zap will work based on the following logic: When a sales representative contacts a customer, gives them 3 days to respond and then send an email.
We will set up a Zap with multiple steps that waits for a reply in Google Sheets for 3 days and then it composes and sends the email after the time has expired. For a preliminary step, you should create a new, ”Contacted” and add it into the Social Media sheet we mentioned earlier by using ARRAYFORMULA(). This time, =ARRAYFORMULA(contacted) has the role of importing the specified range from your CRM sheet and indicates if a customer is contacted or not.
The very next thing we do relies on certain changes on your Contacted column in order to trigger the Zapier automation. And keep in mind that you will need email addresses. You need to add a new column again and repeat that importing process with =ARRAYFORMULA(email) to gather the email addresses.
Now, all you need to do is to set up a Zap in Zapier and set Google Sheets to be the app that Triggers, and then pick the Updated Spreadsheet Row to be the trigger. If you are done, select the worksheet or spreadsheet that needs monitoring, which will be your CRM and Social Media sheets.
Then specify the columns you want to keep track of, so that will be the Contacted column. To choose an action and add it to your Zap, there’s the app called Delay by Zapier. Open it, and choose the Delay For option, then set it to 3 days of delay.
After that, you are ready to create your email. Simply add another step to your Zap and select either the Gmail app or Email by Zapier for sending a message automatically. Pick the desired account, and start setting up your email template. It’s all simple and straightforward because you can interact with the data in you spreadsheet all the time.
If you’re finished, give the zap a name, save it and it will be turned on. From then on, every time your team marks a client as “contacted”, Zap will recognize the change and it sends an email when the 3 days expire automatically.
Coming up next, we will take a side-step to show you some of Google Sheets’ built-in features that can be a great help to you not just in your work, but in your everyday life. After all, it can definitely save you some time if you have a few tricks up in your sleeve.