7 Replies Latest reply on Jan 23, 2017 1:25 PM by Jamieson Christian

    Dealing with Split Records

    Amit Goldstein

      @@@@@Hello Everyone,

       

      My first post at Community!

       

      I’m dealing with a tricky dataset that revolves around deals flowing from our CRM system. Some of the deals are split between two teams. In some cases there is a team name mentioned on one of the records of a certain split deal, but not on the other. Every deal has revenue associated with it, however not all revenue is ‘impacted’ by the deal team (depends on the activities they take, for the purposes of this example the data is already provided). When a deal is impacted the revenue counts as ‘Revenue - Team Impact’. When a deal is not impacted it does not count (zero or Null value under ‘Revenue - Team Impact’). Split deals provide an immense challenge since the entire revenue needs to be assigned to the deal team if impact is positive. In addition the deal team should be named on both split records (Again, if there is an impact). In the past I used to do this manually looking at Excel reports. However now the data is fed directly to Tableau and I’m not able to fully automate these actions.

       

      I’ve attached a spreadsheet with dummy data to better illustrate my challenge. There are two tabs. In ‘What it is’ you can see how the data looks coming from the CRM. In ‘What it Should Be’ tab you can see the changes I’m trying to apply with calculated fields (highlighted).

       

      What I’ve done so far:

      • In most cases I was able to match each split record with its counterpart using a calculated field with a lookup function. Some deal records occupy two rows which puts a stick in my lookup formula.
      • Separated the split % from the deal name as its own field
      • Created a simple calc for Revenue - Team Impact that does not take split deals into account

       

      Where I need help - applying the following logic:

       

      1. If a team appears on one split record and impact is positive, and the other record has no impact and no team name then the team name should appear on the other split record.
      2. Impact for that second record should be changed from no impact to ‘impact’.
      3. Revenue - Team Impact should be changed to the appropriate amount based on the split %.
      4. If two split records have different team names and both have impact do nothing.
      5. If two split records have same team name and both have impact do nothing.
      6. If one record has a team name but no impact, do nothing.

       

      I see this logic implemented into three fields: Deal Team Name, Deal Team Impact, Revenue - Team Impact

      However anything else that works is welcomed.

       

       

      Nicholas Abbott - Saw your post from a couple of years ago about the same topic. Where you able to figure out an answer?

       

      Thank you for your patience if you made it this far!

       

      Appreciate any advice.

       

      Amit

        • 1. Re: Dealing with Split Records
          Jamieson Christian

          Amit,

           

          I'm looking at the workbook now. This all seems to hinge around being able to identify deal records that refer to the same deal — a challenge because the Deal Name is different in each line. Worse, it's represented one of 3 different ways ("Split XX%", "XX% Split", and "Split 50/50" are the ones I see). So the biggest deal (haha) is to get a "normalized" deal name that you can then use for LOD calculations to see what's going on in the deal holistically.

           

          I'm going to try to put something together, but let me know if I'm on the wrong track.

          • 2. Re: Dealing with Split Records
            Amit Goldstein

            Hi Jamieson,

             

            First off thanks for jumping on this so quickly.

             

            Yes the deal name is not consistent, this is what happens when CRM admin gives sales people flexibility in assigning or changing a name. I started down this route and created a calculated field to generate one name for both split records:

             

            IF [Split Opp Check]="Split"

            AND FIND(LOWER([Deal Name]),"split") < FIND(LOWER([Deal Name]),"%")

            THEN Left(LOWER([Deal Name]),FIND(LOWER([Deal Name]),"split")-3)

            ELSE Left(LOWER([Deal Name]),FIND(LOWER([Deal Name]),"%")-3)

            END

             

            However I then found it much simpler to use the account name to figure out whether to match two splits. I'm not really looking to consolidate two split records, just assign similar attributes to the Deal Team, Deal Team Impact and Revenue - Team Impact fields.

             

            Thanks,

            Amit

            • 3. Re: Dealing with Split Records
              Jamieson Christian

              Well, too late. I already put together a REGEX expression to strip out the "Split" element of the Deal Name.

               

              Attached is my stab at what you're trying to do. The second tab (the one I added) should look identical to your "What It Should Be" tab with a couple exceptions:

              • Because I normalized the Deal Name, the visual grouping is formatted differently.
              • Your "What It Should Be" had an Alpha team under the Migas deal, but I couldn't find that in the "What It Is" data, so my output reflects that Alpha wasn't involved. That's the only discrepancy between the two views.

               

              Was that what you're looking for?

               

              New fields created (check them out):

              • Deal Name (split removed)
              • Deal Team (impact only)
              • Overall Deal Team
              • Overall Impact
              1 of 1 people found this helpful
              • 4. Re: Dealing with Split Records
                Amit Goldstein

                Nice!

                 

                It works great for the dummy data (that Alpha team was just an error from the spreadsheet).

                 

                When I tried implementing your calcs in my actual workbook it did not provide the expected results since deal/opportunity names are even more complex. For example some have lower cap 'split', others 'Split'. Some don't even say split, just the %. Some have split before the %, others opposite. Some have other differentiators within the string, such as sales rep name or region name. The account is the same though for both split records. Is there anyway I can take advantage of that?

                Also, is there a good place you'd recommend to learn/understand REGEXP syntax?

                 

                Thanks Jamieson!

                Amit

                • 5. Re: Dealing with Split Records
                  Jamieson Christian

                  Amit,

                   

                  You can replace [Deal Name (split removed)] with [Account Name] in my formulas only if there is at most 1 deal per [Account Name]. Otherwise, every deal for that account will get lumped together when running the LOD calculations.

                   

                  The key to all of this is ensuring that you have some way of uniquely identifying a deal in order to detect all of the records that relate to that deal. If you have a [Deal ID] you could bring in, that would be best. If [Account Name] can server that purpose, then you're good to go. Otherwise, you probably have no option except to clean up [Deal Name] to make it function as a unique key.

                   

                  You can try to use table calculations to figure out what deals relate to each other, but that is a very tenuous construction that I would not recommend.

                   

                  Regarding REGEXP… a good starting point for learning regular expression syntax may be Regular-Expressions.info - Regex Tutorial, Examples and Reference - Regexp Patterns . There are probably even better tutorials out there, but that's a place to start. Google "regular expressions tutorial" to find more content from other sites.

                  1 of 1 people found this helpful
                  • 6. Re: Dealing with Split Records
                    Amit Goldstein

                    Wanted to post back here with the solution in case someone will need something similar in the future.

                     

                    Jamieson's approach was extremely helpful but when trying to implement it on the real dataset it did not work since the naming conventions of opportunities are so varied (you could say there are no conventions at all). I had to complement his suggestions with another calculated field instead of the regexp one.

                     

                    I used a combination of IF, CONTAINS and FIND to look for specific characters and phrases that appear in deal names, for example: '-', '%', 'split', product names. These appear in somewhat random order and I had to use logic to figure out which one is first in each deal name and then apply the LEFT function to strip the rest of the deal name following the first special character I find. Here's the syntax:

                     

                    IF [Split Opp Check]="Split" AND CONTAINS([Opp Name],"-") AND FIND(([Opp Name]),"-")<FIND(LOWER([Opp Name]),"product1")

                    THEN Left(LOWER([Opp Name]),FIND(([Opp Name]),"-")-1)

                     

                    ELSEIF [Split Opp Check]="Split" AND CONTAINS(LOWER([Opp Name]),"product1") AND FIND(LOWER([Opp Name]),"product1")<FIND(LOWER([Opp Name]),"split")

                    THEN Left(LOWER([Opp Name]),FIND(LOWER([Opp Name]),"product1")-1)

                     

                    ELSEIF [Split Opp Check]="Split" AND FIND(LOWER([Opp Name]),"split") < FIND(LOWER([Opp Name]),"%")

                    THEN Left(LOWER([Opp Name]),FIND(LOWER([Opp Name]),"split")-3)

                     

                    ELSEIF [Split Opp Check]="Split" AND CONTAINS([Opp Name],"%") AND REGEXP_MATCH(lower([Opp Name]),'(\.[0-9]+%)')

                    THEN Left(LOWER([Opp Name]),FIND(LOWER([Opp Name]),"%")-5)

                     

                    ELSEIF [Split Opp Check]="Split" AND CONTAINS([Opp Name],"%") AND REGEXP_MATCH(lower([Opp Name]),'([0-9]+%)')

                    THEN Left(LOWER([Opp Name]),FIND(LOWER([Opp Name]),"%")-3)

                     

                    This calculated field enabled me to normalized the vast majority of opportunity names and use the other calculated fields Jamieson created to determine whether to apply impact based on the parallel split record.

                     

                    With other datasets you might need to apply a different approach, all depending on the naming conventions of records you want to consolidate.

                     

                    Really great to experience the power of community in solving this problem. Appreciate the help Jamieson.

                    • 7. Re: Dealing with Split Records
                      Jamieson Christian

                      Amit,

                       

                      Thanks for posting an update! I'm glad you were able to find an approach to cleaning up the deal names that works for your purposes. Hopefully you can drive more consistency within your organization to make the cleanup easier in the future.

                       

                      It just goes to show how critical Data Prep is to enabling robust reporting and visualization.

                       

                      Happy viz-ing!