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.

Improve this question
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.
    – David Browne - Microsoft
    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
    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.
    – Dave Copeland
    Mar 4, 2021 at 17:31
  • Right. First you must perform GROUP BY to align the fact table grains.
    – David Browne - Microsoft
    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.
    – Dave Copeland
    Mar 4, 2021 at 20:17

0

Reset to default

Your Answer

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