How we use Google Sheets to manage email production

Generated with Speechify Studio


Hello Reforge team. A quick note: I’m playing around with an AI voice generator to make narrating this video faster!

This is how Calm uses Google Sheets to build and track emails.

I’ve created a simplified version of our sheet specifically for Reforge. In practice, Calm’s sheets have 70 or 80 rows instead of the 28 rows in this example.

At right you can see a basic newsletter. We break up each piece of the newsletter into chunks that we map to blocks of rows on the Google Sheet.

We replicate this google sheet column multiple times to represent different versions. In this example I’ve layed out six email newsletters. It could be 6 months or 6 weeks. whatever your newsletter cadence.

We also use the spreadsheets to manage newsletter translations for different languages.

Each section of the spreadsheet maps to a section of the email. Starting with the hero section on the email mockup, we have a header, a paragraph body,  and a call to action link. Each of those elements has a corresponding line on the spreadsheet.

We also have three sections that look the same. Therefore we have  three corresponding sections on the spreadsheet.

Finally, we finish with the email section on the bottom.

Now that the copy has a defined structure and pattern, we can interpret the email easily programmatically.

I connected the spreadsheet to a Google Apps Script that shows a basic preview of the newsletter to make the email easier to review and check for errors.

Notes on updating User Profile via Iterable webhooks

Iterable journeys have two webhook nodes to choose from so I was trying to figure out which node will make an outbound call to an API, receive data, and then write data back into the User Profile.

The “Journey webhook” node will not write data to the user profile.

  • This is an outbound-only HTTP request.
  • We can use the webhook to tell external certain things about the user that Iterable knows. But the webhook itself is fire-and-forget.

We are forced to use the “User Profile update” node

  • Iterable automatically stuffs a ?email=<email address> parameter on every request (security prob?)
  • This node allows for an Authorization header.
    • Screenshot shows Authorization: abcdefg
    • We can do Authorization: Bearer <token> to do basic auth instead of authorization token?

Example request header from User Profile update via webhook

