3410_MKT_BAS_multi_channel_transactions Transformation

Target Database Table


Script Description and Logic

Pulls a series of metrics from ga.base_multi_channel and ga.base_multi_channel_funnel for multi touch marketing channel attribution. Metrics include first and last nodes for campaign, source, and medium, first and last click channel grouping, channel, vendor, date and days between, as well as number of touches.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
ga base_multi_channel_funnel database Primary
ga base_multi_channel database Primary
ga base_transactions database Primary
N/A touches derived Counts number of touches in GA multi channel funnel
drp_staging multi_channel_transactions_to_update database Primary

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

 3410_MKT_BAS_multi_channel_transactions Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
first_click_date TIMESTAMP ga.base_multi_channel created_on DATE(dateadd(day,-CAST(mc.time_lag_in_days_histogram AS INT),mc.created_on))
number_touches BIGINT ga.base_multi_channel_funnel sequence CASE WHEN t.num_touches IS NULL THEN 0 ELSE t.num_touches END

All content © Daasity 2021. Do not copy, share or distribute.