2 Replies Latest reply on Aug 30, 2018 1:35 AM by Khoma Usire

    Correlation across sources

    Khoma Usire



      I'm trying to create a dashboard where we could see how investments correlate to multiple awareness metrics. Ideally I'd be able to filter the investments.


      I've run into a couple of problems:

      • Investments and Awareness data are in different sources
          • The CORR() function refuses to work that way
          • Investments aren't differentiated into colors correctly, when overlayed with Dual Axis from the Awareness source
      • The data structure will always be completely different. I suspect I have to work with Level of Detail in some way to make CORR() work with it?
          • Investments are a detailed list of particular investments
          • Awareness are listed in monthly shares
      • I want to see hundreds of combinations without creating hundreds of CORR() fields
          • I've seen correlation matrix work, but the data can't always be pivoted. Is there any way to see multitude of relationships without pivoting and hours of repetitive coding?
      • Investment from a month affect awareness of the next month. How do I shift the months in Investment values?



      I know I'm asking a lot, but if even one problem gets solved here, it will save me a lot of time next time I'll be working my way through it.



      Thanks and bless all who try to help,



        • 1. Re: Correlation across sources
          Sasha Hanna

          Hi Khoma,

          Blending will not allow you to obtain the correlation coefficient using a calculated field, you could join the data sets together if that's the objective.


          In the current state, to investigate the correlation between the cost and the other awareness metrics, you can create multiple scatter charts and add trend-lines from the analytics pane to the same worksheet. The set-up would look something like the following:

          I noticed you are blending the data on date. This raises a question are you trying to investigate the relationship between the metrics based on the month year values? Or is it media type? If it is the latter, you will need to blend/link the data at that level too!


          Hope this helps!



          • 2. Re: Correlation across sources
            Khoma Usire

            Hi Sasha, thank you for your answer.


            The sources are not blended through media type because awareness is not mediatype-specific.


            The relationship is through date because he correlation should basically say "when we spent more on media, this and this happened with awareness".



            I'll try and merge them together ^^