Goals and Projects data available on Atlassian Analytics

[#1]

Hi, Community!

Goals and Atlassian Projects data can now be queried in the Analytics app. This data gives you visibility into organizational-level work progress by connecting the dots among goals, contributing work items, teams, and contributors.

The new data is organized into 3 new schema categories:

Goals - includes goal metadata, updates, comments, team mappings, and watcher mappings

Projects - includes Atlassian project metadata, updates, comments, milestones, related projects, member mappings, team mappings, and watcher mappings

Relationships across apps - the tables in this category hold relationship data between Atlassian apps. For example, the “Goal Jira work item mapping” table can be used if you’re trying to see which Jira work items are associated with a particular goal. Note that these tables will only appear when you toggle on Show full schema in the schema browser.

Refer to our support documentation for more details about the new tables:

• Schema for Goals

• Schema for Projects

We’ll gradually roll out the new data over the next 2 weeks.

🎥 Check out our video example

This new data gives you the flexibility to see goal statuses in one view or zoom into the goals that need more attention, down to the work item level.

Take a look at our video that shows what kind of insights you can pull from these new tables:

How to query these tables

Goals and Projects data connect to other data from Atlassian apps currently available in the Atlassian Data Lake. Foreign keys and mapping tables connect the data from these apps together.

Here are some examples of what tables you can use to blend Goals, Projects, and Jira data together, depending on which Atlassian apps your organization uses:

Atlassian projects or Jira work items that contribute to goals

• Use the “Goal Atlassian project mapping” table to get which Atlassian projects contribute to each goal.

• Use the “Goal Jira work item mapping” table to get which Jira work items contribute to each goal.

Jira work items that contribute to Atlassian projects

• Use the “Atlassian project Jira work item mapping” table to get which Jira work items contribute to each Atlassian project.

Edit the join path as needed

Since the Goals and Projects tables can be joined with many tables, you’ll want to pay extra attention to the generated join path for visual mode queries, especially when joining with data for Jira work items. You can manually adjust the join path as needed. For example, if you were trying to map Jira work items to goals, the generated join path would have several options for how to connect the “Work item” and “Goal” tables together. If you wanted to see which work items are contributing to goals, make sure to edit the join path so it uses the “Goal Jira work item mapping” table.

Sample queries for SQL mode

For SQL mode users, here are a couple of queries to help you get certain information regarding goals. Note that we’re actively exploring technical options to make it simpler to query this information.

Goals by current status

-- Shows goal count by current status by targeted_at quarter

SELECT 
ranked_goal.
target_at_quarter, 
ranked_goal.new_status, 
count(ranked_goal.goal_id) AS goal_count

FROM 
(
SELECT goal.goal_id,
       goal.key,
       CONCAT(DATE_FORMAT(goal.targeted_at, 'y'), '-Q', QUARTER(goal.targeted_at)) AS target_at_quarter,
       updt.new_status,
       DATE_FORMAT(updt.created_at, 'yyyy-MM-dd') AS created_at,
       DATE_FORMAT(updt.updated_at, 'yyyy-MM-dd') AS updated_at,
    row_number () over (partition by goal.goal_id order by coalesce(updt.updated_at, updt.created_at) DESC) AS rank
FROM goal goal
INNER JOIN goal_update updt ON updt.goal_id = goal.goal_id
INNER JOIN goal_type type ON goal.type_id = type.type_id
WHERE goal.is_archived IS FALSE
  AND goal.parent_goal_id IS NULL
  AND goal.targeted_at >= TIMESTAMP('2025-07-01')
  AND goal.targeted_at < (TIMESTAMP('2026-12-31') + INTERVAL 1 DAY)
  AND type.name = 'Goal'
) ranked_goal

WHERE
ranked_goal.rank = 1
group by 1,2
order by 1
;

Hierarchical view of goals

-- Assume my organization has 3 levels of goals
-- I want to see how the 3rd level goal maps to 2nd and 1st levels
-- Change the join type or add attributes for your specific needs

WITH L1_goal AS (
SELECT
g.goal_id AS L1_goal_id,
g.key AS L1_goal_key,
g.name AS L1_goal_name,
DATE_FORMAT(g.started_at, 'yyyy-MM-dd') AS Started_at,
DATE_FORMAT(g.targeted_at, 'yyyy-MM-dd') AS Targeted_at,
accnt.name AS L1_owner_name
FROM goal AS g
INNER JOIN goal_type AS type ON g.type_id = type.type_id
INNER JOIN account AS accnt ON g.owner_id = accnt.account_id
WHERE g.parent_goal_id IS NULL
AND type.name = 'Goal'
),

All_child_goal AS (
SELECT
g.goal_id,
g.key,
g.name AS child_goal_name,
g.parent_goal_id,
DATE_FORMAT(g.started_at, 'yyyy-MM-dd') AS Started_at,
DATE_FORMAT(g.targeted_at, 'yyyy-MM-dd') AS Targeted_at,
accnt.name AS child_owner_name
FROM goal AS g
INNER JOIN goal_type AS type ON g.type_id = type.type_id
INNER JOIN account AS accnt ON g.owner_id = accnt.account_id
WHERE g.parent_goal_id IS NOT NULL
AND type.name = 'Goal'
),

L2_goal AS (
SELECT
child.goal_id AS L2_goal_id,
child.key AS L2_goal_key,
child.child_goal_name AS L2_goal_name,
child.parent_goal_id AS L2_parent_goal_id,
DATE_FORMAT(child.started_at, 'yyyy-MM-dd') AS Started_at,
DATE_FORMAT(child.targeted_at, 'yyyy-MM-dd') AS Targeted_at,
child.child_owner_name AS L2_owner_name
FROM All_child_goal child
JOIN L1_goal L1 ON child.parent_goal_id = L1.L1_goal_id
),

L3_goal AS (
SELECT
child.goal_id AS L3_goal_id,
child.key AS L3_goal_key,
child.child_goal_name AS L3_goal_name,
child.parent_goal_id AS L3_parent_goal_id,
DATE_FORMAT(child.started_at, 'yyyy-MM-dd') AS Started_at,
DATE_FORMAT(child.targeted_at, 'yyyy-MM-dd') AS Targeted_at,
child.child_owner_name AS L3_owner_name
FROM All_child_goal child
JOIN L2_goal L2 ON child.parent_goal_id = L2.L2_goal_id
)

SELECT
L3.L3_goal_key,
L3.L3_goal_name,
L2.L2_goal_name,
L1.L1_goal_name,
L3.Started_at,
L3.Targeted_at,
L3.L3_owner_name
FROM L1_goal L1
JOIN L2_goal L2 ON L2.L2_parent_goal_id = L1.L1_goal_id
JOIN L3_goal L3 ON L3.L3_parent_goal_id = L2.L2_goal_id
;

Data that’s currently unavailable

• Private Atlassian projects - The data for private Atlassian projects won’t show up in Analytics

• Learning, Risks, and Decisions from Goals or Atlassian Projects

What’s coming next

To empower you to get even more value from your Goals and Projects data, we’re working to release the following features and data improvements:

• Simpler way to query the current status and hierarchy of goals (the 2 SQL mode examples above)

• Dashboard template

• Tags

• Custom fields

How to get the new tables

An organization admin needs to create or edit a Data Lake connection to include Goals or Projects. The relationship tables aren’t selectable in the Data Lake connection flow; they’re automatically included if at least one of the apps in the relationship. More about editing Data Lake connections.

Tell us what you think

Goals and Atlassian Projects data are both new to Analytics. We're exploring ways to improve them and we’d love to hear your feedback. Leave a comment below or contact support if you have any questions, suggestions, or concerns. Thanks!

[Josh]

Thank you for the helpful update, @Tina Ling !

Are we safe to assume this data will soon show up in Data Shares as well?

[Larry Stouder-Studenmund]

Fantastic, so great to see this! Thank you @Tina Ling and your team!

[Tina Ling]

Hi @Josh , there will be some additional work to have the data ready for Data Share. Please make sure to submit or vote for Analytics feature request.  Support team can help with that. 

[Josh]

Hi @Tina Ling . After speaking with the Analytics team around nine months ago, my understanding was that all future tables would be added to Data Shares at the same time or very shortly after they were made available in Analytics proper. I remember being specifically told that I would not have to request inclusion in Data Shares each time. Has the strategy / approach changed?