Les chinois, camaraderie et capitalisme

[Brock]

jamais ?

 

ca expliquerait leur soif de publicite institutionnelle.

[Mégille]

Attendez, il y a des gens qui s'aventurent sur youtube sans adblock ? (avec brave, perso, je n'ai jamais rien)

[Neomatix]

il y a une heure, Wayto a dit :

Youtube n'a jamais été rentable.

On n'en sait rien c'est pas disclosé.

Je détesterais tellement être analyste en tech, c'est des boîtes noires les trucs, zéro info par segment/produit.

[Bézoukhov]

Ben tout le système tient sur le fait que la pub sur internet aurait un ROI.

Personne ne sait mesurer ledit ROI.

[Wayto]

il y a 4 minutes, Bézoukhov a dit :

Personne ne sait mesurer ledit ROI.

wut ?

[Neomatix]

il y a 6 minutes, Bézoukhov a dit :

Ben tout le système tient sur le fait que la pub sur internet aurait un ROI.

Personne ne sait mesurer ledit ROI.

Révélation

--INSERT INTO ######
SELECT '######' AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN y.country IS NULL THEN 'UNKNOWN'
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign AS campaign_name,
'Unknown' AS country,
withoutCountry AS city
FROM (SELECT TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1) FROM 1 FOR POSITION('@' IN SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1)))) AS withoutCountry,
campaign,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name AS campaign,
REPLACE(campaign_name,'_','@') AS campaign_name,
COUNT(campaign_name),
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name NOT LIKE '%>%'
AND LENGTH(campaign_name) > 20
AND network_id IS NOT NULL
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name LIKE '%@%@%@%@%@%@%@%@%')
WHERE LENGTH(withoutCountry) > 2)) AS x
LEFT JOIN (SELECT application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.application_id,
x.campaign_name,
x.network_id,
y.country,
x.city,
x.city_uncleaned
UNION ALL
SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))
UNION ALL
SELECT x.customer_id AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN (y.country IS NULL AND x.city <> '') THEN 'UNKNOWN'
WHEN x.city = '' THEN x.country
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN country_uncleaned LIKE '%@%' AND UPPER(country_uncleaned) NOT LIKE 'ALL@%' THEN SUBSTRING(country_uncleaned FROM 1 FOR 2)
WHEN country_uncleaned LIKE '__' AND country_uncleaned NOT LIKE '13' THEN country_uncleaned
WHEN LENGTH(country_uncleaned) > 2 AND country_uncleaned NOT LIKE '%@%' THEN 'UNKNOWN'
WHEN UPPER(country_uncleaned) LIKE 'ALL@%' THEN 'UNKNOWN'
WHEN country_uncleaned LIKE '13' THEN 'UNKNOWN'
ELSE country_uncleaned
END AS country,
CASE
WHEN LENGTH(country_uncleaned) > 2 AND country_uncleaned NOT LIKE '%@%' THEN country_uncleaned
WHEN UPPER(campaign_name) LIKE UPPER('%Kansas City%') THEN 'Kansas City'
ELSE ''
END AS city,
country_uncleaned AS city_uncleaned
FROM (SELECT campaign_name,
application_id,
network_id,
CASE
WHEN campaign_name_1 LIKE '__' THEN campaign_name_1
WHEN (campaign_name_1 LIKE '__@%' AND campaign_name_1 NOT LIKE 'T1@%') THEN TRIM(TRAILING '@' FROM SUBSTRING(campaign_name_1 FROM 1 FOR POSITION('@' IN campaign_name_1)))
WHEN campaign_name_1 LIKE 'T1@%' THEN TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(campaign_name_1 FROM POSITION('@' IN campaign_name_1) +1) FROM 1 FOR 2))
ELSE campaign_name_1
END AS country_uncleaned
FROM (SELECT campaign_name,
application_id,
network_id,
CASE
WHEN campaign_name_start = ('FLIGHTS') THEN campaign_name_end
WHEN campaign_name_start = ('BR') THEN campaign_name_end
WHEN campaign_name_start = ('ANDROID') THEN campaign_name_end
WHEN campaign_name_start = ('HT') THEN campaign_name_end
WHEN campaign_name_start = ('T1') THEN campaign_name_end
WHEN campaign_name_start = ('T2') THEN campaign_name_end
WHEN campaign_name_start = ('T3') THEN campaign_name_end
WHEN campaign_name_start = ('DEALS') THEN campaign_name_end
WHEN campaign_name_start = ('MOBILEWEB') THEN campaign_name_end
WHEN campaign_name_start = ('Z') THEN campaign_name_end
WHEN campaign_name_start = ('IOS') THEN campaign_name_end
WHEN campaign_name_start = ('DESKTOP') THEN campaign_name_end
ELSE campaign_name_start
END AS campaign_name_1
FROM (SELECT DISTINCT campaign_name,
TRIM(TRAILING '@' FROM UPPER(SUBSTRING(REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@') FROM 1 FOR POSITION('@' IN REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@'))))) AS campaign_name_start,
SUBSTRING(REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@') FROM POSITION('@' IN REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@')) +1) AS campaign_name_end,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name <> ''
AND UPPER(campaign_name) NOT LIKE UPPER('hoteltonight%')
AND campaign_name NOT LIKE '%{%'
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name_start <> ''))) AS x
LEFT JOIN (SELECT '######' AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN y.country IS NULL THEN 'UNKNOWN'
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign AS campaign_name,
'Unknown' AS country,
withoutCountry AS city
FROM (SELECT TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1) FROM 1 FOR POSITION('@' IN SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1)))) AS withoutCountry,
campaign,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name AS campaign,
REPLACE(campaign_name,'_','@') AS campaign_name,
COUNT(campaign_name),
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name NOT LIKE '%>%'
AND LENGTH(campaign_name) > 20
AND network_id IS NOT NULL
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name LIKE '%@%@%@%@%@%@%@%@%')
WHERE LENGTH(withoutCountry) > 2)) AS x
LEFT JOIN (SELECT application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.application_id,
x.campaign_name,
x.network_id,
y.country,
x.city,
x.city_uncleaned
UNION ALL
SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ########
WHERE
customer_id = '#######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.customer_id,
x.application_id,
x.network_id,
x.campaign_name,
y.country,
x.country,
x.city,
x.city_uncleaned;

 

[Bézoukhov]

il y a 7 minutes, Wayto a dit :

wut ?

 

Ben 500k de pub sur des vidéos Youtube, ça ramène combien de ventes ?

 

il y a 1 minute, Neomatix a dit :

  Masquer le contenu

--INSERT INTO ######
SELECT '######' AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN y.country IS NULL THEN 'UNKNOWN'
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign AS campaign_name,
'Unknown' AS country,
withoutCountry AS city
FROM (SELECT TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1) FROM 1 FOR POSITION('@' IN SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1)))) AS withoutCountry,
campaign,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name AS campaign,
REPLACE(campaign_name,'_','@') AS campaign_name,
COUNT(campaign_name),
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name NOT LIKE '%>%'
AND LENGTH(campaign_name) > 20
AND network_id IS NOT NULL
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name LIKE '%@%@%@%@%@%@%@%@%')
WHERE LENGTH(withoutCountry) > 2)) AS x
LEFT JOIN (SELECT application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.application_id,
x.campaign_name,
x.network_id,
y.country,
x.city,
x.city_uncleaned
UNION ALL
SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))
UNION ALL
SELECT x.customer_id AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN (y.country IS NULL AND x.city <> '') THEN 'UNKNOWN'
WHEN x.city = '' THEN x.country
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN country_uncleaned LIKE '%@%' AND UPPER(country_uncleaned) NOT LIKE 'ALL@%' THEN SUBSTRING(country_uncleaned FROM 1 FOR 2)
WHEN country_uncleaned LIKE '__' AND country_uncleaned NOT LIKE '13' THEN country_uncleaned
WHEN LENGTH(country_uncleaned) > 2 AND country_uncleaned NOT LIKE '%@%' THEN 'UNKNOWN'
WHEN UPPER(country_uncleaned) LIKE 'ALL@%' THEN 'UNKNOWN'
WHEN country_uncleaned LIKE '13' THEN 'UNKNOWN'
ELSE country_uncleaned
END AS country,
CASE
WHEN LENGTH(country_uncleaned) > 2 AND country_uncleaned NOT LIKE '%@%' THEN country_uncleaned
WHEN UPPER(campaign_name) LIKE UPPER('%Kansas City%') THEN 'Kansas City'
ELSE ''
END AS city,
country_uncleaned AS city_uncleaned
FROM (SELECT campaign_name,
application_id,
network_id,
CASE
WHEN campaign_name_1 LIKE '__' THEN campaign_name_1
WHEN (campaign_name_1 LIKE '__@%' AND campaign_name_1 NOT LIKE 'T1@%') THEN TRIM(TRAILING '@' FROM SUBSTRING(campaign_name_1 FROM 1 FOR POSITION('@' IN campaign_name_1)))
WHEN campaign_name_1 LIKE 'T1@%' THEN TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(campaign_name_1 FROM POSITION('@' IN campaign_name_1) +1) FROM 1 FOR 2))
ELSE campaign_name_1
END AS country_uncleaned
FROM (SELECT campaign_name,
application_id,
network_id,
CASE
WHEN campaign_name_start = ('FLIGHTS') THEN campaign_name_end
WHEN campaign_name_start = ('BR') THEN campaign_name_end
WHEN campaign_name_start = ('ANDROID') THEN campaign_name_end
WHEN campaign_name_start = ('HT') THEN campaign_name_end
WHEN campaign_name_start = ('T1') THEN campaign_name_end
WHEN campaign_name_start = ('T2') THEN campaign_name_end
WHEN campaign_name_start = ('T3') THEN campaign_name_end
WHEN campaign_name_start = ('DEALS') THEN campaign_name_end
WHEN campaign_name_start = ('MOBILEWEB') THEN campaign_name_end
WHEN campaign_name_start = ('Z') THEN campaign_name_end
WHEN campaign_name_start = ('IOS') THEN campaign_name_end
WHEN campaign_name_start = ('DESKTOP') THEN campaign_name_end
ELSE campaign_name_start
END AS campaign_name_1
FROM (SELECT DISTINCT campaign_name,
TRIM(TRAILING '@' FROM UPPER(SUBSTRING(REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@') FROM 1 FOR POSITION('@' IN REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@'))))) AS campaign_name_start,
SUBSTRING(REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@') FROM POSITION('@' IN REPLACE(REPLACE(UPPER(campaign_name),'_','@'),'-','@')) +1) AS campaign_name_end,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name <> ''
AND UPPER(campaign_name) NOT LIKE UPPER('hoteltonight%')
AND campaign_name NOT LIKE '%{%'
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name_start <> ''))) AS x
LEFT JOIN (SELECT '######' AS customer_id,
x.application_id AS application_id,
x.network_id AS network_id,
x.campaign_name AS campaign_name,
CASE
WHEN y.country IS NULL THEN 'UNKNOWN'
ELSE y.country
END AS country,
x.city AS city,
x.city_uncleaned AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign AS campaign_name,
'Unknown' AS country,
withoutCountry AS city
FROM (SELECT TRIM(TRAILING '@' FROM SUBSTRING(SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1) FROM 1 FOR POSITION('@' IN SUBSTRING(SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1) FROM POSITION('@' IN SUBSTRING(SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1) FROM POSITION('@' IN SUBSTRING(campaign_name FROM POSITION('@' IN campaign_name) +1)) +1)) +1)))) AS withoutCountry,
campaign,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name AS campaign,
REPLACE(campaign_name,'_','@') AS campaign_name,
COUNT(campaign_name),
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND campaign_name NOT LIKE '%>%'
AND LENGTH(campaign_name) > 20
AND network_id IS NOT NULL
GROUP BY campaign_name,
application_id,
network_id)
WHERE campaign_name LIKE '%@%@%@%@%@%@%@%@%')
WHERE LENGTH(withoutCountry) > 2)) AS x
LEFT JOIN (SELECT application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ######
WHERE customer_id = '######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.application_id,
x.campaign_name,
x.network_id,
y.country,
x.city,
x.city_uncleaned
UNION ALL
SELECT '######' AS customer_id,
application_id,
network_id,
campaign_name,
CASE
WHEN UPPER('%' + city + '%') LIKE UPPER('%ZURICH%') THEN 'CHE'
WHEN country = 'NDL' THEN 'NLD'
ELSE country
END AS country,
REPLACE(REPLACE(REPLACE(UPPER(city),'-','@'),'_','@'),'@',' ') AS city,
city AS city_uncleaned
FROM (SELECT application_id,
network_id,
campaign_name,
country,
CASE
WHEN city LIKE '%>%' THEN SUBSTRING(city FROM 1 FOR POSITION('>' IN city) -1)
ELSE city
END AS city
FROM (SELECT application_id,
network_id,
campaign_name,
SUBSTRING(country FROM (CASE POSITION('_' IN country) WHEN 0 THEN 1 ELSE POSITION('_' IN country) +1 END) FOR 3) AS country,
SUBSTRING(withoutCountry FROM 1 FOR POSITION('-' IN withoutCountry) -1) AS city
FROM (SELECT SUBSTRING(campaign_name FROM 1 FOR POSITION('>' IN campaign_name) -1) AS country,
SUBSTRING(campaign_name FROM POSITION('>' IN campaign_name) +1) AS withoutCountry,
campaign_name,
application_id,
network_id
FROM (SELECT DISTINCT campaign_name,
application_id,
network_id
FROM ########
WHERE
customer_id = '#######'
AND network_id IS NOT NULL
AND campaign_name LIKE '%>%'))))) AS y ON UPPER ('%' + x.city + '%') LIKE UPPER ('%' + y.city + '%')
GROUP BY x.customer_id,
x.application_id,
x.network_id,
x.campaign_name,
y.country,
x.country,
x.city,
x.city_uncleaned;

 

 

