Time of first and last email per day

88 views
Skip to first unread message

Michael Conway

unread,
Jun 21, 2023, 2:23:54 PM6/21/23
to GAM for Google Workspace
I have been tasked with generating a report of the time of the first and last email sent per day for a subset of users. A poor proxy for "are you working" I guess. Any ideas short of the admin console? Thanks...

Ian Crew

unread,
Jun 21, 2023, 2:29:18 PM6/21/23
to GAM for G Suite
Warning, moderately-unhelpful reply incoming:

To show just how ineffective an “are you working” proxy it is: https://support.google.com/mail/answer/9214606

(Insert rant about pointy-haired managers trying to use technology and/or “butts in seats” to avoid actually doing their job and managing people…)

On Jun 21, 2023, at 11:23 AM, Michael Conway <Michael...@nfiindustries.com> wrote:

I have been tasked with generating a report of the time of the first and last email sent per day for a subset of users. A poor proxy for "are you working" I guess. Any ideas short of the admin console? Thanks...
Please consider the environment before printing this e-mail.

This e-mail, including any attachments, contains information that may be privileged and confidential and subject to legal restrictions and penalties regarding its unauthorized disclosure or use. You are prohibited from disclosing, copying, distributing or otherwise using information in this e-mail if you are not the intended recipient. If you have received this e-mail in error, please notify me immediately by return e-mail or phone, and promptly delete this e-mail and all attachments from your system. Thank you!


--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/ac125678-de9d-4f56-89a2-54b1c46762b4n%40googlegroups.com.

Jay Lee

unread,
Jun 21, 2023, 2:48:06 PM6/21/23
to google-ap...@googlegroups.com
See I was just going to do the low tech "set an alarm to email my personal account at 7am and then at 9pm" but scheduling them out makes my next promo even easier 😁

2 minutes of my day and I'm immediately the hardest worker at the company.

Slightly more helpful: Yes, you could get this information with Gmail API. But if I were doing it I'd use the Gmail logging to BigQuery and build a dashboard off of it. Unless Ross has any ideas I can't think of an existing way to grab this exact data with GAM.

Jay


To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/2716127E-6A67-439A-A301-D7B51F792F81%40berkeley.edu.

Ross Scroggs

unread,
Jun 21, 2023, 3:26:41 PM6/21/23
to google-ap...@googlegroups.com

# Auto Jay                                                                                                                                                                                                                              

$ gam user us...@domain.com print messages query "in:sent after:2023/06/20 before:2023/06/22" headers date

Getting all Messages for us...@domain.com

Got 2 Messages for us...@domain.com...

User,threadId,id,Date

us...@domain.com,188df62c5b260789,188df62fb60e250c,"Wed, 21 Jun 2023 21:00:00 -0700"

us...@domain.com,188df601bc6cfb01,188df604736c66b3,"Wed, 21 Jun 2023 07:00:00 -0700"


# Minimalist                                                                                                                                                                                                                            

$ gam user us...@domain.com print messages query "in:sent after:2023/06/20 before:2023/06/22" headers date

Getting all Messages for us...@domain.com

Got 1 Messages for us...@domain.com...

User,threadId,id,Date

us...@domain.com,188df601bc6cfb01,188df604736c66b3,"Wed, 21 Jun 2023 12:13:28 -0700"


# Slacker                                                                                                                                                                                                                               

$ gam user us...@domain.com print messages query "in:sent after:2023/06/20 before:2023/06/22" headers date

Getting all Messages for tests...@domain.com

Got 0 Messages for us...@domain.com...

User,threadId,id


Ross



To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/CA%2BVVBp9i_a3nOJQLuVrOOTjWRnZQWfrQgEqKxS17xM9%2Bj0AUcQ%40mail.gmail.com.


--

Kim Nilsson

unread,
Jun 23, 2023, 5:01:59 AM6/23/23
to GAM for Google Workspace
Ohh, resulting list of emails aren't sorted by date & time.
I got 21 hits, but they are all in "random" order.
I also got hits from the 19th and the 21st.
Because of timezones, I guess.

Ross Scroggs

unread,
Jun 23, 2023, 12:02:24 PM6/23/23
to google-ap...@googlegroups.com
I get messages in order newest to oldest; but I'm not dealing with time zones.

Ross

--
You received this message because you are subscribed to the Google Groups "GAM for Google Workspace" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-man...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-manager/b4c79c0e-d2d0-46e7-9411-c1273d7ac369n%40googlegroups.com.


--

Kim Nilsson

unread,
Jun 24, 2023, 6:29:10 AM6/24/23
to google-ap...@googlegroups.com
You are right, Ross.

The messages were in reverse chronological order.
The timezones messed it up when looking at it.

Converted to UTC it is right.

Long dateUTC DateUTC Time
Wed, 21 Jun 2023 14:27:30 -07002023-06-2121:27:30
Wed, 21 Jun 2023 22:46:04 +02002023-06-2120:46:04
Wed, 21 Jun 2023 15:27:13 +02002023-06-2113:27:13
Wed, 21 Jun 2023 15:26:13 +02002023-06-2113:26:13
Wed, 21 Jun 2023 15:23:00 +02002023-06-2113:23:00
Wed, 21 Jun 2023 02:46:03 -07002023-06-2109:46:03
Wed, 21 Jun 2023 00:49:21 -07002023-06-2107:49:21
Tue, 20 Jun 2023 23:52:21 -07002023-06-2106:52:21
Wed, 21 Jun 2023 05:34:56 +02002023-06-2103:34:56
Tue, 20 Jun 2023 22:31:54 +02002023-06-2020:31:54
Tue, 20 Jun 2023 22:06:49 +02002023-06-2020:06:49
Tue, 20 Jun 2023 13:03:08 -07002023-06-2020:03:08
Tue, 20 Jun 2023 15:06:11 +00002023-06-2015:06:11
Tue, 20 Jun 2023 07:24:46 -07002023-06-2014:24:46
Tue, 20 Jun 2023 13:58:44 +02002023-06-2011:58:44
Tue, 20 Jun 2023 03:43:49 -07002023-06-2010:43:49
Tue, 20 Jun 2023 09:44:09 +02002023-06-2007:44:09
Tue, 20 Jun 2023 00:39:04 -07002023-06-2007:39:04
Tue, 20 Jun 2023 00:35:47 -07002023-06-2007:35:47
Tue, 20 Jun 2023 00:30:21 -07002023-06-2007:30:21
Mon, 19 Jun 2023 18:23:49 -07002023-06-1901:23:49

So, is this is an instance when the timezone setting isn't fully respected when displaying the info (as it's pulling the date and the API is returning a datetime combo with TZ info)?

timezone = local

Probably nothing that can be done about that?

/Kim
--
There is No Substitute!
Reply all
Reply to author
Forward
0 new messages