Web Analytics: Advanced Data Analysis in Excel (II.)

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

The previous blog has introduced how to install advanced Data analysis features of Excel, and introduced the regression analysis, to tell the truth is a little long, mainly to install the screenshot more; This article mainly introduces descriptive statistics, sampling analysis and histograms.

I. Descriptive statistics

The median, the number, the data range may also be relatively easy to calculate, but the standard deviation and the poor calculation is more troublesome, these are descriptive sample data commonly used variables, the use of Excel data analysis of "descriptive statistics" to get the data.

For example: based on the conversion rate of e-commerce over the past 15 days, it is intended to describe statistical indicators of its data distribution, standard deviation, peak value, and polar aberration. Generally speaking, the conversion rate of e-commerce website is below 3%, conversion rate refers to the order divided by the number of visits, attention is not divided by PV, because for some foreign trade stations, the depth of access may be deep, every visit may have >10 PV, so use PV to do e-commerce conversion rate is not appropriate.

The data source looks like this:

Set according to the following illustration:

When you set up, you get the chart that looks like this: (The explanation is for me to add, is to explain the indicators)

Numerical Interpretation of indicators

Average 1.9% e-commerce conversion rate

Standard error 0.00201896 The square root of the average value of the square sum of the numerical error of the e-commerce conversion rate is also called the square root of the mean square error.

Median 0.019 in the middle of a sequence

Number of 0.018 occurrences

Standard deviation 0.00781939 is the average number of deviations from each data, which is the square root of the average difference, and in Σ, the standard deviation is the sum of the squares of variance.

Variance 6.1143E-05 The average of the squares of the difference between the data and the average

Kurtosis-0.4960863 a measure of fluctuations in data distribution, based on normal distribution, is positive compared with its smooth value, whereas negative;

Skewness-0.4923336 an exponent that measures the peak offset of a data, either positive or negative, on the left or right side of the average value;

difference between maximum and minimum value of region 0.025

Minimum value 0.005

Maximum Value 0.03

Sum 0.285

Number of observations 15 values

Max (1) 0.03

Min (1) 0.005

Confidence degree (95%) 0.00433023 the so-called confidence level, also known as confidence levels, it refers to the extent to which a particular individual is believed to be authentic.

II. Sampling Analysis

The sampling analysis tool creates a sample of the data source by taking the data source area as a whole. When the overall size is too large to be processed or drawn, a representative sample can be selected.

For example: Suppose such a situation, to check the electronic commerce conversion rate is normal.

The data source looks like this:

Follow the illustration below and note that 8 samples are drawn:

When you set up, you get the chart that looks like this:

Iii. histogram

Histograms are best suited to describe the distribution of data in different selected intervals.

The data source looks like this:

Set according to the following illustration:

After you set up, you get the data and the chart:

This is a very clear estimate of which interval the data is most widely distributed.

Excel's Advanced Data analysis features are designed to improve productivity, and if there are other tools that you use frequently to implement these features, such as SPSS, SAS, and so on, don't change it.

If you feel the value of reprint, please specify the article from Shenzhen website analysis. Questions and suggestions can be made at any time, thank you!

Web Analytics: Advanced Data Analysis in Excel (i)