Customer Status Definition

[Former Member]

Hi, hope someone can help with these and apologies if this has come up before.  I should stress that I am relatively new to BusinessObjects.

A key dimension for our marketing department is customer status - how many new customers bought something in our online shop over a particular period of time, how many new customers returned in this period, and how many were existing customers.  To this end, I have created a derived table in the data foundation called customer status, as follows:

SELECT uni_usr_id,

Customercode,

CASE

WHEN Customercode = 0 THEN 'New: Unique Order'

WHEN Customercode = 1 THEN 'New: Multi Orders'

WHEN Customercode = 2 THEN 'Existing Customer'

ELSE 'Error'

END AS Customerstatus

FROM

(SELECT uni_usr_id,

            CASE

            WHEN min_prev = 0 AND no_ords = 1 THEN 0

            WHEN min_prev = 0 AND no_ords > 1  THEN 1

            WHEN min_prev > 0 THEN 2

            ELSE 3

            END AS Customercode

FROM

(

SELECT

    uni_usr_id, min(prev_orders) AS min_prev, count(DISTINCT order_id) AS no_ords

FROM

fact_orders

WHERE fact_orders.order_date BETWEEN  @Prompt(From_date)  AND @Prompt(To_date)

GROUP BY uni_usr_id) a

) b

ORDER BY Customercode

;

prev_orders is just a field in the orders table, starting at 0 for a customer's first order.  I hope the rest is self-explanatory.  This derived table is connected to the main fact table via uni_usr_id, and the prompt dates are run once for both tables.  In most circumstances this works very well.

However, if a report user wants to specify something regarding the order - for example, minimum order spend - then it gets trickier.  Of course, you can do this for the actual orders with a sub-query, but this will not affect the calculation of status.  So for example, if one customer had 4 orders, and only 2 of them were above €20, then the main report would just include the €20, but the calculation of status would pertain to all customer orders.

Does anyone know of a smarter way to make this kind of report more dynamic, therefore, other than adding a lot of filters to the script of the derived table?  I can also replicate the calculation of status via a report variable, but the problem there is that the variable only works at the level of the individual user, and does not seem to work at the summary level.

Hope this makes sense, very happy to provide more information if required.  Thanks

[Former Member · Accepted solution]

I'd take customer code back to the database level.

Add it as a new column in the customer dimension and introduce some ETL code to update it overnight or however often new orders are loaded into your database. There's way too much overhead on queries for this code to be run every time you want to report on such a commonly used object.

[Former Member · Accepted solution]

Hi Mark, thanks very much for the response.  This is something I have suggested already - having current customer status as a fixed value, run on order history at time of the overnight ETL.  I will probably do something like this anyway fairly soon as it will help in planning new campaigns.  Something like new, active, lapsed, deeply lapsed, premium etc. etc.  No problem in the ETL.

However, the issue here is that the requirement is also for historical information.  So we want to compare how many new, new & returning, and existing customers there were for a particular period, versus the same time frame the previous year.  The BusObj user would of course need to run the report again for this.  This is what they currently do, the fiddly part is as I say filtering at the individual order level.  So for example, they want to know how many new, new & returning, and existing customers, spending €20 per order, that we had ordering in the first quarter of 2013, versus first quarter of 2012.  And order counts, value etc.

We could of course compare customer counts and order counts, getting average order value per customer, but it still doesn't give us quite what we need here.  We could also just keep the historical information static but end users generally want to interact with it in some way, like filtering by country etc.

I agree with what you say about the overhead for the queries - nevertheless I've been pleasantly surprised with the performance.  End users are happy with this as well.

Of course, this is all with SQL, and I wonder whether MDX might provide more flexibility here.

[Former Member · Accepted solution]

The other option is to make the Customer Dimension a slowly changing dimension and track changes in customer status. If you can back date this then retag your surrogate keys in your dim then in your fact. Not sure how much time you've been allowed to do this but that's one option.

I've done similar to what you're after with historic statuses and it can be messy but like you say, as long as it's performing well enough, it shouldn't be a problem.

[Former Member · Accepted solution]

