I'm reopening this as there seems to be an issue: in the packaged twbx that you provided, you'll notice that the "Worst Customer" segment grows for the first month or so, then the number is the same. That doesn't seem right, as it should be growing in count at least. Do you have any insight as to why?
Can you create a similar chart to what I showed above, filtering on that value (and removing the running sum)? I'd like to see how that looks. I'd expect that there is no data after July.
Also, I want to tell you something about this analysis: Customer A may appear as a "Worst Customer" in June 2019, but then may become a "Potential Loyalist" the next month depending on their purchasing behavior. So the goal here really is to ensure that Tableau allows for a free-flowing ranking of each RFM score. Customer A can be a 111 or a 333 or a 514 or a 555, and I need all 4 of those to be shown in their respective month. Not sure if this helps or not.
The above doesn't seem to show the "worst customer"
Yea now I'm even more confused.. all I did was filter on the date to include only June and July 2019, then removed the running total. I did the filter and kept the running total and still didn't show the "worst customer" segment. Hm
Can you filter on rfm_combined and only show "Worst Customer"?
Any chance you can provide this more complete workbook?
Attached another workbook with everyone! Labeled it COMPLETE RFM_Analysis_Tableau5
So there 100% is data for worst customers, which is why this has got me baffled. I checked my data warehouse, and its pulling "worst customers" for every single YearMonth. Yet for some reason, it is not appearing here..
Ah, I see. Let's see if we can break it apart to determine what's going on. I see that "Worst Customer" is from rfm_combined (group), which is a group based on rfm_combined. The "Worst Customer" group includes the following values:
Which of these do/do not have data in the warehouse after July 2018?
Just checked the data for each month after July 2018, I'm up to Dec 2018 and each of the months in between (including Dec 2018) have RFM scores of at least 111, meaning they all seem to have worst customers in that particular range.
Here's a screenshot:
Here's the SQL code being used, if it helps:
SUBSTRING(rfm_combined,1,1) AS recency_score,
SUBSTRING(rfm_combined,2,1) AS frequency_score,
SUBSTRING(rfm_combined,3,1) AS monetary_score
rfm_recency*100 + rfm_frequency*10 + rfm_monetary AS rfm_combined
ntile(5) over (order by last_order_date) AS rfm_recency,
ntile(5) over (order by count_order) AS rfm_frequency,
ntile(5) over (order by total_spent) AS rfm_monetary
to_char(oms_order_date, 'YYYY-MM') AS YearMonth,
MAX(oms_order_date) AS last_order_date,
COUNT(*) AS count_order,
SUM(quantity_ordered * unit_price_amount) AS total_spent
WHERE order_type NOT IN ('Sales Return', 'Sales Price Adjustment')
AND item_description_1 NOT IN ('freight', 'FREIGHT', 'Freight')
AND line_status NOT IN ('CANCELLED', 'HOLD')
AND oms_order_date BETWEEN '2018-12-01' AND '2018-12-31'
AND customer_id NOT IN ('')-- and yearmonth = '2019-01'
--and customer_id = 'US621111112202327'
GROUP BY customer_id, to_char(oms_order_date, 'YYYY-MM')))
ORDER BY customer_id desc, yearmonth