stats and eventstats

[SanthoshSreshta]

Hi All.

I want to calculate the percentage of churned_customer in rural and urban areas.
The columns i have are CHURN with values 0 and 1 where "0" represents unchurn and "1" represents churned and another column PLACEMENT with values 0 and 1 where 0 represents Rural and 1 represents Urban.

the query i had used is

sourcetype="Customer_Churn"
| stats count(eval(CHURN="1")) as churned_count by PLACEMENT
| stats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| stats values(churned_percentage) by PLACEMENT

it is not showing any values. please resolve it.

I have calculated for total_revenue in the same manner by using the code mention below.
here i have calculated tot_revenue percentage in rural and urban areas of all churns. where in above i want only churned. please some one help me out.

sourcetype="Customer_Churn" 
    | stats sum(Total_Revenue) as rev by PLACEMENT
    | eventstats sum(rev) as fin_rev
    | eval REVENUE = rev*100/fin_rev
    | stats values(REVENUE) as Rev_Percent by PLACEMENT
    | replace 0 with Rural in PLACEMENT
    | replace 1 with Urban in PLACEMENT

thanks a lot in advance,
Santhosh.

[umeshsnwl]

sourcetype="Customer_Churn"
| eventstats count(eval(CHURN="1")) as churned_count by PLACEMENT
| eventstats count(eval(CHURN="1")) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats values(churned_percentage) by PLACEMENT

hope this will work

[aweitzman · Accepted solution]

After your first stats statement, your CHURN values are gone, so the second stats statement doesn't work. You want something similar to the second search, like so:

sourcetype="Customer_Churn" 
     | stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
     | eventstats sum(churned_count) as tot_churn_count
     | eval churned_percentage=churned_count*100/tot_churn_count
     | stats values(churned_percentage) as Churn_Percent by PLACEMENT
     | replace 0 with Rural in PLACEMENT
     | replace 1 with Urban in PLACEMENT

[SanthoshSreshta]

Hi @aweitzm yeah.!!! I got it individually.
but, when I am combining both of the queries it is showing empty results, can you please club them.
I have tried like this.

 sourcetype="Customer_Churn" 
      | stats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
      | eventstats sum(churned_count) as tot_churn_count
      | eval churned_percentage=churned_count*100/tot_churn_count
      | stats sum(Total_Revenue) as rev by PLACEMENT
      | eventstats sum(rev) as fin_rev
      | eval REVENUE = rev*100/fin_rev
      | stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent  by PLACEMENT
      | replace 0 with Rural in PLACEMENT
      | replace 1 with Urban in PLACEMENT

Can you find any clue for that. I want them to give values when combined both queries.

[Arun_N_007]

Hi Santhosh,

When you execute transformation commands you will end up loosing other columns its better you use event level stats comamnds till the final transformation to retain all the values.

Once you execute the first stats command you wille end up with only PLACEMENT, churned_count. Use below query,
sourcetype="Customer_Churn"
| eventstats sum(eval(if(CHURN=="1",1,0))) as churned_count by PLACEMENT
| eventstats sum(churned_count) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(rev) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

[SanthoshSreshta]

Here is the Query for my actual result. Thanks for helping me @Arun_N_007 and @aweitzman

sourcetype="Customer_Churn" 
| eventstats sum(eval(if(CHURN=="0",1,0))) as churned_count by PLACEMENT
| eventstats sum(eval(if(CHURN=="0",1,0))) as tot_churn_count
| eval churned_percentage=churned_count*100/tot_churn_count
| eventstats sum(Total_Revenue) as rev by PLACEMENT
| eventstats sum(Total_Revenue) as fin_rev
| eval REVENUE = rev*100/fin_rev
| stats values(churned_percentage) as Churned_Percent values(REVENUE) as Rev_Percent by PLACEMENT
| replace 0 with Rural in PLACEMENT
| replace 1 with Urban in PLACEMENT

[SanthoshSreshta]

@Arun_N_007 sorry .I tried to delete the comment of mailID. it has deleted all the thread. I am so sorry.
and I have accepted the answer.