Reward Tracking and Product Recalls

booth branding business buy
Photo by Pixabay on Pexels.com

Stores like for us as consumers to give them a customer ID to track what it is that we are buying. Many have phone number or a card or an email address. They use this information to track our purchases and personalize their nudges for us to buy products.  There is one way they might improve customer loyalty: Recall notices.

I pay attention to the news, so I see recalls every week. But, I doubt I am seeing them all. And, I doubt that I can reliably say whether I have the recalled item. But, the store where I bought it probably does.

A couple years ago, I was in a grocery aisle mulling over what to select when a manager came through to take off the shelf something nearby. He had a scanner which told him the information about the recalled item.

What would be really cool is if the system that is telling the stores what to pull from their shelves, looks through the customer purchases and informs the customers. They could pass along the recall notice and let the customer identify the lot number the same as the store. (I knew the manager was working a recall notice because he was talking to himself.)

Thinking maybe this already exists as an opt-in, I checked the stores where we have web site accounts. Nothing. (Given these places tend to go with an opt-out model, I was not surprised.)

Stores Tracking Me Could Be More Helpful

I know the stores track my purchases. They have tons of data on each of us. Their apps and rewards cards are precisely for knowing who I am and tracking me.

The other day, my girlfriend asked me to buy something using her rewards card to push it over the $1 she was short to get the reward for that month . (It is one of those you have to cross the threshold within the month or you lose the points.) There was a small temptation to mess with that data by buying something she’d never normally get. Instead, I bought something she would normally buy. I also paid in cash to keep my card number from being associated with her.

I just feel these companies with apps could be doing things to enable me to spend more in their stores.

  1. Their profile knows my purchasing frequency. They should be able to predict fairly well when my next purchase ought to happen. For items that happen monthly or less frequent, they could send me an email or app notification reminder. This value-add to the service would earn my loyalty in buying from them for helping me remember.
    • Of course, if they get it routinely wrong and alert me after I’ve already bought it from them, then I will be so offended that I would look for alternatives.
  2. Their profile knows how much I am willing to pay for specific items. They should be able to predict for which items I am willing pay full versus only sale prices. Then notify me when the items I buy for sale prices are available at close to the cost I am willing to pay.
    • Naturally, if they want to keep quiet when the item is significantly lower than what I am willing to pay, then I abstractly understand. That means in the moment of figuring it out, I would be hurt but as long as it is just a concept the decision makes sense.
    • They could also offer to let me set a price threshold for alerts when the item is offered for less than that amount. That would be useful pricing data for them.

 

Ad Fails

An advertisement for a Porsche plug-in hybrid really fails. First, Porsche was old and lame by high school. Lotus, Lamborghini, Ferrari, and so many other car companies come ahead. Second, I do not have a job where an ostentatious car helps me. Third, I cannot keep my mobile phone properly charged. A plug-in hybrid is not the car for me.

Given how much activity I have online and all the tracking data collected about that activity, I feel that advertisements delivered to me ought to be fantastic. There should only be advertisements delivered on the pages I visit that confirm my desires or make me suddenly desire it.

Certainly looking up this car put plenty of data out there supporting the advertiser’s algorithms pushing this ad at me. Probably I will see more of it. Perhaps it is better, though, than the ads of the last item I checked out on Amazon. Reminding me that I did not buy it probably will not trick me into actually buying it.

UPDATE: Perhaps the ad had more to do with the page I visited than data about me? It was a piece critical of the Chegg IPO by comparing it Twitter as a success. I visited it because I heard a stock doubling after the IPO like Twitter’s did should be considered a failure. (The gains go to investors not Twitter, so Twitter should have set a higher price since other valued it more.)

Institution Analytics #USGRockEagle13

