Swaps and Stats

Hey ​@Melissa_Frank1  all these seems quite some things to do, but also something that can be done within a 5 minutes call. If you are interested I am more than happy to accommodate some time (free) and jump on a quick call. I am sure we can tackle most of this in no time.

Just find a time in here CAL

Felipe @Aspirity.com

Oh that is so nice of you! I would love that, thank you. 

Hey ​@Melissa_Frank1,

Seems like a long post lol! Feel free to grab a slot as needed. I’d be happy to go through it on a call.

Mike, Consultant @ Automatic Nation 

Hm, any chance you could provide some example data for this?  A bit of both of the CSV files in particular would be super helpful!

Here is a link to a shared Google Drive folder with a sample of each CSV file: https://drive.google.com/drive/folders/1rz-2s2f5qvv64tWx6HiU6z5w6jngT9se?usp=sharing

There’s also a Google Sheets version because there previously was a LookerStudio connection and someone else tried to convert it to allow imports of chunks of data. 

Thank you ​@felipe-saucedo for your time! I appreciate the suggestion you had to create an automation to add a record to a new table for joining the Buttons and Users. I will give that a try this afternoon. It makes sense that we would need a script to get the statistical analysis I was hoping for, to see what the most common button combos are.

Re the sample data, there is a column in the Interactions CSV called all_buttons. This field has all of the presses I want to use for analysis, but they are not separated by comma, semicolon… just a space. But some button names have more than one word. So is there a way to have Airtable translate the data in that field into the sequence of buttons that were pressed? I cannot imagine this would be possible without running some script somewhere, but counting buttons from this field is far more accurate than the alternatives.

Oof, if they’re not separated by comma and they can span more than 1 word it’ll be hard to come up with a script or regex solution… Do they have any kind of commonality to start or end with aside from a simple space like starting with “Button” or something ala Button Stop Button Go Button Submit and Reset Button Start because then you could use the word Button to indicate where to split things…

EDIT- Just realized you had posted sample data… hmm

Tragically, no. I thought I could do an automation to grab Button_12 then Button_11 etc in a single field linking to the Buttons table, but I can’t add more than one. Either, one button gets added, or it merges all the button names into a single new button. 
 

So I had the idea to change the names of buttons with more than one word so the spaces would only separate buttons. But I would need other people to be able to use this with their buttons, too, so not the ideal solution. 

Thanks for the data! 

1. Interactions: A line graph with right y-axis just showing average number of presses over time, and left y-axis centered over 0 and showing daily/weekly/monthly deviation in number of interactions (based on whatever x axis we go with). 

This is relatively simple except the left y-axis, which I assume requires a formula field somewhere but what/where? 

Hmm...how did you end up doing this?  I was thinking about it and the only way I could think of doing this was with a table where each record represented a single Button <> Interaction with the date

---

2. Buttons/user: List of just the top 5 buttons use within the past week, with filtering by User  

Right now I have a list of all button presses sorted with the ones with the most interactions at the top, and I change the color of the text for buttons with interactions over a certain number, but I have to manually change that threshold right now. Boo.  

Interesting!  I take it you linked button_1, button_2 to the Buttons table and did Count fields there and summed everything up?

---

3. Pivot table with the combos, like if button in row label appears in an interaction, how many of those interactions also contain the buttons across column header? 

Hmm, I can’t really imagine how this one would work, but it does feel like a script’s your best bet

You could also try going down a (probably crazy) road of creating a table that contains combinations, and then using formula fields and automations to populate it.  e.g. if the buttons were ‘Catch, Please, Frisbee’, then you’d have three records in that table:

1. Catch, Please
2. Please, Frisbee
3. Catch, Please, Frisbee

And this would show you the combinations (ordered only, but if you wanted it unordered you could potentially do that too but at that point you should just write a script I reckon) and also the most likely button to follow

Theoretically possible, but yeah I’d say just use a script heh

