Calculations & Data Validation

0010

0010 — Calculating Basic Metrics

Level 0 · Target: 000001 Enrich Opportunity data Historical opportunities and Pipeline

clears target Update Reporting Layer

6 ops

▾

# Code Description Type Source Target / Method Mappings

0

001

Reading from Virtual Dataset

Insert from other dataset

000015

Opportunity, basic calcs

—

1

0010

Adding Customer Attributes

Enrichment with reference dataset

ref 000014

Customers Attributes

—

2

0020

Mapping Product Items

Enrichment with reference dataset

ref 000013

Product Item / Product / Portfolio mapping

—

3 0030 Deriving budget year and month from close date Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`BDG_PER`	—	to_char(CLOSE_DATE,'MM')	—	—
`BDG_SCE`	—	to_char(CLOSE_DATE,'YYYY')\|\|\|\|'BDG'	—	—

4 0040 Calculating Weighted Value Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`WEIGHTED_VALUE`	—	Value / (case prospect_size when '04' then 140000 when '01' then 100000 when '03' then 65000 when '02' then 40000 end)	—	—

6 TOTO TOTO Enrichment with formula — — —

0020

0020 — Sales Exec forecast to Financial Workspace

Level 1 · Target: 000002 Data for Financial Workspace Data for financial workspace

clears target

2 ops

▾

# Code Description Type Source Target / Method Mappings

0

010

Copying Sales Exec Values loaded from Salesforce

Insert from other dataset

000001 Enrich Opportunity data

Historical opportunities and Pipeline

—

Source filter · WINLOS is null

Target field	Source field	Group by
`SCENARIO`	`BDG_SCE`	Group by
`PERIOD`	`BDG_PER`	Group by
`ENTITY`	`ENTITY`	Group by
`CATEGORY`	`FCT_VERSION`	Group by
`PRODUCTS`	`PRODUCT`	Group by
`CURRENCY`	`CURRENCY`	Group by
`ACCOUNT`	`'ST_900'`	—
`AMOUNT`	`UNITS`	Sum

1

020

Copying Units loaded from Salesforce

Insert from other dataset

000001 Enrich Opportunity data

Historical opportunities and Pipeline

—

Source filter · WINLOS is null

Target field	Source field	Group by
`AMOUNT`	`VALUE`	Sum
`PRODUCTS`	`PRODUCT`	Group by
`ACCOUNT`	`'ST_901'`	—
`CATEGORY`	`FCT_VERSION`	Group by
`CURRENCY`	`CURRENCY`	Group by
`ENTITY`	`ENTITY`	Group by
`PERIOD`	`BDG_PER`	Group by
`SCENARIO`	`BDG_SCE`	Group by

010

010 — Enriching Pipeline Data with Web Activity Metrics

Level 1 · Target: 000001 Enrich Opportunity data Historical opportunities and Pipeline

clears target Update Reporting Layer

1 op

▾

# Code Description Type Source Target / Method Mappings

0

000001

Calculate Activity Metrics

Insert from other dataset

000006

Enrich w/ Web Traffic

—

Target field	Source field	Group by
`RECENT_ACTIVITIES`	`RECENT_ACTIVITIES`	Sum
`LSTMNT_ACTIVITIES`	`LSTMNT_ACTIVITIES`	Sum
`LSTQRT_ACTIVITIES`	`LSTQRT_ACTIVITIES`	Sum
`LSTYR_ACTIVITIES`	`LSTYR_ACTIVITIES`	Sum
`TOT_ACTIVITIES`	`TOT_ACTIVITIES`	Sum
`N`	—	Group by
`LSTMNT_AVG`	`LSTMNT_AVG`	A
`LSTQRT_AVG`	`LSTQRT_AVG`	A
`LSTYR_AVG`	`LSTYR_AVG`	A
`RECENT_AVG`	`RECENT_AVG`	A

011

011 — Enriching Marketing Campaigns with Channel Type

Level 2 · Target: 000011 Enriching with Channel Type Past Mark. Campaigns

clears target Update Reporting Layer

3 ops

▾

# Code Description Type Source Target / Method Mappings

0

001

Reading Marketing Campaigns

Insert from other dataset

000011

Past Mark. Campaigns

—

1

002

Enriching Marketing Campaigns with Channel Type