C'est le code de parcoursup ?

[Neomatix]

il y a 3 minutes, Bézoukhov a dit :

C'est le code de parcoursup ?

Presque ! C'est ce qu'un indien a pondu pour évaluer le ROI d'une campagne marketing Uber.

[Rincevent]

il y a 13 minutes, Bézoukhov a dit :

Ben tout le système tient sur le fait que la pub sur internet aurait un ROI.

Personne ne sait mesurer ledit ROI.

Hé hé hé. La pub sur les Interwebs, c'est typiquement le genre de secteur à un bout de la chaîne de valeur qui est voué à gonfler tant que les taux sont trop bas. Si un jour on retrouve durablement des taux à un niveau normal, ça va être rigolo à voir (ou pas).

 

@Neomatix : tu vas me faire le plaisir de nettoyer ce code, et plus vite que ça. Ça ne passe aucune revue de code sérieuse.

 

Edit :

à l’instant, Neomatix a dit :

Presque ! C'est ce qu'un indien a pondu pour évaluer le ROI d'une campagne marketing Uber.

Ok je comprends mieux.

[Wayto]

il y a 6 minutes, Bézoukhov a dit :

Ben 500k de pub sur des vidéos Youtube, ça ramène combien de ventes ?

Mais...

 

LoL.

 

