How to calculate conversion rate

[#1]

Hi All,

I have the following two tables: Lead, Opportunity

Leads:
LOAD * Inline
[
LeadId,Date
1,2010/01/01
2,2010/01/01
3,2010/01/01

];

Opportunity:
Concatenate
load * Inline
[
OppId,Date,LeadId
1,2010/01/02,1

]

Opportunity is related to Leads by LeadId.

Now I want to calculate how many Leads have been converted to Opportunity by date. You can see that I use Concatenate to link that two tables.

I want to get the result looks like this:

Date           CountLeads CountOpportunity

2010/01/01  3                1

I want to use pivot table to achieve it. How to set the expression?

If not, please let me know how to link that two table except Concatenate.

Thanks.

[nagaiank]

I do not know your actual requirements. Based on my understanding, I loaded data using a left join instead oc concatenate assuming a LeadID must exist before it can be converted into an opportunity. The qvw file is attached.

[#3 · Accepted solution]

Hi Isaac,

A problem with your structure is that both tables have two fields in common. Assuming LeadId is the real connection between the two tables, you should give the dates different names (leadDate and oppDate?).

To calculate what you want you make a (pivot or table) chart, select leadDate as dimension and enter two expresions:

count(distinct LeadId)

count(distinct OppId)

think you can ditch the distincts in this case, but I like them as they help readability.

Is that what you were looking for?

regards, Jeroen

[#4]

Hi Jeroen,

Thanks for your reply, yes, it it good solution to change the dates into LeadDate and OppDate.

But I also add a Date Dimension table into QVW, how to link that two dates to Date Dimension table?

Thanks.

[#5]

Well, connection them to one and the same Date dimension is rather difficult in QV, as typically QV does not allow you to create loops in the model.

you have options:

- create 2 seperate time dimensions

- convert the second date to a meaure, i.e. daysToOpp by calculating the time difference between the dates.

- Leave dateOpp without a date dimension, you can always dynamically derive it if needed.

- creating a concatenated link table.

I kinda like the second option, as it gives you new info, and you can always reconstruct the original oppDate.

The fourth option would give you what you want, your structure would be:

Lead:

LinkKey

1|1/1/2010|

2|1/1/2010|

3|1/1/2010|

Opp:

LinkKey, OppId

1||1/2/2010, 1

Link

LinkKey, LeadId, Date

1|1/1/2010|, 1, 1/1/2010

2|1/1/2010|, 2, /1/2010

3|1/1/2010|, 3, 1/1/2010

1||1/2/2010, 1, 1/1/2010

Basically you make a synthetic key LeadId|LeadDate|OppDate, and put all combinations in one link table. The two fact tables cannot have any other field in common moving LeadId to the LinkTable. If you inclue LeadId as dimension facts referrring to the same LeadId will be grouped so you can do correct calculations on them. However this is the more complex solution, so building charts may become less trivial. This may not be what you are looking for. If you can do without, I'd take the second option.

gl, Jeroen