mysql-Calculate the current month reactivate customer _20161013

No. 13th, draft 12th, will be updated tomorrow, too busy recently.

Customer retention is a measure of customer value often used to reflect the level of customer activity, in the Internet enterprises, especially now mobile phone traffic has exceeded the PC-side traffic, on the Android and iOS devices online time of the data, e-commerce app products, the next single frequency can reflect the customer's active situation.

Recently the company sales staff also need some data to investigate the sales staff in the month's new development customers and reactivate customers.

Here we define the rule is the month under the slip customer for active customers, activate the customer to order in this month, the last place orders the next month to order more than 30 days (today changed 2 times the code and customer operations colleagues to communicate know that they are at this interval to define the customer's new activation retention properties, before the monthly report is a month in months , so the data before the communication is very necessary, statistical caliber is not the same, the result is mutual mulberry damage (⊙o⊙))

That's what I think.

considering the database data update until yesterday, at the beginning of the month of 1th, the data is actually last month's complete data, the new January is actually no data, so the 1th data is still calculated last month, 2nd is the calculation of the new January, so the month is referring to the month except the monthly number 1th, the other is the current month

The #DATE_FORMAT (Date_add current_date,interval-1 Day) is to solve the problem that the monthly data is empty for the month of 1th, which appears in January.

1. If you want to generate a field in the table for a retained property to determine whether the customer is a new customer in the current month , or to retain the customer or reactivate the customer, you need to first adjust the customer's daily details of the current month to find the date of the first day of the user's order in the month, 30 days forward on this date) the following is the code

SELECTA2. User Id,a2. Order Date asThe first day of the month, A2. Sales clerk, A2. Order Amount asthe first single amount of the month in the first day of the month, the month's first single salesperson, the month's first single amount from (    SELECTA1.username asUser Id,a1.order_date asOrder Date, A1.salesperson asSales clerk,SUM(A1.pay_money) asOrder Amount # Every day of the month details fromTest_a03order asA1WHEREA1.city="City_a" andDate_format (A1.order_date, "%Y%M ")=Date_format (Date_add (current_date, INTERVAL-1  Day),"%Y%m ")GROUP  bya1.username,a1.order_date) asA2GROUP  byA2. User ID

2, the above code to the month of the order of the customer list to find out the user does not repeat, a row of a record we generated the above Code field the first day of the month and then use the LEFT JOIN order table to determine user attributes

SELECTA3. User Id,a3. First day of the month, A3. Salesman,SUM(IF(a4.order_date<Date_add (A3. First day of the month, INTERVAL- -  Day), Pay_money,NULL)) asOrder Amount before 30 days before the first day of the monthSUM(IF(a4.order_date>=Date_add (A3. First day of the month, INTERVAL- -  Day) andA4.order_date<A3. First day of the month, Pay_money,NULL)) asOrder Amount for the first 30 days of the monthSUM(IF(a4.order_date<A3. First day of the month, Pay_money,NULL)) asTotal order Amount before the first day of the month, A3. The first single amount of the month, Case  when SUM(IF(a4.order_date<A3. First day of the month, Pay_money,NULL)) is NULL  Then"New" #x1 when SUM(IF(a4.order_date>=Date_add (A3. First day of the month, INTERVAL- -  Day) andA4.order_date<A3. First day of the month, Pay_money,NULL)) is  not NULL  Then"retained" #x2 when SUM(IF(a4.order_date<Date_add (A3. First day of the month, INTERVAL- -  Day), Pay_money,NULL)) is  not NULL  and SUM(IF(a4.order_date>=Date_add (A3. First day of the month, INTERVAL- -  Day) andA4.order_date<A3. First day of the month, Pay_money,NULL)) is  NULL  Then"Reactivate" #x3ELSE NULL ENDRetention Conditions from (    SELECTA2. User Id,a2. Order Date asThe first day of the month, A2. Sales clerk, A2. Order Amount asthe first single amount of the month in the first day of the month, the month's first single salesperson, the month's first single amount from (        SELECTA1.username asUser Id,a1.order_date asOrder Date, A1.salesperson asSales clerk,SUM(A1.pay_money) asOrder Amount # Every day of the month details fromTest_a03order asA1WHEREA1.city="City_a" andDate_format (A1.order_date, "%Y%M ")=Date_format (Date_add (current_date, INTERVAL-1  Day),"%Y%m ")GROUP  bya1.username,a1.order_date) asA2GROUP  byA2. User ID) asA3 Left JOINTest_a03order asA4#a3 is the user to place orders in the same month to find these users in the entire order form order situation to A3 the first day of the month forward calculation 30 days to calculate the retention of new propertiesGROUP  byA3. User ID

Interpretation of annotations in code

#x1 the first day of the month, it's a new customer.

#x2说明在当月首单日之前30天下过单 the first day of the month is less than 30 days from the last time that you left it. This is a retained customer.

#x3说明在首单日前30天再之前下过单 has not been activated for a single re-activation within 30 days from the first day of the month

In essence, the problem of the interval judgment in mathematics interval point contains not to communicate with the operator to solve

The interval nodes were 60 before the

<=60, this section is a zone.

(60,90) for B interval

=90 is C interval

<90 for the D interval represents the entire range of A and B

so the transition to interval judgment is first The C-zone must represent the first -day amount of the month.

Added: D Interval amount is empty, C is not empty so new order

Retained: B Interval amount is not empty then retained

Re-activation: A interval amount is not empty, B interval is empty, c interval is not empty then re-activated

mysql-Calculate the current month reactivate customer _20161013