0 Replies Latest reply: Feb 10, 2017 9:06 AM by nicolas diaz RSS

    Help Matching tables

    nicolas diaz

      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           

      //[C:\Users\Locha\Google Drive\MKT\ReportesQV\ReporteVentas\*.tsv]

      //(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

      //[C:\Users\Locha\Google Drive\MKT\ReportesQV\Facebook\Campaigns Smartly*.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,

           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

      //[C:\Users\Locha\Google Drive\MKT\ReportesQV\Email\email.csv]

      //(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;