Adobe Metrics Calculation Raw Data Feed | Community
Skip to main content
M
MrigayaSi
August 26, 2019
Solved

Adobe Metrics Calculation Raw Data Feed

  • August 26, 2019
  • 3 replies
  • 3025 views

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.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Asheesh_Pandey

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

3 replies

Asheesh_Pandey
Community Advisor
Asheesh_PandeyCommunity AdvisorAccepted solution
Community Advisor
August 27, 2019

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_Jerome
David_Jerome
Level 6
August 27, 2019

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
abhishekj683908
August 27, 2019

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 # .

Terms & ConditionsAccessibility statement

Loading footer...