données

Mon antisèche Google Sheet pour la préparation de données

Bosser en CRM, c’est avant tout bosser dans les données client. Il faut donc les aimer, aimer les voir et aimer les manipuler. Pour ça, Excel est notre meilleur ami… sauf pour des jeux de données léger, pour lesquels je préfère largement utiliser Google Sheet. Année après année, j’ai appris des routines et des manipulations que j’utilise fréquemment sur cet outil. Cet article a pour but de les rassembler et de vous les partager.

NB : cet article n’est pas figé, je le mets régulièrement à jour avec de nouvelles formules et manipulations que je trouve utiles… Dernière mise à jour : février 2020

Au programme :

Quelques détails avant de commencer

Avant d’entrer dans le vif du sujet, quelques petits détails qui ont leur importance dans cet article.

1) Je travaille avec l’interface en français mais avec les fonctions en anglais, et ce pour une raison simple : c’est plus facile de trouver des ressources d’aide en anglais qu’en français.
⇒ Pour régler ce paramètre, cliquez sur “Fichier” puis “Paramètres de la feuille de calcul” et cocher “Toujours utiliser les noms de fonction en anglais”.

2) Cet article n’a pas pour vocation à couvrir les bases de l’utilisation de Google Sheet, comme par exemple masquer/afficher des lignes/colonnes, protéger des plages de données ou des feuilles, figer des lignes/colonnes ou encore appliquer une jolie mise en forme. Je liste pour le moment des manipulations un peu plus avancées, mais sans aller jusqu’aux macros ou aux scripts.

Google Sheet plutôt qu’Excel ?

Comme je le disais en introduction, j’ai une préférence pour Google Sheet par rapport à Excel.

D’abord pour son ergonomie que je trouve plus agréable. Que ce soit pour naviguer dans les menus, pour mettre en forme une feuille, ou pour manipuler des données (déplacement de colonnes/lignes, masquage, filtres…). La création de graphs est aussi rendue largement plus simple et plus précise par Google Sheet.

Un autre gros avantage de Google Sheet est son caractère cloud-based : chaque modification est automatiquement sauvegardée dans le cloud, ce qui évite de perdre ses modifications en cas de plantage d’Excel (certes, il arrive aussi que GS plante lorsqu’il est trop sollicité).

Au-delà de la sauvegarde, son côté cloud-based présente d’autres avantages :

pouvoir collaborer avec d’autres personnes sur un seul fichier, en même temps ;
éviter de devoir créer plusieurs versions d’un même document ;
pouvoir accéder à un document de n’importe où et avec n’importe quel appareil ;
et surtout pouvoir s’intégrer par API avec d’autres web-app, via Zapier par exemple (vraiment utile parfois).

Cela dit, si Google Sheet suffit dans de nombreux cas, il est parfois limité et l’utilisation d’Excel devient alors inévitable. Par exemple lorsqu’on doit traiter des jeux de données volumineux. Ou lorsqu’on veut appliquer des transformations complexes (via du code ou via des extensions Excel comme Kutools qui peuvent faire gagner beaucoup de temps).

Quelques nuances par rapport à Excel pour finir :

je trouve que sa dernière version pour Mac plante beaucoup moins que ses prédécesseurs ;
il existe désormais une version cloud-based de Excel, mais elle est loin d’être aussi répandue que les version desktop (et elle n’est pas gratuite) ;
depuis peu, les fichiers Excel peuvent être ouverts dans Google Sheet sans création d’une copie, mais cela retire les bénéfices d’Excel.

Enfin, la plupart des trucs listés ci-dessous peuvent être effectués dans Excel avec quelques ajustements mineurs.

Rendre un fichier CSV exploitable

Le format standard des fichiers de données que vous pouvez exporter des différents outils martech est le format CSV, pour “comma-separated value”.
Comme son nom anglais l’indique, dans un fichier CSV, les valeurs sont séparées par une virgule, en tout cas la plupart du temps.

Ainsi, lorsque vous ouvrez un fichier CSV dans Google Sheet, les données de chaque ligne seront regroupées dans une seule colonne, chaque donnée étant séparée de la suivante par une virgule.

