Formulas

Retention rate formula

Customer retention rate is the share of customers you had at the start of a period who were still customers at the end of it.

About the Shopify connector

The formula has three inputs and the middle one is the one people drop. Leave the customers you acquired during the period in the end count and the rate flatters you, sometimes past 100%, which is the tell. What counts as a good rate depends on how often your product is bought and how long the period is, so compare like with like and with your own past.

The formula

Retention rate = (customers at end − new customers acquired in the period) ÷ customers at start

Decide what "customer" means before you count anyone. For a subscription it is an active subscriber. For a shop it is usually someone who ordered inside the window, so a customer is retained if they bought in this period and the one before.

In Google Sheets, with customers at start in column B, new customers in C and customers at end in D:

=IF(B2=0, "", (D2-C2)/B2)

Format the column as a percentage. The IF stops a period with no starting customers returning a #DIV/0! error.

A worked example

In Q1, 800 of the original 1,000 stayed and 300 joined, which is how the end count reaches 1,100. The total row pools the quarters: retained customers over starting customers, summed across the three:

=IF(SUM(B2:B4)=0, "", (SUM(D2:D4)-SUM(C2:C4))/SUM(B2:B4))

That is (3,600 − 950) ÷ 3,250 = 81.5%, a quarterly rate weighted by how many customers each quarter started with. It is not the nine-month retention of the original 1,000; for that you follow those customers individually, which is cohort analysis.

The common mistake

Leaving new customers in the end count.

Q3 becomes 1,350 ÷ 1,150 = 117.4%. Q1 is 110.0% and Q2 is 104.5%. A retention rate above 100% says more customers stayed than you had, which is impossible, and that impossibility is the only reason this mistake ever gets noticed. When acquisition is weaker the same error produces a plausible-looking 90% and nobody questions it. The flattery is worst in the quarters where marketing did best, so a good quarter of acquisition reads as a good quarter of retention. Subtract the new customers, every period, and churn is then simply one minus the result (churn rate formula).

Keeping the inputs current

A scheduled Fresh Sheets report can keep your Shopify orders in their own tab, for example one called Shopify, with one row per order and its day. It carries no customer field, so the three counts, who ordered in the period before, who ordered in this one, and who appears in this one for the first time, come from Shopify's own orders export (Orders, then Export), whose Email column identifies the customer. Source: Shopify Help Centre, Exporting orders. That join lives on your own tab.

Fresh Sheets replaces the Shopify tab's contents on every refresh, so keep your formulas on a separate tab. This version finds the order date column by its heading, so it still works if you add a field and the columns move, and counts the orders between the dates in A2 and B2:

=COUNTIFS(INDEX(Shopify!A:Z, 0, MATCH("Day", Shopify!1:1, 0)), ">="&A2, INDEX(Shopify!A:Z, 0, MATCH("Day", Shopify!1:1, 0)), "<="&B2)

That gives orders per period, the denominator's cousin; the customer counts themselves come from the export.