1 Reply Latest reply on Oct 30, 2017 10:46 AM by patrick.byrne.0

    Joins with multiple metrics for sales rep report

    jessica Karels

      Hi all,

       

      So far I've been doing great at building dashboards and reports focused on data from one "metric" table, but I'm stuck now that I'm trying to handle multiple tables.

       

      Here's the data layout

       

      [In SQL Database]

      Table: SalesRepDemographics (~10k records)

      • SalesRepID
      • Rep Territory
      • Rep Status (active, terminated, etc)
      • (Other rep demographic data)
      • FileDate -> This table keeps getting appended to with the most recent data for the sales reps. When I do SQL queries I usually run "Where FileDate = SELECT MAX(FileDate)...".

       

      Table: SalesData (~300k records)

      • SalesRep ID (same as "SalesRepID" in SalesRepDemographics table)
      • Account Date
      • Amount
      • Lots of other columns of "nice to know" data

       

      Table: ContactRecords (~350k records)

      • Rep ID (same as "SalesRepID" in SalesRepDemographics table)
      • Contacter Group (who contacted the sales rep?)
      • Activity Type
      • Activity Status
      • Activity Date
      • Lots of other columns of "nice to know" data

       

      Table: SalesPipelines (~50k records)

      • Rep ID  (same as "SalesRepID" in SalesRepDemographics table)
      • Pipeline Created Date
      • Pipeline Status (Open, Closed/Won, Closed/Lost)

       

      [In Excel Sheet]

      SpecialEventAttendees

      • SalesRepID
      • Attendee Status
      • Event Name
      • Event Date

       

      I'm working on a pre-post event analysis where I'm comparing the pre-event sales & activity to the post-event. I have calculated fields to determine the "pre-event" measuring period (parameter for # of months look-back).

       

      The Sales Rep's ID is the common link between the various tables. I'm doing a left-join between the SalesRepDemographics table and the data, records, and pipelines table. I'm then doing an inner join between the SalesRepDemographics table and the SpecialEventAttendees table since I only want to focus on those who attended the event.

       

      The challenge I'm having is how to join on date (or if I should join on date).  I was able to set everything up focusing on the sales data, and Tableau ran fairly quickly. When I try to add in the other tables, there's a slow-down. When I saw that Tableau was trying to load tens of millions of records, I realized there was something wrong with my joins setup.