Comme tout, ça dépend.
C'est fort de café de dire que le ROI des pubs sur internet est difficile à mesurer quand tes autres alternatives sont la télé et la radio.

 

Edit : putain j'avais oublié où j'étais aussi . Bon allez, je vais faire gagner du temps à tout le monde : pub caca / com caca / vendeur d'huile de serpent / allons construire des lave-phares.

[Bézoukhov]

il y a 2 minutes, Wayto a dit :

Comme tout, ça dépend.

 

Ben oui, ça dépend. Mais ça doit être calculable.

 

il y a 3 minutes, Wayto a dit :

C'est fort de café de dire que le ROI des pubs sur internet est difficile à mesurer quand tes autres alternatives sont la télé et la radio.

 

Et la presse, et les abribus.

La mesure des grosses campagnes marketings se fait parce qu'elles sont assez ponctuelles et massives. C'est un peu flou, mais c'est des stats quasi macro.

La mesure d'une campagne internet, ça repose sur du A/B testing (tu payes parce que on te dit que ça va être ciblé). Ensuite, ben l'échantillonnage, faut être fort en stat pour montrer que ça marche.

[Neomatix]

il y a 15 minutes, Rincevent a dit :

Hé hé hé. La pub sur les Interwebs, c'est typiquement le genre de secteur à un bout de la chaîne de valeur qui est voué à gonfler tant que les taux sont trop bas. Si un jour on retrouve durablement des taux à un niveau normal, ça va être rigolo à voir (ou pas).

