Fun Fun Fun @bubbletea1314-blog - Tumblr Blog

Create Git Page from the Beginning

1. Create a repository in github. Named as ‘USERNAME.github.io’. e.g. my github username is ‘XiaojuanWu’ then the new repository is ‘XiaojuanWu.github.io’

2. Follow up this instructions

https://pages.github.com/

3. Open a browser and enter “https://XiaojuanWu.github.io”

4. Setup ‘jekyll’:

https://jekyllrb.com/docs/quickstart/

How to update Ruby

https://stackoverflow.com/questions/38194032/how-to-update-ruby-version-2-0-0-to-the-latest-version-in-mac-osx-yosemite/38194139

How to setup ‘rvm’

https://stackoverflow.com/questions/11677771/rvm-command-not-found-mac-ox

A little more about RVM (Ruby version manager)

https://rvm.io/rvm/basics

The guide to create and host a personal site on github

http://jmcglone.com/guides/github-pages/

bubbletea1314-blog

Github SSH key Management

1. To generate a new .ssh key

Open terminal:

Step 1 : ssh-keygen -t rsa -b 4096 -C “your github email address” Hit enter

Step 2: Enter a file in which to save the key (/Users/you/.ssh/id_rsa) Press enter

Step 3: Enter your computer password twice.

2. Check the existing ssh keys:

Open terminal:

Type: ls -al ~/.ssh

If you have any of those below:

>id_dsa.pub

>id_ecdsa.pub

>id_ed25519.pub

>id_rsa.pub

3. Add your SSH key to the ssh-agent

Step 1: Ensure ssh-agent is enabled:

Terminal: $eval “$(ssh-agent -s)”

Step 2: Add SSH key to the ssh-agent

Terminal: $ ssh -add ~/.ssh/id_rsa

4. Add a new SSH key to your GitHub account

Step 1: Copy your existing SSH key:

Terminal: pbcopy < ~/.ssh/id_rsa.pub

Open your GitHub account. Then do things according to below:

https://help.github.com/articles/adding-a-new-ssh-key-to-your-github-account/

5. Test the connection:

Terminal: ssh -T [email protected]

Step 2: Enter you computer password when it asks for it

How to calculate CLV

CLV = customer lifetime value

Simple formula = Annual profit contribution per customer * Average number of years that they remains as a customer - the initial cost of customer acquisition

Example:

Profit generated by a customer each year = $1,000

Number of years they remain as a customer of the brand is 5 years

Cost to acquire the customer = $2,000

The customer lifetime value = 1000 * 5 - 2000 = $3000

Another example: (How to use retention rate to cal CLV):

Annual revenue of customer is $2000

Product cost associated with customer is $500 per year;

Customer service fee is $100 per customer per year;

annual retention rate is 80%

Average cost to acquire a customer is $1000

Step 1: Calculate the annual profit of customer:

annual profit = annual revenue - cost

= 2000 - 500 - 100 = 1400

Step 2: Calculate the churn rate or the number of years customer remain as a customer:

churn rate = 1-retention rate = 1-80% = 20%

Number of years customer remain as a customer = 1/churn rate = 1/20% = 5 Years

Step 3: Calculate the CLV:

CLV = annual profit of a customer * the number of years customer remain as a customer - cost to acquire this customer

= 1400 * 5 - 1000

= 6000

So the CLV for this brand is $6000

Good for him.

#CLV #RETENTION RATE;#CHURN RATE

Logic Gates

AND; OR; XOR; NOT; NAND; NOR; NXOR;

AND: It is true if both inputs are true

OR: It is true if either inputs is true.

XOR: Exclusive OR: It is true only if one of those two inputs is true

NOT: Opposite the input; Input == True then Output == False;

NAND: Not AND; The opposite the output of AND operation;

NOR: Not OR; Opposite the output of OR operation;

XNOR: Not exclusive OR; Opposite the output of XOR;

Tricks:

Remember the AND and OR.

#Machine Learning #Logic Gate

Redshift Psql mac Environment Setup

Step one:

Download ProgreSQL server

Step two:

Find out the current psql folder

$locate psql | grep \bin

Step three: Setup the windows

$export PATH=/Library/psql/bin:$PATH

#psql #redshift #mac

Math and Machine Learning

This is about Linear Regression and Gradient Descent in Machine Learning

