Using Airtable to see sales trends?

[oreocereus]

This is a very basic question, but I don't have the terminology to google the things I'm trying to learn, so patience please..

I've spent a while getting used to creating bases for record keeping with lots of automation etc. But I now need to get my head around using airtable to identify patterns and trends against varying factors. My first project to start understanding this is to start unpacking sales trends from our honesty shop.

I have it setup in airtable like this:

Table: Products

This lists all our products with its standard pricing. It links our stocktake table (and our seeds table, and harvest table).

Table: Stocktake

Linked to products. Here we enter anything that goes in or out of the honesty shop, as well as noting any special pricing (which overrides the pricing from the products table). When I enter a stocktake, it creates an "archive" of the stocktake, a wastage record, a sales record (with special pricing, if relevant), and if necessary an "in stock" record. 

What I'd like to be able to visually summarise firstly:

- Sales & wastage of different products over the year
- Sales & wastage relative to its price at the time

I'd appreciate any starting points, or suggestions of how you'd approach this!

[TheTimeSavingCo]

For trying to figure stuff out over the year, you'd need a new table called "Yearly Summary" or some such, and you'd need to link all the appropriate records to a single record that represented a year in the new table.  So records from 2023 would be linked to the "Yearly Summary" record of 2023, etc.  You could then create rollups and formulas and such to display the data you want, and you'd use an automation to help you link stuff together so that you wouldn't have to do it manually

I don't really understand the second bit about summarizing the sales / wastage relative to its price at the time though, could you provide some examples of that?

[oreocereus]

Would you create one record per product? Or are you suggesting one record for a whole year of sales?

---

@TheTimeSavingCo wrote:
I don't really understand the second bit about summarizing the sales / wastage relative to its price at the time though, could you provide some examples of that?

---

I would like to be able to see how well things sell at different prices (or not - hence the wastage query) as we try and hone in on suitable pricing. 

[TheTimeSavingCo]

My understanding is that you're looking for product level data for the year, and so I'd create one record per product per year in that "Yearly Summary" table

---
re: I would like to be able to see how well things sell at different prices (or not - hence the wastage query) as we try and hone in on suitable pricing. 

Interesting!  Hmm...I think for this I'd have a new table and create one record per product per price it was sold at and link stuff up appropriately.  With that I could calculate the total amount of time it was sold at that price, which would allow me to calculate the average sold per unit of time at that price, does that make sense?

[oreocereus]

Gotcha. Well, the sales/stocktake table links to the products table, so that kind of functions as the yearly summary table you're suggesting I think? What tools would you be using the turn this into a digestible/understandable "trend"?

[TheTimeSavingCo]

re: Well, the sales/stocktake table links to the products table, so that kind of functions as the yearly summary table you're suggesting I think?

Does your "Products" table have one record per product per year?

---
re: What tools would you be using the turn this into a digestible/understandable "trend"?

Hm, charts in an Interface I guess?  Not really sure what you're asking here, sorry!

[oreocereus]

I don't need a yearly summary, per se, I need to be able to get a better picture of what sells well at different times of the year (e.g. I know Courgette are popular at the start of the season, but sales drop off quickly). 

It seems airtable has surprisingly limited function for this analysis, unless I'm missing something?

[TheTimeSavingCo]

Ah, then you'd need a record per time of the year you want to compare against I'm afraid.  If not, you could try creating a formula field that outputs the grouping you want and attempt to group by that

Hmm, if we define "limited" as there's nothing pre-built for this, then yeah, I think you could say it's limited?  How one wants to analyze the data is pretty subjective, and so the expectation is to format the data ourselves I suppose