Les dépenses marketing sont très cycliques (c'est pour ça que l'action GOOG ne va pas aussi fort que MSFT en YTD) mais c'est loin d'être une bulle. Je ne pense pas que les startup de merde genre Doordash ou WeWork représentent une part importante des budgets pub internet mondiaux.

 

Don't make me defend marketing.

 

il y a 16 minutes, Rincevent a dit :

@Neomatix : tu vas me faire le plaisir de nettoyer ce code, et plus vite que ça. Ça ne passe aucune revue de code sérieuse.

Maiiiis je suis nul en SQL moi, de toute façon.

D'ailleurs : on prononce ça S.Q.L. ou sequel ?

[Rincevent]

il y a 5 minutes, Neomatix a dit :

Maiiiis je suis nul en SQL moi, de toute façon.

Rassure-toi : Oracle aussi.

 

il y a 5 minutes, Neomatix a dit :

D'ailleurs : on prononce ça S.Q.L. ou sequel ?

On ne prononce pas, on code. Si vraiment tu veux faire cesser le débat, tu sépares DQL, DML, DDL et DCL.

 

(Sinon, c'est presque toujours Est-ce Cul Elle en français, et généralement Sequel en anglais quoique pas toujours).

[Wayto]

Cette conversation est surréaliste.

@Bézoukhov de mon expérience du x6 en moyenne avec les pubs sur Youtube, sans prendre en compte la CLV.

[Rincevent]

@Neomatix Attends, c'est quoi ces Substring( ... From ... For ..) ? Ne me dit pas que c'est du MySQL ?!

[cedric.org]

41 minutes ago, Neomatix said:

Presque ! C'est ce qu'un indien a pondu pour évaluer le ROI d'une campagne marketing Uber.

Mon dieu c'est horrible

 

40 minutes ago, Wayto said:

Mais...

 

LoL.

 

Comme tout, ça dépend.
C'est fort de café de dire que le ROI des pubs sur internet est difficile à mesurer quand tes autres alternatives sont la télé et la radio.

 

Edit : putain j'avais oublié où j'étais aussi . Bon allez, je vais faire gagner du temps à tout le monde : pub caca / com caca / vendeur d'huile de serpent / allons construire des lave-phares.

Je ne suis pas expert en pub internet, mais quand tu sais qui a cliqué quand à quel endroit puis à fini par acheter tel produit, ça me paraît plutôt clair, par rapport à mesurer l'impact ceteris paribus (ça y est, je l'ai placée sur ce forum) dans les X jours qui suivent la campagne en aveugle complet, bon...

 