• Split the space‑separated button list into an array (e.g. SPLIT({Button Order}, " "))

• Use ARRAYREVERSE() to flip order

• Use indexing like ARRAYJOIN(ARRAY_SLICE(...), " → ") to map sequence transitions

• For repeated presses, compare adjacent items in the reversed array for equality

Interesting! I will try this, thank you. Will the split work, though, with buttons that have spaces in the name? I thought I would need something that searches the list of buttons for matching names. I have done something like this with macros in Excel, but I’m struggling in Airtable. 

For 1… still haven’t worked this out. I don’t have access to the old LookerStudio details to see how it was done before. I may have to leave this one to the app developers to provide something in the export. 
 

Re 2, you are correct. It’s very annoying though that I have up to 12 button positions for each interaction, so needed 12 count fields for all interactions, 12 count fields fields for all of User A’s interactions only, etc. Maybe I can do this conditional as a filter in the Interfaces, I actually hadn’t thought to check. Will do today! 
 

And for 3, this suggestion is very interesting! I need to work out a way to do this with Automations though, and in a way that won’t merge button names in a new button… I like where this is going, though. 

Thank you all for your help so far! Here is the (draft) Interface if you’d like to see: https://airtable.com/appgQNEm8H9eORNp2/shroRonqFcFRmr3Mp

I am going to experiment with changing the formula for the Interactions records name - just had a lightbulb tell me I should put the User in there so it comes up in the linked records in the Buttons table...

re: For 1… still haven’t worked this out. I don’t have access to the old LookerStudio details to see how it was done before. I may have to leave this one to the app developers to provide something in the export. 

Hmm, yeah.  Let me know if you want to go down this road manually, I think I have an idea for this.  How does the data get populated btw?  Is it a constant sync?  Or like you’re going to do imports once in awhile?

---

re: Will the split work, though, with buttons that have spaces in the name?

Ah there’s no such thing as ‘SPLIT()’ or ‘ARRAYREVERSE()’ at this point, so I don’t know whether you should spend too much time trying out that solution I’m afraid

---

That Interface looks really neat!  

​@Melissa_Frank1 +1 this Interface looks very cool! Please share the finished product and how you got there in Show & Tell if you can for others to learn from 😍

Re 1: LookerStudio did have a constant sync, but it’s not in the list of connectors any longer. I don’t know why they stopped using it, but I believe they’re planning to build some of their old dashboard template into the app. Currently, though, things they’ve already moved into the app cannot be shared with a static link, and we human users like to compare notes and work together on common challenges sometimes. I’m not sure how people were putting this particular chart to use back when it was functioning… maybe tracking overall behavior changes. 

Frustratingly, we will have to manually add exports over time, and we cannot set a filter before exporting, so I have been deleting records for the day of export and using that to delete duplicate lines in new exports. It’s a manual process all users will just have to look out for, but it’s simple enough to do. If anyone wants to update fields they didn’t previously fill, I’m not sure there’s any good reason to do that in the app, versus just doing it in Airtable going forward. 

Glad you like the interface! I would love to be able to put a table of contents or something at the top but Airtable’s not quite there… yet. The developments made since I first started using it over a decade ago are truly incredible, so I don’t count much out. (The only “no” I’ve ever gotten was when I asked about HIPAA and FERPA compliance 🤣 And we were able to manage FERPA in-house.)

Hi ​@Melissa_Frank1,

Great job on the interface, its looking fantastic. 

I have a suggestion for your buttons dilemma. It will utilize a script and another table. The idea is that using all the actual possible button names from your buttons table, we can give a script an array of possible names, then pass the field of all_buttons and have it return an array of buttons in reverse order. This would make sure that any spaces wont matter since the script will know what constitute a button from the list of buttons. From here you can pass this array of names and create new records in a new table called “Button Press Log” or something like that. Your automation will create a new record here with each of the buttons that were outputted by the script. 