On doit donc diviser cette colonne unique en autant de colonnes que nous avons de données.

Il existe justement une fonctionnalité Google Sheet pour ça, qui s’appelle “Scinder le texte en colonnes” (accessible dans l’onglet “Données”). Vous pouvez ensuite laisser Google Sheet détecter un séparateur ou sélectionner le vôtre.

Résultat : la colonne unique est scindée en plusieurs colonnes, et on va maintenant pouvoir manipuler tout ça.

Quelques précisions :

On pourrait aussi utiliser une fonction pour faire ça. Il s’agit de la fonction SPLIT, dont la notice officielle peut être consultée ici.
Une fois n’est pas coutume, je trouve que cette fonctionnalité est un peu mieux faite sur Excel, plus claire et un peu plus puissante.
Cette fonctionnalité n’est pas seulement utile pour rendre exploitable un CSV. Elle peut par exemple servir à scinder une colonne “date” en trois colonnes “année”, “mois” et “jour” qu’on pourra ensuite utiliser à des fins d’analyses (dans un tableau croisé dynamique par exemple).

Supprimer les doublons

Dans un jeu de données, il est fréquent que certaines données soient présentes en double, en triple…

Prenons un exemple : un fichier contenant une liste de transactions. Il est fort probable que j’y retrouve plusieurs fois le même client, qui aurait fait plusieurs transactions. Maintenant, si je veux connaître le nombre de clients uniques, je dois dédoublonner le tout.

J’ai alors plusieurs options. Si je veux juste connaître le nombre de valeur uniques, j’utilise la formule =UNIQUE(plage), où la plage correspond à la liste de données à dédoublonner. En saisissant cette fonction dans une cellule vide (elle-même dans une colonne vide), je vais obtenir la liste des valeurs uniques contenues dans ma plage de départ.

Si je veux plutôt un décompte du nombre de valeur unique, je vais coupler ma fonction UNIQUE avec une fonction COUNT, comme ceci : =COUNT(UNIQUE(plage))

Et voilà !

Si par contre vous ne vouliez pas compter les valeur unique mais plutôt supprimer les valeurs en double, je préfère alors passer par la fonctionnalité “Supprimer les doublons”, accessible dans l’onglet “Données”.

Commencer par sélectionner tout son jeu de données, et pas seulement la colonne où se trouvent les doublons. Ensuite, dans la fenêtre qui s’ouvre, cliquer sur “Données avec ligne d’en-tête” puis cocher seulement la colonne dans laquelle se trouve les doublons, avant de valider.

Filtrer les données

Fonctionnalité incontournable des tableurs : les filtres !

Généralement, je l’applique à mon jeu de données complet : je sélectionne toutes mes colonnes puis je clique sur l’icône “Entonnoir” dans la barre d’outils.

Résultat : chaque colonne devient filtrable. Vous pouvez alors ordonner la colonne, décider de n’afficher que certaines valeurs, ou même créer des filtres par condition. C’est vraiment puissant et très utile.

NB : la plage filtrée est entourée par un cadre vert. Si vous ajoutez une colonne sur la droite de la zone, elle ne sera pas incluse dans le filtre. Il vous faut alors supprimer le filtre puis le recréer en incluant la nouvelle colonne.

Sauvegarder un filtre

Lorsque vous avez créé une vue filtrée, il peut être pratique de la sauvegarder pour la réutiliser plus tard, sans avoir à réappliquer manuellement toutes les conditions du filtre.

Pour ce faire, lorsque votre filtre est encore appliqué, il vous suffit de cliquer sur la petite flèche à droite de l’icône “Entonnoir” et de sélectionner “Enregistrer en tant que vue filtrée”. Vous pouvez ensuite renommer votre vue puis fermer l’affichage. Ensuite, lorsque vous voudrez réafficher cette vue filtrée, il vous suffira d’aller dans le même menu et de sélectionner votre vue pour qu’elle s’affiche en un instant.

Réconcilier deux jeux de données (Vlookup)

Voilà une fonction un peu technique mais incontournable. Elle permet de réconcilier deux jeux de données à l’aide d’une clé.

