Workings out Cost per Leads

[SeanB]

This is a very specific example and I cannot find a solution anywhere else so hoping for some help on here.

I have a master items and it's just a distinct count to get my total leads, this is broken down via advisor. Example below:

Advisor                 Leads

Advisor A                 32

Advisor B                 54

Advisor C                 48

We have to pay for the leads so we then have a KPI for the total spend, this is just manually inputted.

From this spend we calculate the cost per lead by dividing the spend by the amount of leads. Example:

Spend = £200,000.00

Leads = 200

Cost Per Lead = £1,000.00

With this Cost per Lead figure we then work out a lead cost per advisor by multiplying the cost per lead by the amount of leads the advisor takes. However, when I try to put this into the table instead of doing the cost per lead multiplied by the leads for the advisor, so for Advisor A it should be £32,000.00, what it does instead is it just gives the cost per lead for the total leads and attributes this to all advisors so all the advisors will show to have a cost per lead of £200,000.00 instead of their individual costs.

Is there a way to attribute the Lead Cost per advisor not the total.

[SBN]

Hi there SeanB,

I stumbled upon your question and tried to create something that might help you if I understood it correctly.

The attached dummy app contains the following sheet.

The "Total commission" is a "Variable input" object in which you can enter the total amount of commission paid.
Next to that there are the total amount of leads for all advisors and the commission per lead.
In the pivot table you see the amount of leads per advisor and the calculated commission per advisor.

Hope this is still helpful.

Kind regards,

SBN