Data Studio Data Connector Setup to GA4 [2.1.0]

Introduction

Data Studio is used to visualize and understand the key areas in the data .You can able to connect the GA4 account to Data Studio using this chapter Data Studio to GA4 Account Linkage [2.1.0].

Custom Fields

Data Studio provides default fields along with custom fields which are set up in GA4 account. However, additional custom fields are required to visualise the date accurately. The following are the additional custom fields.

TimeStamp_clientDT setup

TimeStamp_clientID is a custom field added to your DataStudio fields in order to change the Unix time stamp into readable format .This TimeStamp_clientDT is used for further analysis.

1. Click on Add a field button to create the TimeStamp_clientID field.

   

   

2. Enter the name of the field as TimeStamp_clientDT in the field name section and formula which is provided in the below code snippet.
   
   PARSE_DATETIME("%s",SUBSTR(clientDT,1,10))

3. Select Save in the bottom-right corner of the page.

4. Select Close option in the bottom-right corner of the page.

Make field setup

1. Click on Add a field button to create the Make field.

2. Enter the name of the field as Make in the field name section and formula which is provided in the below code snippet. 

case
when a344Manufacturer = "Samsung" then "Samsung"
when a344Manufacturer = "Sony" then "Sony"
when a344Manufacturer = "LG" then "LG"
else "Others"
end

ad_sort_order setup

1. Click on Add a field button to create the ad_sort_order field. This field is added to sort the Ads order ascendingly.

2. Enter the name of the field as ad_sort_order in the field name section and formula which is provided in the below code snippet.

   CASE
     WHEN REGEXP_MATCH(Event name, "ad_exception") THEN 8
     WHEN REGEXP_MATCH(Event name, "ad_end") THEN 7
     WHEN REGEXP_MATCH(Event name, "ad_completed") THEN 6
     WHEN REGEXP_MATCH(Event name, "ad_complete") THEN 6
     WHEN REGEXP_MATCH(Event name, "ad_third_quartile") THEN 5
     WHEN REGEXP_MATCH(Event name, "ad_midpoint") THEN 4
     WHEN REGEXP_MATCH(Event name, "ad_first_quartile") THEN 3
     WHEN REGEXP_MATCH(Event name, "ad_progress") THEN 2
     WHEN REGEXP_MATCH(Event name, "ad_start") THEN 1
     WHEN REGEXP_MATCH(Event name, "ad_start_tag") THEN 1
     WHEN REGEXP_MATCH(Event name, "ad_loaded") THEN 1
     ELSE 0
   end
   

    

vod_sort_order setup

1. Click on Add a field button to create the vod_sort_order field. This field is added to sort the Vods order ascendingly.

2. Enter the name of the field as vod_sort_order in the field name section and formula which is provided in the below code snippet.

   CASE
     WHEN REGEXP_MATCH(Event name, "vod_exception") THEN 10
     WHEN REGEXP_MATCH(Event name, "vod_stop") THEN 9
     WHEN REGEXP_MATCH(Event name, "vod_paused") THEN 8
     WHEN REGEXP_MATCH(Event name, "vod_skip") THEN 7
     WHEN REGEXP_MATCH(Event name, "vod_completed") THEN 6
     WHEN REGEXP_MATCH(Event name, "vod_third_quartile") THEN 5
     WHEN REGEXP_MATCH(Event name, "vod_midpoint") THEN 4
     WHEN REGEXP_MATCH(Event name, "vod_first_quartile") THEN 3
     WHEN REGEXP_MATCH(Event name, "vod_start") THEN 2
     WHEN REGEXP_MATCH(Event name, "vod_loaded") THEN 1
     ELSE 0
   end
   

    

hour_timestamp

1. Click on Add a field button to create the hour_timestamp field. This field is used to categorize timestamp into zones by hour.

2. Enter the name of the field as hour_timestamp in the field name section and formula which is provided in the below code snippet.

   HOUR(timestamp_clientDT,'RFC_1123')

period

1. Click on Add a field button to create the period field.

2. Enter the name of the field as period in the field name section and formula which is provided in the below code snippet.

   case
   when hour_timestamp >= 5 and hour_timestamp <= 12  then "Morning"
   when hour_timestamp >= 12 and hour_timestamp <= 17 then "Afternoon"
   when hour_timestamp >= 17 and hour_timestamp <= 21 then "Evening"
   when hour_timestamp >= 21 then "Night"
   end

 

period-order

1. Click on Add a field button to create the period-order field.

2. Enter the name of the field as period-order in the field name section and formula which is provided in the below code snippet.

   case
   when period = "Morning" then 1
   when period = "Afternoon" then 2
   when period = "Evening" then 3
   when period = "Night" then 4
   else 5
   end

 

vod-events

1. Click on Add a field button to create the vod-events field. This field is used to filter out necessary vod events.

2. Enter the name of the field as vod-events in the field name section and formula which is provided in the below code snippet.

   case
   when Event name = "vod_start" then "vod_start"
   when Event name = "vod_first_quartile" then "vod_first_quartile"
   when Event name = "vod_midpoint" then "vod_midpoint"
   when Event name = "vod_third_quartile" then "vod_third_quartile"
   when Event name = "vod_completed" then "vod_completed"
   else "misc"
   end

ad-events

1. Click on Add a field button to create the ad-events field. This field is used to filter out necessary ad events.

2. Enter the name of the field as ad-events in the field name section and formula which is provided in the below code snippet.

   case
   when Event name = "ad_start" then "ad_start"
   when Event name = "ad_first_quartile" then "ad_first_quartile"
   when Event name = "ad_midpoint" then "ad_midpoint"
   when Event name = "ad_third_quartile" then "ad_third_quartile"
   when Event name = "ad_completed" then "ad_completed"
   else "misc"
   end