Janice Hill, Columbus State University

  • Process:
    • Define KPI’s : grades,  starting degree, ending degree, and many more.
    • Design and Implement : ODI integrator
      • Subject area example : summation helps reports only pull one row per student.
      • Updating : degrees awarded only loaded at end of term.
    • Validation of data : Work with Institutional Research to figure out where wrong. Consulting with individuals who think data did not look right.
    • Production release : Start a new cycle.
  • Data elements:
    • Banner, PeopleSoft, Excel spreadsheets
    • student head count, student attempted credit hours, and about 30 others.
  • Dashboards : 8 in production, 2 in completed validation, 2 subject areas ready to be built. Changes to a dashboard not saved across sessions, so users need to export to a file.
  • Structure of Dashboard : Level prompts : College, department, program, major, term. Analysis. Footnotes.
  • Users with access : President, VP, Deans, Dept heads.
  • Export types : PDF, Excel, Web,
  • Errors: BI data loaded at 6am, so local data pulled at 9am WILL result in very small differences.
  • Progression dashboard : credit hours by term, avg GPA by class, avg GPA vs credit hours earned, demographic breakdowns, grades by academic level, grades by section
  • Retention and Graduation dashboard : after 1 year, after 6 years. Use both counts and percentages.
  • Talk with faculty about their data needs so can show it exists or build it into a report.
  • Individualized training. Understanding how to filter is a challenging concept.
  • User tracking enabled, so know how long they stay on a dashboard, filters used, the SQL used.
  • Try to use as little filters as possible. Her job to get the data. User’s job is to interpret.
  • Decisions and policy affected by this data.
  • Trying to get grade data to improve early warning.
  • What are the products for which they want analytics?
  • Using University System of Georgia requirements for retention, so pegged to Fall enrollment. “Some times you have to go past what makes sense to you and implement the rule.”

Excellent session!

Content Migration Progress Tracking

When moving hundreds of thousands of courses between WebCT Vista and Desire2Learn, keeping track of what made it through which stage seems like an obvious hindsight thing to do. I added that last bit because we started to notice where things fell between the cracks starting to pile up. The basic process…

    1. Through Oracle SQL we populate a middleware database with those courses which meet acceptable criteria.
      CRACK: With the wrong criteria, courses are not even selected.
    2. Through Oracle SQL we generate XML listing in 50 count sets of the courses.
      CRACK: A subset of data loaded into the database may be extracted.
    3. A shell script automates running the WebCT command-line backup process to create a package for each course.
      CRACK: The command-line backup fails on some courses.
    4. Desire2Learn scripts pick up the files and convert WebCT formatted packages to Desire2Learn.
      CRACKS: Too big fail. Too long paths fail. This step can fail to create CSV files for the next step.
    5. Converted packages are placed in a queue to be imported into Desire2Learn.
      CRACKS: WebCT Vista courses can have 1,000 characters in the name and D2L fails if there are more than 50. Courses named the same as a previously loaded one but with a different file name loads both into the same course.

So, there are apparently five different stages and eight potential failures to track and no end-to-end tracking to even know what is missing. Which means inventing something to check logs for errors. 

First thing, I tried writing SQL to create an ordered list of the courses that are available.

The WebCT backups were a little tougher to convert into a useful list. The file names follow the format of Course_Parent_1234567890.bak. They were also in various directories, so I ended up doing something like this to get a list of the files, strip off the parents & time stamps, strip off the directories, and order it.

