Auto Categorization

[QuentinLeblond]

HI
I face an auto-categorization issue with no solution so far

I update a table with hundreds of credit card transactions, with lot of them recurring with 95% of the text string similar from a month to another month.

Is there any chance there is a solution that would save me from categorizing 10 times the line 'Airtable' as 'Online Services' , or even better, that categorizing once 'Airtable November 2022' would allow to automatically categorize in the same way 'Airtable December 2022', 'Airtable January 2023', 'Airtable February 2023', etc...?

Many thanks for your help

[Bill_French]

---

@QuentinLeblondwrote:

... a solution that would save me from categorizing 10 times the line 'Airtable' as 'Online Services' , or even better ...

---

Indeed there is. A script process that leverages GP3 to perform keyword and entity extraction automatically.

[Karlstens]

Using regex in a script, you could write a map key:value object that trigger specific categorisation upon matches.

This is something I’ve been wanting to do for sometime now, I’ll see what I can come up with.

[Karlstens]

With a transactions table, and a Categories table - you can have a script match a Transaction Description against a Category String Term, and then update that transaction with that category upon having a match.

I wrote this with this mornings coffee, it seems to work - you can see the automation filling in the matched Categories;

The match term is found within the Categories Table;

The automation triggers upon the Transaction Table being updated - it could probably be further refined;

The tricky bit is the scripting, where you'll need a solid understanding with arrays, objects, and what data the Airtable API expects. There are many ways to do this task.

Unfortunately I have to paste my script as an image due to a bug in the new Airtable forums - typing it out is good practice.

Hopefully this helps! I plan to implement this into my own systems now I've figured it out.

[Bill_French]

>>> The tricky bit is the scripting ...

Indeed, there are a lot of nuances that need to be considered. But this is a pretty elegant approach and should get the job done.

When faced with these types of challenges, I always worry about the maintenance required by the end users. In this case, it seems pretty straightforward; if you maintain the match terms and keep them ahead of the actual terms flowing into the transactions, it will work well.

There is a way to eliminate a sizeable chunk of this solution, namely the Categories Table and any code that needs to work with it. As I mentioned earlier in this thread GPT-3 could be used to extract keywords from the transactions and apply the discovered tags. This, of course, would have the added benefit of never needing to maintain any separate table or try to predict the terms and matches required for proper classifications. I make this suggestion only for the benefit of other readers.

As Musk often says - the best part in a machine is no part at all. 😉 But there is a replacement part involved - GPT-3; it does require an API call, and it is a non-zero cost (about $0.0002 per transaction). A thousand transactions per month would be about $2.00, so it seems like a net positive tradeoff if you want a deeply elegant and effortless system.

[QuentinLeblond]

Ok many thanks, very useful one.

I am no coder at all though, so I'll try to see if i can implement myself this; if there is any chance you can send me the code line, i'd really appreciate ! or even share the tables you just did, that I would adapt 🙂

Thanks a lot though, Im a first airtable-forum user, and very positively surprised by responsiveness and kindness.

Thanks a lot again !

[QuentinLeblond]

Thanks a lot Bill

I'll see if i can have a look into GP3, but as said previously, im not a coder, so not sure im able to play with API either - despite ill probably have to try to directly plug my bank account's API to Airtable so i dont have to download/copy/paste transactions each month...

Thanks a lot for your time and kind reply though !

[TheTimeSavingCo]

When faced with this issue I ended up creating a script that would check whether the text included any specific words, and I output stuff from there

For example, if the text contains any of "wix", "squarespace", "airtable", it would output the word "Subscriptions" for me

You'd have to manually categorize everything but eventually you get to a point where you've covered most of them anyway and / or get too lazy to keep it up (which is what happened to me heh)

[Karlstens]

Hopefully this posts OK - the recent community forum updates introduced some bugs that have been making it difficult to post.

let {transactionRecordId} = input.config();

let transactionsTable = base.getTable("Transactions");
let myRecord = await transactionsTable.selectRecordAsync(transactionRecordId, 
    {fields: ["Description", "Category"]})

let categoriesTable = base.getTable("Categories");
let myCategories = await categoriesTable.selectRecordsAsync( {fields: ["Category Name", "Match Term"]})

let categoryBank = myCategories.records.map( record => (
    {
        id : record.id,
        name : record.name,
        category : record.getCellValueAsString("Match Term").toLowerCase()
    }
));

let noMatchrecord = categoryBank.filter((record) =>
	record.name.includes("No Match")
);

let matchedObject = categoryBank.filter((record) =>
	record.category.includes(myRecord.name.toLowerCase())
);

let myReturn = [];
if (matchedObject.length === 0) {
	myReturn = noMatchrecord;
} else {
	myReturn = matchedObject.map( record => ({
        id :record.id,
        name : record.name
    }));
}

console.log(myReturn);

await transactionsTable.updateRecordAsync(transactionRecordId, {
    "Category" : myReturn
})

[Bill_French]

The irony that a platform noted for its deep commitment to #no-code, which requires so much code, does not escape me. My advice, find a student at a local community college who codes and become close friends. The student needs these types of experiences and you may learn some coding along the way.