What is LRPC in Customer Service?

In Today’s highly competitive market every business is trying hard to woo customers and retain them. Each and every customer is important for business, irrespective of their transaction size. Every penny counts. I was going through some customer facing training materials and I learn about LRPC.

LRPC means Life Time Revenue Per Customer. The life time value or simply dollar that a customer can bring to your business is called LRPC in customer service area. The concept of LRPC focuses on the importance of every customer. Each and every customer does some transaction with a business unit. This business unit can be a shop, restaurant, company etc. The size of the transaction can be small($2 or $5) or big(hundreds of dollars). The customer might be an existing customer or a new customer. Some business calculates LRPC for each customer. The calculation of LRPC gives business useful information. By calculating LRPC you come across revenue that you can earn from a customer in his or her life time.

LRPC gives a bigger picture of value that each customer can bring to the table. So no matter what the current size of transaction the customer is doing, serving them with quality services and satisfaction that they carry through your transaction can bring big value to your business.

Let us take an example to understand how LRPC is calculated. Mr. Jim is a customer and he buys $10 of grocery items from a shop. Now we can calculates LRPC for Mr. Jim.

LRPC calculation is a four step process.

Step 1: Average amount per sale

In this first step you consider what is the average amount that Mr. Jim spend each time he made a purchase or visit to your grocery store. For example let us consider he make $10 purchase every time he visits. So, average amount per sale is $10.

Step 2: No. of customer visit or sale/year

In this second step, you calculate how many times Mr. Jim made a purchase from your shop. You will have this information in your database. Let us say on an average he made 100 purchases in a year from your shop. So he is making $10*100=$1000 purchase in a year from your shop. You are doing annual sale of $1000 to Mr. Jim.

Step 3: Identify no. of years customer might make purchase

In this third step, you need to identity number of years Mr. Jim may come to the store. This number is assumption that can be taken from many factors such as Mr. Jim age, area of locality, financial condition etc, Let us say 20 years is timeline that Mr. Jim might keep coming to the store.

Step 4: Calculate Lifetime Revenue for customer

In this step, we need to multiply annual sale (Step 2) and no. of years customer might come to the store (Step 3) to calculate life time revenue that Jim might bring to the store.

$1000 * 20=$20000

So $20000 is the LRPC value that Mr. Jim can bring to your shop, provided he is served with quality products and with quality services.

So the point is it does not matter what the customer is buying today and as a business unit you should not avoid a customer just because he just make $10 purchase from your shop. Each and every customer that brings any size of transaction to your business is important.

How to read data from Google BigTables using .NET?

This article is next in series of exploring Google BigQuery and BigTables. In last few posts we learnt what is Google BigQuery and we created some BigTables for demonstration. In this post we will explore how we can read data from Google BigTables using .NET.

Before we jump to .NET, we need to understand some concepts which goes behind the scene towards pulling data from Google BigQuery. We need to know three important details:

- Project ID
- Client Key
- Client Secret

Project ID: When you create your Google account, you get default access to Google API console, a central place to mange access to your Google APIs and billing details. By default, Google creates a project with the name API Project for you. You can create a new project or rename the default project. You need to know Project id, a numeric value of your project or default project( if you are using the default project name). When you create BigTables with Google BigQuery services you create them under this project name. If you do not know the project id of your project, you can visit How to get Project ID of Google Console Projects?

Client Key and Client Secret: When you query data from Google services such as Google Spreadsheet, Google Docs, Picasa etc using your web/ windows application or mobile/ andorid based devices your applications act as client. As a client your identity and authorization need to be known to Google services and, Client key and Client Secret provides this. You need to generate your client key and client secret key using Google API console. This is because Google place limits on API requests and in case you are crossing the free quota limit, Google will charge you as per the uses. You need to visit Google API console to generate your client id and client seceret. When you visit the Google API console for the first time, you see the following screen. You need to click on API Access and go to Create an OAuth 2.0 client ID button to generate your authorization. OAuth is Google Open Authorization. By generation the Google Open Authorization client ID you are allowing user data to be read by clients such as a web page, web service, desktop/mobile applications etc. Next you can click on Generate Key link to generate your client key and client secret.

Once you are set with the three important details, you are all set to start with .NET. You need to download Google Client Library that contains DLLs that you will use in .NET applicaitons. You can download the library from http://code.google.com/p/google-api-dotnet-client/wiki/Downloads#Latest_Stable_Release.

