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.
|23||Email Order Value||£906.45|
|23||Organic Order Value||£451.83|
|24||Email Order Value||£832.89|
|24||Organic Order Value||£962.89|
|25||Email Order Value||£472.53|
|25||Organic Order Value||£580.52|
|26||Email Order Value||£109.89|
|26||Organic Order Value||£328.40|
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?
I figured it out, the week number on GA was being turned into an actual data rather than 'Week 47' etc. After changing it from 'Date Part' to 'Date Value' the join worked correctly.