How our Open Studio team hacked together a project tracker with Python and a Raspberry Pi

Yet another marketing piece was late, and what we had published was underperforming. 

In October 2018, Red Hat marketers recognized that our existing project management process, which relied on manual data entry and email updates, was unsustainable given an exploding demand for datasheets, whitepapers, e-books, and other marketing collateral.

Purchasing an existing system didn’t make sense. Our project management tool, CA Agile Central (previously known as Rally), does not allow content requesters to kick off projects through a form. 

To initiate a project, requesters had to fill out a template file. The project manager would then manually create a project and pass the template file back and forth as it was edited, designed, and published. When requesters wanted to know the status of a project, they would email their project manager, who would check CA Agile Central.

The workflow was opaque, slow, frustrating, inconsistent, and time-consuming. It also did not include any reporting about how collateral performed. As data-driven marketers, this was a problem.

To speed up the process, improve transparency, and report data, we decided to tie our systems together using open source tools and run the whole project on a Raspberry Pi.

Kicking off projects 

Here’s how we did it. We gave our requesters the ability to initiate requests directly, speeding up the process and freeing our project managers to focus on other, more valuable work. 

To start, we replaced our LibreOffice cover sheet with a Google Form, where collateral requesters upload their Google Docs drafts and fill in metadata. Moving to a system where we could respond to changes and comments in real time, without emailing a file, saved time and prevented version control errors.

Using a systemd timer, or scheduled task, we check for new form responses every 10 minutes using a Python script on the Raspberry Pi running Fedora Server edition.

We then load the content and metadata from the form into a new CA Agile Central feature using gspread, a Python API to read Google Sheets, and pyral, a REST toolkit to interact with CA Agile Central. The script creates subtasks, or user stories, depending on the content requested through the Google Form.

We use Cockpit, a web console for system administration, to manage the Raspberry Pi and aid with troubleshooting. The logger Python library lets us send an email when an error is detected. 

Automatically reporting progress

Requests are prioritized during a weekly meeting and go through editing, stakeholder review, design, proofing, and publishing subtasks. 

Requesters are notified automatically as the piece progresses, and they can check the status themselves at any time. The automated emails let the requesters know when and how to review and approve edits and the location of their published piece on redhat.com.

We send automated emails as the CA Agile Central status changes using Python’s smtplib and write the status to a stakeholder view Google Sheet using gspread.

As a result of the automation, we’ve reduced the time it takes to publish content by more than 70%. 

Data transparency

After a piece is published, we send automated performance reports at three and six months using a mix of Adobe Analytics and Salesforce data. 

Adobe supplies web traffic data, and Salesforce tracks the marketing and sales cycle from first contact through a closed deal. Blending these two datasets helps Red Hat marketers understand what’s successful.

We use Pandas, a Python data analysis library, to sort through the data and create HTML tables of promotion tactics and performance, pyral to read the original promotion plan in CA Agile Central, and smtplib to send the emails.

Publishing to GitHub

At the core of this project was a need to transfer data between Google Sheets and CA Agile Central. We’ve published the code at py-ral-goog on GitHub. 

The project will help you query CA Agile Central Kanban boards, create user stories, features, and tasks, and use a Google Form to automate work requests. It includes two libraries, rally_connector.py and google_connector.py, which contain code to help connect and use both tools.