Active Customer Count

We need a help with a certain metric, which is somewhat difficult to calculate. We almost have a need for a unique count in a moving window.

For instance, we need to calculate active customers for any given month. An active customer is defined as someone who has had a transaction in the last 12 months.

So, in Jan 2018, this count represents a unique count of customers who have done a transaction with us in the last 12 months.

In Feb 2018, this count represents a unique count of customers who have done a transaction with us in the last 12 months.

The transaction table has customer and transaction date information.

For example – Customer ABC did a transaction in Jan 2017 and did not do business with us after that. This customer needs to be counted as an active customer from Jan 2017 all the way up to Jan 2018 and then drops off after that.

What we are challenged with at the moment, is that the transaction table does not have transactions for this customer after Jan 2017. However, this customer needs to be accounted for in Feb 2017, Mar 2017, Apr 2017 etc.. all the way to Jan 2018.

Would love to hear any thoughts / recommendations on how to achieve this in a practical way. Unfortunately, ThoughtSpot does not have a moving count distinct function (has sum, min, max, avg).

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Sathish Rao ,

    Assuming transactions table contains the customer_id and the date_id on which the customer does a transaction. (Refer screenshots for the sample implementation)

    transactions(date_id, cust_id)

    dm_date(date_id, date_daily…… etc.)
     

    Note - Remember to make all the columns as ‘Attribute’ in the UI as you move along.

    1. You need to create a table from your date dimension table using CTAS which contains the date id, date, dummy field=1. Let call this table firs_dates. It will contain the first dates for every month; this is to keep track of the customers that have been active in a particular month.

      SELECT date_id, date_daily into firs_dates from dm_date where DAY(date_daily)=1;

      Here, date_daily will be a daily calendar date contained in the date dimension table. for eg - 01/01/2017.
       
    2. Add dummy columns with value 1 to your first_dates and the transactions table. This column will be used to select common values from the two tables in the next step.

      Alter table firs_dates add column dummy INTEGER default 1;

      Alter table transactions add column dummy INTEGER default 1;

       
    3. Create a table called ‘active_transactions’ using CTAS which contains a customer id and the months in which the customers will be active.
      active_transactions(cust_id, date_daily, date_id)

      SELECT transactions.cust_id, firs_dates.date_daily, firs_dates.date_id
      INTO active_transactions
      FROM
      transactions
      JOIN
      firs_dates ON transactions.dummy = firs_dates.dummy
      JOIN
      dm_date ON dm_date.date_id = transactions.date_i
      WHERE (startofmonth(dm_date.date_daily) = firs_dates.date_daily) OR (dm_date.date_daily < firs_dates.date_daily AND transactions.date_id+365 > firs_dates.date_id) ;

       
    4. Set Primary Keys on the newly created table.

      ALTER TABLE active_transactions SET DIMENSION;

      ALTER TABLE active_transactions ADD CONSTRAINT PRIMARY KEY("cust_id","date_id");

       
    5. Join the active_transaction table to the date dimension table using the date_id.
       
    6. Now we can open the Search tab, and start doing a select from the newly created active_transactions and the date dimension table table. You can look at the trends by quarter, year etc by selecting appropriate columns from the date dimension table.
       
    7. You will need to keep refreshing the active_transaction periodically. For this purpose you can put the query for ‘Upserting’ active_transactions in a crontab.

      INSERT into active_transactions
      SELECT transactions.cust_id, firs_dates.date_daily, firs_dates.date_id
      FROM
      transactions
      JOIN
      firs_dates ON transactions.dummy = firs_dates.dummy
      JOIN
      dm_date ON dm_date.date_id = transactions.date_id
      WHERE (startofmonth(dm_date.date_daily) = firs_dates.date_daily) OR (dm_date.date_daily < firs_dates.date_daily AND transactions.date_id+365 > firs_dates.date_id) ;


      If you have any questions, feel free to let me know.

      Thank you Siva Singaram for working out the solution.
    Like
Like Follow
  • Status Answered
  • 1 yr agoLast active
  • 1Replies
  • 645Views
  • 2 Following