Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

Adobe Metrics Calculation Raw Data Feed

mrigayas1618410
Level 1
Level 1

Hi,

I am trying to calculate total number of times an evar event fired on the website,

Event Description: Trying to calculate site search metric on a particular page, that is top searched terms on a page. The search terms are filtered in a custom report called post_evar4 where all terms searched on the page are captured. I am trying to create formula to calculate instances from the raw data feed.

Problem: I am not sure what is the best way to go about calculating events fired for this particular custom event, any help would be appreciated. In the end I want to write a sql query to calculate instances for the post_evar4 terms searched.

3 Replies
Asheesh_Pandey
Community Advisor
Community Advisor

Hi,

The event post_event_list contains a comma-delimited list of events, which may have a value and/or a unique ID. You should be doing all processing on the post_event_list because it is de-duplicated and has already applied logic to remove duplicate events with the same ID.

When an eVar is set on a hit, event_list contains an instance of that eVar, find your event number in the event_lookup.tsv file. Now use that to build your sql query. e.g.

/* Returning rows containing a custom event */

SELECT event_list
  FROM online_users
WHERE CONCAT
(',', event_list, ',') LIKE "%,103=%,"
AND hit_source
= 1
AND exclude_hit
= 0;


Example Output:
+--------------------+
| event_list |
+--------------------+
| ,1,103=17,201, |
| ,1,103=20, |
| ,1,103=10, |
| ,1,103=17,201, |
| ,1,203,103=7,201, |
+--------------------+
5 rows in set (0.01 sec)

you can also use post_evar4 in your query to get results by terms, you will then need to split your event list to make it meaningful for analysis.

Ex. Data Feed contents - overview

Thanks,

Asheesh

David-123
Level 7
Level 7

I might be reading this completely wrong (as my answer is very different to asheeshp​ however if I wanted to to see "evar4" and "count of evar4 instances" I would do the following using evar4 - not post_evar4:

select evar4 as "Search Term"

,count(evar4) as "Searches"

from adobe_hit_data

group by evar4

abhishekj683908
Level 1
Level 1

Yeah I also think that in this case using evar4 instance would be more helpful, as the post_evar4 will reappear in all the subsequent hit until the value changes. Similarly post_evant_list will also appear till the event list gets a new value. Since you are only interested in instances of evar4 search event_list for the corresponding event # .