2 Replies Latest reply on Apr 7, 2014 9:32 AM by desiree.modic

    help with MySQL data connection; joining tables (multiple tables or custom sql) and calculations

    desiree.modic

      Hey guys, I am still new to Tableau and my SQL as well as my Tableau knowledge is still very basic…

      For my master thesis though, I need to work with both. Therefore I would like to ask you guys for help.. would be really great! thanks a lot you in advance!!

      Désirée

       

      Here is what I need to calculate:

      • # trials (all photographers who signed up in a certain month)
      • # subscribers (all photographers who changed rate in a certain month)
      • Conversion from trial to subscriber
      • Revenue/subscriber and month
      • CAC
      • Months to recover CAC

       

       

      When using the multiple table option I have some problems with that data

        screenshot_connecting tables_multiple tables option.JPG

      When I try to join multiple tables (along the dimensions which are shown in the sreenshot) and then click on preview results it doesn´t show me any data.

      • what could be the problem?
      • do I need to use the Custom SQL function instead? When yes how, what would be the statement?

      When I try to use at least one table in Tableau (e.g. the rate_changes table)

          • I want to calculate the number of trials per month
          • Then I need to count the numbers of photographers (photographers_id) when is_trial = 1
          • I tried _If (Is_trial = 1) Then count (photographer_id) End_  but that doesn´t work
          • which is the right expression for the calculated field?

       

      this are my relevant tables (see above the screenshot attached of the tables joined along common dimensions)

                                                                                                                                                                                                     

      Rates

      • Id
      • Price
      • Type
      • Commission

      Rate changes

      • Id
      • Photographer_id
      • Qualification
      • Marketing_channel_id
      • Kid
      • Affiliate_id
      • Old_rate_id
      • New_rate_id
      • Created
      • Is_trial
      • Is_conversion
      • Is_churn
      • Is_upgrade
      • Is_downgrade

       

      Photographer

      • Id
      • Qualification
      • Rate_id
      • K_id
      • Marketing_channel_id
      • Created
      • Modified

      Payments

      • Id
      • Photographer_id
      • Rate_id
      • Type
      • Amount

      marketing_channels

      • id
      • name
      • description

      marketing_channel_costs

      • id
      • marketing_channel_id
      • month
      • costs

      marketing_campaigns

      • id
      • name
      • partner
      • costs


      marketing_campaign_costs

      • id
      • marketing_campaign_id
      • month
      • costs