# Schema and/or reporting SQL for customer acquisition cost?

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.

## Calculating CAC

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.

• "I'm to understand one should not join fact tables and that doing so can lead to incorrect results." Who says? It's quite common to group a low-level fact and join it to a fact on another grain. Commented Mar 4, 2021 at 16:09
• Also: is it necessary for a customer's channel/acquisition date to match a date when money was spent on that channel? Also, would a customer who is not acquired have a channel? What does that mean within your context (quoted/shopped/browsed/etc)?
– user212533
Commented Mar 4, 2021 at 16:11
• @DavidBrowne-Microsoft my googling returned many different results that said you should not do this, and DW Toolkit also says it can lead to incorrect results due to differing cardinalities of the respective fact tables. Commented Mar 4, 2021 at 17:31
• Right. First you must perform GROUP BY to align the fact table grains. Commented Mar 4, 2021 at 18:17
• @bbaird only acquired customers show up in the hypothetic fact table. Anyone not acquired would simply not exist. Further, the likely use of this would be aggregating by week, but we would consider an acquisition on a date to be "paid for" by marketing dollars on that date. Commented Mar 4, 2021 at 20:17