Help linking lead and spend data

[rogerandrew]

Hello, 

I'm having trouble linking together two tables and getting the results I'm after. Relatively new to this paradigm so any help at all would be so helpful.

1. A leads table

2. A log of spend by cost category

All I need to be able to do is be able to see the return on investment by Cost Category over time and ideally, the cost per lead for each cost category in a given month. 

I'm happy writing the measures to work out ROI and CPL once the tables are linked, but I can't work out the correct way to link them. 

I'm probably being stupid but hopefully the solution is relatively simple. Examples of each table are below.

Leads

Created Date	Lead id	Cost Category	Revenue
01/01/2023	iklvnsdklnfdls	Paid Search	10,000
04/02/2023	jnfurbeybvas	Paid Social	5,000

Cost Category

Date	Cost Category	Cost
12/01/2023	Paid Search	1354
28/02/2024	Paid Social	2100

[lbendlin]

You need to add a Dates table to your data model, and describe the reporting granularity (you said monthly?)