[DeadBot]

il y a 8 minutes, cedric.org a dit :

Mon dieu c'est horrible

 

Je ne suis pas expert en pub internet, mais quand tu sais qui a cliqué quand à quel endroit puis à fini par acheter tel produit, ça me paraît plutôt clair, par rapport à mesurer l'impact ceteris paribus (ça y est, je l'ai placée sur ce forum) dans les X jours qui suivent la campagne en aveugle complet, bon...

 

 

Oui, tout à fait. Quand l'achat du produit ou service s'effectue en ligne, on a rapidement une bonne idée du ROI à terme. Je dépense X pour une campagne de pub sur Youtube, ça me ramène Y clients sur mon site (tout est tracké évidemment) qui dépensent Z dans les 90 jours qui suivent, et voilà mon ROI. On peut même comparer le panier moyen selon les différentes campagnes. Après c'est sûr que si les clients doivent se rendre en magasin pour acheter le bidule en question, ça devient beaucoup plus compliqué, mais c'est le même problème quel que soit le support de la pub. 

[Neomatix]

il y a une heure, Rincevent a dit :

@Neomatix Attends, c'est quoi ces Substring( ... From ... For ..) ? Ne me dit pas que c'est du MySQL ?!

https://eng.uber.com/postgres-to-mysql-migration/

