I would like to add some analytics to my Glide App that shows basic usage analytics by date. I have an analytics table that will have a row for each date, and the KPIs for that day.
I would like to automate this process so that instead of having to manually look it up and enter it, the data is automatically calculated and inserted into my analytics table. For purposes of this request let’s just limit it to Total # of Users - each day I would like to update the Analytics>Users column with the total number of users as of that day.
I have a ‘realtime-analytics’ table that has lookup columns to provide that data, but when I try and access that Computed column via API it is not there (other forums have enlightened me as to why)
Is there any way within Glide to do what I am looking to do, or is the best course of action to export my data on a daily basis and run the historical analytics outside of Glide?
Are you wanting to build this analytics outside of Glide?
The reason I ask is that you mention the API, which suggests that you’re trying to fetch the data from some external system.
In any case, I’m sure what you want to do is possible, as long as you have the data.
Can you explain a little more about your “realtime-analytics” table? How is that table structured?
The only reason I was thinking it would need to be external from Glide is so that I could schedule it to automatically run every 24 hours - as far as I can find, Glide doesn’t have that capability yet. So my thought was to build a schedule in Zapier which queries the data from “realtime-analytics” and posts it back into “historical-analytics”
My “realtime-analytics” table is three rollup columns, that bring back the realtime counts of our primary KPIs:
Total # of Users (Count of rows in the Users table)
Total # of Events Created (count of rows in the Events table)
Total # of Events Completed (count of rows in the Events table where ‘Complete’ = True)
I would like to take a historical snapshot of these stats every day at midnight, so that I can track their trends over time.
Currently I have another table called “historical-analytics” that has the same 3 columns, plus a DateTime field. Every day I manually go in and copy the numbers from the rollup columns in “realtime-analytics” and create a new row in “historical-analytics” where I paste the data.
I have the exact need as you for my last project. I think there are 2 ways to go here:
If you have access to Glide API’s query (on Business/Enterprise), perhaps you can query the whole tables and do aggregation to get the right numbers (of Users, Events, Events Complete - basically re-calculating them in Make/Zapier), then push those to your analytics table.
The alternative is to duplicate your app, which will keep your tables intact. Change your app to fully public, clear all tabs and add only one new, built on top of your realtime-analytics table. Add a button that will add a row to the historical-analytics table.
To fully automate the 2nd option, I use Automa. My scenario looks like this.
Oh great idea - I could pull all the data into Zapier tables, crunch the data there, and then post the analytics back to the historical-analytics table. I will try that and post the progress here!
I have never used Automa, but it looks very useful. I will also check that tool out!
Here is an alternative idea that doesn’t require any automation at all.
Add a “Date Onboarded” to your Users table. If you don’t already have a way to capture that, create a simple onboarding screen that forces first time users to click a button so you can set that date.
Add a “Created At” column to your Events table, and populate it with the current date/time when a row is added to that table
Add a “Completed On” column to your Events table, and do the same as above when an Event is completed
With those 3 columns in place, you will have everything you need to dynamically generate your analytics for any date or date range using just Queries and Rollups, and perhaps a helper table.
