(Cliquez ici pour la version française)

There are a lot of fancy tools out there for data analysis - some of which are extremely complex, and some of which are actually fairly simple to use and set up. If you are looking to take the next baby step along the road to using your data to make better decisions, a good place to start might be to take a second look at the good old spreadsheet. Spreadsheet programs have a lot of data analysis tools built right in, and today I want to discuss a lesser-known (in my experience) but extremely powerful feature: pivot tables! 

Pivot tables are available in most spreadsheet programs you might use (they even were recently added to Google Spreadsheets), but for this post I'll focus on Excel 2010 since that's what I'm using most at the moment. Where pivot tables really shine is when you have a big, straightforward table full of data and you want to make some links between different types of data (beyond what you can do with filters and sorting). In particular, pivot tables can be a great way to explore your data if you're not quite sure what you're looking for yet.

The Context: tracking donation requests

To show you what I'm talking about, I'll give you an example that is directly relevant to our work here at TechSoup Canada: tracking requests of products in our donations program. I can do an export from my database to get a nice big spreadsheet where each row represents one product that was ordered.

Obviously I've stripped out a lot of data (such as the organization's name and info, and the product they ordered) but I have left in some of the other information such as the type of organization who ordered the product (i.e. what is their primary mission), the province where the organization is from, the number of licenses of the product they ordered (for example, one order might include 5 licenses of Microsoft Office) and the date they ordered.

So if I want to find out the number of orders we had in a given month, that's pretty simple. I would just use Excel's auto-filter to filter the month and year and then count up all the rows.

Question 1: from which province do we get the most orders?

But what if I want to do something more complex? Say I want to find out which province we get the most orders from. I could do some sorting and counting, but it would involve a lot of manual work. This is where pivot tables come to the rescue! I select the data I want (which is my entire table of data, all rows and columns) and go to Insert -> Pivot Table.

Now if you've never used pivot tables before, the screen that you get can be pretty confusing:

Don't be intimidated! What you need to do is to try to visualize the report that you want, and then drag your fields from the list on the right into their spot in the table or the boxes on the right.

If you're struggling to figure out what goes where, just try it out and see if it makes sense or not. In this case, let me rephrase my question in terms of my data: I want to know which province had the highest sum of licenses ordered overall.

I'll start by dragging "Province" to the "Row Labels", so I get a list of all the provinces, and then I will drag "Quantity" to "Values". By default it is doing a count of the quantities, so I right-click go into Field Value Settings and choose sum. Now I have a list of all the provinces and the total number of licenses that have been ordered from each one.

This is great, but it's hard to see which one is most popular. So what I'm going to do is to click on the little arrow by "Province" (the header for the table), pick "More Sort Options". After a bit of playing around, I found that I can pick "Descending (Z to A)" and choose "Sum of QTY" and it sorts the provinces in decreasing order by number of license ordered. In other words, the province that ordered the most licenses is at the top:

Not surprisingly, we get the most orders from Ontario. This makes sense because there are quite a lot of charities, nonprofits and libraries in Ontario. However, it also means that we need to be doing more outreach to other provinces to spread the word about the donations program. So we can use this data to inform our outreach efforts and ensure that our time is used most effectively, where it is most needed.

Question 2: in which month do we get the most orders?

So since this is so much fun, let's do one more example. This time I want to see a trend in the number of orders by month, to see which month we get the most orders. Again, I'm going to select all my data in the main spreadsheet, but this time I'll make use of Excel's newer "Pivot Chart" feature and pick Insert -> Pivot Chart.

As before, I'm going to build the table to show my data in the way I want it, but since I picked the Pivot Chart option, Excel is also going to make me a chart of my data which will make it easier to visualize the trend.

Since I want to see time along the horizontal axis of my chart, I'll drag "Year" and "Month" to the "Axis Field" section. The data that I want to see is the number of orders, so I'll drag "Quantity" to "Values". I notice that it's automatically set to count the values instead of summing them, which is what I want in this case (I want to know about number of orders, not individual licenses). So now I have a table with my values, and a chart as well:

Now this looks good, but a bar chart - which is the default - isn't really the best option for showing a trend over time. Instead, I'm going to change the chart type to be a line chart. As well, I'll do a bit of clean up so the chart is easier to read.

