Adobe Campaign - Aggregate Column Values (SQL in Campaign)

Navigate:

Campaign is fantastic at creating big tables of specific records, personalised on a row by row basis. For example, you’ve done a query on everyone who’s bought something in the last week - you’ve got their email, their mobile, and an enrichment of the amount each person has spent online in the last 7 days .

But what do you do when you want to get a single sum of how much everyone spent? Marketing also want these values weekly, so you’re absolutely not going to be running it by hand.

Easy to pull this one off with a combo of SQL and Javascript!

Within a standard javascript activity, you’d enter in:


var sql = "SELECT sum(" + vars.tableName + ".dcolumnName) FROM " + vars.tableName;
var number = sqlGetDouble(sql);
var corrected = number.toFixed(2);
instance.vars.totalColumnName = corrected;

Line 1: Create a local javascript variable called sql which includes a string of the sql function you want to execute.

The vars.tableName part is to dynamically insert the name of the temporary target table as it changes every time!

The name of the column is the temporary table name, full stop, a character that matches the data type of the field and then the name you gave the column.

I called mine columnName in the enrichment, which stores a double value, so it’d be VARIABLE.dcolumnName.

The other data type characters are:

Data Type	Character
Integer (Whole Number - 4)	i
Double (Number with decimal places - 4.01)	d
String (The word Four)	s

You can also see I’ve run an aggregate of sum() on the column I selected. Info of more aggregates are available here.

Line 2: Create a local variable called number, which runs sqlGetDouble() - which runs a sql statement and returns a double type value (number including decimal places). There’s a list of all the different kind of functions you can run in the Campaign API documentation here.

Line 3: Because the getDouble returns a very LONG floating point number with like 7-8 decimal places, we trim it down to 2 decimal places using .toFixed(). The number you put in the brackets defines how long the result is.

Line 4: Sets a workflow level variable with the output of line 3, which is now a nice neat number with two decimal places!

I’ll add in a new page soon which outlines how to use the workflow variable in an email!