Cette clé doit se trouver dans les deux jeux de données, pour qu’on puisse ensuite faire la correspondance. Elle peut être l’ID client, l’adresse email, ou autre.

Et la fonction est le VLOOKUP (pour “recherche verticale”).

Allons-y avec un exemple. Mon premier jeu de données est la liste de mes clients. Mon second jeu est la liste des transactions/visites en magasin. Je veux savoir lesquels de mes clients sont venus le mois dernier. Généralement, je réunis mes deux jeux de données dans un même document, dans deux feuilles différentes.

Dans ma feuille contenant les clients, je vais saisir ma fonction dans une colonne vide: =VLOOKUP(clé_à_rechercher; plage_de_recherche; index; FALSE)

La première valeur est la clé à rechercher. Par exemple, l’ID client. Je vais donc sélectionner la cellule de mon premier jeu de données dans laquelle se trouve cette clé.

La seconde valeur est la plage de recherche, c’est à dire les cellules de mon second jeu de données dans lesquelles je dois retrouver ma clé. Dans ce cas, on peut sélectionner uniquement la colonne dans laquelle se trouvent les ID clients, ou bien toutes les colonnes du second jeu (ça peut être utile). Attention : la première colonne de la plage de recherche doit impérativement être celle dans laquelle se trouvent les clés (si ce n’est pas le cas, ré-organiser les colonnes du second jeu pour y arriver).

La troisième valeur correspond à ce que renverra la fonction si elle trouve une correspondance. Mais cet “index” doit impérativement être un chiffre qui correspondra à une colonne de la plage sélectionnée en seconde valeur. Pfiou, ça se complique.
Exemple : mettons que j’ai deux colonnes dans ma plage de recherche, l’ID client et la date de visite. Si j’utilise le chiffre 1 pour cette troisième valeur de ma formule, le résultat que j’obtiendrai sera soit l’ID client si une correspondance est faite, soit un #N/A si la formule ne retrouve pas la clé recherchée.
Mais en utilisant le chiffre 2 en index, la formule ne ressortira pas l’ID client en cas de clé trouvée, mais la date de visite, soit la valeur présente dans ma colonne n°2.
Ça peut donc faire gagner pas mal de temps…

La dernière valeur, je l’ai jamais trop comprise. Elle est facultative et doit être TRUE ou FALSE (TRUE si vous ne la renseignez pas). Moi, on m’a expliqué le VLOOKUP avec un FALSE, et depuis j’utilise toujours le FALSE. Peut-être qu’un jour j’essaierai de gratter pour mieux comprendre.

Bref, voilà ce que ça donne :

Une fois la formule appliquée dans une cellule, je la réplique sur l’ensemble de ma colonne. Puis je supprime les valeurs #N/A après avoir appliqué un filtre. Puis j’aplatis le reste des données pour ne conserver que les valeurs de sortie, et non les formules qui peuvent se briser si je modifie d’autres colonnes

NB : pour aplatir des données, je sélectionne toute la colonne, puis je fais un Ctrl+C puis Ctrl+Maj+V (ce copié-collé ne colle que les valeurs, par les formules).

Nettoyer les noms et prénoms

Les données CRM sont souvent des données saisies manuellement, et elles ne respectent donc pas nécessairement une nomenclature précise. C’est notamment le cas des Prénoms, Noms ou Adresses, qui sont parfois écrits tout en minuscules, ou tout en majuscules, ou avec seulement la première lettre en majuscule. Pour harmoniser le tout, il existe des formules :

PROPER(cellule) qui mettra la première lettre en majuscule
LOWER(cellule) qui passera tout en minuscules
UPPER(cellule) qui passera tout en majuscules

Trouver & remplacer

Voilà une fonctionnalité un peu plus simple que les autres, et pourtant très utile. Je parle ici du classique “Ctrl+F” qui ouvre un champ de recherche qui permettra lui-même de trouver ce qu’on veut dans la feuille de calcul.

Mais là où ça devient plus puissant, c’est quand on clique sur le petit bouton avec les trois points superposés, juste à côté du champ de recherche. On ouvre alors la fenêtre “Rechercher et remplacer”, et en plus de trouver une valeur, on peut la remplacer par une autre valeur de son choix.