Now, I can clearly see some trends in my data. For example, I can see that March and June have spikes in all 3 years shown here. If I link this to my general knowledge about the sector and the donations program, I know that March is high because many nonprofits' fiscal year ends on March 31, and that June is high because many of our donor programs reset their allotment limits at the end of June. Now that I'm informed by this data, I can start making decisions such as dedicating additional staff time to customer service during these months.

Of course, all this is only just scratching the surface, there are many other ways I could slice and dice this data. As I mentioned at the beginning of this post, being able to easily play around with your data will help you uncover trends and find out which views of your data are most valuable. At the end of the day, this will help you to be more data-informed and to better tell your organization's story through data.

Have you used pivot tables? What ways/tools for analyzing your data have you found helpful?

 

---

 

L'analyse de données à la portée de tous ! Une courte introduction aux tableaux croisés dynamiques

Il existe de nombreux outils dédiés à l'analyse de données : certains sont extrêmement sophistiqués, et d'autres sont plutôt simples à utiliser et à configurer. Si vous êtes prêt à entreprendre un tout petit effort pour prendre de meilleures décisions à l'aide de vos données, il vous suffit juste de vous replonger dans une bonne vieille feuille de calcul. Les tableurs disposent de beaucoup d'outils d'analyse de données, et je voudrais vous parler aujourd'hui d'une fonctionnalité peu connue (d'après mon impression), mais extrêmement puissante : les tableaux croisés dynamiques ! 

Les tableaux croisés dynamiques sont inclus dans la plupart des tableurs que vous pouvez utiliser (cette fonctionnalité a même été ajoutée récemment à celui de Google Docs). Dans ce billet toutefois, je me concentrerai sur Excel 2010 puisque c'est celui dont je me sers le plus en ce moment. Les tableaux croisés dynamiques vous faciliteront la vie si vous avez des grands tableaux de données, simples et complets, et que vous souhaitez établir des liens entre les différents types de données (au-delà de ce que vous pouvez faire avec des filtres et du tri). Ils peuvent constituer notamment un excellent moyen d'explorer vos données si vous ne savez pas bien encore ce que vous cherchez.

Mise en situation : suivi des demandes de dons

Afin d'illustrer ma présentation, je vais vous donner un exemple nous concernant directement à TechSoup Canada : le suivi des demandes de produits dans le cadre de notre programme de dons. Je peux exporter ma base de données pour obtenir une belle grosse feuille de calcul, dans laquelle chaque ligne représente un produit commandé.

