These are notes relating to the SMS campaigns that Full House carry out on a monthly basis.
These credentials may be out of date. There are currently plans to move this project to a new DB.
fullhouse.axion, traderMarketing SMSs are sent out monthly to Full House customers. The marketing SMSs will contain a message specified by Full House. This process involves sending messages to roughly 120k customers.
Generally, we send an "Audit" message first to Francois, Le Roi and Allister (?). This way, Le Roi is able to confirm that the SMS is working nicely before sending to other people.
It is critical that we are not sending multiple SMSs to the same number, as this is both wasteful, and irritating for the person receiving the SMSs. Bear this in mind when running through the process, and do some sanity checks along the way.
The important SQL queries for running this process are found here:
SELECT *
FROM `fullhouse.axion`.def_calculations
WHERE `Group` = "PromotionGo"
The marketing message I received from Le Roi contained the text $param31234$. This was changed to just $param3$ in def_promotion.
These marketing SMSs cost credits to send out. It is worthwhile checking with Denis that there are enough credits in the account before sending any messages. We will query the exact number of messages later in the process.
trader database.
data_prospect_flyer. Note: there are a lot of overlapping values in the CSV file from Le Roi and in the table already. This is fine, you can just do an INSERT IGNORE into the table. UPDATE marketing_pool_YYYYMMDD
SET Cellclean = RIGHT({{CellphoneColumn}}, 9)
/*INSERT into `fullhouse.axion`.stage_sms
(TimeScheduled, Guid, Cellnumber, SMSCode, Result1,Result3, Message,Param1, Param2, Param3,Param4,`Status`)*/
SELECT *
FROM (
SELECT
DATE_ADD(P.StartTime, INTERVAL 9 HOUR ) AS TimeScheduled
, CONCAT(P.PromotionCode,' ',UUID()) AS Guid
, CONCAT('0',C.CellnumberClean) AS Cellnumber
, 'fullhouse_sms' AS SMSCode
, 'Marketing' as Result1
, P.PromotionCode AS Result3
, CONCAT(
IF (
P.ClearedSms = '' AND C.Branch_Manager = 'AUDIT'
,CONCAT('[AUDIT ',C.FirstName,'] ')
, ""
)
, COALESCE(
IF(
C.Lang='A'
, P.MessageTemplateAfr
, P.MessageTemplate
)
, P.MessageTemplate
)
) AS MessageTemplate
, IF (
C.Branch_Manager = 'Y'
, 'S'
, REPLACE(
IF(
LENGTH(C.FirstName)>1
, CONCAT(Upper(Left(C.FirstName,1)),Lower(SubString(C.FirstName,2)),',')
,''
)
, '"'
, ''
)
) AS Param1
, C.Branch AS Param2
, REPLACE(REPLACE(COALESCE(S.ST_TELEPHONE1,'021-5264300'),'-',''),' ','') AS Param3
, CONCAT('0',C.CellnumberClean) AS Param4
, '' AS `Status`
FROM def_promotion AS P
LEFT JOIN `trader`.data_prospects_clean AS C
ON 1 = 1
LEFT JOIN `trader`.stores AS S
ON C.Branch = RIGHT(S.ST_NAME, LENGTH(C.Branch))
WHERE
P.Active = 'Y'
AND P.ClearedSms = ''
AND C.Branch_Manager = 'AUDIT'
) AS X;
/*INSERT INTO `fullhouse.axion`.stage_sms
(TimeScheduled, Guid, Cellnumber, SMSCode, Result1,Result3, Message,Param1, Param2, Param3,Param4,Status)*/
SELECT *
FROM (
SELECT
DATE_ADD(P.StartTime, INTERVAL 9 HOUR ) AS TimeScheduled
, CONCAT(
P.PromotionCode
, ' '
, UUID()
) AS Guid
, CONCAT('0', mpool.CellClean) AS Cellnumber
, 'fullhouse_sms' AS SMSCode
, 'Marketing' as Result1
, P.PromotionCode AS Result3
, COALESCE(
IF(
mpool.preferred_language='A'
, P.MessageTemplateAfr
, P.MessageTemplate
)
,P.MessageTemplate
) AS MessageTemplate
, IF (
LENGTH(mpool.FirstName) > 1
, CONCAT(
UPPER(LEFT(mpool.FirstName,1))
, LOWER(SUBSTRING(mpool.FirstName,2))
, ','
)
, ''
) AS Param1
, mpool.NEW_Store_ID AS Param2
, REPLACE(REPLACE(COALESCE(S.ST_TELEPHONE1,'021-5264300'),'-',''),' ','') AS Param3
, CONCAT('0', mpool.CellClean) AS Param4
, 'mvlpause' AS Status
FROM `trader`.stores S
INNER JOIN (
SELECT
*
, IF (
COALESCE(Store_ID, "") = ""
, 299
, Store_ID
) AS New_Store_ID
FROM `trader`.marketing_pool_YYYYMMDD
GROUP BY Cellclean
) mpool
ON S.ST_ID = mpool.New_Store_ID
LEFT JOIN `fullhouse.axion`.def_promotion AS P
ON 1=1
WHERE P.Active = 'Y'
AND P.ClearedSms = 'Y'
AND mpool.CellClean NOT IN (
SELECT cellnumberclean
FROM `trader`.data_blacklist
)
) AS X
/*INSERT INTO `fullhouse.axion`.stage_sms
(TimeScheduled, Guid, Cellnumber, SMSCode, Result1,Result3, Message,Param1, Param2, Param3,Param4,Status)*/
SELECT *
FROM (
SELECT
DATE_ADD(P.StartTime, INTERVAL 9 HOUR ) AS TimeScheduled
, CONCAT(P.PromotionCode,' ',UUID()) AS Guid
, CONCAT('0',C.Cellclean) AS Cellnumber
, 'fullhouse_sms' AS SMSCode
, 'Marketing' as Result1
, P.PromotionCode AS Result3
, P.MessageTemplateAfr AS MessageTemplate
, IF(
LENGTH(C.Name_First)>1
, CONCAT(
UPPER(LEFT(C.Name_First,1))
, LOWER(SUBSTRING(C.Name_First,2))
,','
)
, ''
) AS Param1
, C.branch_label AS Param2
, REPLACE(REPLACE(COALESCE(S.ST_TELEPHONE1,'021-5264300'),'-',''),' ','') AS Param3
, CONCAT('0',C.Cellclean) AS Param4
, 'mvlpause' AS Status
FROM `fullhouse.axion`.def_promotion AS P
LEFT JOIN `trader`.data_prospect_flyer AS C
ON 1 = 1
LEFT JOIN `trader`.stores AS S
ON
C.branch_label = RIGHT(S.ST_NAME, LENGTH(C.branch_label))
OR CAST(C.branch_label as char) = CAST(S.ST_ID AS CHAR)
WHERE
P.Active = 'Y'
AND P.ClearedSms = 'Y'
AND SendThemMarketingMaterialspecials='YES'
AND C.OptOut IS NULL
AND c.cellclean NOT IN (
SELECT cellnumberclean
FROM trader.data_blacklist
)
AND c.cellclean NOT IN (
SELECT Cellnumberclean
FROM trader.marketing_pool_YYYYMMDD
)
/*AND C.CellClean NOT IN (
SELECT CellnumberClean
FROM `trader`.marketing_pool_YYYYMMDD_loyalty
)*/
GROUP BY C.CellClean
) AS X
GROUP BY is added to ensure that multiple cellnumbers are not present.WHERE clause to avoid messaging people who are already present in other Marketing Campaigns like the loyalty campaign./*INSERT INTO `fullhouse.axion`.stage_sms
(TimeScheduled, Guid, Cellnumber, SMSCode, Result1,Result3, Message,Param1, Param2, Param3,Param4,Status)*/
SELECT
*
FROM (
SELECT
DATE_ADD(pro.StartTime, INTERVAL 9 HOUR ) AS TimeScheduled
, CONCAT(pro.PromotionCode,' ',UUID()) AS Guid
, CONCAT('0', mpool.Cellclean) AS Cellnumber
, 'fullhouse_sms' AS SMSCode
, 'Marketing' as Result1
, pro.PromotionCode AS Result3
, COALESCE(
IF(
mpool.LANGUAGE='A'
, pro.MessageTemplateAfr
, pro.MessageTemplate
)
, pro.MessageTemplate
) AS MessageTemplate
, IF (
LENGTH(mpool.FirstName)>1
, CONCAT(
UPPER(LEFT(mpool.FirstName,1))
,LOWER(SUBSTRING(mpool.FirstName,2))
,','
)
,''
) AS Param1
, mpool.LOYALTY_VALUE AS Param2
, REPLACE(REPLACE(COALESCE(sto.ST_TELEPHONE1,'021-5264300'),'-',''),' ','') AS Param3
, mpool.DB_ACCOUNTCODE AS Param4
, 'mvlpause' AS Status
FROM `fullhouse.axion`.def_promotion AS pro
LEFT JOIN `trader`.marketing_pool_YYYYMMDD_loyalty AS mpool
ON 1 = 1
LEFT JOIN `trader`.stores AS sto
ON
left(mpool.DB_ACCOUNTCODE, 3) = sto.ST_ID
WHERE pro.Active = 'Y'
AND pro.ClearedSms = 'Y'
AND mpool.Cellclean NOT IN (
SELECT cellnumberclean
FROM trader.data_blacklist
)
GROUP BY mpool.cellclean
) AS X
SELECT
COUNT(*) AS Volume
, Result3 AS Campaign
, MIN(LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Message,'$param1$',param1),'$param2$',param2),'$param3$',param3),'$param4$',param4),'$param5$',param5),'$param6$',param6))) as minleng
, MAX(LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Message,'$param1$',param1),'$param2$',param2),'$param3$',param3),'$param4$',param4),'$param5$',param5),'$param6$',param6))) AS maxleng
FROM stage_sms
WHERE `Guid` NOT LIKE 'LOYALTY%'
GROUP BY Result3;
SELECT *
FROM stage_sms
WHERE CellNumber IN (
SELECT
DISTINCT CellNumber
FROM (
SELECT
CellNumber
, COUNT(*) AS nums
FROM stage_sms
WHERE Result3 LIKE "YYYYMMDD%"
GROUP BY CellNumber
) AS cell_counts
WHERE nums > 1
)
UPDATE stage_sms
SET `Status` = ""
WHERE `Status` = "mvlpause"