Onboard Tenant Account

Onboard Tenant Account in DB

To onboard the campaign ID (tenant account), follow the steps below:

1. In the database, update the following queries in sequence to onboard the tenant account. As a first step, select a vrm database.

   Use vrm;

2. Set the Tenant ID /Campaign ID to the campaign variable. Make sure this tenant is created in the Admin UI.

   set @campaign := <campaignId>;

3. Set the AWS region of the tenant account in the region variable.

   set @region:= "aps1";

4. Update the AWS region in the DMPCampaign table.

   Update DMPCampaign set Region = 'aps1' where CampaignId = @campaign;
   

5. To support Unicode characters, @campaign is tenantid.

   insert into `CampaignUnicodeRangeMapping` (CampaignId, UnicodeRanges) values (@campaign, 'U+0020-U+007F');

6. After updating the vertical name, generate a KMS key using the AWS console. Then, attach a key policy that allows the EC2 and Kubernetes service account IAM role to access the created KMS Key ID. Similarly, insert KMS key Id for Hashicorp(Openshift into CampaignEncryptionKeyMapping. This key ID will be used for PII (e.g. Mobile/Phone) Data Encryption.

   insert into `CampaignEncryptionKeyMapping` (CampaignId, keyId, TTL_Seconds, Keys_Count, IsActive) values (@campaign, '<KMS KEY ARN>', 86400, 100, 1);

7. If needed, expose the segmentation parameters on UI as mentioned below. To enable the segmentation in fields like Country customer properties/attributes, add entries in FieldAliasTable table for attribute in profile, where adv_id is tenantid, original_field_name is data point code, modified_field_name is UI field name, type_of_ui_widget is data type of field like Text/Number, json_key is json path.

   INSERT Into FieldAliasTable ( adv_id,original_field_name,modified_field_name,type_of_ui_widget,is_new_user,json_key,index_of_group_key,suggest_at,CreatedOn,UpdatedOn,CreatedBy,UpdatedBy)
    VALUES ('@campaign','COUNTRY','COUNTRY','Text',0,'country',0,0,'2024-04-12',NULL,<user ID>,NULL), ('@campaign','CITY','CITY','Text',0,'city',0,0,'2024-04-20',NULL,<user ID>,NULL);

8. Insert all the following dashboard layouts in Unica Dashboard. Before inserting change the campaignId in the queries with respective ID.
   QUERY 1: Main Dashboard

   INSERT INTO UnicaDashboard (Name, CampaignId, Layout, CreatedBy, CreatedOn)
   VALUES ('Main Dashboard', campaignId, '[{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/campaigns.svg","infoText":"campaignsInfoText","label":"campaigns","dataKey":"campaigns","growthPercentageKey":"campaigns_growth","endPoint":"summaryCard"},"id":"N1732710966519","x":0,"y":0},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/conversion.svg","infoText":"totalConversionsInfoText","label":"conversion","dataKey":"conversions","growthPercentageKey":"conversions_growth","endPoint":"summaryCard"},"id":"T1732713799705","x":7,"y":0},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/clicks.svg","infoText":"clicksInfoText","label":"clicks","dataKey":"clicks","growthPercentageKey":"clicks_growth","endPoint":"summaryCard"},"id":"S1732713803095","x":14,"y":0},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/usersTargeted.svg","infoText":"activeUsersTargetedInfoText","label":"usersTargeted","dataKey":"userTargeted","growthPercentageKey":"userTargetedGrowth","endPoint":"userTargeted"},"id":"R1732714361763","x":21,"y":0},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/cvr.svg","infoText":"cvrInfoText","label":"cvr","dataKey":"cvr","growthPercentageKey":"cvr_growth","endPoint":"summaryCard"},"id":"G1732714368923","x":28,"y":0},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/ctr.svg","infoText":"ctrInfoText","label":"ctr","dataKey":"ctr","growthPercentageKey":"ctr_growth","endPoint":"summaryCard"},"id":"S1732714371889","x":0,"y":5},{"type":0,"label":"Summary Card","value":0,"w":7,"h":5,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/responseRate.svg","infoText":"responseRateInfoText","label":"responseRate","dataKey":"responseRate","growthPercentageKey":"responseRate_growth","endPoint":"summaryCard"},"id":"J1732714375405","x":7,"y":5},{"type":3,"label":"Channel Insight Card","value":3,"config":{"label":"channelInsights","infoText":"defaultChannelInsightInfotext","summaryCardList":[{"icon":"/app/assets/icons/cdpDashboard/usersTargetedBg.svg","infoText":"uniqueProfilesInfoText","label":"usersTargeted","dataKey":"userTargeted","growthPercentageKey":"userTargetedGrowth","endPoint":"channelInsightUserTargeted"},{"icon":"/app/assets/icons/cdpDashboard/responseRateBg.svg","infoText":"uniqueProfilesInfoText","label":"responseRate","dataKey":"responseRate","growthPercentageKey":"responseRate_growth","endPoint":"channelInsightSummeryCard"},{"icon":"/app/assets/icons/cdpDashboard/cvrBg.svg","infoText":"uniqueProfilesInfoText","label":"cvr","dataKey":"cvr","growthPercentageKey":"cvr_growth","endPoint":"channelInsightSummeryCard"}],"graphCard":{"chartType":"composedChart","layout":"horizontal","metrics":[{"key":"field_1","label":"clicked","type":"line","color":"#e64215"},{"key":"field_2","label":"Read","type":"line","color":"#5e4646"}],"endPoint":"eventCompareChart","dimension":{"xLabel":"date","yLabel":"count","key":"week","hideXaxis":false,"hideYaxis":false}}},"w":48,"h":15,"autoPosition":true,"id":"T1733117625073","x":0,"y":10},{"type":4,"label":"Revenue Insight Card","value":4,"config":{"label":"revenueInsights","infoText":"defaultRevenueInsightInfotext","gridCardList":[{"type":"","label":"Campaigns with highest revenue","infoText":"campaignsWithHighestRevenueInfotext","endPoint":"campaignshHighestRevenue","headerData":[{"id":0,"columnName":"Campaign","columnKey":"campaignId","isEditing":false},{"id":1,"columnName":"Campaign Name","columnKey":"campaignName","isEditing":false,"linkUrl":"/app/campaignDetails/:campaignId","linkValue":"campaignId","linkKey":"campaignId"},{"id":2,"columnName":"Conversions","columnKey":"conversions","isEditing":false},{"id":6,"columnName":"Revenue","columnKey":"revenue","isEditing":false}],"labelAllignment":"left","labelIcon":"/app/assets/icons/cdpDashboard/incrementIcon.svg"},{"type":"","label":"Campaigns with lowest revenue","infoText":"campaignsWithLowestRevenueInfotext","endPoint":"campaignshLowestRevenue","headerData":[{"id":0,"columnName":"Campaign","columnKey":"campaignId","isEditing":false},{"id":3,"columnName":"Campaign Name","columnKey":"campaignName","isEditing":false,"linkUrl":"/app/campaignDetails/:campaignId","linkValue":"campaignId","linkKey":"campaignId"},{"id":4,"columnName":"Conversions","columnKey":"conversions","isEditing":false},{"id":5,"columnName":"Revenue","columnKey":"revenue","isEditing":false}],"labelAllignment":"left","labelIcon":"/app/assets/icons/cdpDashboard/decrementIcon.svg"}],"graphCard":{"chartType":"composedChart","layout":"vertical","metrics":[{"key":"revenue","label":"revenue","type":"bar","color":"#e64215","barChartShape":"rounded"},{"key":"spent","label":"spent","type":"bar","color":"#5e4646","barChartShape":"rounded"}],"endPoint":"revenueInsightDetail","dimension":{"xLabel":"","yLabel":"revenue","key":"","hideXaxis":false,"hideYaxis":true}}},"w":48,"h":12,"autoPosition":true,"id":"Y1733117776592","x":0,"y":25},{"type":2,"label":"Grid Card","value":2,"w":48,"h":16,"autoPosition":true,"config":{"type":"","label":"topPerformingCampaigns","infoText":"topPerformingCampaignsInfotext","endPoint":"topPerformingCampaign","headerData":[{"id":0,"columnName":"Campaign Code","columnKey":"campaignCode","isEditing":false},{"id":2,"columnName":"Revenue","columnKey":"revenue","isEditing":false},{"id":3,"columnName":"Users Targeted","columnKey":"usersTargeted","isEditing":false},{"id":4,"columnName":"Clicks","columnKey":"clicks","isEditing":false},{"id":5,"columnName":"Conversions","columnKey":"conversions","isEditing":false}],"labelAllignment":"left"},"id":"W1733118051954","x":0,"y":37}]', 3527, CURRENT_TIMESTAMP);
   

   QUERY 2: Campaign Dashboard

   INSERT INTO UnicaDashboard (Name, CampaignId, Layout, CreatedBy, CreatedOn)
   VALUES ('Campaign Dashboard', campaignId, '[{"type":0,"label":"Summary Card","value":0,"w":12,"h":6,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/offers.svg","infoText":"offer","label":"offer","dataKey":"offers","growthPercentageKey":"offers","endPoint":"summaryCard"},"id":"X1732017756248","x":12,"y":0},{"type":0,"label":"Summary Card","value":0,"w":12,"h":6,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/campaigns.svg","infoText":"campaigns","label":"campaigns","dataKey":"countTargetedUsers","growthPercentageKey":"countTargetedUsersPercentage","endPoint":"summaryCard"},"id":"H1732017831843","x":0,"y":0}]', 3527, CURRENT_TIMESTAMP);
   

   QUERY 3: COV Basics

   INSERT INTO UnicaDashboard (Name, CampaignId, Layout, CreatedBy, CreatedOn)
   VALUES ('COV Basics', campaignId, '[{"type":0,"label":"Summary Card","value":0,"w":12,"h":6,"autoPosition":true,"config":{"icon":"/app/assets/icons/cdpDashboard/campaigns.svg","infoText":"uniqueProfilesInfoText","label":"uniqueProfiles","dataKey":"countTargetedUsers","growthPercentageKey":"countTargetedUsersPercentage","endPoint":"summaryCard"},"id":"F1732109817102","x":0,"y":0}]', 3527, CURRENT_TIMESTAMP);
   

   QUERY 4: COV campaign

   INSERT INTO UnicaDashboard (Name, CampaignId, Layout, CreatedBy, CreatedOn)
   VALUES ('COV campaign', campaignId, '[{"type":2,"label":"Grid Card","value":2,"w":48,"h":16,"autoPosition":true,"config":{"type":"","label":"Top Performing Campaigns","infoText":"topPerformingCampaigns","endPoint":"topPerformingCampaign","headerData":[{"id":0,"columnName":"Name","columnKey":"campaigncode","isEditing":false}],"labelAllignment":"center","columnWidth":"auto","customerId":"viz_66ea7ac0d0c0d"},"id":"E1733747539834","x":0,"y":0}]', 3527, CURRENT_TIMESTAMP);
   

   QUERY 5: AIPropertyCardQuery

   INSERT INTO UnicaDashboard (
     Name, CampaignId, Layout, CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
   ) VALUES (
     'AI Property Card',
     6536,
     '[{"type":7,"label":"AI Generated Property Card","value":7,"config":{"label":"maxAiPrediction","icon":"","headerData":[{"id":0,"columnName":"preferredChannelProbability","columnKey":"channelProbability","isEditing":false},{"id":1,"columnName":"preferredContactTime","columnKey":"preferredContactTime","isEditing":false},{"id":2,"columnName":"preferredContactTimeProbability","columnKey":"preferredContactTimeProbability","isEditing":false},{"id":3,"columnName":"preferredChannel","columnKey":"preferredChannel","isEditing":false}],"endPoint":"","AiCardList":[{"label":"communicationPrefs","infoText":"ChannelAiCardInfo","headerData":[{"id":0,"columnName":"primaryChannel","key_1":"primary_channel","key_2":"top_sto_for_primary_channel","isEditing":false,"key_3":"Custom","customInput":"","customFormat":":00"},{"id":1,"columnName":"secondaryChannel","key_1":"secondary_channel","key_2":"top_sto_for_secondary_channel","isEditing":false,"key_3":"Custom","customInput":":00","customFormat":":00"},{"id":2,"columnName":"tertiaryChannel","key_1":"tertiary_channel","key_2":"top_sto_for_tertiary_channel","isEditing":false,"key_3":"Custom","customInput":"","customFormat":":00"}],"col":4}]},"w":48,"h":7,"autoPosition":true,"id":"C1739974424544","x":0,"y":0}]',
     1001,
     NOW(),
     NULL,
     NULL
   );

9. After executing the queries successfully, run query below to get IDs:

   select * from UnicaDashboard;

10. Collect the IDs and update the ID in the DMPCampaign Config as shown below.

    "navConfig":{"dashboard":{"id":49},"campaign":{"id":50},"cov_basic":{"id":51,"ai_card_id":157},"cov_campaign":{"id":52}}

11. After you replace campaignId with newly created campaignId like 6565, complete update query like below.

    update DMPCampaign set Config='{"featureEnablingConfig":{"isDestinationEnabled":true,"isSourceConnectionEnabled":true,"isConnectionsEnabled":true,"sources":{"allEnabled":true},"destinations":{"allEnabled":true},"isCustomerPropertiesEnabled":true,"isCalculatedPropertiesEnabled":true,"isChannelsAvailable":true,"isSegmentsAvailable":true,"isReportingEnabled":true,"isFunnelReportsEnabled":true,"isPathMetricsReportsEnabled":true,"isUserReportsEnabled":false,"isCampaignReportsEnabled":false,"isSettingsAvailable":true,"isOfflineDataSourceEnabled":true,"isProfileUploadEnabled":true,"isCustomerOneViewEnabled":true,"isEventsReportsEnabled":true,"isTelemetryReportsEnabled":true,"isLiveEventEnabled":true,"isURLReportingEnabled":true,"isEventsDashboardEnabled":true,"eventOccurrenceEnabled":true,"isCampaignSummaryEnabled":true,"isAnalyticsEnabled":true,"eventTrendsEnabled":true,"isDashboardEnabled":true,"isCampaignEnabled":true,"cov":{"isCovBasicDetailsEnabled":true,"isCovAttributeEnabled":true,"isCovDeviceEnabled":true,"isCovCampaignEnabled":true,"isCovActivityEnabled":true,"isCovVoiceFlowEnabled":false},"isOldCovActivityEnabled":true,"isLinkEnabled":true,"isAngularChannels":true,"isAngularDashboard":true,"isAngularSegment":true,"isAngularCov":true},"BusinessGeo":{"region":"aps1","tkd":{"regionUrl":"http://www.hclcdp.com:8001"},"dash":{"regionUrl":"http://www.hclcdp.com:8080"}},"isCustomListSegmentEnabled":true,"capsSettingsConfig":{},"cdnType":"cloudFront","tagInfo":{"__onsiteEndpoint__":"cdp.dev.hxcd.now.hclsoftware.cloud/","__analyzeUrl__":"https://pl.dev.hxcd.now.hclsoftware.cloud/analyze/analyze.php","__fpcUrl__":"https://pl.dev.hxcd.now.hclsoftware.cloud/analyze/cookieCallback.php?cb=","__curCamp__":"VIZVRM6536","__cookieDomain__":"hclcdp-tango-6543.dev.hxcd.now.hclsoftware.cloud","__domainList__":"hclcdp-banking-6526.dev.hxcd.now.hclsoftware.cloud","__logLevelFlag__":1,"__advId__":6543,"version":"v3","uglify":true},"navConfig":{"dashboard":{"id":49},"campaign":{"id":50},"cov_basic":{"id":51,"ai_card_id":157},"cov_campaign":{"id":52}},"dashboardConfig":{"default":{"id":3},"cov":{"id":4}},"maxPasswordAgeInDays":"90"}' where CampaignId=campaignId;

Data Source Configurations

Configure the MySQL data sources with campaign details like campaign ID, email, mobile and so on.

To update the Mysql DB with the data source configurations, follow the steps below:

1. Set the User Identifier for Upset charts, and insert the campaign details in the CDPCampaignUserIdentifier table using the following queries in sequence.

   insert into CDPCampaignUserIdentifier (CampaignId,Code,AlternateDescription) values (<CampaignId>,'vizid','Cookie');
   insert into CDPCampaignUserIdentifier (CampaignId,Code,AlternateDescription) values (<CampaignId>,'crmid','Customer_Id');
   insert into CDPCampaignUserIdentifier (CampaignId,Code,AlternateDescription) values (<CampaignId>,'he','Email');
   insert into CDPCampaignUserIdentifier (CampaignId,Code,AlternateDescription) values (<CampaignId>,'hm','Mobile');
   commit;

2. Update the DMPCampaignDataDictionary table with Campaign ID details to set the primary identifier for the tenant:

   Update DMPCampaignDataDictionary set Preference='pri' where Id = <crmAttributeId> and CampaignId = <CampaignId>;

3. Set the secondary identifier and priority of identifier for the tenant using the below queries. The <crmAttributeId>,<emailAttributeId> and <phoneAttributeId> are from DMPCampaignDataDictionary table.

   Update DMPCampaignDataDictionary set Preference='sec' where Id = <emailAttributeId> and CampaignId = <CampaignId>;
   Update DMPCampaignDataDictionary set Preference='sec',Priority=2 where Id = <phoneAttributeId> and CampaignId = <CampaignId>;
   commit;
   update DMPCampaignDataDictionary set IsRtsWhitelisted = 1 where Id = <crmAttributeId>;

4. Create a Data Ingestion (DI) API data source in the MySQL database using the following queries in sequence.

   set @pss :=(select max(Id) from DMPCampaignDataSource)+1;
   INSERT INTO DMPCampaignDataSource (Id, CampaignId, Name, Format, IsActive, IsNba, IsTriggerDataSource, DisplayName) VALUES
   (@pss,<CampaignID>,'dataingestionapi','json',1, 1, 1, 'DIAPI');
   

5. Similarly, create a pixel listener data source using the following queries in sequence.

   INSERT INTO DMPCampaignDataSource(CampaignId, Name, Format, IsActive, IsOnline, IsTriggerDataSource, DisplayName) 
   VALUES(<CampaignID>, 'analyze_post', 'json', 1, 1, 1, 'analyze_post');

Create Pixel Listener Data Source

To create a Pixel Listener Data Source, follow the steps below:

1. Connect to the database that hosts the DMPCampaignDataSource table.
2. Run the following SQL command, replacing <CampaignID> with the actual tenant ID to insert the pixel listener data source:

   INSERT INTO DMPCampaignDataSource
   (CampaignId, Name, Format, IsActive, IsOnline, IsTriggerDataSource, DisplayName)
   VALUES(<CampaignID>, 'analyze_post', 'json', 1, 1, 1, 'analyze_post');

Create Index in MongoDB

After onboarding the tenant account in db, create indexes on the collections used for segmentation and suggestion/count caching in the MongoDB. To create indexes in MongoDB, follow the steps below:

1. Log in to the primary node using mongosh, and execute the below queries in sequence. Switch the segmentation database.

   use segmentation;

2. Create a hashed index for segmentation purposes.

   db.VIZVRM<CampaignId>.createIndex({"key":"hashed"},{"sparse":true});

3. Now, switch cache database, and create an index on the queryId field for storing count or metrics data for that campaign.

   use cache;
   db.VIZVRM<CampaignId>_count.createIndex({"queryId":1},{"sparse":true,"unique":true});

4. Create an index on the queryId field for storing suggestion related data.

   db.VIZVRM<CampaignId>_suggestion.createIndex({"queryId":1},{"sparse":true,"unique":true});