GET /?email=exampleemail@foobar.com HTTP/1.1
Host: <HOST>
User-Agent: AHC/2.1
Accept: */*
Authorization: <STRING>
X-Forwarded-For: <IP ADDRESS>
X-Forwarded-Host: <HOST>
X-Forwarded-Proto: https
Accept-Encoding: gzip

A thoughtful UX Teardown on my work

Calm was the subject of a email UX teardown recently which was well-written and had several interesting insights. The obvious first reaction to this post was “oh no, we’re horrible” but the reality is she articulates several weaknesses that the team has been aware of.


We missed an opportunity for personalization

Complaint – She was a happy user churning out of the product. When she churned we hit her with a generic urgent messaging that others recognized not to be soothing. Why are we talking to her like a beginner?

What is the root of the complaint? We don’t distinguish “happy churn” from other types of churn. (Note to self: what are the other types of churn?) Our churned-user experience is certainly under-appreciated.

This user has multiple email addresses: a paid subscription email and a trial churned email. Presumably she used the trial churn email to conduct her research.

The paid subscription email address was unsubscribed from marketing emails. She received transactional emails only. This underlines that we should sneak in some more light marketing into our transactional emails.

What technology do we need to solve this? Lots of people have multiple email addresses. It’s not a technical issue. However we could benefit from having consistent time-based email cadence (use as a default) augmented by an event-based cadence when users are well engaged.

Is there a barrier to fixing it? No barriers. We have the capability to look at the user’s engagement within the last several months or weeks to separate out an engaged user from unengaged users — at all times!


Inconsistent email cadence

Complaint – she experienced an email dead zone where she didn’t see anything messaging from us for weeks (months?)

What is the root of the complaint? There was a dead zone for several weeks after churn from trial. She eventually got enrolled into a monthly email but monthly may not be enough frequency to maintain engagement.

What technology do we need to solve this? Increase production

Is there a barrier to fixing it? Emails are too difficult to specify right now.


Off-brand copywriting

Complaint – The author and several LinkedIn commenters perceived the emails to be aggressive and ironically not “calming”

What is the root of the complaint? She received several sale-oriented headlines during a large revenue-producing campaign.

What technology do we need to solve this? No new technology necessary to write copy. Slow down and work cross-functionally to capture critique from marketing & content teams to get on the same page for user-facing communication.

Is there a barrier to fixing it? We are not trained copywriters. However, we can learn to write in an on-brand voice. That is a marketable skill that can be augmented with AI.

Referring back to the personalization problem, we could come up with alternate subject lines for well-engaged users who do not need urgency-oriented messaging.

12-month advisor check

One year ago, in March 2022, a startup contacted me out of the blue to see if I could help them get their Lifecycle program off the ground. I started advising them a month later, in April 2022.

They are an East coast-based company with clusters of employees in the US, Europe and India. My Pacific time day job has almost no overlap with their schedules at all. About 8:30am-10am is the only time they’ve sent me a Slack message. Over 90% of my time has been working asynchronously early in the morning, late at night, or on weekends.

On my first week, I was shell-shocked by the amount of work they wanted to complete. Even one year later, the team continues to be laser-focused. I am thoroughly impressed with the speed and urgency at which they ship week-after-week. The pace is frenetic.

This has turned out to be a great learning opportunity for me as an marketer.

A rudimentary lifecycle automation can bring a lot of revenue.

In the chart below we can see when we launched the lifecycle marketing campaigns at the end of July 2022.

The product is a fantastic utility so it’s an easier sale than other products. I wrote a small collection of emails and we started generating enough revenue from Lifecycle each week to easily cover the cost of the CRM and my hourly consulting for 20 to 30 hours each month.

This program is heavily dependent on top-of-funnel channels

No doubt in my mind that paid acquisition is the life blood of this company. You can clearly see when the ad team was spending money and when they weren’t. Any major spike or dip is directly related to increasing spend or pulling back on spend, respectively.

I tip my hat tip anyone working on doing the ad-buying because that is tough, high stakes work. Lifecycle can close the sale but the paid acquisition team is what brings the customers in the digital doorway.

Dormant users are difficult

Email is the natural channel to re-engage dormant users. We have a giant list of 20 million people who came through to look at the product and disappeared forever.

In my experience it has been extraordinarily tough to get a dormant user to look at the product a second time purely because of a discount email. 0.15% — that’s our actual email conversion rate for dormant users who have never paid before.

We don’t need gigantic volume to make money

Originally the plan for Lifecycle was to send millions of emails per month to get as many eyeballs as possible. Problem: high-volume email services are expensive!

After some thought we budgeted our outreach to the quotas of our Customer Service platform (Intercom): 400,000 unique users per month. That’s one-tenth of the 4,000,000 uniques per month that the marketing team originally wanted to reach. Reaching out to all possible users in the database doesn’t help on revenue. The people who buy mostly are (1) users that hit the paywall or (2) past customers that we win back.

On average we reach out to about 250,000 people per month.

Big-time copy-writing failure!

I had to add a disclaimer block to some messaging so that users don’t mistakenly look for coupons on Apple or Android.

I suspect this line of text caused some of the drop.

“This discount is valid only for subscriptions purchased through [domain name]. Offer not valid for subscriptions purchased in the Apple Store or Google Play Store.”

Notes from Iterable Activate 2022 in San Francisco

I attended Activate in San Francisco held at the Marriott Marquis. It was a fine time. I gravitated toward high-level strategic talks in-person. I take a look

Most insightful talk: Women Language + Power and Standard Communication by Susannah Baldwin

When I walked into the room vaguely labeled “professional development” session I was late so I hurriedly took a seat at the side of the room. After I got settled and composed I realized I was one of two men in a room of one hundred women. Ooops! I felt way out of place but I stayed in the room and happy I’m happy I did.

The speaker’s point was that women are taught at an early age to speak in a soft or indirect manner. To reach the upper echelons of But that’s not

Many Useful takeaways : Find Your Target: Reaching Different Personality Types Without Flooding Your Marketing Channels by Tim Hemingway, Havas Media

Broadening my horizons: Presenting with Impact by Susannah Baldwin

Reforge notes: Retention & Engagement

Rough notes while I go through the Reforge Summer 2022 program on Retention & Engagement.

4 Strategies to increase ENGAGEMENT

  1. Add Use Cases
    1. Uber
      1. start with going to airport
      2. add going out with friends
      3. add commuting to work
    2. JIRA
      1. start with bug tracking
      2. add product releases
      3. add project management
    3. Calm
      1. listen to sleep stories
      2. add journaling
      3. add coaching
  2. Increase usage frequency
    1. Metric is weekly active user (all are weekly actives)
      1. 1x/week
      2. 4x/week
      3. multiple times/day?
  3. Increase feature usage
    1. Expand breadth of features does individual use.
      1. User 1: Sleep stories & music
      2. User 2: music & Daily Calm & Journal
  4. Increase intensity of usage
    1. amount of time per use? (listen to Calm music 5hrs/session)
    2. $ spent
    3. # actions per use (?)

Add Use Cases

In what ways can we expand the number of uses for subscription apps that I am trying to improve?

Calm use cases

  1. Bed time
  2. Focus during work/study
  3. Leisure during commute
  4. Consistent daily mindful practice (advanced)
  5. (expand to) measuring personal growth?
  6. (expand to) handling anxiety in-the-moment

TTS Inc. use cases

  1. Leisure reading fiction
  2. Reading textbooks
  3. Reading work documents
  4. Proof-reading, editing, review
  5. (expand to) studying, taking notes on subjects.
  6. (expand to)

How we saved $15,000/month and increase conversion 300% with Lifecycle automation.

Summary

On my nights and weekends I worked with a startup to automate their Lifecycle email program. Within 3 months of working together the company saved $15k/month and increased new trial subscription starts from 50 per week to 200 per week.

Weekly sales revenue growth before Lifecycle and after. The different color bars and coupon discounts that I tested to optimize pricing.

How did they achieve this?

  1. Prioritized revenue automation as our Q3 goal
  2. Eliminated any initiatives not directly related to the Q3 goal. (Such as brand marketing)
  3. Identified events critical during the customer lifecycle that needed to be sent to the marketing platform.
    1. new user sign up
    2. paywall abandon cart
    3. trial and paid subscription churn (user cancellations or time-based expirations)
  4. Partnered with the engineering teams to get the lifecycle events flowing into the marketing platform of choice. In this case, Intercom.
  5. Monitored and tested the data coming from engineering.
  6. Launched Lifecycle programs

Company Profile

This startup is a subscription-based text-to-speech productivity tool available on the web, iOS, Android, and as a Chrome Extension. To keep things simple, I’ll refer to this startup as TTS Inc.

TTS Inc. is a small-but-mature company that the CEO started in dorm-room in his dorm room a number of years ago. The company had mild growth and accelerated when they got into aggressive paid advertising The team is currently ~100 people, including contractors like myself.

TTS Inc. offers a freemium product. The free tier is the core text-to-speech product with a limited feature set. For example on the free tier the reading speed is capped at 150 words per minute while the Premium tier listening speed is 900 words per minute. 900 words per minute sounds crazy but it’s possible to comprehend.

Most users upgrade to the Premium tier subscription by joining a 3-day trial. The trial requires a credit card and converts to a 12-month subscription, renewable annually.

TTS Inc. reached out to me because they wanted to get their martech stack aligned to allow them to scale and automate their Lifecycle marketing campaigns through email, push and in-app messaging.

Execution

Step 1: Prioritize revenue automation

Lifecycle marketing started as a component within the TTS Inc. Brand marketing team. Brand marketing had two goals.

(1) Increase revenue with Lifecycle
(2) establish a consistent brand identity.

Having these two goals caused TTS Inc. to make some missteps that harmed them. Part of the Brand team’s plans for the future was to send a highly stylized, beautiful weekly email newsletters to the active user base. Keeping that future brand-focused state caused me personally to get distracted because I was trying to push their marketing infrastructure to support for a future that had not been realized yet. For instance, I was advocating for us to switch to an email service provider that would make sending high volumes of email easier and cheaper than their current system.

By the end of my first two months we missed our revenue goals. We were getting single-digit trial starts per day. Not good. Our lifecycle program was abject failure.

Since we missed our goal, leadership chose to eliminate the Branding team except for Lifecycle. As a regular listener of Alex Hormozi’s podcast I now understand his view in episode 382: branding is a game the big companies. During this time TTS Inc. was not at the stage where it could spend a lot of money on activities that did not have immediate measurable impact on sales – branding is one of those. Brand-related activities included surveys, newsletters, and highly stylized one-off email sales announcements were a distraction and difficult to attribute to sales.

Now my priority was clear: improve LIfecycle revenue.

Step 2: Eliminate all non-core activities

To become hyper-focused on our one goal we had to stop doing non-sales activity. We dropped doing branding which in turn allowed us eliminate other products.

WE eliminated unnecessary software

TTS Inc. had two marketing-related systems: Drip and Intercom.

TTS Inc. was spending $12-$15k per month on Drip — an email marketing platform designed for small-to-medium sized business. TTS was getting very little out of Drip because they had not dedicated serious engineering resources to get their data synchronized with their own user database.

Having two systems made things worse because engineering didn’t know which systems needed which event? If both systems needed the same events or data there would be some minimal amount of maintenance work involved with keeping things accurate on both platforms. It was an all around mess.

With branding removed from the priorities, leadership made the decision to eliminate Drip from the tech stack. Canceling the drip account saved the company $10,000-$15,000 per month.

WE Eliminated resource-heavy activities

Understanding that Intercom is not well-suited to sending a lot of email (too expensive, bad interface) we pared down email production down to just one person to do all the work with minimal concern for visual appeal.

Brand-focused email:

4 people, 1 week

  1. Mockup email (Lifecycle)
  2. Write email copy in the brand’s voice (copywriter)
  3. Design a pretty email (email designer)
  4. Build the HTML email (HTML developer)
  5. Set up the automation (Lifecycle)
  6. Launch the email (Lifecycle)

Ugly email:

1 person, 2 hours

  1. Cobble together some copy text (Lifecycle)
  2. Build HTML email – basic styling (Lifecycle)
  3. Set up the automation (Lifecycle)
  4. Launch the email (Lifecycle)

In the weeks after eliminating the “pretty” emails I have found that sales have not been noticeably harmed. In fact, email-to-trial conversion rates may have actually gone up.

Step 3: Identify critical events and data to use in the marketing platform

Automation was difficult regardless of marketing platform. Neither of TTS Inc.’s marketing platforms were integrated with the business’s data pipelines. Intercom, in particular, was missing a lot of critical account and subscription-related information. In order to get Intercom operational quickly we stripped down our requirements to a set of eight subscription-oriented Lifecycle events that could be the bare minimum backbone for direct sales messaging.

  • New user created an account
  • User saw paywall but did not buy (abandoned cart)
  • Trial started
  • Trial expired
  • Trial canceled
  • Subscription started
  • Subscription expired
  • Subscription canceled

In my experience the subscription events are difficult to get because subscription data comes from many sources: paypal, stripe, apple, Google, etc. The team responsible for managing subscription did a fantastic job in delivering subscription events to Intercom on an hourly basis.

Step 4: Partner with engineering

TTS Inc.’s leadership did an outstanding job of aligning engineering resources to prioritize Lifecycle. This was easy because the company’s engineers are naturally business-minded. Leadership communicated clearly and directly to the engineering teams that Lifecycle’s goal for the quarter was to start making money and to prioritize Lifecycle requests over other initiatives. Being business-minded folks, the engineers were also innately eager to help us get these projects off the ground.

We met with the engineers a few times to discuss our minimum needs and the engineers added in some things that would make reporting easier for other departments (such as ad-buying group, or finance). Since we agreed on the set of events ahead of time I was able to start building the user messaging journeys in Intercom in parallel with the engineering data team’s work.

There were many teams involved in this work:

  • Platform engineering did the work to synch subscription data to Intercom
  • Web engineering had to start sending new user signup events to Intercom
  • Product management team found flows that broke and needed to be migrated from Drip to Intercom

After a rapid 3-4 week development cycle engineering completed their work and we were ready to turn things on.

Step 5: Monitor and test the data

Once engineering launched their respective work we had to double check their data.

For the first few days of the launch we observed that many events were under-firing. Engineering found that in many cases the user’s identity was not correctly being established before sending the data to Intercom. This is not necessarily name & phone number identity — i mean database unique id which is usually a jumble of letters and numbers.

This is an important bug. Marketing systems don’t magically tie data to a user. Engineering has to put operations in the correct order. If you do not have the correct order events that do not have a proper user ID will go to a black hole.

Correct order

  1. user sign up
  2. wait until user id assigned by backend system
  3. establish connection to marketing platform
  4. send events

Incorrect order

  1. user sign up
  2. establish connection to marketing platform
  3. send events
  4. user id assigned by backend system

This is an engineering thing but it’s important to know. In one bug we found, the web app was sending a “new user signed up” event before the user’s ID was established on TTS Inc.’s database. So we were getting a lot of events but no user data. The web app made a small code change to wait until the new user’s id was established on the backend before sending the event data to Intercom. After that we were able to send messaging to new web users.

This is the case for many marketing platforms and if you are losing a lot of event data or getting a lot of “anonymous” or “unidentified” users in your SaaS platform this might be the issue.

In summary: order-of-operations is important. Your engineers need to know that the user’s ID needs to be established before logging any events to your marketing or analytics platforms. (Amplitude, Segment, Iterable, Braze, Intercom, Drip, etc.!)

Step 6: Launch the Lifecycle programs

This is the easy part! Once the data is live and flowing we are free to experiment using the marketing platform.

There is no secret sauce here. Other subscription apps and e-commerce websites follow a similar pattern of direct-to-consumer sales. We mapped the events directly to their own set of emails.

  • New user: send a welcome series of emails
  • Paywall: send abandon cart email
  • Trial expired/canceled: send a win-back email
  • Paid subscription expired/canceled: send win-back email

We launched the lifecycle automations and by the first full day of emails upsells were 2x the previous day. Over a 3-week period the average weekly web sales volume was three to four times the typical week.

Further refinements that we made:

  • built a customized and editable web landing page to test and measure conversions
  • offered discounted plans
  • sent in-app messages in each workflow

Conclusion

Although it was a bumpy ride at first, TTS Inc.’s marketing automation stack is well-situated for growth. Within the first week, their lifecycle sales automation performed well far beyond my expectations. Now that they have had consistent sales baseline they can begin to experiment with branding and other marketing tricks to boost conversion performance.

The MVP for a multi-million dollar product built on Google Sheets and Iterable

In April 2020, I helped launch Calm Sleep School, which was an early prototype of the coaching product that later became Calm Sleep Coaching (Sept 2020-2021) and continued to grow into what we now know as Calm Health.

In late 2019 Calm hired a Chief Medical Officer to spearhead the company’s initiative to move into the mental health realm. The Chief Medical Officer-slash-head-of-sales came to us from a mental health platform that sold as a business-to-business product for large companies to offer therapy as an employee benefit. His Big Idea was for us to create a similar product that wasn’t quite therapy-level help but would address broad problems that affect mental health (i.e. sleep, diet, physical health, stress, anxiety, etc.). Leadership settled on sleep coaching in part because our CEO admitted that he was having trouble sleeping at night so he could be the Founder AND a customer.

The idea was simple: professional sleep therapists would conduct a series of 6 one-on-one video coaching calls with individual students to identify issues that were causing poor sleep and to provide a personalized sleep plan for the clients to follow. An example sleep plan might be to stop drinking caffeine after 3pm, turn off the TV at 9pm, and go to bed consistently each night.

The design concept was beautiful (see the Launch Email). Our product designers contracted a professional illustrator to create one-of-a-kind art with everything in a deep sleepy purple. Design was top-notch.

Calm Sleep School had some serious challenges. Engineering would commit near-zero resources to building the system. Since this was only going to be prototype with unknown value, leadership would not commit significant development time because the engineering team was already stretched thinner than a spider’s web.

Engineering worked on these components:

  • One backend engineer to create the SKU and hook up the Stripe billing mechanism. I believe she accomplished this within one sprint.
  • Frontend web team built a landing page
  • Frontend web team built the checkout page for customers (students) to pay via Stripe (via the billing backend)

Lifecycle (my team) was responsible for all of the onboarding and this is where I am quite proud of our achievement because I was able to build the onboarding AND a coaching management system using Google Sheets, Google App Script, and Iterable. (My equivalent of duct tape and paper clips.)

Launch Email

Sleep School Onboarding

Before onboarding could begin people would arrive on the landing pages and checkout built by engineering.

Upon successful purchase, a Welcome email to explain the product and inform the new student the details of Sleep School.

Using Google App Script I scripted the user intake form to take several actions:

The Welcome email pointed to a user intake form (Google Form) which asked a series of questions to understand why the new student needed help sleeping.

(1) It emitted an event to Iterable so that we would have a record of user’s progress.

(2) It sent an email and logged a row to our internal teams to know a new student signed up.

The coaching team would manually match the new student to one of six Sleep Coaches. The match of sleep coach to student was logged on the sheet. With the match made we would send an email with the introduction of the coach with name, picture, and bio.

(3) The script had to copy a Google Drive folder filled with a handful of assets that coaches used for taking notes and sharing reports with the students.

Since each student needed to have a unique Sleep Journal I logged the folder, Sleep Journal Google Form, and other things into the Google Sheet as well as Iterable. By saving the Sleep Journal Google Form ID we could send an email to each student that had a link to their personal Sleep Journal.

After students’ 7 days of journaling they would have a one-on-one Zoom call with their Sleep Coach and the coaches would then carryout delivering the remainder of the Sleep School experience.

Sleep coach match

We launched Sleep School slowly by sending batches of 10,000 emails to existing Calm Subscribers so that we could pace the flow of student enrollment and not overwhelm the coaching staff. Our goal was to sign up roughly 100 students to complete the 6-week course which we were able to accomplish. Students were satisfied with the program! I don’t think we had to issue any refunds related to the quality of the course. Some folks canceled because they didn’t realize the program cost around $600 USD. In further sales development our Head of Sales had inked a deal with some corporate clients to

As I said before, this was an early prototype. Leadership was encouraged by the product so they decided to build Calm Sleep Coaching product which was staffed by 1-2 full-time engineers. As of 2022 that product further evolved into Calm Health which has a full staff of 12+ full-time engineers dedicated to bringing mental wellness as an employee benefit for thousands of companies and tens of thousands of employees!