Filtering Customers based on many Marketing Campaigns RRS feed

  • Question

  • We have an existing tabular model that works great. There is a customer dimension table that joins to our sales fact table. The marketing department has kept track of every piece of marketing that they have used to contact our customers (on-site visit, call, email, mailing, etc). I want to bring in this new a new campaign table to my model then allow them to see what campaigns and campaign types were most effective. Are there any links or examples on-line I could use to pattern my solution after?

    Basically going from many (sales) to 1 (customer) to many (campaign).

    An additional item: they want to be able to see how this group performed compared to customers not in the campaign, customers in other campaigns, or a control group of customers.

    I believe this can be done with adding a Dim_Campaign table:


    Campaing ID  INT

    , Campaign_Name varchar(50)

    ,Customer_ID INT (foreign key)

    ,Campaing_Start_Date Datetime

    Or something along these lines.

    Thursday, June 2, 2016 10:34 PM


  • This is a classic many-to-many scenario. You'd want to model it as follows:

    Sales -> Customer <- BridgeCustomerCampaign -> Campaign

    Where BridgeCustomerCampaign just has CustomerID and CampaignID

    Then the Campaign table has details like CampaignID, CampaignName, CampaignStartDate

    If you are using Excel 2016 I think you can set the relationship between Customer and BridgeCustomerCampaign to be bi-directional. For earlier versions you need to change your measures to work with this bridge table, typically you wrap them in a calculate with the bridge table as the second parameter.

    =Calculate( <measure logic> , BridgeCustomerCampaign)

    There is a great whitepaper on using many to many patterns with DAX here: http://www.sqlbi.com/articles/many2many/

    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Michael Amadi Friday, June 3, 2016 10:11 PM
    • Marked as answer by Charlie Liao Thursday, June 30, 2016 2:16 AM
    Friday, June 3, 2016 3:44 AM