J'ai bien évidemment retiré un grand nombre de données (comme le nom des organisations, leurs coordonnées et les produits qu'elles ont commandés), mais j'ai conservé d'autres informations telles que le type d'organisation à l'origine de la commande (c'est-à-dire la nature de sa mission principale), la province dont elle est originaire, le nombre de licences commandées (une commande peut compter par exemple 5 licences de Microsoft Office) et la date de demande.

Si je veux connaitre par exemple le nombre de commandes passées dans un mois donné, la procédure est assez simple. Il me suffit d'utiliser le filtre automatique d'Excel pour filtrer le mois et l'année voulus, puis de compter le nombre de lignes.

Question no 1 : de quelle province recevons-nous le plus de commandes ?

Mais comment faire si je veux réaliser quelque chose de plus complexe ? Mettons que je veuille savoir quelle est la province où le plus de commandes sont passées. Je pourrais me débrouiller en procédant à quelques tris et comptages, mais cela demanderait beaucoup de travail manuel. C'est là que les tableaux croisés dynamiques viennent à la rescousse ! Je sélectionne les données qui m'intéressent (ici, tout mon tableau, c'est-à-dire toutes les lignes et toutes les colonnes), puis je clique sur Insertion -> TblCroiséDynamique.

Si vous n'avez jamais utilisé de tableaux croisés dynamiques auparavant, la fenêtre qui s'affiche maintenant peut paraitre assez déroutante :

Pas de panique ! Ce qu'il faut que vous fassiez c'est d'essayer d'imaginer le rapport que vous souhaitez, puis de faire glisser les champs se trouvant dans la liste à droite vers le bon emplacement dans le tableau ou dans les cases à droite.

Si vous avez du mal à deviner ce qui va où, faites des essais et voyez si le résultat est pertinent. Je vais reformuler le problème selon les termes du cas que je vous présente : Je veux savoir quelle province compte le plus grand nombre de licences commandées au total.

Je commence par faire glisser « Province » vers « Row Labels » (Étiquettes de lignes), de sorte à obtenir une liste de toutes les provinces, puis je fais glisser « QTY » (Quantité) vers « Values » (Valeurs). Par défaut, c'est le nombre de lignes qui est pris en compte. Je vais donc dans le menu Paramètres des champs de valeurs (après un clic droit sur le champ QTY) et je sélectionne Somme. J'ai maintenant devant moi une liste de toutes les provinces et le nombre total de licences commandées dans chacune d'elles.

C'est intéressant, mais il n'est pas facile de trouver quelle province est la plus demandeuse. Donc, ce que je vais faire c'est cliquer sur la petite flèche à côté de « Province » (en en-tête du tableau), et choisir « Options de tri supplémentaires ». En y regardant de plus près, je vois que je peux choisir « Descendant (de Z à A) » et sélectionner « Somme de QTY », ce qui va trier les provinces par ordre décroissant selon le nombre de licences commandées. En d'autres termes, la province ayant commandé le plus de licences se trouve en haut de la liste :

Sans surprise, c'est l'Ontario qui a généré le plus de commandes. C'est un résultat prévisible car il y a beaucoup d'associations, d'organisations caritatives et de bibliothèques en Ontario. Toutefois, cela signifie aussi que nous devons communiquer plus auprès des autres provinces au sujet de notre programme de dons. Nous pouvons ainsi utiliser ces données pour appuyer nos efforts de communication et veiller à ce que notre temps soit utilisé plus efficacement, là où cela est le plus nécessaire.

Question no 2 : durant quel mois recevons-nous le plus de commandes ?

Comme c'est plutôt amusant, procédons à un autre exemple. Cette fois, je veux afficher une tendance du nombre de commandes par mois, afin de voir durant quels mois nous en recevons le plus. Là encore, je vais sélectionner toutes mes données dans la feuille de calcul principale, mais cette fois je vais utiliser la toute nouvelle fonctionnalité d'Excel, le « Graphique croisé dynamique », en cliquant sur Insertion -> Graphique croisé dynamique (dans le menu déroulant TblCroiséDynamique).

Comme précédemment, je vais paramétrer le tableau de sorte à afficher les données comme je le souhaite. Mais étant donné que j'ai choisi l'option Graphique croisé dynamique, Excel va aussi construire à partir de mes données un diagramme explicitant la tendance.

Puisque je veux voir le temps en abscisse, je vais faire glisser « Year » (Année) et « Month » (Mois) vers la zone « Axis Field » (Champs Axe). Sachant que c'est le nombre de commandes qui m'intéresse, je fais glisser « QTY » vers « Values ». Je constate que ce champ est automatiquement réglé pour compter les valeurs au lieu d'en faire la somme, ce qui correspond à ce que je veux ici (je veux connaitre le nombre de commandes, et non pas le nombre de licences). J'obtiens alors un tableau avec les valeurs en question, ainsi qu'un graphique :

 

Le résultat est engageant, mais un histogramme (graphique par défaut) n'est pas vraiment la meilleure façon d'afficher une tendance au fil du temps. Je vais plutôt opter pour un graphique en courbe. Je vais également nettoyer un peu le graphique pour le rendre plus facile à lire. 

Je peux désormais voir clairement apparaitre certaines tendances dans mes données. Par exemple, on voit que des pics de commandes ont lieu en mars et en juin dans les 3 années présentées ici. Si je fais le lien avec mes propres connaissances sur le secteur et le programme de dons, je sais que le 31 mars correspond à la fin de l'année fiscale de nombreux associations, et que de nombreux programmes de nos donateurs réinitialisent leurs limites d'attribution à la fin juin. Maintenant que j'en sais plus grâce à ces données, je peux commencer à prendre des décisions comme augmenter le temps du personnel dédié au service clients durant ces mois-là.

Tout ceci ne constitue bien sûr qu'une analyse superficielle, et il existe bien d'autres manières de triturer ces données. Comme évoqué au début de ce billet, en étant capable de jongler avec vos données vous pourrez découvrir des tendances et les meilleures façons de représenter vos données. Cela vous aidera en fin de compte à être mieux informés et à mieux décrire votre organisation par le biais de données.

Avez-vous utilisé des tableaux croisés dynamiques ? Quels sont les méthodes/outils d'analyse de données que vous jugez utiles ?