Titre

SQL, Base webStat, statistiques de visites

Présentation

Lorsqu'on navigue sur le net, sur les sites commerciaux, on laisse toujours des traces, à l'insu de notre plein gré!

Voici un petit exemple de base de données où des internautes ont laissé des traces sur un site marchand.

Voyons voir si vous êtes capable de répondre aux questions qui pourraient se poser l'analyse du comportement de l'utilisateur.

Vous pouvez vous-même imaginer différentes recherches pour produire des données à afficher, des croisements de comportement commercial.

Vous avez le droit d'être politiquement incorrect, les sites, souvent basés à l'étranger ne se gènent pas pour autant et font souvent appel à des société spécialisées en analyses de comportement commercial.

J'espère vous faire réfléchir en vous proposant cet exercice, sur les capacité des sites commerciaux à tracer votre comportement.

Dans un avenir proche, je vais faire pareil et vous montrer comment ça marche (anonymement, bien sûr).

Vous pouvez télécharger la base de données au format Access (webStat.mdb)ou mysql (webStat.sql à importer).

N'oubliez pas qu'internet est votre ami ...

A vous de jouer.

Note : la base est (presque) vide, vous pouvez la remplir comme vous le sentez, pour faire des tests.

Schéma relationnel de la base

La base de données est architecturée de la façon suivante :

Nous avons 11 tables (en gras les ppales, en italique, les associations) :

Le client a acheté des produits,
Ces produits appartiennent à des catégories de produits (ski, fixations, snow, chaussures, bâtons ; on peut gérer autre chose ...),
Un cookie ou "tracker", déposé sur l'ordi d'un utilisateur, permet de tracer deux choses :

la visite de pages qui appartiennent à un certain type de page (produit, faq, liste, promo, etc. ...) pour avoir des stats sur la fréquentation du site
l'observation de produits pour avoir des stats sur le comportement commercial
le navigateur utilisé, pour avoir une info technique
remarquer le champ adrIP, pour avoir une autre info technique (il peut y avoir plusieurs cookies sur la même IP => même utilisateur ? Bonne question, hein ?)

Il nous reste identifier qui, après avoir passé la commande, permet de maintenir la connexion entre le site et le client via l'association du cookie et de l'utilisateur. C'est là qu'on saura à qui afficher des pubs ciblées ...

Top of page

Les questions (enfin!)

Série 1 : gentilles

1-01 Quelle est la liste des enregistrements de la table produit ?
1-02 Quelle est la liste des produits de moins de 1Kg
1-03 Quelle est la liste des désignations des produits ?
1-04 Quelle est la liste des désignations des produits entre 15 et 50 euros ?
1-11 Quelle est la liste des clients ?
1-12 Quelle est la liste des clients de Strasbourg ?
1-13 Quelle est la liste des clients de Strasbourg ou de Molsheim ?
1-14 Quelle est la liste des clients de Strasbourg, de Molsheim et Saverne ?
1-21 Quelle est la liste des clients, triée par ville, nom et prénom ?
1-22 Quelle est la liste des id, designation et prix HT des produits, classée par prix décroissant ?

Mots clés utilisés : SELECT, FROM, WHERE, ORDER BY, DESC, OR, AND, BETWEEN, IN et les opérateurs de comparaison

Série 2 : fréquentation du site

Liste et regroupements (12 requêtes)

