Je suis parti de cette fonction:
/* ***********************************************************************
* Written 7 June 2012 by Mason Fabel
* Revised 8 June 2012 by David Lim
* Revised 19 June 2014 by David Lim for Google Spreadsheets V3 API
*
* V2 Description
* This function takes a url in the form:
* http://spreadsheets.google.com/feeds/cells/$KEY/1/public/values
* where $KEY is the key given to the published version of the
* spreadsheet.
*
* To publish a spreadsheet in Google Drive (2012), open the
* spreadsheet. Under 'file', select 'Publish to the web...'
* The key will be a part of the GET portion of the URL listed
* at the bottom of the dialog box (https://....?key=$KEY&...)
*
* This function returns a multidimensional array in the form:
* $array[$row][$col] = $content
* where $row is a number and $col is a letter.
*
* Limitations
* This only works for one sheet
************************************************************************ */
/* Get a google spreadsheet and return its contents as an array */
function google_spreadsheet_to_array($key) {
// initialize URL
$url = 'http://spreadsheets.google.com/feeds/cells/' . $key . '/1/public/values';
// initialize curl
$curl = curl_init();
// set curl options
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, TRUE);
// get the spreadsheet using curl
$google_sheet = curl_exec($curl);
// close the curl connection
curl_close($curl);
// import the xml file into a SimpleXML object
$feed = new SimpleXMLElement($google_sheet);
// get every entry (cell) from the xml object
// extract the column and row from the cell's title
// e.g. A1 becomes [1][A]
$array = array();
foreach ($feed->entry as $entry) {
$location = (string) $entry->title;
preg_match('/(?P<column>[A-Z]+)(?P<row>[0-9]+)/', $location, $matches);
$array[$matches['row']][$matches['column']] = (string) $entry->content;
}
// return the array
return $array;
}
/*
Get a google spreadsheet and return its contents as an array
For version 3.0 of the Google Spreadsheet API, this requires the spreadsheet worksheet
to be published as a web page. This function will parse through the generated HTML table
to extract spreadsheet contents.
This is because API v3 requires authentication and we don't want to put credentials in code.
*/
function google_spreadsheet_to_array_v3($url=NULL) {
// make sure we have a URL
if (is_null($url)) {
return array();
}
// initialize curl
$curl = curl_init();
// set curl options
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, TRUE);
// get the spreadsheet data using curl
$google_sheet = curl_exec($curl);
// close the curl connection
curl_close($curl);
// parse out just the html table
preg_match('/(<table[^>]+>)(.+)(<\/table>)/', $google_sheet, $matches);
$data = $matches['0'];
// Convert the HTML into array (by converting into HTML, then JSON, then PHP array
$cells_xml = new SimpleXMLElement($data);
$cells_json = json_encode($cells_xml);
$cells = json_decode($cells_json, TRUE);
// Create the array
$array = array();
foreach ($cells['tbody']['tr'] as $row_number=>$row_data) {
$column_name = 'A';
foreach ($row_data['td'] as $column_index=>$column) {
$array[($row_number+1)][$column_name++] = $column;
}
}
return $array;
}
$spreadsheet = google_spreadsheet_to_array("1q9lWVncwWnWfPjM2Xw4b6yhfK_6GF8o0zULd08DCxrM");
Vous pouvez jouer avec mes données si vous voulez tester. Ensuite, pour avoir accès au nombre de données, il ne suffit que de faire:
$n = count($spreadsheet);
Ensuite, c’est du PHP pour calculer les données à mettre dans les graphiques:
foreach($spreadsheet as $key => $value){
if(($value[E] > 0) AND ($value[G] > 0)){
if ($value[E] < 15000){
$a = str_replace(',', '.', $value[E]);
$a = str_replace('%', '', $a);
$b = str_replace(',', '.', $value[G]);
$b = str_replace('%', '', $b);
$open_rate .= "[".$a.", ".$b."], ";
}
}
}
$open_rate = trim($open_rate, ",");
$open_rate .="]";
$click_rate = "[";
foreach($spreadsheet as $key => $value){
if(($value[E] > 0) AND ($value[H] > 0)){
if ($value[E] < 15000){
$a = str_replace(',', '.', $value[E]);
$a = str_replace('%', '', $a);
$b = str_replace(',', '.', $value[H]);
$b = str_replace('%', '', $b);
$click_rate .= "[".$a.", ".$b."], ";
}
}
}
$click_rate = rtrim($click_rate, ",");
$click_rate .="]";
$list_size = "[";
foreach($spreadsheet as $key => $value){
if(($value[E] > 0) AND ($value[J] > 0)){
if (($value[E] < 15000) AND ($value[J] <= 200)){
$a = str_replace(',', '.', $value[E]);
$a = preg_replace("/[^0-9]/", "",$a);
$b = str_replace(',', '.', $value[J]);
$b = preg_replace("/[^0-9]/", "",$b);
$list_size .= "[".$b.", ".$a."], ";
}
}
}
$list_size = rtrim($list_size, ",");
$list_size .="]";
// This calculates the open and click rates per types of clients
$i = 0;
$ii = 0;
$particuliers = 0;
$entreprises = 0;
foreach($spreadsheet as $key => $value){
if(($value[G] > 0) AND ($value[K] == "Entreprises")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$entreprises += $a;
$i++;
}
if(($value[H] > 0) AND ($value[K] == "Particuliers")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$particuliers +=$a;
$ii++;
}
}
$taux_ouverture_type = "[".$entreprises/$i.", ".$particuliers/$ii."]";
$i = 0;
$ii = 0;
$entreprises = 0;
$particuliers = 0;
foreach($spreadsheet as $key => $value){
if(($value[H] > 0) AND ($value[K] == "Entreprises")){
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$entreprises += $a;
$i++;
}
if(($value[H] > 0) AND ($value[K] == "Particuliers")){
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$particuliers +=$a;
$ii++;
}
}
$taux_clics_type = "[".$entreprises/$i.", ".$particuliers/$ii."]";
## this calculates the open and click rate per types of business
$i = 0;
$ii = 0;
$tdc = 0;
$tdo = 0;
foreach($spreadsheet as $key => $value){
if(($value[G] > 0) AND ($value[I] == "Service")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$service += $a;
$i++;
}
if(($value[H] > 0) AND ($value[I] == "Produit")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$produit +=$a;
$ii++;
}
}
$taux_ouverture = "[".$produit/$ii.", ".$service/$i."]";
$i = 0;
$ii = 0;
$produit = 0;
$service = 0;
foreach($spreadsheet as $key => $value){
if(($value[G] > 0) AND ($value[I] == "Service")){
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$service += $a;
$i++;
}
if(($value[H] > 0) AND ($value[I] == "Produit")){
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$produit +=$a;
$ii++;
}
}
$taux_clics = "[".$produit/$ii.", ".$service/$i."]";
## this calculates the open and click rate per frequency
$i = 0;
$ii = 0;
$iii = 0;
$iiii = 0;
$iiiii = 0;
$iiiiii = 0;
foreach($spreadsheet as $key => $value){
if(($value[G] > 0) AND ($value[F] == "Environ 4 fois par année")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$tt +=$a;
$ii++;
}
if(($value[G] > 0) AND ($value[F] == "Une fois par moi")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$ttt +=$a;
$iii++;
}
if(($value[G] > 0) AND ($value[F] == "Une fois par deux semaine")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$tttt +=$a;
$iiii++;
}
if(($value[G] > 0) AND ($value[F] == "Une fois par semaine")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$ttttt +=$a;
$iiiii++;
}
if(($value[G] > 0) AND ($value[F] == "Plus d'une fois par semaine")){
$a = str_replace(',', '.', $value[G]);
$a = str_replace('%', '', $a);
$tttttt +=$a;
$iiiiii++;
}
}
$open_rate_by_frequency = "[".$tt/$ii.", ".$ttt/$iii.", ".$tttt/$iiii.", ".$ttttt/$iiiii.", ".$tttttt/$iiiiii."]";
$ii = 0;
$iii = 0;
$iiii = 0;
$iiiii = 0;
$iiiiii = 0;
$tt = 0;
$ttt = 0;
$tttt = 0;
$ttttt = 0;
$tttttt = 0;
$non1 = 0;
$non2 = 0;
$non3 = 0;
$non4 = 0;
$non5 = 0;
$oui1 = 0;
$oui2 = 0;
$oui3 = 0;
$oui4 = 0;
$oui5 = 0;
foreach($spreadsheet as $key => $value){
if(($value[H] > 0) AND ($value[F] == "Environ 4 fois par année")){
if ($value[D]=="Oui"){
$oui1++;
}
if ($value[D]=="Non"){
$non1++;
}
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$tt +=$a;
$ii++;
}
if(($value[H] > 0) AND ($value[F] == "Une fois par moi")){
if ($value[D]=="Oui"){
$oui2++;
}
if ($value[D]=="Non"){
$non2++;
}
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$ttt +=$a;
$iii++;
}
if(($value[H] > 0) AND ($value[F] == "Une fois par deux semaine")){
if ($value[D]=="Oui"){
$oui3++;
}
if ($value[D]=="Non"){
$non3++;
}
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$tttt +=$a;
$iiii++;
}
if(($value[H] > 0) AND ($value[F] == "Une fois par semaine")){
if ($value[D]=="Oui"){
$oui4++;
}
if ($value[D]=="Non"){
$non4++;
}
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$ttttt +=$a;
$iiiii++;
}
if(($value[H] > 0) AND ($value[F] == "Plus d'une fois par semaine")){
if ($value[D]=="Oui"){
$oui5++;
}
if ($value[D]=="Non"){
$non5++;
}
$a = str_replace(',', '.', $value[H]);
$a = str_replace('%', '', $a);
$tttttt +=$a;
$iiiiii++;
}
}
$s1 = $oui1/($oui1+$non1)*100;
$s2 = $oui2/($oui2+$non2)*100;
$s3 = $oui3/($oui3+$non3)*100;
$s4 = $oui4/($oui4+$non4)*100;
$s5 = $oui5/($oui5+$non5)*100;
$click_rate_by_frequency = "[".$tt/$ii.", ".$ttt/$iii.", ".$tttt/$iiii.", ".$ttttt/$iiiii.", ".$tttttt/$iiiiii."]";
$satisfaction_by_frequency = "[".$s1.", ".$s2.", ".$s3.", ".$s4.", ".$s5."]";
## Satisfaction en fonctiond de l'industrie
foreach($spreadsheet as $key => $value){
if($value[A] == "Oui"){
if ($value[D]=="Oui"){ #Pour compter ceux qui sont satisfaits
if ($value[I]=="Produit"){
if ($value[K]=="Particuliers"){ #Produit B2C
$produit_B2C_oui++;
}
if ($value[K]=="Entreprises"){ #Produit B2B
$produit_B2B_oui++;
}
}
if ($value[I]=="Service"){
if ($value[K]=="Particuliers"){ #service B2C
$service_B2C_oui++;
}
if ($value[K]=="Entreprises"){ #service B2B
$service_B2B_oui++;
}
}
}
if ($value[D]=="Non"){ #Pour compter ceux qui sont satisfaits
if ($value[I]=="Produit"){
if ($value[K]=="Particuliers"){ #Produit B2C
$produit_B2C_non++;
}
if ($value[K]=="Entreprises"){ #Produit B2B
$produit_B2B_non++;
}
}
if ($value[I]=="Service"){
if ($value[K]=="Particuliers"){ #service B2C
$service_B2C_non++;
}
if ($value[K]=="Entreprises"){ #service B2B
$service_B2B_non++;
}
}
}
}
if($value[A] == "Non"){ #pour compter ceux qui n'ont pas de liste
if ($value[I]=="Produit"){
if ($value[K]=="Particuliers"){ #Produit B2C
$produit_B2C_nolist++;
}
if ($value[K]=="Entreprises"){ #Produit B2B
$produit_B2B_nolist++;
}
}
if ($value[I]=="Service"){
if ($value[K]=="Particuliers"){ #service B2C
$service_B2C_nolist++;
}
if ($value[K]=="Entreprises"){ #service B2B
$service_B2B_nolist++;
}
}
}
}
$s1 = $produit_B2B_oui/($produit_B2B_non+$produit_B2B_oui)*100;
$s2 = $produit_B2C_oui/($produit_B2C_non+$produit_B2C_oui)*100;
$s3 = $service_B2B_oui/($service_B2B_non+$service_B2B_oui)*100;
$s4 = $service_B2C_oui/($service_B2C_non+$service_B2C_oui)*100;
$satisfaction_by_industry = "[".$s1.", ".$s2.", ".$s3.", ".$s4."]";
$s1 = ($produit_B2B_non+$produit_B2B_oui)/($produit_B2B_non+$produit_B2B_oui+$produit_B2B_nolist)*100;
$s2 = ($produit_B2C_non+$produit_B2C_oui)/($produit_B2C_non+$produit_B2C_oui+$produit_B2C_nolist)*100;
$s3 = ($service_B2B_non+$service_B2B_oui)/($service_B2B_non+$service_B2B_oui+$service_B2B_nolist)*100;
$s4 = ($service_B2C_non+$service_B2C_oui)/($service_B2C_non+$service_B2C_oui+$service_B2C_nolist)*100;
$list_by_industry = "[".$s1.", ".$s2.", ".$s3.", ".$s4."]";
## Question 1
$oui = 0;
$non = 0;
foreach($spreadsheet as $key => $value){
if ($value[A]=="Oui"){
$oui++;
} elseif($value[A]=="Non"){
$non++;
}
}
$total =0;
$total = $oui+$non;
$oui = $oui*$total/100;
$non = $non*$total/100;
## Question 2
$oui2 = 0;
$non2 = 0;
foreach($spreadsheet as $key => $value){
if ($value[B]=="Oui"){
$oui2++;
} elseif($value[B]=="Non"){
$non2++;
}
}
$total =0;
$total = $oui2+$non2;
$oui2 = $oui2*$total/100;
$non2 = $non2*$total/100;
## Question 3
$oui3 = 0;
$non3 = 0;
foreach($spreadsheet as $key => $value){
if ($value[C]=="Oui"){
$oui3++;
} elseif($value[C]=="Non"){
$non3++;
}
}
$total =0;
$total = $oui3+$non3;
$oui3 = $oui3*$total/100;
$non3 = $non3*$total/100;
## Question 4
$oui4 = 0;
$non4 = 0;
foreach($spreadsheet as $key => $value){
if ($value[D]=="Oui"){
$oui4++;
} elseif($value[D]=="Non"){
$non4++;
}
}
$total =0;
$total = $oui4+$non4;
$oui4 = $oui4*$total/100;
$non4 = $non4*$total/100;
$q_taille_efficacité = array();
foreach($spreadsheet as $key => $value){
if (($value[D]!="")&&($value[E]!="")){
if($value[D]=="Oui"){
$q_taille_efficacité[efficace_oui]++;
$q_taille_efficacité[efficace_oui_taille]+=$value[E];
}
if($value[D]=="Non"){
$q_taille_efficacité[efficace_non]++;
$q_taille_efficacité[efficace_non_taille]+=$value[E];
}
$q_taille_efficacité[n]++;
}
} ## end for each
#echo $q_taille_efficacité[efficace_oui_taille]/$q_taille_efficacité[efficace_oui];
#echo "<br>";
#echo $q_taille_efficacité[efficace_non_taille]/$q_taille_efficacité[efficace_non];
function roundUpToAny($n,$x=500) {
return round(($n+$x/2)/$x)*$x;
}
$array_oui=array();
$array_non=array();
foreach($spreadsheet as $key => $value){
if (($value[D]!="")&&($value[E]!="")){
#$x = round ($value[E], -3);
$x = roundUpToAny($value[E]);
if($value[D]=="Oui"){
$array_oui[$x][oui]++;
$array_oui[$x][total]++;
}
if($value[D]=="Non"){
$array_oui[$x][non]++;
$array_oui[$x][total]++;
}
}
} ## end for each
ksort($array_oui);
#print_r($array_oui);
$satisfaction_taille = "[";
foreach($array_oui as $key => $value){
$percentage = 0;
$percentage = ($value[oui]/$value[total])*100;
$percentage = round($percentage, 2);
if((($percentage<100)&&($percentage>0))&&($key<5501)){
$satisfaction_taille .= "[".$key.", ".$percentage."], ";
}
}
$satisfaction_taille = rtrim($satisfaction_taille, ",");
$satisfaction_taille .="]";
Finalement, les graphiques sont générés en utilisant Highcharts:
https://www.highcharts.com/
Ça ressemble à ça:
$(function () {
$('#tdc_taille').highcharts({
chart: {
type: 'scatter',
zoomType: 'xy'
},
title: {
text: 'Le taux d\'ouverture et de clic selon la taille de la liste'
},
subtitle: {
text: 'Source: <a href="https://www.olivierlambert.ca/page/email-marketing-study-results.php">olivierlambert.ca</a>'
},
xAxis: {
title: {
enabled: true,
text: 'Nombre d\'abonnés'
},
startOnTick: true,
endOnTick: true,
showLastLabel: true
},
yAxis: {
title: {
text: '% Pourcentage'
}
},
legend: {
layout: 'vertical',
align: 'left',
verticalAlign: 'top',
x: 100,
y: 70,
floating: true,
backgroundColor: (Highcharts.theme && Highcharts.theme.legendBackgroundColor) || '#FFFFFF',
borderWidth: 1
},
plotOptions: {
scatter: {
marker: {
radius: 5,
states: {
hover: {
enabled: true,
lineColor: 'rgb(100,100,100)'
}
}
},
states: {
hover: {
marker: {
enabled: false
}
}
},
tooltip: {
headerFormat: '<b>{series.name}</b><br>',
pointFormat: '{point.x} abonnés, {point.y} %'
}
}
},
series: [{
name: 'Taux d\'ouverture',
color: 'rgba(223, 83, 83, .5)',
data: <?=$open_rate?>
}, {
name: 'Taux de clic',
color: 'rgba(119, 152, 191, .5)',
data: <?=$click_rate?>
}]
});
});
Puisque c’est du JavaScript, n’importe qui peut accéder au code source alors c’est moins pertinent que je publie le tout ici.