Archive every month by merging records

[julien_level]

Hi there,

I've created an airtable where i store (anonymously) every lead generated for our clients by our agency.
i have différent informations : ID of the lead, client, agency of the client, campaign, Revenue, Cost, source and a few other field.

The problem is in 3 months we are already reaching 50K records and even getting the 250K plan won't be an option. So i plan to have 2 bases :
- One (the current) where the leads keeps arriving every day (the history will stop at the previous month included)
- One where i merge the records by month (by doing the sum of the cost and the revenue). This would reduce greatly the number of records.

For example, let's say i have 200 records of leads for one of my client that came from Facebook in november, I would reduce that to one line only.
Then 1500 leads for that same client come from Google and it would also be reduced to a line.

Any idea how i could do this ? via a Script ?
or is there a better way to manage this ?

thanks in advance !

[Karlstens]

I've been meaning to investigate condense methods for myself. I suspect that the scripting will be fairly simple - take data from several records and map it into a condensed object, and posted as one record - but... as with all things scripting, easier said than done!

There's things to consider here;

• Will the compressed data ever need to be decompressed into the original state? 1 record back into 200 records?
• Will the archive need to remain searchable and usable, and to what extent?
• Does the archived data drive reports, dashboards?
• Does the archived data even need to exist in Airtable, or just output to a log?

Solutions will be unique per base, so any further info you could provide might help your cause.

[julien_level]

HI @Karlstens 

Thanks for your reply !
You are right, i should give more information :

• No the data won't need to be decompressed back to the 200 records for example.
• yes the archive will need to be searchable and usable so that they can give info to the linked interface (that will be duplicate from the other table)

In a perfect work i would keep everything in one table as we don't really work inside the tables, the real feature that will be used is the different interfaces that will allow us to have statistics for our campaign, or interfaces that we create for our clients.

Having two tables won't give us a complete view of our stats but it could do the job for the time being as we most of the time are looking for stats for the current and previous month.