At MarketLytics, we try to be data-driven in everything we do. From helping clients figuring out how to do marketing or whether it’s our processes internally. From how quickly we respond to emails to how much time we spend on various tasks we try to use tools to help us capture all the data.
Unsurprisingly the hard part hasn’t been how we collect data which we have oodles of, at this point rather how do we consistently collect and visualize it for meaningful insights. Here is how we started and the tools we used to get a fully automated way to query data from our project management tool.
What forced us to create the teamwork connector?
Creating a teamwork connector was definitely not our one time idea. Our main goal was to have a report where we could easily see the status of tasks and time it took to complete each task. A report which also makes it easy to view the performance of each team member at a glance.
For managing tasks and recording time, we use Teamwork. It is a project management tool which marries task management, time tracking all from a single web interface.
Here is how we started using
1) Manual Reporting within Teamwork
Teamwork basically is a task management tool where we can create tasks and record time entries for each task. This helps us track the total time each task took to complete. We also use the logged time to measure the performance of each team member and the total hours logged each month.
Below is how a report looks like in teamwork. This is pretty helpful but without having a graphical interface it seems a little boring and hard to read.
For viewing different types of reports we have to navigate to different pages. I am not blaming teamwork for having this kind of report, it looks good for granular analysis but I needed something a bit more visual.
2) CSV to BigQuery
After viewing data within Teamwork for a while, we thought of getting data out and trying to visualize it manually. Teamwork has a handy CSV feature which was able to use to get data to google sheets and then build a few charts.
Then we thought to hang on, Google just released this new and shiny Looker Studio with a sheets integration, let’s try that. And even though it was a manual weekly process the data proved very useful, so much so that we decided to use BigQuery to automate fetching data. We setup a pipeline using the Teamwork API and a custom Heroku app to fetch and transform data into BigQuery.
So far so good but this still suffered from a few issues; we had a scheduler running every night so data was not as real time as we wanted, plus occasionally the underlying data would change (a user tracked time to incorrect project or edited some details) which would cause confusion.
3) Looker Studio with Teamwork Connector in Real Time
When Looker Studio introduced data connectors to import data from third party sources, we again thought of saying goodbye to BigQuery and jump to Looker Studio. This had a few benefits, for one it allowed us to query data in realtime and see what everyone was working on even in the last couple of hours. To do so we had to create our own data connector using AppScript.
We can use it to import your teamwork time entries and create report. Even you can use our pre-built template. In case you also use teamwork, our data connector is now available in data connectors gallery and FREE :).
You can follow the instructions here to use this connector.
Also using teamwork use our template?
We made this template very easy to read and provided filters to view each team member data in a separately. Below is how the template looks:
In order to use, you will have to copy this template and add your own teamwork source via our teamwork connector.
Although, this template is very simple and not any difficult thing to explain. You can replace “YOUR AWESOME COMPANY” text to your company name.
Here is a step by step instructions to use the connector.
If you are new to Looker Studio and want to create your own custom report, you can go through our Looker Studio tutorial.
At the end of the day most cloud-based tools have very robust API to get data out but building your reporting has been very time consuming now we with looker studio and its connectors this is going to become a lot easier.
We are also exploring building connectors for other common tools that we use to get all the data goodness in one place. Let us know how you’d want to apply these super powers and reach out if you need help!