Jump to content

Determining rising customer order value


This topic is 1034 days old. Please don't post here. Open a new topic instead.

Recommended Posts

It's the first time in a while that I've had something to really get my teeth into with Filemaker. 

I'm needing to run some analysis on an invoicing database. The basic structure of the database is pretty much as you'd expect with a table each for customers, orders, and line items on these orders. 

 

This is essentially the conversation that gave rise to this task:

"Small orders don't bring in much money and are a lot of hassle. Let's impose a minimum order limit or find ways of discouraging these kinds of orders"

"Ah, but customers start off with small orders, and come back to us with larger ones later"

 

So what I'm essentially trying to find is, I guess, a way of identifying a trend. I was wondering about comparing a customer's average order size with their first order. That seems like it might be straightforward to implement but I'm not sure if there are better ways to represent the trend. Also I'm thinking that percentages won't be much use here, someone starting with a $10 order and coming back with a $30 order would be a 200% increase but both would be in the "too small to be worth the hassle" bracket. The hope is that there are people starting with $10 orders who have gone on to spend hundreds or thousands. 

 

I am a complete novice when it comes to statistics, so I want to make sure I'm not inadvertently skewing the picture. We have roughly 2000 customers to get through, so I'd far prefer something that isn't the manual approach. Any recommendations for how best to tackle it?

Link to comment
Share on other sites

Couldn't you simply chart this by customer, then look at each graph, unless there are thousands of customers.

Seems like an odd request, to discourage small orders.  Anything can be made profitable.  Amazon will ship something that costs $1.

As a business owner, I had a simple job for a homeowner turn into him giving me all of his business for his company.  Had I turned down the small job, I never would've got the big one.

Link to comment
Share on other sites

Thanks Steve - you make a good point. We've got about 2000 customers to consider, if we take only orders from the past 2-3yrs. 

I could arrange some kind of report listing by customer with the totals of all their orders arranged, perhaps with some conditional formatting to indicate whether each subsequent order is bigger or smaller than the baseline. I suspect it'll be a bit difficult to comprehend at a glance, though.

The sort of finding that would be useful is if I can say along the lines of "40% of customers who place a <$50 order go on to place a >$100 order within the next year". 

From a business perspective, the problem is that quite a lot of decisions about premises and staffing are being dictated by the need to service these very small orders. They'd be tolerable as "loss leaders" but not if that's all those customers ever spend. Retooling to handle more volume of small orders would be possible, but it's hard to justify the spend and effort when there's other business to chase that's much more profitable. 

The situation you had with the homeowner is a good example of how the process should work. I'm guessing you made a good impression and did the job well. Crucially, the homeowner had other work needing done in future. What I'm wondering is if the customers placing <$50 orders are never going to order anything larger. It'd be like doing lots of bitty maintenance jobs in the hope of being asked back for a major remodel, only to find that the customers are tenants rather than homeowners and only likely to ever pay for the bare minimum. 

(Amazon, BTW, barely break even on their retail operations, it's web services that bring in the profits. I suspect shipping even the smallest orders is part of the process of training customers to go straight to the Amazon site without even considering anywhere else)

 

Link to comment
Share on other sites

I think you need to break this question in two: (1) what to do, and (2) how to do it in Filemaker.

The first question has nothing to do with Filemaker or even with statistics. I believe it is purely a business management question and it would be presumptuous of me to offer any advice in that area (as my bank would be quick to confirm...).  Once you figure it out, we could move to the second question which is more appropriate to discuss in this forum.

 

47 minutes ago, Angus McKinnon said:

What I'm wondering is if the customers placing <$50 orders are never going to order anything larger.

That would be actually easy to find out by comparing their first order with their maximal order. And it would be also easy to find out the percentage of customers  that have moved from a <$50 first order to a >$500 order. Whether it has any meaning is again a question I don't feel qualified to answer.

 

Link to comment
Share on other sites

I've chewed over this a bit more over the past few days. (And done a bit of reading around on KPIs and other business metrics.)

The simplest way I think the query can be expressed is by a table/spreadsheet style layout. Each row is a customer, and their order totals appear in the columns. This can obviously be read and interrogated line by line, but I'm thinking if I apply a little conditional formatting on each cell, it'll look like a "heat map" of sorts if I zoom out far enough. 

I'm a little stuck on how to do the calculations behind the layout. I can't run portal rows horizontally. I could use the List command to return a list of related order records, but that'll give me a single text result, rather than the individual totals that I could apply conditional formatting to. Possibly populate a repeating field with the related data?

Link to comment
Share on other sites

14 minutes ago, Angus McKinnon said:

Possibly populate a repeating field with the related data?

That is possible - but even with a repeating field, the number of columns must be fixed and set in advance. You could provide a mechanism to scroll horizontally, but I'm not  sure how effective this would be when not all customers have the same (or perhaps even comparable) number of orders. But if you can squeeze all the columns you want to show inside a single screen, this is doable - though with ~2k customers with ?? orders each, it may not be very fast.

Another option you may consider is using a web viewer to produce this display.

How often do you need this? It sounds like this is a one-time study. Perhaps you could simply export your data and do the analysis in Excel or another tool.

 

Link to comment
Share on other sites

You're right - it's not quite a one-off, but not the sort of thing that's going to be rolled out on a weekly or monthly basis. 

The number of orders does vary quite wildly between customers. We have a few outliers in the 100-500 range, most are under 50. However for the purposes of this report I think everyone would be quite content with the first ten orders each customer makes, by that point the pattern has been established. (We can also use the average order value as a comparison, that will take into account any dramatic changes outwith the range of ten)

Speed isn't too much of a worry, I can always leave it to crunch and export a PDF for viewing.  

Link to comment
Share on other sites

48 minutes ago, Angus McKinnon said:

for the purposes of this report I think everyone would be quite content with the first ten orders each customer makes

Well, if you define a calculation field in the Customers table as = 

Let ( 
totals = List ( Orders::TotalAmount )
;
GetValue ( totals ; Get ( CalculationRepetitionNumber ) )
)

and set it to have 10 repetitions, you should get what you described.

 

  • Like 1
Link to comment
Share on other sites

That's worked perfectly, although the full layout has certainly got my computer working hard!

There's a few variations that I can do now that the basic structure is sound, it looks like it's going to be very useful for the analysis we're planning. 

The "heatmap" effect does work. It's not particularly accurate because I've only got a few states for the conditional formatting, but zoomed out on a 4k monitor it gives a pretty good impression of the general trend. 

Link to comment
Share on other sites

This topic is 1034 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.