Hello, I want to convert the transactional data below into a matrix to count the frequency of DIFFERENT days the customer made orders.
I'm basically trying to figure out, based on transactional data for 2 years. How many different days a customerID has made purchases.
For example, Customer ID 17850 has frequency of 1 in the data set below (it purchased different items during that day as you can see in the transactional data).
I'm trying TABULATE, but not sure how to come up with rows of single customerIDs and the frequency (based on unique days).
Example of Tabulation outcome (based on unique dates, not on ROWS!):
| Customer ID | Frequency |
| 17850 | 45 |
| 13047 | 30 |
| … | … |
PS: I attach the excell with transactional data
| InvoiceNo | StockCode | Description | Quantity | InvoiceDate | UnitPrice | CustomerID | Country |
| 536365 | 85123A | WHITE HANGING HEART T-LIGHT HOLDER | 6 | 1/12/2010 8:26 | 2.55 | 17850 | United Kingdom |
| 536365 | 71053 | WHITE METAL LANTERN | 6 | 1/12/2010 8:26 | 3.39 | 17850 | United Kingdom |
| 536365 | 84406B | CREAM CUPID HEARTS COAT HANGER | 8 | 1/12/2010 8:26 | 2.75 | 17850 | United Kingdom |
| 536365 | 84029G | KNITTED UNION FLAG HOT WATER BOTTLE | 6 | 1/12/2010 8:26 | 3.39 | 17850 | United Kingdom |
| 536365 | 84029E | RED WOOLLY HOTTIE WHITE HEART. | 6 | 1/12/2010 8:26 | 3.39 | 17850 | United Kingdom |
| 536365 | 22752 | SET 7 BABUSHKA NESTING BOXES | 2 | 1/12/2010 8:26 | 7.65 | 17850 | United Kingdom |
| 536365 | 21730 | GLASS STAR FROSTED T-LIGHT HOLDER | 6 | 1/12/2010 8:26 | 4.25 | 17850 | United Kingdom |
| 536366 | 22633 | HAND WARMER UNION JACK | 6 | 1/12/2010 8:28 | 1.85 | 17850 | United Kingdom |
| 536366 | 22632 | HAND WARMER RED POLKA DOT | 6 | 1/12/2010 8:28 | 1.85 | 17850 | United Kingdom |
| 536367 | 84879 | ASSORTED COLOUR BIRD ORNAMENT | 32 | 1/12/2010 8:34 | 1.69 | 13047 | United Kingdom |
| 536367 | 22745 | POPPY'S PLAYHOUSE BEDROOM | 6 | 1/12/2010 8:34 | 2.1 | 13047 | United Kingdom |
| 536367 | 22748 | POPPY'S PLAYHOUSE KITCHEN | 6 | 1/12/2010 8:34 | 2.1 | 13047 | United Kingdom |
| 536367 | 22749 | FELTCRAFT PRINCESS CHARLOTTE DOLL | 8 | 1/12/2010 8:34 | 3.75 | 13047 | United Kingdom |
| 536367 | 22310 | IVORY KNITTED MUG COSY | 6 | 1/12/2010 8:34 | 1.65 | 13047 | United Kingdom |
| 536367 | 84969 | BOX OF 6 ASSORTED COLOUR TEASPOONS | 6 | 1/12/2010 8:34 | 4.25 | 13047 | United Kingdom |
| 536367 | 22623 | BOX OF VINTAGE JIGSAW BLOCKS | 3 | 1/12/2010 8:34 | 4.95 | 13047 | United Kingdom |
| 536367 | 22622 | BOX OF VINTAGE ALPHABET BLOCKS | 2 | 1/12/2010 8:34 | 9.95 | 13047 | United Kingdom |
| 536367 | 21754 | HOME BUILDING BLOCK WORD | 3 | 1/12/2010 8:34 | 5.95 | 13047 | United Kingdom |
| 536367 | 21755 | LOVE BUILDING BLOCK WORD | 3 | 1/12/2010 8:34 | 5.95 | 13047 | United Kingdom |
| 536367 | 21777 | RECIPE BOX WITH METAL HEART | 4 | 1/12/2010 8:34 | 7.95 | 13047 | United Kingdom |
| 536367 | 48187 | DOORMAT NEW ENGLAND | 4 | 1/12/2010 8:34 | 7.95 | 13047 | United Kingdom |
| 536368 | 22960 | JAM MAKING SET WITH JARS | 6 | 1/12/2010 8:34 | 4.25 | 13047 | United Kingdom |
| 536368 | 22913 | RED COAT RACK PARIS FASHION | 3 | 1/12/2010 8:34 | 4.95 | 13047 | United Kingdom |
| 536368 | 22912 | YELLOW COAT RACK PARIS FASHION | 3 | 1/12/2010 8:34 | 4.95 | 13047 | United Kingdom |
| 536368 | 22914 | BLUE COAT RACK PARIS FASHION | 3 | 1/12/2010 8:34 | 4.95 | 13047 | United Kingdom |
| 536369 | 21756 | BATH BUILDING BLOCK WORD | 3 | 1/12/2010 8:35 | 5.95 | 13047 | United Kingdom |
| 536370 | 22728 | ALARM CLOCK BAKELIKE PINK | 24 | 1/12/2010 8:45 | 3.75 | 12583 | France |
| 536370 | 22727 | ALARM CLOCK BAKELIKE RED | 24 | 1/12/2010 8:45 | 3.75 | 12583 | France |
| 536370 | 22726 | ALARM CLOCK BAKELIKE GREEN | 12 | 1/12/2010 8:45 | 3.75 | 12583 | France |
| 536370 | 21724 | PANDA AND BUNNIES STICKER SHEET | 12 | 1/12/2010 8:45 | 0.85 | 12583 | France |
| 536370 | 21883 | STARS GIFT TAPE | 24 | 1/12/2010 8:45 | 0.65 | 12583 | France |
An alternative way to do this. First, as Jim says, change the Customer ID (either to Nominal or to Ordinal). Create a new date column by changing the current InvoiceDate to a Character Nominal variable. Create a new column for date by using the Left (Invoice Date,10) - this strips away the time. Then use Tabulate, with Customer ID followed by the new date column (both as rows). At the red arrow, Make into Data Table.
Then, create a new column with the formula Column Number (date, by Customer ID). This gives the number of distinct days each customer placed orders.
The solution to your question is very simple. You can easily do this interactively or as a script. These steps are all found in the introductory JMP documentation, which I suggest you take the time to read. It will give you a good overview on the different components available.
Help==>Books==>Using JMP
Interactive:
1. Open the Excel file from JMP
File==>Open
2. The CustomerID opens as a Numeric/Continuous column. It needs to be an Ordinal value.
3. The InvoiceDate column is formatted to have the Time Part also displayed. You want to deal with InvoiceDate as separate days, not times within days, so change open the Column Info for that column and change the format to "d/m/y"
4. To summarize the data to one row per CustomerID/InvoiceDate, run
Tables==>Summary
Put both CustomerID and InvoiceDate into the Group selection and Click OK
5. Now to Summarize down to one row per CustomerID, run the Summary Platform again, against the newly created table
Tables==>Summary
Placing the CustomerID column into the Group selection, and Clicking on OK
The resulting table will have your requested values
The script below, was basically generated by running the above steps interactively and then just saving the scripts for each step into a script window. I wrote only a minor part of the code below. JMP generated it.
names default to here(1);
// Open the Excel file
dt=Open(
// Change the below to the path to the Excel File
"<PATH to="" folder="">\Online Retail.xlsx");
// The CustomerID should not be treated as a continuous,
// analytical column of data, so
// Change CustomerID to an Ordinal Modeling Type
dt:CustomerID<<MODELING type="">Tabulate
// which will allow for the Calculation of the
// counts of the number of different InvoiceDates
dtSum1 << Tabulate(
Show Control Panel( 0 ),
Include missing for grouping columns( 1 ),
Add Table(
Column Table( Analysis Columns( :InvoiceDate ), Statistics( N ) ),
Row Table( Grouping Columns( :CustomerID ) )
)
);
// Or
// Run the Summary Platform again, but this time run it on
// the current Summarized data table to count the number of
// rows for each CustomerID
dtSum2 = dtSum1 << Summary(
Group( :CustomerID ),
Freq( "None" ),
Weight( "None" ),
Link to original data table( 0 )
);
// Now Clean up the N Rows name to # of Invoiced Days
dtSum2:N Rows << set name("# of Invoiced Days");</MODELING></PATH>