Important Concept:

Feature Scaling and Normalization

Gradient Descent

Linear Regression

Cost Function

Platform using:

Octave/Matlab

I will come back later

Github CLS

5 Really common and useful CLS of Github

First of all cd to the repository to you want to put your file locally

Step 1: Clone the Github repository to your local folder

$ git clone github repository https link

Step 2: Add new file to Github

$ git add filename

Step 3: Commit the file to Github

$ git commit -m ‘something you would like to add’

Step 4: (optional) Check the status

$ git status

Step 5: Push the file to Github repository

$ git push

#GitHub CLS

Github SSH key Management

1. To generate a new .ssh key

Open terminal:

Step 1 : ssh-keygen -t rsa -b 4096 -C “your github email address” Hit enter

Step 2: Enter a file in which to save the key (/Users/you/.ssh/id_rsa) Press enter

Step 3: Enter your computer password twice.

2. Check the existing ssh keys:

Open terminal:

Type: ls -al ~/.ssh

If you have any of those below:

>id_dsa.pub

>id_ecdsa.pub

>id_ed25519.pub

>id_rsa.pub

3. Add your SSH key to the ssh-agent

Step 1: Ensure ssh-agent is enabled:

Terminal: $eval “$(ssh-agent -s)”

Step 2: Add SSH key to the ssh-agent

Terminal: $ ssh-add ~/.ssh/id_rsa

4. Add a new SSH key to your GitHub account

Step 1: Copy your existing SSH key:

Terminal: pbcopy < ~/.ssh/id_rsa.pub

Open your GitHub account. Then do things according to below:

https://help.github.com/articles/adding-a-new-ssh-key-to-your-github-account/

5. Test the connection:

Terminal: ssh -T [email protected]

Step 2: Enter you computer password when it asks for it

#GitHub SSH

Python Tableau REST Api

Here I want to record how to use python and Tableau REST API to manage the credentials. It includes about 5 small scripts to add, remover, change user password, site role and get user info from tableau.

Tool: Python;

Environment: Ipython notebook

I would just put my Ipython notebook here

#Python Tableau REST API

Mysql Command line

Install Mysql:

sudo apt-get install mysql-server;

Access to Mysql by Command LIne:

Open terminal:

$mysql --host=hostname --user=username --port=portnumber --password

Then enter the password;

Some basic mysql commands:

show databases;

use databases;

show tables;

describe tablename;

Text Analysis_Python

This is a fun project as I use python and gmail API to grab all data of breakfast provided by our company. This dataset includes 180 day from the day I run the script (which is a parameter you can set it as any number as your wish).

Step 1:

Connect to Gmail account using python gmail API

Step 2:

Search all mails by related subjests

Step 3:

Data clean. Use NLTK to get the token, stem, strip and get rid of the stop words

Step 4:

Put all data to .csv file and save it to your local folder

Step 5:

Visualize data in Tableau to generate word clouds

Below is how it looks:

Develop Language: Python

Visualization tool: Tableau

Development Environment: Ipython

Main Libraries: imaplib, nltk, pandas

Step one: Connect to gmail (If your email asks for the two step certification, try to use the app password)

mail = imaplib.IMAP4_SSL('imap.gmail.com') mail.login(account,password) mail.select('inbox')

Step two: Get the email content and sent date

Step three:

Clean text and generate the csv file

Step four:

Vis it in Taleau

Let me put the reference site here first lol:

https://yuji.wordpress.com/2011/06/22/python-imaplib-imap-example-with-gmail/

#http://www.voidynullness.net/blog/2013/07/25/gmail-email-with-python-via-imap/#tag #Python; Gmail; Text analysis; API;

When iPython Notebook does not Work

You can use ipython. But can not get ipython notebook work try code below:

Step 1:

open terminal

Step 2:

pip uninstall ipython

Step 3:

pop install ‘ipython[all]’

That is it.

#iPython Notebook

Python Mysql Connect

Connect to mysql by python:

Pre-prepare:

1. Install mysql server

2. install MySQLdb module in python

Steps:

Method1: Use the MySQLdb module:

Python example code:

import MySQLdb # 打开数据库连接 db = MySQLdb.connect("localhost","testuser","test123","TESTDB" ) # 使用cursor()方法获取操作游标 cursor = db.cursor() # 使用execute方法执行SQL语句 cursor.execute("SELECT VERSION()") # 使用 fetchone() 方法获取一条数据库。 data = cursor.fetchone() print "Database version : %s " % data # 关闭数据库连接 db.close()

