Tweet random throwback post to engage your community

Running a business has many moving parts. The more you can automate, the more it frees you up to address higher level and strategic problems of your business. This is a newsletter about automating aspects of your business.

Problem

Twitter is a social media platform that favors recency. So while you might feel sheepish about posting the same link multiple times during the week, chances are most of your followers didn't catch it first time around. There's always more tweets that scroll your tweet out of sight and out of mind. If what you built is actually helpful to them, they won't mind potentially seeing it twice.

Automation can help if your marketing channel is content marketing, and you write evergreen content. Here's how you can set up a retweet of a random previous post you wrote.

Automation

We'll need two Zaps here. One to record the list of posts, and the second to select a random post. We'll use Google Sheets as our poor-man's database and record every new post to the blogging platform, Ghost. Then we'll look up a random row in our Sheets and post it to Twitter.

First, we need to create the spreadsheet holding the list of our posts. You'll want the following columns: id, status, title, url, and image.

You'll also want to create at least a single row with some data. This is important because the subsequent steps need to query for it.

The status column will always have the value `published`

Next, we need a zap that records a list of our posts. It'll add a new row every time we publish a post. Pick the ghost app, and choose the event Post Pubished

Step 2, we want to get the total number of rows. We do this by getting the most recent rows with the column "published", and reading the row number. Select a google sheet and choose Lookup Spreadsheet Row event.

You want to make sure that the status is published. This will match all the rows in the spreadsheet. But Zapier only returns one of them, because we chose `Lookup Spreadsheet Row in Google Sheets. But this is ok, because we selected True for Bottom-up, which means we'll get the very last row. The very last row's ID field is the ID of the new field we want to enter.

Step 3, Choose Google Sheets and select the Create Spreadsheet Row event.

Next, the ID column should use the ID field (which is the row number) from step 2. Status should always be published, and the other fields should be self-explanatory.

Now that we have the recording of blog posts set up, we can create our second Zap.

The second zap will generate a random number, and then use that number to look up a row to tweet. Let's fire up another Zap. Start off in Step 1 by selecting the Schedule by Zapier app with the Every Day event, and choose the time.

Next, we need to look up the spreadsheet rows, to determine the total number of rows in the sheet, so we don't choose random numbers outside our total number of posts in the sheet. This step is the same as in the first zap.

Next, we can add the Formatter by Zapier to generate a random number between 1 and the total number of rows in the spreadsheet.

After that, we can enter in a formula to generate random number generation with minimum code. The imporant thing to get right is the formula `randbetween(1, ( 2: ID: 9). What did you like about the game, and what did you dislike?

The important bit to get right is in the formula field. RANDBETWEEN(1, [row ID] - 1) This part is the magical part. Next we'll take that random number and use it as a lookup in Google Sheets.

Next, we'll Customize the Spreadsheet row. Note that the lookup value is now the randomly generated number from Step 3. So now, we've selected a random row from our Google Sheet. The rest of the fields don't matter, so they aren't shown.

Lastly, we'll create a tweet from the values of the randomly selected row. Note that I added the row number at the end of the tweet, since Twitter has a policy against duplicate content from bots.

And that's it! Whenever you make a new post, the First Zap will record the post data into a spreadsheet. And every day, the Second Zap randomly pick a post to tell your followers about.

Enjoy spreading the word!

Alternatives

As always, there are alternatives to the tools I used.

• Zapier Alternatives
• Ghost Alternatives
• Google Sheet Alternatives
• Twitter Alternatives

Photo by Huseyn Kamaladdin from Pexels