[Bézoukhov]

il y a 26 minutes, cedric.org a dit :

Je ne suis pas expert en pub internet, mais quand tu sais qui a cliqué quand à quel endroit puis à fini par acheter tel produit, ça me paraît plutôt clair, par rapport à mesurer l'impact ceteris paribus (ça y est, je l'ai placée sur ce forum) dans les X jours qui suivent la campagne en aveugle complet, bon...

 

Une campagne classique : tu vends 100 par semaine d'habitude ; tu payes de la pub, sur les semaines suivantes tu vends 150. C'est facile parce que ta seule mesure est facile est très agrégée.

Une campagne web ciblée : tu vends cher des leads, et tu vas devoir montrer à ton client que tu vends 6x plus que dans la population normale. Maintenant, qu'est-ce-qui se passe si tu cibles des gens qui auraient acheté de toute manière ? Si tu ne maîtrises pas l'échantillonnage, tu peux facilement te faire arnaquer en tant que client (et si tu laisses ton échantillonnage à des réseaux de neurones que personne ne capte, c'est encore plus fun).

 

L'intellectual fallacy (on dit comment en français) derrière la pub web, c'est qu'en pensant tout mesurer, on se noie dans la mesure. Du moins dans une bonne partie de celle ci.

 

il y a une heure, Wayto a dit :

@Bézoukhov de mon expérience du x6 en moyenne avec les pubs sur Youtube, sans prendre en compte la CLV.

 

J'ai une excellente idée du mode de calcul des CLV dans la finance de détail. Et c'est assez lol.

[Rincevent]

il y a 25 minutes, Neomatix a dit :

https://eng.uber.com/postgres-to-mysql-migration/

Oh, je ne dis pas que PostgreSql n'a pas de problèmes ; sa force, c'est en effet l'intégrité, pas le transactionnel. Mais en passant à MySQL plutôt qu'autre chose, ils risquent de perdre davantage niveau intégrité (ils ont choisi du InnoDB donc pas complètement crade, mais il reste des gotchas à la con un peu partout qui peuvent poser des emmerdes de fiabilité) que ce qu'ils gagneront niveau transactionnel entrant (les Insert/Update/Delete). Je dis transactionnel entrant, parce que si pour le sortant, MySQL est historiquement imbattable pour les Select simples, il n'a jamais été doué (euphémisme) pour traiter des requêtes un tant soit peu complexes (et même pas besoin de voir celle de ton indien plus haut). Mais bon, je suppose que tout le transactionnel passe par n requêtes mono-lignes...

 

TL;DR : leur abandon de PostgreSql est motivé pour des raisons techniques ; leur adoption de MySQL en revanche est probablement motivée par la disponibilité de gens habitués à traiter avec, plutôt que par des considérations techniques.

[Tramp]

HK se fait annexer et vous parlez code 

[Brock]

c'est fou

[cedric.org]

6 hours ago, Rincevent said:

Oh, je ne dis pas que PostgreSql n'a pas de problèmes ; sa force, c'est en effet l'intégrité, pas le transactionnel. Mais en passant à MySQL plutôt qu'autre chose, ils risquent de perdre davantage niveau intégrité (ils ont choisi du InnoDB donc pas complètement crade, mais il reste des gotchas à la con un peu partout qui peuvent poser des emmerdes de fiabilité) que ce qu'ils gagneront niveau transactionnel entrant (les Insert/Update/Delete). Je dis transactionnel entrant, parce que si pour le sortant, MySQL est historiquement imbattable pour les Select simples, il n'a jamais été doué (euphémisme) pour traiter des requêtes un tant soit peu complexes (et même pas besoin de voir celle de ton indien plus haut). Mais bon, je suppose que tout le transactionnel passe par n requêtes mono-lignes...

 

TL;DR : leur abandon de PostgreSql est motivé pour des raisons techniques ; leur adoption de MySQL en revanche est probablement motivée par la disponibilité de gens habitués à traiter avec, plutôt que par des considérations techniques.

Ça dépend quel moteur est utilisé. Celui de facebook a un poil montré sa scalabilité et sa performance.

 

http://myrocks.io/

 

 

Oui, pour HK, c'est quand même détendu du string : on dégage de l'Assemblée manu militari ceux dont on ne veut pas, on vote, et, étonnement, on obtient une unanimité. Tout va bien.

[cedric.org]

11 hours ago, h16 said:

 

Je pense que ça ne durera pas. L'économie chinoise va s'effondrer et rendre le pays nettement moins attractif.

Parce que l'économie des occidentaux va s'effondrer aussi ?

[Rübezahl]

il y a 13 minutes, cedric.org a dit :

Parce que l'économie des occidentaux va s'effondrer aussi ?

"reprise en main" = dégradation, pour sûr.

à quelle vitesse, that's the question.

Tout ça quand même un peu à cause d'un seul bonhomme, Xi machin, qui aurait aussi pu emprunter une autre voie.

[Mathieu_D]

10 hours ago, Neomatix said:

Presque ! C'est ce qu'un indien a pondu pour évaluer le ROI d'une campagne marketing Uber.

 

10 hours ago, Rincevent said:

 

@Neomatix : tu vas me faire le plaisir de nettoyer ce code, et plus vite que ça. Ça ne passe aucune revue de code sérieuse.

 

J'ai lu en diagonale mais je ne pige pas les "customer_id=xxx". Ils regardent chaque client dans une boucle ? ?

[Mathieu_D]

8 hours ago, Bézoukhov said:

 

Une campagne classique : tu vends 100 par semaine d'habitude ; tu payes de la pub, sur les semaines suivantes tu vends 150. C'est facile parce que ta seule mesure est facile est très agrégée.

Une campagne web ciblée : tu vends cher des leads, et tu vas devoir montrer à ton client que tu vends 6x plus que dans la population normale. Maintenant, qu'est-ce-qui se passe si tu cibles des gens qui auraient acheté de toute manière ? Si tu ne maîtrises pas l'échantillonnage, tu peux facilement te faire arnaquer en tant que client (et si tu laisses ton échantillonnage à des réseaux de neurones que personne ne capte, c'est encore plus fun).

Et encore là tu es dans le pilotage de la performance des campagnes, quand tu rentres dans l'attribution c'est encore plus fun !

L'uplift modeling c'est fun aussi.

 

Bref.

[Neomatix]

Il y a 8 heures, Tramp a dit :

HK se fait annexer et vous parlez code 

HK s'est fait annexer en 1997.

 

S'ils imposent cette loi c'est la fin du 1 country 2 systems donc ils perdent HK comme intermédiaire pour lever des fonds pour les boîtes chinoises. Le dernier truc dont le PCC a besoin en ce moment est de rendre la reprise plus difficile.

[Tramp]

à l’instant, Neomatix a dit :

Le dernier truc dont le PCC a besoin en ce moment est de rendre la reprise plus difficile.

 

Ils sont communistes, ils sont dirigés par une armée de Bruno Le Maire sous amphétamines 

[h16]

Il y a 2 heures, cedric.org a dit :

Parce que l'économie des occidentaux va s'effondrer aussi ?

Oui et non. 

L'économie occidentale va prendre un gros coup, mais je pense que le régime Chinois vit ses derniers mois.