Method 1: Use the mysql connector

python example code:

import datetime import mysql.connector cnx = mysql.connector.connect(user='scott', database='employees') cursor = cnx.cursor() query = ("SELECT first_name, last_name, hire_date FROM employees " "WHERE hire_date BETWEEN %s AND %s") hire_start = datetime.date(1999, 1, 1) hire_end = datetime.date(1999, 12, 31) cursor.execute(query, (hire_start, hire_end)) for (first_name, last_name, hire_date) in cursor: print("{}, {} was hired on {:%d %b %Y}".format( last_name, first_name, hire_date)) cursor.close() cnx.close()

#Python Mysql Connection

Windows Function Partition by

when you count all orders and partitioned by customerid. Then when you want to get the total orders by specific customer, you should use max()

Null In SQL

Null is not Zero, Null is tricky.

E.g.

sql> select Count(*) as Total from Orders; +-------+ | Total | +-------+ | 100 | +-------+ sql> select Count(*) as Cust123Total from Orders where customer_id = '123' +--------------+ | Cust123Total | +--------------+ | 15 | +--------------+

Given the above query results, what will be the result of the query below?

sql> select Count(*) from Orders where customer_id <> '123'

The answer is not 85. It would be 80 or any number depends on the number of ‘NULL’ value in the table. The ‘NULL’ will not be counted to the ‘123′ nor to the other customer_ids. NULL is NULL, is not any other things.

E.g.2:

sql> SELECT * FROM runners; +----+--------------+ | id | name | +----+--------------+ | 1 | John Doe | | 2 | Jane Doe | | 3 | Alice Jones | | 4 | Bobby Louis | | 5 | Lisa Romero | +----+--------------+ sql> SELECT * FROM races; +----+----------------+-----------+ | id | event | winner_id | +----+----------------+-----------+ | 1 | 100 meter dash | 2 | | 2 | 500 meter dash | 3 | | 3 | cross-country | 2 | | 4 | triathalon | NULL | +----+----------------+-----------+

What will be the result of the query below?

select * from runners where id not in (select winner_id from races)

The answer is an empty set. When the null in ‘not in’ clause, it will return an empty set.

E.g.3:

select case when null = null then 'Yup' else 'Nope' end as Result;

The answer is Nope. Null cannot be used as ‘=‘. It should be ‘Select case when null is null then ‘Yup’ else ‘Nope’ end as Result;

#SQL

User retention is without doubt the most important metric with the user lifecycle. However, retention has a big problem: it has no unified definition.

Retention Analysis

#Data Analysis

DataWarehouse Populate the Dim_Date Table

When build the data warehouse, most of the tables can be populated from the data source. Except the date table. There I will record how I build the Date table and populate it using SQL script.

Step 1: Create dimensional date table, including date key, year, quarter, month, day:

CREATE TABLE Dim_Date (datekey INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Year] VARCHAR(4), [Quarter] VARCHAR(1), [Month] VARCHAR(2), [Day] VARCHAR(2))

Write SQL Script to populate the table. The date range is from 2000 to 2016

DECLARE @datestring VARCHAR(10), @date DATE, @startdate DATE, @enddate DATE, @Year VARCHAR(4), @Month VARCHAR(2), @Quarter VARCHAR(1), @Day VARCHAR(2); SET @startdate = '2000-01-01' SET @enddate = '2016-01-01' SET @date = @startdate

WHILE @date <= @enddate BEGIN SET @datestring = CONVERT(VARCHAR(10), @date) SET @Year = DATEPART(year, @datestring) SET @Month = DATEPART(month, @datestring) SET @Day = DATEPART(day, @datestring) SET @Quarter = CASE WHEN @Month<= 3 THEN 1 WHEN @Month<= 6 THEN 2 WHEN @Month<=9 THEN 3 WHEN @Month<= 12 THEN 4 END INSERT INTO dim_date VALUES( @Year, @Quarter,@Month,@Day) SET @date = dateadd(day, 1, @date) END

Check the result.

SELECT * FROM Dim_Date

#SQL

Trending Blogs

Recently Viewed Blogs

Fun Fun Fun