Calcul d’une moyenne pondérée (moyenne des moyennes)

Un problème de reporting courant dans Excel est la fusion d'ensembles de données, comme la combinaison de chiffres mensuels pour un total trimestriel ou annuel. L'ajout de mesures simples est évident, mais que faites-vous avec des choses comme la position moyenne ou le taux de conversion? [ Astuce: vous ne devez pas faire la moyenne ou le total de tout ce qui est déjà une moyenne ou un ratio ]

207 001 impressions avec une position moyenne de 35,7 + 
735 462 impressions avec une position moyenne de 55,1 = 
942 463 impressions avec une position moyenne de ?

L'attrait de la moyenne pondérée

Les taux et les moyennes sont des métriques calculées, et elles sont toutes basées sur un échantillon ou une «population» de données. Dans l'exemple ci-dessus, une position moyenne de 55,1 (surlignée en jaune) est basée sur un groupe spécifique de 735 462 impressions. Le calcul réel derrière cette position moyenne consiste à additionner toutes les valeurs de position individuelles et à les diviser par le nombre total d'impressions:

position moyenne = (somme des positions) / (nombre d'impressions)

Lors de la combinaison de mesures calculées comme celles-ci, la taille de chaque groupe est importante - la combinaison d'un petit échantillon en un plus grand échantillon ne devrait pas affecter beaucoup la moyenne globale (elle devrait être plus proche de 55,1 que de 35,7 dans l'exemple ci-dessus). Ce que nous voulons, c'est une moyenne «pondérée», où la taille de l'échantillon est prise en compte lorsque les chiffres sont combinés. La recherche d'une formule typique pour une moyenne pondérée donne quelque chose comme ceci - vous multipliez chaque moyenne par un facteur de pondération qui est calculé à partir de la proportion des impressions qu'ils représentent, puis ajoutez-les ensemble:

moyenne pondérée = position moyenne1 * impressions1 / (impressions1 + impressions2) 
+ avg position2 * impressions2/(impressions1+impressions2)

Le décrire semble compliqué et cette formule est difficile à mettre en œuvre , en particulier avec de grands ensembles de données. Cela nécessiterait de rechercher des lignes correspondantes, puis de calculer la proportion ou le «poids», puis de l'appliquer au numéro de position moyen, et enfin de les ajouter ensemble. C'est beaucoup de recherches et de calculs, et cela pourrait être très lent.

Simplifier le problème et la solution

Dans des situations comme celle-ci, nous devons reformuler le problème d'une manière plus facile (plus rapide) à résoudre. Pour revenir à la formule simple de la moyenne, nous pouvons supposer que si la position moyenne est la somme des positions divisée par le nombre d'impressions, la position moyenne des données combinées doit être la somme de toutes les positions , divisée par le nombre de toutes les impressions :

position moyenne = (somme des positions1 + somme des positions2) / (impressions1 + impressions2)

Cela semble beaucoup plus simple que la formule ci-dessus, et si vous êtes bon dans les substitutions de formules mathématiques, vous pouvez réellement montrer que cela se traduira exactement par le même nombre. La différence est qu'elle implique un nombre que nous n'avons pas dans nos données: la «somme des positions». Mais c'est assez facile à calculer; puisque la position moyenne est la (somme des positions) / impressions, alors nous savons:

somme des positions = position moyenne * impressions

Croyez-le ou non, nous avons maintenant un moyen très simple de combiner notre moyenne avec quelques étapes simples dans une feuille de calcul:

• ajouter un ensemble de données à l'autre
• ajouter une colonne de calcul (position moyenne * impressions) appelée «somme des positions»
• combiner des lignes en double, en additionnant les nombres dans des lignes correspondantes
• calculer une nouvelle position moyenne = (somme des positions) / impressions

Une approche générique

Si vous appliquez la même pensée au taux de clics (ctr) dans un rapport de recherche Google standard, vous vous rendrez compte que vous avez déjà les clics et les impressions (ce sont des mesures par défaut) qui sont nécessaires pour calculer le ctr (ctr = clics / impressions). Tout ce que vous avez à faire est d'ajouter les données, de combiner les lignes en double, puis de calculer la nouvelle colonne ctr à partir des clics / impressions.

L'étape «supplémentaire» pour la position moyenne dans le premier exemple existe parce que nous n'avons pas commencé avec l'une des mesures «de base» derrière la mesure calculée - la (somme des positions). Pour revenir à la liste pas à pas, nous pouvons la rendre plus générique:

• ajouter un ensemble de données à l'autre
• si vous devez combiner des métriques calculées (moyenne, taux, pourcentage), décomposez-les d'abord en métriques de base *
• combiner des lignes en double, en additionnant les nombres dans chaque colonne
• recalculer les métriques calculées à partir des métriques de base (sommées)

Remarque: si vous le pouvez, incluez les métriques de base dans votre requête pour éliminer le besoin de la deuxième étape. par exemple, inclure les rebonds et les sessions dans votre requête au lieu du taux de rebond.

Mesures de base pour les taux et les moyennes communs

Votre premier défi consiste à déterminer quelles sont les mesures de base pour tout taux ou mesure moyenne que vous utilisez. Vous pouvez ensuite inclure ces mesures dans votre requête ou les calculer à partir des données dont vous disposez. Voici quelques-uns des plus courants pour la recherche Google et Google Analytics:

ctr = clics / impressions
[moy.] position = (somme des positions) / impressions

taux de rebond = rebonds / sessions
% nouvelles sessions = nouveaux utilisateurs / sessions
durée moyenne de la session = durée de la session / sessions
taux de conversion de l'objectif = objectifs terminés / sessions
valeur de la commande moyenne = revenus / sessions
taux de conversion du commerce électronique = transactions / sessions

Si vous devez fusionner deux ou plusieurs ensembles de nombres et qu'ils incluent des mesures calculées telles que des moyennes, des taux ou des pourcentages, assurez-vous de les combiner de la bonne façon. Ne vous laissez pas rebuter par des formules compliquées pour les moyennes pondérées - le problème et la solution sont en fait assez simples.

Commencez par (ou calculez) les mesures de base, combinez (additionnez) les lignes en double et recalculez les mesures calculées. Opérations simples pour des rapports précis.