8 Replies Latest reply on May 19, 2015 3:51 PM by Josh Kennedy

# calculate percent of returning customers

Hi! I work for a grocery store and we're expirementing with online sales. We created a very simple database to record the sales and I've hooked Tablaeu directly to that database to build a dashboard for our business users to gain insights into sales and customer behavior.

I'm having trouble thinking through how to calculate a specific requirement:  "What is the % of returning customers who order?"

This seems easy for me to calculate on my own, and I could TELL them what the number is, however, they want this value in a widget on their Tableau dashboard. To calculate by hand the %, I would do this psuedo math:

What is the % of returning customers who order?

[count of customers whose order count is > 1] / [count of customers] x 100 = [% of returning customers]

I tried to solve in Tableau by creating a calculated field, however I'm having trouble figuring out how to make the calcuation understand the "whose" clause in my psuedo math. For example, I tried this:

Calculated1 =

IF COUNTD([cust_id]) > 1

THEN

COUNTD([cust_id])

END

Calculated2 =

[Calculated1] / COUNTD([cust_id])

This is failing terribly, because the result is 100% return customers, even though it is clear in the data that only 50% of the customers are returning. Unfortunately, I just can't wrap my head around how to do this calculation in Tableau.

My interim solution is to write a seperate database table that will store the calculated values via a SQL script that I'll have run as a scheduled job on the server. I'll then include that table in the datasource and just basicly print that value to Tableau. That just seems like a hack, and that Tableau should be able to do this kind of thing on its own :-/

Any tips, clues, admonishment, etc... are welcome!

thanks!

pat

• ###### 1. Re: calculate percent of returning customers

Hi Pat,

Based on your data and scernario, I think the correct formula for calculating % of returning customers is

```IIF(SUM([Number of Records])>1, 1, 0)/COUNTD([cust_id])
```

Number of Customers:

```COUNTD([cust_id])
```

Returning customers

```IIF(SUM([Number of Records])>1, 1, 0)
```

and

Pct of Returning Customers

```IIF(SUM([Number of Records])>1, 1, 0)/COUNTD([cust_id])
```

I created Stat callouts to show these values in a dashboards.

Take a look at the attached workbook and let me know if it provides a solution to to your question.

Best

Ramon

1 of 1 people found this helpful
• ###### 2. Re: calculate percent of returning customers

This is a nice piece. However,  formula doesn't seem to fit in my calculations in a project I am working on. This returns the percentage as (1/CountD[customer IDs]) % , which seems wrong. Any inputs?

• ###### 3. Re: calculate percent of returning customers

Hi Manas,

You are right, the formula to calculate % of returning customer is not working. I'm trying to figure out a solution.

Best,

Ramon

• ###### 4. Re: Re: calculate percent of returning customers

Hi Pat and Manas,

Please, check out this new attached workbook with a solution to calculate the % of returning customer.

In this case I have use Table Calculation. Tale a look at all worksheets from Sheet 1 to Sheet 3 to see the sequence of procedures

Sheet 3 is similar to Sheet 2 but with rows and columns hided just to keep the % of returning values.

I hope this helps

Best regards,

Ramon

• ###### 5. Re: calculate percent of returning customers

Wow, very neat!  The use of the IIF function is new to me. Thank you for demonstrating how it can be used to help solve this problem.

• ###### 6. Re: calculate percent of returning customers

Hi Pat,

I'm glad you have a solution to calculate the % of returning customer based on you scenario.

Best regards,

Ramon

• ###### 7. Re: calculate percent of returning customers

Hi Ramon Martinez, can you help me make sense of the calculations? I didn't get the calculations in your last sheet. Was it the correct one?

Thanks !

• ###### 8. Re: calculate percent of returning customers

This is a great response Ramon.  I was curious if you could take it a step further.  I have a similar requirement however I'm unable to use countd(cust_id) in my calculation as I need to consider a specific window of time for the number of customers:

Of customers who showed up last Sunday, or last week to simplify, what is the % of returning this week?

I considered using a set of customer IDs instead but was struggling to get it to play nicely.

Thanks!