This a design question around creating fact/dimension tables to query customer acquisition cost and how to properly model the tables and query them.
- We spend some money per day per channel. For example, we spend $500 yesterday with Facebook, $400 with Google. Day before yesterday we may have spent $600 with Facebook and $200 with Google.
- Each day some number of customers are acquired and we can attribute the channel at the time of acquisition
- We assume that acquisitions from a paid channel are the result of that day's payments
For example, if we paid per the examples above, and suppose yesterday we had 10 customers from Facebook and 5 from Google, yesterday's Customer Acquisition Cost (CAC) would be
(500 + 400) / (10 + 5) or $60
For the sake of this question, let's not quibble over the "correctness" of these rules.
The dimensional model where I'm getting stuck
I am new to dimensional modeling, but my understanding is facts represent business things that happen, so I'd expect something like so:
fact_acquisition ---------------- acquired -- 0 or 1 customer_id -- ref to customer dimension channel_id -- ref to acquisition channel dimension date_id -- ref to a date dimension as to when this happend
I'd also expect some sort of marketing spend fact:
fact_marketing_spend -------------------- amount channel_id date_id
From this, it's clear to me how to calculate something like spend-per channel or acquisitions per day.
To calculate CAC in this schema, it seems I'd need to join the two fact tables somehow (by
channel_id) and use aggregate functions to aggregate acquisitions per day over spend per day per channel. I'm confident I could write SQL for that, but, here is my question:
I'm to understand one should not join fact tables and that doing so can lead to incorrect results. So how can I produce the needed values for CAC per channel? Do I write separate queries and do the aggregation inside my reporting tool (e.g. Excel)?
I have googled extensively to find an example schema for this and have looked at the relevant parts of The Data Warehouse Toolkit, and am at a loss.