Enrichment with reference dataset

ref 000012

Channel Type Mapping

—

2 003 Assigning Channel Type to Digital when null Enrichment with formula — CH_TYPE is null

Target filter · CH_TYPE is null

Target field	WHEN condition	THEN	ELSE	Grp
`CH_TYPE`	—	'02'	—	—

012

012 — Enriching Pipeline Data with Marketing Metrics

Level 3 · Target: 000001 Enrich Opportunity data Historical opportunities and Pipeline

clears target Update Reporting Layer

11 ops

▾

# Code Description Type Source Target / Method Mappings

0

0010

Retriving Opprtunity data

Insert from other dataset

000001

Historical opportunities and Pipeline

—

1

0020

Enriching with Past Direct Mailing Campaigns

Enrichment from other dataset

000005

Enrich w/ Past Marketing Camp.

—

Source filter · CH_TYPE = '01'

Target field	Source field	Group by
`SCENARIO`	`SCENARIO`	Group by
`ID`	`ID`	Group by
`ITEM`	`ITEM`	Group by
`MAIL_CAMPAIGN`	`COST`	Sum

2

0025

Enriching with Planned Direct Mailing Campaigns

Enrichment from other dataset

000007

Enrich w/ Planned Marketing Camp.

—

Source filter · CAMPAIGN_TYPE = '01'

Target field	Source field	Group by
`PLAN_MAIL_CAMPAIGN`	`COST`	Sum
`SCENARIO`	`SCENARIO`	Group by
`ENTITY`	`ENTITY`	Group by
`ID`	`ID`	Group by
`PRODUCT`	`PRODUCT`	Group by

3

0030

Enriching with Past Digital Campaigns

Enrichment from other dataset

000005

Enrich w/ Past Marketing Camp.

—

Source filter · CH_TYPE = '02'

Target field	Source field	Group by
`ID`	`ID`	Group by
`SCENARIO`	`SCENARIO`	Group by
`ITEM`	`ITEM`	Group by
`DIG_CAMPAIGN`	`COST`	Sum

4

0035

Enriching with Planned Digital Campaigns

Enrichment from other dataset

000007

Enrich w/ Planned Marketing Camp.

—

Source filter · CAMPAIGN_TYPE = '02'

Target field	Source field	Group by
`PLAN_DIG_CAMPAIGN`	`COST`	Sum
`ENTITY`	`ENTITY`	Group by
`ID`	`ID`	Group by
`SCENARIO`	`SCENARIO`	Group by
`PRODUCT`	`PRODUCT`	Group by

5

0040

Enriching with Past Content Campaigns

Enrichment from other dataset

000005

Enrich w/ Past Marketing Camp.

—

Source filter · CH_TYPE = '03'

Target field	Source field	Group by
`ID`	`ID`	Group by
`ITEM`	`ITEM`	Group by
`SCENARIO`	`SCENARIO`	Group by
`CONT_CAMPAIGN`	`COST`	Sum

6

0045

Enriching with Planned Content Campaigns

Enrichment from other dataset

000007

Enrich w/ Planned Marketing Camp.

—

Source filter · CAMPAIGN_TYPE = '03'

Target field	Source field	Group by
`PLAN_CONT_CAMPAIGN`	`COST`	Sum
`SCENARIO`	`SCENARIO`	Group by
`ID`	`ID`	Group by
`ENTITY`	`ENTITY`	Group by
`PRODUCT`	`PRODUCT`	Group by

7

0050

Enriching with Past Events Campaigns

Enrichment from other dataset

000005

Enrich w/ Past Marketing Camp.

—

Source filter · CH_TYPE = '04'

Target field	Source field	Group by
`ID`	`ID`	Group by
`ITEM`	`ITEM`	Group by
`SCENARIO`	`SCENARIO`	Group by
`EVENTS_CAMPAIGN`	`COST`	Sum

8

0055

Enriching with Planned Events Campaigns

Enrichment from other dataset

000007

Enrich w/ Planned Marketing Camp.

—

Source filter · CAMPAIGN_TYPE = '04'

Target field	Source field	Group by
`ID`	`ID`	Group by
`SCENARIO`	`SCENARIO`	Group by
`ENTITY`	`ENTITY`	Group by
`PRODUCT`	`PRODUCT`	Group by
`PLAN_EVENTS_CAMPAIGN`	`COST`	Sum

