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)
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])
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.
Information about customernumber – invoicedate – Total Amount without VAT.
- How can I connect multiple fact-tables with each other? Do I have to make a new dimensiontable with CROSSJOIN(values(….); values (….) ; values (….)?
- I want a table with:
Date – CustomerNumber – Keyword – CPC – Invoice Amount excl. VAT – Diff. CPC and Invoice
- 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,