Help Matching tables

[#1]

Hi Guys, I need some help with this, i will try to explain it the best i can.

I have 3 tables one from backoofice, one for our tracker platform, and one wich concatenates all the costs from all our networks of marketing.

The backoffice table has the following relevant fields:

Date Transaction_Id(tid) Status Payment_Method Model

The tracker table has the following relevant fields:

Date Tansaction_Id Campaig_Id Adgroup_Id Creative_Id Revenue

The costs table has te following relevant fields:

Date Campaign_id Campaign_name Adgroup_Id Adgroup_name Cost

What I need is to have the informaion of ROI for each of our marketing campaigns, so what i thought to do was to have a table with all the information of revenue for each campaigns, comming this from the tracker table, and all the costs for each campaign in another table comming from the table of costos; using the fields of DATE CAMPAIGN_ID ADGROUP_ID as the fields used to match.

When I do this, I use a pivot table to see the information, but I don't get any ROI. But, if i don't use the ADGROUP_ID to match, I do get this information, but only for a Campaign level, i can't open it by adgroups (i can open the Cost, but not the cost)

I think here i have a trouble because not all our sources of marketing cost have adgroups; I mean, we have adgroup level for Adwords and Facebook campaigns but for example Emil Campaigns doesn't exist an adgroup level.

Here i leave the script, I hope someone can help me with this guys!!

Some comments about the script, at first I use Applymap from the backoffice table ("ReporteVentas") to get the status, payment method and model of each transaction id in the tracker table ("Trackeame:")

Then I use some Drop Tables to clear up the script.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Ene;Feb;Mar;Abr;May;Jun;Jul;Ago;Sep;Oct;Nov;Dic';

SET DayNames='Lun;Mar;Mié;Jue;Vie;Sáb;Dom';

MapBackoffice:

Mapping Load

            Num#(transaction_id, '0.') as KeyDateTid,

            transaction_status as estado

FROM           

[ReportesQV\ReporteVentas\*.tsv]

(txt, utf8, embedded labels, delimiter is '\t', msq)

WHERE(not promo_code like 'RET*') and (transaction_origin_name like 'Website');

MapBackofficePaymentMethod:

Mapping Load

            Num#(transaction_id, '0.') as KeyDateTid,

            payment_method_name as payment_method

FROM           

[ReportesQV\ReporteVentas\*.tsv]

(txt, utf8, embedded labels, delimiter is '\t', msq)

WHERE(not promo_code like 'RET*') and (transaction_origin_name like 'Website');

MapBackofficeModel:

Mapping Load

            Num#(transaction_id, '0.') as KeyDateTid,

            model_name as Modelo

FROM           

[ReportesQV\ReporteVentas\*.tsv]

(txt, utf8, embedded labels, delimiter is '\t', msq)

WHERE(not promo_code like 'RET*') and (transaction_origin_name like 'Website');

//

//MapBackofficeLocalDate:

//

//Mapping Load

//            Date(Floor(transaction_created_at),'YYYY-MM-DD')&'/'&Num#(transaction_id, '0.') as KeyDateTid,

//            Date(Floor(transaction_created_at + 22/24),'YYYY-MM-DD') as Date

//           

//FROM           

//

//(txt, utf8, embedded labels, delimiter is '\t', msq)

//WHERE(not promo_code like 'RET*') and (transaction_origin_name like 'Website');

Trackeame:

LOAD transaction_date as Date,

     transaction_id as tid,

     product,

     transaction_date&'/'&transaction_id as KeyDateTid,

     event_date_time,

     event_type,

     revenue*(3.17) as Revenue,

     creative, 

     campaign_id as CampaignID2,

     //transaction_date&'/'&campaign_id&'/'&adgroup_id as KeyDateCampaignIDAdGroupID,

     keyword,

     adgroup_id as AdGroupID,

     matchtype,

     device,

     device_model,

     placement,

     ad_position,

     ApplyMap('MapBackoffice', transaction_id, null()) as Estado,

     ApplyMap('MapBackofficePaymentMethod', transaction_id, null()) as payment_method,

//     ApplyMap('MapBackofficeLocalDate', transaction_date&'/'&transaction_id, null()) as Date,

     ApplyMap('MapBackofficeModel', transaction_id, null()) as Modelo

FROM

[ReportesQV\Trackeame_Ventas\trocafone_r*.tsv]

(txt, utf8, embedded labels, delimiter is '\t', msq);

lastclick:

load

max(event_date_time) as event_date_time,

tid

Resident Trackeame

group by tid;

left join (Trackeame)

load   

event_date_time,

tid,

1 as last_click

Resident lastclick;

drop table lastclick;

firstclick:

load

min(event_date_time) as event_date_time,

tid

Resident Trackeame

group by tid;

left join (Trackeame)

load   

event_date_time,

tid,

1 as first_click

Resident firstclick;

drop table firstclick;

TABLAA:

Noconcatenate

LOAD * Resident Trackeame;

DROP Table Trackeame;

Costos:

LOAD Date(Day, 'YYYY-MM-DD') as Date, 

     Device,

     Account,

     [Campaign ID] as CampaignID,

     Campaign,

     [Ad group] as AdGroup,

     [Ad group ID] as AdGroupID,

     Clicks,

     Impressions,

     Cost/1000000 as Cost,

     Clicks/Impressions as CTR,

     Cost/Clicks as [Avg. CPC],

     [Avg. position],

     Conversions,

     [Cross-device conv.],

     [Search Exact match IS],

     [Search Impr. share]

FROM

[ReportesQV\BR_AdWords_Search\*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq, header is 1 lines);

Concatenate(Costos)

LOAD Account,

     Campaign,

     [Campaign ID] as CampaignID,

     [Ad group] as AdGroup,

     Clicks,

     Impressions,

     [Avg. CPC],

     Cost/1000000 as Cost,

     Conversions,

     [Cross-device conv.],

     [Search Impr. share],

     [Click share],

     [Custom label 1] as Model,

     [Custom label 2] as Condition,

     Brand,

     Date(Day, 'YYYY-MM-DD') as Date,

     Device

FROM

[ReportesQV\BR_AdWords_Shopping\shopping*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Concatenate(Costos)

LOAD ACCION as Campaign,

    ACCION as CampaignID,

    Date(Floor(FECHA),'YYYY-MM-DD') as Date,

//    Month(Date(Floor(FECHA),'YYYY-MM-DD')) as Month,

//    DIA,

//    HORA,

//    DESTINATARIOS,

    ENVIADOS * 0.00098 as Cost

//    REBOTES,

//    EFECTIVOS,

//    [ABIERTOS-T],

//    [ABIERTOS-U],

//    [CLICKS-T],

//    SUSCRIPTOS,

//    DESUSCRIPTOS,

//    VIRALES,

//    DRT,

//    ORT,

//    ORU,

//    BRT,

//    CTRT

FROM

[ReportesQV\Email\Performance\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', no quotes);

Concatenate(Costos)

LOAD Date(Day, 'YYYY-MM-DD') as Date,

     Device,

     Account, 

     Campaign,

     [Campaign ID] as CampaignID,  

     Clicks,

     Impressions,

     Clicks/Impressions as CTR,

     Cost/1000000 as Cost,

     Clicks*0.29 as CostoPlataforma,

     [Converted clicks] as Conversions

FROM

[ReportesQV\BR_AdWords_Display\br*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq, header is 1 lines);

Concatenate (Costos)

// Reportes bajados de Facebook

LOAD Date(date_start, 'YYYY-MM-DD') as Date, 

    campaign_name as Campaign,

    campaign_id as CampaignID,

    adset_name as AdGroup,

    adset_id as AdGroupID, 

    reach,

    frequency,

    impressions as Impressions,

    website_clicks as Clicks,  

    spend as fb_cost,

    spend*0.04 as smartly_cost,

    spend*1.04 as Cost

    FROM

[ReportesQV\BR_FacebookInsights_Daily\face*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq);

//

//Concatenate(Costos)

//LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

//     Campaña as Campaign,

//     [Smartly fee]/100 as Cost

//FROM

//

//(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate (Costos)

LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

     Campaign,

     CampaignID as CampaignID,

     Impressions,

     Clicks,

     Cost as Cost,

     Conversions

FROM

[ReportesQV\Criteo\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate (Costos)

LOAD date(Date#(Date, 'YYYY/MM/DD'), 'YYYY-MM-DD')  as Date,

     [Insertion Order] as Campaign,

     [Insertion Order ID] as CampaignID,

     [Line Item],

     Impressions,

     Clicks,

     Impressions/Clicks as CTR,

     [Total Conversions] as Conversions,

     Clicks*0.21 as CostoPlataforma,

     [Revenue (Adv Currency)] as Cost

FROM

[ReportesQV\DBM\BR_*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',',msq)

where([Insertion Order] like 'BR_*');

Concatenate(Costos)

LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

    Campaign as CampaignID,

    Campaign,

    Cost

FROM

[ReportesQV\Otros\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

//

//Concatenate (Costos)

//LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

//  Campaign,

//     Campaign as CampaignID,

//     Impressions,

//     Cost as Cost

//FROM

//

//(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate (Costos)

LOAD

     'BR_B2C_BUSCAPE_PROS_'&id as Campaign,

     'BR_B2C_BUSCAPE_PROS_'&id as CampaignID,

     replace(PurgeChar(custo,'R$.'),',','.') as Cost,

     cliques,

     Date(Date#(final,'D/M/YYYY'),'YYYY-MM-DD') as Date

FROM

[ReportesQV\Buscape\Admatic*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Concatenate (Costos)

LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

     Campaign,

     Campaign as CampaignID,

     Cost as Cost

FROM

[ReportesQV\Credit\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate (Costos)

LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

     Campaign, 

     Campaign as CampaignID,

     Cost as Cost

FROM

[ReportesQV\Afilio\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate (Costos)

LOAD Date(Date#(Date,'M/D/YYYY'),'YYYY-MM-DD') as Date,

     Campaign,

     Campaign as CampaignID,

     Impressions,

     Clicks,

     Cost as Cost

FROM

[ReportesQV\Meli\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

// Reportes bajados de Bing Argentino

Concatenate (Costos)

LOAD Date(Date#([Gregorian date],'M/D/YYYY'),'YYYY-MM-DD') as Date,

     [Account name] as Account,

     [Campaign name] as Campaign,

     Impressions,

     Clicks, 

     Spend * (0.35) as Cost,

     [Conversions]

FROM

[ReportesQV\BingArgentina\*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq, header is 12 lines);

// Reportes bajados de Bing Brasil

Concatenate (Costos)

LOAD Date(Date#([Gregorian date],'M/D/YYYY'),'YYYY-MM-DD') as Date,

     [Campaign name] as Campaign,

     Impressions,

     Clicks, 

     Spend as Cost,

     [Conversions]

FROM

[ReportesQV\BingBrasil\*.csv]

(txt, utf8, embedded labels, delimiter is ',', msq, header is 12 lines);

//Where IsNum([Gregorian date]);

// Aplico subfield para filtros por nomenclatura

TablaFinal:

NoConcatenate LOAD

     *,

     SubField(Campaign,'_',1) as Sub_Pais,

     SubField(Campaign,'_',2) as Sub_Negocio,

     SubField(Campaign,'_',3) as Sub_Network,

     SubField(Campaign,'_',4) as Sub_TipoCampana,

     SubField(Campaign,'_',5) as Sub_MatchType,

     SubField(Campaign,'_',6) as Sub_MarcaCelular,

     SubField(Campaign,'_',7) as Sub_ModeloCelular,

     Date&'/'&CampaignID&'/'&AdGroupID as KeyDateCampaignIDAdGroupID

Resident Costos;

DROP Table Costos;