title: Best practices for fact table that depends on two processes
body: <p>I am building a star schema for an online business. One of the key processes is email newsletter signup.</p>
  
  <p>But the analysis depends on two processes and I can't figure out how to model it the best way.</p>
  
  <p><strong>Here's how the process works:</strong></p>
  
  <ul>
  <li>Person visits website</li>
  <li>Person fills out web form and is recorded as a contact in our CRM</li>
  <li>Person receives a link asking him to confirm if this is really his email</li>
  <li>Person clicks the link and is considered confirmed</li>
  <li>Person can now receive emails from us</li>
  </ul>
  
  <p>The signup and confirmation process take place at different times. Most people click the confirmation link on the same day, but we send two follow up email over a few days after the signup so some people may confirm their email only after a few days.</p>
  
  <p>On top of that a person could signup several times on the website. Most of our signups are people who exchange their email address in return for some sort of resource like an eBook.</p>
  
  <p>As long as the person's email is not marked confirmed in our system, we ask the person to confirm on each signup.</p>
  
  <p>Since we have multiple offers it's not uncommon for a person to request eBook A, eBook B and eBook C and only confirm after several signups.</p>
  
  <p>In the fact table each signup for emails that are unconfirmed yet is marked as ConfirmationRequested -> True.</p>
  
  <p>If the person clicks a confirmation link of ANY of the confirmation request emails he should be considered confirmed for each of those signups.</p>
  
  <p><strong>How I want to analyse the data</strong></p>
  
  <ul>
  <li>See how many signups we had</li>
  <li>See how many signups were re-signups and how many were new contacts in the CRM (new email address)</li>
  <li>See how many new contacts have confirmed their email address (and become full subscribers)</li>
  <li>See how many re-signups were asked to confirm their email and how many have done so</li>
  <li>Analyse how long it takes for people to confirm their email address</li>
  <li>Analyse the confirmation rate</li>
  <li>Filter contacts by their confirmation status and analyse what people who have or have not confirmed have in common</li>
  </ul>
  
  <p>I don't really care about confirmations in isolation from signups.</p>
  
  <p>And for my purposes I would like to have a ConfirmationStatus dimension that is...</p>
  
  <ul>
  <li>"Confirmed" if the person confirms within 7 days of sign up</li>
  <li>"Pending" if the person hasn't confirmed, but 7 days haven't passed since signup yet</li>
  <li>"Not Confirmed" if the person hasn't confirmed within 7 days (even if the person does confirm at some later point)</li>
  </ul>
  
  <p>On top of that I usually look at this report on Mondays to analyse the previous week and compare it to other weeks. (I already have a working version of this report in a flat table, but I am trying to learn how to build proper star schemas.)</p>
  
  <p>This has the additional challenge that contacts that signed up on Sunday for example only had less than a day to confirm and would drag down the confirmation rate and the latest week would look bad if compared to previous week where all contacts had the full 7 days to confirm.</p>
  
  <p>So I calculate a "Confirmed within signup week" confirmation count and rate for all weeks to allow apples to apples comparisons.</p>
  
  <p><strong>How to model this...</strong></p>
  
  <p>I have considered the following options...</p>
  
  <p><strong>Option #1: Separate fact tables</strong>
  Since these are separate processes that happen at separate times I have learned that I should create separate fact tables and then drill across common dimensions.</p>
  
  <p>I could calculate signups that requested confirmations from the signup table and then calculate confirmations within a week of the signup through the contact and date dimensions.</p>
  
  <p>But that wouldn't allow me to filter the signups by confirmation status.</p>
  
  <p>That's why I am considering...</p>
  
  <p><strong>Option 2: A fact table that combines both signups and confirmations</strong></p>
  
  <p>I am thinking of something like this:</p>
  
  <pre><code>| Dim Signup Info       |      |   | Dim Contact |      |   | Fact Signups         |    |
  |-----------------------|------|---|-------------|------|---|----------------------|----|
  | SignupInfoKey         | SK   |   | ContactKey  | SK   |   | SignupDateKey        | FK |
  | SignupType            | SCD1 |   | Name        | SCD1 |   | ConfirmationDate     | FK |
  | ConfirmationRequested | SCD1 |   | Email       | SCD1 |   | SignupInfoKey        | FK |
  | ConfirmationSucceeded | SCD1 |   | ...         |      |   | ContactKey           | FK |
  | ConfirmationStatus    | SCD1 |   |             |      |   | SignupId             | DD |
  |                       |      |   |             |      |   | SignupDateTime       | DD |
  |                       |      |   |             |      |   | ConfirmationDateTime | DD |
  |                       |      |   |             |      |   | Signups              | M  |
  |                       |      |   |             |      |   | NewContacts          | M  |
  |                       |      |   |             |      |   | ConfirmationMin      | M  |
  |                       |      |   |             |      |   | ConfirmationDays     | M  |
  </code></pre>
  
  <p>I need the ConfirmationDate in the fact to calculate the "Confirmed Within Week" measures at report time (I am using powerbi and it's easy there). I could of course also create a dimension "ConfirmedWIthinWeek" and then filter based on that, but it won't be as flexible... What if I decide later on to look at the data on a daily or monthly basis for example?</p>
  
  <p>Another concern is that it will require to reprocess and update the fact tables on each incremental load for the past 7 days.</p>
  
  <p>I know that's ok for dimensions, but is that ok for fact tables too?</p>
  
  <p><strong>So my questions are</strong></p>
  
  <ul>
  <li>Is option #2 a good solution or is there a better way to do this?</li>
  <li>Is it ok to update fact tables or is that discouraged?</li>
  </ul>
  
  <p>Overall my question is: What am I missing?</p>
  
  <p>This seems like a very common thing. One obvious example would be an order star that has fact table columns for AmountOrdered, AmountPaid, AmountRefunded and dimensions like "Order Status", "Paid Status" and "Refunded Status".</p>
  
  <p>But none of my searches have resulted in answers to this common problem. Surely there must be a term for the problem and a pattern name for the solution where I can learn more about it?</p>