ePrivacy and GPDR Cookie Consent by Cookie Consent Skip to main content

Documentation for Reporting - Attributes and Data Prep

Web banner and SmartEmailing reporting

This is a list of attributes and tables required as the standard setup for reporting dashboards in CDP. Web banner/Email metrics like customers’ interactions and impressions are first calculated as attributes. In MI, these attributes are aggregated and used to prepare the necessary tables. Metabase in CDP will then parse the tables and display them on the reporting dashboards.

Attributes

Attributes are list compound type. In general, the dimensions are datetime, campaign ids and utm parameters so that metrics can be categorized accordingly.

—----------------------------------------------------------------------------------------------------------------------------

All campaigns received 
All campaigns opened
All campaigns clicked

Dimensions: 

  • Datetime
  • Automation ID, Automation Name, Node ID, Node Name -> campaign information
  • UTM Campaign, Source, Medium, Term, Content

—----------------------------------------------------------------------------------------------------------------------------

All web banner impressions
All web banner clicks

Dimensions: 

  • Datetime
  • Banner ID
  • Banner Name

—----------------------------------------------------------------------------------------------------------------------------

All order transactions

Dimensions: 

  • Datetime
  • Price -> revenue conversion
  • Order ID / Product ID & Product name

Reporting Tables

These are the tables which the Metabase template is built on. Please strictly follow the table names and column names when creating these tables in your clients’ instance. It is ok to have additional columns if the columns in these tables are not enough for your use case.

se_campaign_conversion_window 

for dashboard attribution window filters

id

conversion_window

1

Same session (1 hour)

2

Direct (1 day)

3

Assisted (1 week)


se_campaign_all

Aggregated campaign data

column_name

description

automation_id

Email campaign ID from EDM platform 

automation_name

Email campaign name - human friendly

utm_medium

utm_source

utm_content

utm_campaign

utm_term

start_date

Start date of email campaign - min(event_time) of campaign_received 

end_date

Last date of email campaign - max(event_time) of campaign_received 

is_active

Yes/No - If latest time of campaign_received > 7 days ago, email campaign is considered inactive.
Definition can be changed 

email_sent

Counts number of emails sent

customers_sent

Counts distinct customer_entity_id who was sent this email campaign

emails_opened

Counts number of emails opened 

customers_opened

Counts distinct customer_entity_id who opened this email

emails_clicked

Counts number of emails clicked

customers_clicked

Counts distinct customer_entity_id who clicked this email

 
with received as (
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_received_comp)::jsonb rec
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_received_comp is not null) ,
opened as (
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_opened_comp)::jsonb opn
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_opened_comp is not null),
clicked as(
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_clicked_comp)::jsonb clk
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_clicked_comp is not null),
   
