3 Replies Latest reply on Jan 30, 2019 2:49 PM by William Rielly

    Equal Attribution

    Tim Sunzeri

      I've attached a sample workbook, the data has 2 fields, Campaign and Customer ID. I'm trying to equally attribute customers to campaigns based on the # of emails they opened before making a purchase. If a customer opened 4 emails before their purchase (e.g. email1, email2, email3, & email4), each email would be credited with attracting 0.25 customers. I then want to sum up the total customers each campaign is credited with, but the amount being attributed to each campaign will vary by customer. For example, if customer A opened 4 emails (email1, email2, email3, & email4), email1 will be credited with 0.25 customers, but if customer B only opened 2 emails (email1 & email2), email1 will be credited with 0.50 customers, if these were the only 2 customers to open email1 that campaign would be credited with 0.75 customers. I've included a screenshot of what the totals should look like for every campaign, but can't seem to write a calculated field in Tableau to replicate. Any help is greatly appreciated, thanks!

       

       

      Data example

       

      What the customer totals should be for each campaign based on the screenshot above.

        • 1. Re: Equal Attribution
          William Rielly

          Hello Tim,

           

          Please try creating the following calculated field and call it 'Total Customers':

           

          { FIXED [Customer ID]: 1 / COUNTD([Campaign])}

           

          Doing this I was able to get your totals to match your example (except for the first entry where you may have a typo?).

           

          Thanks,

           

          Will

          • 2. Re: Equal Attribution
            Tim Sunzeri

            Thanks Will! I was hoping to count duplicate opens, so customer1 emails would all be credited with 0.2 customers, but since customer1 opened email1 twice, email1 would be credited 0.4 customers from this customer. Do you know how that could be calculated? I tried changing countd to count in the formula you provided but then the total for each campaign only sums to 3.8 instead of 4.0 (it should total 4 since there are 4 unique customers. Thanks!

            • 3. Re: Equal Attribution
              William Rielly

              Ah, Ok.  I think I understand what you are saying.  Yes, after changing to COUNT from COUNTD, all the totals match except for the first row because of excluding the double entry of  customer1 opening email one twice.

               

              I am not sure how to quickly also incorporate this use case into the LOD calculation.

               

              Thanks,

               

              Will