9

0065

Customer Purcheses of the last year

Enrichment from other dataset

000001 Enrich Opportunity data

Historical opportunities and Pipeline

—

Source filter · WINLOS = 'W' and cast(extract('day' from @COUNT_OFF@ - WINLOS_DATE) as integer)<=365

Target field	Source field	Group by
`PROSPECT`	`PROSPECT`	Group by
`SCENARIO`	`SCENARIO`	Group by
`LY_PURCHASE_NR`	`UNITS`	Sum
`LY_PURCHASE_VALUE`	`VALUE`	Sum

10

0060

Adding custom attributes (tbd)

Enrichment with reference dataset

ref 000014

Customers Attributes

—

015

015 — Validate Pipeline Data

Level 4 · Target: 000001 Enrich Opportunity data Historical opportunities and Pipeline

Update Reporting Layer

3 ops

▾

#	Code	Description	Type	Source	Target / Method	Mappings
0	`000001`	Check Close Date is not Empty	Check with formula	—	—	—
1	`000002`	Check Opp.ty Aging higher than 360 days	Check with formula	—	—	—
2	`000003`	Checks on admitted stage	Dictionary check	—	—	—
Message · Some opportunity is in an inconsistent stage

020

020 — Predictive Forecast, calculating Discrete values from continuous values

Level 5 · Target: 000001 Enrich Opportunity data Historical opportunities and Pipeline

1 op

▾

# Code Description Type Source Target / Method Mappings