cr as(
  select
    rec->>2 automation_id,
    max(rec->>3) automation_name,
    coalesce(rec->>7,'') utm_medium,
    coalesce(rec->>6,'') utm_source,
    coalesce(rec->>10,'') utm_content,
    coalesce(rec->>8,'') utm_campaign,
    coalesce(rec->>9,'') utm_term,
    min((rec->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague') start_date,
    max((rec->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague') end_date,
    case
      when max((rec->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague') >= current_date - interval '7 days' then 'Active'
      else 'Disabled'
    end is_active,
    count(1) emails_sent, 
    count(distinct customer_entity_id) customers_sent
  from received
  group by 1, 3, 4, 5, 6, 7
),
   
co as(
  select
    opn->>2 automation_id,
    coalesce(opn->>7,'') utm_medium,
    coalesce(opn->>6,'') utm_source,
    coalesce(opn->>10,'') utm_content,
    coalesce(opn->>8,'') utm_campaign,
    coalesce(opn->>9,'') utm_term,
    count(1) emails_opened, 
    count(distinct customer_entity_id) customers_opened
  from opened
  group by 1, 2, 3, 4, 5, 6
),
   
cc as(
  select
    clk->>2 automation_id,
    coalesce(clk->>7,'') utm_medium,
    coalesce(clk->>6,'') utm_source,
    coalesce(clk->>10,'') utm_content,
    coalesce(clk->>8,'') utm_campaign,
    coalesce(clk->>9,'') utm_term,
    count(1) emails_clicked, 
    count(distinct customer_entity_id) customers_clicked
  from clicked
  group by 1, 2, 3, 4, 5, 6
)
   
select 
  cr.*,
  coalesce(co.emails_opened, 0) emails_opened,
  coalesce(co.customers_opened, 0) customers_opened,
  coalesce(cc.emails_clicked, 0) emails_clicked,
  coalesce(cc.customers_clicked, 0) customers_clicked
from cr
left join co on cr.automation_id = co.automation_id and cr.utm_medium = co.utm_medium and cr.utm_source = co.utm_source and cr.utm_content = co.utm_content and cr.utm_campaign = co.utm_campaign and cr.utm_term = co.utm_term
left join cc on cr.automation_id = cc.automation_id and cr.utm_medium = cc.utm_medium and cr.utm_source = cc.utm_source and cr.utm_content = cc.utm_content and cr.utm_campaign = cc.utm_campaign and cr.utm_term = cc.utm_term
where cr.automation_name is not null
order by end_date desc

 

se_campaign_conversion

Daily conversion metrics for each email campaign

column_name

description

t_time

Datetime of order transaction

email_click_time

Datetime of email click

automation_id

automation_name

utm_medium

utm_source

utm_content

utm_campaign

utm_term

t_price_1h

Total revenue of all transactions within one hour of email click

t_count_1h

Total number of transactions within one hour of email click

t_price_1d

Total revenue of all transactions within one day of email click

t_count_1d

Total number of transactions within one day of email click

t_price_1w

Total revenue of all transactions within one week of email click

t_count_1w

Total number of transactions within one week of email click

 
with clicked as(
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_clicked_comp)::jsonb clk
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_clicked_comp is not null),
newsletter_subscription as (
    select customer_entity_id,
    unnest(website_all_subscribe_form_submitted)::jsonb fsm
    from public.pivoted_customer_attributes
    where website_all_subscribe_form_submitted is not null)
select
  date_trunc('day', t_time) as t_time,
  date_trunc('day', click_time) as banner_click_time,
  banner_id,
  count(1) filter (where t_time<click_time + interval '1 hours') as t_count_1h,
  count(1) filter (where t_time<click_time + interval '24 hours') as t_count_1d,
  count(1) filter (where t_time<click_time + interval '168 hours') as t_count_1w
from
  (
  select
    (fsm ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague' as t_time,
    (clk ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague' as click_time,
    clk->>2 automation_id,
    coalesce(clk->>7,'') utm_medium,
    coalesce(clk->>6,'') utm_source,
    coalesce(clk->>10,'') utm_content,
    coalesce(clk->>8,'') utm_campaign,
    coalesce(clk->>9,'') utm_term,
    ROW_NUMBER() over (
      PARTITION by bc.customer_entity_id, bc.clk->>1
      ORDER BY fsm->>0 ASC
    ) click_rn,
    ROW_NUMBER() over (
      PARTITION by ns.fsm->>2
      ORDER BY clk->>0 ASC
    ) t_rn
  from newsletter_subscription ns
  left join clicked rc on rc.customer_entity_id =ns.customer_entity_id and ns.fsm->>2 = rc.clk->>2
) tbl
group by 1, 2, 3


se_campaign_daily

Email campaign received, opened and clicked. Aggregated on a daily basis

column_name

description

automation_id

automation_name

utm_medium

utm_source

utm_content

utm_campaign

utm_term

date

Date where event happened

emails_sent

Counts number of emails sent

customers_sent

Counts distinct customer_entity_id who was sent this email campaign

emails_opened

Counts number of emails opened 

customers_opened

Counts distinct customer_entity_id who opened this email

emails_clicked

Counts number of emails clicked

customers_clicked

Counts distinct customer_entity_id who clicked this email

 
with received as (
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_received_comp)::jsonb rec
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_received_comp is not null),
opened as (
    select *,
    row_number() over (partition by opn->>2, customer_entity_id order by opn->>0) rn from (
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_opened_comp)::jsonb opn
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_opened_comp is not null) as tbl),
clicked as(
    select *,
    row_number() over (partition by clk->>2, customer_entity_id order by clk->>0) rn from (
    select customer_entity_id,
    unnest(se_reporting_all_campaigns_clicked_comp)::jsonb clk
    from public.pivoted_customer_attributes
    where se_reporting_all_campaigns_clicked_comp is not null) as tbl),
   
cr as (
  select
    date_trunc('day', (rec->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date date,
    rec->>2 automation_id,
    coalesce(rec->>7,'') utm_medium,
    coalesce(rec->>6,'') utm_source,
    coalesce(rec->>10,'') utm_content,
    coalesce(rec->>8,'') utm_campaign,
    coalesce(rec->>9,'') utm_term,
    count(1) emails_sent, 
    count(distinct customer_entity_id) customers_sent
  from received
  where rec->>2 != ''
  group by 1,2,3,4,5,6,7
),
co as (
  select
    date_trunc('day', (opn->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date date,
    opn->>2 automation_id,
    opn->>3 automation_name,
    coalesce(opn->>7,'') utm_medium,
    coalesce(opn->>6,'') utm_source,
    coalesce(opn->>10,'') utm_content,
    coalesce(opn->>8,'') utm_campaign,
    coalesce(opn->>9,'') utm_term,
    count(1) emails_opened,
    count(distinct customer_entity_id) customers_opened
  from opened
  where rn = 1
  group by 1,2,3,4,5,6,7,8
),
cc as (
  select
    date_trunc('day', (clk->>0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date date,
    clk->>2 automation_id,
    coalesce(clk->>7,'') utm_medium,
    coalesce(clk->>6,'') utm_source,
    coalesce(clk->>10,'') utm_content,
    coalesce(clk->>8,'') utm_campaign,
    coalesce(clk->>9,'') utm_term,
    count(1) emails_clicked,
    count(distinct customer_entity_id) customers_clicked
  from clicked
  where rn = 1
  group by 1,2,3,4,5,6,7
)
   
select
    co.automation_id,
    co.automation_name,
    co.utm_medium utm_medium,
    co.utm_source utm_source,
    co.utm_content utm_content,
    co.utm_campaign utm_campaign,
    co.utm_term utm_term,
    co.date,
    sum(coalesce(cr.emails_sent, 0)) emails_sent,
    sum(coalesce(cr.customers_sent, 0)) customers_sent,
    sum(coalesce(co.emails_opened, 0)) emails_opened,
    sum(coalesce(co.customers_opened, 0)) customers_opened,
    sum(coalesce(cc.emails_clicked, 0)) emails_clicked,
    sum(coalesce(cc.customers_clicked, 0)) customers_clicked
from co
left join cr on cr.automation_id = co.automation_id and cr.utm_medium = co.utm_medium and cr.utm_source = co.utm_source and cr.utm_content = co.utm_content and cr.utm_campaign = co.utm_campaign and cr.utm_term = co.utm_term and cr.date = co.date
left join cc on co.automation_id = cc.automation_id and co.utm_medium = cc.utm_medium and co.utm_source = cc.utm_source and co.utm_content = cc.utm_content and co.utm_campaign = cc.utm_campaign and co.utm_term = cc.utm_term and co.date = cc.date
group by 1,2,3,4,5,6,7,8

 

web_banner_status

Current status of web banners in CDP

column_name

description

banner_id

ID of web banner

banner_name

Banner name/title

status

Banner status (enabled/disabled/deleted)

banner_type

Banner type (pop-up/embedded)

 
import json
import requests
import copy
import pandas as pd

web_banners_mapped = {}
df_web_banners_status = pd.DataFrame()

with open('/data/config.json') as f:
    env = json.load(f)['parameters']['vars']
   
    EMAIL= env['MEIRO_USER']
    PASSWORD = env['#MEIRO_PASSWORD']
    BASE_URL = env['BASE_URL']

def login(url=BASE_URL, email=EMAIL, password=PASSWORD):
    print("Logging in...")
    session = requests.Session()
    resp = session.post(f'{url}/login', json={
        'email': email,
        'password': password
    })
    resp.raise_for_status()
    token = resp.json()['token']
    session.headers['X-Access-Token'] = token
    return session
   
with login() as session:
    resp_popup = session.get(f"{BASE_URL}/web_banners?limit=100&offset=0")
    resp_embedded = session.get(f"{BASE_URL}/embedded_web_banners?limit=100&offset=0")
    web_banners_popup = resp_popup.json()['web_banners']
    web_banners_embedded = resp_embedded.json()['web_banners']

for web_banner in web_banners_popup:
    web_banner_status = 'disabled' if web_banner['disabled'] else 'enabled'
    df_web_banners_status=df_web_banners_status.append([[web_banner['id'], web_banner['name'], web_banner_status, 'popup']])
   
for web_banner in web_banners_embedded:
    web_banner_status = 'disabled' if web_banner['disabled'] else 'enabled'
    df_web_banners_status=df_web_banners_status.append([[web_banner['id'], web_banner['name'], web_banner_status, 'embedded']])

df_web_banners_status.columns = ['banner_id', 'banner_name', 'status', 'banner_type']

df_web_banners_status_db = pd.read_csv('in/tables/web_banner_status_db.csv')
df_web_banners_status_db['status'] = 'deleted'
df_web_banners_status = pd.concat([df_web_banners_status, df_web_banners_status_db])
df_web_banners_status.drop_duplicates(subset='banner_id', inplace=True, keep = 'first')

df_web_banners_status.to_csv('out/tables/web_banner_status.csv', index=False)


web_banner_conversion

Daily conversion metrics for each web banner

column_name

description

t_time

Transaction time

banner_click_time

Banner click time

banner_id

ID of web banner

t_price_1h

Total revenue of all transactions within one hour of banner click

t_count_1h

Total number of transactions within one hour of banner click

t_price_1d

Total revenue of all transactions within one day of banner click

t_count_1d

Total number of transactions within one day of banner click

t_price_1w

Total revenue of all transactions within one week of banner click

t_count_1w

Total number of transactions within one week of banner click

use_case*

Use case of banner conversion (registration/subscription)

* for CNC project only

with banner_clickers as (
    select customer_entity_id,
    unnest(website_web_banner_clicks_all)::jsonb clk
    from public.pivoted_customer_attributes
    where website_web_banner_clicks_all is not null),
newsletter_subscription as (
    select customer_entity_id,
    unnest(website_all_subscribe_form_submitted)::jsonb fsm
    from public.pivoted_customer_attributes
    where website_all_subscribe_form_submitted is not null)
select
  date_trunc('day', t_time) as t_time,
  date_trunc('day', click_time) as banner_click_time,
  banner_id,
  count(1) filter (where t_time<click_time + interval '1 hours') as t_count_1h,
  count(1) filter (where t_time<click_time + interval '24 hours') as t_count_1d,
  count(1) filter (where t_time<click_time + interval '168 hours') as t_count_1w
from
(
  select
    (fsm ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague' as t_time,
    (clk ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague' as click_time,
    (fsm ->> 1) as banner_id,
    ROW_NUMBER() over (
      PARTITION by e.customer_entity_id, bc.clk->>1
      ORDER BY fsm->>0 ASC
    ) click_rn,
    ROW_NUMBER() over (
      PARTITION by ns.fsm->>2
      ORDER BY clk->>0 ASC
    ) t_rn
  from newsletter_subscription ns
  left join banner_clickers bc on bc.customer_entity_id =ns.customer_entity_id and ns.fsm->>1 = bc.clk->>1
) tbl
group by 1, 2, 3

web_banner_engagement

Daily metrics of web banners seen and clicked

column_name

description

date

Date

banner_id

ID of web banner

impression_count

Total number of impressions

impression_per_cei

Total number of customers who see the banner

interaction_count

Total number of clicks

interaction_per_cei

Total number of customers who click the banner

 


with impressions as (
    select customer_entity_id,
    unnest(website_web_banner_impressions_all)::jsonb imp
    from public.pivoted_customer_attributes
    where website_web_banner_impressions_all is not null),
clicks as (
    select customer_entity_id,
    unnest(website_web_banner_clicks_all)::jsonb clk
    from public.pivoted_customer_attributes
    where website_web_banner_clicks_all is not null)

select
  ((imp ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date as date,
  (imp ->> 1) as banner_id,
  count(*) as impression_count,
  count(distinct customer_entity_id) as impression_per_cei,
  inter.interaction_count,
  inter.interaction_per_cei
from impressions
full outer join (
  select
    ((clk ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date as date,
    (clk ->> 1) as banner_id,
    count(*) as interaction_count,
    count(distinct customer_entity_id) as interaction_per_cei
  from clicks
  group by 1, 2
  order by 1 desc) inter
on ((imp ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date = inter.date and (imp ->> 1) = inter.banner_id
where imp ->> 0 is not null and imp ->> 1 is not null
and ((imp ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date > current_date - interval '14 days'
group by 1, 2, 5, 6
order by 1 desc;

web_banner_submit (some clients only)

Daily metrics of other web banner interactions (e.g. form submits or registration events)

column_name

description

date

Date

banner_id

ID of web banner

action

Type of action (ex: submitted)

interaction_count

Number of interactions

 
with newsletter_subscription as (
    select customer_entity_id,
    unnest(website_all_subscribe_form_submitted)::jsonb fsm
    from public.pivoted_customer_attributes
    where website_all_subscribe_form_submitted is not null)
   
select
  ((fsm ->> 0)::timestamp at time zone 'utc' at time zone 'Europe/Prague')::date as date,
  (fsm ->> 1) as banner_id,
  'subscribe_newsletter_form' as action,
  count(*) as interaction_count
from newsletter_subscription
group by 1, 2, 3

 

No Comments
Back to top