BIX to extract campaign result into table
Hi all,
i am newbie here, need some advise about BIX...:)
user need some reporting and analyse based on campaign data for all campaign which sent to customer.
what i know that each time campaign run - PEGA will store result record into PEGA table (e.g. batchoutpr3236, batchoutpr3237,batchoutpr3238, etc)
i need to consolidate all campaign run records into my campaign output table.
Does BIX able to extract campaign result on the fly ? how to integrate this process that after campaign has completed then BIX can automatically write the campaign output to my campaign output table
Regards,
vironica
***Edited by Moderator Marissa to update platform capability tags***
Accepted Solution
Pegasystems Inc.
GB
Hi Veronica,
you could identify your "customer" table by identifying the Customer class set in the "Manage Data Relationship" tab in Configuration landing page of Marketing portal. Open the class definition rule and perform Test connectivity the resulting dialog will indicate the underlying database table.
I was suggesting to join Communication history with "Customer" table, assuming you need both offer names/responses from strategy and corresponding customer data.
IH already has the reference to your campaign runs. its stored as a Response context (and therefore available Communication history as pyCategory) which points to your campaign runs,
In IH data model, refer
1. PZCONTEXTID in PR_DATA_IH_FACT, and PYCATEGORY in PR_DATA_IH_DIM_CONTEXT for Campaign run class.
2. PYSUBJECTID in PR_DATA_IH_FACT for your customer id.
As Simon suggested, Since there are no BLOBs to handle to get the data you need, may be BIX is not the right tool, you could just construct a sql query to get the data.
alternatively you could also try using output db templates in campaign engagement configuration, to store the same data in a known table.
Pegasystems Inc.
GB
Hi Vironicam,
BIX has been designed to extract data from a BLOB pzPvStream stream. In the marketing campaign output table, there are no BLOB created.
I am not sure if BIX is the correct tool to achieve your requirement.
Regards
Simon
M1 Limited
SG
Hi Chans3,
Thank you for your reply.
if i manually do the mapping class for each of the PEGA campaign result tables; BIX is able to extract the data.
Not sure if BIX can read those table without need to create the mapping class ... and also BIX is able to identify what is the campaign result table which generated by PEGA after campaign has completed
cheers,
V
Pegasystems Inc.
GB
The Extract rule is mean to extract instances of a class. As such, the class mapping is required.
Regards
Simon
M1 Limited
SG
Hi Simon,
could share your view - in practise day-to-day;
what people use BIX for ?
does my user requirement is not recommend to use BIX ? but need to use ETL tool instead ?
my worry that if i use ETL tool to extract out the campaign result;
will PEGA internally change the table naming convension, etc as it is runtime generated table or any other things ?
this is the first comes in mind if i use external tool to extract out data from PEGA internal tables.
cheers,Vironica
Pegasystems Inc.
GB
I guess you should also check if Interaction History (IH) could cater to your reporting requirements.
for all outbound Campaigns, at the time of offer processing, a default record is inserted to IH with "pending Neutral" response.
Pega Marketing also have view called "MKT_COMMUNICATION_HISTORY" which flattens the IH data structure for easy reading of data. may be this could help gathering Campaign stats.
M1 Limited
SG
Hi ManuV_GCS,
Thank you for your reply.
The MKT_COMMUNICATION_HISTORY view has stored any customer records which sent out;
But i need other information which defined/used in each campaign e.g some customer profile like their ID No, DOB, address, spending score, status, etc as of the campaign run.
These information will be available in those PEGA campaign result e.g. batchoutpr3236,batchoutpr3237,batchoutpr3238, etc.
Not sure BIX is able to use perform join 2 tables (tables name need to be passed as parameter as it is generated by PEGA internally) ?
or any other alternative to join the MKT_COMM_HIST table with PEGA campaign result then insert the records in my table ?
Cheers
V
Pegasystems Inc.
GB
i still think accessing batch table probably not a good idea as they are generated runtime, the solution wont be generic.
However the same information is available in "Customer" table as well, you could use report definition to join between customer table and communication history table and possibly use schedule option to email out the report.
Since both of these tables are extremely big, RD might not be performant here. you could run a query directly on DB , use an etl or something and get the output.
M1 Limited
SG
Hi ManuV_GCS,
not sure why the "Customer" table is empty in my PEGA DB.
if using Report Definition then i need to join the "comm history table and campaign output table" one by one,
and user has lot of campaign run in daily basis (both recurring and adhoc).
looking at what's Simon and your view, does BIX is not suitable for this user requirement ??
cheers
Vironica
Accepted Solution
Pegasystems Inc.
GB
Hi Veronica,
you could identify your "customer" table by identifying the Customer class set in the "Manage Data Relationship" tab in Configuration landing page of Marketing portal. Open the class definition rule and perform Test connectivity the resulting dialog will indicate the underlying database table.
I was suggesting to join Communication history with "Customer" table, assuming you need both offer names/responses from strategy and corresponding customer data.
IH already has the reference to your campaign runs. its stored as a Response context (and therefore available Communication history as pyCategory) which points to your campaign runs,
In IH data model, refer
1. PZCONTEXTID in PR_DATA_IH_FACT, and PYCATEGORY in PR_DATA_IH_DIM_CONTEXT for Campaign run class.
2. PYSUBJECTID in PR_DATA_IH_FACT for your customer id.
As Simon suggested, Since there are no BLOBs to handle to get the data you need, may be BIX is not the right tool, you could just construct a sql query to get the data.
alternatively you could also try using output db templates in campaign engagement configuration, to store the same data in a known table.