2 Replies Latest reply on Sep 5, 2017 9:12 AM by Eliran Epshtein

    Join two tables by source to calculate ROI

    Eliran Epshtein

      Hello everyone,

       

      My manager asked me to create ROI time-series charts for every marketing source we use (how much we paid for every lead, appointment and contract). In order for me to do so, I need to combine two databases:

      1. This database contains all the leads, appointments, contracts from difference source by date:

      2. This Table contains all the monthly expenses by source:

       

      When I tried to join both tables by the source column,  the monthly expense was associated with each day that there was lead coming from this source.

      Is there a way to calculate the monthly ROI for each source for every lead, appointment and contract on Tableau? (or maybe calculable the daily ROI by dividing the monthly expense by the number of days but it might be an incorrect approach).

       

      Thank you in advance for you help,

      Eliran

        • 1. Re: Join two tables by source to calculate ROI
          Matt Lutton

          I'm not 100% clear on the goal, but to relate the two tables mentioned, you may want to Pivot the second source, so that your Dates are all in one column, similar to the first data source -- that way you can Join or Blend on the date, if needed.  Again, no idea if this is helpful, but let us know more about what expected results you are hoping to generate (what viz would you like to see once the data is joined).  Any mockup of expected results may help volunteers on the Forum with your request -- best wishes!

          • 2. Re: Join two tables by source to calculate ROI
            Eliran Epshtein

            Hi Matt,

             

            Thank you for trying to help.

            I see what you're saying, You want me to make the second table something like this:

            Yet, imagine if you have 40 different marketing sources and and 4 years of leads data. I'd need to create a file with 5*365*40=73,000 raws. I was wondering if Tableau has a way to aggregate the monthly leads, appointments and contracts came from source X and then divide it by a monthly expanse for this source. In this case, table 2 would look like that:

             

            My final goal is something like that: (instead of Media View I'd have appointments and then another line for contracts)