I currently provide an analytics dashboard for my paid users to see the activity on their profiles
I use a pivot table to get averages for things like “time spent on profile” and “events for session” as well as providing a “top 10 profiles” legend
The problem is that the Pivot table keeps no-longer updating on me and becoming static. So I either need to find a new way to create this data in Glide or I need a pivot table expert (@Jeff_Hager perhaps you could help me AGAIN lol).
The challenge is that each profile has multiple rows associated with it (which is why the pivot table made it easy to condense them into 1 row each) and also The top ten list was super easy to create in a pivot table bc it automatically lists profiles in order of most popular.
Any advice or someone with a similar situation??
Hola!
I do something similar in one of my apps, I log my raw data in one sheet (it has almost 5k-6k rows and it’s growing daily) but in another sheet a pivot table outputs my key results and it’s the sheet connected to Glide to display tables/charts (only 150-200 rows are loaded).
In this way the APP works well and loads very quickly.
How do I update my data from my APP? I place a button on the screen to manually write any dummy value into my google spreadsheet and force a refresh from Glide. Since my client uses the App few times per day, the number of edits/updates isn’t a nightmare.
I hope it helps.
Saludos!
2 Likes
Couldn’t you just replace that pivot table with a relation and some rollups?
Is there a direct relationship between the "path end"column shown in your pivot table and a salon?
2 Likes
Hi Darren! OK how do I combine several rows into one with a relation column? I feel like I would need to do it on a new sheet as well because if there were, for example, 7 rows associated with visits to my profile showing data and all of them had relations connecting one another it would end up multiplying the data.
So I need a new sheet right?
And then, would I need to start with pulling the rowIDs of paid users so that each one would only have one row and then try to make a relation from that ID to all of the other ID’s on the original sheet? My mind is already spinning
This is the sheet I’m pulling the data from (connected to GA). It’s starting with page paths and a ton of the paths aren’t even profiles and then several of the paths are the same profiles over and over again
Hi! So you have a button that triggers the update of the pivot table?
I had mine auto refreshing daily as the original sheet updated and it was working great…and then suddenly no update! Even if I manually refresh, it still isn’t adding the newest data. It’s like it’s jammed.
That’s okay, as long as you can identify which paths belong to which profiles, which I assume you can?
Does each profile have a single path, or could they have multiple?
Let’s assume only one, and you have that path in a column in your profiles table (or you can get it there). Then all you need to do is create a multiple relation between the path name in your profiles table and the path name in that raw data sheet. Then do your rollups through that relation and you’re done.
ok so here is an example of the multiple paths associated with Row owners in Glide
As you can see, Vivid salon has a bunch of different paths to their salon via different tabs, but one RowId so it’s easy to make an association. For total user engagement this works bc Glide automatically creates a SUM. For an average of all of those timings, this table doesn’t work.
So are you saying that I should make the multi relation from the Salons sheet?
I also still have no idea how to create a legend of top ten so hopefully there’s a solution for that too.
ok i might have figured it out for average event count per session!
Yeah. So it looks like you’re already relating Extracted RowID path to RowID in your Salons table?
I’d just reverse that relation - that is, create it from your Salons table (or whatever table) and make it a multi-relation. And then do rollups through that.
Only thing is with your average session length, it will give you an “average of averages”. But I suspect that’s what your pivot table currently does anyway.
ok i got the events, and now i need to figure out how to convert the “roll up time spent” into minute:seconds display
it looks like the display now is seconds:milliseconds
Actually, that looks like it might be decimal seconds.
You can probably format it to mm:ss by using the Format Duration plugin.
1 Like
Upppsss, sorry, I didn’t understand the situation well!
I though your sheet (where the pivot table is) was working fine but Glide wasn’t updating those values.
Bye
1 Like
OK perfect - that worked!
So now…how to make a top 10 list?? Do I have to make a relation to the visits column and then somehow make an array based off the highest number? IS that even something an array column can do?
Top 10 based on which metric, and how do you want to display it?
It was based on the ‘most visited salons’ and the pivot table naturally ordered them based on that metric so it was super easy to plug in.
Previously when I tried to make a legend of 10, it would only pull the first 10 row owners on the sheet regardless of metrics, so the fact that i could base the pivot table off that metric made it easier. So i’m also not sure if I need it to be on a separate table or something in order to recreate that?
pivot table ordering them by views:
mmm, I see. A doughnut chart. That’s a little tricky, because a doughnut chart doesn’t have any sorting options.
So yes, you’ll probably need a helper table. Okay, so I’m just thinking out aloud here, so I might not get this exactly right. But here goes…
- Start by creating a new table, and add 10 rows
- Add a number column, and number the rows from 0 to 9.
- In your Salons table, create a template column that joins the number of views and the salon RowID (in that order).
- Now back in your helper table, add two lookup columns:
– The first one will target the template column in your Salons table
– The second one will target the page views column in your Salons table
- Now add two Sort Array columns, one for each lookup column
- Because the Sort Array column sorts from lowest to highest, you’ll also need two Reverse Array columns, to reverse the sort from highest to lowest.
- Now you’ll want two Single Value columns, one for each of the previous two columns.
– Each one should take From Start → Row Number, from the reverse sorted array
- If everything has worked so far, you should have 2 columns with the pageview numbers correctly sorted in one, and the page views + RowID sorted in the other
- The last thing you’ll need will be the Salon name. To get that, create a relation column that matches the pageviews + RowID column with the same template column in your Salons table. Then use a lookup through that relation.
Phew! I think that should work. Give it a try if you’re game 
omg ok lol let me solve an easier problem real quick and then jump on that.
I just plugged in the “formatted time” column and even though it looks right in the database, it’s not displaying the same way…
Looks like it might be dropping the zeros. Try putting it through a template column to “lock” the format, and then use that.
Although, given it’s for quantity, that might not work. You’ll have to try it.
(sorry, I hardly ever use Glide charts - I always use quickcharts)
