A base design that counts the average amount of click of online adverts

[Paul_Van_Dooren]

Hello everyone,

I’m wondering if the following idea could be created in Airtable.

Suppose I have a business that sells online adverts. When a client buys an advert, we’ll put it online and the advert will be shown on specific newspaper websites.

When the client agrees with the offer, we provide the client with 2 things:

• An amount of clicks (the advert is clickable an redirects to his homepage)
• A period between a start and an end date that the advert is online.

Now, what do I want to create in Airtable:

I have a sales team that can spent a budget 500.000 clicks a year.

So, I’d like to create a table in Airtable, where my sales team can add new clients and each client is then granted an amount (1.000, 2.000, 5.000, …) of clicks, a start and an end date.

I would then be great that Airtable calculates the average amount of clicks between the start and end date and also subtracts the amount of clicks of the yearly budget.

Can this be done in Airtable?

Any help is much appreciated. Thanks very much

[Rupert_Hoffsch1]

Hi Paul, this is very much possible. Are you entering the amount of clicks manually? In what interval? Best, Rupert

[Paul_Van_Dooren]

Hello Rupert,

The yearly budget of clicks is a fixed amount.

All the other data i’d like to enter by a form;

• some client data (name, adress,…)
• the amount of clicks the customer buys = random number (2.855, 5622, 2500,…)
• the start date is chosen by the customer
• the end date is chosen by the customer (start and end date is consecutive, no pauses in between)

All the data above is for one specific advert. If the customer wants to extend is end date, then an new input (order) is created.

I hope this is the information you wanted.

Big thanks for helping me out!

greetings,
Paul

[Rupert_Hoffsch1]

So this is how I would do it:

In order to calculate an average over many projects, you need a single record that all projects are linked to. So…

Table 1: Sales team (here you literally only have one record called “Sales Team”. You could have multiple teams of course.
Table 2: Projects (this is where the form enters your data) and every project / record is linked to the sales team.

In table 1, you can now create a Rollup field and calculate the average of all values. In another Rollup field, you build the sum of all clicks. In a formula field, you subtract that sum from your 500.000 total clicks.

Does that make sense?

[Paul_Van_Dooren]

Hi Rupert,

Thanks for your input.
I’m going to try to make your idea to work.
I’ll keep you posted !

Thank you very much.

greets,
Paul

[Paul_Van_Dooren]

Hello Rupert,

Your solution works perfect!

I’ve also created a function that i can see how many clicks there were requested in each month.
And the main goal, to get the amount of remaining clicks has been reached!

Thank you Rupert.