Create Project Board Looker Dashboard for Different Roles

[kimberlytanyh]

Overview

We are creating a Looker dashboard (that updates on schedule or on-demand) that displays 1) the breakdown of counts of issues by complexity type and role labels per project board columns of interest, 2) the number of issues with a certain status label (e.g. drafts, 2 weeks inactive, etc.) or unknown status by project board columns, and 3) a comprehensive anomaly report to allow developers and other roles access to real-time data to guide issue-making decisions and detect inconsistencies with regular practice.

Action Items

• [x] Research online resources and GitHub REST API on retrieving issue information from GitHub project board columns
• [x] Retrieve issues in Jupyter Notebook and verify accuracy of issue retrieval (first check)
• [x] Explore data and design datasets and dashboard to display information requested
• [x] Clean and manipulate data to create datasets
• [x] Create blank Google sheet and send data from Jupyter Notebook to Google Sheet to act as data source
• [x] Create Looker dashboard with data in Google Sheet
• [x] Confirm adjustments required in Sunday meeting (recurring)
• [x] Edit Python script to reflect adjustments and additional information requested in Sunday meeting (recurring)
• [x] Verify accuracy of data from data cleaning and manipulation (recurring)
• [x] Automate running of Python script to get updated data (using GitHub Actions in repository)
• [x] Create a Google spreadsheet with official HackforLA labels to be used for dashboard (include columns that indicate whether a label is in use, what series does the label belong to (complexity, role, feature, size, dependency, ready, ignore), and whether the label belongs to the missing series (Complexity: Missing, role missing, etc.)
• [x] Edit Python script to use Google spreadsheet from previous point to pull in official labels to future-proof dashboard and ensure link to unknown status issues filters out issues with ignore labels.
• [x] Refine Looker dashboard to include the following pages:
  • Developer dashboard with project board column and role breakdown,
  • Customer dashboard with column and role breakdown, and
  • Anomaly reports broken down into 4 pages:
    • Complexity Labeling Anomalies,
    • Issues with Missing or No Labels from complexity, role, feature, size, and dependency series,
    • Issues with Unofficial Labels, and
    • Detecting Blank Issues (more functionalities to be added by other data scientists/analysts that will take over)
• [x] Create a button where script can be run on demand (added a workflow_dispatch event trigger in the live-dashboard-automation repository) and added button with link to pull data on-demand at top right of Looker dashboard banner
• [x] Add cache dependency to GitHub Actions to reduce runtime
• [x] Create documentation on methodology in Google Slides
• [x] Add sheet protection to Looker data sources (Google Sheets) to prevent accidental changes by people accessing the sheet
• [x] Update README.md in live-dashboard-automation repository
• [x] Add the following to live-dashboard-automation repository:
  • [x] Markdown Looker troubleshooting guide
  • [x] .env template for other data analysts continuing to work on sub-issues for this dashboard
  • [x] Editable .ipynb file for other data analysts continuing to work on sub-issues for this dashboard

Resources/Instructions

• Looker dashboard being worked on
• Repository controlling automation
• Spreadsheet for team to update list of official labels needed for anomaly detection
• Using Google API to Send and Read Data to Google Sheets: Google Sheets API Automation in Python, Update Google Sheets Using A Service Account With Google Sheets API In Python, How to Analyze Data in Google Sheets With Python: A Step-By-Step Guide
• Resource for Figuring Out Cron Job Expression: https://crontab.guru/
• References for Adding Cache Dependency: Reference 1, Reference 2
• Guide on adding workflow dispatch event/button
• Slides with methodology documentation

Refer to main issue #4152

[github-actions[bot]]

Hi @kimberlytanyh.

Please don't forget to add the proper labels to this issue. Currently, the labels for the following are missing: Complexity, Role, Feature

NOTE: Please ignore the adding proper labels comment if you do not have 'write' access to this directory.

To add a label, take a look at Github's documentation here.

Also, don't forget to remove the "missing labels" afterwards. To remove a label, the process is similar to adding a label, but you select a currently added label to remove it.

After the proper labels are added, the merge team will review the issue and add a "Ready for Prioritization" label once it is ready for prioritization.

Additional Resources:

• Wiki: How to create issues
• Wiki: How to read and interpret labels

[kimberlytanyh]

Weekly Update:

Progress:

• Talked to Sophia and Chelsea on July 10, 2023 about creating a repository for automating the script for the current dashboard and they said it should be fine for me to use the same repository as the one they created for the cohort analysis dashboard. Given temporary admin access to repository by @ExperimentsInHonesty today (note for Bonnie to create team and give appropriate access to relevant website team members). Next step to edit the readme file and add in Python script.
• I have dedicated this week to taking 3 courses on improving dashboard design and am working to improve the design of the dashboard based on feedback from last week.
• Per the requests on detecting anomalies last week, relevant datasets have been created. Basic checks for accuracy have been done but more time is needed for one-to-one checks of all 622 issues. After today's meeting, will make minor adjustments to the script according to updated understanding of needs for weekly label checks.

Blockers: None Availability: Mon - Fri, 12:00-5:00PM ETA: ~21 hours

[kimberlytanyh]

Weekly Update:

Progress:

• Created Google spreadsheet with official HackforLA labels (to be used for dashboard weekly label check)

• Connected the above spreadsheet to dashboard by editing Python script

• Fixed table for missing dependencies in Looker by editing Python script and reconnecting spreadsheet to Looker

• Fixed bug in script for clarity in dataset to understand data discrepancies in anomaly for developer issues section and anomaly report

• Currently redesigning anomaly-related elements in dashboard for more intuitive usage

Blockers: None Availability: Mon - Fri, 12:00-5:00PM ETA: ~15 hours (if including optional items which require further research and trial and error)

[ExperimentsInHonesty]

@kimberlytanyh Please provide update

1. Progress: "What is the current status of your project? What have you completed and what is left to do?"
2. Blockers: "Difficulties or errors encountered."
3. Availability: "How much time will you have this week to work on this issue?"
4. ETA: "When do you expect this issue to be completed?"
5. Pictures or links* (if necessary): "Add any pictures or links that will help illustrate what you are working on."

• remember to add links to the top of the issue if they are going to be needed again.

[ExperimentsInHonesty]

~Hi @kimberlytanyh I noticed a problem~ ~https://lookerstudio.google.com/reporting/f42df1c1-99df-4fa1-b719-7e135168bfa3/page/p_f952karx9c~

~Its showing the following, even though I re-ran the workflow~

~1. fixed the issue by adding a complexity: large label~

~Issues with "Complexity: Missing" label outside of Icebox, ER, and New Issue Approval columns​~ ~column: 9 - QA (senior review)~ ~issue url: https://github.com/hackforla/website/issues/2949~ ~title: Wiki: Research Plan Review: Impact Page Research~ ~role: user research~

~and~

~2. fixed the issue by adding an ER label~

~Issues with 2+ Complexity Labels (E​xcluding: epic, ER, and prework)~ ~issue url: https://github.com/hackforla/website/issues/5647~ ~issue title: ER: Devs running WSL Ubuntu cannot run docker-compose up~

NEVERMIND, I ran it again and it cleared everything.

[kimberlytanyh]

~Hi @kimberlytanyh I noticed a problem~ ~https://lookerstudio.google.com/reporting/f42df1c1-99df-4fa1-b719-7e135168bfa3/page/p_f952karx9c~

~Its showing the following, even though I re-ran the workflow~

~1. fixed the issue by adding a complexity: large label~

~Issues with "Complexity: Missing" label outside of Icebox, ER, and New Issue Approval columns​~ ~column: 9 - QA (senior review)~ ~issue url: #2949~ ~title: Wiki: Research Plan Review: Impact Page Research~ ~role: user research~

~and~

~2. fixed the issue by adding an ER label~

~Issues with 2+ Complexity Labels (E​xcluding: epic, ER, and prework)~ ~issue url: #5647~ ~issue title: ER: Devs running WSL Ubuntu cannot run docker-compose up~

NEVERMIND, I ran it again and it cleared everything.

Hi @ExperimentsInHonesty,

I'm glad that everything is resolved. I noticed that sometimes there is a lag between changes on the GitHub interface and the results pulled by the API. The same applies to Looker. Sometimes a manual refresh via the "View" menu or reconnecting the connection between the spreadsheet and Looker is necessary to force it to update - scenarios that require these seem to be if you want Looker to refresh data earlier than 15 minutes before its last refresh (because Looker is currently set to refresh data every 15 mins, the earliest setting) or if changes are made to the structure of the dataset in the source spreadsheet (addition of new columns).

[kimberlytanyh]

Updates:

Progress: Began research on the creation of env files for main Jupyter notebook used for retrieving and cleaning data so that the data analysts who will take over will be able to edit and experiment with changes or additions made to the script before committing it to the file in the automation repository.

Other tasks:

• Finish editing created issues after uploading Jupyter notebook.
• Since I'm handing this project off soon, I'd also like to check the accuracy of my data one last time after seeing if the script can be shortened to reduce runtime (cache dependencies did not cut down as much time as I imagined).

Blockers: Might need to message Sophia as next step to understand the best approach to employing the usage of env files for handoff.

Availability: Rest of 11/8, 10AM-6PM for 11/9, and maybe 11/11.

ETA: By end of this week or next week.

[djbradleyii]

@kimberlytanyh It looks like there are only 41 ready for dev lead label issues at this time. On the dashboard it shows 100 issues. See screenshot.

Screenshot


On this page of the dashboard. https://lookerstudio.google.com/u/0/reporting/f42df1c1-99df-4fa1-b719-7e135168bfa3/page/e1JSB

[kimberlytanyh]

Hi @djbradleyii, thank you for checking the accuracy of the dashboard and alerting me of your findings. While I have yet to finish completing a comprehensive manual check of the accuracy of the datasets created by my code, I just briefly took a look into the problem you have raised and have the following findings:

1. The source of the data for my dashboard is actually the project board. As you can see from the link, there are 83 issues that have the "ready for dev lead " label from Icebox column to QA-senior review column.

2. I did a manual count and noticed that there are several issues that have more than one complexity label. Since the dashboard aggregates the counts of issues with the "ready for dev lead" label by issue complexity labels and role labels, this means that there will be double counting of issues if they have more than one complexity label. Hence, I have noted at the bottom of the table that the grand total is not accurate if there are issues with more than one complexity label or more than one role label.

However, I do find it intriguing why there is a discrepancy in the link you provided. Perhaps there are also closed issues in the project board? I will do a deeper investigation in the next few days.

Thank you so much!

[kimberlytanyh]

Weekly Update:

Progress:

• Did comprehensive check on accuracy of Looker charts and data
• 70% done with creating tutorial in Data Science CoP
• Added ignore labels to weekly label check spreadsheet to improve static link for unknown issues
• Edited sub-issues 5810, 5812, 5813 and posted to first 2 to Data Science Slack channel to see who wants to tackle the issues.
• Added .env and editable Jupyter Notebook script to live-dashboard-automation repository
• Created Looker troubleshooting guide in live-dashboard-automation repository
• Updated README.md file in live-dashboard-automation repository

Next Steps:

• Take closer look at Jason's work - check accuracy of numbers and any issues to address
• Finish tutorial
• Look into issue highlighted above

Blockers: None Availability for next week: Mon - Thurs ETA: 5-10 hours?

[kimberlytanyh]

@ExperimentsInHonesty I have made changes to the script for the dashboard to eliminate running errors and have made the script more concise. The dashboard should be usable again now. Sorry for the inconvenience and delay in rectifying the issue!