0

This a design question around creating fact/dimension tables to query customer acquisition cost and how to properly model the tables and query them.

Business Rule

  • 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.

9
  • "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. Mar 4 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)?
    – bbaird
    Mar 4 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. Mar 4 at 17:31
  • Right. First you must perform GROUP BY to align the fact table grains. Mar 4 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. Mar 4 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.