I have following BigTable in Google BigQuery and we will read EmpName from Google BigTable Employee.

Reading data from Google BigTable using .NET is a two step process. In Step 1 we basically sent the user to Google Authorization page. Once the request is authorize, Google geneate an access code which is valid for a limited time. You need to copy this access code to beging with Step 2.

In .NET I have created following interface keeping the two step process in mind.

For Step 1, I have a button Get Authentication. When user click on this button, they are taken to Google Authorization page.

If user click on Allow access, Google generates an access code.

You need to copy this code and paste it to Text box in step 2. When you are done with this, you need to click on button Read Big Table. The code logic written on button 2 will interact with Google BigQuery services and pull data from Google BigTables and data will be shown in Data Grid.

The .NET code logic on this form is following. We need to add references of following Google Data API from the client library that we downloaded before.

using DotNetOpenAuth.OAuth2;
using Google.Apis.Authentication.OAuth2;
using Google.Apis.Authentication.OAuth2.DotNetOpenAuth;

using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;

using Google.Apis.Util;
using System.Diagnostics;

namespace BigQuery
{
    public partial class Form1 : Form
    {
        static string clientId = "<ClientID>.apps.googleusercontent.com";
        static string clientSecret = "<ClientSecret>";
        static string projectId = "<ProjectID>";
        static string query = "SELECT EmpName FROM [BigCompany.Employee];";
        static OAuth2Authenticator<NativeApplicationClient> xx;       
        static IAuthorizationState state;
        static NativeApplicationClient myclient;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            myclient = new NativeApplicationClient(GoogleAuthenticationServer.Description);
            myclient.ClientIdentifier = clientId;
            myclient.ClientSecret = clientSecret;

            state = new AuthorizationState(new[] { BigqueryService.Scopes.Bigquery.GetStringValue() });
            state.Callback = new Uri(NativeApplicationClient.OutOfBandCallbackUrl);
            Uri authUri = myclient.RequestUserAuthorization(state);

            Process.Start(authUri.ToString());          

        }

        private  IAuthorizationState GetAuthorization(NativeApplicationClient arg)
        {            
            return arg.ProcessUserAuthorization(textBox1.Text, state);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            xx = new OAuth2Authenticator<NativeApplicationClient>(myclient, GetAuthorization);
            
            // Create the Google BigQuery service.
            var service = new BigqueryService(xx);
            JobsResource j = service.Jobs;
            QueryRequest qr = new QueryRequest();
            qr.Query = query;

            QueryResponse response = j.Query(qr, projectId).Fetch();

            //Create a DataTable
            DataTable dt = new DataTable("emp");
            dt.Columns.Add("EmpName");
                    
            foreach (TableRow row in response.Rows)
            {
                foreach (TableRow.FData field in row.F)
                {
                    DataRow DR = dt.NewRow();
                    DR["EmpName"] = field.V;
                    dt.Rows.Add(DR);

                }
                
            }
            dataGridView1.DataSource = dt.DefaultView;
        }

      }
}

After clicking on Read BigTable data from Google BigQuery services was pulled and shown in the data grid.

So this is how we can read data from Google BigTables using .NET.

Reference: http://stackoverflow.com/questions/12443878/google-bigquery-with-net-documentation-samples

How to get Project ID of Google API Console Projects?

Today I learn an interesting thing about Google API Console. I was working on a .NET prototype with Google BigQuery and I wanted to know about my Google Project ID.

When you create a Google account and want to use Google Data API, the first thing you need to do is to enable your access to Google API. Google API console is the centralized place which gives you an interface to manage your access to Google API. When you login to Google API console by visiting to https://code.google.com/apis/console/ you find a default Project with the name API Project has been created for you.

If you want to get Project ID for this project, all you need to do is to look at the address bar, you will find your URL in following format https://code.google.com/apis/console/?pli=1#project:XXXXXXXXX, where XXXXXXXXX is your project ID.

How to apply JOINS on Google BigTables with Google BigQuery?

This article is in continuation towards exploring Google BigQuery and Google BigTables. In last few post we learnt what Google BigQuery is and how to create Dataset and BigTables with Google BigQuery. We also learnt how to read data from BigTables. In this post we will learn how we can join two BigTables and read data from both tables.

Google BigQuery service allows to apply Join on BigTables. There are two type of JOIN that Google BigQuery services supports on BigTables.

• Inner Join
• Left Outer Join

