BigQuery Google Analytics Automation Question

Question

Hello: Can someone please help me with this issue.

All our campaigns begin with a date

"20200312_NEWS_....."

I want to be able to get "New User" information for a 7 day period (i. 2020-03-12 to 2020-03-19)

How can I automate this process? I have 100's of these campaigns. I know my start date because its in the campaign name. I know my end date because its just +7.

I want to do dumb it down like: Start_Date = Left(trafficSource.campaign,8) End_Date=Left(trafficSource.campaign,8) +7. But then how do i tell it to give me info of new users from Start_Date to End_Date. [enter image description here][1]

This is my very basic code:

SELECT trafficSource.campaign AS Campaign,

-- New Users (metric) COUNT(DISTINCT( CASE WHEN totals.newVisits = 1 THEN fullVisitorId ELSE NULL END )) AS New_Users

FROM Table_1.ga_sessions_*

WHERE totals.visits = 1 and _table_suffix BETWEEN '20200101' AND '20201231'

GROUP BY trafficSource.campaign

Answer 1

If I get your question correct, you are looking to extract date from the Campaign name and then trying to add 7 to it and getting all the new users

Here is something you can try

select PARSE_DATE('%Y%m%d',(SUBSTR('20200312_NEWS_SampleData',0,8))), DATE_ADD (PARSE_DATE('%Y%m%d',(SUBSTR('20200312_NEWS_SampleData',0,8))), INTERVAL 7 DAY)

Answer 2

So I get my start date and end date. I am able to parse date. But for example if the campaign start date is "2020-01-03" and end date is "2020-01-10" But the "new user" data is from "2020-01-03" to "2020-04-05". The "new user" is not giving me data from date range " "2020-01-03" to "2020-01-10"

My Code:

SELECT trafficSource.campaign AS Campaign,

Case When (SUBSTR(trafficSource.campaign,10,4)) = 'news' then PARSE_DATE('%Y%m%d',(SUBSTR(trafficSource.campaign,0,8))) end as Start_Date, Case When (SUBSTR(trafficSource.campaign,10,4)) = 'news' then DATE_ADD (PARSE_DATE('%Y%m%d',(SUBSTR(trafficSource.campaign,0,8))), INTERVAL 7 DAY) end as End_Date,

-- New Users (metric) COUNT(DISTINCT( CASE WHEN totals.newVisits = 1 THEN fullVisitorId ELSE NULL END )) AS New_Users

FROM ga_sessions_*

WHERE totals.visits = 1 and _table_suffix BETWEEN '20190101' AND '20201231'

GROUP BY trafficSource.campaign

Order By 2 Desc

enter image description here

Answer 3

I think I finally figured it out. But I wrote the worst code in the galaxy. Can someone help me make it more efficient:

Select Table_XYZ.Campaign, Table_XYZ.Start_Date, Table_XYZ.End_Date, SUM(col_1) From (

Select b. Campaign, Start_Date, End_Date ,

(Case When a.date>= b.Start_Date AND a.date<= b.End_Date then SUM(a.New_Users) End) as col_1

From TABLE_1.VW_GA_TABLE1a Left outer join TABLE_2.GA_TABLE2 b

ON a.Camp = b.Campaign

Group by b.Campaign, a.date, b.Start_Date,b.End_Date

Order By b.Campaign Desc) Table_XYZ

Group By Table_XYZ.Campaign, Table_XYZ.Start_Date, Table_XYZ.End_Date