3 Replies Latest reply: Apr 13, 2011 5:08 AM by Muncho Ts RSS

    Matching tables

    Gerhard Laubscher

      Hi,

      I'm still busy figuring QV out, and would appreciate some help with the following:

      I recently sent a marketing text to about 40,000 customers. In QV I loaded (from excel) the following fields:

      • Account No
      • Cell phone
      • Country
      • Title
      • First Name
      • Last Name
      • Available Credit
      • Date SMS Sent

      Now, what I want to do is measure these customers' purchasing behaviour. I have a daily "transaction extract", which is a delimited text file with the following fields:

      • Account No
      • Posted Date
      • Effective Date
      • Store No
      • Merchant Code
      • Store Name
      • Store Country
      • Transaction Type
      • Transaction Code
      • Transaction Description
      • Transaction Amount

      I want to upload this file daily, but only where [Tran Code] = 35 or 36.

      Then I want to be able to measure how many of the 40,000 customers made a purchase, their values, etc. Remember that the transaction file will also include Account Numbers that are NOT in the SMS List.

      Eventually I want to be able to compare the SMS customers' behavious before AND after the message was sent, so I would have tables like "% of SMS customers spending", "total turnover" etc. all before Vs. After.

      Currently I get stuck when importing the transaction files - I do not know whether they should be joined, concatenated, or what - and I don't know how to mape the fields to each other.

      Please help!! If I need to give sample data, please explain to me what you need - I can't legally share this information as it is, so I will have to manipulate it first, but of course some account numbers must still match.

        • Matching tables
          Vlad Gutkovsky

          I'm going to call your first table Table1 and your transaction extract Table2. You would load Table1 as usual. Include the following WHERE clause for the load of Table2:

          WHERE ([Trans Code]=35 or [Trans Code]=36) and exists([Account No])

          This will ensure you are only loading in relevant transactions for those customers that exist in Table1. You can then do counts/sums/etc in your charts as usual.

          Regards,

            • Matching tables
              Gerhard Laubscher

              Hi,

              I get an error message "garbage after statement" when the script execution gets to "table2".

              When I enter the WHERE clause the FROM statement changes from blue to normal black font, so not sure what I'm doing wrong... Tried putting the where clause at the start, end and middle of the load of table 2 - no luck.

              Here is my script:

              SET ThousandSep=',';
              SET DecimalSep='.';
              SET MoneyThousandSep=',';
              SET MoneyDecimalSep='.';
              SET MoneyFormat='R #,##0.00;R-#,##0.00';
              SET TimeFormat='hh:mm:ss TT';
              SET DateFormat='YYYY/MM/DD';
              SET TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff] TT';
              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

              LOAD [Cell phone],
              [Account No],
              Title,
              [First name],
              [Last Name],
              OTB,
              Country,
              [Date SMS Sent]
              FROM
              [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\SMS List.xlsx]
              (ooxml, embedded labels, table is Sheet1);

              LOAD
              Company,
              Product,
              [Account No],
              [Posted Date],
              [Effective Date],
              [Store No],
              [Merchant Code],
              [Store Name],
              [Store Country],
              [Card No],
              [Transaction Type],
              [Tran Code],
              [Trans Desc],
              [Trans Amount],
              [Trans Auth]
              WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No]),
              FROM
              [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\Daily_TransactionExtract_A_11-APR-2011_12042011014626.txt]
              (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines);

                • Matching tables

                  Use Where() after From().

                  LOAD
                  Company,
                  Product,
                  [Account No],
                  [Posted Date],
                  [Effective Date],
                  [Store No],
                  [Merchant Code],
                  [Store Name],
                  [Store Country],
                  [Card No],
                  [Transaction Type],
                  [Tran Code],
                  [Trans Desc],
                  [Trans Amount],
                  [Trans Auth]
                  FROM
                  [C:\Documents and Settings\GERHARDL\My Documents\TENACITY\Dunns & DFX\SMS 6 month campaigns\Campaign 2 - 11 April\Reporting\Spend\Daily_TransactionExtract_A_11-APR-2011_12042011014626.txt]
                  (txt, codepage is 1252, embedded labels, delimiter is '~', no quotes, header is 1 lines)

                  WHERE ([Tran Code]=35 or [Tran Code]=36) and exists([Account No])

                  ;