Je me sers souvent de cette fonctionnalité pour nettoyer des jeux de données.

Regrouper deux cellules

Pour regrouper deux cellules dans une seule, il existe deux fonctions :

CONCAT(chaine1; chaine2) qui permet de regrouper exactement deux cellules.
CONCATENATE(chaine1; chaine2;…) qui est plus flexible : elle permet de regrouper autant de cellules qu’on le souhaite, et même d’ajouter des caractères de son choix).

Par exemple, si j’ai une colonne avec le prénom et une colonne avec le nom, je peux utiliser CONCATENATE pour créer un email factice (en utilisant même LOWER pour n’avoir que des minuscules) :
=CONCATENATE(LOWER(celulle_prénom); ”.”; LOWER(cellule_nom); ”@google.com”)

Analyser des données (tableaux croisés dynamiques)

Les tableaux données dynamiques sont une autre fonctionnalité stars des tableurs. Ils permettent d’analyser rapidement un grand jeu de données en résumant les données qu’il contient.

Comme c’est davantage un outil d’analyse que de préparation de données, j’en ai une utilisation assez basique et occasionnelle.

Plutôt que de me lancer dans un tuto incomplet, je vous renvoie pour le moment vers deux ressources (en anglais) qui vous montreront comment utiliser cet outil. La première ressource est un long article publié sur le blog de Zapier, la seconde est une courte vidéo YouTube qui montre pas à pas comment créer un tableau croisé dynamique.

Formater des numéros de téléphone

Voilà par contre quelque chose que je fais souvent lorsque je prépare des jeux de données client : formater des numéros de téléphone pour permettre d’envoyer des SMS. Et c’est pas une chose simple.

Généralement, je vais chercher à uniformiser les numéros sous la forme “0606060606” ou “+33606060606”. Dans un premier temps, je vais donc supprimer tous les autres caractères, comme les points, les espaces ou les tirets, avec un simple Trouver & remplacer.

Mais c’est après que ça se complique : Google Sheet n’aime pas trop lorsqu’on commence une celulle par un “0” ou par un “+”. Dans le premier cas, il va interpréter la chaîne de caractères comme un nombre (et donc supprimer le premier “0”), et dans le second cas, comme une formule (et donc supprimer le “+” et ajouter un “=” à la place). No bueno.

La parade consiste à ajouter une apostrophe devant la chaîne de caractères : ‘0606060606 ou ‘+33606060606. Google Sheet considérera alors la chaîne comme du texte et ne supprimera rien.

Cette apostrophe sera toujours visible dans la barre de saisie au-dessus de votre feuille, mais elle ne sera pas affichée dans la cellule ni lorsque vous exporterez votre feuille en CSV pour l’importer ailleurs.

Mais si vous êtes tatillon et que vous ne voulez pas des apostrophes dans votre feuille, il existe une autre méthode. A partir de la cellule à laquelle Google Sheet a retiré le premier caractère, créez une nouvelle cellule à sa droite à l’aide d’un CONCAT, comme ceci : =CONCAT(“+”; cellulle_transformee). Google Sheet affichera alors bien le “+”. Vous pouvez ensuite aplatir cette cellule, et le “+” restera.

Raccourcis

Sur Google Sheet, il existe un certain nombre de raccourcis clavier qui permettent de gagner un temps fou, que ce soit pour naviguer ou pour copier des choses. Voici ceux que j’utilise le plus souvent (je travaille sur Mac, je vous partage donc les raccourcis clavier Mac, mais il me semble qu’ils sont assez similaires sur Windows) :

Command + ⬇

Permet de descendre en un clic jusqu’à la dernière valeur renseignée dans une colonne. Si j’ai une colonne est continue (c’est-à-dire que toutes les cellules contiennent une valeur) et que ma cellule active est en première ligne, l’activation de ce raccourci m’emmènera jusqu’à la dernière ligne. Par contre, si la colonne contient des cellules vides, le raccourci m’emmènera à la dernière cellule renseignée avant le “trou”. C’est un raccourci utile pour se déplacer rapidement de haut en bas d’un long fichier.

