Connect multiple fact-tables - Web Analytics

[CorvanDalfzen]

Hi Fellow Power BI Users -

Guess who’s back? 
You’ve helped me a lot with my questions about the churn, hopefully you can also help me with my next questions about Web Analytics.

What do I want?
I want to know which keywords are most valuable for us and which keywords are not.

What do I have?
I’ve got a datamodel with 1 dimension-table (Calendar) and 3 fact-tables (Invoices, Customernumbers with keywords and Keywords with Cost per Click (CPC)

Key measures:
Number of Keywords table 2016+ Customernumber = 
COUNTROWS('2016+ - Date - Customernumber - Keyword - Revenue - Transactions')

SUMX Total CPC = 
CALCULATE(sumx('2016+ - Date - Keyword - Clicks - CPC';'2016+ - Date - Keyword - Clicks - CPC'[Clicks]*'2016+ - Date - Keyword - Clicks - CPC'[CPC]))

Total Amount excl VAT = 
SUM('2016+ - Yourcrm20 Invoices'[Total Amount excl VAT])

Additional information:
There is no connection in Web Analytics between the customernumbers and the Cost per Click (CPC). We have to make this relation ourselves.

Table 2016+ - Date – Keyword – Clicks - CPC

The cost per click (CPC) for a keyword used in a particular search engine. Not every search leads to a conversion.

Table 2016+ - Date – Customernumber – Keyword – Revenue – Transactions:

These are the successful conversions, this customer will receive an invoice from the table Invoices.
On the same date this customer has received an invoice.
You can ignore the column revenue mentioned in the table, this isn’t the right figure. The right figure has to come out of the Invoice table.

Invoices:
Information about customernumber – invoicedate – Total Amount without VAT.

Questions:

1. How can I connect multiple fact-tables with each other? Do I have to make a new dimensiontable with CROSSJOIN(values(….); values (….) ; values (….)?
2. I want a table with:
   Date – CustomerNumber – Keyword – CPC – Invoice Amount excl. VAT – Diff. CPC and Invoice
3. Another table with:
   Period – Keyword – Total CPC (sum) – Total Invoice Amount – Diff. Total CPC and Inv.Amount

Can you help me with the formulas?

Thanks in advance,

Cor

[sam.mckay]

Sorry for the delay, a bit in this one so have had to digest for a while.

Can we just start first with question 1

To join table you need a common dimension (column) in most cases. I would also rather do joining and merging at the querying level than in formulas, if you can that is.

In your tables what is the common column that you could merge on?

Can you show me the two exact table you want to merge in question 1?

[CorvanDalfzen]

Hi Sam,

I hope you are feeling fine now. I saw 2 sessions of the summit until now, so I have to see the other 4 sessions this weekend.

I reply to your question, I have 3 fact-tables that I want to connect with each other.
There is no common column for these 3 tables. We do have:

The common column between the tables 2016+ - Yourcrm20 Invoices and 2016+ - Date - Customernumber - Keyword - Revenue - Transactions is the column Customernumber.

The common column between the tables 2016+ - Date - Customernumber - Keyword - Revenue - Transactions and 2016+ - Date - Keyword - Clicks - CPC is the column Keyword.

Do I have to make 2 new lookup-tables?

[sam.mckay]

I might have to look at this model I think.

Due to the complexity of what you’re asking I’ll just be taking guesses I feel on the best solutions.

All I can try to do is get my mind around the data and then offer suggestions.

Send it through to my email, and I’ll attempt to look at in the coming days and come back to you.

Sam

[CorvanDalfzen]

Hi Sam,

I’ve sent you the model by mail.

I’ve got another question:

Some customers have used more keywords, for example customer 22616161:

This customer has had invoices on 18-11-2017, 01-12-2017, 29-12-2017 and 30-12-2017.

The values of the invoices from 18-11 and 01-12 belongs to keyword 1, the value of invoice of 29-12 belongs to keyword 2 and the value of invoice of 30-12 belongs to keyword 3.

Is it possible to do this calculation with DAX?

Thanks in advance,

Greetings from Holland,

Cor van Dalfzen

[sam.mckay]

Cor I’m really sorry but I keep getting this error when trying to open that file

Today I worked out how to add zip files to forum posts, so can you try again on here.

[sam.mckay]

I just tried downloading it again and not having any luck. I’m just reading through the post now, seeing if there’s anything I can add right now to help.

[sam.mckay]

I just need to see it, I’m struggling to picture it. Let me know how you go.

[CorvanDalfzen]

Hi Sam,

I’ve sent you 2 e-mails, I’ve made a new version of the datamodel. Maybe you can open one of the 2 files?

Please let me know if I have to do more to get the file to you.

[sam.mckay]

Ok been looking into it

This is where I’m currently at. I think you should create table of unique values for your customers and the keywords. Like so…

So you mentioned earlier in the post that you want to find out which keyword are most valuable and which are not.

I can easily do this for the keyword by itself

But based on the data, I can’t see anyway to do this for individual customers at the moment

There no connection in the Keywords table where the value calculation is happening to any customer data or reference.

Is this what you had in mind?

[CorvanDalfzen]

Hi Sam,

Thanks for your response.

What you’ve calculated are the costs, what I am looking for are the profits (net-value).

Google AdWords is Google’s advertising system in which advertisers bid on certain keywords in order for their clickable ads to appear in Google’s search results.

We are paying the CPC (cost per click), the measure you used (= costs)

Unfortunately not every prospect becomes our customer so we are paying for keywords without any revenue. When a prospect becomes a customer then the prospects gets a customernumber and we send him an invoice.

So back to the datamodel:
Table Keywords: a statement with all the costs
Table Customers: a statement which keyword the customer has used in Google.
Table Invoices: at the moment the conversion has taken place, the customer receives an invoice (the revenue is the amount without VAT)

For example customer 22623536:
The keywords he used (table Customers):

I’m sorry for the keywords, I had to scramble the keyword for confidentiality.

The cost we’ve paid for each keyword (table Keywords):

Cost oontyrhsuig 3-5-17: 0,04
Cost anoienmnedm 7-7-17: 2,44

The revenue (table invoices):

On 3rd of May 2017 the customer uses the keyword “oontyrhsuig” and on the same day he received an invoice of 5,79. We have the assumption that this keyword is also responsible for the invoices until 06-07-2017. The total revenue is 104,13. On the 7th of Juli 2017 the customer uses the keyword “anoienmnedm” and he received an invoice of 4,13, the invoices after 07-07-2017 we’re calculating towards keyword “anoienmnedm”. The total revenue for this keyword is 109,10.

The profit for “oontyrhsuig” = 104,13 minus 0,04 = 104,09
The profit for “anoienmnedm” = 109,10 – 2,44 = 106,66

This in an example for one customer, I would like a report for all customers and keywords.

I know, it is complex but is it doable for DAX?

With kind regards,

Cor van Dalfzen

[sam.mckay]

At the moment I’m not finding a solution here.

Is the customer number in the invoices table at all related to the customer number in the customers table?

At the moment, I can’t see that it is, so it is presenting a bit of a problem.

It looks like this relationship is not actually doing anything from my testing

Let me know.

[CorvanDalfzen]

Hi Sam,

I’ve sent you a new file by mail.

What have I done?

• There is a new table “Prospect-Customer number”;
• I’ve merged the table Customers with this new table. In the table Customers you will now find the correct customer number.
• The customer number in the invoices table is related to the customer number in the customers table.

The new Data model:

The problem was in the old Customers-table, column Customer number. The content of this column was a prospect number or a customer number. In the merged table you will find the correct customer number. This customer number is related to the customer number in the Invoices table.

Hopefully you can help me with the formulas.

Thanks in advance,

[sam.mckay]

Hi Cor,

At the moment based on the model there is no solution here.

The Customers table which contains all keyword information has no relationship at all to the invoices table directly.

All I think you can do it try to filter them at the same time by date and customer number.

I’m a bit confused by this new table you’ve created and what it might be for?

This can’t have a relationship to anything at the moment.

The only solution I can find here is being able to filter by date across the invoices and customers table because of these relationships

Then also you need to somehow link up this customers table or a customer lookup table, to the table with the keywords in it. Like below

This is the only way.

If you don’t have these relationships working then there is just no way to calculated what you need because there is no direct relationship

Sam

[CorvanDalfzen]

Hi Sam,

I’ve made a mistake yesterday, I forgot to set a relation between Unique Customers and Customers. I am sorry for that.

I sent you a new file by mail.

What have I done?

• There is a new relationship between Unique Customers and Customers;
• I’ve removed the column Prospect or Customer number in the table Customers, we don’t need this column anymore.
• I’ve removed the table Prospect – Customer number out of the data model.

The customer number from Invoices is now connected with the Customer number from Customers through Unique Customers;
The keyword from Customers is now connected with the table Keywords through Unique Keywords.

Hopefully is this enough to make the calculations.

The new data model:

[sam.mckay]

Cor, I’m very confused as to what you’ve done.

You have create a relationship between tables Unique Customers and Customers, but there is no data in the Customernumber column???

So I’m not sure how this is meant to filter anything?

[CorvanDalfzen]

I’ve sent you the wrong document, I’m very sorry for that. You will find the correct document in your mail.

[CorvanDalfzen]

For “Correct Customernumbers” in “Customers” beginning with 1000 you will find not a relation with a customer number in “Invoices” but that doesn’t matter.

[sam.mckay]

Hi Cor,

We’ve been going round in circles on this for a while now, I’ve pretty much forgotten what you need.

Can you please give something a go yourself and then ask for advice on a specific aspect of this.

I want to stay away from doing development work services on the forum. I want the forum to be about providing education and support around what you are doing.

If there is something specific you are struggling on then I want to advise on how to fix that one thing and then hopefully direct you to the content I’ve created that can upskill you on how to fix it also.

To me it looks like the model is much better, so you will know the data better than me and can at least start on something.

What parts are you struggling with the most when looking at the updated model? This is where I can offer the most value with some advice or assistance.

Are there some formulas you have been working on?

Also have you gone through this training course?

Data Transformations & Modeling

This is absolutely key to understanding how the model works around your data.

Chrs
Sam

Also you can add your model to forum posts now.

[CorvanDalfzen]

I’m very sorry for all the confusion the last few days, please accept my deepest apologies.

The first thing I wanted was to connect 3 fact-tables with 1 lookup-table.

At this moment I’ve made this Datamodel and measures:

In the datamodel there is no direct relationship between the table Unique Keywords and the Invoice-table so I get this result::

It would be very nice to have a table with the revenues (column Total Amount excl VAT from Invoice-table) per keyword.

My question for you now:
Can I solve this with another lookup-table or do I have to solve this with a DAX-formula?