Author: Vladimir Myuge
library(RMySQL)## Warning: package 'RMySQL' was built under R version 3.1.3## Loading required package: DBI## Warning: package 'DBI' was built under R version 3.1.2#setwd("C:/Users/vladimir/Documents/R/experian_users")
if(!file.exists("db_users.csv"))
file.copy("C:/Users/vladimir/Documents/n_exp_user-2015-09-29.csv.done", "db_users.csv", overwrite = T)
if(!file.exists("exp_users.csv"))
file.copy("C:/Users/vladimir/Downloads/export_385_161630.txt", "exp_users.csv", overwrite = T)readLines("db_users.csv", n = 3)## [1] "id_user|date_created|date_changed|email|firstname|lastname|website|civility|birthdate|status|orders_count|listed_products|listed_products_available|listed_products_sold|last_open_date|connectionhash|is_optout|is_bounce|orders_12M|GMB_12M"
## [2] "1|2009-05-05 00:00:00.0|2015-02-02 00:00:00.0|webmaster@neovia.pro|Webmaster@Neovia.Pro|Webmaster@Neovia.Pro|FR|ms|2011-03-14 00:00:00.0|activated|0|0|0|0||59016e3b445695bfbb76048a2a68db56d48b14|0|0|0|0.0"
## [3] "2|2009-06-15 00:00:00.0|2015-09-24 00:00:00.0|meryl.job@gmail.com|||FR|ms|1980-04-04 00:00:00.0|activated|48|126|104|46|2015-06-10 00:00:00.0|134f3d27b752a6ffdd1234b4c2a50de2317110|0|0|9|2491.0"db_file_header <- names(read.delim("db_users.csv", sep = "|", quote = "", nrows = 3))
db_file_header## [1] "id_user" "date_created"
## [3] "date_changed" "email"
## [5] "firstname" "lastname"
## [7] "website" "civility"
## [9] "birthdate" "status"
## [11] "orders_count" "listed_products"
## [13] "listed_products_available" "listed_products_sold"
## [15] "last_open_date" "connectionhash"
## [17] "is_optout" "is_bounce"
## [19] "orders_12M" "GMB_12M"db_col_classes <- rep("NULL", times = length(db_file_header))
db_col_classes[db_file_header %in% c("id_user", "is_optout","is_bounce")] <- "numeric"
db_col_classes[db_file_header %in% c("civility", "website", "status")] <- "character"
db_users <- read.delim("db_users.csv", sep = "|", colClasses = db_col_classes, quote = "")
head(db_users)## id_user website civility status is_optout is_bounce
## 1 1 FR ms activated 0 0
## 2 2 FR ms activated 0 0
## 3 3 FR mrs deleted 0 1
## 4 60 FR mr activated 0 0
## 5 61 FR mrs activated 0 0
## 6 62 FR ms activated 0 0str(db_users)## 'data.frame': 949015 obs. of 6 variables:
## $ id_user : num 1 2 3 60 61 62 63 64 65 66 ...
## $ website : chr "FR" "FR" "FR" "FR" ...
## $ civility : chr "ms" "ms" "mrs" "mr" ...
## $ status : chr "activated" "activated" "deleted" "activated" ...
## $ is_optout: num 0 0 0 0 0 0 0 0 0 0 ...
## $ is_bounce: num 0 0 1 0 0 0 0 0 0 0 ...db_users_rows <- nrow(db_users)readLines("exp_users.csv", n = 3)## [1] "pk_id\tp_id_user\tp_email\tp_email_status_id\tp_is_optout\tp_is_bounce"
## [2] "7318854\t870141\t'mathilde.labeyrie@gmail.com\t1350\t0\t1"
## [3] "2121963\t178749\t00000yu00000@gmail.com\t100\t0\t0"exp_file_header <- names(read.delim("exp_users.csv", nrows = 5, quote = ""))
exp_file_header## [1] "pk_id" "p_id_user" "p_email"
## [4] "p_email_status_id" "p_is_optout" "p_is_bounce"exp_col_classes <- rep("NULL", times = length(exp_file_header))
exp_col_classes[exp_file_header %in% c("pk_id", "p_id_user", "p_is_bounce", "p_is_optout", "p_email_status_id")] <- "numeric"
exp_col_classes[exp_file_header %in% c("p_email")] <- "character"
exp_users <- read.delim("exp_users.csv", colClasses = exp_col_classes, quote = "")
head(exp_users)## pk_id p_id_user p_email p_email_status_id
## 1 7318854 870141 'mathilde.labeyrie@gmail.com 1350
## 2 2121963 178749 00000yu00000@gmail.com 100
## 3 4121260 898529 007eishockey@web.de 100
## 4 7299470 835815 007elisa2014@gmail.com 100
## 5 3671943 821828 0608644608@orange.fr 100
## 6 7340633 926786 katia_cabrol@yahoo.fr 100
## p_is_optout p_is_bounce
## 1 0 1
## 2 0 0
## 3 0 0
## 4 0 0
## 5 0 1
## 6 0 0str(exp_users)## 'data.frame': 949324 obs. of 6 variables:
## $ pk_id : num 7318854 2121963 4121260 7299470 3671943 ...
## $ p_id_user : num 870141 178749 898529 835815 821828 ...
## $ p_email : chr "'mathilde.labeyrie@gmail.com" "00000yu00000@gmail.com" "007eishockey@web.de" "007elisa2014@gmail.com" ...
## $ p_email_status_id: num 1350 100 100 100 100 100 100 100 100 100 ...
## $ p_is_optout : num 0 0 0 0 0 0 0 0 0 0 ...
## $ p_is_bounce : num 1 0 0 0 1 0 0 0 0 0 ...exp_users_rows <- nrow(exp_users)missed_users <- exp_users[!(exp_users$p_id_user %in% db_users$id_user), ]
missed_users[, c("p_id_user", "p_email")]## p_id_user p_email
## 842 963058 chabouso@yahoo.com
## 939 963021 celia.alexandral@gmail.com
## 2514 963218 celia-deir@hotmail.com
## 13894 963229 kbecker1801@freenet.de
## 16486 889022 totosylvia@yopmail.com
## 18593 963062 ritamadi1@hotmail.com
## 19152 963262 acgentil.sb@live.fr
## 19651 962994 midram55@hotmail.com
## 21498 963186 iznias@yahoo.fr
## 21776 963158 coralie.baudouin@gmail.com
## 22747 963248 fridouli@hotmail.fr
## 29773 963212 j.neef@yahoo.com
## 31578 963266 cperlini@cltautotrasporti.it
## 32985 962996 craigpro1994@gmail.com
## 35418 963067 1jour1clic@gmail.com
## 35651 889000 ftsd-1726_b@test.com
## 39349 963097 lilounguyen@gmail.com
## 42133 963015 lilycat756@gmail.com
## 43609 962993 coraly.34@hotmail.fr
## 47097 963197 marie-maton@laposte.net
## 48123 963149 coraliejacob@hotmail.com
## 49095 963139 celine.tasson@free.fr
## 59285 963140 anneaabelle@gmail.com
## 63353 889001 rezrer@gmail.com
## 63931 962992 aa.rd@mail.ru
## 64213 963155 aaanastassia@gmail.com
## 66145 888970 abab@gmail.com
## 67716 963147 bouli.et.cie@hotmail.fr
## 67818 963024 aboutissement.1@gmail.com
## 70127 963083 eliedanous@gmail.com
## 75563 963073 elodie.rengel@gmail.com
## 76132 963217 sofquinet@gmail.com
## 77895 963181 roserob@laposte.net
## 84290 963004 kikakacimi@hotmail.fr
## 87135 963109 jamais2sansmoi@gmail.com
## 87934 963128 odile-tennis@hotmail.fr
## 94703 963201 olya.palet@gmail.com
## 96981 NA marie.trespeuch@orange.com
## 99645 963127 marie.lequilleuc@hotmail.fr
## 99945 963169 marieaubreym@gmail.com
## 109866 963177 mireille.dumanois@neuf.fr
## 110209 963251 mariedo.zereni@orange.fr
## 111382 963173 milouchey@hotmail.fr
## 111524 963264 marielegentil@hotmail.fr
## 114990 963009 roblet.meganne@hotmail.fr
## 116952 963143 oceane.deniger@sfr.fr
## 119957 963108 cey59@hotmail.fr
## 120719 963019 bjorkiebeetle@hotmail.com
## 122947 963052 rozennlavaurfournier@yahoo.fr
## 125199 963207 elisa.cabannes@gmail.com
## 138067 963061 s.cimen@hotmail.fr
## 138920 963141 s.toq@free.fr
## 139586 963221 s.rouie@free.fr
## 144433 963151 bline60@hotmail.com
## 157377 963231 ladyzotika@gmail.com
## 160081 963032 gatito_murr@yahoo.com
## 161246 922352 fzefzefzf@zfefzefze.fr
## 166733 963056 adeliniglesias@hotmail.fr
## 174999 808284 ctaytest@yopmail.com
## 175239 963242 mistral.amh@wanadoo.fr
## 183670 963192 marilou902@yahoo.fr
## 184226 963050 mlecroix@me.com
## 185622 963040 elyass.lahjiouj@gmail.com
## 186734 963100 marieameliechotard@hotmail.fr
## 193077 963157 kwetsch@hotmail.com
## 193234 963214 elise.teppa@free.fr
## 203539 963254 laetitia.prete@free.fr
## 205772 963028 valou76450.vrl@gmail.com
## 207471 963203 gemin.lea@hotmail.fr
## 208909 963088 cylt@wanadoo.fr
## 210247 963252 geraldine.ofter@neuf.fr
## 214207 963071 emilie18@outlook.fr
## 214262 963118 parissima.saleh@cartier.com
## 222978 963116 apolline.bgd@gmail.com
## 226463 963085 jeremy.rouche@gmail.com
## 233000 962998 pascal5629@outlook.com
## 238835 963180 pascalelebleu@free.fr
## 239475 963174 loverfloweb@yahoo.com
## 243318 962995 ml.bristol@live.fr
## 243504 963047 lizunkova.olga@gmail.com
## 252729 963199 dairo@wanadoo.fr
## 260236 963124 mlouvieaux@gmail.com
## 260451 963215 mortellier03@orange.fr
## 266184 963030 moysan_catherine@yahoo.fr
## 272595 963178 saintorens.morgane@gmail.com
## 272946 962990 patriceyohou@yahoo.fr
## 275688 963046 brabantbeatrice@aol.com
## 278463 963255 apemaya86@yahoo.it
## 282868 963043 muriel.bouharmont@hotmail.fr
## 285897 963226 jessicaleclech@yahoo.fr
## 286745 962983 brocmathilde@gmail.com
## 290984 963211 jessica.agnel@gmail.com
## 293884 963228 elodieduhamelle@gmail.com
## 296194 963091 chaimaetoile@hotmail.fr
## 305146 963239 laizalola@hotmail.fr
## 309854 963069 charlotte.marc7@gmail.com
## 311953 963208 gostozinho@hotmail.fr
## 312838 963092 paulegillier@orange.fr
## 317208 963265 lalacorinne@gmail.com
## 319843 963259 monik.cossalter@gmail.com
## 329629 963096 marionbeaufils@hotmail.fr
## 337414 963044 athmanzegh@hotmail.fr
## 337626 962999 marielaure.gauthier@gmail.com
## 341634 963258 stabylojo@yahoo.fr
## 342512 963189 stef_legrand@hotmail.com
## 354465 963105 emmanuelle.farcette@hotmail.fr
## 359908 963164 erwan-speeg@numericable.fr
## 360862 790419 chgg@hhj.bh
## 364874 963099 pauline57_23@hotmail.com
## 366677 963232 pauline.ziade@yahoo.fr
## 369406 963068 cristinadon@hotmail.it
## 373200 963250 luiza877@yahoo.com
## 376436 963236 martine.calbrix@bbox.fr
## 381124 963224 muriellegoyat29@gmail.com
## 387189 963076 chloe.maupu@gmail.com
## 389624 963235 val02woman@yahoo.fr
## 391695 962987 valegg24@hotmail.com
## 394284 963063 audreydiana@hotmail.fr
## 394587 963010 chris.lesko75@gmail.com
## 398912 963005 esimo84@gmail.com
## 400517 963066 myam.shugard@gmail.com
## 405637 963159 perri.sasha@gmail.com
## 408516 963020 perrineregnier51@gmail.com
## 413582 963210 stephanie.pecault@gmail.com
## 414617 963247 marvin_dienst@live.de
## 415637 963138 stephaniebittespires@gmail.com
## 417191 963081 myriammarc@hotmail.fr
## 421059 963172 myriam_maestri@yahoo.fr
## 421807 908742 jonathan@test6.com
## 422569 963244 jos.bielawski@gmail.com
## 422596 963219 joseph.papus@laposte.net
## 424465 963065 lydie.juste@laposte.net
## 428819 963145 jsapiega@gmail.com
## 428948 963070 julia.chinon@gmail.com
## 435132 963222 juclero@aol.com
## 437377 963182 esther.chevaleyre@gmail.com
## 442360 963168 delphine.gouchault@gmail.com
## 447648 963191 caillotchris@yahoo.fr
## 447659 963035 calamityfred2@gmail.com
## 448166 963026 camille.brissot@gmail.com
## 450112 963002 alarcon.f@wanadoo.fr
## 451720 963130 mathilde.jastreb@gmail.com
## 452280 963114 mathilde.leplat@gmail.com
## 455069 963115 julie-785@live.fr
## 455414 963243 delphinedizier@yahoo.fr
## 457106 963223 denis.steve.ont@gmail.com
## 457876 963086 johanna.gaye@hotmail.fr
## 459834 908782 jonas.maumene+vendeur@videdressing.com
## 464246 888939 sylvia2602@test.com
## 464248 963148 sylviabideau@gmail.com
## 465348 962988 sylviae@hotmail.de
## 467035 888933 sylvia1302@test.com
## 467421 963249 sylviane.bordas@gmail.com
## 468556 963093 chloevelut@gmail.com
## 470822 888917 dfzfzef@ffzfze.fr
## 471979 963126 dfjanin@hotmail.fr
## 474012 963082 camille.deluca@free.fr
## 478230 963090 piki057@hotmail.fr
## 478587 889031 sylvia03025@test.com
## 481583 963187 devignes.camille@gmail.com
## 487234 963150 myriam.messaoudi@swisslife.fr
## 488141 963131 aline.lafont@yahoo.com
## 488857 963196 alinebondeau@bbox.fr
## 493489 963123 lauren.lafont@yahoo.com
## 506387 963049 hafidakenny@yahoo.fr
## 507130 963240 sarahmaman@hotmail.fr
## 510658 963205 saramuri8@gmail.com
## 511748 963051 f2x35@hotmail.fr
## 512743 963054 fabienne.est@gmail.com
## 514345 963008 super.eichi@yandex.com
## 516120 963184 sabeukven@gmail.com
## 519636 888967 pi@test.com
## 520224 888978 pierre@pierre.com
## 520299 963014 dannavenga2006@yahoo.fr
## 528469 962997 fabiolui.s@live.com.pt
## 529406 963137 m.metivier65@gmail.com
## 532990 963074 leaguillaut@hotmail.fr
## 534729 963206 vivoun@hotmail.fr
## 541576 963111 mchaudhary@hotmail.fr
## 542700 963195 alliot_camille@yahoo.fr
## 547094 963017 heberttheresa@googlemail.com
## 549999 963113 vincekite51@hotmail.com
## 550150 963120 alexandra.barisone@hotmail.fr
## 550750 2345676 alexandre.bry@experian.com
## 552824 963132 vincentchris@live.fr
## 564805 963146 walsh.emilie@gmail.com
## 566338 963185 mainamila13@hotmail.fr
## 568498 963256 maine.repos@wanadoo.fr
## 569902 963013 domdaries@orange.fr
## 574459 963234 heike.armery@gmail.com
## 579022 963253 f.forthoffer@orange.fr
## 579806 963193 davidcharlesstj@gmail.com
## 584698 963198 maudlgnantes@yahoo.fr
## 588047 963257 nanachocolat@live.fr
## 593242 963037 claire.rouxel@free.fr
## 594268 963107 winniesev@hotmail.com
## 595541 963246 pierre-jean.loubere@orange.fr
## 597120 963022 barraultangelique@orange.fr
## 600849 963161 juls.did@gmail.com
## 601770 963079 july0054@gmail.com
## 602391 963204 julypinklady@gmail.com
## 605982 963089 juliechassine@hotmail.fr
## 608673 962985 laurentvachier@gmail.com
## 610264 963175 julie_z2@hotmail.com
## 615958 962991 wtfcatxoxo@gmail.com
## 616600 963106 claireccg@hotmail.fr
## 623300 963018 laurie.giannini21@gmail.com
## 624360 963170 lauresaintfelix@laposte.net
## 625215 963225 cassilia.pujol@gmail.com
## 626306 963263 laury.30@hotmail.fr
## 627597 963121 bastinchantal90@gmail.com
## 641835 963036 b.clerc@neuf.fr
## 642609 888941 faustinem@yopmail.com
## 643421 963007 b_hadem@hotmail.fr
## 650039 963057 cissnei@live.it
## 656521 963041 cjegoux@gmail.com
## 677992 962986 majda.ben1@voila.fr
## 685658 963094 poda.ana@gmail.com
## 695351 963202 td1987@hotmail.be
## 697144 963152 pub31130@gmail.com
## 702319 963238 magalidevaux@hotmail.com
## 705751 963119 medior08@hotmail.fr
## 707449 963129 megbusiness@hotmail.fr
## 718306 963101 lecoinbon@gmail.com
## 721147 963156 mel_zobda@orange.fr
## 725434 963006 amharrar.h@hotmail.fr
## 726733 963064 amelie.renaux@grenoble-em.com
## 728115 963011 ines.vernerey@gmail.com
## 731855 963216 younes.gajjaoui@gmail.com
## 731932 963078 yuvalemarsys@yahoo.fr
## 734109 963084 justinerozieres@hotmail.com
## 736654 963194 clara.acton333@gmail.com
## 736885 963122 lemoingaurore@gmail.com
## 745923 963059 kaburu@laposte.net
## 747924 963154 benjamin.bau@laposte.net
## 760217 963200 clarisse.connan@yahoo.fr
## 760551 888934 test13022@test.com
## 761313 963103 baude.christine@gmail.com
## 761646 963095 baudelet.baptiste@gmail.com
## 762867 963110 claudia.wypych@hotmail.fr
## 763363 922363 test2_fr@test.fr
## 769372 963188 sefiani.zineb1@hotmail.fr
## 771417 963023 angele.rouget@gmail.com
## 776925 963000 nelsonrogerio17@gmail.com
## 782307 963220 r.guernalec@orange.fr
## 782893 963241 shelababo@yahoo.fr
## 788151 963033 cecile.kayat@hotmail.fr
## 791976 963166 mf.pires@free.fr
## 797619 234567654 thalyvest@hotmail.com
## 798353 963012 lilas.henriet@hotmail.fr
## 800168 963160 thais.mari.palacios@gmail.com
## 801776 963171 nicolas.twiggy.rommel@gmail.com
## 810985 963233 bebe14@orange.fr
## 814710 963031 ana_dias5@hotmail.fr
## 820529 963230 rachidakhell@yahoo.fr
## 821543 963133 raholijaona.miharizo@gmail.com
## 821910 963237 ranakpur@gmx.ch
## 823737 963038 ramanath67@yahoo.fr
## 826339 963209 melolabosse@hotmail.fr
## 826827 963102 isabelle-steiner@hotmail.de
## 826943 963072 isabelle.lambert1111@bbox.fr
## 830599 963134 maomeiline@hotmail.com
## 836750 963144 katell.verrier@hotmail.fr
## 841991 963167 anapaula01@hotmail.fr
## 844794 963125 anastassiakrez@gmail.com
## 852940 963077 siskatdeu@magic.fr
## 858533 963098 e.masse6@laposte.net
## 862193 963055 innocent78@hotmail.fr
## 863242 963227 tipoute@hotmail.fr
## 866493 963135 titefloflodu78@hotmail.fr
## 866739 963034 dreygas87@gmail.com
## 871092 963048 annabellefarras@gmail.com
## 874964 963104 tomsinhelene@hotmail.com
## 875360 963003 nenette.kmli@hotmail.fr
## 880984 963213 seouane@gmail.com
## 883440 963029 servicepaie.sorom@yahoo.fr
## 890959 962989 remywohrel@gmail.com
## 893151 963176 bernardnalot31@gmail.com
## 895698 963261 zayoun@free.fr
## 896485 963162 cohenisabelle16@yahoo.fr
## 898326 963075 cocom92@yahoo.fr
## 901951 963142 colette.bernard@systeme-u.fr
## 902963 963087 andronagathe@outlook.fr
## 906637 963027 cathymey19@gmail.com
## 911476 963153 ela1963.eb@gmail.com
## 913476 963001 xxxxx6169@gmail.com
## 915476 963117 biemonchristine@gmail.com
## 917193 963080 zouli_06@yahoo.fr
## 917651 963053 ilaria.cossettini@gmail.com
## 920024 963112 folivier@hotmail.com
## 920183 962984 follotjulien1@gmail.com
## 923801 963179 nonobabahi@gmail.com
## 924475 963025 nonocoy@live.fr
## 925020 963060 bidouche1975@hotmail.fr
## 926306 963165 cecilelamalke@orange.fr
## 926519 963190 cecilelamalle@orange.fr
## 928975 963016 maria.g.kakoulli@gmail.com
## 935932 963039 francoise.ponsonnaille@wanaddddoo.fr
## 938036 963136 zaaqh54@laposte.net
## 945084 569626
## 945760 569628
## 946324 597195
## 946360 569623
## 946363 569657
## 946438 569627
## 947066 963042 zaogasy83@yahoo.com
## 947548 963260 zoebouillon.pro@gmail.com
## 947966 569669
## 947967 026 rows have test emails and email with bug, the rest is new suibscribers need to delete this wrong users.
table(exp_users$p_email_status_id)##
## 100 500 1300 1350 1500 1525 1550 1575
## 849469 27070 30989 31839 1 2886 5312 1758head(exp_users$p_id_user[exp_users$p_email_status_id == 1350], n = 10)## [1] 870141 505986 392687 300067 9984 76220 704878 400587 393060 221492status <- data.frame(status_id = 1350, status = "Hard Bounce", stringsAsFactors = F)
head(exp_users$p_id_user[exp_users$p_email_status_id == 1300], n = 10)## [1] 212086 168448 1610 439672 4751 783558 187104 493759 294221 208744status <- rbind(status, c(1300, "Soft Bounce"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 500], n = 10)## [1] 150369 101068 137674 71386 191781 154409 202973 155238 94423 164187status <- rbind(status, c(500, "Opt-Out (from Proof; still Active)"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 100], n = 10)## [1] 178749 898529 835815 821828 926786 757648 913521 666108 674927 72919status <- rbind(status, c(100, "Active"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 1500], n = 10)## [1] 214109status <- rbind(status, c(1500, "Opt-Out via Form"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 1525], n = 10)## [1] 639996 85498 754993 55970 219092 727224 69252 543528 225770 86193status <- rbind(status, c(1525, "Opt-Out via Reply"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 1550], n = 10)## [1] 406517 751781 70495 311487 575137 351878 216826 58397 654473 192407status <- rbind(status, c(1550, "Opt-Out via Feedback Loop"))
head(exp_users$p_id_user[exp_users$p_email_status_id == 1575], n = 10)## [1] 456157 386893 887152 869211 191475 275947 724913 851895 744118 516456status <- rbind(status, c(1575, "Banned Email/Phone"))
status$status_id <- as.numeric(status$status_id)
status <- status[order(status$status_id),]
status## status_id status
## 4 100 Active
## 3 500 Opt-Out (from Proof; still Active)
## 2 1300 Soft Bounce
## 1 1350 Hard Bounce
## 5 1500 Opt-Out via Form
## 6 1525 Opt-Out via Reply
## 7 1550 Opt-Out via Feedback Loop
## 8 1575 Banned Email/Phoneexp_users <- merge(exp_users, status, by.x = "p_email_status_id", by.y = "status_id")
table(exp_users$status)##
## Active Banned Email/Phone
## 849469 1758
## Hard Bounce Opt-Out (from Proof; still Active)
## 31839 27070
## Opt-Out via Feedback Loop Opt-Out via Form
## 5312 1
## Opt-Out via Reply Soft Bounce
## 2886 30989exp_users$is_active <- exp_users$status == "Active"
active_users <- sum(exp_users$is_active)users <- merge(exp_users, db_users, by.x = "p_id_user", by.y = "id_user")
head(users)## p_id_user p_email_status_id pk_id p_email
## 1 1 1575 2034688 webmaster@neovia.pro
## 2 2 1575 2034751 meryl.job@gmail.com
## 3 3 1350 3908051 lostuser@videdressing.com
## 4 60 100 2034754 renaudguillerm@wanadoo.fr
## 5 61 100 3932777 julienguillerm@wanadoo.fr
## 6 62 100 2034756 ventesmlpk@gmail.com
## p_is_optout p_is_bounce status.x is_active website civility
## 1 0 0 Banned Email/Phone FALSE FR ms
## 2 0 0 Banned Email/Phone FALSE FR ms
## 3 0 1 Hard Bounce FALSE FR mrs
## 4 0 0 Active TRUE FR mr
## 5 0 0 Active TRUE FR mrs
## 6 0 0 Active TRUE FR ms
## status.y is_optout is_bounce
## 1 activated 0 0
## 2 activated 0 0
## 3 deleted 0 1
## 4 activated 0 0
## 5 activated 0 0
## 6 activated 0 0sum(users$p_is_optout != users$is_optout)## [1] 0sum(users$p_is_bounce != users$p_is_bounce)## [1] 0users <- users[ , !(names(users) %in% c("is_optout", "is_bounce"))]
names(users) <- gsub("p_", "", names(users))
users$is_deleted <- users$status.y == "deleted"res <- dbSendQuery(con, "select id_user, newsletter, date_datesubscribe, date_dateunsubscribe from il_db_user_newsletter")
user_newsletter <- fetch(res, n = -1)
dbClearResult(res)## [1] TRUEdbDisconnect(con)## [1] TRUEuser_newsletter$subscribed <- as.Date(user_newsletter$date_datesubscribe)
user_newsletter$unsubscribed <- as.Date(user_newsletter$date_dateunsubscribe)
user_newsletter <- user_newsletter[, !grepl("date_", names(user_newsletter))]
head(user_newsletter[user_newsletter$subscribed > user_newsletter$unsubscribed && !is.na(user_newsletter$unsubscribed),])## [1] id_user newsletter subscribed unsubscribed
## <0 rows> (or 0-length row.names)table(user_newsletter$newsletter)##
## aufeminin elle
## 4137 1224
## glossybox op_aufeminin_coregistration
## 869 20
## op_elle_coregistration op_glossybox_coregistration_003
## 197 641
## videdressing_main videdressing_partner
## 575129 177899
## videdressing_sms
## 7356table(user_newsletter$newsletter)##
## aufeminin elle
## 4137 1224
## glossybox op_aufeminin_coregistration
## 869 20
## op_elle_coregistration op_glossybox_coregistration_003
## 197 641
## videdressing_main videdressing_partner
## 575129 177899
## videdressing_sms
## 7356head(user_newsletter[user_newsletter$subscribed > user_newsletter$unsubscribed && !is.na(user_newsletter$unsubscribed),])## [1] id_user newsletter subscribed unsubscribed
## <0 rows> (or 0-length row.names)