MDW Cross Channel Table Spec: mv_cross_channel_v4
mv_cross_channel_v4
Term | Definition | DataType |
Day | Date | Date |
Month | Month | Date |
Channel | Channel as defined by taxonomy rules | varchar(500) |
Segment | Segment as defined by taxonomy rules | varchar(500) |
Tactic | Tactic as defined by taxonomy rules | varchar(500) |
Conversion_type | Conversion type (e.g. online, offline, add to cart, etc.) | varchar(500) |
Impressions | Number of impressions | float8 |
Clicks | Number of clicks | float8 |
Spend | Amount spent | float8 |
Orders_g | Last touch orders | float8 |
Demand_g | Last touch revenue | float8 |
Perc_inc | Incrementality | float8 |
Inc_ct | Incrementality - Click Through | float8 |
Inc_vt | Incrementality - View Through | float8 |
Orders_i | Incremental orders | float8 |
Demand_i | Incremental revenue | float8 |
Orders_ct | Click through orders | float8 |
Demand_ct | Click through revenue | float8 |
Orders_vt | View through orders | float8 |
Demand_vt | View through revenue | float8 |
Derived_dimen_1 | This column is determined by the CCMAF spec | varchar(3000) |
Derived_dimen_2 | This column is determined by the CCMAF spec | varchar(3000) |
Derived_dimen_3 | This column is determined by the CCMAF spec | varchar(3000) |
Derived_dimen_4 | This column is determined by the CCMAF spec | varchar(3000) |
Derived_dimen_5 | This column is determined by the CCMAF spec | varchar(3000) |
Net_sales_i | demand_i * (1 - net_sales_deduction) | float8 |
Net_orders_i | orders_i * (net_sales_deduction + cost_of_goods) | float8 |
Gross_margin_i | (demand_i * (1 - net_sales_deduction) * (1 - cost_of_goods) * (1 - include_variable_cost * (variable_cost_perc))) | float8 |
Net_profit_i | gross_margin_i - total_spend | float8 |
Net_profit_i_per_dollar | (gross_margin_i - total_spend) / total_spend | float8 |
Ad_per_net_profit_i | total_spend/(gross_margin_i - total_spend) | float8 |
Ad_per_sales | total_spend / net_sales_i | float8 |
Creative_cost | creative.weighted_cost_sum * spend / channel_spend | float8 |
Agency_cost | Media.weighted_cost_sum * spend / channel_spend | float8 |
Variable_cost | Demand_i * variable_cost_percentage | float8 |
Total_spend | Total spend | float8 |
Total_conversions | Total conversions | float8 |
Total_revenue | Total revenue | float8 |
Use Case Scenario
select to_char(month, 'MonthYYYY') "month", channel, segment, tactic, sum(spend) spend, sum(orders_g) conversions, sum(demand_g) revenue, sum(orders_i) conversions_i, sum(demand_i) revenue_i
from mv_cross_channel_v4
where conversion_type='Online Orders'
group by "month", channel, segment, tactic