Hi Mark

I'd been thinking along these lines as well.  A solution like this would be great, and time is not an issue, but the customer status is all to do with the relationship between the orders in a user-specified period of time, which always changes.

So end users want to know over a certain period of time how many users were new, and which proportion returned to us. 

One possible solution would be to say that end users can only report on such information via set periods such as quarters - then your suggestion would be ideal, since I could run that calculation in the ETL.  I guess going down to the month level then makes the whole thing too unwieldy.

Up until now the derived table has worked well - just when filtering by actual order value do things get tricky.  As I said to , I could just add an order value prompt to the derived table and the fact table, so that it replicates my SQL here:

SELECT

CASE WHEN min_prev = 0 AND ords = 1 THEN 0

WHEN min_prev = 0 AND ords > 1 THEN 1

ELSE 2 END AS KS,

uni_usr_id

FROM

(SELECT uni_usr_id, min(prev_orders) min_prev, count(DISTINCT order_id) ords, sum(`netto_eur`) netto_eur

FROM

fact_orders fo

WHERE fo.order_date BETWEEN '2012-01-01' AND '2012-03-31'

AND EXISTS

(SELECT fo2.order_id, sum(netto_eur) net_eur

FROM fact_orders fo2

WHERE fo.order_id = fo2.order_id

GROUP BY fo2.order_id

HAVING net_eur >= 20)

GROUP BY uni_usr_id) a

ORDER BY KS

;

The advantage of BusinessObjects here is this prompt only appears to the user once.

Cheers

Richard

[Former Member · Accepted solution]

Another option is to see what time intervals they would want. I'm thinking along the lines of a monthly and/or weekly snapshot table. Every Friday, say, and the first day of every month. Then your queries would perform brilliantly, you could use aggregate awareness and they could still dip into the main fact for a longer-running version if they wanted, for example, as at Wednesday 11th December

[Former Member · Accepted solution]

Hi Mark

I had something similar in mind when I mentioned performing this calculation by quarters, and just running it in the ETL.  However, I hadn't thought about using separate snapshot tables.  That may well be something I might consider in the future, but unfortunately this still doesn't solve the issue of filtering on the order value. 

Overall, the individual customer has a status, but if we want to calculate this by minimum order spend, which will be an arbitrary user-input amount, then this calculation changes.

Many thanks

Richard

[Former Member]

I must admit this is a interesting report requirement I have seen in SCN place for a while..

The way I would have gone if I were you is to create a additional query just to get the users who satisfy the criteria of the sub query and then pass the user id in through the query panel to the main query to retrieve the data from.. If the reporting sub query I will a optional prompt for the  minimum amount spend with a default value..

Now this will produce the result set for all the users who satisfy your criteria to play with your report..

Hope this approach helps..

[Former Member]

Thanks a lot for the helpful response Durgamadhab.  The problem with the user sub-query is that whilst you are getting the right people (those with at least one order over a certain value), you still can't filter on the individual orders for this status calculation.  So what I might do, as you suggest, is to add a filter to this sub-query in the derived table.  Many thanks.

[Former Member]

Hi Richard

Can you explain what exactly you mean "you still can't filter on the individual orders for this status calculation"

Can not you use the main query or an additional query for this criteria? The sub query will only to get the right people selected in the query..

[Former Member]

Hi

Yes, this is what I do at the moment - the orders in the main query are filtered by a sub-query, see attached file.  This part works fine, but the derived table calculating status just returns the user id plus status for whichever period is chosen.  So although the main query can filter out orders below a certain value, these orders will all be included in the status calculation.

So if a customer had 3 orders in a period of time, and only one was > €20, if this report filters on €20 or above then you'll only get one order included in the output, but the status calculation will run on all 3 orders, categorising them as 'New: Multi Orders', rather than 'New: Unique Order', which in this case they should be.

Therefore the people involved will be correct, and the numbers of orders, just not their status.

What would work I guess would be to add a prompt to the derived table and the fact table, which is what I did with the date prompts - I just thought there might be a more elegant way of providing the answer.

Cheers

Richard