| public static getSelectsFromRangedColumn ( string $column, array $ranges, string $table, string $selectColumnPrefix, boolean $restrictToReturningVisitors = false ) : array | ||
| $column | string | The name of a column in `$table` that will be summarized. |
| $ranges | array | The array of ranges over which the data in the table will be summarized. For example, ``` array( array(1, 1), array(2, 2), array(3, 8), array(8) // everything over 8 ) ``` |
| $table | string | The unprefixed name of the table whose rows will be summarized. |
| $selectColumnPrefix | string | The prefix to prepend to each SELECT expression. This prefix is used to differentiate different sets of range summarization SELECTs. You can supply different values to this argument to summarize several columns in one query (see above for an example). |
| $restrictToReturningVisitors | boolean | Whether to only summarize rows that belong to visits of returning visitors or not. If this argument is true, then the SELECT expressions returned can only be used with the {@link queryVisitsByDimension()} method. |
| return | array | An array of SQL SELECT expressions, for example, ``` array( 'sum(case when log_visit.visit_total_actions between 0 and 2 then 1 else 0 end) as vta0', 'sum(case when log_visit.visit_total_actions > 2 then 1 else 0 end) as vta1' ) ``` |
public static function getSelectsFromRangedColumn($column, $ranges, $table, $selectColumnPrefix, $restrictToReturningVisitors = false)
{
$selects = array();
$extraCondition = '';
if ($restrictToReturningVisitors) {
// extra condition for the SQL SELECT that makes sure only returning visits are counted
// when creating the 'days since last visit' report
$extraCondition = 'and log_visit.visitor_returning = 1';
$extraSelect = "sum(case when log_visit.visitor_returning = 0 then 1 else 0 end) " . " as `" . $selectColumnPrefix . 'General_NewVisits' . "`";
$selects[] = $extraSelect;
}
foreach ($ranges as $gap) {
if (count($gap) == 2) {
$lowerBound = $gap[0];
$upperBound = $gap[1];
$selectAs = "{$selectColumnPrefix}{$lowerBound}-{$upperBound}";
$selects[] = "sum(case when {$table}.{$column} between {$lowerBound} and {$upperBound} {$extraCondition}" . " then 1 else 0 end) as `{$selectAs}`";
} else {
$lowerBound = $gap[0];
$selectAs = $selectColumnPrefix . ($lowerBound + 1) . urlencode('+');
$selects[] = "sum(case when {$table}.{$column} > {$lowerBound} {$extraCondition} then 1 else 0 end) as `{$selectAs}`";
}
}
return $selects;
} protected function aggregateGeneralGoalMetrics()
{
$prefixes = array(self::VISITS_UNTIL_RECORD_NAME => 'vcv', self::DAYS_UNTIL_CONV_RECORD_NAME => 'vdsf');
$selects = array();
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::VISITS_COUNT_FIELD, self::$visitCountRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::VISITS_UNTIL_RECORD_NAME]));
$selects = array_merge($selects, LogAggregator::getSelectsFromRangedColumn(self::DAYS_SINCE_FIRST_VISIT_FIELD, self::$daysToConvRanges, self::LOG_CONVERSION_TABLE, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]));
$query = $this->getLogAggregator()->queryConversionsByDimension(array(), false, $selects);
if ($query === false) {
return;
}
$totalConversions = $totalRevenue = 0;
$goals = new DataArray();
$visitsToConversions = $daysToConversions = array();
$conversionMetrics = $this->getLogAggregator()->getConversionsMetricFields();
while ($row = $query->fetch()) {
$idGoal = $row['idgoal'];
unset($row['idgoal']);
unset($row['label']);
$values = array();
foreach ($conversionMetrics as $field => $statement) {
$values[$field] = $row[$field];
}
$goals->sumMetrics($idGoal, $values);
if (empty($visitsToConversions[$idGoal])) {
$visitsToConversions[$idGoal] = new DataTable();
}
$array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::VISITS_UNTIL_RECORD_NAME]);
$visitsToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
if (empty($daysToConversions[$idGoal])) {
$daysToConversions[$idGoal] = new DataTable();
}
$array = LogAggregator::makeArrayOneColumn($row, Metrics::INDEX_NB_CONVERSIONS, $prefixes[self::DAYS_UNTIL_CONV_RECORD_NAME]);
$daysToConversions[$idGoal]->addDataTable(DataTable::makeFromIndexedArray($array));
// We don't want to sum Abandoned cart metrics in the overall revenue/conversions/converted visits
// since it is a "negative conversion"
if ($idGoal != GoalManager::IDGOAL_CART) {
$totalConversions += $row[Metrics::INDEX_GOAL_NB_CONVERSIONS];
$totalRevenue += $row[Metrics::INDEX_GOAL_REVENUE];
}
}
// Stats by goal, for all visitors
$numericRecords = $this->getConversionsNumericMetrics($goals);
$this->getProcessor()->insertNumericRecords($numericRecords);
$this->insertReports(self::VISITS_UNTIL_RECORD_NAME, $visitsToConversions);
$this->insertReports(self::DAYS_UNTIL_CONV_RECORD_NAME, $daysToConversions);
// Stats for all goals
$nbConvertedVisits = $this->getProcessor()->getNumberOfVisitsConverted();
$metrics = array(self::getRecordName('conversion_rate') => $this->getConversionRate($nbConvertedVisits), self::getRecordName('nb_conversions') => $totalConversions, self::getRecordName('nb_visits_converted') => $nbConvertedVisits, self::getRecordName('revenue') => $totalRevenue);
$this->getProcessor()->insertNumericRecords($metrics);
}