A Solution for Dividing/Summing/Multiplying by the Previous Value!
Hey Y’all,
So I’m working on a use case where I need to determine metrics for a funnel. I want to know two things:
- the percent of value that remains in the funnel from the previous step, and
- the percent remaining from the beginning of the funnel.
So that looks like:
Metric A - 10,000 | 100% | 100%
Metric B - 5,000 | 50% | 50%
Metric C - 1,000 | 20% | 10%
Metric D - 500 | 50% | 5%
This of course would require me to use the previous value in a function, something that Airtable isn’t built for on its own, but it is something that people have found great workarounds for, like @W_Vann_Hall who has covered a great workaround thoroughly in this post. I happen to be dividing, but this is also great if you’re looking to do a running balance as well.
To make this easier and cleaner, and just to see if I could do it (tbh), I tried my hand at a script that will do this in a snap.
The Pros: No complicated rollups
The Cons: Since it uses the scripting block, it doesn’t update automatically.
Here’s a a video of me walking through the base setup and the script, and here’s the example base, which has a couple of other scripts in it was well for you to play around with. I’d love to know how you’d improve this or use this, and happy Airtabling and Scripting!
:raising_hand_woman:t5: :raising_hand_woman:t5: :raising_hand_woman:t5: I want to mention that before using the scripting block I NEVER used JavaScript. I had a bit of VBA background and made some messy Macros in my Excel days. If I can I can do this, anyone learning this can too. It just takes a lot of googling, paticence, and taking a walk when you run into problems.
Script below. I’m using @Jeremy_Oglesby’s great batchAnd() function so that I can update more than 50 records at a time.
let funnelTable = base.getTable('Funnel Metrics');
let funnelQuery = await funnelTable.selectRecordsAsync();
let funnelRecords = funnelQuery.records;
let months = funnelTable.getField('Month').options.choices;
let years = funnelTable.getField('Year').options.choices;
let metrics = funnelTable.getField('Metric').options.choices;
// Goal: use the preceding number in the funnel to calcualte the percentage
// For each group - create an array of the funnel numbers
// Create an array of the values you need, as well as the ID of the record so that you can update it later.
// I've also added a filter to this array, because there are some records in this table that I don't want to include in this calculation
let recordsMap = funnelRecords.map(c => ({
'include': c.getCellValue('Include in Funnel Calculation'),
'value': c.getCellValue('Value'),
'id': c.id
})).filter( x => x.include == 'Yes' );
// This is where you'll definitely need to customize for yourself.
// Each of my campaigns has 4 steps, so I can decided to splice my recordsMap array every 4 values.
// Depending on how your information is structured you'll need to figure out the best way to separate your records into the groups you want to divide/add/subtract/mulitply by
let splicedArray = []
while (recordsMap.length > 0) splicedArray.push(recordsMap.splice(0, 4));
// Using forEach and reduce, I'm able to go through each of the splicedArrays in splicedArray
// and use the reduce function to caluclate the percentage and add two new key value pairs to the objects in each splicedArray
splicedArray.forEach(function(array){
array.reduce(function(acc, curr, i, array){
curr.funnelVal = curr.value / array[i - 1].value;
curr.totalFunnelVal = curr.value / array[0].value
})
});
// Getting this in the right format for batchAnd as well as updateRecordsAsync
let update = splicedArray.flat().filter(x => x.funnelVal != undefined).map( c => ({id: c.id, fields:{
'Funnel Retention by Step': c.funnelVal,
'Funnel Retention - Overall': c.totalFunnelVal
}}))
/*
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.
::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()
table = Table; the table the action will be performed in
records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform
::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;
switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;
case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
};
await batchAnd('Update',funnelTable,update);
