Analytics query to fetch micro certification data

[shivmano]

Hi All, 

I am looking for SQL query to fetch a report of users for whom the user update rule to launch user manager campaign was launched but the campaign was not created for some reason. Can anyone please share or advise 

Basically I am looking for a validation to make sure that the no. of rules triggered equals the no. of campaigns created to catch any failures

Thank you 

[dgandhi]

I don't think you will be able to find this information.

When the rule runs , its not like the campaign is launched immediately. The campaign is launched based on the campaign job that we run. Mostly the campaign creation job would be run once in day.

[shivmano]

Right. I am trying to find a way to get the list of users who had the rule triggered from the usershistory table and then compare that list with the certification_user table in a single query to catch users who did not have the campaign launched. There will be scenario where rule is executed successfully but campaign creation failed. So, it will be good to have a way of identifying those failed campaign triggers 

[shivmano · Accepted solution]

I was able to get the users who had the rule run but campaign not created using the below query

select u.username, a.ruleruns, a.updatedate as update_date from users u, usershistory a where
a.USERKEY = u.USERKEY and
a.ruleruns like '%<Rule Name>%' and
DATEDIFF(now(),a.UPDATEDATE) <= 1 and
u.userkey not in (select b.userkey from campaign cm inner join certification c on cm.id=c.campaignkey inner join certification_user_status cus on c. certkey=cus.certkey inner join certification_user cu on cus.cert_userkey= cu.cert_userkey inner join users b on cu.userkey=b.userkey where cm.campaign_name like '%<Campaign Name>%' and DATEDIFF(now(),c.createdate) <= 1)