0 01 Calculating Discrete values from continuous values Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`LSTYR_AVG_CAT`	—	CASE WHEN(LSTYR_AVG<=14.729)THEN 11 WHEN(LSTYR_AVG<=14.73 AND LSTYR_AVG>14.729)THEN 10 WHEN(LSTYR_AVG<=14.761 AND LSTYR_AVG>14.73)THEN 9 WHEN(LSTYR_AVG<=14.765 AND LSTYR_AVG>14.761)THEN 8 WHEN(LSTYR_AVG<=14.768 AND LSTYR_AVG>14.765)THEN 7 WHEN(LSTYR_AVG<=14.778 AND LSTYR_AVG>14.768)THEN 6 WHEN(LSTYR_AVG<=14.796 AND LSTYR_AVG>14.778)THEN 5 WHEN(LSTYR_AVG<=14.816 AND LSTYR_AVG>14.796)THEN 4 WHEN(LSTYR_AVG<=14.826 AND LSTYR_AVG>14.816)THEN 3 WHEN(LSTYR_AVG<=14.893 AND LSTYR_AVG>14.826)THEN 2 ELSE 1 END	—	—
`LY_PURCHASE_NR_CAT`	—	CASE WHEN(LY_PURCHASE_NR<=78672)THEN 2 ELSE 1 END	—	—
`AGING_CAT`	—	CASE WHEN(AGING<=486)THEN 2 ELSE 1 END	—	—
`INACTIVITY_CAT`	—	CASE WHEN(INACTIVITY<=419)THEN 2 ELSE 1 END	—	—
`LSTMNT_ACTIVITIES_CAT`	—	CASE WHEN(LSTMNT_ACTIVITIES<=34)THEN 2 ELSE 1 END	—	—
`LSTMNT_AVG_CAT`	—	CASE WHEN(LSTMNT_AVG<=0)THEN 8 WHEN(LSTMNT_AVG<=2.851142857 AND LSTMNT_AVG>0)THEN 7 WHEN(LSTMNT_AVG<=2.8796 AND LSTMNT_AVG>2.851142857)THEN 6 WHEN(LSTMNT_AVG<=2.938857143 AND LSTMNT_AVG>2.8796)THEN 5 WHEN(LSTMNT_AVG<=2.939 AND LSTMNT_AVG>2.938857143)THEN 4 WHEN(LSTMNT_AVG<=2.945 AND LSTMNT_AVG>2.939)THEN 3 WHEN(LSTMNT_AVG<=2.949222222 AND LSTMNT_AVG>2.945)THEN 2 ELSE 1 END	—	—
`LY_PURCHASE_VALUE_CAT`	—	CASE WHEN(LY_PURCHASE_VALUE<=3558633)THEN 2 ELSE 1 END	—	—
`TOT_ACTIVITIES_CAT`	—	CASE WHEN TOT_ACTIVITIES <= 171 THEN 2 ELSE 1 END	—	—
`WEIGHTED_VALUE_CAT`	—	CASE WHEN WEIGHTED_VALUE >= 8.75 THEN 3 WHEN (WEIGHTED_VALUE >= 8.76 AND WEIGHTED_VALUE <= 12.5) THEN 2 ELSE 1 END	—	—
`LSTQRT_ACTIVITIES_CAT`	—	CASE WHEN(LSTQRT_ACTIVITIES<=70)THEN 2 ELSE 1 END	—	—
`LSTYR_ACTIVITIES_CAT`	—	CASE WHEN(LSTYR_ACTIVITIES<=161)THEN 2 ELSE 1 END	—	—
`LSTQRT_AVG_CAT`	—	CASE WHEN(LSTQRT_AVG<=6.005714286)THEN 15 WHEN(LSTQRT_AVG<=6.2025 AND LSTQRT_AVG>6.005714286)THEN 14 WHEN(LSTQRT_AVG<=6.625 AND LSTQRT_AVG>6.2025)THEN 13 WHEN(LSTQRT_AVG<=6.717 AND LSTQRT_AVG>6.625)THEN 12 WHEN(LSTQRT_AVG<=6.735 AND LSTQRT_AVG>6.717)THEN 11 WHEN(LSTQRT_AVG<=6.750333333 AND LSTQRT_AVG>6.735)THEN 10 WHEN(LSTQRT_AVG<=6.777333333 AND LSTQRT_AVG>6.750333333)THEN 9 WHEN(LSTQRT_AVG<=6.783 AND LSTQRT_AVG>6.777333333)THEN 8 WHEN(LSTQRT_AVG<=6.796 AND LSTQRT_AVG>6.783)THEN 7 WHEN(LSTQRT_AVG<=6.8145 AND LSTQRT_AVG>6.796)THEN 6 WHEN(LSTQRT_AVG<=14.101 AND LSTQRT_AVG>6.8145)THEN 5 WHEN(LSTQRT_AVG<=14.102 AND LSTQRT_AVG>14.101)THEN 4 WHEN(LSTQRT_AVG<=14.278 AND LSTQRT_AVG>14.102)THEN 3 WHEN(LSTQRT_AVG<=14.42 AND LSTQRT_AVG>14.278)THEN 2 ELSE 1 END	—	—
`UNITS_CAT`	—	CASE WHEN(UNITS<=11600)THEN 24 WHEN(UNITS<=12162 AND UNITS>11600)THEN 23 WHEN(UNITS<=12189 AND UNITS>12162)THEN 22 WHEN(UNITS<=12278 AND UNITS>12189)THEN 21 WHEN(UNITS<=12471 AND UNITS>12278)THEN 20 WHEN(UNITS<=12601 AND UNITS>12471)THEN 19 WHEN(UNITS<=12765 AND UNITS>12601)THEN 18 WHEN(UNITS<=12869 AND UNITS>12765)THEN 17 WHEN(UNITS<=12877 AND UNITS>12869)THEN 16 WHEN(UNITS<=13331 AND UNITS>12877)THEN 15 WHEN(UNITS<=13381 AND UNITS>13331)THEN 14 WHEN(UNITS<=13474 AND UNITS>13381)THEN 13 WHEN(UNITS<=13642 AND UNITS>13474)THEN 12 WHEN(UNITS<=13995 AND UNITS>13642)THEN 11 WHEN(UNITS<=14094 AND UNITS>13995)THEN 10 WHEN(UNITS<=14291 AND UNITS>14094)THEN 9 WHEN(UNITS<=14522 AND UNITS>14291)THEN 8 WHEN(UNITS<=15000 AND UNITS>14522)THEN 7 WHEN(UNITS<=15125 AND UNITS>15000)THEN 6 WHEN(UNITS<=19827 AND UNITS>15125)THEN 5 WHEN(UNITS<=19851 AND UNITS>19827)THEN 4 WHEN(UNITS<=21002 AND UNITS>19851)THEN 3 WHEN(UNITS<=21020 AND UNITS>21002)THEN 2 ELSE 1 END	—	—

