Set up intern time card Data Studio dashboard and Google Sheet

[Annie-Truong]

Overview

We need to set up a time card dashboard and Google Sheet for the interns so they can record their time spent on different activities. The goal then is to visualize the time spent on a Data Studio dashboard for tracking and time management purposes.

Action Items

• Set up TWE: IS22 Time Card (Responses) sheet
• Create a v1 Data Studio dashboard with all the data from TWE: IS22 Time Card (Responses) sheet
• Create a time card sheet for the interns to log in their time spent on every task/activity on a specific day
• Review the dashboard and time card
• Finalize the dashboard and time card
• Update relevant WIKI page (See resources)

Requirements for time card dashboard

• Contains data for each intern separately
• Can be sorted by activity

Resources/Instructions

• TWE: IS22 Time Card (Responses) sheet
• Time Card dashboard - example intern 1

[Annie-Truong]

@Rabia2219 You can review this issue and edit it.
FYI:

• I still have to add the dashboard URL under "Resources".
• I will create a new issue for v2 dashboard once I am done with this issue and created the v1 dashboard and sheet (v1 dashboard is basically the data log with all the intern time data. This is different from v2 dashboard... v2 dashboard will be visualization of the time data of each intern).

[Annie-Truong]

TO DO before Monday:

• Delete out the data pre-filled for testing in the time cards (Time Card data sheet and the individual intern time cards)
• Give access to the interns to their time cards

[Annie-Truong]

@Rabia2219 @ExperimentsInHonesty you can have a look at the sheets and dashboards I created (in Resources/Links) and shared with you.

1. In the TWE: IS22 Time Card (Responses) sheet we will record all the interns' time data (in tab Form Responses 1).
2. We will need to copy the data for each intern into the individual tabs ("Intern 1", "Intern 2" etc. in the same workbook).
3. Those tabs ("Intern 1", "Intern 2" etc) are linked to the workbook TWE: Time Card (Work Hours Calculated) - Intern #. Each intern will have their own workbook. In the tab "(Do Not Edit) - Data", they can see their time data. In the next tab "(Please Fill Out) - Hours Worked") they will calculate and enter the hours worked for each activity for each day.
4. The tab "(Please Fill Out) - Hours Worked") automatically updates the data studio dashboard Time Card dashboard - Intern #. This is the dash board that shows their time data visuals in charts.

I will be spending some time with my dad today but will be available after 3pm PST if you want to jump on a call to review and work together.

[Annie-Truong]

@Rabia2219 @bilkishgautham @bonniewolfe

1. I made some edits to the Time Sheet (Recorded):

• The "All Data (incl. makeup) form" consolidates the "makeup" time data and the "normal" time data. The consolidation process is automated - no need to edit anything in the "All Data (incl. makeup)" sheet.
• The "Processed Data (all)'" sheet entails all of the time data in the "All Data (incl. makeup)" sheet and calculates the hours worked based on the times clocked in and clocked out.
• The "Processed Data (all)" sheet automatically pulls data in the "All Data (incl. makeup)" sheet - no need to edit anything in this sheet.

2. The time card dashboards for each intern:

• The data in the time card dashboards (all linked below) are automatically pulled from the "Processed Data (all)".
• I created a fix-sized list control that is sorted by week and that shows how many hours the intern worked per week.
• The stacked bar chart shows the hours worked on each activity the intern worked on per week. The intern can select the weeks to be shown in the chart by clicking on the "Week" drop down menu.
• The time data on the bottom of the dashboard encompasses the "normal" time data as well as the makeup time data.
• For the dashboards, I created "ID filters" so the time data would filter for each intern. I also created a "null" filter for some of the controls so the "null" data wouldn't show up.
• Time Card - Intern 2
• Time Card - Intern 3
• Time Card - Intern 4
• Time Card - Intern 5
• Time Card - Intern 6
• Time Card - Intern 7
• Time Card - Intern 8
• Time Card - Intern 9
• Time Card - Intern 10
• Time Card - Intern 11

What I have noticed from the data analysis of the time card dashboards:

• Interns 9 (Brian), 10 (Asher) and 11 (Mateo) have not recorded their times after 6/20... we need to check in with them. Maybe they had trouble with the response form?

[Rabia2219]

Unfortunately, this did not work.

1. The Time Card is NOT automated and does require manual manipulation at the end of every day.
2. The week formula was changed and not working as was the concat formula.
3. The Dashboards displayed differently and/or were broken.

I worked on both the time card and creating individual dashboards as per Org requirements. All the work linked in this issue is still saved with the spreadsheet depreciated folder and the dashboards have been marked as depreciated.

[epierotti3]

Feedback from IS22 Off-boarding:

• Time card provides structure in asynchronous internship format
• Looked at tracker at the end of the day to get a sense of how much work had been done each day
• Helpful even though not good with it
• Sometimes would log in but not log off
• Not always accurate but still gave rough estimate of how much they are working and how time was spent
• Liked pie chart of time spent on project dashboard
• Difficult to keep track of filling it out
• Needs more visibility on interns’ end so they can if they’ve clocked in or not (possibility to edit or at least see it in spreadsheet)