EP Activity Report question

[Former Member]

Hi experts,

We are currently developing a customized portal activity report where the visits and impressions of each portal page will be categorized into Region, MonthYear and user types. However we are currently facing difficulties.

We are pulling data from portal database tables WCR_USERPAGEUSAGE and WCR_WEBCONTENTSTAT.

My question is, is it possible to get the visits and impressions of each user for a specific page? What will be the best way to go at this?

Thanks in advance!

[Former Member · Accepted solution]

Hello, thanks for the reply!

What if I want to display this data on a monthly aggregation? (hits and impressions of a specific user on a specific page for the whole month)

[sharon_dassa · Accepted solution]

Hi Greg,

You can also add the monthly column to the select statement :

[J30].[SAPJ30DB].[WCR_USERPAGEUSAGE].[MONTHLY_ID]

however , you need to know that the results of this column,

means the aggregation will be available only after a month.

Table [WCR_USERPAGEUSAGE] has several columns that each of them

has aggregation data only after the time that defined such as :

[HOURLY_ID] - aggregation after one hour

[DAILY_ID]-   aggregation after one day

[WEEKLY_ID] - aggregation after a week

[MONTHLY_ID]- aggregation after a month

I hope you understand the way how the aggregation is handled.

Thanks & Best Regards,

Sharon

[Former Member · Accepted solution]

Thanks Sharon! We were able to complete the enhancement on time.

[sharon_dassa]

Hi,

Yes , it is possible to get those information from the two table
by creating a new query that join the two table information

Here is how the query should be:

SELECT  [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT].[PCDURL],

         [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT].[IMPRESSIONS] ,

         [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT].[VISITS],

          [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT].CUSTOM,

       [J30].[SAPJ30DB].[WCR_USERPAGEUSAGE].[LOGONID]

FROM  [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT]

INNER JOIN [J30].[SAPJ30DB].[WCR_USERPAGEUSAGE]

ON  [J30].[SAPJ30DB].[WCR_WEBCONTENTSTAT].[ID]= [J30].[SAPJ30DB].

[WCR_USERPAGEUSAGE].[HOURLY_ID]

ORDER BY [J30].[SAPJ30DB].[WCR_USERPAGEUSAGE].[LOGONID];

Thank you & BR,

Sharon