030

030 — Predictive Forecast, creating Training Data

Level 7 · Target: 000001 Training Data Historical opportunities and Pipeline

clears target

1 op

▾

#	Code	Description	Type	Source	Target / Method	Mappings
0	`01`	Loading closed opportunities only	Insert from other dataset	`000001` Enrich Opportunity data Historical opportunities and Pipeline	—	—
Source filter · `WINLOS IS NOT NULL`

040

040 — Generating Predictive Forecast

Level 8 · Target: 000001 Predictive Data Historical opportunities and Pipeline

clears target

8 ops

▾

# Code Description Type Source Target / Method Mappings

0

010

Loading open opportunities

Insert from other dataset

000001 Enrich Opportunity data

Historical opportunities and Pipeline

—

Source filter · WINLOS IS NULL

1

020

Predicting WINLOS

Special method

—

RANDOM_FOREST

Calculate Forecast Values using Random Forest model

—

2

030

Predicting AGING

Special method

—

RANDOM_FOREST

Calculate Forecast Values using Random Forest model

—

3

040

Saving opportunity Aging Categories Before Prediction

Enrichment from other dataset

000001 Enrich Opportunity data

Historical opportunities and Pipeline

—

Source filter · WINLOS IS NULL

Target field	Source field	Group by
`AGING_CAT_OLD`	`AGING_CAT`	—
`ID`	`ID`	—
`ITEM`	`ITEM`	—

4 050 Check on Calculated Aging Categories Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`AGING_CAT`	—	CASE WHEN AGING_CAT > AGING_CAT_OLD THEN AGING_CAT_OLD ELSE AGING_CAT END	—	—

5 060 Recalculating Aging based on predictions Enrichment with formula — AGING_CAT <> AGING_CAT_OLD

Target filter · AGING_CAT <> AGING_CAT_OLD

Target field	WHEN condition	THEN	ELSE	Grp
`AGING`	—	CASE WHEN AGING_CAT = 1 THEN floor(random()(486))+1 ELSE floor(random()(493-487+1))+487 END	—	—

6 070 Recalculating Predicted Closing Date Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`CLOSE_DATE`	—	CREATION + (CAST(AGING AS INTEGER) * INTERVAL '1 day')	—	—

7 080 Deriving budget year and month from the predicted close date Enrichment with formula — —

Target field	WHEN condition	THEN	ELSE	Grp
`BDG_SCE`	—	to_char(CLOSE_DATE,'YYYY')\|\|\|\|'BDG'	—	—
`BDG_PER`	—	lpad(floor(random() * 12 + 1)::text,2,'0')	—	—

050

050 — Predictive forecast to Financial Workspace

Level 8 · Target: 000002 Data for Financial Workspace Data for financial workspace

Update Reporting Layer

1 op

▾

# Code Description Type Source Target / Method Mappings

0

010

Copying Predicted Values

Insert from other dataset

000001 Predictive Data

Historical opportunities and Pipeline

—

Target field	Source field	Group by
`ACCOUNT`	`'ST_902'`	—
`AMOUNT`	`VALUE`	Sum
`CATEGORY`	`FCT_VERSION`	Group by
`CURRENCY`	`CURRENCY`	Group by
`ENTITY`	`ENTITY`	Group by
`PERIOD`	`BDG_PER`	Group by
`PRODUCTS`	`PRODUCT`	Group by
`SCENARIO`	`BDG_SCE`	Group by

060

060 — Adding closed opportunities

Level 9 · Target: 000001 Predictive Data Historical opportunities and Pipeline

Update Reporting Layer

1 op

▾

#	Code	Description	Type	Source	Target / Method	Mappings
0	`010`	Adding closed opportunities	Insert from other dataset	`000001` Training Data Historical opportunities and Pipeline	—	—

Setupsource
Sources: OWS_DTP

Activities 11source
Sources: OWS_ATTIVITA, OWS_OPERAZIONE

Workflow task lists 1

Calculations & Data Validation

SetupsourceSources: OWS_DTP

Activities 11sourceSources: OWS_ATTIVITA, OWS_OPERAZIONE

Workflow task lists 1

Setupsource
Sources: OWS_DTP

Activities 11source
Sources: OWS_ATTIVITA, OWS_OPERAZIONE