Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Power Hely]

Hello all! I’m hoping the crowdsourcing of a present problem to wiser minds than mine might help, because I am stumped.

Our Sales side launched a marketing initiative with the sales force, creatively called the “AND1” initiative. The goal is for the salespeople to attempt to get each of their regular customers to add a product family to their orders which the customer had not previously purchased. This marketing campaign started at the middle of July 2017.  The Sales management now wants to see how successful that campaign has been.

I’ve been able to identify the first instances of any “AND1”-qualifying sales, namely where the first time a customer has purchased a product from a product family is within the marketing campaign. I did it using a measure, and a filter on the visual such that Invoice_Date is on or after 15 July 2017:

NewPurchase =
var NewProduct=CALCULATE(MIN(‘Std Sales'[Invoice_Date]),ALLEXCEPT(‘Std Sales’,’Std Sales'[Product_Family], ‘Std Sales'[CUSTNMBR]))
return
CALCULATE(COUNTROWS(‘Std Sales’),FILTER(‘Std Sales’,’Std Sales'[Invoice_Date]<=NewProduct)) 

I’m now looking to generate a table or matrix showing, aggregated on a per-Salesperson basis, the gross revenue of sales of items to customers who had not purchased items in that item family prior to 15 July 2017. My measure above will help me identify first sales, and thus product family-customer matches which would qualify, but it doesn’t help me sum up any subsequent sales of those products to those customers, which also need to pulled into the valuation of the program. And that’s what I can’t wrap my head around – how can I sum up the gross sales value of not only such first sales that occurred after 15 July 2017, but all subsequent sales of those particular customer-product family matches that occurred from 15 July 2017 to the present? I frankly can’t even think of where to begin.

Any help any of you could provide would be immeasurably appreciated. Even if you can only steer me in the right direction, it’s better than where I am right now.

Best,

Power

——————————
J. Power Hely
Director of Operations
Hely & Weber
Santa Paula, CA & DFW Airport, TX
phely@hely-weber.com
800-654-3241
——————————

[Val Gameiro]

I’m not sure which ERP you’re using, but in NAV we have a field to tag sales with campaigns. I’m assuming your sales order do not have that, and you’re having to calculate everything on your own.

It sounds like what you’re trying to do is, for a given customer:

1. Determine all items that were sold after July 15th (maybe go through all sales invoice lines?)
2. For each of those items, determine first date of purchase, and increment (or stuff into a temp table) your count if it is >= July 15th
3. Be sure and exclude duplicate items (if item already exists in Temp table do not insert)

Does that seem feasible? I’m very rusty on my SQL, and I don’t know the structure of your ERP.

In NAV I can FINDFIRST on the Item Ledger Entry table to get the first sale of a given item, and pull the Posting Date from there. I filter by Customer No. and no Posting Date. This gives me the date of first purchase of that item for that customer.

For the other part, I would run the same table, but filter on Customer No. and Posting Date >= July 15th and for Type = Sales. That would give me a list of all purchases against which I can look for first time buys.

If true, and record doesn’t already exist in TEMP table, then insert new record.

Hope that helps

——————————
Val Gameiro
Senior NAV Analyst
BPL Plasma Inc.
Austin TX
——————————
——————————————-

[Michael Swisher]

Hi John,
I would tackle your question from a different angle.  I would identify each customer with the attribute you’re looking for, along with characteristics that would help you analyze the sales for each group as well as in aggregate.  The steps I’d take are:

1. Classify each customer in the customer reference file accordingly by comparing their post campaign purchasing to their pre-campaign purchasing. This will give you a set of customers who purchased an additional product family and those who did not.  You can then filter on the two groups in a matrix.
2. When you identify those that added a product family, record the product family they added, and the date they added it.  This will allow for your additional sales calculation after the date the first additional sales occurred.

I hope that’s enough to get you started, but I believe you should be able to get to your final objective.  From your example, I don’t think you’ll have any problem with the code for this.

After you finish your analysis, you can start thinking about classifying all your customers moving forward by what they purchase, how frequently, and create a metric to identify customer importance.  This will lead to a lot more information you can provide to sales that will help them come up with new programs and evaluate the programs more easily.

Please let me know if you have any questions, or if you find this helpful.  I’d be happy to discuss it further if you like.
Best,
Michael

——————————
Michael Swisher
VP Analytics
Foresight ROI
Chicago IL
312.635.1061
——————————
——————————————-