Google BigQuery also expects that when we are joining two BigTables, one of the BigTable is relatively small in size. A Big Table is considered as small if the data size in the table is less than 7MB. If we try to join two BigTables with data size of more than 7 MB, the Join will fail.

We can apply row filter by using WHERE clause while joining two tables but it only supports AND condition. What it mean is we can apply multiple row filter criteria using AND condition but if we try to apply JOIN and put OR condition in the Where clause the Join will fail.

Let us create an example by joining two BigTables. We have following two CSV files with data of Employees and Departments.

I created two BigTables Employee and Dept by navigating to Google BigQuery webpage https://bigquery.cloud.google.com

The goal is to join these two BigTables and read Employee code, Employee name and Department name. To join these two tables, I have written following query.

SELECT [BigCompany.Employee.EmpCode], [BigCompany.Employee.EmpName], [BigCompany.Dept.DeptName] FROM [BigCompany.Employee] JOIN [BigCompany.Dept] ON [BigCompany.Employee.DeptCode] = [BigCompany.Dept.DeptCode]

I clicked on Run Query and results were expected. The two BigTables were successfully Join and resultset had data from both tables

So this is how we can apply Join on Big Tables.

How to read data from Google BigTables using Google BigQuery?

This post is in continuation towards our learning Google BigQuery. In last few posts we covered What is Google BigQuery and how to create dataset and BigTables. In this post we will learn how we can read data from Google BigTables using Google BigQuery?

With Google BigQuery we can write SQL-like statement to read data from Google BigTables. We can use the SELECT statement just like we use it to read data from SQL or Oracle tables. In last post we created a sample big table Employee_DS_BQ and stored employee data into it. To read this data using Google Browser Tool, we need to navigate to the Google BigQuery page. We need to click on Compose Query button.

To read data from employee table, we need to write query as following:

SELECT empName, empAge, empActive FROM [Employee_DS_BQ.myTable001].

If we want to limit our query resultset to n rows, we can use LIMIT clause. After writing query we need to click on RUN Query.

We can filter our query result set and apply condition using WHERE clause with Google BigQuery. In following example, I wanted to read data of employees where age is less than 25 so I applied WHERE clause in the query.

Google BigQuery provides Aggregrate functions, string functions, Bitwise functions, Comparison functions just like SQL Server or Oracle has provided with their products. We can JOIN two tables and apply Group by and Having clause into it. For Google BigQuery reference you can visit https://developers.google.com/bigquery/docs/query-reference.

How to create datasets and BigTables into Google BigQuery?

This article is in continuation to the last article What is Google BigQuery.

Once your access to Google BigQuery is enabled and your billing details are setup, you are all set to store data into Google cloud. The data stored in these BigTables can be accessed using three tools that Google has provided.

1. BigQuery Browser Tools
2. Bq Command-line tool
3. REST API

In this post we will examine BigQuery Browser Tool. To start with Browser Tool you need to navigate to Google BigQuery webpage https://bigquery.cloud.google.com/.

When you login into Google BigQuery, the default screen you see is following. Your projects are listed on the left side of panel. Google provides a sample dataset and few sample BigTables.

The first step towards creating BigTables is creating a Dataset. Dataset are containers for BigTables. To create Dataset you can click on your project and select Create new dataset. You will find the Create Dataset screen where you have to provide your Dataset a name.

For demonstration purpose, I created a dataset with the name Employee_DS_BQ.

Once I clicked on OK, data set Employee_DS_BQ got created and it got listed on the left side of panel.

Now we have created the dataset, it is time now to create BigTables. To create BigTables we need to click on the + sign next to dataset. This will launch the Create Tables screen.

In the Create Table screen, you have to enter your table name into Table ID field. In Schema field you need to specify your columns names. The columns are specified in the format [column name:datatype]. Google BigQuery currently supports following data types for columns:

- String
- Integer
- Boolean
- Float

You can click on OK button if you want or you can proceed to load data from Create Table screen. For demonstration purpose, I created one table EmpDetails and specified three columns empName, empAge and empActive.

The next thing I am doing is loading data into these tables. I have following data in a CSV file that I need to upload to the BigTable EmpDetails.

I clicked on Choose File button on Create Table screen and selected my CSV file. I clicked on Ok button.

A job was created by the Google BigQuery service and my BigTable was successfully created with the sample data.

So this is how we can create BigTable and load data inside it using Google BigQuery.

In next article we will learn to Query this data