How do I get data from the Analytics module in the logic?

Question

I have a Datamart named "SalesHistoryDM" with the following columns:

Sku
CustomerId
YearMonth (Time Dimension)
Revenue
UnitsSold
MarginalContributionAbs
MarginalContributionPerc

I need to aggregate the Datamart in the logic for a specific product. I need to sum Revenue, UnitsSold, MarginalContribution and count how many customers bought the product per year.

Answer

You need to run a Datamart query:

def filters = [
Filter.equal("Sku", sku),
]

def dmCtx = api.getDatamartContext()
def salesDM = dmCtx.getTable("SalesHistoryDM")
def datamartQuery = dmCtx.newQuery(salesDM)

datamartQuery.select("SUM(Revenue)", "revenue")
datamartQuery.select("SUM(UnitsSold)", "unitsSold")
datamartQuery.select("SUM(MarginalContributionAbs)", "marginAbs")
datamartQuery.select("COUNTDISTINCT(CustomerId)", "customersCount")
datamartQuery.select("YearMonthYear", "year")
datamartQuery.where(filters)

def result = dmCtx.executeQuery(datamartQuery)?.getData()?.collect() ?: []

In the result you will get a list of maps:

[
[ revenue: 123.0, unitsSold: 2, marginAbs:  56.78, customersCount: 2, year: 2017],
[ revenue: 456.0, unitsSold: 4, marginAbs: 123.45, customersCount: 3, year: 2018]
]