So you want to get your data out of Asana to create your own analysis in a spreadsheet or Business Intelligence tool.
Sure, you can export your data with a CSV download, but that data is static. The spreadsheet and all corresponding charts are outdated once any subsequent changes are made in Asana.
With these limitations in mind, we created Data Links to allow Asana customers to create live spreadsheets that automatically update whenever their Asana data is updated. For more context, read more about Data Links in our previous post.
In this post, we’ll walk through a template we built in Google Sheets that you can use to start building custom reports with Velocity Data Links. Each tab shows a different Google Sheets function that can be used with your own data – including Pivot Tables, charts, and more.
In addition to the step-by-step instructions below, we’ve also put together a quick video tutorial you can access here.
- Access the Google Sheets Template
First, use this link and create a copy of the Data Links template for your own use: Velocity + Google Sheets Template
- Sample Data Feed
Once your Sheet is ready to go, copy the Data Link URL and paste it into cell ‘A1’ of the Tab “Sample Data Feed.” The Data Link URL will have a format that looks like this: =IMPORTDATA(“https://www.getvelocity.co/data_links/123456789ABCDEFG):
That’s it! Your Data Link has been created and this data will be updated anytime data is changed or added in Asana.
- Creating a copy of the data to filter data
If you want to filter or manipulate the raw data feed from Velocity, we recommend copying the data to a new tab where you can more easily filter and manipulate this data. (We do not recommend manipulating the Data Link feed directly).
In the tab titled “Data to Manipulate + Filter” each column is copied over using the QUERY function. From here, you can filter to the specific data you need. We won’t get into it too much, but the QUERY function can be very powerful and you can use the QUERY function to further sort the data. (Read more here)
- Building Charts/Dashboards
Alright – here’s where the fun begins! Once you have your Asana data flowing into Google Sheets, your Asana Reporting now has the full power of Google Sheets to create visualizations, reports, and dashboards. There are dozens of charts that you can use from a simple line graph to area, column, pie charts and more.
As you can see in the “Sample Charts + Dashboard” tab, we created a few simple charts to show you what’s possible. Depending on your own data and reporting needs, you can create charts here that show progress and status of all your key projects.
Where this gets really powerful is when you create dashboards using data from other data sources (not just Asana). This could be your support, financial, or marketing software that has crucial information for your team to stay aligned.
- Using Pivot Tables
Pivot Tables are a valuable tool to “slice and dice” large data sets. Pivot Tables allow you to organize your data in a variety of ways and are most helpful when there are large amounts of data.
As you can see in the example in the template, we created a simple Pivot Table showing open/closed tasks for each assignee. In this case, we chose to use Assignee as the first level of data, but we could have chosen open/closed tasks or priority levels and then drilled down from there. This really depends on your data set and your goals.
There are some helpful resources across the internet that we’ve found, to help customers understand Pivot Tables, like this one. Pivot Tables can be very powerful when used correctly, so we encourage you to take some time and build a Pivot Tale that fits the needs of your teams.
- BONUS: Google Data Studio
For Velocity’s power users, reports and dashboards in Google Sheets are not quite powerful enough. Using Data Links, customers can create live Asana data feeds into Google Data Studio, which opens the analysis capabilities of a Business Intelligence tool.
Getting data set up in Google Data Studio is easy – just select “Add data to report” and select Google Sheets as a data source.
From there, select the Google Sheet with your Data Link. You’ll now have all your selected Asana data available in Google Data Studio.
With Data Links, you now have full control to take your Asana reporting to the next level. As always, let us know if there are any questions we can answer for you: [email protected].