A note on using MS pivot tables for quantitative data analysis in dissertation projects: for MBA and Housing Studies students (information from perplexity.ai).
My question 1: "As a part-time dissertation project student, I find that the MS pivot table is quite easy to use to conduct quantitative data analysis on questionnaire survey data. My questions are (1) what are the main ways to employ MS pivot table to organize and summarize questionnaire survey data, e.g. on "how the Hong Kong consumers perceive the influence of influencer branding on their consumer behavior [an MBA topic]" and "how property price cutting influences the buying confidence of potential buyers of residential properties in Hong Kong [a Housing Studies topic]" and (2) what are the main actionable knowledge that can be obtained from the pivot tables produced?"
The reply: "Effective Use of MS Pivot Tables for Questionnaire Survey Data Analysis
(1) Main Ways to Employ MS Pivot Tables to Organize and Summarize Questionnaire Data
MS Excel's pivot tables are versatile tools for dissecting and analyzing survey data—especially for MBA and Housing Studies topics related to consumer behavior or property market confidence. Here are key approaches for using pivot tables in your dissertation research:
Frequency Distribution: Quickly tally responses for each survey question. For instance, see how many respondents selected each option for “How influential are social media influencers on your buying decision?” This provides a snapshot of opinion distribution.
Cross-tabulation / Segmentation: Place one survey variable on rows and another on columns (e.g., age group vs. influencer trust—MBA topic, or income level vs. response to price cuts—Housing Studies). This reveals relationships, such as which demographics are most swayed by influencers, or which buyer groups feel most confident after price reductions.
Summarizing Statistics: Pivot tables easily calculate averages, counts, and percentages (e.g., % of respondents rating influencer impact as “high” or “very high”). You can add calculated fields to show the proportion of strong agreement or compare means across groups.
Trend and Filter Analysis: Employ date or year fields to track changes over time, if your survey includes them (e.g., confidence in buying property before and after major price cuts). You can also filter by other respondent attributes using slicers for segment analysis.
Handling “Select All That Apply” Questions: Summarize multiple-choice questions where respondents can pick several options (e.g., “What sources influence your property purchase?”), by counting each option selection via the pivot table fields.
Data Reshaping with Power Query: If your data is not in the right format (wide vs. long), Power Query can convert your responses for cleaner, more flexible pivot table analysis.
Detecting Outliers and Patterns: Compare groups or time periods, flagging unusually high or low values—which can highlight where influencer branding or price cuts are having disproportionate effects.
(2) Main Actionable Knowledge from Pivot Table Analysis
Pivot tables transform raw survey data into insights that support strategic and academic conclusions:
Identify Key Drivers: Find which consumer segments respond strongest to influencer branding or to price adjustments. For example, if younger consumers (18–25) are more influenced by social media influencers, marketing can target those groups more aggressively.
Track Changes Over Time: See if consumer attitudes or buying confidence shifts after specific market interventions (e.g., new influencer campaigns or a wave of price cuts in property), assisting in evaluating effectiveness of these strategies.
Spot Patterns for Further Analysis: Detect unexpected patterns (outliers or trends) that merit qualitative follow-up—for example, if a small demographic has unexpectedly low buying confidence, this could prompt interviews or focus groups.
Resource Allocation: Actionable segmentation helps allocate resources efficiently—such as focusing influencer campaigns on buyer segments showing the most positive response in the pivot tables.
Inform Policy or Sales Approaches: For property market research, identify which buyer types (e.g., first-time buyers vs. investors) are most swayed by discounts, guiding sales tactics or public policy support.
Measure Impact: Quantify the effect of interventions by comparing averages, percentages, or counts before and after strategic actions (e.g., influencer partnerships or property discounts), supporting evidence-based recommendations in your dissertation.
Benchmarking and Hypothesis Generation: Use pivot tables to form theories (e.g., “influencer branding has a greater effect on lower-income consumers in Hong Kong”) and as input for further statistical testing.
“The set of pivot tables serves as a means to convey data analysis findings in the dissertation. The analysis enables further analytical stimulation—a researcher can generate additional research questions and empirical hypotheses.”
|
Topic
Example |
Pivot
Table Approach |
Actionable
Knowledge |
|
Influence
of Influencer Branding (MBA) |
Cross-tabulate
age, gender, and social media use against rating of influencer impact |
Targeted
marketing on the most responsive demographics |
|
Impact
of Property Price Cuts (Housing Studies) |
Cross-tabulate
buyer type, income, prior intent with responses to "Does a price cut
increase your buying confidence?" |
Design
tiered discounts or policy interventions for hesitant segments |
Utilizing these approaches lets you summarize, visualize, and interpret survey data directly in MS Excel, providing both a robust support for your dissertation’s findings and concrete recommendations for business or policy action"
My question 2: "What are the limitations of this usage of the MS pivot tables for questionnaire survey data analysis purpose?"
The reply: "The main limitations of using MS Excel pivot tables for questionnaire survey data analysis include:
Data Structure Constraints: Pivot tables require data to be in a "flat" or tidy format—each row as a single response with columns as variables. Survey datasets with complex or multiple-answer questions (e.g., "select all that apply") often need data reshaping or transformation (like using Power Query) before effective use with pivot tables.
Limited Complex Statistical Analysis: Pivot tables primarily summarize data with counts, averages, and percentages. They don't support advanced statistical tests (e.g., regressions, significance testing) directly within Excel, limiting deeper inferential analysis.
Handling Large or High-Dimension Data: While Excel can handle large datasets, very large surveys with many questions (>30 columns) or thousands of respondents may result in slow pivot table performance or complicated layouts.
Filtering and Segmentation Limitations: Filtering data within pivot tables can be done by defined fields but filtering on complex conditions (e.g., multi-criteria filters across unrelated variables) can be cumbersome or require manual workaround.
Aggregation Limitations for Multiple Responses: For multiple response questions, pivot tables count occurrences but cannot natively capture the nuance of overlapping responses without careful data pre-processing.
Data Visualization Constraints: Pivot tables provide tabular summaries but limited direct visualization options; users often need to create separate charts or use additional Excel features for graphical insights.
No Automatic Data Update: Pivot tables require manual refreshing when the underlying data changes, and if the data structure changes (adding columns/questions), the pivot table layout may need rebuilding or adjustment.
These aspects mean MS pivot tables are excellent for initial descriptive analysis and quick data summarization but are less suited for complex survey data structures or advanced analysis without supplementary tools or data preparation steps."
A collection of blog notes on using chatgpt for research purpose.
No comments:
Post a Comment