For example, if you have an all_buttons field say “Wind Yes Noise Noise”, then your script will return an array of [Noise, Noise, Yes, Wind]. Then the automation will create 4 new records in this new table with these button names and it will also tag each of these names to the actual Button table so that you can get lookups and rollups in there to help with your statistics. This is a way to get around linking multiple repetitions of a button multiple times in a linked record field. 

Here is a sample script you can use:

// Replace this with input.config() to get an array from the result of a find block from the Buttons table.
let possibleButtonNames = [
  "Poops",
  "Ear",
  "Oopsie!",
  "Love you",
];


let reversedSequence = "Oopsie! Poops Poops Love you Love you Ear";


//Sort names by length so multi-word names match first
let sortedNames = possibleButtonNames.slice().sort((a, b) => b.length - a.length);


//Parse
function parseReversed(names, str) {
  let out = [];
  let remaining = str.trim();
  while (remaining.length > 0) {
    // find the first name that matches at the front
    let match = names.find(name => remaining.startsWith(name));
    if (!match) {
      throw new Error(
        `Unable to match any button at: “${ remaining.slice(0, 20) }…”`
      );
    }
    out.push(match);
    remaining = remaining.slice(match.length).trim();
  }
  return out;
}


//Output
let parsedReversed;
try {
  parsedReversed = parseReversed(sortedNames, reversedSequence);

  //reverse order
  let originalOrder = parsedReversed.slice().reverse();

  //Original order of buttons pressed
  console.log("Original order of buttons pressed");   
  console.log(originalOrder);
  //Replace this with an output.set('original_button_press_array', originalOrder);

} catch (err) {
  output.text("Error parsing sequence:\n" + err.message);
}

The way to architect this is going to be unique to how you plan on calculating your statistics so some further considerations are probably needed. Happy to discuss this further if you think this is going in the right direction!

Found this!! I adapted the code to my tables and ran the script and I think this might be the answer to some of our needs… Going to play with the results a bit and see if we can live with this. I still want to play around with the idea of creating a record for each combo of buttons per interaction, and a table that joins the buttons and users. But that’s a project for tomorrow...

// Replace this with input.config() to get an array from the result of a find block from the Buttons table.
let possibleButtonNames = [
  "Poops",
  "Ear",
  "Oopsie!",
  "Love you",
];


let reversedSequence = "Oopsie! Poops Poops Love you Love you Ear";


//Sort names by length so multi-word names match first
let sortedNames = possibleButtonNames.slice().sort((a, b) => b.length - a.length);


//Parse
function parseReversed(names, str) {
  let out = [];
  let remaining = str.trim();
  while (remaining.length > 0) {
    // find the first name that matches at the front
    let match = names.find(name => remaining.startsWith(name));
    if (!match) {
      throw new Error(
        `Unable to match any button at: “${ remaining.slice(0, 20) }…”`
      );
    }
    out.push(match);
    remaining = remaining.slice(match.length).trim();
  }
  return out;
}


//Output
let parsedReversed;
try {
  parsedReversed = parseReversed(sortedNames, reversedSequence);

  //reverse order
  let originalOrder = parsedReversed.slice().reverse();

  //Original order of buttons pressed
  console.log("Original order of buttons pressed");   
  console.log(originalOrder);
  //Replace this with an output.set('original_button_press_array', originalOrder);

} catch (err) {
  output.text("Error parsing sequence:\n" + err.message);
}

Oh this sounds great! I will give this a shot after a good night’s rest :D Thanks!

re: Frustratingly, we will have to manually add exports over time, and we cannot set a filter before exporting, so I have been deleting records for the day of export and using that to delete duplicate lines in new exports.

Hmm, could I check if you’re familiar with the CSV Import extension?  The ‘interaction_id’ field seems to be unique, so if you used the CSV Import extension, turned on ‘Merge with existing records’ and pointed it at that field you shouldn’t have duplicate lines I believe

almost, it will works with it, you have to give it a try.