Product / Service Item breakdown

Hi there

My use case is that we’re an agency and each month we deliver items from a service catalogue to deliver services for our clients. Our service catalogue is in Airtable (see below)

I now want to use AT to forecast the items we are going to provide each month so I can see the number of points (think ‘hours’) we are going to deliver both at a customer & global level. I have set up a simple table to do this (see below)

This step is easy, however, the next level of detail I’m trying to crack is something I’m not sure how to architect in AT.

A lot of our services are further broken down in terms of the resource required to deliver them (i.e Account Managers, Content Team, Design Team, Development Team etc.) and the total points for each item may be broken down into a fixed number of points per team. For example, creating a blog article is 3.5 points in total but those points are broken down across various teams.

What I am trying to do is enter a list of the services for each customer, for each month, but then have it further breakdown to show me the actual resources required and the points for each resource so that I can see at a customer and also a global level the points needed for each team, each month.

In the example below I have the same ‘Blog article’ but now broken down by the effort required by each team to deliver the service. This is what I envisaged seeing that in Airtable once I had added the ‘services’ at high level. How the data is presented is less important to me, but I do need to be able to total the points per team required, per customer or company wide, each month.

Any help is much appreciated!

Ben

Hello @Ben_Crouch!

I could see this workflow in Airtable in a couple of different ways— so I’ve created the below short video to walkthrough two possible workflows I could see working for your scenario.

You can watch the video here!

Here is the formula I used in the video to have a dynamic team time via project type:

IF(
    FIND("Design Team", {Team Involved}),
    SWITCH({Project Type},
        "Blog Post",  3600,
        "Article", 2000
    )
)

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark:.

Hey Rose, first of all I really appreciate you jumping in and offering to help out. I kind of get where you’re going with the suggestion, but I’m not 100% my use case has been understood or that the solution is scalable.

If I were to use the terminology you suggested then I’ll point out where the crucial piece is missing:

We have ‘projects’ - which are a series of ‘services’ delivered across a time frame. Each service has tasks which take up different time from different teams. I’ve created some of these tables in the way I think they need to be created but I could do with some help sanity checking if I have done them right and also how I then use them to breakdown the points for each tasks.

Any feedback welcome!