Activity log of users of app

[JojoSylvia]

• Goal: I'd like to see how people interact with my app and how many people use it. The app is to be shared with tutors outside of the central company.

[trz-justin-dev]

hi @JojoSylvia !

not sure how in-depth you need activity details, BUT...

we are doing something very simple to get user metrics about app usage:

create schema if not exists app;

-- drop table if exists app.log;
create table if not exists app.log (
  id           bigserial primary key,
  user_id      text,
  user_name    text,
  user_email   text,
  app_name     text,
  app_uuid     text,
  app_version  text,
  created_at   timestamp default current_timestamp
);

-- database resource query in each app, runs on page load
insert into app.log (
  user_id,
  user_email,
  user_name,
  app_name,
  app_uuid,
  app_version
)
select
  '{{current_user.id}}' as user_id,
  '{{current_user.email}}' as user_email,
  '{{current_user.fullName}}' as user_name,
  '{{retoolContext.appName}}' as app_name,
  '{{retoolContext.appUuid}}' as app_uuid,
  '{{retoolContext.pageTag}}' as app_version

note: i have disabled prepared statements for this database resource (you can read about what that means here) so you may need to remove the ' around literal values if you're using default settings.

from there, we do some simple aggregations to compare the created_at values per user to see how long they spent on an app, which apps each user likes the most, the total visit counts for external users, etc.

let me know if any of that helps!

[MiguelOrtiz]

Nice one @trz-justin-dev.

Just linking this other topic which may be relevant and give you other ideas @JojoSylvia

Retool Analytics & Audit - supports public applications?

I was looking through Retool's documentation around Analytics, Retool's Audit, and tried reaching out to book a Demo to learn more but was unsuccessful. I am trying to understand whether or not Retool's Analytics and Audit would present data for public applications, specifically: Retool Analytics: Visits per day Time per Visit Retool Audit: Queries Run per Day Specific Query data (e.g variable inputs that were run for each query) IP Address triggering query For example, if a user were to…

[JojoSylvia]

@trz-justin-dev

create schema if not exists app;

-- drop table if exists app.log;
create table if not exists app.log (
  id           bigserial primary key,
  user_id      text,
  user_name    text,
  user_email   text,
  app_name     text,
  app_uuid     text,
  app_version  text,
  created_at   timestamp default current_timestamp

So will this work for a publically available app? Or do tutors have to login?

[trz-justin-dev]

@JojoSylvia interesting, so are you hoping to publish the app activity log to tutors? if so yeah I think you could basically build a Retool app to show this activity.

otherwise, if i understand correctly, yeah any user able to access an app which implements such a INSERT INTO app.log query with {{current_user.*}} will end up in the table.

[JojoSylvia]

@trz-justin-dev not publish the app activity log to tutors. Just make it publically available - so tutors will not have to login to use the app.

How, for example, can you collect an email address if the tutors isn't logged into retool?

Sorry if this is super basic. I'm relatively new to all this.

[trz-justin-dev]

@JojoSylvia no worries! I barely passed six months using Retool stuff, so we're all new

ohhh I understand better now. let me tinker for a little bit and see what info we can get from public users. hopefully more than just IP address

[Jack_T]

Hey folks! @JojoSylvia @trz-justin-dev

Just wanted to check in and see if Jojo had gotten to a working solution with the amazing help of Justin

Or if the thread linked by Miguel was able to help understand the data that can be gathered on users via Google Analytics and Retool Analytics