ls */*.bak | awk -F_ ‘{print $1}’ | awk -F\/ ‘{print $2}’ | sort

So I have two ordered lists. Anything in the D2L one and not in the WebCT one ought to be the work of clients making their own stuff. Anything in the WebCT one and not in the D2L one ought to be my missing ones. Only almost every line is a mismatch.

Visually comparing them, I realized the same courses had in effect different names. All the spaces were converted to underscores. So I ended up adding a sed to convert spaces to underscores after the sort.

Then I wrote some more scripts.

    • Go through the logs from #4 and #5 to display the errors. With it I was able to compare my list of missing with the errors and confirm why they did not come through.
    • Some of the cracks can be addressed by making new import files. So I wrote a script to add those lines to a redo.csv file. Touch up the problems and go.

Basically at this point it only covers 3-5. At some point I am going to have to check steps 1-5. 

Most Recent Data

One of the common complaints instructors have about CE/Vista is the Tracking reports don’t have recent enough data. They are shown this for selecting the date range.

Select a Date Range for the Report
Select a Date Range for the Report

Including here the most recent time the tracking was processed (which the application already displays to the server administrator in background jobs) would help the instructor know whether the data is as recent as 4:00 am or 1:00pm.

Maybe when Tracking will run again ought to be displayed to the instructor so he or she knows it will run within the hour or the next morning. That might cut down on instructors running it again and again expecting it to magically show data which won’t be available until many hours later.

Administrators some times have to pick the best operational time to run Tracking. We have direct login checks running several times per hour. When Tracking is run every hour and these checks run at the same time, the time these direct login checks took spiked. Users also complained about poor performance. So we have these run in the wee hours of the morning when users are not generally on the system.

Tracking Specific File Use

CE/Vista Reports and Tracking displays summaries of activity. If an instructor seeks to know who clicked on a specific file, then Reports and Tracking falls down on the job.

Course Instructor can produce a report of the raw tracking data. However, access to the role falls under the Administration tab so people running the system need to make a user specifically to enroll themselves at the course level to get the reports. (Annoying.)

Instead the administrators for my campuses pass up to my level of support requests to generate reports. For providing these I have SQL to produce a report. This example is for users who clicked on a specific file. Anything in bold is what the SQL composer will need to alter.

set lines 200 pages 9999
col user format a20
col action format a32
col pagename format a80

clear breaks computes
break on User skip 1
compute count of Action on User

select tp.user_name "User",ta.name "Action",
      to_char(tua.event_time,'MM/DD/RR HH24:MI:SS') "Time",
      NVL(tpg.name,'--') "PageName"
  from trk_person tp, trk_action ta, trk_user_action tua,
      trk_page tpg, learning_context lc
  where tp.id = tua.trk_person_id
    and ta.id = tua.trk_action_id
    and tua.trk_page_id = tpg.id (+)
    and tua.trk_learning_context_id = lc.id
    and lc.id = 1234567890
    and tpg.name like '%filename.doc%'
  order by tp.user_name,tua.event_time
/

Output

  • User aka tp.user_name – This is the student’s account.
  • Action aka ta.name – This is an artifact of the original script. You might drop it as meaningless from this report.
  • Time aka tua.event_time – Day and time the action took place.
  • PageName aka tpg.name – Confirmation of the file name. Keep if using like in a select on this.

Considerations

I use the learning context id (lc.id aka learning_context.id) because in my multi-institution environment, the same name of a section could be used in many places. This id ensures I data from multiple sections.

The tricky part is identifying the file name. HTML files generally will show up as the name of in the title tag (hope the instructor never updates it). Office documents generally will show as the file name. Here are a couple approaches to determining how to use tpg.name (aka trk_page.name).

  1. Look at the file in the user interface.
  2. Run the report without limiting results to any tpg.name. Identify out of the results the name you wish to search and use: tpg.name = ‘page name

Most tracked actions do have a page name. However, some actions do not. This SQL is designed to print a “–” in those cases.

Odd Tracking File Recording

Every time a Vista 3 node is shut down without going through the initiated shut down process, there is a chance of incorrect data written to the tracking files (in NodeA/tracking/). Normally it leaves strange characters or partial lines at the end of the file. This is the first time I have seen it write the contents of another log instead of the tracking data.

click – 1.0 – 1244228052889 – 1135588340001 – “nova.view.usg.edu-1244227762853-6288” – SSTU – discussion – “compiled-message-viewed” – “page name” – 558711383 –

click – 1.0 – 1244228052891 – 15.0; .NET CLR 1.1.4322)”

2009-04-23      20:58:35        0.0030  xxx.xxx.xxx.xxx    JxH1zg4fZT1LTGcpmyNW    200     GET     /webct/libraryjs.dowebct        locale=en_US    0       “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0; .NET CLR 1.1.4322)”

Even better. The node went down on June 5th at around 3pm. The lines from the other log were from April 23rd at 8:58pm.

Why am I surprised to see new incorrect behavior? Especially when the node was really confused?

Microsoft Outlook 2007 Wishlist

From 2001 to 2006, Microsoft Outlook was the email client I used for work (and on my home computer to access work stuff). Back then, Exchange was not available, so a number of the features were more hacks than reality. However, it worked pretty well.

When I changed jobs, Netscape and Thunderbird were the pre-installed clients. I opted for Thunderbird. It worked pretty well for me. Calendaring was in MeetingMaker. Everything worked pretty well.

Recently work shifted to Exchange, so going back to Outlook made sense. Maybe because I have so much experience, the transition was not as bad as it might have been. Still… These are gotchas which have annoyed me lately:

  1. Editable subject usability: The emails from our client issue tracking system put the description where its hidden. I was really pissed that I could not edit the subject until I figured out unlike most software which changes the shading to show it is now editable, Outlook just lets me edit at any time. Also, editing the subject after it is used by something else like a task results in the change in the email but not the task. (The main reason I want to change them is so it appears correctly in the task list. ) Copying to a second email results in the same problem. Apparently I have to either create a new task and copy-n-paste the subject I want or forward the email to myself.
  2. Spacebar moves to next message instead of next new message: I really like the Thunderbird method of skipping to the next unread message when I hit the spacebar at the end of the current message. It even will find the next unread message in another folder. Outlook just advances to the next message.
  3. Boolean is more than OR: I had this fantastic Thunderbird filter which looked for user@ AND domain.tld. Outlook only honors OR. We have 15 admin nodes and databases which send up reports. Alerts and tickets come from a different source and unaffected by this.
  4. Search ignores special characters: I thought in the past I had sent email to abc-defghi@domain.tld. However, the message bounced, so I searched my email for part of the address “abc-defghi” as its not in the address book. I got results which match “abc” not “abc-defghi”. So it ignored the hyphen and everything after. FAIL!
  5. Send email as plain text or paste a plain text: Yes, I know lots of people have HTML capable clients. I hate Outlook puts my replies in a sickly blue font. When I copy and paste from the elsewhere in the message, it changes the font. So then I have to go and do formatting to have a presentable email. I just want to type and send. I don’t care about fonts, colors, etc. If I did, then I would create a web page. … (Added 2009-JUN-03)

That’s it for now.

CE / Vista Undocumented Workspaces

On the WebCT Users email list (hosted by Blackboard) there is a discussion about a mysterious directory called unmarshall which suddenly appeared. We found it under similar circumstances as others by investigating why a node consumed so much disk space. Failed command-line restores end up in this unmarshall directory.

Unmarshalling in Java jargon means:

converting the byte-stream back to its original data or object 1

This suspiciously sounds like what a decryption process would use to convert a .bak file into a .zip so something can open the file.

This is fourth undocumented work space where failed files site for a while and cause problems and no forewarning from the vendor.

Previous ones are:

  1. Failed UI backups end up in the weblogic81 (Vista 3, does this still happen in Vista 8?) directory.
  2. Failed tracking data files end up in WEBCTDOMAIN/tracking (Vista 3, apparently no longer stored this way in Vista 4/8 according to CSU-Chico and Notre Dame)
  3. Web Services content ends up in /var/tmp/ and are named Axis####axis. These are caused by a bug in DIME (like MIME) for Apache Axis. No one is complaining about the content failing to arrive, so we presume the files just end up on the system.

#3 were the hardest to diagnose because of a lack of an ability to tie the data back to user activity.

Is this all there are? I need to do testing to see which of these I can cross off my list goring forward in Vista 8. Failed restores are on it indefinitely for now.
🙁

References:

  1. http://www.jguru.com/faq/view.jsp?EID=560072