Jira for Google Data Studio is a community connector for Google’s Data Studio product. It allows you to pull statistics like story lead time into Data Studio and generate reports that help your team gather insights into potential process changes that may improve delivery.
Data Studio is a free product offered by Google that allows you to build reports using a plethora of data sources. With this connector you will be able to add a data source that targets your Jira instance. The connector code runs as a Google Apps Script (similar to Google Sheets scripts) in the context of your Google account. This connector code makes requests to my proprietary API which pulls data from Jira and crunches the statistics.
The connector is not currently public; eventually my goal is to submit it to Google so that it will appear in the Data Studio Connector Gallery but in the meantime access must be requested from me directly. If you need assistance getting it set up, please contact me.
Once you have been granted access to the connector itself, you will need to add it as a data source to your report.
You will be prompted for your Jira credentials - for this you will need your Jira token. To issue a new access token for your user go to your Jira account settings, then go to
Security > API Token > Create and manage API tokens and create a new token. The token will be copy/pasted to the token field in Data Studio and your username will be your Jira e-mail prefixed with the instance ID and a forward slash. This is required because each Jira instance runs on its own subdomain and without knowing which domain is associated with your username it will be impossible to connect to the API. So, for example if you access Jira via
https://mycompany.atlassian.net/ and if your Jira e-mail is
firstname.lastname@example.org then your username for the purposes of the Data Studio connector will be
Once authentication has succeeded, you will be prompted to enter a JQL query and select the type of report you’re interested in. The JQL query is essentially the filter for which issues you want to calculate statistics for. At its simplest, this could be
project = XYZ but you can play with the “issues” view in Jira to create more customized filters to match your needs. For example, you may want to ignore sub-tasks and epics (
project = XYZ AND issuetype != Sub-task AND type not in ("Epic")). For more info regarding JQL check out Atlassian’s documentation on the subject.
For the “report type” select whichever one you are interested in. If you want both, which is likely, you’ll simply need to add a second data source using the same JQL query but the opposite report type.
In Data Studio there is a concept of “dimensions” (ways to categorize data points) and “metrics” (which are statistics that can be applied in aggregate to groups of data points). For values like lead time (a metric) the corresponding smallest dimension is the issue key (e.g. XYZ-123). However, for values like the number of issues in WIP or the cumulative finished issues there is no corresponding issue key - the smallest dimension (really pair of dimensions) for that value is the day which you are looking at and the issue type. For this reason, calculating WIP/finished issues/throughput is impossible to do without also separating the concept of lead times.
Lead times are calculated as the number of seconds since the time the issue first transitioned from the “To Do” state until the exact second that it last time it transitioned into the “Done” state. Anything that happened in between, including multiple moves to/from the “To Do” and “Done” states will be ignored. Issues that are not completed are completely ignored - only issues that are in “Done” will be counted. Weekends are ignored for the purposes of lead time calculations - if the period between when an issue was started and finished crosses a weekend, or multiple weekends, the entire 48 hour period of Saturday and Sunday is subtracted from the lead time.
WIP is calculated as of 23:59 (in your local time, as per the Jira timezone setting) of every day. That is, if you started an issue at 9AM and finished it at 5PM it will not show up as WIP because it was not in WIP at 23:59. This way WIP is only calculated for work that is carrying over from one day to the next rather than during at any point in the day when the figure will probably vary a lot.
CASEstatements to group your values into buckets.
I’ve tried to proactively address some security concerns you may have.
This additional hop is required in order to greatly speed up the rate at which records are tabulated and returned. Google App Scripts (the connector code itself) runs on limited processing power and does not have any way to store state (e.g. cache results). Suppose you have 2000 issues in Jira returned by your JQL query - this would require 20 Jira API requests (Jira paginates results by 100 issues) for every single chart or table in your report (Data Studio issues a new data request for each control). Running a report that has 10 charts would not only end up flooding Jira’s API with 200 requests every time but also it would be so slow that the report would be unusable.
Credentials are stored as properties on your own Google account - this is the only place they are persisted. When a request is made to the connector’s API (the intermediary layer between the connector and Jira itself) the credentials are passed along in an HTTPS request but not stored anywhere. When the results from Jira are tallied and processed, the results (which contain only things like the issue key, the lead time, and start/finish date - nothing sensitive) are persisted in a cache that gets cleared every day. The cache uses an SHA256 hash of your credentials and some other meta data as the key so finding the appropriate cache item for your request does not require persisting your credentials in any direct way.
Only to do issue searches and confirm if the credentials are valid. No data will ever be modified in your Jira board.