Command + ⬆

Fonctionnement identique au raccourci précédent.

Et ça marche aussi pour Command + ⬅ et Command + ⮕. Incroyable n’est-ce pas ?

Command + Shift + ⬇

Fonctionnement identique au premier raccourci, mais l’ajout de la touche Shift dans le raccourci permet de sélectionner des cellules. Si vous vous trouvez en première ligne et que vous activez ce raccourci, vous sélectionnerez toutes les cellules entre la première et la dernière cellule de la colonne du moment que toutes les cellules contiennent une valeur. Comme précédemment, si une cellule est vide, le raccourci vous arrêtera à la dernière cellule avant le “trou”.

Evidemment, ça marche aussi avec les autres flèches 😉

Double-clic sur la poignée d'édition

La poignée d’édition, c’est le petit carré bleu en bas à droite d’une cellule active. Si vous appliquez une formule dans une cellule située en ligne 1, et que vous double-cliquez sur cette poignée, la cellule sera appliquée sur toutes les cellules en-dessous. C’est un peu comme si vous tiriez cette poignée vers le bas, sauf que quand vous devez appliquer une formule sur 10000 lignes (un Vlookup par exemple), c’est un peu plus rapide de double-cliquer 😉

Attention : il faut que la colonne située juste à gauche (de celle où vous voulez appliquer vos formules) soit continue, donc qu’il n’y ait aucune cellule vide. S’il y’a des cellules vides, le raccourci ne copiera votre formule que jusqu’au prochain “trou”, pas plus loin.

D'autres formules en vrac

Et pour finir ce répertoire, voici quelques formules supplémentaires que j’utilise de temps en temps.

IF

Cette fonction est super puissante pour créer des conditions. Mais elle est aussi un peu complexe à expliquer, car très flexible.

Sur le papier, elle est pourtant assez simple : =IF(expression_logique; valeur_si_true; valeur_si_false)

En gros, si ceci est vrai, renvoie-moi ça, sinon, renvoie moi ça. La doc officielle de Google Sheet explique bien la logique de cette fonction.

Là où ça se corse, c’est qu’il est possible d’imbriquer plusieurs IF dans une seule formule pour tester plusieurs choses à la fois. Et sans exemple précis, pas simple d’expliquer ça. Je m’en tiens donc là pour le moment 😉

ISURL, ISEMAIL, ISNUMBER

Ces formules permettent de vérifier si la syntaxe d’une cellule correspond à, une URL, un email ou un nombre.

Pour l’URL, la formule va donc vérifier si la chaîne de caractère se termine bien par un “.quelquechose”, ce qu’on appelle une extension de domaine (ou TLD en anglais). Deux précisions : pas besoins que la chaîne commence par http:// ou https://, un simple “google.com” retournera un TRUE. Par ailleurs, on aura un TRUE seulement si l’extension de domaine est connue (.com, .fr, .co, .io) ; les TLDs peu utilisés comme .school, .club ou .bhz retourneront un FALSE.

Pour l’email, même chose que pour l’URL sauf que la formule vérifiera aussi la présente d’un “@”. Et se comportera de la même façon vis-à-vis des extensions de domaine méconnues. Précision ici aussi : la formule ne vérifiera pas si l’adresse email existe, elle contrôle juste la syntaxe.

Pour le nombre, pas vraiment besoin de détailler…

SUM et AVERAGE

Ces formules sont plus utiles pour de l’analyse que pour de la préparation de données, mais j’en parle quand même rapidement. Elles permettent de faire la somme ou la moyenne d’une série de données. Utile par exemple en bas d’un tableau qui récapitulerait les ventes d’un mois, pour connaître le nombre de commandes ou le panier moyen.

COUNT et COUNTIF

Ces deux formules sont utiles pour décompter des éléments. J’ai utilisé la première plus haut dans cet article pour compter des UNIQUE. La seconde fait la même chose mais permet d’ajouter une condition. Par exemple, compter toutes les cellules où la valeur est supérieur à tel montant.