2-01 Combien y a-t-il de pages web ?
2-02 Combien y a-t-il de pages de Front Office (Access:FrontOffice=-1 ; mysql, frontOffice="f") ?
2-03 Quels sont les noms des pages visitées ce mois-ci (nom page, id page, idCookie) ? (nécessite : DISTINCT, YEAR(), MONTH() et CURDATE())
2-04 Donner la liste des noms de page, id de page et numéro de cookie (attention, pas l'identifiant du cookie).
2-11 Combien y a-t-il de visite ce mois-ci ?
2-12 Combien y a-t-il de visites par page ce mois-ci, donner l'idPage et le nombre de visites ?
2-21 Combien de visites sont effectuées par cookie, donner l'id et le nombre de visites.
2-22 Combien de visites sont effectuées par cookie, donner le numéro de cookie et le nombre de visites.
2-23 Donner la liste des numéros de cookie et d'id de page visitées en éliminant les doublons.
2-24 Refaire la requête 2-22 en utilisant la requête 2-23 à la place de visiter. Qu'obtient-on ?
(requête imbriquée dans FROM, c'est possible!)
2-31 Quelle est le nombre moyen de visites par page ? (requêtes imbriquées)
2-32 Combien de visites ont eu lieues sur les pages de type FAQ ?
2-33 Quel est le chiffre d'affaire hors taxe par client cette année (donner l'id client, son nom, son prénom et le CAClient)?

Mots clés utilisés : ceux de la série 1 et : DISTINCT, COUNT(*), AVG, GROUP BY

Série 3 : Analyse commerciale

La difficulté réside dans la requête ... ou dans la question!

Imbrication : utilisation du résultat d'une sous-requête pour obtenir une valeur ou une liste servant à une comparaison dans la requête cadre.

3-01 Combien y a-t-il de cookie de clients identifiés strasbourgeois (+/-simple) ?
3-02 Quelles sont les id des personnes strasbourgeoises qui ont consulté plus de 10 produits ce mois-ci ? (on leur enverra une promo-initation à l'achat)
3-03 Quelle est la moyenne des consultations par cookie ?
3-04 Combien de personnes par mois ont consulté l'article 1 ?
3-05 Combien de personnes par mois ont acheté l'article 1 ?
3-11 Quels sont les id des 10 articles les plus consultés ? (utiliser la clause LIMIT avec mysql)
3-12 Parmi les articles consultés, quels sont les id de ceux qui n'ont pas été achetés ? (utiliser IN et une requête imbriquée)
3-13 est-il possible de faire une seule requête avec les deux précédentes ?
3-14 Combien de personnes ont consulté l'article 1, en fonction du département ?
3-15 Quels sont les autres id des produits consultés par des clients non identifiés (cookies) qui ont consulté le produit id=123 que je consulte (num de mon cookie=456) ?
Cette requête permet d'afficher le rubrique : "les autres clients ont aussi consulté ces articles ..."
ou : "Ces articles pouraient aussi vous intéresser ..."

Top of page

Corrigés ???

Série 1 : Gentilles

Série 2 : Fréquentation

2-01 Combien y a-t-il de pages web ?
SELECT COUNT(*) FROM page
2-02 Combien y a-t-il de pages de Front Office (Access:FrontOffice=-1 ; mysql, frontOffice="f") ?
(mysql) SELECT COUNT(*) FROM page WHERE frontOffice='f'
2-03 Quels sont les noms des pages visitées ce mois-ci (nom page, id page, idCookie)?
SELECT DISTINCT nomFichier FROM page, visiter WHERE page.id=visiter.idPage AND YEAR(dateVisite)=YEAR(CURDATE()) AND MONTH(dateVisite)=MONTH(CURDATE())

/!\ Jointure et bien indiquer le nom de la table devant les champs homonymes dans les tables

Remarquer : DISTINCT pour éliminer les doublons, YEAR() et MONTH() pour obtenir l'année et le mois de la date et CURDATE() pour obtenir la date courante
2-04 Donner la liste des noms de page, id de page et numéro de cookie (attention, pas l'identifiant du cookie).
SELECT nomFichier, page.id, cookieTracker.numCookie FROM page, visiter, cookieTracker
WHERE page.id=visiter.idPage
AND visiter.idCookie=cookieTracker.id

Deux jointures ! Nombre de jointure = nombre de tables - 1 (= nombre de virgules dans FROM). Ici : 3 tables => 2 virgules = 2 jointures !
2-11 Combien y a-t-il de visite ce mois-ci ?
SELECT COUNT(*) FROM visiter WHERE YEAR(dateVisite)=YEAR(CURDATE()) AND MONTH(dateVisite)=MONTH(CURDATE())
2-12 Combien y a-t-il de visites par page ce mois-ci ?
SELECT idPage, COUNT(*) FROM visiter
WHERE YEAR(dateVisite)=YEAR(CURDATE()) AND MONTH(dateVisite)=MONTH(CURDATE())
GROUP BY idPage
2-21 Combien de visites sont effectuées par cookie, donner l'id et le nombre de visites.
SELECT idCookie, COUNT(*) FROM visiter
GROUP BY idPage
2-22 Combien de pages sont visitées par cookie, donner le numéro et le nombre de visites.
SELECT numCookie, COUNT(*) FROM visiter
GROUP BY idPage
2-23 Donner la liste des numéro de cookie et d'id de page visitées en éliminant les doublons.
SELECT DISTINCT numCookie, idPage FROM visiter, cookieTracker WHERE idCookie=cookieTracker.id
2-24 Refaire la requête 2-22 en utilisant la requête 2-23 à la place de visiter. Qu'obtient-on ?
(requête imbriquée dans FROM, c'est possible!)
SELECT numCookie, COUNT(*) FROM (
SELECT DISTINCT numCookie, idPage FROM visiter, cookieTracker WHERE idCookie=cookieTracker.id
)
GROUP BY idPage
2-31 Quelle est le nombre moyen de visites par page ? (requêtes imbriquées)
SELECT AVG(nbreVisites) FROM (SELECT idPage, COUNT(*) FROM visiter GROUP BY idPage)
2-32 Combien de visites ont eu lieues sur les pages de type FAQ ?
SELECT COUNT(*) FROM visiter, page, typePage WHERE visiter.idPage=page.id AND page.idTypePage=typePage.id
2-33 Quel est le chiffre d'affaire hors taxe par client cette année (donner l'id client, son nom, son prénom et le CAClient)?
SELECT client.id, nom, prenom, sum(qty*prixHT) AS CAClient
FROM client, acheter, produit
WHERE client.id=acheter.idClient AND acheter.idProd=produit.id
AND YEAR(dateAchat)=YEAR(CURDATE())
GROUP BY client.id, nom, prenom

Série 3 : difficiles (très)

3-01 Combien y a-t-il de cookie de clients identifiés strasbourgeois (+/-simple) ?
SELECT count(*) FROM identifier, client WHERE identifier.idClient=client.id AND ville="strasbourg"
3-02 Quelles sont les id des personnes strasbourgeoises qui ont consulté plus de 10 produits ce mois-ci ? (on leur enverra une promo-initation à l'achat)
SELECT client.id FROM client, identifier, consulter
WHERE client.id=identifier.idClient
AND identifier.idCookie=consulter.idCookie
AND ville="strasbourg"
AND YEAR(dateConsult)=YEAR(CURDATE())
AND MONTH(dateConsult)=MONTH(CURDATE())
HAVING count(*)>10
GROUP BY client.id
Théoriquement, nous aurions besoin de la table cookieTracker, mais comme on n'en a besoin que de l'identifiant du tracker, on peut faire un raccourci en allant directement de indentifier à consulter (= transitivité en maths : si a=b et b=c alors a=c ! hé, hé, hé)
3-03 Quelle est la moyenne des consultations par cookie ?
SELECT AVG(nbreConsultations) FROM (
SELECT idCookie, COUNT(*) FROM consultation GROUP BY idCookie )
3-04 Combien de personnes par mois ont consulté l'article 1 ?
SELECT MONTH(dateConsult) AS mois, count(*) FROM consulter WHERE idProd=1 GROUP BY mois ;
ou
SELECT MONTH(dateConsult) AS mois, count(*) FROM consulter WHERE idProd=1 AND YEAR(dateConsult)=YEAR(CURDATE()) GROUP BY mois ;
3-05 Combien de personnes par mois ont acheté l'article 1 ?
SELECT MONTH(dateAchat) AS mois, count(*) FROM acheter WHERE idProd=1 GROUP BY mois ;
3-11 Quels sont les id des 10 articles les plus consultés ? (utiliser la clause LIMIT avec mysql)
SELECT idProd, COUNT(*) FROM consulter ORDER BY COUNT(*) DESC LIMIT 0, 10
3-12 Parmi les articles consultés, quels sont les id de ceux qui n'ont pas été achetés ? (utiliser IN et une requête imbriquée) SELECT DISTINCT idProd FROM consulter WHERE idProd NOT IN (SELECT DISTINCT idProd FROM acheter)
3-13 est-il possible de faire une seule requête avec les deux précédentes ?
SELECT idProd, COUNT(*) FROM consulter
WHERE idProd NOT IN (SELECT DISTINCT idProd FROM acheter)
ORDER BY COUNT(*) DESC
LIMIT 0, 10
3-14 Combien de personnes ont consulté l'article 1, en fonction du département ?
SELECT

La difficulté vient de la façon dont on retrouve le n° de département.
Soit le code postal est numérique et on fait une division entière par 100, soit le code opstal est alphabétique et on fait une extraction de chaine (substr()).

Dans notre cas, on a un nombre entier donc :
- Access : 2 solutions, on utilise :
  - soit l'opérateur "\" : "SELECT 20/3" renvoie 6.66666 et "SELECT 20\3" renvoie 6.
  - soit la fonction int() : "SELECT int(20/3)" renvoie 6.
- Mysql, Oracle Sql, SqlServer, etc... : FLOOR(20/3) renvoie 6.
3-15 Quels sont les autres id des produits consultés par des clients non identifiés (cookies) qui ont consulté le produit id=123 que je consulte (num de mon cookie=456) ?
La réponse est : les id de produits consultés dont l'id de cookie est dans la liste des (id de cookie qui ont consulté le produit 123 sauf moi) :
a) Ensemble des idCookie qui ont consulté le produit 123, sauf le mien ?
R1= SELECT DISTINCT idCookie FROM consulter, cookieTracker WHERE idCookie=cookieTracker.id AND idProd=123 AND numCookie<>456
DISTINCT car on peut avoir consulté plusieurs fois le même article ... on obtient une liste.

b) ensemble des idProd consultés sauf celui que je vois
R2= SELECT DISTINCT idProd FROM consulter AND idProd<>123
Le DISTINCT est là pour éviter les doublons

La totale est les idProd des consultations dont l'idCookie est dans la liste R1
R= SELECT DISTINCT idProd FROM consulter
WHERE icCookie IN (
SELECT DISTINCT idCookie FROM consulter, cookieTracker WHERE idCookie=cookieTracker.id AND idProd=123 AND numCookie<>456
)
Comme on le voit, la difficulté est dans la question, pas dans la réponse ...

Y a-t-il une autre réponse ? Sûrement, mais celle-ci a l'avantage de vous présenter une version ensembliste du langage

Top of page