How to calculate lead cost from 2 different tables?

[zeremeser]

Hi, how are you?
I have one table of leads that has the date each lead entered the system.
Another table of advertising expenses where I pull yesterday's advertising expenses every day.
At the end, I want to make a total of how much a lead costs me each day.
I thought about adding a column in the advertising expenses table where I would pull the total of each day's leads and then divide the expense by the number of leads.
But - I can't figure out how to do it correctly without running a lot of internal automation.

Could it be that I'm missing something? It seems very simple and logical to me that it should be possible to do it quite easily.

I would appreciate your help!

Thank you very much!

[Dimitris_Goudis]

Hey @zeremeser, 

While you get this infirmation from a graph on interfaces, I would recommend to do the following, and I will explain below the reason I recommend this. 

Please create a new table, 'Days.' Each row will be one day' date. Link this table with Leads and Ads spend tables based on the date of reference. The connection can be updated automatically from an automation. 

In table 'days' add one rollup to sum all leads and one other form ads connection to sum the spends, then you can calculate the avg cost of acquisition. 

By doing this, you can track the performance day by day automatically, and you can extract useful data results for your advertising strategy. 

Please let us know how that worked. Please feel free to reach out with any questions

Thanks
Dimitris Goudis

[zeremeser]

Thanks for the explanation.
I did think of a process similar to what you suggest, but I'm getting stuck at the stage of automatically summarizing all the leads by date.
How can I do this?
That is, if I withdrew the amount of the SPEND from yesterday (12/21), how do I know how to withdraw the total of the leads registered on that date?
Thank you very much!!

[zeremeser]

so no one have an answer for it?

[TheTimeSavingCo]

Try creating a formula field with DATETIME_FORMAT that just outputs the date, and then use an automation to paste that into a linked field to a 'Days' table?