It seems like the first version of All Hands on Data (AHoD) just released yesterday. However, this week marks the 50th version of the newsletter. That means that we have released AHoD for 50 consecutive weeks.
When Blake and I came up with the idea of creating a newsletter, we had a lot of fears going into the endeavor. We discussed whether we could get an audience and people would care with all of the other data newsletters out in the world. We were also worried that we would not have anything to say after a few weeks. Thankfully, both of these fears ended up not coming to fruition.
After a few versions of AHoD came out, we knew that the time that it took to build the newsletter on a weekly basis was going to be unsustainable. One of the key values of Shipyard is to try and automate everything that is possible, so, I looked into how I could automate the building process of AHoD.
I thought the best article that I could submit for the 50th version of AHoD would be an article that describes the process that I go through to build AHoD every week, and a view into a way that Shipyard uses Shipyard. With that being said, let's jump in and discuss what goes into putting out an AHoD every week starting with the only non-automated portion.
Getting the Crew's Articles
There are a ton of newsletters out in the marketplace that share articles on a weekly basis. We wanted AHoD to be different based on who we are as a team. We are a mix of data practitioners and engineers from all different backgrounds. That by itself would give our newsletter an unique feel versus the other ones in the marketplace. More importantly than that, each member of our team provides a blurb on why that article is interesting to them every week. We hope that allows you to read 3 or 4 sentences and see if that article is for you, so you can maximize your time as well.
One of the hardest things to automate is personal opinions and thoughts on things. With that in mind, this is the only part of the newsletter building process that isn't automated. Each week, the team fills out a Google Form where they give information about their article and why they recommend it. The information from the Google Form is dumped into a Google Sheet.
Every Wednesday morning I head over into the Google Sheet where the submissions are held and pick an article from each crew member to go in that version of AHoD by putting the day's date in the date to publish column. The date from that column is what is used later in the code to filter the CSV that is generated from Google Sheets.
Once I have submissions selected for each member of the team, I head over into Shipyard to begin the process that automatically builds the newsletter.
Automating AHoD with Shipyard
In Shipard, we need to create a Fleet that can download the sheet from Google Sheets as a CSV. Thankfully, the native Blueprint that is built in Shipyard can take care of that task for us easily.
After we have the CSV, we need to create a Python script that will filter the table by today's date in the Date to Publish column. The filtered table will leave us with the rows that have the articles that will make AHoD that week. The script then takes the title and link of the article along with the blurb and employee name and inserts those into lists.
Those separate lists are then zipped together and put together in a f-string that resembles what newsletter looks like. You can see the full script that is run to generate AHoD below:
import pandas as pd
import os
body_content = [
'* [Title of Article 1](https://altair-viz.github.io/index.html) \\n Information on why Article 1 is good\n Recommended by: \n']
df = pd.read_csv('team_responses.csv',on_bad_lines='skip')
feature_date = os.environ.get('review_date')
today_df = df[df['Date to Publish'] == feature_date]
other_article_links = today_df['Article Link'].tolist()
other_article_titles = today_df['Title of Article'].tolist()
other_article_whys = today_df['Why is this article interesting to you?'].tolist()
other_article_featured_team_member = today_df['Who is this?'].tolist()
content_list = []
for link, title, why, team in zip(other_article_links, other_article_titles, other_article_whys, other_article_featured_team_member):
content = f"### [{title}]({link}) \n {why} - *{team}* \n \n \n"
content_list.append(content)
content_string = '\n'.join(content_list)
body_content[0] = content_string
filename = "output.txt"
#w tells python we are opening the file to write into it
outfile = open(filename, 'w')
for i in body_content:
outfile.write(i)
outfile.close()
reading = open(filename, 'r')
read_this = reading.read()
print(read_this)
reading.close()After the script runs, I copy and paste the results of our Python script into Substack. The only thing left to do is write a witty subheader for that week's version.
I hope you enjoyed our short walk through of building All Hands on Data with Shipyard. If you'd like to start a newsletter and want to automate it, feel free to jump onto our developer plan and give it a go! More importantly, we all appreciate the AHoD community that takes a look at the newsletter every week! Here's to 50 more!
