Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Appreciation Competition

Winter/Spring - OC Tanner's Apprecation Competition
by

Nick Baguley

on 24 March 2015

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Appreciation Competition

Appreciation Analytics
OC Tanner - An International Employee Recognition and Appreciation company, has provided us with data sets to help them determine the "ripple effect" of their awards.
Crash Course in Fact Recognition –
Nomination
Nomination

A printed certificate that is presented to the receiver in person, usually with a ceremony.
The nomination also
usually has a point
value that can be
used to redeem
items of the user's
choice from an online store.
They can accumulate points over
time.
Reward Codes
Reward_codes
Transaction ID
Transaction ID
The Data
User_query.sql
The query used to generate the data. There isn’t a header row in the big files, so they are including the query so you can know what the columns are.
Dim_user_sample.csv
Just a few rows so you can test your import without dealing with millions of rows
Dim_user.csv
The Whole Enchilada
Rec_query.sql
The query used to generate the file
Fact_recognition_sample.csv
Again a small file to help you test your import
Appreciation Competition
Advanced Analytics
The Questions they are posing:
When people give recognition, does it cause a ripple effect out into different employees and business units?


If so, who are our instigators of Appreciation?



Are there specific employee attributes or award levels that cause this to happen?

Does the business unit, height within the chain of command from the person instigating appreciation cause a ripple effect? Etc.
What causes them to be instigators?
In other words, what influence does recognition have on Others?
Fact_recognition.csv
Everything!
There are four types of recognition that we have consolidated into one table.
Nomination

Reward_Codes

Transaction ID

E-Products
A one time use gift card.
Used to redeem an amount up to X
Amount of points. If you don't use all of
the points you lose them. The
unredeemed points are not
accumulated over time.
If points are involved, there should be a Transaction ID.
This means that money was involved, and is a pointer
to the ledger table.
There are two ways to determine what type of recognition occurred
Recognition Recognition
1st - Only the ID of the type given will have a
value.
If an eproduct was given, nomination_nominee_id will be -2 (Not applicable).
2nd - The giver and receiver activities
will be the indicators.
Rules
All Results will be judged by OC Tanner
They will choose The overall Winner/Team
Community Rules
You've gotta play by the rules
Teams must have at least 2 members
At least 1 team member must reside in Utah and must be present to win.
Your solution/source code must be given to the judges at the end of the competition.

... Wait... That's it?
Judging Criteria
Accuracy of the model a team submits will be judged
Ability to transition your solution into production in a timely manner will be another criteria
Bonus Points
Awarded for indicators of the
Motivations behind the decision to provide an appreciation award.
What are they looking for?
Rules and details are subject to change
Read the fine print...
Scoring Criteria
Here is some additional information that
should help you see what OC Tanner is
Looking at to score the solutions.
Scoring Criteria
based on the Model, but includes Business Relevance
1. Accuracy
a. How predictive is/are the model/models?
2. Business Relevance
a. Would OC Tanner be able to implement a viable solution(s) around the model(s)?
3. Creativity
a. How did the team approach the problem and solution?


Additional Notes on Relevance
As for Business Relevance, is the resultant model and process something OC Tanner could actually leverage or is it a Netflix result that wouldn't be cost effective to implement?
We want to identify those who recognize more than others and what are their behaviors. We would also like to understand the results of their recognizing others.
For example, do the recognizers get promoted more often? Do those being recognized stay with the company longer? Do those being recognized tend to get promoted more or faster?
At the core, we want to understand the levers of increased recognition as well as the results.
Questions and Answers
There are two user identifiers. The first column is the user_key, which is a surrogate primary key in my user dimension. It is unique. The second row is the system_user_id. This is a foreign key to the user table where I get my data. In the file the system_user_id is not unique. I make a new copy every time the record changes in the source system. It may look like the record is a duplicate because the file does not contain all of the fields in the user dimension table.

If you want to count the number of unique users, you could have to select a count of distinct system_user_id.

I do see a problem in the data, but shouldn’t be a problem for your analysis. I truncated the effective and next effective dates to yyyymmdd, leaving out the time component. It shouldn’t be a problem because the fact table and user tables are linked with the user_key, so the effective date really isn’t important. I can generate another set of files if you would like.

One other note, the counts on the handout were generated about 30 minutes before our meeting Monday. The file was generated a month ago. The numbers aren’t going to match. I included it just as an example to show that the case of coded fields can’t be trusted.
Questions
... difficulty with “User” table - -it seems to be suffering some quality issues. For instance the “Current” field contains what looks like another row.

"\n8522253,2383143,ACTIVE,,20080721,sr. director business development and fleet…"

As a result our row counts don’t match the hand out from the other day, we’re higher by 60% or so. Our questions that might help everyone...

1. How many distinct employees should be in the user table? How many in the event table?
2. Is security role actually just free form text? containing things like "lead cigarette/tobac” or "bcc projspec”
3. Should “Current” ONLY be Y or N, with no nulls? (and is there any relationship to Employment Status definition)


To add more context to question 1. There are several times more rows in the users table then the actual count of users we would expect to get. The reason is probably because of a join in the sql that caused unintended duplication of records. Just a quick survey of the first few records in dim_users.csv show duplicates in the second column which should be system_user_id. Please advise how to handle these so we get the right "distinct" count of users.

For example, should we be using the timestamps to determine the account status so the current state would be the most recent record for each user? What method was used to get the counts in the handout.

[root@alton1 raw-data]# head dim_user.csv -n 20
146,72371,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
147,72372,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114
148,72372,"TERMINATED",20100518,,"account representative","0001305356",,6379,"N",20141114,20141114
149,72372,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
150,72373,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114
151,72373,"TERMINATED",20100518,,"account representative","0001305356",,6379,"N",20141114,20141114
152,72373,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
153,72377,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114
154,72377,"TERMINATED",20100518,,"account representative","0001305356",,6379,"N",20141114,20141114
155,72377,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
156,72380,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114
157,72380,"TERMINATED",20100518,,"account representative","0001305356",,6379,"N",20141114,20141114
158,72380,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
159,72381,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114
160,72381,"TERMINATED",20100518,,"account representative","0001305356",,6379,"N",20141114,20141114
161,72381,"TERMINATED",20100518,,"terminated","0001305356",,6379,"Y",20141114,90001231
162,72382,"ACTIVE",20100518,,"account representative","0001305356",,6379,"N",20100518,20141114


Answers
I know this is sloppy, Sorry.
AS IS THIS MUSTACHE
Full transcript