1 Reply Latest reply on Nov 24, 2016 2:05 AM by Filipe Hemsworth

    Joining Google Analytics data to an Excel document by Week Number

    Filipe Hemsworth

      Hello everyone!

       

      I have recently been having some issues with the 'Organic' traffic visiting our website, whereby Google are reporting a large number of customers who have visited as 'Referral' traffic rather than 'Organic' due to some tagging errors on our site. To temporarily get around this problem I am having to create a report from Google Analytics which shows all of our 'Referral' revenue, that started off as an 'Organic' source. I then add this value on to the 'Organic' figure being under-reported by GA.

       

      I have done this manually up until now, however in an attempt to automate much of our reporting, I am trying to create a simple database of all our historical 'Organic Referral' traffic in an excel file. I would like to join this table up to my Google Analytics data by the week number, and in a calculated field specify when to add the value to the GA figure.

       

      Here are some lines of the 'Organic Referral' data that is to be joined to my GA data.

        

      WeekCategoryValue
      23Email Order Value£906.45
      23Email Orders24
      23Organic Order Value£451.83
      23Organic Orders13
      24Email Order Value£832.89
      24Email Orders21
      24Organic Order Value£962.89
      24Organic Orders27
      25Email Order Value£472.53
      25Email Orders12
      25Organic Order Value£580.52
      25Organic Orders16
      26Email Order Value£109.89
      26Email Orders3
      26Organic Order Value£328.40
      26Organic Orders9

       

       

      I attempted it myself with the following code, however unfortunately no results are being displayed:

       

      IF ATTR([Sheet1 (Organic Referrals)].[Category]) = "Email Order Value"

      THEN sum([Transactions])+SUM([Sheet1 (Organic Referrals)].[Value]) end

       

      I think i may be potentially going wrong with the join, whereby in the 'Edit Relationships' window I am joining the WEEK(Day) on to the Week column.

       

      How would I go about getting this to work correctly?

       

      Many thanks.